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(); }
//private ExcelLibrary.SpreadSheet.Worksheet CreateWorkSheet(DataTable dataTable) private NPOI.HSSF.UserModel.HSSFSheet CreateWorkSheet(DataTable dataTable) { if (workbook != null) { 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"); NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); numericCellStyle.DataFormat = format.GetFormat("0.00##"); NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(dataTable.TableName.Replace("$", "")); //ExcelLibrary.SpreadSheet.Row headerRow = new ExcelLibrary.SpreadSheet.Row(); NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0); int columnCount = 0; foreach (DataColumn headercolumn in dataTable.Columns) { headercolumn.ColumnName = headercolumn.ColumnName.Trim(); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty)); cell.SetCellValue(headercolumn.ColumnName); //headerRow.SetCell(columnCount,cell); //worksheet.Cells[0, columnCount] = cell;//new ExcelLibrary.SpreadSheet.Cell(column.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty)); columnCount++; } //worksheet.Cells.Rows.Add(0, headerRow); int rowCount = 0; //NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); //numericCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); ; //NPOI.HSSF.UserModel.HSSFCellStyle integerCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); //integerCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0"); ; foreach (DataRow row in dataTable.Rows) { rowCount++; columnCount = 0; // ExcelLibrary.SpreadSheet.Row detailRow = new ExcelLibrary.SpreadSheet.Row(); NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount); foreach (DataColumn column in dataTable.Columns) { //ExcelLibrary.SpreadSheet.Cell cell =new ExcelLibrary.SpreadSheet.Cell(string.Empty, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty)); NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(columnCount); if (column.DataType.Equals(typeof(string))) { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty); //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString()); cell.SetCellValue(row[column] == System.DBNull.Value ? string.Empty : row[column].ToString()); } else if (column.DataType.Equals(typeof(double)) || column.DataType.Equals(typeof(float))) { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00"); //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]); if (row[column] != System.DBNull.Value) { double value = Convert.ToDouble(row[column].ToString()); if (value.Equals(double.NaN)) { cell.SetCellValue(string.Empty); } else { cell.SetCellValue(value); } } cell.CellStyle = numericCellStyle; } else if (column.DataType.Equals(typeof(int))) { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00"); //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]); if (row[column] != System.DBNull.Value) { cell.SetCellValue(Convert.ToDouble(row[column].ToString())); } //cell.CellStyle = integerCellStyle; } else if (column.DataType.Equals(typeof(DateTime))) { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.DateTime, "yyyy-MM-dd"); //if (row[column] == System.DBNull.Value) // cell.Value = string.Empty; //else // cell.Value = (DateTime)row[column]; if (row[column] != System.DBNull.Value) { cell.SetCellValue((DateTime)row[column]); } cell.CellStyle = dateCellStyle; } else { //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty); //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString()); if (row[column] != System.DBNull.Value) { cell.SetCellValue(row[column].ToString()); } } //worksheet.Cells[rowCount, columnCount] = cell; columnCount++; } // worksheet.Cells.Rows.Add(rowCount, detailRow); } //workbook.Worksheets.Add(worksheet); return(worksheet); } else { return(null); } }
public void WriteToSpreadsheet(DataTable _dataTable, NPOI.HSSF.UserModel.HSSFSheet _worksheet, string _schemeNo, string _companyName, int _billingClass) { if (_worksheet != null) { NPOI.HSSF.UserModel.HSSFDataFormat _format = (NPOI.HSSF.UserModel.HSSFDataFormat)_worksheet.Workbook.CreateDataFormat(); // font setting(bold) NPOI.HSSF.UserModel.HSSFFont _boldFont = (NPOI.HSSF.UserModel.HSSFFont)_worksheet.Workbook.CreateFont(); _boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; //900; // font setting(underline) NPOI.HSSF.UserModel.HSSFFont _sectionHeaderFont = (NPOI.HSSF.UserModel.HSSFFont)_worksheet.Workbook.CreateFont(); _sectionHeaderFont.Underline = (byte)1; _sectionHeaderFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; //900; // style setting NPOI.HSSF.UserModel.HSSFCellStyle _sectionHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _sectionHeaderStyle.SetFont(_sectionHeaderFont); NPOI.HSSF.UserModel.HSSFCellStyle _dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _dateCellStyle.DataFormat = _format.GetFormat("yyyy-MM-dd"); _dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFCellStyle _doubleCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _doubleCellStyle.DataFormat = _format.GetFormat("#,##0.00"); _doubleCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; NPOI.HSSF.UserModel.HSSFCellStyle _boldTextCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _boldTextCellStyle.SetFont(_boldFont); //NPOI.HSSF.UserModel.HSSFCellStyle _underlineTextCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); //_underlineTextCellStyle.SetFont(_underlineFont); // bottom line style NPOI.HSSF.UserModel.HSSFCellStyle _boldTextWithBottomBorderCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _boldTextWithBottomBorderCellStyle.SetFont(_boldFont); _boldTextWithBottomBorderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; _boldTextWithBottomBorderCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFCellStyle _bottomBorderCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _bottomBorderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle _chequeAmountCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _chequeAmountCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; _chequeAmountCellStyle.DataFormat = _format.GetFormat("$#,##0.00"); // header style NPOI.HSSF.UserModel.HSSFCellStyle _leftHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _leftHeaderStyle.SetFont(_boldFont); _leftHeaderStyle.WrapText = true; NPOI.HSSF.UserModel.HSSFCellStyle _rightHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _rightHeaderStyle.SetFont(_boldFont); _rightHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; _rightHeaderStyle.WrapText = true; NPOI.HSSF.UserModel.HSSFCellStyle _centerHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)_worksheet.Workbook.CreateCellStyle(); _centerHeaderStyle.SetFont(_boldFont); _centerHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; _centerHeaderStyle.WrapText = true; // START !!! NPOI.HSSF.UserModel.HSSFRow _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(0); NPOI.HSSF.UserModel.HSSFCell _cell = (NPOI.HSSF.UserModel.HSSFCell)_row.CreateCell(0); WriteCellText(_row, 0, "Scheme No."); WriteCellText(_row, 1, _schemeNo); _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(1); WriteCellText(_row, 0, "Billing Class"); WriteCellText(_row, 1, _billingClass.ToString("00")); _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(2); WriteCellText(_row, 0, "Co. Name"); WriteCellText(_row, 1, _companyName.ToUpper()); _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(4); switch (_worksheet.SheetName) { case "NewJoinMember": WriteCellText(_row, 0, "Remittance Statement for NEW EMPLOYEES", _sectionHeaderStyle); break; case "ExistingEmployee": WriteCellText(_row, 0, "Remittance Statement for EXISTING EMPLOYEES", _sectionHeaderStyle); break; case "TerminatedEmployee": WriteCellText(_row, 0, "Remittance Statement for TERMINATED EMPLOYEES", _sectionHeaderStyle); break; default: break; } // column header _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(6); WriteCellText(_row, 0, "Member Name", _leftHeaderStyle); WriteCellText(_row, 1, "Cert No.", _leftHeaderStyle); WriteCellText(_row, 2, "HKID no.", _leftHeaderStyle); WriteCellText(_row, 3, "Job Category", _leftHeaderStyle); WriteCellText(_row, 4, "Date of Employment\n(dd/mm/yyyy)", _centerHeaderStyle); WriteCellText(_row, 5, "Payroll Period\n(dd/mm/yyyy)", _centerHeaderStyle); WriteCellText(_row, 7, "Relevant Income", _rightHeaderStyle); WriteCellText(_row, 8, "Employee Mandatory Contribution", _rightHeaderStyle); WriteCellText(_row, 9, "Employer Mandatory Contribution", _rightHeaderStyle); WriteCellText(_row, 10, "Employee Voluntary Contribution", _rightHeaderStyle); WriteCellText(_row, 11, "Employer Voluntary Contribution", _rightHeaderStyle); WriteCellText(_row, 12, "Surcharge", _rightHeaderStyle); WriteCellText(_row, 13, "Last Date of Employment\n(dd/mm/yyyy)", _centerHeaderStyle); WriteCellText(_row, 14, "LSP/ SP Entitlement#\n(Y/N)", _leftHeaderStyle); WriteCellText(_row, 15, "Remarks^\n(Code: 1/2/3/4/5/6/7)", _leftHeaderStyle); // column header 2 _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(7); WriteCellText(_row, 0, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 1, ".", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 2, ".", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 3, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 4, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 5, "From", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 6, "To", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 7, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 8, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 9, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 10, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 11, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 12, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 13, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 14, "", _boldTextWithBottomBorderCellStyle); WriteCellText(_row, 15, "", _boldTextWithBottomBorderCellStyle); // merge column header NPOI.SS.Util.CellRangeAddress _cellRangeAddress; for (int i = 0; i < 16; i++) { if (i == 5) { _cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(6, 6, 5, 6); // merge "Payroll Period" } else if (i == 6) { continue; } else { _cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(6, 7, i, i); } _worksheet.AddMergedRegion(_cellRangeAddress); } // generate data int _currentRow = 8; int _dataStartRow = 8; int _dataEndRow = 0; foreach (DataRow _dataRow in _dataTable.Rows) { _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(_currentRow); WriteCellText(_row, 0, _dataRow[FIELD_MEMBER_NAME].ToString()); WriteCellText(_row, 1, _dataRow[FIELD_CERT_NO].ToString()); WriteCellText(_row, 2, _dataRow[FIELD_HKID].ToString()); WriteCellText(_row, 3, _dataRow[FIELD_JOB_CATEGORY].ToString()); WriteCellText(_row, 4, ((DateTime)_dataRow[FIELD_EMPLOYMENT_DATE]), _dateCellStyle); WriteCellText(_row, 5, ((DateTime)_dataRow[FIELD_FROM]), _dateCellStyle); WriteCellText(_row, 6, ((DateTime)_dataRow[FIELD_TO]), _dateCellStyle); WriteCellText(_row, 7, (double)_dataRow[FIELD_RELEVANT_INCOME], _doubleCellStyle); WriteCellText(_row, 8, (double)_dataRow[FIELD_MCEE], _doubleCellStyle); WriteCellText(_row, 9, (double)_dataRow[FIELD_MCER], _doubleCellStyle); WriteCellText(_row, 10, (double)_dataRow[FIELD_VCEE], _doubleCellStyle); WriteCellText(_row, 11, (double)_dataRow[FIELD_VCER], _doubleCellStyle); WriteCellText(_row, 12, (double)_dataRow[FIELD_SURCHARGE], _doubleCellStyle); if (_dataRow[FIELD_LAST_DATE_OF_EMPLOYMENT] != DBNull.Value) { WriteCellText(_row, 13, (double)_dataRow[FIELD_LAST_DATE_OF_EMPLOYMENT], _dateCellStyle); } WriteCellText(_row, 14, _dataRow[FIELD_LSP_SP_ENTITLMENT].ToString()); WriteCellText(_row, 15, _dataRow[FIELD_REMARKS].ToString()); _currentRow++; } _dataEndRow = _currentRow - 1; _currentRow++; _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(_currentRow); WriteCellText(_row, 0, "Note:"); _currentRow++; _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(_currentRow); WriteCellText(_row, 0, "# Please indicate Yes/ No if reported Last Date of Employment for the member. If Yes, Notice of Termination form for the reimbursement of LSP/ SP will be provided"); _currentRow++; _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(_currentRow); WriteCellText(_row, 0, "^ 1. Rejoin 2. Intra-group transfer 3. back-payment for terminated member 4. Over age 65 5. Overseas member 6.New in Billing Class 02 7. Work < 60 days"); _currentRow += 2; _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(_currentRow); WriteCellText(_row, 0, "Cheque No."); WriteCellText(_row, 1, ChequeNo, _bottomBorderCellStyle); WriteCellText(_row, 3, "Cheque Amount"); // write Cheque Amount //_row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(_currentRow); _cell = (NPOI.HSSF.UserModel.HSSFCell)_row.CreateCell(4); _cell.CellStyle = _chequeAmountCellStyle; if (_dataEndRow >= _dataStartRow) { _cell.CellFormula = "SUM(I" + Convert.ToInt32(_dataStartRow + 1).ToString("0") + ":L" + Convert.ToInt32(_dataEndRow + 1).ToString("0") + ")"; } _currentRow += 6; _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(_currentRow); WriteCellText(_row, 0, " ", _bottomBorderCellStyle); WriteCellText(_row, 1, "", _bottomBorderCellStyle); WriteCellText(_row, 2, "", _bottomBorderCellStyle); _currentRow++; _row = (NPOI.HSSF.UserModel.HSSFRow)_worksheet.CreateRow(_currentRow); WriteCellText(_row, 0, "Authorized Signature and Co. Chop"); _worksheet.SetColumnWidth(0, 20 * 256); _worksheet.SetColumnWidth(1, 15 * 256); _worksheet.SetColumnWidth(2, 14 * 256); _worksheet.SetColumnWidth(3, 14 * 256); _worksheet.SetColumnWidth(4, 14 * 256); _worksheet.SetColumnWidth(5, 14 * 256); _worksheet.SetColumnWidth(6, 14 * 256); _worksheet.SetColumnWidth(7, 14 * 256); _worksheet.SetColumnWidth(8, 14 * 256); _worksheet.SetColumnWidth(9, 14 * 256); _worksheet.SetColumnWidth(10, 14 * 256); _worksheet.SetColumnWidth(11, 14 * 256); _worksheet.SetColumnWidth(12, 14 * 256); _worksheet.SetColumnWidth(13, 14 * 256); _worksheet.SetColumnWidth(14, 15 * 256); _worksheet.SetColumnWidth(15, 30 * 256); //_worksheet.Workbook.SetPrintArea(_worksheet _worksheet.PrintSetup.Landscape = true; _worksheet.PrintSetup.FitWidth = (short)1; _worksheet.SetMargin(NPOI.SS.UserModel.MarginType.TopMargin, (double)0); _worksheet.SetMargin(NPOI.SS.UserModel.MarginType.BottomMargin, (double)0); _worksheet.SetMargin(NPOI.SS.UserModel.MarginType.LeftMargin, (double)0); _worksheet.SetMargin(NPOI.SS.UserModel.MarginType.RightMargin, (double)0); _worksheet.PrintSetup.HeaderMargin = (double)0; _worksheet.PrintSetup.FooterMargin = (double)0; } }