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 void GenerateReport(ArrayList empList, DateTime PeriodFrom, DateTime PeriodTo, string SortBy)
{
DataTable dataTable = new DataTable();
dataTable.Columns.Add("EmpID", typeof(int));
dataTable.Columns.Add("EmpNo", typeof(string));
dataTable.Columns.Add("EmpName", typeof(string));
dataTable.Columns.Add("EmpAlias", typeof(string));
dataTable.Columns.Add("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("Position", typeof(string));
dataTable.Columns.Add("TrainingSeminarID", typeof(int));
dataTable.Columns.Add("TrainingCourseName", typeof(string));
dataTable.Columns.Add("TrainingSeminarDesc", typeof(string));
dataTable.Columns.Add("TrainingSeminarDateFrom", typeof(DateTime));
dataTable.Columns.Add("TrainingSeminarDateTo", typeof(DateTime));
dataTable.Columns.Add("TrainingSeminarDuration", typeof(double));
dataTable.Columns.Add("TrainingSeminarDurationUnit", typeof(string));
dataTable.Columns.Add("TrainingSeminarTrainer", typeof(string));
foreach (EEmpPersonalInfo empInfo in empList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, empInfo.EmpID);
ECompany company = new ECompany();
EPosition position = new EPosition();
ArrayList empHierarchyList = new ArrayList();
if (empPos != null)
{
company.CompanyID = empPos.CompanyID;
ECompany.db.select(dbConn, company);
//row["Company"] = company.CompanyCode;
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
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;
// }
//}
position.PositionID = empPos.PositionID;
EPosition.db.select(dbConn, position);
}
DBFilter empTrainingSeminar = new DBFilter();
empTrainingSeminar.add(new Match("EmpID", empInfo.EmpID));
OR orTrainingCourse = null;
foreach (ListItem item in TrainingCourseList.Items)
{
if (item.Selected)
{
if (orTrainingCourse == null)
{
orTrainingCourse = new OR();
}
orTrainingCourse.add(new Match("te.TrainingCourseID", item.Value));
}
}
DBFilter trainingSeminarFilter = new DBFilter();
if (!PeriodFrom.Ticks.Equals(0))
{
trainingSeminarFilter.add(new Match("TrainingSeminarDateFrom", ">=", PeriodFrom));
}
if (!PeriodTo.Ticks.Equals(0))
{
trainingSeminarFilter.add(new Match("TrainingSeminarDateTo", "<=", PeriodTo));
}
if (orTrainingCourse != null)
{
trainingSeminarFilter.add(orTrainingCourse);
}
empTrainingSeminar.add(new IN("TrainingSeminarID", "Select TrainingSeminarID from " + ETrainingSeminar.db.dbclass.tableName + " te", trainingSeminarFilter));
ArrayList empTrainingSeminarList = EEmpTrainingEnroll.db.select(dbConn, empTrainingSeminar);
foreach (EEmpTrainingEnroll empTrainingEnroll in empTrainingSeminarList)
{
ETrainingSeminar trainingSeminar = new ETrainingSeminar();
trainingSeminar.TrainingSeminarID = empTrainingEnroll.TrainingSeminarID;
if (ETrainingSeminar.db.select(dbConn, trainingSeminar))
{
DataRow row = dataTable.NewRow();
row["EmpID"] = empInfo.EmpID;
row["EmpNo"] = empInfo.EmpNo;
row["EmpName"] = empInfo.EmpEngFullName;
row["EmpAlias"] = empInfo.EmpAlias;
row["Company"] = company.CompanyCode;
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;
}
}
}
row["Position"] = position.PositionDesc;
row["TrainingSeminarID"] = trainingSeminar.TrainingSeminarID;
ETrainingCourse trainingCourse = new ETrainingCourse();
trainingCourse.TrainingCourseID = trainingSeminar.TrainingCourseID;
if (ETrainingCourse.db.select(dbConn, trainingCourse))
{
row["TrainingCourseName"] = trainingCourse.TrainingCourseName;
}
else
{
row["TrainingCourseName"] = string.Empty;
}
row["TrainingSeminarDesc"] = trainingSeminar.TrainingSeminarDesc == null ? string.Empty : trainingSeminar.TrainingSeminarDesc;
row["TrainingSeminarDateFrom"] = trainingSeminar.TrainingSeminarDateFrom;
row["TrainingSeminarDateTo"] = trainingSeminar.TrainingSeminarDateTo;
row["TrainingSeminarDuration"] = trainingSeminar.TrainingSeminarDuration;
if (trainingSeminar.TrainingSeminarDurationUnit.Equals("H"))
{
row["TrainingSeminarDurationUnit"] = "Hour(s)";
}
else
{
row["TrainingSeminarDurationUnit"] = trainingSeminar.TrainingSeminarDurationUnit;
}
row["TrainingSeminarTrainer"] = trainingSeminar.TrainingSeminarTrainer;
dataTable.Rows.Add(row);
}
}
}
}
//org.in2bits.MyXls.XlsDocument document = new org.in2bits.MyXls.XlsDocument();
//org.in2bits.MyXls.Worksheet worksheet = document.Workbook.Worksheets.Add("training report");
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("training report");
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
reportHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleCenter.SetFont(boldFont);
columnHeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
//NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
int rowCount = 0;
//worksheet.Cells.Add(rowCount, (ushort)1, "Training Report").Font.Bold = true;
NPOI.HSSF.UserModel.HSSFCell reportHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)worksheet.CreateRow(rowCount).CreateCell(0);
reportHeaderCell.SetCellValue("Training Report");
reportHeaderCell.CellStyle = reportHeaderStyle;
rowCount++;
if (!PeriodFrom.Ticks.Equals(0) && !PeriodTo.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
rowCount++;
}
else if (!PeriodTo.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
rowCount++;
}
else if (!PeriodFrom.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "From: " + PeriodFrom.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("From: " + PeriodFrom.ToString("dd/MM/yyyy"));
rowCount++;
}
if (SortBy.Equals("Date", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName, EmpNo";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
//worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
//worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
//worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
int currentTrainingSeminarID = 0;
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
if (!currentTrainingSeminarID.Equals((int)row["TrainingSeminarID"]))
{
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
}
currentTrainingSeminarID = ((int)row["TrainingSeminarID"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)3, row["TrainingSeminarDesc"]);
//worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
//worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
detailRow.CreateCell(1).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(2).SetCellValue(row["TrainingSeminarDesc"].ToString());
detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(3).CellStyle = numericStyle;
detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());
colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
}
else if (SortBy.Equals("Position", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "Position, EmpNo, TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName ";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)5, "Company").Font.Bold = true;
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(4);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 4;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
//worksheet.Cells.Add(rowCount, (ushort)colCount + 1, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 2, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 4, "Duration").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 5, string.Empty);
//worksheet.Cells.Merge(rowCount, rowCount, colCount + 4, colCount + 5);
//worksheet.Rows[rowCount].GetCell((ushort)(colCount + 4)).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 6, "Trainer").Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 1);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 2);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 3);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 4);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, colCount + 4, colCount + 5));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 6);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
int currentEmpID = 0;
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
if (!currentEmpID.Equals((int)row["EmpID"]))
{
//worksheet.Cells.Add(rowCount, (ushort)1, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)3, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)4, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["Company"]);
detailRow.CreateCell(0).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(1).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(2).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(3).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(4).SetCellValue(row["Company"].ToString());
colCount = 4;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
currentEmpID = ((int)row["EmpID"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 1, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 2, row["TrainingSeminarDesc"]);
detailRow.CreateCell(colCount + 1).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(colCount + 2).SetCellValue(row["TrainingSeminarDesc"].ToString());
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
//worksheet.Cells.Add(rowCount, (ushort)colCount + 4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 6, row["TrainingSeminarTrainer"]);
detailRow.CreateCell(colCount + 4).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(colCount + 4).CellStyle = numericStyle;
detailRow.CreateCell(colCount + 5).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(colCount + 6).SetCellValue(row["TrainingSeminarTrainer"].ToString());
}
}
else if (SortBy.Equals("Course", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "TrainingCourseName, TrainingSeminarDateFrom, TrainingSeminarDateTo, EmpNo";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
//worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
//worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
//worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
//worksheet.Cells.Add(rowCount, (ushort)1, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingSeminarDesc"]);
detailRow.CreateCell(0).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(1).SetCellValue(row["TrainingSeminarDesc"].ToString());
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
//worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
//worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(3).CellStyle = numericStyle;
detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());
colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
}
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
//document.FileName = exportFileName;
//document.Save();
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
string filename = "TrainingReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls";
WebUtils.TransmitFile(Response, exportFileName, filename, true);
return;
}
protected ushort GenerateColumnHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, System.Data.DataSet dataSet,
string HierarchyHeader, ushort RowPos,
NPOI.HSSF.UserModel.HSSFCellStyle GroupHeaderStyle,
NPOI.HSSF.UserModel.HSSFCellStyle MonthHeaderStyle,
NPOI.HSSF.UserModel.HSSFCellStyle ColumnHeaderStyle)
{
DataTable paymentTable = dataSet.Tables["payment"];
DataTable hierarchyTable = dataSet.Tables["hierarchy"];
DataTable payPeriodTable = dataSet.Tables["payPeriod"];
NPOI.HSSF.UserModel.HSSFRow monthRow;
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0);
//workSheet.Cells.Add((int)RowPos, 1, HierarchyHeader, xf);
monthRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
RowPos++;
columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
ushort colPos = 0;
if (bolShowIndividual == true)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Employee No.");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("English Name");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Alias");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Chinese Name");
cell.CellStyle = ColumnHeaderStyle;
foreach (DataRow hierarchyRow in hierarchyTable.Rows)
{
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue(hierarchyRow["LevelDesc"].ToString());
cell.CellStyle = ColumnHeaderStyle;
}
}
else
{
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue(HierarchyHeader);
cell.CellStyle = ColumnHeaderStyle;
}
summaryStart = Convert.ToUInt16(colPos + Convert.ToUInt16(1));
foreach (DataRow payPeriodRow in payPeriodTable.Rows)
{
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)monthRow.CreateCell(colPos);
cell.SetCellValue(DateTime.Parse(payPeriodRow["payPeriodFr"].ToString()).ToString("dd-MM-yyyy"));
cell.CellStyle = MonthHeaderStyle;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(monthRow.RowNum, monthRow.RowNum, colPos, colPos + 3));
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("MPF EE");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Net Payment");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("MPF ER");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Total");
cell.CellStyle = ColumnHeaderStyle;
}
RowPos++;
return(RowPos);
}
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 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));
}
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;
}
}
protected ushort GenerateColumnHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, System.Data.DataSet dataSet, string CompanyHeader, string HierarchyHeader, ushort RowPos, NPOI.HSSF.UserModel.HSSFCellStyle GroupHeaderStyle, NPOI.HSSF.UserModel.HSSFCellStyle ColumnHeaderStyle)
{
DataTable paymentTable = dataSet.Tables["payment"];
//org.in2bits.MyXls.XF xf = xlsDoc.NewXF();
//xf.Font.Bold = true;
//xf.BottomLineStyle = (ushort)1;
RowPos += 2;
//workSheet.Cells.Add((int)RowPos, 1, "Company");
//workSheet.Cells.Add((int)RowPos, 2, CompanyHeader);
//workSheet.Rows[RowPos].GetCell(1).Font.Bold = true;
//workSheet.Rows[RowPos].GetCell(2).Font.Bold = true;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0);
cell.SetCellValue("Company");
cell.CellStyle = GroupHeaderStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1);
cell.SetCellValue(CompanyHeader);
cell.CellStyle = GroupHeaderStyle;
RowPos += 2;
//workSheet.Cells.Add((int)RowPos, 1, HierarchyHeader, xf);
row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
ushort colPos = 0;
// ***** Start 2013/11/22, Ricky So, grouping for Staff where Employee No is necessary
if (intHierarchyLevelID == STAFF_LEVEL_ID)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue("Employee No.");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
}
// ***** End 2013/11/22, Ricky So, grouping for Staff where Employee No is necessary
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue(HierarchyHeader);
cell.CellStyle = ColumnHeaderStyle;
foreach (DataColumn column in paymentTable.Columns)
{
if (column.ColumnName.Equals("EmpPayrollID"))
{
continue;
}
colPos++;
//workSheet.Cells.Add(RowPos, colPos, column.ColumnName, xf);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = ColumnHeaderStyle;
}
colPos++;
//workSheet.Cells.Add(RowPos, colPos, "Net Pay", xf);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue("Net Pay");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
//workSheet.Cells.Add(RowPos, colPos, "Employer Contribution", xf);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue("Employer Contribution");
cell.CellStyle = ColumnHeaderStyle;
//workSheet.Cells.Merge((int)RowPos - 2, (int)RowPos - 2, 2, colPos);
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress((int)RowPos - 2, (int)RowPos - 2, 1, colPos));
return(RowPos);
}
protected override void GenerateWorkbookDetail(NPOI.HSSF.UserModel.HSSFWorkbook workBook, System.Data.DataSet dataSet)
{
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("Payroll Detail");
ushort rowPos = 0;
DataTable empInfoTable = dataSet.Tables["EmpInfo"];
DataTable paymentTable = dataSet.Tables["payment"];
DataTable costCenterDetailTable = dataSet.Tables["CostCenterDetail"];
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont();
boldFont.Boldweight = 700;
NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
reportHeaderStyle.SetFont(boldFont);
reportHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFCellStyle groupHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
groupHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
columnHeaderStyle.SetFont(boldFont);
columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle detailStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
detailStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
subTotalStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
subTotalStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
//rowPos = GenerateHeader(xlsDoc, workSheet, PeriodFrom, PeriodTo, rowPos);
rowPos = GenerateHeader(workSheet, PeriodFrom, PeriodTo, rowPos, reportHeaderStyle, groupHeaderStyle);
DataRow[] rows = dataSet.Tables["EmpInfo"].Select("", "Company, " + empInfoTable.Columns[hierarchyLevelGroupingFieldName].ColumnName + ", Employee Name, Alias");
string currentCompany = string.Empty;
string currentHierarchyGroup = string.Empty;
string currentEmployeeNo = string.Empty;
ushort groupRowCount = 0;
Hashtable groupedTotalCostCenterHash = new Hashtable();
ArrayList subTotalRowList = new ArrayList();
Hashtable paymentHashTable = new Hashtable();
Hashtable companyTotalHashTable = new Hashtable();
double netPayment = 0;
double companyNetPayment = 0;
double employerContribution = 0;
double companyEmployerContribution = 0;
ArrayList companyTotalRowNumList = new ArrayList();
foreach (DataRow row in rows)
{
string tmpCompany = row["Company"].ToString();
string tmpHierarchy = row[hierarchyLevelGroupingFieldName].ToString();
string tmpEmployeeNo = row["Employee No."].ToString();
if (!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase) || rows[0] == row)
{
if (rows[0] != row)
{
groupRowCount++;
rowPos = GenerateHierarchyTotal(workSheet, paymentTable, currentHierarchyGroup, currentEmployeeNo, paymentHashTable, netPayment, employerContribution, rowPos, detailStyle);
paymentHashTable = new Hashtable();
rowPos = GenerateCompanyTotal(workSheet, paymentTable, groupRowCount, companyEmployerContribution, rowPos, subTotalStyle);
companyTotalRowNumList.Add(rowPos);
companyTotalHashTable = new Hashtable();
}
groupRowCount = 0;
netPayment = 0;
employerContribution = 0;
currentHierarchyGroup = tmpHierarchy;
currentEmployeeNo = tmpEmployeeNo;
companyNetPayment = 0;
companyEmployerContribution = 0;
currentCompany = tmpCompany;
rowPos = GenerateColumnHeader(workSheet, dataSet, currentCompany, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, columnHeaderStyle);
}
else if (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase))
{
groupRowCount++;
rowPos = GenerateHierarchyTotal(workSheet, paymentTable, currentHierarchyGroup, currentEmployeeNo, paymentHashTable, netPayment, employerContribution, rowPos, detailStyle);
paymentHashTable = new Hashtable();
netPayment = 0;
employerContribution = 0;
currentHierarchyGroup = tmpHierarchy;
currentEmployeeNo = tmpEmployeeNo;
}
DataRow[] paymentRows = paymentTable.Select("[EmpPayrollID]='" + row["EmpPayrollID"].ToString() + "'");
foreach (DataRow paymentRow in paymentRows)
{
foreach (DataColumn column in paymentTable.Columns)
{
if (column.ColumnName.Equals("EmpPayrollID"))
{
continue;
}
double amount = 0;
double companyTotalAmount = 0;
if (paymentHashTable.ContainsKey(column.ColumnName))
{
amount = (double)paymentHashTable[column.ColumnName];
}
else
{
paymentHashTable.Add(column.ColumnName, amount);
}
if (companyTotalHashTable.ContainsKey(column.ColumnName))
{
companyTotalAmount = (double)companyTotalHashTable[column.ColumnName];
}
else
{
companyTotalHashTable.Add(column.ColumnName, companyTotalAmount);
}
if (!string.IsNullOrEmpty(paymentRow[column.ColumnName].ToString()))
{
amount += (double)paymentRow[column.ColumnName];
companyTotalAmount += (double)paymentRow[column.ColumnName];
netPayment += (double)paymentRow[column.ColumnName];
companyNetPayment += (double)paymentRow[column.ColumnName];
paymentHashTable[column.ColumnName] = amount;
companyTotalHashTable[column.ColumnName] = companyTotalAmount;
}
}
}
if (!row.IsNull("MCER"))
{
double contribution = (double)row["MCER"];
employerContribution += contribution;
companyEmployerContribution += contribution;
}
if (!row.IsNull("VCER"))
{
double contribution = (double)row["VCER"];
employerContribution += contribution;
companyEmployerContribution += contribution;
}
if (!row.IsNull("PFundER"))
{
double contribution = (double)row["PFundER"];
employerContribution += contribution;
companyEmployerContribution += contribution;
}
}
if (paymentHashTable.Count > 0)
{
groupRowCount++;
rowPos = GenerateHierarchyTotal(workSheet, paymentTable, currentHierarchyGroup, currentEmployeeNo, paymentHashTable, netPayment, employerContribution, rowPos, detailStyle);
paymentHashTable = new Hashtable();
netPayment = 0;
employerContribution = 0;
}
if (groupRowCount > 0)
{
rowPos = GenerateCompanyTotal(workSheet, paymentTable, groupRowCount, companyEmployerContribution, rowPos, subTotalStyle);
companyTotalRowNumList.Add(rowPos);
}
if (companyTotalRowNumList.Count > 0)
{
rowPos = GenerateTotal(workSheet, paymentTable, companyTotalRowNumList, rowPos, subTotalStyle);
}
//workSheet.Cells.Merge(1, 1, 1, paymentTable.Columns.Count + 2);
//workSheet.Rows[1].GetCell(1).Font.Bold = true;
//workSheet.Rows[1].GetCell(1).Font.Height = 300;
//workSheet.Rows[1].GetCell(1).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, paymentTable.Columns.Count + 1));
// checking the number of column to resize for better user view
int resizeColumn = paymentTable.Columns.Count + 1;
if (intHierarchyLevelID == STAFF_LEVEL_ID)
{
resizeColumn++;
}
for (int i = 0; i <= resizeColumn; i++)
{
//org.in2bits.MyXls.ColumnInfo columnInfo = new org.in2bits.MyXls.ColumnInfo(xlsDoc, workSheet);
//workSheet.AddColumnInfo(columnInfo);
//columnInfo.ColumnIndexStart = 0;
//columnInfo.ColumnIndexEnd = (ushort)(paymentTable.Columns.Count + 2);
//columnInfo.Width = 15 * 254;
//columnInfo.Collapsed = true;
workSheet.SetColumnWidth(i, 15 * 254);
}
}