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);
}
public override DataTable UploadToTempDatabase(string Filename, int UserID, string ZipPassword)
{
ClearTempTable();
DataTable rawDataTable;
if (Filename.EndsWith(".xls"))
{
rawDataTable = HROne.Import.ExcelImport.parse(Filename, ZipPassword).Tables[0];
// Force override setting for excel format
DateSeparator = "-";
TimeSeparator = ":";
DateSequence = "YMD";
TimeSequence = "Hms";
YearFormat = "YYYY";
}
else
{
System.IO.FileStream reader = System.IO.File.OpenRead(Filename);
rawDataTable = CSVReader.parse(reader, UploadFileHasHeader, ColumnDelimiter, "\"");
reader.Close();
}
if (rawDataTable != null)
{
foreach (DataRow row in rawDataTable.Rows)
{
EUploadTimeCardRecord uploadTimeCardRecord = new EUploadTimeCardRecord();
EUploadTimeCardRecord uploadTimeCardRecord2 = null;
if (rawDataTable.Columns.Contains("SourceString"))
{
uploadTimeCardRecord.TimeCardRecordOriginalData = row["SourceString"].ToString();
}
else
{
foreach (object value in row.ItemArray)
{
string stringValue = string.Empty;
if (value != null)
{
stringValue = value.ToString();
}
if (string.IsNullOrEmpty(uploadTimeCardRecord.TimeCardRecordOriginalData))
{
uploadTimeCardRecord.TimeCardRecordOriginalData = stringValue;
}
else
{
uploadTimeCardRecord.TimeCardRecordOriginalData += "," + stringValue;
}
}
}
if (rawDataTable.Columns.Count >= TimeCardNumColumnIndex)
{
uploadTimeCardRecord.TimeCardRecordCardNo = row[TimeCardNumColumnIndex - 1].ToString().Trim();
}
else
{
errors.addError("Time Card column not found", null);
}
if (rawDataTable.Columns.Count >= DateColumnIndex && rawDataTable.Columns.Count >= TimeColumnIndex)
{
try
{
uploadTimeCardRecord.TimeCardRecordDateTime = getDateTime(row, DateColumnIndex, TimeColumnIndex);
}
catch (Exception)
{
// add empty datetime to skip import
uploadTimeCardRecord.TimeCardRecordDateTime = new DateTime();
//errors.addError(ex.Message, null);
}
}
else
{
errors.addError("Date/Time column not found", null);
}
if (DateColumnIndex2 > 0 && TimeColumnIndex2 > 0)
{
if (rawDataTable.Columns.Count >= DateColumnIndex2 && rawDataTable.Columns.Count >= TimeColumnIndex2)
{
uploadTimeCardRecord2 = new EUploadTimeCardRecord();
try
{
uploadTimeCardRecord2.TimeCardRecordDateTime = getDateTime(row, DateColumnIndex2, TimeColumnIndex2);
}
catch (Exception)
{
// add empty datetime to skip import
uploadTimeCardRecord2.TimeCardRecordDateTime = new DateTime();
//errors.addError(ex.Message, null);
}
}
else
{
errors.addError("2nd Date/Time column not found", null);
}
}
if (rawDataTable.Columns.Count >= LocationColumnIndex)
{
uploadTimeCardRecord.TimeCardRecordLocation = ETimeCardLocationMap.ConvertToNewLocationCode(dbConn, row[LocationColumnIndex - 1].ToString());
}
else
{
errors.addError("Location column not found", null);
}
uploadTimeCardRecord.TimeCardRecordInOutIndex = ETimeCardRecord.TimeCardRecordInOutIndexEnum.Unspecify; //unspecify
uploadTimeCardRecord.EmpID = Import.Parse.GetEmpIDFromCardNo(uploadTimeCardRecord.TimeCardRecordCardNo);
if (errors.List.Count > 0)
{
throw new HRImportException(errors.Message());
}
if (uploadTimeCardRecord.TimeCardRecordOriginalData.Length <= 450)
{
// Compare previous import record only when length <=450
DBFilter timeCardRecordFilter = new DBFilter();
timeCardRecordFilter.add(new Match("TimeCardRecordOriginalData", uploadTimeCardRecord.TimeCardRecordOriginalData));
ArrayList list = ETimeCardRecord.db.select(dbConn, timeCardRecordFilter);
if (list.Count > 0)
{
uploadTimeCardRecord.TimeCardRecordID = ((ETimeCardRecord)list[0]).TimeCardRecordID;
uploadTimeCardRecord.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE;
}
else
{
uploadTimeCardRecord.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT;
}
}
else
{
// Up to 450 character is stored for original data
uploadTimeCardRecord.TimeCardRecordOriginalData = uploadTimeCardRecord.TimeCardRecordOriginalData.Substring(0, 450);
uploadTimeCardRecord.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT;
}
uploadTimeCardRecord.SessionID = m_SessionID;
uploadTimeCardRecord.TransactionDate = AppUtils.ServerDateTime();
if (!uploadTimeCardRecord.TimeCardRecordDateTime.Ticks.Equals(0))
{
EUploadTimeCardRecord.db.insert(dbConn, uploadTimeCardRecord);
}
if (uploadTimeCardRecord2 != null)
{
uploadTimeCardRecord2.EmpID = uploadTimeCardRecord.EmpID;
uploadTimeCardRecord2.TimeCardRecordCardNo = uploadTimeCardRecord.TimeCardRecordCardNo;
uploadTimeCardRecord2.TimeCardRecordInOutIndex = uploadTimeCardRecord.TimeCardRecordInOutIndex;
uploadTimeCardRecord2.TimeCardRecordLocation = uploadTimeCardRecord.TimeCardRecordLocation;
uploadTimeCardRecord2.TimeCardRecordOriginalData = uploadTimeCardRecord.TimeCardRecordOriginalData + ":idx2";
uploadTimeCardRecord2.SessionID = uploadTimeCardRecord.SessionID;
uploadTimeCardRecord2.TransactionDate = uploadTimeCardRecord.TransactionDate;
if (uploadTimeCardRecord2.TimeCardRecordOriginalData.Length <= 450)
{
// Compare previous import record only when length <=450
DBFilter timeCardRecordFilter = new DBFilter();
timeCardRecordFilter.add(new Match("TimeCardRecordOriginalData", uploadTimeCardRecord2.TimeCardRecordOriginalData));
ArrayList list = ETimeCardRecord.db.select(dbConn, timeCardRecordFilter);
if (list.Count > 0)
{
uploadTimeCardRecord2.TimeCardRecordID = ((ETimeCardRecord)list[0]).TimeCardRecordID;
uploadTimeCardRecord2.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE;
}
else
{
uploadTimeCardRecord2.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT;
}
}
else
{
// Up to 450 character is stored for original data
uploadTimeCardRecord2.TimeCardRecordOriginalData = uploadTimeCardRecord.TimeCardRecordOriginalData.Substring(0, 450);
uploadTimeCardRecord2.ImportActionStatus = ImportDBObject.ImportActionEnum.INSERT;
}
if (!uploadTimeCardRecord2.TimeCardRecordDateTime.Ticks.Equals(0))
{
EUploadTimeCardRecord.db.insert(dbConn, uploadTimeCardRecord2);
}
}
}
}
return(GetImportDataFromTempDatabase(null));
}
