////// Reads the contents of an excel spreadsheet (.xlsx || .xls) /// or a comma separated value file (.csv) into a data table. /// (Works with Excel 2003 and Excel 2007) /// /// Path to file on server, type System.string /// True or false to indicate if first row is a header row or not /// Name of the spreadsheet to select data from. Use null for .csv file ///System.Data.DataTable protected DataTable GetFileContent(string path, bool hasHeader, string sheet) { #region Data Providers /*Office 2007*/ string ace = "Microsoft.ACE.OLEDB.12.0"; /*Office 97 - 2003*/ string jet = "Microsoft.Jet.OLEDB.4.0"; #endregion #region Excel Properties string xl2007 = "Excel 12.0 Xml"; string xl2003 = "Excel 8.0"; string imex = "IMEX=1"; #endregion #region CSV Properties string text = "text"; string fmt = "FMT=Delimited"; #endregion string hdr = hasHeader ? "Yes" : "No"; string conn = "Provider={0};Data Source={1};Extended Properties="{2};HDR={3};{4}";"; string select = "SELECT * FROM {0}"; string ext = Path.GetExtension(path); OleDbDataAdapter oda; DataTable dt = new DataTable("data"); switch (ext.ToLower()) { case ".xlsx": conn = String.Format(conn, ace, Path.GetFullPath(path), xl2007, hasHeader, imex); break; case ".xls": conn = String.Format(conn, jet, Path.GetFullPath(path), xl2003, hasHeader, imex); break; case ".csv": conn = String.Format(conn, jet, Path.GetDirectoryName(path), text, hasHeader, fmt); sheet = Path.GetFileName(path); break; default: throw new Exception("File Not Supported!"); } select = String.Format(select, sheet); oda = new OleDbDataAdapter(select, conn); oda.Fill(dt); return dt; }
0 Replies - 2192 Views - Last Post: 25 February 2011 - 12:04 AM
#1
Load data from Excel (2003 - 2007) or CSV file into Data Table
Posted 25 February 2011 - 12:04 AM
Description: Requires the following namespaces:
using System.Data.OleDb;
using System.Data;
using System.IO;
Also, HttpServerUtility is needed (for Server.MapPath())
//Sample usage:
gvFiles.DataSource = GetFileContent(filePath, true, "[Sheet1$]");
gvFiles.DataBind();This function will read the contents of an excel spreadsheet (.xlsx || .xls) or a comma separated value file (.csv) into a data table. (Works with Excel 2003 and Excel 2007)
Page 1 of 1