Example #1
0
        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;
            }
        }
Example #2
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);
                }
            }
        }
Example #3
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 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);
            }
        }