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 override void GenerateWorkbookDetail(NPOI.HSSF.UserModel.HSSFWorkbook workBook, System.Data.DataSet dataSet)
{
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("Payroll Allocation Report - Detail");
ushort rowPos = 0;
DataTable empInfoTable = dataSet.Tables["EmpInfo"];
DataTable paymentTable = dataSet.Tables["payment"];
DataTable costCenterDetailTable = dataSet.Tables["CostCenterDetail"];
DataTable hierarchyTable = dataSet.Tables["hierarchy"];
DataTable payPeriodTable = dataSet.Tables["payPeriod"];
DataTable hierarchyTotalTable = new DataTable("HierarchyTotal"); // use for calculate local hierarchy total
hierarchyTotalTable.Columns.Add("company", typeof(string));
hierarchyTotalTable.Columns.Add("LevelDesc", typeof(string));
hierarchyTotalTable.Columns.Add("payPeriodFr", typeof(DateTime));
hierarchyTotalTable.Columns.Add("ee", typeof(double));
hierarchyTotalTable.Columns.Add("er", typeof(double));
hierarchyTotalTable.Columns.Add("netAmount", typeof(double));
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.LEFT;
NPOI.HSSF.UserModel.HSSFCellStyle groupHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
groupHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle monthHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
monthHeaderStyle.SetFont(boldFont);
monthHeaderStyle.DataFormat = workBook.CreateDataFormat().GetFormat("MMM-yyyy");
monthHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
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 detailNumberStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
detailNumberStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
detailNumberStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
NPOI.HSSF.UserModel.HSSFCellStyle detailTextStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
detailTextStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
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;
DataRow[] EmpInfoRows = dataSet.Tables["EmpInfo"].Select("", "Company, " + empInfoTable.Columns[hierarchyLevelGroupingFieldName].ColumnName + ", Employee No.");
string currentCompany = "#$%@$@#$"; //string.Empty;
string currentHierarchyGroup = string.Empty;
string currentEmployeeNo = string.Empty;
string tmpCompany = "";
string tmpHierarchy = "";
string tmpEmployeeNo = "";
ushort groupRowCount = 0;
bool sectionEnded = false;
if (bolShowIndividual == true)
{
summaryStart = Convert.ToUInt16(4 + hierarchyTable.Rows.Count);
}
else
{
summaryStart = 1;
}
reportEndCol = Convert.ToUInt16(summaryStart + (payPeriodTable.Rows.Count * 4) - 1);
foreach (DataRow EmpInfoRow in EmpInfoRows)
{
tmpCompany = EmpInfoRow["Company"].ToString();
tmpEmployeeNo = EmpInfoRow["Employee No."].ToString();
tmpHierarchy = EmpInfoRow[hierarchyLevelGroupingFieldName].ToString();
if (EmpInfoRows[0] == EmpInfoRow)
{
currentHierarchyGroup = tmpHierarchy;
currentCompany = tmpCompany;
rowPos = GenerateHeader(workSheet, PeriodFrom, PeriodTo, rowPos, reportHeaderStyle, groupHeaderStyle);
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
sectionEnded = (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase) ||
!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase));
if (sectionEnded)
{
if (bolShowIndividual == true)
{
// print hirarchy total if show employee detail
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
rowPos += 2;
}
else
{
// just print hierarchy line (i.e. not subtotal) if employee detail not shown
ushort colPos = 0;
NPOI.HSSF.UserModel.HSSFRow sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)rowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell((int)colPos);
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue(currentHierarchyGroup);
cell.CellStyle = detailTextStyle;
colPos++;
foreach (DataRow m_hiearchyTotalRow in hierarchyTotalTable.Select("LevelDesc = '" + currentHierarchyGroup + "' ", "payPeriodFr"))
{
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["EE"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["netAmount"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["ER"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
rowPos++;
groupRowCount++;
}
if (!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase))
{
if (bolShowIndividual == true)
{
// print company header and column header for new company (in show-employee mode)
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
else
{
// print a summary for previous company (in hide employee info mode)
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
rowPos += 2;
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
currentCompany = tmpCompany;
currentHierarchyGroup = tmpHierarchy;
groupRowCount = 0;
// print company information for new company
}
else if (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase))
{
if (bolShowIndividual == true)
{
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
groupRowCount = 0;
}
else
{
}
currentHierarchyGroup = tmpHierarchy;
}
hierarchyTotalTable.Clear();
}
if (bolShowIndividual == true)
{
groupRowCount++;
NPOI.HSSF.UserModel.HSSFRow m_sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos);
NPOI.HSSF.UserModel.HSSFCell cell;
ushort colPos = 0;
// Employee Number
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Employee No."].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Employee Name
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Employee Name"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Alias
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Alias"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Chinese Name
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Chinese Name"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// hierarchy
foreach (DataRow m_hierarchyRow in hierarchyTable.Rows)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow[m_hierarchyRow["LevelDesc"].ToString()].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
}
foreach (DataRow m_payPeriodRow in payPeriodTable.Rows)
{
//DBFilter m_paymentFilter = new DBFilter();
//m_paymentFilter.add(new Match("payPeriodFr", m_payPeriodRow["payPeriodFr"]));
//m_paymentFilter.add(new Match("EmpID", row["EmpID"]));
double m_er = 0;
double m_ee = 0;
double m_netAmount = 0;
foreach (DataRow m_paymentRow in paymentTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
"EmpID=" + EmpInfoRow["EmpID"].ToString()))
{
m_ee += (double)m_paymentRow["EE"];
m_er += (double)m_paymentRow["ER"];
m_netAmount += (double)m_paymentRow["netAmount"];
}
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_ee);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_netAmount);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_er);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
rowPos++;
}
else
{
foreach (DataRow m_payPeriodRow in payPeriodTable.Rows)
{
if (tmpCompany == "Magazines International (Asia) Limited - SALES" && tmpHierarchy == "Sales Division")
{
double m_trash = 0;
}
double m_er = 0;
double m_ee = 0;
double m_netAmount = 0;
foreach (DataRow m_paymentRow in paymentTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
"EmpID=" + EmpInfoRow["EmpID"].ToString()))
{
m_ee += (double)m_paymentRow["EE"];
m_er += (double)m_paymentRow["ER"];
m_netAmount += (double)m_paymentRow["netAmount"];
}
// find from local HierarchyTotal table and add the new employee amounts
DataRow[] m_hierarchyTotalRows = hierarchyTotalTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
// "Company='" + tmpCompany + "' AND " +
"LevelDesc='" + tmpHierarchy + "' ");
if (m_hierarchyTotalRows.Length > 0)
{
m_hierarchyTotalRows[0]["EE"] = (double)m_hierarchyTotalRows[0]["EE"] + m_ee;
m_hierarchyTotalRows[0]["ER"] = (double)m_hierarchyTotalRows[0]["ER"] + m_er;
m_hierarchyTotalRows[0]["netAmount"] = (double)m_hierarchyTotalRows[0]["netAmount"] + m_netAmount;
}
else
{
DataRow m_newHierarchyTotal = hierarchyTotalTable.Rows.Add();
m_newHierarchyTotal["company"] = tmpCompany;
m_newHierarchyTotal["LevelDesc"] = tmpHierarchy;
m_newHierarchyTotal["payPeriodFr"] = (DateTime)m_payPeriodRow["payPeriodFr"];
m_newHierarchyTotal["EE"] = m_ee;
m_newHierarchyTotal["ER"] = m_er;
m_newHierarchyTotal["netAmount"] = m_netAmount;
}
}
}
}
// insert last hierarchy total
if (bolShowIndividual == true)
{
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
}
else
{
NPOI.HSSF.UserModel.HSSFRow m_sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)rowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(0);
ushort colPos = 0;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(tmpHierarchy);
cell.CellStyle = detailTextStyle;
colPos++;
foreach (DataRow m_hiearchyTotalRow in hierarchyTotalTable.Select("LevelDesc = '" + tmpHierarchy + "' ", "payPeriodFr"))
{
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["EE"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["netAmount"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["ER"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
groupRowCount++;
rowPos++;
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
}
for (int i = 0; i <= reportEndCol; i++)
{
if (i < summaryStart)
{
workSheet.SetColumnWidth(i, 15 * 254);
}
else
{
workSheet.SetColumnWidth(i, 14 * 254);
}
}
}
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);
}
}
