Esempio n. 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;
            }
        }