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); }
private void button13_Click(object sender, EventArgs e) { //清空报表 report1.ClearReport(); //设置报表为非只读 report1.ReadOnly = false; //报表停止公式计算,这样速度快点 report1.CalcFormula(false); //报表停止绘制 report1.AllowDraw(false); string fileName = Application.StartupPath + "\\Test.xls"; NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook; try { this.Cursor = Cursors.WaitCursor; using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream); NPOI.HSSF.UserModel.HSSFSheet sheet = hssfworkbook.GetSheetAt(0); if (sheet.LastRowNum == 0) { this.Cursor = Cursors.Arrow; return; } else { //产生列 NPOI.HSSF.UserModel.HSSFRow row = sheet.GetRow(0); //report1.AddColumn(row.Cells.Count); report1.AddColumn(row.LastCellNum); } int rowIndex = 0; System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { //增加行 rowIndex = report1.AddRow(Gscr.Band.Detail); NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current; for (int i = 0; i < row.LastCellNum; i++) { Gscr.Cell cellRpt = report1[rowIndex, i]; NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(i); if (cell == null) { cellRpt.Value = null; } else { switch (cell.CellType) { case NPOI.HSSF.UserModel.HSSFCellType.BLANK: cellRpt.Value = null; break; case NPOI.HSSF.UserModel.HSSFCellType.BOOLEAN: cellRpt.Value = cell.BooleanCellValue; break; case NPOI.HSSF.UserModel.HSSFCellType.NUMERIC: cellRpt.Value = cell.ToString(); break; case NPOI.HSSF.UserModel.HSSFCellType.STRING: cellRpt.Value = cell.StringCellValue; break; case NPOI.HSSF.UserModel.HSSFCellType.ERROR: cellRpt.Value = cell.ErrorCellValue; break; default: break; } } } } } //报表恢复公式计算 report1.CalcFormula(true); //报表恢复绘制 report1.AllowDraw(true); this.Cursor = Cursors.Arrow; MessageBox.Show("成功读取Excel文件【" + fileName + " 】。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception err) { report1.CalcFormula(true); report1.AllowDraw(true); this.Cursor = Cursors.Arrow; MessageBox.Show("读取Excel文件失败。" + err.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Stop); } }
private void GenerateRosterTableData(ArrayList EmpInfoList, int year, int month) { DateTime dateStart = new DateTime(year, month, 1); DateTime dateEnd = new DateTime(year, month, DateTime.DaysInMonth(year, month)); string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; const string FIELD_EMP_NO = "Emp. No"; const int COLUMN_HEADER_ROW = 2; NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("RosterTable"); NPOI.HSSF.UserModel.HSSFCellStyle upperLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle leftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); leftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle rightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); rightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle upperLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperLeftLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; upperLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomLeftLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; bottomLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle upperRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperRightLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; upperRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomRightLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; bottomRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; workSheet.CreateRow(0).CreateCell(0).SetCellValue("Year"); workSheet.GetRow(0).CreateCell(1).SetCellValue(year); workSheet.CreateRow(1).CreateCell(0).SetCellValue("Month"); workSheet.GetRow(1).CreateCell(1).SetCellValue(month); NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW); DBFilter hLevelFilter = new DBFilter(); hLevelFilter.add("HLevelSeqNo", true); int HIERARCHYLEVEL_COLUMN = 0; ArrayList hLevelList = EHierarchyLevel.db.select(dbConn, hLevelFilter); for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++) { EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex]; headerRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hLevel.HLevelDesc); } int POSITION_COLUMN = headerRow.LastCellNum; headerRow.CreateCell(POSITION_COLUMN).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Position")); int EMPNO_COLUMN = headerRow.LastCellNum; headerRow.CreateCell(EMPNO_COLUMN).SetCellValue(FIELD_EMP_NO); headerRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Name")); headerRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Alias")); headerRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Chinese Name")); NPOI.HSSF.UserModel.HSSFCellStyle sundayStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); NPOI.HSSF.UserModel.HSSFFont sundayFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont(); sundayFont.Color = NPOI.HSSF.Util.HSSFColor.RED.index; sundayStyle.SetFont(sundayFont); Hashtable styleList = new Hashtable(); ArrayList availableRosterClientList = new ArrayList(); ArrayList availableRosterClientSiteList = new ArrayList(); #region Create Column Header int ROSTER_DETAIL_COLUMN = headerRow.LastCellNum; for (int i = 1; i <= DateTime.DaysInMonth(year, month); i++) { //workSheet.Cells.Add(HEADER_ROW, ROSTAER_DETAIL_COLUMN + i - 1,i); NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(ROSTER_DETAIL_COLUMN + i - 1); headerCell.SetCellValue(i); if (new DateTime(year, month, i).DayOfWeek == DayOfWeek.Sunday) { headerCell.CellStyle = sundayStyle; } } #endregion #region Create Employee Roster Detail int recordCount = 0; foreach (EEmpPersonalInfo empInfo in EmpInfoList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { recordCount++; //workSheet.Cells.Add(HEADER_ROW + recordCount, 1,empInfo.EmpNo); //workSheet.Cells.Add(HEADER_ROW + recordCount, 2,empInfo.EmpEngFullName); NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + recordCount); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, dateEnd, empInfo.EmpID); if (empPos != null) { for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++) { EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex]; DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); if (empHierarchyList.Count > 0) { EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0]; EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { detailRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hElement.HElementDesc); } } } EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { detailRow.CreateCell(POSITION_COLUMN).SetCellValue(position.PositionDesc); } } detailRow.CreateCell(EMPNO_COLUMN).SetCellValue(empInfo.EmpNo); detailRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(empInfo.EmpEngFullName); detailRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(empInfo.EmpAlias); detailRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(empInfo.EmpChiFullName); DBFilter rosterTableFilter = new DBFilter(); rosterTableFilter.add(new Match("EmpID", empInfo.EmpID)); rosterTableFilter.add(new Match("RosterTableDate", ">=", dateStart)); rosterTableFilter.add(new Match("RosterTableDate", "<=", dateEnd)); ArrayList rosterTableList = ERosterTable.db.select(dbConn, rosterTableFilter); foreach (ERosterTable rosterTable in rosterTableList) { ERosterCode rosterCode = new ERosterCode(); rosterCode.RosterCodeID = rosterTable.RosterCodeID; if (ERosterCode.db.select(dbConn, rosterCode)) { string value = string.Empty; //if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists ((ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)) ) // value = workSheet.Rows[(ushort)(HEADER_ROW+ recordCount)].CellAtCol( (ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)).Value.ToString(); //if (string.IsNullOrEmpty(value)) // workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,rosterCode.RosterCode); //else // workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,value + "|" + rosterCode.RosterCode); int cellColIndex = ROSTER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1; NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex); } else { value = rosterCell.StringCellValue; } string rosterCodeValue = rosterCode.RosterCode; if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0) || !rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0)) { DateTime inTime = rosterCode.RosterCodeInTime; DateTime outTime = rosterCode.RosterCodeOutTime; if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0)) { inTime = rosterTable.RosterTableOverrideInTime; } if (!rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0)) { outTime = rosterTable.RosterTableOverrideOutTime; } rosterCodeValue += "(" + inTime.ToString("HHmm") + "~" + outTime.ToString("HHmm") + ")"; } if (string.IsNullOrEmpty(value)) { rosterCell.SetCellValue(rosterCodeValue); //if (!string.IsNullOrEmpty(rosterCode.RosterCodeColorCode)) //{ // //System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode); // //System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color); // //rosterCell.CellStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex(); // //rosterCell.CellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; // //rosterCell.CellStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex(); // string styleCode = "RosterCode" + "_" + rosterCode.RosterCode; // if (styleList.Contains(styleCode)) // rosterCell.CellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)styleList[styleCode]; // else // { // NPOI.HSSF.UserModel.HSSFCellStyle rosterCodeStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); // System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode); // System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color); // rosterCodeStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex(); // rosterCodeStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; // rosterCodeStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex(); // styleList.Add(styleCode, rosterCodeStyle); // rosterCell.CellStyle = rosterCodeStyle; // } //} } else { rosterCell.SetCellValue(value + "|" + rosterCodeValue); //rosterCell.CellStyle=workBook.GetCellStyleAt(0); } } } for (DateTime dateIndex = dateStart; dateIndex <= dateEnd; dateIndex = dateIndex.AddDays(1)) { string value = string.Empty; //if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)) ) // value = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)).Value.ToString(); int cellColIndex = ROSTER_DETAIL_COLUMN + dateIndex.Day - 1; NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex); } else { value = rosterCell.StringCellValue; } if (string.IsNullOrEmpty(value)) { EEmpPositionInfo empPosInfo = AppUtils.GetLastPositionInfo(dbConn, dateIndex, empInfo.EmpID); if (empPosInfo != null) { if ((empPosInfo.EmpPosEffTo.Ticks.Equals(0) || dateIndex <= empPosInfo.EmpPosEffTo)) { EWorkHourPattern workPattern = new EWorkHourPattern(); workPattern.WorkHourPatternID = empPosInfo.WorkHourPatternID; if (EWorkHourPattern.db.select(dbConn, workPattern)) { ERosterCode rosterCode = new ERosterCode(); rosterCode.RosterCodeID = workPattern.GetDefaultRosterCodeID(dbConn, dateIndex); if (ERosterCode.db.select(dbConn, rosterCode)) { //workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1, rosterCode.RosterCode); rosterCell.SetCellValue(rosterCode.RosterCode); } } DBFilter empRosterTableGroupListFilter = new DBFilter(); empRosterTableGroupListFilter.add(new Match("EmpID", empInfo.EmpID)); empRosterTableGroupListFilter.add(new Match("empRosterTableGroupEffFr", "<=", dateIndex)); OR orEmpPosEffToTerms = new OR(); orEmpPosEffToTerms.add(new Match("empRosterTableGroupEffTo", ">=", dateIndex)); orEmpPosEffToTerms.add(new NullTerm("empRosterTableGroupEffTo")); empRosterTableGroupListFilter.add(orEmpPosEffToTerms); ArrayList empRosterTableGroupList = EEmpRosterTableGroup.db.select(dbConn, empRosterTableGroupListFilter); foreach (EEmpRosterTableGroup empRosterTableGroup in empRosterTableGroupList) { ERosterTableGroup rosterTableGroup = new ERosterTableGroup(); rosterTableGroup.RosterTableGroupID = empRosterTableGroup.RosterTableGroupID; if (ERosterTableGroup.db.select(dbConn, rosterTableGroup)) { if (rosterTableGroup.RosterClientSiteID > 0) { if (!availableRosterClientSiteList.Contains(rosterTableGroup.RosterClientSiteID)) { availableRosterClientSiteList.Add(rosterTableGroup.RosterClientSiteID); } } else if (rosterTableGroup.RosterClientID > 0) { if (!availableRosterClientList.Contains(rosterTableGroup.RosterClientID)) { availableRosterClientList.Add(rosterTableGroup.RosterClientID); } } } } } } } } DBFilter leaveAppFilter = new DBFilter(); leaveAppFilter.add(new Match("EmpID", empInfo.EmpID)); leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", dateStart)); leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", dateEnd)); ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter); foreach (ELeaveApplication leaveApp in leaveAppList) { ELeaveCode leaveCode = new ELeaveCode(); leaveCode.LeaveCodeID = leaveApp.LeaveCodeID; if (ELeaveCode.db.select(dbConn, leaveCode)) { //if (leaveCode.LeaveCodeColorCode.Length == 6) //{ // try // { // int red = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(0, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // int green = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(2, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // int blue = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(4, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // string Color = System.Drawing.Color.FromArgb(red, green, blue).ToKnownColor().ToString(); // for (DateTime dateIndex = leaveApp.LeaveAppDateFrom; dateIndex <= leaveApp.LeaveAppDateTo; dateIndex = dateIndex.AddDays(1)) // { // org.in2bits.MyXls.Cell cell = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)); // //cell.Pattern = 1; // //cell.PatternColor = org.in2bits.MyXls.Colors.Yellow; // } // } // catch // { // } //} } } } } #endregion #region Create Roster Code Liet OR orRosterCodeTerm = new OR(); foreach (int rosterClientID in availableRosterClientList) { AND andRosterCodeTerms = new AND(); orRosterCodeTerm.add(new Match("RosterClientID", rosterClientID)); } foreach (int rosterClientSiteID in availableRosterClientSiteList) { AND andRosterCodeTerms = new AND(); orRosterCodeTerm.add(new Match("RosterClientSiteID", rosterClientSiteID)); } orRosterCodeTerm.add(new Match("RosterClientID", 0)); DBFilter rosterCodeListFilter = new DBFilter(); rosterCodeListFilter.add(orRosterCodeTerm); rosterCodeListFilter.add("RosterCode", true); ArrayList rosterCodeList = ERosterCode.db.select(dbConn, rosterCodeListFilter); int ROSTER_CODE_START_ROW = COLUMN_HEADER_ROW + recordCount + 5; int rosterCodeCount = 0; int maxColumnCount = 3; int columnCellWidth = 9; int maxRowCount = (int)(rosterCodeList.Count / maxColumnCount) + (rosterCodeList.Count % maxColumnCount == 0 ? 0 : 1); foreach (ERosterCode rosterCode in rosterCodeList) { int currentRowNum = rosterCodeCount % maxRowCount; int currentColumnNum = (rosterCodeCount / maxRowCount) * columnCellWidth; rosterCodeCount++; NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(ROSTER_CODE_START_ROW + currentRowNum); if (rosterCodeRow == null) { rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(ROSTER_CODE_START_ROW + currentRowNum); } NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum); rosterCell.SetCellValue(rosterCode.RosterCode); rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum + 1); rosterCell.SetCellValue(rosterCode.RosterCodeDesc); if (rosterCodeCount.Equals(1)) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN - 1); rosterCell.SetCellValue("Code:"); } } for (int rowIdx = ROSTER_CODE_START_ROW - 1; rowIdx < ROSTER_CODE_START_ROW + maxRowCount + 1; rowIdx++) { NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowIdx); if (rosterCodeRow == null) { rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowIdx); } if (rowIdx == ROSTER_CODE_START_ROW - 1) { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = upperLeftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = upperRightLineStyle; } else { rosterCell.CellStyle = upperLineStyle; } } } else if (rowIdx == ROSTER_CODE_START_ROW + maxRowCount) { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = bottomLeftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = bottomRightLineStyle; } else { rosterCell.CellStyle = bottomLineStyle; } } } else { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = leftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = rightLineStyle; } //else // rosterCell.CellStyle = bottomLineStyle; } } } #endregion //doc.FileName = exportFileName; //doc.Save(); System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workBook.Write(file); file.Close(); WebUtils.TransmitFile(Response, exportFileName, "RosterTable_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); Response.End(); }
protected void btnGenerate_Click(object sender, EventArgs e) { const string FIELD_COMPANY = "Company"; const string FIELD_POSITION = "Position"; const string FIELD_PAYROLLGROUP = "Payroll Group"; const string FIELD_EMPNO = "EmployeeID"; const string FIELD_EMPENGFULLNAME = "English Name"; const string FIELD_CHINESENAME = "¤¤¤å©m¦W"; const string FIELD_HKID = @"HKID/Passport"; const string FIELD_PERIODFROM = "From"; const string FIELD_PERIODTO = "To"; const string FIELD_WAGESWORK = "Wages Paid"; const string FIELD_WORKHOURTOTAL = "Total Working Hours"; const string FIELD_RESTDAYTOTAL = "No. of Rest Day"; const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH"; const string FIELD_FULLPAIDLEAVETOTAL = "No. of Full Paid Leave"; const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave"; ArrayList list = new ArrayList(); foreach (RepeaterItem i in Repeater.Items) { CheckBox cb = (CheckBox)i.FindControl("ItemSelect"); if (cb.Checked) { EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject(); WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb); list.Add(o); } } ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList(); if (list.Count > 0 && payPeriodList.Count > 0) { //const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] "; string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; //System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true); HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName); DataSet dataSet = new DataSet(); //export.GetDataSet(); DataTable dataTable = new DataTable("Payroll$"); dataSet.Tables.Add(dataTable); dataTable.Columns.Add(FIELD_COMPANY, typeof(string)); DBFilter hierarchyLevelFilter = new DBFilter(); Hashtable hierarchyLevelHashTable = new Hashtable(); hierarchyLevelFilter.add("HLevelSeqNo", true); ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter); foreach (EHierarchyLevel hlevel in hierarchyLevelList) { dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string)); hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel); } dataTable.Columns.Add(FIELD_POSITION, typeof(string)); dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string)); dataTable.Columns.Add(FIELD_EMPNO, typeof(string)); dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string)); dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string)); dataTable.Columns.Add(FIELD_HKID, typeof(string)); dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime)); dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime)); dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double)); dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double)); dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double)); dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double)); int firstSummaryColumnPos = dataTable.Columns.Count; int firstDetailColumnPos = dataTable.Columns.Count; foreach (EPayrollPeriod payPeriod in payPeriodList) { if (EPayrollPeriod.db.select(dbConn, payPeriod)) { EPayrollGroup payrollGroup = new EPayrollGroup(); payrollGroup.PayGroupID = payPeriod.PayGroupID; EPayrollGroup.db.select(dbConn, payrollGroup); foreach (EEmpPersonalInfo empInfo in list) { EEmpPersonalInfo.db.select(dbConn, empInfo); EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID)); empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID)); if (PayrollStatus.SelectedValue.Equals("T")) { empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T")); } else { empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T")); } DataRow row = dataTable.NewRow(); row[FIELD_EMPNO] = empInfo.EmpNo; row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName; row[FIELD_CHINESENAME] = empInfo.EmpChiFullName; row[FIELD_HKID] = empInfo.EmpHKID; row[FIELD_PERIODFROM] = payPeriod.PayPeriodFr; row[FIELD_PERIODTO] = payPeriod.PayPeriodTo; DBFilter empPosFilter = new DBFilter(); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID); if (empPos != null) { ECompany company = new ECompany(); company.CompanyID = empPos.CompanyID; if (ECompany.db.select(dbConn, company)) { row[FIELD_COMPANY] = company.CompanyCode; } EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { row[FIELD_POSITION] = position.PositionDesc; } DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); foreach (EEmpHierarchy empHierarchy in empHierarchyList) { EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID]; if (hierarchyLevel != null) { EHierarchyElement hierarchyElement = new EHierarchyElement(); hierarchyElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hierarchyElement)) { row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc; } } } EPayrollGroup curentPayGroup = new EPayrollGroup(); curentPayGroup.PayGroupID = empPos.PayGroupID; if (EPayrollGroup.db.select(dbConn, curentPayGroup)) { row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc; } } double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0; double mcER = 0, mcEE = 0; double vcER = 0, vcEE = 0; double pFundER = 0, pFundEE = 0; double wagesByWork = 0; double wagesByRest = 0; double fullPaidLeaveDays = 0; double nonFullPaidLeaveDays = 0; DBFilter paymentRecordFilter = new DBFilter(); paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); paymentRecordFilter.add(new Match("PayRecStatus", "A")); ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter); foreach (EPaymentRecord paymentRecord in paymentRecords) { EPaymentCode payCode = new EPaymentCode(); payCode.PaymentCodeID = paymentRecord.PaymentCodeID; EPaymentCode.db.select(dbConn, payCode); // Always Use Payment Code Description for grouping payment code with same description //string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc; //if (dataTable.Columns[fieldName] == null) // dataTable.Columns.Add(new DataColumn(fieldName, typeof(double))); //if (row[fieldName] == null || row[fieldName] == DBNull.Value) // row[fieldName] = 0; //row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount; netAmount += paymentRecord.PayRecActAmount; if (payCode.PaymentCodeIsMPF) { releventIncome += paymentRecord.PayRecActAmount; } else { nonRelevantIncome += paymentRecord.PayRecActAmount; } DBFilter taxPaymentMapFilter = new DBFilter(); taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID)); if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0) { taxableAmount += paymentRecord.PayRecActAmount; } else { nonTaxableAmount += paymentRecord.PayRecActAmount; } if (payCode.PaymentCodeIsWages) { if (paymentRecord.PayRecIsRestDayPayment) { wagesByRest += paymentRecord.PayRecActAmount; } else { wagesByWork += paymentRecord.PayRecActAmount; } } } DBFilter mpfRecordFilter = new DBFilter(); mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter); foreach (EMPFRecord mpfRecord in mpfRecords) { vcER += mpfRecord.MPFRecActVCER; mcER += +mpfRecord.MPFRecActMCER; vcEE += mpfRecord.MPFRecActVCEE; mcEE += mpfRecord.MPFRecActMCEE; } ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter); foreach (EORSORecord orsoRecord in orsoRecords) { pFundER += orsoRecord.ORSORecActER; pFundEE += orsoRecord.ORSORecActEE; } row[FIELD_WAGESWORK] = wagesByWork; DBFilter workingSummaryFilter = new DBFilter(); workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr)); if (empTerm != null) { workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo)); } else { workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo)); } workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID)); ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter); double workHourTotal = 0, restDayTotal = 0; foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList) { workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours; restDayTotal += empWorkSummary.EmpWorkingSummaryRestDayEntitled; } row[FIELD_WORKHOURTOTAL] = workHourTotal; row[FIELD_RESTDAYTOTAL] = restDayTotal; DBFilter statutoryHolidayFilter = new DBFilter(); statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr)); if (empTerm != null) { statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo)); } else { statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo)); } ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter); double restDayCount = 0; foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList) { restDayCount++; } row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount; DBFilter LeaveAppEmpPayrollFilter = new DBFilter(); LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter)); ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter); foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists) { ELeaveCode leaveCode = new ELeaveCode(); leaveCode.LeaveCodeID = leaveApp.LeaveCodeID; if (ELeaveCode.db.select(dbConn, leaveCode)) { if (leaveCode.LeaveCodePayRatio >= 1) { fullPaidLeaveDays += leaveApp.LeaveAppDays; } else { nonFullPaidLeaveDays += leaveApp.LeaveAppDays; } } } row[FIELD_FULLPAIDLEAVETOTAL] = fullPaidLeaveDays; row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays; dataTable.Rows.Add(row); } } } //DBFilter paymentCodeFilter = new DBFilter(); //paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false); //paymentCodeFilter.add("PaymentCode", false); //ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter); //foreach (EPaymentCode paymentCode in paymentCodeList) //{ // if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc)) // { // DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc]; // paymentColumn.SetOrdinal(firstDetailColumnPos); // if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc)) // paymentColumn.ColumnName = paymentCode.PaymentCodeDesc; // else // { // Console.Write("System reserved payment column is used"); // } // } //} //for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++) // dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1); export.Update(dataSet); System.IO.FileStream excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open); NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0); workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1); NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0); if (excelRow == null) { excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0); } NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0); if (excelCell == null) { excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0); } excelCell.SetCellValue("Statutory Minimum Wage Summary Report"); excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open); workbook.Write(excelfileStream); excelfileStream.Close(); WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); return; } else { PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.addError("Employee not selected"); } }
/// <summary> /// haza01的文件导入函数,从npoi的sheet转化成list /// </summary> /// <param name="HSSFSheet"></param> /// <returns></returns> public static CSException <List <TH_THAZA01> > BuildListByNPOISheet(NPOI.HSSF.UserModel.HSSFSheet HSSFSheet, string userid) { try { var risk_status_Dic = EP_TEPEP01.GetDicByCName("风险状态"); var rowcount = HSSFSheet.LastRowNum;//有一行是标题,但是还是+1,因为从0开始算。如果没有标题还是+1,因为这个不是统计总行,而是行数的最后一行的代码 var hazaList = new List <TH_THAZA01>(); for (var i = 1; i < rowcount + 1; i++) { try { string force_haza_lvl; var row = HSSFSheet.GetRow(i); if (row.Cells.Count() < 9) { break; } //读取sheet对应行所有数据 //一边读数据一边新建risk var newrisk = new TH_TRISK01("insert"); var haza_loca_deptCode = row.Cells[0].ToString().Trim(); if (haza_loca_deptCode == "END") { //根据要求,加了这个标记的行作为结束行 break; } var haza_loca_name = ""; var haza_name = ""; var risk_status_Cname = ""; try { newrisk.RISK_DEPT = haza_loca_deptCode; haza_loca_name = row.Cells[1].ToString().Trim(); haza_name = row.Cells[2].ToString().Trim(); risk_status_Cname = row.Cells[3].ToString(); newrisk.RISK_STATUS = risk_status_Dic.Where(x => x.Value == risk_status_Cname).First().Key; newrisk.RISK_DECONTENT = row.Cells[4].ToString().Trim(); newrisk.RISK_MOD = row.Cells[5].ToString().Trim(); newrisk.RISK_L = decimal.Parse(row.Cells[6].ToString().Trim()); newrisk.RISK_E = decimal.Parse(row.Cells[7].ToString().Trim()); newrisk.RISK_C = decimal.Parse(row.Cells[8].ToString().Trim()); newrisk.CalcLVL(); newrisk.RISK_SOL = row.Cells[9].ToString().Trim().Replace("、", ""); if (row.PhysicalNumberOfCells > 10) { force_haza_lvl = row.Cells[10].ToString().Trim(); } else { force_haza_lvl = ""; } } catch { throw new Exception("数据中有空白行,不符合规范"); } //看危险源区域是否已经录入 var locare = TH_HAZALOCA.FindLoca(haza_loca_deptCode, haza_loca_name); if (!locare.Flag) { throw new Exception("没有找到危险源区域"); } //危险源区域获得 var hazaloca = locare.Entity; var re_inList = TH_THAZA01.FindInList(hazaloca, haza_name, hazaList); var re_inDB = TH_THAZA01.FindInDB(hazaloca, haza_name); if (re_inDB.Flag && re_inList.Flag)//在数据库和表中同时找到两项,则优先加入临时程序表中 { var hazard = hazaList[(int)re_inList.StatusNum]; //直接添加一个风险进入 hazard.RISKLIST.Add(newrisk); hazard.ReCalcHAZA_LVL(hazard.RISKLIST); if (force_haza_lvl != "") { var lvldic = EP_TEPEP01.GetDicByCName("危险源等级"); hazard.HAZA_LVL = lvldic.Where(x => x.Value == force_haza_lvl).First().Key; } } else if (re_inDB.Flag && !re_inList.Flag)//在数据库中存在 { re_inDB.Entity.ReCalcHAZA_LVL(re_inDB.Entity.HAZA_RISK_String); re_inDB.Entity.RISKLIST.Add(newrisk); re_inDB.Entity.ReCalcHAZA_LVL(re_inDB.Entity.RISKLIST); if (force_haza_lvl != "") { var lvldic = EP_TEPEP01.GetDicByCName("危险源等级"); re_inDB.Entity.HAZA_LVL = lvldic.Where(x => x.Value == force_haza_lvl).First().Key; } hazaList.Add(re_inDB.Entity); } else if (!re_inDB.Flag && re_inList.Flag)//在列表中 { var hazard = hazaList[(int)re_inList.StatusNum]; //直接添加一个风险进入 hazard.RISKLIST.Add(newrisk); hazard.ReCalcHAZA_LVL(hazard.RISKLIST); if (force_haza_lvl != "") { var lvldic = EP_TEPEP01.GetDicByCName("危险源等级"); hazard.HAZA_LVL = lvldic.Where(x => x.Value == force_haza_lvl).First().Key; } } else if (!re_inDB.Flag && !re_inList.Flag)//该危险源在已有资源中不存在,是个完全新危险源 { //新建危险源 var newhaza = new TH_THAZA01("insert"); newhaza.HAZA_LOCA = hazaloca.ID; newhaza.REC_CREATOR = userid; newhaza.HAZA_DEPT = haza_loca_deptCode; newhaza.HAZA_NAME = haza_name; newhaza.RISKLIST = new List <TH_TRISK01>(); //马上添加一个新的风险 newhaza.RISKLIST.Add(newrisk); newhaza.ReCalcHAZA_LVL(newhaza.RISKLIST); if (force_haza_lvl != null && force_haza_lvl != "") { try { var lvldic = EP_TEPEP01.GetDicByCName("危险源等级"); newhaza.HAZA_LVL = lvldic.Where(x => x.Value == force_haza_lvl).First().Key; } catch { throw new Exception("强制危险源等级错误"); } } //新危险源添加入List hazaList.Add(newhaza); } } catch (Exception ex) { var cell = HSSFSheet.GetRow(i).Cells.Select(x => { return(x.ToString()); }); var jsoncell = JsonHelper.SerializeObject(cell); var errmsg = String.Format("至第{0}行录入成功,第{1}行录入失败 原因:{2},\n错误行信息:{3}", i, i + 1, ex.Message, jsoncell); return(new CSException <List <TH_THAZA01> >(false, errmsg, hazaList)); } } //返回加完的hazalist return(new CSException <List <TH_THAZA01> >(true, hazaList)); } catch (Exception ex) { return(new CSException <List <TH_THAZA01> >(false, ex.Message, new List <TH_THAZA01>())); } }