private void createWorkSheetPeriodRow(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, DateTime periodFromDate, DateTime periodToDate) { // create header styles NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleLeft = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); NPOI.HSSF.UserModel.HSSFFont HeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont(); HeaderFont.Boldweight = 900; HeaderFont.FontHeightInPoints = 16; HeaderStyleLeft.SetFont(HeaderFont); NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); HeaderStyleCenter.CloneStyleFrom(HeaderStyleLeft); HeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleRight = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); HeaderStyleRight.CloneStyleFrom(HeaderStyleLeft); HeaderStyleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; // Create header row NPOI.HSSF.UserModel.HSSFRow HeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0); NPOI.HSSF.UserModel.HSSFCell HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(5); HeaderCell.SetCellValue(periodFromDate.ToString("dd-MMM-yy")); HeaderCell.CellStyle = HeaderStyleRight; HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(6); HeaderCell.SetCellValue("~"); HeaderCell.CellStyle = HeaderStyleCenter; HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(7); HeaderCell.SetCellValue(periodToDate.ToString("dd-MMM-yy")); HeaderCell.CellStyle = HeaderStyleLeft; }
private void createWorkSheetDetailRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, int RowPos, List <string> hElementDescList, EEmpPersonalInfo empInfo, string positionTitle, int countDay, DateTime periodFromDate, int empID) { // Create column detail styles NPOI.HSSF.UserModel.HSSFCellStyle detailStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); detailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; detailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; detailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle detailStyleCenterAlignment = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); detailStyleCenterAlignment.CloneStyleFrom(detailStyle); detailStyleCenterAlignment.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFCellStyle detailStyleFirst = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); detailStyleFirst.CloneStyleFrom(detailStyle); detailStyleFirst.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle detailStyleLast = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); detailStyleLast.CloneStyleFrom(detailStyle); detailStyleLast.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM; // Create column detail rows const int DETAIL_START_ROW = 4; NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(DETAIL_START_ROW + RowPos); NPOI.HSSF.UserModel.HSSFRow detailRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(DETAIL_START_ROW + RowPos + 1); // second row of record NPOI.HSSF.UserModel.HSSFCell detailCell; NPOI.HSSF.UserModel.HSSFCell detailCell2; int colpos = 0; foreach (string hElementDesc in hElementDescList) { workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(hElementDesc); detailCell.CellStyle = detailStyleFirst; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(hElementDesc); detailCell2.CellStyle = detailStyleFirst; colpos++; } workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(empInfo.EmpEngFullName); detailCell.CellStyle = detailStyleFirst; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(empInfo.EmpEngFullName); detailCell2.CellStyle = detailStyleFirst; colpos++; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(positionTitle); detailCell.CellStyle = detailStyle; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(positionTitle); detailCell2.CellStyle = detailStyle; colpos++; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(empInfo.EmpNo); detailCell.CellStyle = detailStyle; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(empInfo.EmpNo); detailCell2.CellStyle = detailStyle; colpos++; for (int dayIdx = 0; dayIdx < countDay; dayIdx++) { //first line of employee record detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos + dayIdx); detailCell.CellStyle = detailStyleCenterAlignment; // second line of employee data detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos + dayIdx); detailCell2.CellStyle = detailStyleCenterAlignment; string currentTimeSpanRangeStr = getCurrentTimeSpanRangeStr(periodFromDate, dayIdx, empID); if (currentTimeSpanRangeStr != null) { detailCell.SetCellValue(currentTimeSpanRangeStr); } else { string currentRosterCodeDesc = getCurrentRosterCodeDesc(periodFromDate, dayIdx, empID); if (currentRosterCodeDesc != null) { detailCell.SetCellValue(currentRosterCodeDesc); } } string s = getCurrentLeaveCodeDesc(periodFromDate, dayIdx, empID); if (s != "") { detailCell2.SetCellValue(s); } } colpos += countDay; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos)); detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos); detailCell.SetCellValue(string.Empty); detailCell.CellStyle = detailStyleLast; detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos); detailCell2.SetCellValue(string.Empty); detailCell2.CellStyle = detailStyleLast; }
private void createWorkSheetHeaderRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, List <EHierarchyLevel> hLevelList, int countDay, DateTime periodFromDate, DateTime periodToDate) { // Create column header styles NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; columnHeaderStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFFont columnHeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont(); columnHeaderFont.Boldweight = 900; columnHeaderStyle.SetFont(columnHeaderFont); columnHeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; columnHeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; columnHeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleFirstTop.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleFirstTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM; columnHeaderStyleFirstTop.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleMiddleTop.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleMiddleTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleLastTop.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleLastTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM; columnHeaderStyleLastTop.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleFirstBottom.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleFirstBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM; columnHeaderStyleFirstBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleMiddleBottom.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleMiddleBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM; NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); columnHeaderStyleLastBottom.CloneStyleFrom(columnHeaderStyle); columnHeaderStyleLastBottom.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM; columnHeaderStyleLastBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM; // Create column header rows const int COLUMN_HEADER_ROW = 2; NPOI.HSSF.UserModel.HSSFRow columnHeaderRow1 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW); NPOI.HSSF.UserModel.HSSFRow columnHeaderRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + 1); NPOI.HSSF.UserModel.HSSFCell columnHeaderCell; int colpos = 0; foreach (EHierarchyLevel hLevel in hLevelList) { columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue(hLevel.HLevelDesc); columnHeaderCell.CellStyle = columnHeaderStyleFirstTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleFirstBottom; colpos++; } columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue("Name"); columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom; colpos++; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue("Title"); columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom; colpos++; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue("Staff No."); columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom; colpos++; for (int colIdx = 0; colIdx < colpos; colIdx++) { workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colIdx, colIdx)); } for (int dayIdx = 0; dayIdx < countDay; dayIdx++) { DateTime currentDate = periodFromDate.AddDays(dayIdx); columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos + dayIdx); columnHeaderCell.SetCellValue(currentDate.ToString("dd-MMM-yy")); columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos + dayIdx); columnHeaderCell.SetCellValue(currentDate.ToString("ddd")); columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom; } colpos += countDay; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos); columnHeaderCell.SetCellValue("Signature"); columnHeaderCell.CellStyle = columnHeaderStyleLastTop; columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos); columnHeaderCell.SetCellValue(string.Empty); columnHeaderCell.CellStyle = columnHeaderStyleLastBottom; workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colpos, colpos)); }