- /// <summary>
- /// 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)
- /// </summary>
- /// <param name="path">Path to file on server, type System.string</param>
- /// <param name="hasHeader">True or false to indicate if first row is a header row or not</param>
- /// <param name="sheet">Name of the spreadsheet to select data from. Use null for .csv file</param>
- /// <returns>System.Data.DataTable</returns>
- 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;
- }
来源: http://www.phpxs.com/code/1008883/