public override ReportDocument GenerateReport() { DataSet.Payroll_KTPFundStatement dataSet = CreateDataSource(); if (reportDocument == null) { reportDocument = new ReportTemplate.Report_Payroll_KTPFundStatement(); } reportDocument.SetDataSource(dataSet); if (_payPeriodFr.Month != _payPeriodTo.Month) { reportDocument.SetParameterValue("ContributionPeriod", _payPeriodFr.ToString("dd MMMM yyyy") + " - " + _payPeriodTo.ToString("dd MMMM yyyy")); } else { reportDocument.SetParameterValue("ContributionPeriod", _payPeriodFr.ToString("MMMM yyyy")); } return(reportDocument); }
protected DataSet.Payroll_KTPFundStatement CreateDataSource() { DataSet.Payroll_KTPFundStatement dataSet = new DataSet.Payroll_KTPFundStatement(); Payroll_KTPFundStatement.ExistingMemberDataTable existingORSO = dataSet.ExistingMember; Payroll_KTPFundStatement.ORSOPlanDataTable orsoPlan = dataSet.ORSOPlan; if (_payPeriodFr.Ticks != 0 && _payPeriodTo.Ticks != 0 && EmpList != null) { string strPrintPeriod = _payPeriodFr.ToString("yyyy-MM-dd") + " - " + _payPeriodTo.ToString("yyyy-MM-dd"); DBFilter payPeriodFilter = new DBFilter(); payPeriodFilter.add(new Match("pp.PayPeriodFr", "<=", _payPeriodTo)); payPeriodFilter.add(new Match("pp.PayPeriodTo", ">=", _payPeriodFr)); foreach (EEmpPersonalInfo empInfo in EmpList) { EEmpPersonalInfo.db.select(dbConn, empInfo); DBFilter empPayrollFilter = new DBFilter(); empPayrollFilter.add(new Match("EmpID", empInfo.EmpID)); empPayrollFilter.add(new IN("PayPeriodID", "Select PayPeriodID from PayrollPeriod pp", payPeriodFilter)); DBFilter orsoRecordFilter = new DBFilter(); orsoRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter)); orsoRecordFilter.add(new Match("ORSOPlanID", ORSOPlanID)); orsoRecordFilter.add("ORSORecPeriodFr", true); orsoRecordFilter.add("ORSORecPeriodTo", true); ArrayList orsoRecords = EORSORecord.db.select(dbConn, orsoRecordFilter); DataSet.Payroll_KTPFundStatement.ExistingMemberRow existingORSORow = null; foreach (EORSORecord orsoRecord in orsoRecords) { EORSOPlan orsoPlanObject = new EORSOPlan(); orsoPlanObject.ORSOPlanID = orsoRecord.ORSOPlanID; if (EORSOPlan.db.select(dbConn, orsoPlanObject)) { if (orsoPlan.Select("ORSOPlanID=" + orsoPlanObject.ORSOPlanID).Length == 0) { DataSet.Payroll_KTPFundStatement.ORSOPlanRow orsoPlanRow = orsoPlan.NewORSOPlanRow(); orsoPlanRow.ORSOPlanID = orsoPlanObject.ORSOPlanID; orsoPlanRow.ORSOPlanCode = orsoPlanObject.ORSOPlanCode; orsoPlanRow.ORSOPlanCompanyName = orsoPlanObject.ORSOPlanCompanyName; orsoPlanRow.ORSOPlanPayCenter = orsoPlanObject.ORSOPlanPayCenter; orsoPlanRow.ORSOPlanSchemeNo = orsoPlanObject.ORSOPlanSchemeNo; orsoPlanRow.ORSOPlanDesc = orsoPlanObject.ORSOPlanDesc; orsoPlan.Rows.Add(orsoPlanRow); } } EEmpPayroll empPayroll = new EEmpPayroll(); empPayroll.EmpPayrollID = orsoRecord.EmpPayrollID; EEmpPayroll.db.select(dbConn, empPayroll); EPayrollPeriod payrollPeriod = new EPayrollPeriod(); payrollPeriod.PayPeriodID = empPayroll.PayPeriodID; EPayrollPeriod.db.select(dbConn, payrollPeriod); { if (existingORSORow == null) { existingORSORow = existingORSO.NewExistingMemberRow(); LoadExistingMemberRowInfo(empInfo, orsoRecord, existingORSORow); } else { if (!(existingORSORow.EmpID == empInfo.EmpID && existingORSORow.PeriodFrom.Equals(orsoRecord.ORSORecPeriodFr) && existingORSORow.PeriodTo.Equals(orsoRecord.ORSORecPeriodTo))) { existingORSO.Rows.Add(existingORSORow); existingORSORow = existingORSO.NewExistingMemberRow(); LoadExistingMemberRowInfo(empInfo, orsoRecord, existingORSORow); } } existingORSORow.RelevantIncome += orsoRecord.ORSORecActRI; existingORSORow.EE += orsoRecord.ORSORecActEE; existingORSORow.ER += orsoRecord.ORSORecActER; DBFilter m_mpfRecordFilter = new DBFilter(); m_mpfRecordFilter.add(new Match("EmpPayrollID", orsoRecord.EmpPayrollID)); foreach (EMPFRecord m_mpfRecord in EMPFRecord.db.select(dbConn, m_mpfRecordFilter)) { existingORSORow.MpfMCEE += m_mpfRecord.MPFRecActMCEE; existingORSORow.MpfMCER += m_mpfRecord.MPFRecActMCER; existingORSORow.MpfVCEE += m_mpfRecord.MPFRecActVCEE; existingORSORow.MpfVCER += m_mpfRecord.MPFRecActVCER; } } } if (existingORSORow != null) { // business requirement from user, "Basic Salary over $100,000 should be shown $100,000 only if (existingORSORow.RelevantIncome > 100000) { existingORSORow.RelevantIncome = 100000; } existingORSO.Rows.Add(existingORSORow); } } } return(dataSet); }
public void GenerateExcelReport(string exportFileName) { DataSet.Payroll_KTPFundStatement dataSet = CreateDataSource(); int lastRowIndex = 0; // Set column style NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("KTPF Contribution Report"); // Date format NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat(); NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; dateCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; dateCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; dateCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Numeric format NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; numericStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; numericStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; numericStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // String left format NPOI.HSSF.UserModel.HSSFCellStyle stringLeftStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); stringLeftStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; stringLeftStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; stringLeftStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; stringLeftStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // String center format NPOI.HSSF.UserModel.HSSFCellStyle stringCenterStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); stringCenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; stringCenterStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; stringCenterStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; stringCenterStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 0 style NPOI.HSSF.UserModel.HSSFCellStyle column0Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column0Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; column0Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; column0Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column0Style.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 10, 11 style NPOI.HSSF.UserModel.HSSFCellStyle column10To11Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column10To11Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column10To11Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column10To11Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 4 style NPOI.HSSF.UserModel.HSSFCellStyle column4Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column4Style.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); column4Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column4Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column4Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 8 style NPOI.HSSF.UserModel.HSSFCellStyle column8Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column8Style.DataFormat = format.GetFormat("yyyy-MM-dd"); column8Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; column8Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column8Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column8Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Align right NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; // Bottom border NPOI.HSSF.UserModel.HSSFCellStyle bottomBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); bottomBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; // Bold style NPOI.HSSF.UserModel.HSSFCellStyle boldStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); NPOI.SS.UserModel.IFont boldFont = workbook.CreateFont(); boldFont.Boldweight = 700; boldStyle.SetFont(boldFont); // Header Border NPOI.HSSF.UserModel.HSSFCellStyle headerBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; headerBorderStyle.SetFont(boldFont); headerBorderStyle.WrapText = true; headerBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Header Left Border NPOI.HSSF.UserModel.HSSFCellStyle headerLeftBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerLeftBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; headerLeftBorderStyle.SetFont(boldFont); headerLeftBorderStyle.WrapText = true; headerLeftBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Header Right Border NPOI.HSSF.UserModel.HSSFCellStyle headerRightBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerRightBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.SetFont(boldFont); headerRightBorderStyle.WrapText = true; headerRightBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Total numeric format NPOI.HSSF.UserModel.HSSFCellStyle totalNumericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); totalNumericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); totalNumericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; totalNumericStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; // Grey color NPOI.HSSF.UserModel.HSSFCellStyle grey25Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); grey25Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; grey25Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; grey25Style.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; grey25Style.SetFont(boldFont); // Yellow color NPOI.HSSF.UserModel.HSSFCellStyle yellowStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); yellowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; yellowStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index; yellowStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; yellowStyle.SetFont(boldFont); // Set column width worksheet.SetColumnWidth(0, 12 * 256); worksheet.SetColumnWidth(1, 20 * 256); worksheet.SetColumnWidth(2, 15 * 256); worksheet.SetColumnWidth(3, 15 * 256); worksheet.SetColumnWidth(4, 15 * 256); worksheet.SetColumnWidth(5, 7 * 256); worksheet.SetColumnWidth(6, 15 * 256); worksheet.SetColumnWidth(7, 15 * 256); worksheet.SetColumnWidth(8, 15 * 256); worksheet.SetColumnWidth(9, 15 * 256); worksheet.SetColumnWidth(10, 15 * 256); worksheet.SetColumnWidth(11, 25 * 256); // Set column title NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0); NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue("KTPF Contribution Report"); headerCell.CellStyle = boldStyle; headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(1); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); if (dataSet.ORSOPlan.Rows.Count > 0) { Payroll_KTPFundStatement.ORSOPlanRow m_orsoPlan = (Payroll_KTPFundStatement.ORSOPlanRow)dataSet.ORSOPlan.Rows[0]; headerCell.SetCellValue(m_orsoPlan.ORSOPlanCompanyName); } headerCell.CellStyle = boldStyle; string m_reportPeriod = ""; if (_payPeriodFr.Month != _payPeriodTo.Month) { m_reportPeriod = _payPeriodFr.ToString("dd MMMM yyyy") + " - " + _payPeriodTo.ToString("dd MMMM yyyy"); } else { m_reportPeriod = _payPeriodFr.ToString("MMMM yyyy"); } headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(2); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue(m_reportPeriod); headerCell.CellStyle = boldStyle; // Merge header headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(3); // Merge cell from 5-8 NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 5, (short)8); worksheet.AddMergedRegion(cellRangeAddress); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5); headerCell.SetCellValue("For New Joiner"); headerCell.CellStyle = yellowStyle; ((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); // Merge cell from 9-10 cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 9, (short)10); worksheet.AddMergedRegion(cellRangeAddress); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9); headerCell.SetCellValue("For Resigned Staff"); headerCell.CellStyle = grey25Style; ((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(4); headerRow.HeightInPoints = 40; // column A headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue("Member ID"); headerCell.CellStyle = headerLeftBorderStyle; // column B headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(1); headerCell.SetCellValue("Employee Name"); headerCell.CellStyle = headerBorderStyle; // column C headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(2); headerCell.SetCellValue("Basic Salary"); headerCell.CellStyle = headerBorderStyle; // column D headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(3); headerCell.SetCellValue("KTPF Contribution"); headerCell.CellStyle = headerBorderStyle; // column E headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(4); headerCell.SetCellValue("Employer MPF Contribution"); headerCell.CellStyle = headerRightBorderStyle; // column F headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5); headerCell.SetCellValue("Sex"); headerCell.CellStyle = headerBorderStyle; // column G headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(6); headerCell.SetCellValue("Date Of Birth"); headerCell.CellStyle = headerBorderStyle; // column H headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(7); headerCell.SetCellValue("Date Join"); headerCell.CellStyle = headerBorderStyle; // column I headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(8); headerCell.SetCellValue("Effective Date"); headerCell.CellStyle = headerRightBorderStyle; // column J headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9); headerCell.SetCellValue("Termination Date"); headerCell.CellStyle = headerBorderStyle; // column K headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(10); headerCell.SetCellValue("Termination Mode"); headerCell.CellStyle = headerRightBorderStyle; // column L headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(11); headerCell.SetCellValue("Remarks"); headerCell.CellStyle = headerRightBorderStyle; // Create total int length = dataSet.ExistingMember.Rows.Count + 5; NPOI.HSSF.UserModel.HSSFRow totalRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + length); NPOI.HSSF.UserModel.HSSFCell totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(0); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(5); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(6); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(7); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(8); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(9); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(10); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(11); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(1); totalCell.SetCellValue("Total"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(2); totalCell.SetCellFormula("SUM(C5:C" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(3); totalCell.SetCellFormula("SUM(D5:D" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(4); totalCell.SetCellFormula("SUM(E5:E" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; int rowLength = 0; // Set value for every row dataSet.ExistingMember.DefaultView.Sort = "EmpName"; DataTable m_table = dataSet.ExistingMember.DefaultView.ToTable(); foreach (DataRow m_row in m_table.Rows) //foreach (DataRow row in tmpDataTable.Rows) { NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 5); rowLength++; if (lastRowIndex == (m_table.Rows.Count)) { detailRow.RowStyle = bottomBorderStyle; } NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(0); cell.SetCellValue(m_row["MemberID"].ToString()); cell.CellStyle = column0Style; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(1); cell.SetCellValue(m_row["EmpName"].ToString()); cell.CellStyle = stringLeftStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(2); cell.SetCellValue((double)m_row["RelevantIncome"]); cell.CellStyle = numericStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(3); cell.SetCellValue((double)m_row["ER"]); cell.CellStyle = numericStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(4); cell.SetCellValue((double)m_row["MpfMCER"]); cell.CellStyle = column4Style; DateTime m_periodFrom = (DateTime)m_row["PeriodFrom"]; DateTime m_effDate = (DateTime)m_row["OrsoEffDate"]; { cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(5); cell.CellStyle = stringCenterStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { cell.SetCellValue(m_row["EmpSex"].ToString()); } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(6); cell.CellStyle = dateCellStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["EmpDOB"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(7); cell.CellStyle = dateCellStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["EmpDateJoin"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8); cell.CellStyle = column8Style; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["OrsoEffDate"]); } catch { } } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9); cell.CellStyle = dateCellStyle; cell.SetCellValue("");// put something into the cell and so as the border line can be shown if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["LastEmploymentDate"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(10); cell.SetCellValue(m_row["TermCode"].ToString()); cell.CellStyle = column10To11Style; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11); //cell.SetCellValue(row[FIELD_REMARKS].ToString()); cell.CellStyle = column10To11Style; lastRowIndex++; } System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workbook.Write(file); file.Close(); }