private void AddSubTotalRow(NPOI.HSSF.UserModel.HSSFSheet excelWorksheet, string currentCompany, int recordCount, int lastRowIndex, int lastColumnIndex, NPOI.HSSF.UserModel.HSSFRow headerRow, NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle) { //execlWorksheet.Cells[lastRowIndex, PAYMENTDETAIL_START_COLUMN - 1].Value = "Subtotal - " + currentCompany; NPOI.HSSF.UserModel.HSSFRow subTotalRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex); NPOI.HSSF.UserModel.HSSFCell subTotalCell = (NPOI.HSSF.UserModel.HSSFCell)subTotalRow.CreateCell(0); subTotalCell.SetCellValue("Subtotal - " + currentCompany); subTotalCell.CellStyle = subTotalStyle; excelWorksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(lastRowIndex, lastRowIndex, 0, PAYMENTDETAIL_START_COLUMN - 1)); for (int iCol = PAYMENTDETAIL_START_COLUMN; iCol <= lastColumnIndex; iCol++) { //double totalPayment = 0; //bool IsTotalPaymentNull = true; //for (int iRow = lastRowIndex - recordCount; iRow < lastRowIndex; iRow++) //{ // ExcelLibrary.SpreadSheet.Cell cell = execlWorksheet.Cells[iRow, iCol]; // if (cell.Value is double) // { // IsTotalPaymentNull = false; // totalPayment += (double)cell.Value; // } //} //if (!IsTotalPaymentNull) //{ // ExcelLibrary.SpreadSheet.Cell totalCell = execlWorksheet.Cells[lastRowIndex, iCol]; // totalCell.FormatString = "#,##0.00"; // totalCell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(totalPayment, 2, 2); //} if (headerRow.GetCell(iCol) != null) { subTotalCell = (NPOI.HSSF.UserModel.HSSFCell)subTotalRow.CreateCell(iCol); subTotalCell.CellFormula = "SUM(" + ToCellString(lastRowIndex - recordCount, iCol) + ":" + ToCellString(lastRowIndex - 1, iCol) + ")"; subTotalCell.CellStyle = subTotalStyle; } } }
private static void CopyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, bool keepFormulas) { List<SS.Util.CellRangeAddress> mergedRegions = destSheet.Sheet.MergedRecords.MergedRegions; destRow.Height = srcRow.Height; destRow.IsHidden = srcRow.IsHidden; destRow.RowRecord.OptionFlags = srcRow.RowRecord.OptionFlags; for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++) { HSSFCell oldCell = (HSSFCell)srcRow.GetCell(j); HSSFCell newCell = (HSSFCell)destRow.GetCell(j); if (srcSheet.Workbook == destSheet.Workbook) { newCell = (HSSFCell)destRow.GetCell(j); } if (oldCell != null) { if (newCell == null) { newCell = (HSSFCell)destRow.CreateCell(j); } HSSFCellUtil.CopyCell(oldCell, newCell, styleMap, paletteMap, keepFormulas); CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum, (short)oldCell.ColumnIndex); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow, mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn); if (IsNewMergedRegion(newMergedRegion, mergedRegions)) { mergedRegions.Add(newMergedRegion); } } } } }
/// <summary> /// 利用office组件导出Excel表格文件 /// </summary> //public static void ExportExcelFile(DataTable ExcelTable) //{ // try // { // string SaveExcelName = string.Empty;//保存的Excel文件名称 // SaveFileDialog SFDialog = new SaveFileDialog(); // SFDialog.DefaultExt = "xls"; // SFDialog.Filter = "Excel文件(*.xls)|*.xls"; // SFDialog.ShowDialog(); // SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称 // if (SaveExcelName.IndexOf(":") < 0) return; // Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序 // object missing = System.Reflection.Missing.Value; // if (XlsApp == null) // { // MessageBoxEx.Show("无法创建Excel表格文件,您的电脑可能未安装Excel软件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); // return; // } // else // { // Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像 // Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板 // Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1; // Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格 // int TotalCount = ExcelTable.Rows.Count; // //int rowRead = 0;//读取行数 // //float PercentRead = 0;//导入百分比 // //写入字段名 // for (int i = 0; i < ExcelTable.Columns.Count; i++) // { // WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称 // Ran = (Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1, i + 1];//列名称写入单元格 // Ran.Interior.ColorIndex = 15; // Ran.Font.Bold = true;//标题加粗 // } // //ProgressBarMsg ProgBarMsg = new ProgressBarMsg(); // //ProgBarMsg.MaxNum = TotalCount;//获取总记录行项 // //ProgBarMsg.ShowDialog();//显示进度条 // //写字段值 // for (int j = 0; j < ExcelTable.Rows.Count; j++) // { // for (int k = 0; k < ExcelTable.Columns.Count; k++) // { // WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值 // } // //rowRead++; // //PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比 // //ProgressBarMsg.PercentMsg = rowRead; // //Thread.Sleep(200); // Application.DoEvents();//处理当前在消息队列中所有windows消息 // } // WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing, missing); // Ran = WkSheet.get_Range((object)WkSheet.Cells[2, 1], (object)WkSheet.Cells[ExcelTable.Rows.Count + 1, ExcelTable.Columns.Count]);//给工作表指定区域 // //设置Excel表格边框样式 // Ran.BorderAround2(missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, // Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, missing, missing); // Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色 // Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框 // Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度 // if (ExcelTable.Columns.Count > 1) // {//设置垂直表格颜色索引 // Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic; // } // WkBk.Save();//保存Excel表数据 // //关闭表格对像,并退出应用程序域 // WkBk.Close(missing, missing, missing); // XlsApp.Quit(); // XlsApp = null; // GC.Collect();//强制关闭 // MessageBoxEx.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); // } // } // catch (Exception ex) // { // MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); // } //} /// <summary> /// 设置导出Excel标题样式 /// </summary> /// <param name="HeaderRow">标题行</param> /// <param name="WkBk">工作表</param> /// <param name="ColNum">列序号</param> private static void SetXlsHeaderStyle(HSSFRow HeaderRow, HSSFWorkbook WkBk, int ColNum) { try { HSSFCellStyle HeaderStyle = (HSSFCellStyle)WkBk.CreateCellStyle();//创建Excel表格标题样式 HSSFFont fontStyle = (HSSFFont)WkBk.CreateFont();//创建Excel表格字体样式 HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//设置居中 HeaderStyle.FillForegroundColor = HSSFColor.Lime.Index;//设置背景色 HeaderStyle.FillPattern = FillPattern.SolidForeground;//设置填充样式 HeaderStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0");//设置数据格式 fontStyle.FontHeightInPoints = 11; fontStyle.Boldweight = 700; HeaderStyle.SetFont(fontStyle); HeaderRow.GetCell(ColNum).CellStyle = (ICellStyle)HeaderStyle;//设置列样式 } catch (Exception ex) { MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); } }
/// <summary> /// Get a specific cell from a row. If the cell doesn't exist, /// </summary> /// <param name="row">The row that the cell is part of</param> /// <param name="column">The column index that the cell is in.</param> /// <returns>The cell indicated by the column.</returns> public static HSSFCell GetCell(HSSFRow row, int column) { HSSFCell cell = row.GetCell(column); if (cell == null) { cell = row.CreateCell(column); } return cell; }
/// <summary> /// 插入Excel行 /// </summary> /// <param name="sheet"></param> /// <param name="rowIndex"></param> /// <param name="count"></param> /// <param name="row"></param> private static void MyInsertRow(HSSFSheet sheet, int rowIndex, int count, HSSFRow row) { #region 批量移动行 sheet.ShiftRows( rowIndex, //--开始行 sheet .LastRowNum, //--结束行 count, //--移动大小(行数)--往下移动 true, //是否复制行高 false, //是否重置行高 true //是否移动批注 ); #endregion #region 对批量移动后空出的空行插,创建相应的行,并以插入行的上一行为格式源(即:插入行-1的那一行) for (int i = rowIndex; i < rowIndex + count - 1; i++) { HSSFRow targetRow; HSSFCell sourceCell; HSSFCell targetCell; targetRow = sheet.CreateRow(i + 1); for (int m = row.FirstCellNum; m < row.LastCellNum; m++) { sourceCell = row.GetCell(m); if (sourceCell == null) continue; targetCell = targetRow.CreateCell(m); targetCell.Encoding = sourceCell.Encoding; targetCell.CellStyle = sourceCell.CellStyle; targetCell.SetCellType(sourceCell.CellType); } //CopyRow(sourceRow, targetRow); //Util.CopyRow(sheet, sourceRow, targetRow); } HSSFRow firstTargetRow = sheet.GetRow(rowIndex); HSSFCell firstSourceCell; HSSFCell firstTargetCell = null; for (int m = row.FirstCellNum; m < row.LastCellNum; m++) { firstSourceCell = row.GetCell(m); if (firstSourceCell == null) continue; firstTargetCell = firstTargetRow.CreateCell(m); firstTargetCell.Encoding = firstSourceCell.Encoding; firstTargetCell.CellStyle = firstSourceCell.CellStyle; firstTargetCell.SetCellType(firstSourceCell.CellType); } sheet.AddMergedRegion(new Region(firstTargetRow.RowNum, 3, firstTargetRow.RowNum, 4)); #endregion }
/** * @return maximum 1-base index of column that were rendered, zero if none */ protected int ProcessRow(CellRangeAddress[][] mergedRanges, HSSFRow row, XmlElement tableRowElement) { HSSFSheet sheet = (HSSFSheet)row.Sheet; int maxColIx = row.LastCellNum; if (maxColIx <= 0) return 0; List<XmlElement> emptyCells = new List<XmlElement>(maxColIx); if (OutputRowNumbers) { XmlElement tableRowNumberCellElement = htmlDocumentFacade.CreateTableHeaderCell(); ProcessRowNumber(row, tableRowNumberCellElement); emptyCells.Add(tableRowNumberCellElement); } int maxRenderedColumn = 0; for (int colIx = 0; colIx < maxColIx; colIx++) { if (!OutputHiddenColumns && sheet.IsColumnHidden(colIx)) continue; CellRangeAddress range = ExcelToHtmlUtils.GetMergedRange( mergedRanges, row.RowNum, colIx); if (range != null && (range.FirstColumn != colIx || range.FirstRow != row.RowNum)) continue; HSSFCell cell = (HSSFCell)row.GetCell(colIx); int divWidthPx = 0; if (UseDivsToSpan) { divWidthPx = GetColumnWidth(sheet, colIx); bool hasBreaks = false; for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++) { if (!OutputHiddenColumns && sheet.IsColumnHidden(nextColumnIndex)) continue; if (row.GetCell(nextColumnIndex) != null && !IsTextEmpty((HSSFCell)row.GetCell(nextColumnIndex))) { hasBreaks = true; break; } divWidthPx += GetColumnWidth(sheet, nextColumnIndex); } if (!hasBreaks) divWidthPx = int.MaxValue; } XmlElement tableCellElement = htmlDocumentFacade.CreateTableCell(); if (range != null) { if (range.FirstColumn != range.LastColumn) tableCellElement.SetAttribute("colspan", (range.LastColumn - range.FirstColumn + 1).ToString()); if (range.FirstRow != range.LastRow) tableCellElement.SetAttribute("rowspan", (range.LastRow - range.FirstRow + 1).ToString()); } bool emptyCell; if (cell != null) { emptyCell = ProcessCell(cell, tableCellElement, GetColumnWidth(sheet, colIx), divWidthPx, row.Height / 20f); } else { emptyCell = true; } if (emptyCell) { emptyCells.Add(tableCellElement); } else { foreach (XmlElement emptyCellElement in emptyCells) { tableRowElement.AppendChild(emptyCellElement); } emptyCells.Clear(); tableRowElement.AppendChild(tableCellElement); maxRenderedColumn = colIx; } } return maxRenderedColumn + 1; }
private void SetRowStringValue(HSSFRow row, string column, string value) { ((HSSFCell)row.GetCell(Convert.ToInt32(column))).SetCellValue(value); }
private void SetRowNumericValue(HSSFRow row, string column, double value) { ((HSSFCell)row.GetCell(Convert.ToInt32(column))).SetCellValue(value); }
private void SetRowFormula(HSSFRow row, string column, string formula) { row.GetCell(Convert.ToInt32(column)).CellFormula = formula; }
/// <summary>填充内容</summary> static void FillContent(HSSFRow hssfRow , DataColumn column, DataRow row , HSSFCellStyle contentStyle, HSSFFont contentFont , HSSFCellStyle contentDateStyle , HSSFCellStyle contentStyleDailyBalance, HSSFFont fontDailyBalance , string colHeadFont, short colHeadSize, short colHeadColor , short contentColor, bool contentBorder , string contentChangeColorFrom, short contentChangeColor , HSSFSheet sheet, string headTextStrA) { HSSFCell newCell = hssfRow.CreateCell(column.Ordinal) as HSSFCell; #region 当数据访问转换DataTime时生效 HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; contentDateStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); #endregion string columnF = row[column].ToString(); decimal i; bool b = decimal.TryParse(columnF, out i); #region 判断如果是仓库库存日结核对 if (contentChangeColorFrom == "DailyBalance" && sheet == workbook.GetSheet(headTextStrA) && ((column.Ordinal == 5 && b == false) || (column.Ordinal == 6 && b == false) || (column.Ordinal == 7 && b == false) || (column.Ordinal == 8 && b == false) || (column.Ordinal == 9 && b == false) || (column.Ordinal == 10 && b == false))) { fontDailyBalance.FontName = colHeadFont; fontDailyBalance.FontHeightInPoints = colHeadSize; fontDailyBalance.Color = contentChangeColor; contentStyleDailyBalance.SetFont(fontDailyBalance); if (contentBorder == true) { contentStyleDailyBalance.BorderBottom = BorderStyle.THIN; contentStyleDailyBalance.BorderLeft = BorderStyle.THIN; contentStyleDailyBalance.BorderRight = BorderStyle.THIN; contentStyleDailyBalance.BorderTop = BorderStyle.THIN; } hssfRow.GetCell(column.Ordinal).CellStyle = contentStyleDailyBalance; } #endregion else { contentFont.FontName = colHeadFont; contentFont.FontHeightInPoints = colHeadSize; contentFont.Color = contentColor; contentStyle.SetFont(contentFont); //画边框 if (contentBorder == true) { contentStyle.BorderBottom = BorderStyle.THIN; contentStyle.BorderLeft = BorderStyle.THIN; contentStyle.BorderRight = BorderStyle.THIN; contentStyle.BorderTop = BorderStyle.THIN; } hssfRow.GetCell(column.Ordinal).CellStyle = contentStyle; } string drValue = row[column].ToString(); ChangeFormat(column,drValue,newCell,contentDateStyle); }
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"); } }
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(); }
public static bool CheckValidDataRow(HSSFRow row, int startColIndex, int endColIndex) { for (int i = startColIndex; i < endColIndex; i++) { ICell cell = row.GetCell(i); if (cell != null && cell.CellType != NPOI.SS.UserModel.CellType.BLANK) { return true; } } return false; }