public static DataSet parse(string ExcelFilePath, string ZipPassword, string FirstColumnName) { //OleDbConnection cnnxls = null; DataSet ds = new DataSet(); if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".zip", StringComparison.CurrentCultureIgnoreCase)) { string strTmpFolder = ExcelFilePath + ".dir"; try { zip.ExtractAll(ExcelFilePath, strTmpFolder, ZipPassword); System.IO.DirectoryInfo rootDir = new System.IO.DirectoryInfo(strTmpFolder); foreach (System.IO.FileInfo fileInfo in rootDir.GetFiles("*", System.IO.SearchOption.AllDirectories)) { ds.Merge(parse(fileInfo.FullName, ZipPassword, FirstColumnName)); } } catch (Exception ex) { throw ex; } finally { System.IO.Directory.Delete(strTmpFolder, true); } //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strTmpFolder + ";Extended Properties=\"text;HDR=YES;IMEX=1;FMT=Delimited;\""; //cnnxls = new OleDbConnection(strConn); //cnnxls.Open(); } else if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".csv", StringComparison.CurrentCultureIgnoreCase)) { System.IO.FileInfo fileInfo = new System.IO.FileInfo(ExcelFilePath); DataTable table = CSVReader.parse(fileInfo.OpenRead(), true, ",", "\""); table.TableName = System.IO.Path.GetFileNameWithoutExtension(fileInfo.FullName); ds.Tables.Add(table); } else { NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(ExcelFilePath, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename); for (int sheetIndex = 0; sheetIndex < workBook.NumberOfSheets; sheetIndex++) { if (!workBook.IsSheetHidden(sheetIndex)) { int intHeaderRow = 0; NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(sheetIndex); NPOI.HSSF.UserModel.HSSFRow headerRow = null; //= (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow); if (!string.IsNullOrEmpty(FirstColumnName)) { for (int tmpRowIdx = intHeaderRow; tmpRowIdx <= workSheet.LastRowNum; tmpRowIdx++) { headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(tmpRowIdx); if (headerRow == null) { continue; } bool columnNameMatch = false; for (int tmpColumnIndex = 0; tmpColumnIndex <= headerRow.LastCellNum; tmpColumnIndex++) { if (headerRow.GetCell(tmpColumnIndex) != null) { string columnName = headerRow.GetCell(tmpColumnIndex).ToString().Trim(); if (FirstColumnName.Equals(columnName)) { intHeaderRow = tmpRowIdx; columnNameMatch = true; break; } } } if (columnNameMatch) { break; } } } else { headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow); } if (headerRow == null) { continue; } string tableName = workSheet.SheetName.Trim(); DataTable table = new DataTable(tableName); int intColumnIndex = 0; while (intColumnIndex <= headerRow.LastCellNum) { if (headerRow.GetCell(intColumnIndex) != null) { string columnName = headerRow.GetCell(intColumnIndex).ToString().Trim(); if (string.IsNullOrEmpty(columnName)) { columnName = "Column_" + intColumnIndex; } if (table.Columns.Contains(columnName)) { columnName = "Column_" + intColumnIndex; } table.Columns.Add(columnName, typeof(string)); // resign new value of column name to Excel for below part of import headerRow.GetCell(intColumnIndex).SetCellValue(columnName); } intColumnIndex++; } int rowCount = 1; while (intHeaderRow + rowCount <= workSheet.LastRowNum) { int colCount = 0; NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow + rowCount); if (row == null) { rowCount++; continue; } DataRow dataRow = table.NewRow(); while (colCount <= headerRow.LastCellNum) { if (headerRow.GetCell(colCount) != null) { string columnName = headerRow.GetCell(colCount).ToString(); if (table.Columns.Contains(columnName)) { NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(colCount); if (cell != null) { if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.FORMULA)) { NPOI.HSSF.UserModel.HSSFFormulaEvaluator e = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workBook); cell = (NPOI.HSSF.UserModel.HSSFCell)e.EvaluateInCell(cell); } string fieldValue = cell.ToString(); if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC)) { string format = string.Empty; //bool IsBuildinformat = false; // Not sure whether workBook.CreateDataFormat().GetFormat(index) can obtain all the build-in format try { format = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat(cell.CellStyle.DataFormat); //IsBuildinformat = true; } catch { format = workBook.CreateDataFormat().GetFormat(cell.CellStyle.DataFormat); } // [h]:mm:ss handle NOT support int midBlanketStartPos = format.IndexOf('['); while (midBlanketStartPos >= 0) { int midBlanketEndPos = format.IndexOf(']', midBlanketStartPos); format = format.Substring(0, midBlanketStartPos) + format.Substring(midBlanketStartPos + 1, midBlanketEndPos - midBlanketStartPos - 1) + format.Substring(midBlanketEndPos + 1); midBlanketStartPos = format.IndexOf('['); } if (format.IndexOf("y", StringComparison.CurrentCultureIgnoreCase) >= 0 || format.IndexOf("d", StringComparison.CurrentCultureIgnoreCase) >= 0) { if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0) { fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { DateTime date = cell.DateCellValue; if (date.TimeOfDay.TotalSeconds > 0) { fieldValue = date.ToString("yyyy-MM-dd HH:mm:ss"); } else { fieldValue = date.ToString("yyyy-MM-dd"); } } } else if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0) { DateTime date = cell.DateCellValue; // default date of "Time Only" field is 1899-12-31 if (!date.Date.Ticks.Equals(new DateTime(1899, 12, 31).Ticks)) { fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { fieldValue = cell.DateCellValue.ToString("HH:mm:ss"); } } else { fieldValue = cell.NumericCellValue.ToString(); } } dataRow[columnName] = fieldValue; } } } colCount++; } table.Rows.Add(dataRow); rowCount++; } ds.Tables.Add(table); } } //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES;\""; //cnnxls = new OleDbConnection(strConn); //try //{ // cnnxls.Open(); //} //catch //{ // cnnxls.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\""; // cnnxls.Open(); //} //DataTable schemaTable = cnnxls.GetSchema("Tables"); //foreach (DataRow schemaRow in schemaTable.Rows) //{ // string tableName = schemaRow["Table_Name"].ToString().Trim(); // if (tableName.EndsWith("$")) // { // OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", cnnxls); // try // { // //DataTable[] tables = oda.FillSchema(ds, SchemaType.Mapped);// // //tables[0].TableName = schemaRow["Table_Name"].ToString().Replace("$", "").Replace("#csv", ""); // //if (tables[0].Columns.Contains("Emp No*")) // // tables[0].Columns["Emp No*"].DataType = typeof(string); // //OleDbDataReader dr = oda.SelectCommand.ExecuteReader(); // //while (dr.Read()) // //{ // // DataRow row = tables[0].NewRow(); // // for (int i = 0; i < tables[0].Columns.Count; i++) // // row[i] = dr[i]; // // tables[0].Rows.Add(row); // //} // //// oda.Fill(tables[0]); // //if (ds.Tables.Contains(tableName) && tableName.ToString().EndsWith("$")) // // ds.Tables.Remove(tableName); // string actualTableName = tableName.Substring(0, tableName.Length - 1); // if (!ds.Tables.Contains(actualTableName)) // oda.Fill(ds, actualTableName); // } // catch // { // // unknown error caused by hidden sheet // } // // oda.Fill(ds); // } //} //cnnxls.Close(); } foreach (DataTable tempTable in ds.Tables) { for (int rowIdx = tempTable.Rows.Count - 1; rowIdx >= 0; rowIdx--) { DataRow row = tempTable.Rows[rowIdx]; bool isEmptyRow = true; foreach (DataColumn tempColumn in tempTable.Columns) { if (!row.IsNull(tempColumn)) { if (!string.IsNullOrEmpty(row[tempColumn].ToString().Trim())) { isEmptyRow = false; break; } } } if (isEmptyRow) { tempTable.Rows.Remove(row); } else { break; } } } foreach (DataTable tempTable in ds.Tables) { foreach (DataColumn tempColumn in tempTable.Columns) { string tempColumnName = tempColumn.ColumnName; tempColumnName = tempColumnName.Trim().Replace("*", ""); tempColumnName = tempColumnName.Trim().Replace("#", ""); tempColumn.ColumnName = tempColumnName; } } return(ds); }