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);
}