Example #1
0
    private void createWorkSheetPeriodRow(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet,
                                          DateTime periodFromDate, DateTime periodToDate)
    {
        // create header styles
        NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleLeft = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        NPOI.HSSF.UserModel.HSSFFont      HeaderFont      = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
        HeaderFont.Boldweight         = 900;
        HeaderFont.FontHeightInPoints = 16;
        HeaderStyleLeft.SetFont(HeaderFont);

        NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        HeaderStyleCenter.CloneStyleFrom(HeaderStyleLeft);
        HeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;

        NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleRight = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        HeaderStyleRight.CloneStyleFrom(HeaderStyleLeft);
        HeaderStyleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;

        // Create header row
        NPOI.HSSF.UserModel.HSSFRow  HeaderRow  = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
        NPOI.HSSF.UserModel.HSSFCell HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(5);
        HeaderCell.SetCellValue(periodFromDate.ToString("dd-MMM-yy"));
        HeaderCell.CellStyle = HeaderStyleRight;

        HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(6);
        HeaderCell.SetCellValue("~");
        HeaderCell.CellStyle = HeaderStyleCenter;

        HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(7);
        HeaderCell.SetCellValue(periodToDate.ToString("dd-MMM-yy"));
        HeaderCell.CellStyle = HeaderStyleLeft;
    }
        /// <summary>
        /// 获取单元格类型(xls)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLS(XLS.HSSFCell cell)
        {
            if (cell == null)
            {
                return(null);
            }
            switch (cell.CellType)
            {
            case CellType.BLANK:     //BLANK:
                return(null);

            case CellType.BOOLEAN:     //BOOLEAN:
                return(cell.BooleanCellValue);

            case CellType.NUMERIC:     //NUMERIC:
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return(cell.DateCellValue.ToString("yyyy-MM-dd"));
                }
                return(cell.NumericCellValue);

            case CellType.STRING:     //STRING:
                return(cell.StringCellValue.Trim());

            case CellType.ERROR:     //ERROR:
                return(cell.ErrorCellValue);

            case CellType.FORMULA:     //FORMULA:
            default:
                return(cell.NumericCellValue);
            }
        }
Example #3
0
 private ExcelCell ResolveCell(Npoi.HSSFCell native)
 {
     if (!_cells.ContainsKey(native))
     {
         _cells.Add(native, new NpoiExcelCell(native));
     }
     return(_cells[native]);
 }
Example #4
0
 public void SetUp()
 {
     HSSFWorkbook wb = new HSSFWorkbook();
     try
     {
         HSSFSheet sheet = wb.CreateSheet("new sheet") as HSSFSheet;
         cell11 = sheet.CreateRow(0).CreateCell(0) as HSSFCell;
         cell11.SetCellType(CellType.Formula);
         Evaluator = new HSSFFormulaEvaluator(wb);
     }
     finally
     {
         //wb.Close();
     }
 }
Example #5
0
        /// <summary>
        /// Check if a cell Contains a date
        /// Since dates are stored internally in Excel as double values
        /// we infer it Is a date if it Is formatted as such.
        /// </summary>
        /// <param name="cell">The cell.</param>
        /// <returns>
        ///     <c>true</c> if [is cell date formatted] [the specified cell]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsCellDateFormatted(HSSFCell cell)
        {
            if (cell == null)
            {
                return(false);
            }
            bool bDate = false;

            double d = cell.NumericCellValue;

            if (HSSFDateUtil.IsValidExcelDate(d))
            {
                HSSFCellStyle style = cell.CellStyle;
                int           i     = style.DataFormat;
                String        f     = style.GetDataFormatString(cell.BoundWorkbook);
                bDate = IsADateFormat(i, f);
            }
            return(bDate);
        }
 private static void SetCellType(HSSFCell cell, CellValue cv)
 {
     int cellType = cv.CellType;
     switch (cellType)
     {
         case HSSFCell.CELL_TYPE_BOOLEAN:
         case HSSFCell.CELL_TYPE_ERROR:
         case HSSFCell.CELL_TYPE_NUMERIC:
         case HSSFCell.CELL_TYPE_STRING:
             cell.SetCellType(cellType);
             return;
         case HSSFCell.CELL_TYPE_BLANK:
         // never happens - blanks eventually get translated to zero
             break;
         case HSSFCell.CELL_TYPE_FORMULA:
         // this will never happen, we have already evaluated the formula
             break;
     }
     throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
 }
Example #7
0
 public static void ChangeFormat(DataColumn column, string drValue, HSSFCell newCell, HSSFCellStyle contentDateStyle)
 {
     switch (column.DataType.ToString())
     {
         case "System.String":   //字符串类型
             string result = drValue;
             newCell.SetCellValue(result);
             break;
         case "System.DateTime": //日期类型
             DateTime dateV;
             DateTime.TryParse(drValue, out dateV);
             newCell.SetCellValue(dateV);
             newCell.CellStyle = contentDateStyle; //格式化显示
             break;
         case "System.Boolean":  //布尔型
             bool boolV = false;
             bool.TryParse(drValue, out boolV);
             newCell.SetCellValue(boolV);
             break;
         case "System.Int16":    //整型
         case "System.Int32":
         case "System.Int64":
         case "System.Byte":
             int intV = 0;
             int.TryParse(drValue, out intV);
             newCell.SetCellValue(intV);
             break;
         case "System.Decimal":  //浮点型
         case "System.Double":
             double doubV = 0;
             double.TryParse(drValue, out doubV);
             newCell.SetCellValue(doubV);
             break;
         case "System.DBNull":   //空值处理
             newCell.SetCellValue("");
             break;
         default:
             newCell.SetCellValue("");
             break;
     }
 }
    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;
            }
        }
    }
Example #9
0
        /// <summary>Gets the cell located at a specific address.</summary>
        /// <param name="row">The 0-based row.</param>
        /// <param name="col">The 0-based column.</param>
        /// <returns>The cell at the specified location.</returns>
        public override ExcelCell CellAt(int row, int col)
        {
            var dataRow = this._sheet.GetRow(row);

            if (dataRow == null)
            {
                dataRow = this._sheet.CreateRow(row);
            }

            if (col > dataRow.LastCellNum)
            {
                dataRow.CreateCell(col);
            }

            Npoi.HSSFCell cell = dataRow.GetCell(col) as Npoi.HSSFCell;
            if (cell == null)
            {
                cell = (Npoi.HSSFCell)dataRow.CreateCell(col);
            }

            return(ResolveCell(cell));
        }
    private NPOI.HSSF.UserModel.HSSFSheet CreateWorkSheet(NPOI.HSSF.UserModel.HSSFWorkbook workbook, DataTable dataTable)
    {
        if (workbook != null)
        {
            NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(dataTable.TableName.Replace("$", ""));
            //NPOI.HSSF.UserModel.HSSFRow chineseHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
            NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
            int columnCount = 0;

            //System.Globalization.CultureInfo chineseCI = new System.Globalization.CultureInfo("zh-cht");
            foreach (DataColumn headercolumn in dataTable.Columns)
            {
                headercolumn.ColumnName = headercolumn.ColumnName.Trim();

                if (!headercolumn.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase))
                {
                    string columnName = headercolumn.ColumnName;
                    //string chineseColumnName = HROne.Common.WebUtility.GetLocalizedString(columnName, chineseCI);
                    //if (columnName.Equals(chineseColumnName))
                    //    chineseColumnName = string.Empty;

                    NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
                    cell.SetCellValue(columnName);

                    //cell = (NPOI.HSSF.UserModel.HSSFCell)chineseHeaderRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
                    //cell.SetCellValue(chineseColumnName);

                    //headerRow.SetCell(columnCount,cell);
                    //worksheet.Cells[0, columnCount] = cell;//new ExcelLibrary.SpreadSheet.Cell(column.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
                    columnCount++;
                }
            }
            //worksheet.Cells.Rows.Add(0, headerRow);

            int rowCount = 0;


            NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();

            NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
            boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; //900;

            NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
            dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");

            NPOI.HSSF.UserModel.HSSFCellStyle ManualAdjustCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
            //ManualInputDateCellStyle.CloneStyleFrom(dateCellStyle);
            ManualAdjustCellStyle.SetFont(boldFont);

            //NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
            //numericCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); ;

            //NPOI.HSSF.UserModel.HSSFCellStyle integerCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
            //integerCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0"); ;

            foreach (DataRow row in dataTable.Rows)
            {
                rowCount++;
                columnCount = 0;

                //                    ExcelLibrary.SpreadSheet.Row detailRow = new ExcelLibrary.SpreadSheet.Row();

                NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);

                foreach (DataColumn column in dataTable.Columns)
                {
                    if (!column.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        //ExcelLibrary.SpreadSheet.Cell cell =new ExcelLibrary.SpreadSheet.Cell(string.Empty, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
                        NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(columnCount);

                        if (column.DataType.Equals(typeof(string)))
                        {
                            //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
                            //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
                            cell.SetCellValue(row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());

                            //  Override style to bold if manual adjust
                            if (cell.StringCellValue != string.Empty)
                            {
                                if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN))
                                {
                                    ETimeCardRecord timeCard = new ETimeCardRecord();
                                    try
                                    {
                                        timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID];
                                    }
                                    catch { }
                                    if (ETimeCardRecord.db.select(dbConn, timeCard))
                                    {
                                        if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
                                        {
                                            cell.CellStyle = ManualAdjustCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.CellStyle = ManualAdjustCellStyle;
                                    }
                                }
                                else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT))
                                {
                                    ETimeCardRecord timeCard = new ETimeCardRecord();
                                    try
                                    {
                                        timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID];
                                    }
                                    catch { }
                                    if (ETimeCardRecord.db.select(dbConn, timeCard))
                                    {
                                        if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
                                        {
                                            cell.CellStyle = ManualAdjustCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.CellStyle = ManualAdjustCellStyle;
                                    }
                                }
                                else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT))
                                {
                                    ETimeCardRecord timeCard = new ETimeCardRecord();
                                    try
                                    {
                                        timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID];
                                    }
                                    catch { }
                                    if (ETimeCardRecord.db.select(dbConn, timeCard))
                                    {
                                        if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
                                        {
                                            cell.CellStyle = ManualAdjustCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.CellStyle = ManualAdjustCellStyle;
                                    }
                                }
                                else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN))
                                {
                                    ETimeCardRecord timeCard = new ETimeCardRecord();
                                    try
                                    {
                                        timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID];
                                    }
                                    catch { }
                                    if (ETimeCardRecord.db.select(dbConn, timeCard))
                                    {
                                        if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
                                        {
                                            cell.CellStyle = ManualAdjustCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.CellStyle = ManualAdjustCellStyle;
                                    }
                                }
                                else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_LOCATION))
                                {
                                    ETimeCardRecord timeCard = new ETimeCardRecord();
                                    try
                                    {
                                        timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID];
                                    }
                                    catch { }
                                    if (ETimeCardRecord.db.select(dbConn, timeCard))
                                    {
                                        if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
                                        {
                                            cell.CellStyle = ManualAdjustCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.CellStyle = ManualAdjustCellStyle;
                                    }
                                }
                                else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_LOCATION))
                                {
                                    ETimeCardRecord timeCard = new ETimeCardRecord();
                                    try
                                    {
                                        timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID];
                                    }
                                    catch { }
                                    if (ETimeCardRecord.db.select(dbConn, timeCard))
                                    {
                                        if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
                                        {
                                            cell.CellStyle = ManualAdjustCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.CellStyle = ManualAdjustCellStyle;
                                    }
                                }
                                else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_LOCATION))
                                {
                                    ETimeCardRecord timeCard = new ETimeCardRecord();
                                    try
                                    {
                                        timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID];
                                    }
                                    catch { }
                                    if (ETimeCardRecord.db.select(dbConn, timeCard))
                                    {
                                        if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
                                        {
                                            cell.CellStyle = ManualAdjustCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.CellStyle = ManualAdjustCellStyle;
                                    }
                                }
                                else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_LOCATION))
                                {
                                    ETimeCardRecord timeCard = new ETimeCardRecord();
                                    try
                                    {
                                        timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID];
                                    }
                                    catch { }
                                    if (ETimeCardRecord.db.select(dbConn, timeCard))
                                    {
                                        if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
                                        {
                                            cell.CellStyle = ManualAdjustCellStyle;
                                        }
                                    }
                                    else
                                    {
                                        cell.CellStyle = ManualAdjustCellStyle;
                                    }
                                }
                            }
                        }
                        else if (column.DataType.Equals(typeof(double)) || column.DataType.Equals(typeof(float)))
                        {
                            //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
                            //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
                            if (row[column] != System.DBNull.Value)
                            {
                                double value = Convert.ToDouble(row[column].ToString());
                                if (value.Equals(double.NaN))
                                {
                                    cell.SetCellValue(string.Empty);
                                }
                                else
                                {
                                    cell.SetCellValue(value);
                                }
                            }
                            //cell.CellStyle = numericCellStyle;
                        }
                        else if (column.DataType.Equals(typeof(int)))
                        {
                            //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
                            //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
                            if (row[column] != System.DBNull.Value)
                            {
                                cell.SetCellValue(Convert.ToDouble(row[column].ToString()));
                            }
                            //cell.CellStyle = integerCellStyle;
                        }
                        else if (column.DataType.Equals(typeof(DateTime)))
                        {
                            //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.DateTime, "yyyy-MM-dd");
                            //if (row[column] == System.DBNull.Value)
                            //    cell.Value = string.Empty;
                            //else
                            //    cell.Value = (DateTime)row[column];
                            if (row[column] != System.DBNull.Value)
                            {
                                cell.SetCellValue((DateTime)row[column]);
                            }

                            cell.CellStyle = dateCellStyle;
                        }
                        else
                        {
                            //cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
                            //cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
                            if (row[column] != System.DBNull.Value)
                            {
                                cell.SetCellValue(row[column].ToString());
                            }
                        }
                        //worksheet.Cells[rowCount, columnCount] = cell;
                        columnCount++;
                    }
                }
                //                    worksheet.Cells.Rows.Add(rowCount, detailRow);
            }
            //workbook.Worksheets.Add(worksheet);

            return(worksheet);
        }
        else
        {
            return(null);
        }
    }
 /**
  * Returns the Formatted value of an Excel date as a <tt>String</tt> based
  * on the cell's <c>DataFormat</c>. i.e. "Thursday, January 02, 2003"
  * , "01/02/2003" , "02-Jan" , etc.
  *
  * @param cell The cell
  * @return a Formatted date string
  */
 private String GetFormattedDateString(HSSFCell cell)
 {
     FormatBase dateFormat = GetFormat(cell);
     DateTime d = cell.DateCellValue;
     if (dateFormat != null)
     {
         return dateFormat.Format(d);
     }
     return d.ToString();
 }
        /**
         * Create and return a FormatBase based on the FormatBase string from a  cell's
         * style. If the pattern cannot be Parsed, return a default pattern.
         *
         * @param cell The Excel cell
         * @return A FormatBase representing the excel FormatBase. May return null.
         */
        public FormatBase CreateFormat(HSSFCell cell)
        {

            int formatIndex = cell.CellStyle.DataFormat;
            String formatStr = cell.CellStyle.DataFormatString;
            return CreateFormat(cell.NumericCellValue, formatIndex, formatStr);
        }
Example #13
0
 /**
  * Returns a default FormatBase for a cell.
  * @param cell The cell
  * @return a default FormatBase
  */
 public FormatBase GetDefaultFormat(HSSFCell cell)
 {
     return(GetDefaultFormat(cell.NumericCellValue));
 }
Example #14
0
        /// <summary>
        /// Use this to create new cells within the row and return it.
        /// The cell that is returned is a CELL_TYPE_BLANK. The type can be changed
        /// either through calling setCellValue or setCellType.
        /// </summary>
        /// <param name="columnIndex">the column number this cell represents</param>
        /// <param name="type">a high level representation of the created cell.</param>
        /// <returns></returns>
        public ICell CreateCell(int columnIndex, CellType type)
        {
            short shortCellNum = (short)columnIndex;
            if (columnIndex > 0x7FFF)
            {
                shortCellNum = (short)(0xffff - columnIndex);
            }

            ICell cell = new HSSFCell(book, sheet, RowNum, (short)columnIndex, type);
            AddCell(cell);
            sheet.Sheet.AddValueRecord(RowNum, ((HSSFCell)cell).CellValueRecord);
            return cell;
        }
Example #15
0
 public void TestHSSFCell1()
 {
     HSSFWorkbook wb = new HSSFWorkbook();
     HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
     HSSFRow row = sheet.CreateRow(0) as HSSFRow;
     row.CreateCell(0);
     HSSFCell cell = new HSSFCell(wb, sheet, 0, (short)0);
     Assert.IsNotNull(cell);
 }
Example #16
0
    protected void btnGenerate_Click(object sender, EventArgs e)
    {
        const string FIELD_COMPANY               = "Company";
        const string FIELD_POSITION              = "Position";
        const string FIELD_PAYROLLGROUP          = "Payroll Group";
        const string FIELD_EMPNO                 = "EmployeeID";
        const string FIELD_EMPENGFULLNAME        = "English Name";
        const string FIELD_CHINESENAME           = "¤¤¤å©m¦W";
        const string FIELD_HKID                  = @"HKID/Passport";
        const string FIELD_PERIODFROM            = "From";
        const string FIELD_PERIODTO              = "To";
        const string FIELD_WAGESWORK             = "Wages Paid";
        const string FIELD_WORKHOURTOTAL         = "Total Working Hours";
        const string FIELD_RESTDAYTOTAL          = "No. of Rest Day";
        const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH";
        const string FIELD_FULLPAIDLEAVETOTAL    = "No. of Full Paid Leave";
        const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave";

        ArrayList list = new ArrayList();

        foreach (RepeaterItem i in Repeater.Items)
        {
            CheckBox cb = (CheckBox)i.FindControl("ItemSelect");
            if (cb.Checked)
            {
                EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject();
                WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb);
                list.Add(o);
            }
        }

        ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList();



        if (list.Count > 0 && payPeriodList.Count > 0)
        {
            //const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] ";
            string exportFileName = System.IO.Path.GetTempFileName();
            System.IO.File.Delete(exportFileName);
            exportFileName += ".xls";
            //System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true);
            HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
            DataSet   dataSet   = new DataSet(); //export.GetDataSet();
            DataTable dataTable = new DataTable("Payroll$");
            dataSet.Tables.Add(dataTable);
            dataTable.Columns.Add(FIELD_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(FIELD_POSITION, typeof(string));
            dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string));
            dataTable.Columns.Add(FIELD_EMPNO, typeof(string));
            dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string));
            dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string));
            dataTable.Columns.Add(FIELD_HKID, typeof(string));
            dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime));
            dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime));

            dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double));
            dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double));
            dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double));
            dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double));
            dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double));
            dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double));



            int firstSummaryColumnPos = dataTable.Columns.Count;
            int firstDetailColumnPos  = dataTable.Columns.Count;

            foreach (EPayrollPeriod payPeriod in payPeriodList)
            {
                if (EPayrollPeriod.db.select(dbConn, payPeriod))
                {
                    EPayrollGroup payrollGroup = new EPayrollGroup();
                    payrollGroup.PayGroupID = payPeriod.PayGroupID;
                    EPayrollGroup.db.select(dbConn, payrollGroup);

                    foreach (EEmpPersonalInfo empInfo in list)
                    {
                        EEmpPersonalInfo.db.select(dbConn, empInfo);
                        EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID);

                        DBFilter empPayrollFilter = new DBFilter();
                        empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID));
                        empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID));
                        if (PayrollStatus.SelectedValue.Equals("T"))
                        {
                            empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T"));
                        }
                        else
                        {
                            empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T"));
                        }

                        DataRow row = dataTable.NewRow();
                        row[FIELD_EMPNO]          = empInfo.EmpNo;
                        row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName;
                        row[FIELD_CHINESENAME]    = empInfo.EmpChiFullName;
                        row[FIELD_HKID]           = empInfo.EmpHKID;
                        row[FIELD_PERIODFROM]     = payPeriod.PayPeriodFr;
                        row[FIELD_PERIODTO]       = payPeriod.PayPeriodTo;
                        DBFilter empPosFilter = new DBFilter();

                        EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
                        if (empPos != null)
                        {
                            ECompany company = new ECompany();
                            company.CompanyID = empPos.CompanyID;
                            if (ECompany.db.select(dbConn, company))
                            {
                                row[FIELD_COMPANY] = company.CompanyCode;
                            }

                            EPosition position = new EPosition();
                            position.PositionID = empPos.PositionID;
                            if (EPosition.db.select(dbConn, position))
                            {
                                row[FIELD_POSITION] = position.PositionDesc;
                            }

                            DBFilter empHierarchyFilter = new DBFilter();
                            empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
                            ArrayList 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;
                                    }
                                }
                            }

                            EPayrollGroup curentPayGroup = new EPayrollGroup();
                            curentPayGroup.PayGroupID = empPos.PayGroupID;
                            if (EPayrollGroup.db.select(dbConn, curentPayGroup))
                            {
                                row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc;
                            }
                        }

                        double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0;
                        double mcER = 0, mcEE = 0;
                        double vcER = 0, vcEE = 0;
                        double pFundER = 0, pFundEE = 0;

                        double   wagesByWork          = 0;
                        double   wagesByRest          = 0;
                        double   fullPaidLeaveDays    = 0;
                        double   nonFullPaidLeaveDays = 0;
                        DBFilter paymentRecordFilter  = new DBFilter();
                        paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
                        paymentRecordFilter.add(new Match("PayRecStatus", "A"));
                        ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);

                        foreach (EPaymentRecord paymentRecord in paymentRecords)
                        {
                            EPaymentCode payCode = new EPaymentCode();
                            payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
                            EPaymentCode.db.select(dbConn, payCode);
                            //  Always Use Payment Code Description for grouping payment code with same description
                            //string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc;
                            //if (dataTable.Columns[fieldName] == null)
                            //    dataTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
                            //if (row[fieldName] == null || row[fieldName] == DBNull.Value)
                            //    row[fieldName] = 0;
                            //row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount;


                            netAmount += paymentRecord.PayRecActAmount;
                            if (payCode.PaymentCodeIsMPF)
                            {
                                releventIncome += paymentRecord.PayRecActAmount;
                            }
                            else
                            {
                                nonRelevantIncome += paymentRecord.PayRecActAmount;
                            }

                            DBFilter taxPaymentMapFilter = new DBFilter();
                            taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID));
                            if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0)
                            {
                                taxableAmount += paymentRecord.PayRecActAmount;
                            }
                            else
                            {
                                nonTaxableAmount += paymentRecord.PayRecActAmount;
                            }

                            if (payCode.PaymentCodeIsWages)
                            {
                                if (paymentRecord.PayRecIsRestDayPayment)
                                {
                                    wagesByRest += paymentRecord.PayRecActAmount;
                                }
                                else
                                {
                                    wagesByWork += paymentRecord.PayRecActAmount;
                                }
                            }
                        }


                        DBFilter mpfRecordFilter = new DBFilter();
                        mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
                        ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
                        foreach (EMPFRecord mpfRecord in mpfRecords)
                        {
                            vcER += mpfRecord.MPFRecActVCER;
                            mcER += +mpfRecord.MPFRecActMCER;
                            vcEE += mpfRecord.MPFRecActVCEE;
                            mcEE += mpfRecord.MPFRecActMCEE;
                        }
                        ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
                        foreach (EORSORecord orsoRecord in orsoRecords)
                        {
                            pFundER += orsoRecord.ORSORecActER;
                            pFundEE += orsoRecord.ORSORecActEE;
                        }
                        row[FIELD_WAGESWORK] = wagesByWork;

                        DBFilter workingSummaryFilter = new DBFilter();
                        workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
                        if (empTerm != null)
                        {
                            workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
                        }
                        else
                        {
                            workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo));
                        }
                        workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID));

                        ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter);

                        double workHourTotal = 0, restDayTotal = 0;


                        foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList)
                        {
                            workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours;
                            restDayTotal  += empWorkSummary.EmpWorkingSummaryRestDayEntitled;
                        }

                        row[FIELD_WORKHOURTOTAL] = workHourTotal;
                        row[FIELD_RESTDAYTOTAL]  = restDayTotal;

                        DBFilter statutoryHolidayFilter = new DBFilter();
                        statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
                        if (empTerm != null)
                        {
                            statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
                        }
                        else
                        {
                            statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo));
                        }

                        ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter);

                        double restDayCount = 0;
                        foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList)
                        {
                            restDayCount++;
                        }

                        row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount;

                        DBFilter LeaveAppEmpPayrollFilter = new DBFilter();
                        LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
                        ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter);
                        foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists)
                        {
                            ELeaveCode leaveCode = new ELeaveCode();
                            leaveCode.LeaveCodeID = leaveApp.LeaveCodeID;
                            if (ELeaveCode.db.select(dbConn, leaveCode))
                            {
                                if (leaveCode.LeaveCodePayRatio >= 1)
                                {
                                    fullPaidLeaveDays += leaveApp.LeaveAppDays;
                                }
                                else
                                {
                                    nonFullPaidLeaveDays += leaveApp.LeaveAppDays;
                                }
                            }
                        }
                        row[FIELD_FULLPAIDLEAVETOTAL]    = fullPaidLeaveDays;
                        row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays;

                        dataTable.Rows.Add(row);
                    }
                }
            }

            //DBFilter paymentCodeFilter = new DBFilter();
            //paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
            //paymentCodeFilter.add("PaymentCode", false);
            //ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
            //foreach (EPaymentCode paymentCode in paymentCodeList)
            //{
            //    if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc))
            //    {
            //        DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc];
            //        paymentColumn.SetOrdinal(firstDetailColumnPos);
            //        if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc))
            //            paymentColumn.ColumnName = paymentCode.PaymentCodeDesc;
            //        else
            //        {
            //            Console.Write("System reserved payment column is used");
            //        }
            //    }
            //}

            //for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
            //    dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);


            export.Update(dataSet);

            System.IO.FileStream             excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
            NPOI.HSSF.UserModel.HSSFWorkbook workbook        = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream);
            NPOI.HSSF.UserModel.HSSFSheet    workSheet       = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
            workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1);
            NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0);
            if (excelRow == null)
            {
                excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
            }
            NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0);
            if (excelCell == null)
            {
                excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0);
            }
            excelCell.SetCellValue("Statutory Minimum Wage Summary Report");

            excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
            workbook.Write(excelfileStream);
            excelfileStream.Close();

            WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
            return;
        }
        else
        {
            PageErrors errors = PageErrors.getErrors(db, Page.Master);
            errors.addError("Employee not selected");
        }
    }
Example #17
0
        /// <summary>
        /// Looks for text in the cell that should be unicode, like alpha; and provides the
        /// unicode version of it.
        /// </summary>
        /// <param name="cell">The cell to check for unicode values</param>
        /// <returns>transalted to unicode</returns>
        public static HSSFCell TranslateUnicodeValues(HSSFCell cell)
        {

            String s = cell.RichStringCellValue.String;
            bool foundUnicode = false;
            String lowerCaseStr = s.ToLower();

            for (int i = 0; i < unicodeMappings.Length; i++)
            {
                UnicodeMapping entry = unicodeMappings[i];
                String key = entry.entityName;
                if (lowerCaseStr.IndexOf(key) != -1)
                {
                    s = s.Replace(key, entry.resolvedValue);
                    foundUnicode = true;
                }
            }
            if (foundUnicode)
            {
                cell.SetCellValue(new HSSFRichTextString(s));
            }
            return cell;
        }
    private void GenerateRosterTableData(ArrayList EmpInfoList, int year, int month)
    {
        DateTime dateStart      = new DateTime(year, month, 1);
        DateTime dateEnd        = new DateTime(year, month, DateTime.DaysInMonth(year, month));
        string   exportFileName = System.IO.Path.GetTempFileName();

        System.IO.File.Delete(exportFileName);
        exportFileName += ".xls";

        const string FIELD_EMP_NO = "Emp. No";

        const int COLUMN_HEADER_ROW = 2;


        NPOI.HSSF.UserModel.HSSFWorkbook workBook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
        NPOI.HSSF.UserModel.HSSFSheet    workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("RosterTable");

        NPOI.HSSF.UserModel.HSSFCellStyle upperLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        upperLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;

        NPOI.HSSF.UserModel.HSSFCellStyle bottomLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        bottomLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;

        NPOI.HSSF.UserModel.HSSFCellStyle leftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        leftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;

        NPOI.HSSF.UserModel.HSSFCellStyle rightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        rightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;

        NPOI.HSSF.UserModel.HSSFCellStyle upperLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        upperLeftLineStyle.BorderTop  = NPOI.SS.UserModel.BorderStyle.THIN;
        upperLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;

        NPOI.HSSF.UserModel.HSSFCellStyle bottomLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        bottomLeftLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
        bottomLeftLineStyle.BorderLeft   = NPOI.SS.UserModel.BorderStyle.THIN;

        NPOI.HSSF.UserModel.HSSFCellStyle upperRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        upperRightLineStyle.BorderTop   = NPOI.SS.UserModel.BorderStyle.THIN;
        upperRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;

        NPOI.HSSF.UserModel.HSSFCellStyle bottomRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        bottomRightLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
        bottomRightLineStyle.BorderRight  = NPOI.SS.UserModel.BorderStyle.THIN;

        workSheet.CreateRow(0).CreateCell(0).SetCellValue("Year");
        workSheet.GetRow(0).CreateCell(1).SetCellValue(year);
        workSheet.CreateRow(1).CreateCell(0).SetCellValue("Month");
        workSheet.GetRow(1).CreateCell(1).SetCellValue(month);

        NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW);
        DBFilter hLevelFilter = new DBFilter();

        hLevelFilter.add("HLevelSeqNo", true);

        int       HIERARCHYLEVEL_COLUMN = 0;
        ArrayList hLevelList            = EHierarchyLevel.db.select(dbConn, hLevelFilter);

        for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++)
        {
            EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex];
            headerRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hLevel.HLevelDesc);
        }

        int POSITION_COLUMN = headerRow.LastCellNum;

        headerRow.CreateCell(POSITION_COLUMN).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Position"));
        int EMPNO_COLUMN = headerRow.LastCellNum;

        headerRow.CreateCell(EMPNO_COLUMN).SetCellValue(FIELD_EMP_NO);
        headerRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Name"));
        headerRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Alias"));
        headerRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Chinese Name"));

        NPOI.HSSF.UserModel.HSSFCellStyle sundayStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
        NPOI.HSSF.UserModel.HSSFFont      sundayFont  = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont();
        sundayFont.Color = NPOI.HSSF.Util.HSSFColor.RED.index;
        sundayStyle.SetFont(sundayFont);

        Hashtable styleList = new Hashtable();

        ArrayList availableRosterClientList     = new ArrayList();
        ArrayList availableRosterClientSiteList = new ArrayList();

        #region Create Column Header
        int ROSTER_DETAIL_COLUMN = headerRow.LastCellNum;

        for (int i = 1; i <= DateTime.DaysInMonth(year, month); i++)
        {
            //workSheet.Cells.Add(HEADER_ROW, ROSTAER_DETAIL_COLUMN + i - 1,i);
            NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(ROSTER_DETAIL_COLUMN + i - 1);
            headerCell.SetCellValue(i);
            if (new DateTime(year, month, i).DayOfWeek == DayOfWeek.Sunday)
            {
                headerCell.CellStyle = sundayStyle;
            }
        }
        #endregion
        #region Create Employee Roster Detail
        int recordCount = 0;
        foreach (EEmpPersonalInfo empInfo in EmpInfoList)
        {
            if (EEmpPersonalInfo.db.select(dbConn, empInfo))
            {
                recordCount++;

                //workSheet.Cells.Add(HEADER_ROW + recordCount, 1,empInfo.EmpNo);
                //workSheet.Cells.Add(HEADER_ROW + recordCount, 2,empInfo.EmpEngFullName);

                NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + recordCount);
                EEmpPositionInfo            empPos    = AppUtils.GetLastPositionInfo(dbConn, dateEnd, empInfo.EmpID);
                if (empPos != null)
                {
                    for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++)
                    {
                        EHierarchyLevel hLevel             = (EHierarchyLevel)hLevelList[levelIndex];
                        DBFilter        empHierarchyFilter = new DBFilter();
                        empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
                        empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID));
                        ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
                        if (empHierarchyList.Count > 0)
                        {
                            EEmpHierarchy     empHierarchy = (EEmpHierarchy)empHierarchyList[0];
                            EHierarchyElement hElement     = new EHierarchyElement();
                            hElement.HElementID = empHierarchy.HElementID;
                            if (EHierarchyElement.db.select(dbConn, hElement))
                            {
                                detailRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hElement.HElementDesc);
                            }
                        }
                    }

                    EPosition position = new EPosition();
                    position.PositionID = empPos.PositionID;
                    if (EPosition.db.select(dbConn, position))
                    {
                        detailRow.CreateCell(POSITION_COLUMN).SetCellValue(position.PositionDesc);
                    }
                }
                detailRow.CreateCell(EMPNO_COLUMN).SetCellValue(empInfo.EmpNo);
                detailRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(empInfo.EmpEngFullName);
                detailRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(empInfo.EmpAlias);
                detailRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(empInfo.EmpChiFullName);



                DBFilter rosterTableFilter = new DBFilter();
                rosterTableFilter.add(new Match("EmpID", empInfo.EmpID));
                rosterTableFilter.add(new Match("RosterTableDate", ">=", dateStart));
                rosterTableFilter.add(new Match("RosterTableDate", "<=", dateEnd));
                ArrayList rosterTableList = ERosterTable.db.select(dbConn, rosterTableFilter);
                foreach (ERosterTable rosterTable in rosterTableList)
                {
                    ERosterCode rosterCode = new ERosterCode();
                    rosterCode.RosterCodeID = rosterTable.RosterCodeID;
                    if (ERosterCode.db.select(dbConn, rosterCode))
                    {
                        string value = string.Empty;
                        //if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists ((ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)) )
                        //    value = workSheet.Rows[(ushort)(HEADER_ROW+ recordCount)].CellAtCol( (ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)).Value.ToString();
                        //if (string.IsNullOrEmpty(value))
                        //    workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,rosterCode.RosterCode);
                        //else
                        //    workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,value + "|" + rosterCode.RosterCode);

                        int cellColIndex = ROSTER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1;
                        NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex);
                        if (rosterCell == null)
                        {
                            rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex);
                        }
                        else
                        {
                            value = rosterCell.StringCellValue;
                        }
                        string rosterCodeValue = rosterCode.RosterCode;
                        if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0) || !rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0))
                        {
                            DateTime inTime  = rosterCode.RosterCodeInTime;
                            DateTime outTime = rosterCode.RosterCodeOutTime;
                            if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0))
                            {
                                inTime = rosterTable.RosterTableOverrideInTime;
                            }
                            if (!rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0))
                            {
                                outTime = rosterTable.RosterTableOverrideOutTime;
                            }
                            rosterCodeValue += "(" + inTime.ToString("HHmm") + "~" + outTime.ToString("HHmm") + ")";
                        }
                        if (string.IsNullOrEmpty(value))
                        {
                            rosterCell.SetCellValue(rosterCodeValue);
                            //if (!string.IsNullOrEmpty(rosterCode.RosterCodeColorCode))
                            //{
                            //    //System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode);
                            //    //System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color);
                            //    //rosterCell.CellStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex();
                            //    //rosterCell.CellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
                            //    //rosterCell.CellStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex();
                            //    string styleCode = "RosterCode" + "_" + rosterCode.RosterCode;
                            //    if (styleList.Contains(styleCode))
                            //        rosterCell.CellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)styleList[styleCode];
                            //    else
                            //    {
                            //        NPOI.HSSF.UserModel.HSSFCellStyle rosterCodeStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
                            //        System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode);
                            //        System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color);
                            //        rosterCodeStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex();
                            //        rosterCodeStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
                            //        rosterCodeStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex();
                            //        styleList.Add(styleCode, rosterCodeStyle);
                            //        rosterCell.CellStyle = rosterCodeStyle;
                            //    }
                            //}
                        }
                        else
                        {
                            rosterCell.SetCellValue(value + "|" + rosterCodeValue);
                            //rosterCell.CellStyle=workBook.GetCellStyleAt(0);
                        }
                    }
                }
                for (DateTime dateIndex = dateStart; dateIndex <= dateEnd; dateIndex = dateIndex.AddDays(1))
                {
                    string value = string.Empty;
                    //if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)) )
                    //    value = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)).Value.ToString();
                    int cellColIndex = ROSTER_DETAIL_COLUMN + dateIndex.Day - 1;
                    NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex);
                    if (rosterCell == null)
                    {
                        rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex);
                    }
                    else
                    {
                        value = rosterCell.StringCellValue;
                    }

                    if (string.IsNullOrEmpty(value))
                    {
                        EEmpPositionInfo empPosInfo = AppUtils.GetLastPositionInfo(dbConn, dateIndex, empInfo.EmpID);
                        if (empPosInfo != null)
                        {
                            if ((empPosInfo.EmpPosEffTo.Ticks.Equals(0) || dateIndex <= empPosInfo.EmpPosEffTo))
                            {
                                EWorkHourPattern workPattern = new EWorkHourPattern();
                                workPattern.WorkHourPatternID = empPosInfo.WorkHourPatternID;
                                if (EWorkHourPattern.db.select(dbConn, workPattern))
                                {
                                    ERosterCode rosterCode = new ERosterCode();
                                    rosterCode.RosterCodeID = workPattern.GetDefaultRosterCodeID(dbConn, dateIndex);
                                    if (ERosterCode.db.select(dbConn, rosterCode))
                                    {
                                        //workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1, rosterCode.RosterCode);
                                        rosterCell.SetCellValue(rosterCode.RosterCode);
                                    }
                                }

                                DBFilter empRosterTableGroupListFilter = new DBFilter();
                                empRosterTableGroupListFilter.add(new Match("EmpID", empInfo.EmpID));
                                empRosterTableGroupListFilter.add(new Match("empRosterTableGroupEffFr", "<=", dateIndex));
                                OR orEmpPosEffToTerms = new OR();
                                orEmpPosEffToTerms.add(new Match("empRosterTableGroupEffTo", ">=", dateIndex));
                                orEmpPosEffToTerms.add(new NullTerm("empRosterTableGroupEffTo"));
                                empRosterTableGroupListFilter.add(orEmpPosEffToTerms);
                                ArrayList empRosterTableGroupList = EEmpRosterTableGroup.db.select(dbConn, empRosterTableGroupListFilter);

                                foreach (EEmpRosterTableGroup empRosterTableGroup in empRosterTableGroupList)
                                {
                                    ERosterTableGroup rosterTableGroup = new ERosterTableGroup();
                                    rosterTableGroup.RosterTableGroupID = empRosterTableGroup.RosterTableGroupID;
                                    if (ERosterTableGroup.db.select(dbConn, rosterTableGroup))
                                    {
                                        if (rosterTableGroup.RosterClientSiteID > 0)
                                        {
                                            if (!availableRosterClientSiteList.Contains(rosterTableGroup.RosterClientSiteID))
                                            {
                                                availableRosterClientSiteList.Add(rosterTableGroup.RosterClientSiteID);
                                            }
                                        }
                                        else if (rosterTableGroup.RosterClientID > 0)
                                        {
                                            if (!availableRosterClientList.Contains(rosterTableGroup.RosterClientID))
                                            {
                                                availableRosterClientList.Add(rosterTableGroup.RosterClientID);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                DBFilter leaveAppFilter = new DBFilter();
                leaveAppFilter.add(new Match("EmpID", empInfo.EmpID));
                leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", dateStart));
                leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", dateEnd));
                ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter);
                foreach (ELeaveApplication leaveApp in leaveAppList)
                {
                    ELeaveCode leaveCode = new ELeaveCode();
                    leaveCode.LeaveCodeID = leaveApp.LeaveCodeID;
                    if (ELeaveCode.db.select(dbConn, leaveCode))
                    {
                        //if (leaveCode.LeaveCodeColorCode.Length == 6)
                        //{
                        //    try
                        //    {
                        //        int red = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(0, 2), System.Globalization.NumberStyles.AllowHexSpecifier);
                        //        int green = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(2, 2), System.Globalization.NumberStyles.AllowHexSpecifier);
                        //        int blue = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(4, 2), System.Globalization.NumberStyles.AllowHexSpecifier);

                        //        string Color = System.Drawing.Color.FromArgb(red, green, blue).ToKnownColor().ToString();

                        //        for (DateTime dateIndex = leaveApp.LeaveAppDateFrom; dateIndex <= leaveApp.LeaveAppDateTo; dateIndex = dateIndex.AddDays(1))
                        //        {
                        //            org.in2bits.MyXls.Cell cell = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1));
                        //            //cell.Pattern = 1;
                        //            //cell.PatternColor = org.in2bits.MyXls.Colors.Yellow;

                        //        }
                        //    }
                        //    catch
                        //    {
                        //    }

                        //}
                    }
                }
            }
        }
        #endregion
        #region Create Roster Code Liet
        OR orRosterCodeTerm = new OR();
        foreach (int rosterClientID in availableRosterClientList)
        {
            AND andRosterCodeTerms = new AND();
            orRosterCodeTerm.add(new Match("RosterClientID", rosterClientID));
        }
        foreach (int rosterClientSiteID in availableRosterClientSiteList)
        {
            AND andRosterCodeTerms = new AND();
            orRosterCodeTerm.add(new Match("RosterClientSiteID", rosterClientSiteID));
        }
        orRosterCodeTerm.add(new Match("RosterClientID", 0));
        DBFilter rosterCodeListFilter = new DBFilter();
        rosterCodeListFilter.add(orRosterCodeTerm);
        rosterCodeListFilter.add("RosterCode", true);
        ArrayList rosterCodeList = ERosterCode.db.select(dbConn, rosterCodeListFilter);

        int ROSTER_CODE_START_ROW = COLUMN_HEADER_ROW + recordCount + 5;
        int rosterCodeCount       = 0;
        int maxColumnCount        = 3;
        int columnCellWidth       = 9;
        int maxRowCount           = (int)(rosterCodeList.Count / maxColumnCount) + (rosterCodeList.Count % maxColumnCount == 0 ? 0 : 1);
        foreach (ERosterCode rosterCode in rosterCodeList)
        {
            int currentRowNum    = rosterCodeCount % maxRowCount;
            int currentColumnNum = (rosterCodeCount / maxRowCount) * columnCellWidth;

            rosterCodeCount++;

            NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(ROSTER_CODE_START_ROW + currentRowNum);
            if (rosterCodeRow == null)
            {
                rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(ROSTER_CODE_START_ROW + currentRowNum);
            }

            NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum);
            rosterCell.SetCellValue(rosterCode.RosterCode);

            rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum + 1);
            rosterCell.SetCellValue(rosterCode.RosterCodeDesc);

            if (rosterCodeCount.Equals(1))
            {
                rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN - 1);
                rosterCell.SetCellValue("Code:");
            }
        }

        for (int rowIdx = ROSTER_CODE_START_ROW - 1; rowIdx < ROSTER_CODE_START_ROW + maxRowCount + 1; rowIdx++)
        {
            NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowIdx);
            if (rosterCodeRow == null)
            {
                rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowIdx);
            }

            if (rowIdx == ROSTER_CODE_START_ROW - 1)
            {
                for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++)
                {
                    NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx);
                    if (rosterCell == null)
                    {
                        rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx);
                    }
                    if (colIdx == ROSTER_DETAIL_COLUMN - 1)
                    {
                        rosterCell.CellStyle = upperLeftLineStyle;
                    }
                    else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1)
                    {
                        rosterCell.CellStyle = upperRightLineStyle;
                    }
                    else
                    {
                        rosterCell.CellStyle = upperLineStyle;
                    }
                }
            }
            else if (rowIdx == ROSTER_CODE_START_ROW + maxRowCount)
            {
                for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++)
                {
                    NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx);
                    if (rosterCell == null)
                    {
                        rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx);
                    }
                    if (colIdx == ROSTER_DETAIL_COLUMN - 1)
                    {
                        rosterCell.CellStyle = bottomLeftLineStyle;
                    }
                    else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1)
                    {
                        rosterCell.CellStyle = bottomRightLineStyle;
                    }
                    else
                    {
                        rosterCell.CellStyle = bottomLineStyle;
                    }
                }
            }
            else
            {
                for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++)
                {
                    NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx);
                    if (rosterCell == null)
                    {
                        rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx);
                    }
                    if (colIdx == ROSTER_DETAIL_COLUMN - 1)
                    {
                        rosterCell.CellStyle = leftLineStyle;
                    }
                    else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1)
                    {
                        rosterCell.CellStyle = rightLineStyle;
                    }
                    //else
                    //    rosterCell.CellStyle = bottomLineStyle;
                }
            }
        }
        #endregion

        //doc.FileName = exportFileName;
        //doc.Save();
        System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
        workBook.Write(file);
        file.Close();

        WebUtils.TransmitFile(Response, exportFileName, "RosterTable_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
        Response.End();
    }
    private void GenerateExcelReport(DataSet dataSet, string exportFileName)
    {
        //ExcelLibrary.SpreadSheet.Worksheet execlWorksheet = null;

        DataView tmpView = new DataView(dataSet.Tables["Hierarchy"]);

        tmpView.Sort = "CompanyID, PayGroupID, HElementID Desc,CostCenterID";
        DataTable sortedHierarchyTable = tmpView.ToTable();

        int    curringCompanyID = 0;
        string currentCompany = string.Empty;
        int    recordCount = 0;
        int    lastRowIndex = 0, lastColumnIndex = 0;

        //ExcelLibrary.SpreadSheet.Workbook excelWorkbook = new ExcelLibrary.SpreadSheet.Workbook();
        //execlWorksheet = new ExcelLibrary.SpreadSheet.Worksheet("CostAllocation");
        //excelWorkbook.Worksheets.Add(execlWorksheet);
        NPOI.HSSF.UserModel.HSSFWorkbook excelWorkbook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
        NPOI.HSSF.UserModel.HSSFSheet    excelWorksheet = (NPOI.HSSF.UserModel.HSSFSheet)excelWorkbook.CreateSheet("CostAllocation");

        NPOI.HSSF.UserModel.HSSFRow headerRow           = null;

        NPOI.HSSF.UserModel.HSSFFont headerFont = (NPOI.HSSF.UserModel.HSSFFont)excelWorkbook.CreateFont();
        headerFont.Boldweight = 1;
        NPOI.HSSF.UserModel.HSSFCellStyle headerStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
        headerStyle.SetFont(headerFont);
        //headerStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THICK;

        NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
        numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");

        NPOI.HSSF.UserModel.HSSFFont subTotalFont = (NPOI.HSSF.UserModel.HSSFFont)excelWorkbook.CreateFont();
        subTotalFont.Boldweight = 1;
        NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
        subTotalStyle.SetFont(subTotalFont);
        //subTotalStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THICK;
        subTotalStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");

        foreach (DataRow row in sortedHierarchyTable.Rows)
        {
            if (curringCompanyID != (int)row["CompanyID"])
            {
                if (recordCount > 0)
                {
                    lastRowIndex++;

                    AddSubTotalRow(excelWorksheet, currentCompany, recordCount, lastRowIndex, lastColumnIndex, headerRow, subTotalStyle);
                    recordCount = 0;

                    lastRowIndex++;
                }
                //else
                {
                    //execlWorksheet.Cells[lastRowIndex, 0].Value = row["Company"].ToString();
                    //lastRowIndex += FIELD_HEADER_ROW;
                    //execlWorksheet.Cells[lastRowIndex, PAYROLL_GRUOP_COLUMN].Value = "Payroll Group";
                    //execlWorksheet.Cells[lastRowIndex, COST_CENTER_COLUMN].Value = "Cost Ctr";
                    //execlWorksheet.Cells[lastRowIndex, HIERARCHY_COLUMN].Value = "Hierarchy";
                    NPOI.HSSF.UserModel.HSSFRow companyRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
                    companyRow.CreateCell(0).SetCellValue(row["Company"].ToString());
                    lastRowIndex += FIELD_HEADER_ROW;

                    headerRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
                    headerRow.CreateCell(PAYROLL_GRUOP_COLUMN).SetCellValue("Payroll Group");
                    headerRow.CreateCell(COST_CENTER_COLUMN).SetCellValue("Cost Ctr");
                    headerRow.CreateCell(HIERARCHY_COLUMN).SetCellValue("Hierarchy");
                }
                curringCompanyID = (int)row["CompanyID"];
                currentCompany   = row["Company"].ToString();
            }
            lastRowIndex++;

            recordCount++;
            //lastRowIndex = FIELD_HEADER_ROW + recordCount;

            //execlWorksheet.Cells[lastRowIndex, PAYROLL_GRUOP_COLUMN].Value = row["PayrollGroupDesc"];
            //execlWorksheet.Cells[lastRowIndex, COST_CENTER_COLUMN].Value = row["CostCenterDesc"];
            //execlWorksheet.Cells[lastRowIndex, HIERARCHY_COLUMN].Value = row["HierarchyDesc"];
            NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
            detailRow.CreateCell(PAYROLL_GRUOP_COLUMN).SetCellValue(row["PayrollGroupDesc"].ToString());
            detailRow.CreateCell(COST_CENTER_COLUMN).SetCellValue(row["CostCenterDesc"].ToString());
            detailRow.CreateCell(HIERARCHY_COLUMN).SetCellValue(row["HierarchyDesc"].ToString());

            lastColumnIndex = PAYMENTDETAIL_START_COLUMN - 1;
            DataRow[] paymentRows = dataSet.Tables["Payment"].Select("ID=" + row["ID"]);
            if (paymentRows.Length > 0)
            {
                //double netPayment = 0;
                DataRow paymentRow = paymentRows[0];
                foreach (DataColumn column in paymentRow.Table.Columns)
                {
                    if (!column.ColumnName.Equals("ID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        lastColumnIndex++;
                        if (recordCount == 1)
                        {
                            //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex].Value = column.ColumnName;
                            headerRow.CreateCell(lastColumnIndex).SetCellValue(column.ColumnName);
                        }
                        if (!paymentRow.IsNull(column))
                        {
                            //ExcelLibrary.SpreadSheet.Cell cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex];
                            //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2);
                            //cell.FormatString = "#,##0.00";
                            //netPayment += HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2);
                            NPOI.HSSF.UserModel.HSSFCell dataCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex);
                            dataCell.CellStyle = numericStyle;
                            dataCell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2));
                        }
                    }
                }
                lastColumnIndex++;
                if (recordCount == 1)
                {
                    //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex].Value = "NET PAYMENT";
                    headerRow.CreateCell(lastColumnIndex).SetCellValue("NET PAYMENT");
                }
                //ExcelLibrary.SpreadSheet.Cell netPaymentCell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex];
                //netPaymentCell.FormatString = "#,##0.00";
                //netPaymentCell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netPayment, 2, 2);
                NPOI.HSSF.UserModel.HSSFCell netPaymentCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex);
                netPaymentCell.CellStyle   = numericStyle;
                netPaymentCell.CellFormula = "SUM(" + ToCellString(lastRowIndex, PAYMENTDETAIL_START_COLUMN) + ":" + ToCellString(lastRowIndex, lastColumnIndex - 1) + ")";
            }
            lastColumnIndex++;
            DataRow[] contributionRows = dataSet.Tables["contribution"].Select("ID=" + row["ID"]);
            if (contributionRows.Length > 0)
            {
                DataRow contributionRow = contributionRows[0];
                if (recordCount == 1)
                {
                    //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 1].Value = "MPF (ER)";
                    //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 2].Value = "VC (ER)";
                    //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 3].Value = "P-FUND (ER)";
                    //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 4].Value = "Total Contribution (ER)";
                    //execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 6].Value = "Total Contribution (EE)";
                    headerRow.CreateCell(lastColumnIndex + 1).SetCellValue("MPF (ER)");
                    headerRow.CreateCell(lastColumnIndex + 2).SetCellValue("VC (ER)");
                    headerRow.CreateCell(lastColumnIndex + 3).SetCellValue("P-FUND (ER)");
                    headerRow.CreateCell(lastColumnIndex + 4).SetCellValue("Total Contribution (ER)");
                    headerRow.CreateCell(lastColumnIndex + 6).SetCellValue("Total Contribution (EE)");
                }
                //ExcelLibrary.SpreadSheet.Cell cell=execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 1];
                //cell.FormatString = "#,##0.00";
                //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["MCER"], 2, 2);

                //cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 2];
                //cell.FormatString = "#,##0.00";
                //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["VCER"], 2, 2);

                //cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 3];
                //cell.FormatString = "#,##0.00";
                //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"], 2, 2);

                //cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 4];
                //cell.FormatString = "#,##0.00";
                //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"] + (double)contributionRow["MCER"] + (double)contributionRow["VCER"], 2, 2);

                //cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 6];
                //cell.FormatString = "#,##0.00";
                //cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDEE"] + (double)contributionRow["MCEE"] + (double)contributionRow["VCEE"], 2, 2);
                NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 1);
                cell.CellStyle = numericStyle;
                cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["MCER"], 2, 2));

                cell           = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 2);
                cell.CellStyle = numericStyle;
                cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["VCER"], 2, 2));

                cell           = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 3);
                cell.CellStyle = numericStyle;
                cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"], 2, 2));

                cell           = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 4);
                cell.CellStyle = numericStyle;
                cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"] + (double)contributionRow["MCER"] + (double)contributionRow["VCER"], 2, 2));

                cell           = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 6);
                cell.CellStyle = numericStyle;
                cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDEE"] + (double)contributionRow["MCEE"] + (double)contributionRow["VCEE"], 2, 2));
            }
            lastColumnIndex += 6;
        }
        lastRowIndex++;
        AddSubTotalRow(excelWorksheet, currentCompany, recordCount, lastRowIndex, lastColumnIndex, headerRow, subTotalStyle);
        //excelWorkbook.Save(exportFileName);
        System.IO.FileStream file                       = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
        excelWorkbook.Write(file);
        file.Close();
    }
Example #20
0
        /// <summary>
        /// Check if a cell Contains a date
        /// Since dates are stored internally in Excel as double values
        /// we infer it Is a date if it Is formatted as such.
        /// </summary>
        /// <param name="cell">The cell.</param>
        /// <returns>
        /// 	<c>true</c> if [is cell date formatted] [the specified cell]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsCellDateFormatted(HSSFCell cell)
        {
            if (cell == null) return false;
            bool bDate = false;

            double d = cell.NumericCellValue;
            if (HSSFDateUtil.IsValidExcelDate(d))
            {
                HSSFCellStyle style = cell.CellStyle;
                int i = style.DataFormat;
                String f = style.GetDataFormatString(cell.BoundWorkbook);
                bDate = IsADateFormat(i, f);
            }
            return bDate;
        }
Example #21
0
        /// <summary>
        /// Create a high level Cell object from an existing low level record.  Should
        /// only be called from HSSFSheet or HSSFRow itself.
        /// </summary>
        /// <param name="cell">The low level cell to Create the high level representation from</param>
        /// <returns> the low level record passed in</returns>
        public Cell CreateCellFromRecord(CellValueRecordInterface cell)
        {
            Cell hcell = new HSSFCell(book, sheet, cell);

            AddCell(hcell);
            int colIx = cell.Column;
            if (row.IsEmpty)
            {
                row.FirstCol=(colIx);
                row.LastCol=(colIx + 1);
            }
            else
            {
                if (colIx < row.FirstCol)
                {
                    row.FirstCol = (colIx);
                }
                else if (colIx > row.LastCol)
                {
                    row.LastCol = (colIx + 1);
                }
                else
                {
                    // added cell is within first and last cells
                }
            }
            return hcell;
        }
Example #22
0
        /// <summary>
        /// Use this to create new cells within the row and return it.
        /// The cell that is returned is a CELL_TYPE_BLANK. The type can be changed
        /// either through calling setCellValue or setCellType.
        /// </summary>
        /// <param name="columnIndex">the column number this cell represents</param>
        /// <param name="type">a high level representation of the created cell.</param>
        /// <returns></returns>
        public NPOI.SS.UserModel.Cell CreateCell(int columnIndex, NPOI.SS.UserModel.CellType type)
        {
            Cell cell = new HSSFCell(book, sheet, RowNum, (short)columnIndex, type);

            AddCell(cell);
            sheet.Sheet.AddValueRecord(RowNum, ((HSSFCell)cell).CellValueRecord);
            return cell;
        }
    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;
    }
Example #24
0
 /// <summary>
 /// Take a cell, and apply a font to it
 /// </summary>
 /// <param name="cell">the cell to Set the alignment for</param>
 /// <param name="workbook">The workbook that is being worked with.</param>
 /// <param name="font">The HSSFFont that you want to Set...</param>
 public static void SetFont(HSSFCell cell, HSSFWorkbook workbook, HSSFFont font)
 {
     SetCellStyleProperty(cell, workbook, FONT, font);
 }
Example #25
0
 /// <summary>Default constructor</summary>
 /// <param name="cell">NPOI native encapsulation of the cell.</param>
 public NpoiExcelCell(Npoi.HSSFCell cell)
 {
     _cell = cell;
 }
Example #26
0
        /// <summary>
        /// Check if a cell Contains a date, Checking only for internal
        /// excel date formats.
        /// As Excel stores a great many of its dates in "non-internal"
        /// date formats, you will not normally want to use this method.
        /// </summary>
        /// <param name="cell">The cell.</param>
        /// <returns>
        /// 	<c>true</c> if [is cell internal date formatted] [the specified cell]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsCellInternalDateFormatted(HSSFCell cell)
        {
            if (cell == null) return false;
            bool bDate = false;

            double d = cell.NumericCellValue;
            if (HSSFDateUtil.IsValidExcelDate(d))
            {
                HSSFCellStyle style = cell.CellStyle;
                int i = style.DataFormat;
                bDate = IsInternalDateFormat(i);
            }
            return bDate;
        }
Example #27
0
 /// <summary>Default constructor</summary>
 /// <param name="cell">NPOI native encapsulation of the cell.</param>
 public NpoiExcelCell(Npoi.HSSFCell cell)
 {
     _cell = cell;
 }
Example #28
0
 /// <summary>
 /// Take a cell, and align it.
 /// </summary>
 /// <param name="cell">the cell to Set the alignment for</param>
 /// <param name="workbook">The workbook that is being worked with.</param>
 /// <param name="align">the column alignment to use.</param>
 public static void SetAlignment(HSSFCell cell, HSSFWorkbook workbook, short align)
 {
     SetCellStyleProperty(cell, workbook, ALIGNMENT, align);
 }
Example #29
0
        /// <summary>
        /// Goes through the Wokrbook, optimising the cell styles
        /// by removing duplicate ones and ones that aren't used.
        /// For best results, optimise the fonts via a call to
        /// OptimiseFonts(HSSFWorkbook) first
        /// </summary>
        /// <param name="workbook">The workbook in which to optimise the cell styles</param>
        public static void OptimiseCellStyles(HSSFWorkbook workbook)
        {
            // Where each style has ended up, and if we need to
            //  delete the record for it. Start off with no change
            short[] newPos =
                new short[workbook.Workbook.NumExFormats];
            bool[] isUsed     = new bool[newPos.Length];
            bool[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                isUsed[i]     = false;
                newPos[i]     = (short)i;
                zapRecords[i] = false;
            }

            // Get each style record, so we can do deletes
            //  without Getting confused
            ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                xfrs[i] = workbook.Workbook.GetExFormatAt(i);
            }

            // Loop over each style, seeing if it is the same
            //  as an earlier one. If it is, point users of the
            //  later duplicate copy to the earlier one, and
            //  mark the later one as needing deleting
            // Only work on user added ones, which come after 20
            for (int i = 21; i < newPos.Length; i++)
            {
                // Check this one for being a duplicate
                //  of an earlier one
                int earlierDuplicate = -1;
                for (int j = 0; j < i && earlierDuplicate == -1; j++)
                {
                    ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
                    if (xfCheck.Equals(xfrs[i]))
                    {
                        earlierDuplicate = j;
                    }
                }

                // If we got a duplicate, mark it as such
                if (earlierDuplicate != -1)
                {
                    newPos[i]     = (short)earlierDuplicate;
                    zapRecords[i] = true;
                }
                // If we got a duplicate, mark the one we're keeping as used
                if (earlierDuplicate != -1)
                {
                    isUsed[earlierDuplicate] = true;
                }
            }
            // Loop over all the cells in the file, and identify any user defined
            //  styles aren't actually being used (don't touch built-in ones)
            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
                foreach (IRow row in s)
                {
                    foreach (ICell cellI in row)
                    {
                        HSSFCell cell  = (HSSFCell)cellI;
                        short    oldXf = cell.CellValueRecord.XFIndex;
                        isUsed[oldXf] = true;
                    }
                }
            }
            // Mark any that aren't used as needing zapping
            for (int i = 21; i < isUsed.Length; i++)
            {
                if (!isUsed[i])
                {
                    // Un-used style, can be removed
                    zapRecords[i] = true;
                    newPos[i]     = 0;
                }
            }
            // Update the new positions based on
            //  deletes that have occurred between
            //  the start and them
            // Only work on user added ones, which come after 20
            for (int i = 21; i < newPos.Length; i++)
            {
                // Find the number deleted to that
                //  point, and adjust
                short preDeletePos = newPos[i];
                short newPosition  = preDeletePos;
                for (int j = 0; j < preDeletePos; j++)
                {
                    if (zapRecords[j])
                    {
                        newPosition--;
                    }
                }

                // Update the new position
                newPos[i] = newPosition;
            }

            // Zap the un-needed user style records
            // removing by index, because removing by object may delete
            // styles we did not intend to (the ones that _were_ duplicated and not the duplicates)
            int max     = newPos.Length;
            int removed = 0; // to adjust index after deletion

            for (int i = 21; i < max; i++)
            {
                if (zapRecords[i + removed])
                {
                    workbook.Workbook.RemoveExFormatRecord(i);
                    i--;
                    max--;
                    removed++;
                }
            }

            // Finally, update the cells to point at their new extended format records
            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
                //IEnumerator rIt = s.GetRowEnumerator();
                //while (rIt.MoveNext())
                foreach (IRow row in s)
                {
                    //HSSFRow row = (HSSFRow)rIt.Current;
                    //IEnumerator cIt = row.GetEnumerator();
                    //while (cIt.MoveNext())
                    foreach (ICell cell in row)
                    {
                        //ICell cell = (HSSFCell)cIt.Current;
                        short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
                        NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
                            newPos[oldXf]
                            );
                        cell.CellStyle = (newStyle);
                    }
                }
            }
        }
Example #30
0
        /**
         *  This method attempt to find an already existing HSSFCellStyle that matches
         *  what you want the style to be. If it does not find the style, then it
         *  Creates a new one. If it does Create a new one, then it applies the
         *  propertyName and propertyValue to the style. This is necessary because
         *  Excel has an upper limit on the number of Styles that it supports.
         *
         *@param  workbook               The workbook that is being worked with.
         *@param  propertyName           The name of the property that is to be
         *      changed.
         *@param  propertyValue          The value of the property that is to be
         *      changed.
         *@param  cell                   The cell that needs it's style changes
         *@exception  NestableException  Thrown if an error happens.
         */
        public static void SetCellStyleProperty(HSSFCell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue)
        {
            HSSFCellStyle originalStyle = cell.CellStyle;
            HSSFCellStyle newStyle = null;
            Hashtable values = GetFormatProperties(originalStyle);
            values[propertyName] = propertyValue;

            // index seems like what  index the cellstyle is in the list of styles for a workbook.
            // not good to compare on!
            short numberCellStyles = workbook.NumCellStyles;

            for (short i = 0; i < numberCellStyles; i++)
            {
                HSSFCellStyle wbStyle = workbook.GetCellStyleAt(i);
                Hashtable wbStyleMap = GetFormatProperties(wbStyle);

                if (wbStyleMap.Equals(values))
                {
                    newStyle = wbStyle;
                    break;
                }
            }

            if (newStyle == null)
            {
                newStyle = workbook.CreateCellStyle();
                SetFormatProperties(newStyle, workbook, values);
            }

            cell.CellStyle = (newStyle);
        }
Example #31
0
    private void GenerateExcelReport(DataTable tmpDataTable, string exportFileName)
    {
        int columnCount  = 0;
        int lastRowIndex = 0;

        // Set column style
        NPOI.HSSF.UserModel.HSSFWorkbook   workbook      = new NPOI.HSSF.UserModel.HSSFWorkbook();
        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.LEFT;
        NPOI.HSSF.UserModel.HSSFSheet     worksheet    = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("AverageCostCentreExport");
        NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
        numericStyle.Alignment  = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
        NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;

        // Set column width
        worksheet.SetColumnWidth(0, 40 * 256);
        worksheet.SetColumnWidth(2, 20 * 256);
        worksheet.SetColumnWidth(5, 15 * 256);
        worksheet.SetColumnWidth(7, 15 * 256);
        worksheet.SetColumnWidth(8, 15 * 256);
        worksheet.SetColumnWidth(9, 15 * 256);
        worksheet.SetColumnWidth(10, 12 * 256);

        // Set column title
        NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex);
        headerRow.CreateCell(0).SetCellValue("Average Cost Centre Export");
        headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 1);
        headerRow.CreateCell(0).SetCellValue(peroid);
        headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 3);

        foreach (DataColumn headercolumn in tmpDataTable.Columns)
        {
            NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount);
            cell.SetCellValue(headercolumn.ColumnName);
            if (columnCount == 11)
            {
                cell.CellStyle = style;
            }
            columnCount++;
        }

        // Set value for every row
        foreach (DataRow row in tmpDataTable.Rows)
        {
            NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 4);

            detailRow.CreateCell(0).SetCellValue(row[FIELD_COMPANY].ToString());
            detailRow.CreateCell(1).SetCellValue(row[FIELD_DIVISION].ToString());
            detailRow.CreateCell(2).SetCellValue(row[FIELD_DEPARTMENT].ToString());
            detailRow.CreateCell(3).SetCellValue(row[FIELD_SECTION].ToString());
            detailRow.CreateCell(4).SetCellValue(row[FIELD_EMP_NO].ToString());
            detailRow.CreateCell(5).SetCellValue(row[FIELD_EMP_NAME].ToString());
            detailRow.CreateCell(6).SetCellValue(row[FIELD_ALIAS].ToString());
            detailRow.CreateCell(7).SetCellValue(row[FIELD_POSITION].ToString());

            NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8);
            cell.SetCellValue((DateTime)row[FIELD_FROM]);
            cell.CellStyle = dateCellStyle;

            cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9);
            cell.SetCellValue((DateTime)row[FIELD_TO]);
            cell.CellStyle = dateCellStyle;

            detailRow.CreateCell(10).SetCellValue(row[FIELD_COST_CENTER].ToString());
            cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11);
            cell.SetCellValue((double)row[FIELD_PERCENTAGE]);
            cell.CellStyle = numericStyle;

            lastRowIndex++;
        }

        System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
        workbook.Write(file);
        file.Close();
    }
        /* package */
        static void SetCellValue(HSSFCell cell, String text)
        {
            cell.SetCellValue(new HSSFRichTextString(text));

        }
 /**
  * 
  * Returns the Formatted value of a cell as a <tt>String</tt> regardless
  * of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
  * cell value will be Formatted using a default FormatBase.
  * 
  * When passed a null or blank cell, this method will return an empty
  * String (""). Formulas in formula type cells will not be evaluated.
  * 
  *
  * @param cell The cell
  * @return the Formatted cell value as a String
  */
 public String FormatCellValue(HSSFCell cell)
 {
     return FormatCellValue(cell, null);
 }
Example #34
0
 private static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, Boolean keepFormulas)
 {
     if (styleMap != null)
     {
         if (oldCell.CellStyle != null)
         {
             if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook)
             {
                 newCell.CellStyle = oldCell.CellStyle;
             }
             else
             {
                 int styleHashCode = oldCell.CellStyle.GetHashCode();
                 if (styleMap.ContainsKey(styleHashCode))
                 {
                     newCell.CellStyle = styleMap[styleHashCode];
                 }
                 else
                 {
                     HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle();
                     newCellStyle.CloneStyleFrom(oldCell.CellStyle);
                     RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually
                     newCell.CellStyle = newCellStyle;
                     //Clone of cell style always clones the font. This makes my life easier
                     IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook);
                     if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color))
                     {
                         theFont.Color = paletteMap[theFont.Color]; //Remap font color
                     }
                     styleMap.Add(styleHashCode, newCellStyle);
                 }
             }
         }
         else
         {
             newCell.CellStyle = null;
         }
     }
     switch (oldCell.CellType)
     {
         case NPOI.SS.UserModel.CellType.STRING:
             newCell.SetCellValue(oldCell.StringCellValue);
             break;
         case NPOI.SS.UserModel.CellType.NUMERIC:
             newCell.SetCellValue(oldCell.NumericCellValue);
             break;
         case NPOI.SS.UserModel.CellType.BLANK:
             newCell.SetCellType(NPOI.SS.UserModel.CellType.BLANK);
             break;
         case NPOI.SS.UserModel.CellType.BOOLEAN:
             newCell.SetCellValue(oldCell.BooleanCellValue);
             break;
         case NPOI.SS.UserModel.CellType.ERROR:
             newCell.SetCellValue(oldCell.ErrorCellValue);
             break;
         case NPOI.SS.UserModel.CellType.FORMULA:
             if (keepFormulas)
             {
                 newCell.SetCellType(CellType.FORMULA);
                 newCell.CellFormula = oldCell.CellFormula;
             }
             else
             {
                 try
                 {
                     newCell.SetCellType(CellType.NUMERIC);
                     newCell.SetCellValue(oldCell.NumericCellValue);
                 }
                 catch (Exception ex)
                 {
                     try
                     {
                         newCell.SetCellType(CellType.STRING);
                         newCell.SetCellValue(oldCell.StringCellValue);
                     }
                     catch (Exception exInner)
                     {
                     }
                 }
             }
             break;
         default:
             break;
     }
 }
        /**
         * 
         * Returns the Formatted value of a cell as a <tt>String</tt> regardless
         * of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
         * cell value will be Formatted using a default FormatBase.
         * 
         * When passed a null or blank cell, this method will return an empty
         * String (""). Formula cells will be evaluated using the given
         * {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
         * evaluator is null, then the formula String will be returned. The caller
         * is responsible for setting the currentRow on the evaluator
         *
         *
         * @param cell The cell (can be null)
         * @param evaluator The HSSFFormulaEvaluator (can be null)
         * @return a string value of the cell
         */
        public String FormatCellValue(HSSFCell cell, HSSFFormulaEvaluator evaluator)
        {

            if (cell == null)
            {
                return "";
            }

            int cellType = cell.CellType;
            if (evaluator != null && cellType == HSSFCell.CELL_TYPE_FORMULA)
            {
                try
                {
                    cellType = evaluator.EvaluateFormulaCell(cell);
                }
                catch (Exception e)
                {
                    throw new Exception("Did you forGet to set the current" +
                            " row on the HSSFFormulaEvaluator?", e);
                }
            }
            switch (cellType)
            {
                case HSSFCell.CELL_TYPE_BLANK:
                    return "";

                case HSSFCell.CELL_TYPE_FORMULA:
                    // should only occur if evaluator is null
                    return cell.CellFormula;

                case HSSFCell.CELL_TYPE_NUMERIC:

                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                    {
                        return GetFormattedDateString(cell);
                    }
                    return GetFormattedNumberString(cell);

                case HSSFCell.CELL_TYPE_STRING:
                    return cell.RichStringCellValue.String;

                case HSSFCell.CELL_TYPE_BOOLEAN:
                    return cell.BooleanCellValue?"TRUE":"FALSE";

                case HSSFCell.CELL_TYPE_ERROR:
                    return NPOI.HSSF.Record.Formula.Eval.ErrorEval.GetText(cell.ErrorCellValue);

            }
            throw new Exception("Unexpected celltype (" + cellType + ")");
        }
 static void FillContent(DataRow row, DataColumn column, HSSFRow xlsRow, HSSFCell newCell)
 {
     string drValue = row[column].ToString();
     switch (column.DataType.ToString())
     {
         case "System.String": //字符串类型
             string result = drValue;
             newCell.SetCellValue(result);
             break;
         case "System.DateTime": //日期类型
             DateTime dateV;
             DateTime.TryParse(drValue, out dateV);
             newCell.SetCellValue(dateV);
             break;
         case "System.Boolean": //布尔型
             bool boolV = false;
             bool.TryParse(drValue, out boolV);
             newCell.SetCellValue(boolV);
             break;
         case "System.Int16": //整型
         case "System.Int32":
         case "System.Int64":
         case "System.Byte":
             int intV = 0;
             int.TryParse(drValue, out intV);
             newCell.SetCellValue(intV);
             break;
         case "System.Decimal": //浮点型
         case "System.Double":
             double doubV = 0;
             double.TryParse(drValue, out doubV);
             newCell.SetCellValue(doubV);
             break;
         case "System.DBNull": //空值处理
             newCell.SetCellValue("");
             break;
         default:
             newCell.SetCellValue("");
             break;
     }
 }
Example #37
0
        protected bool IsTextEmpty(HSSFCell cell)
        {
            string value;
            switch (cell.CellType)
            {
                case CellType.STRING:
                    // XXX: enrich
                    value = cell.RichStringCellValue.String;
                    break;
                case CellType.FORMULA:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.STRING:
                            HSSFRichTextString str = cell.RichStringCellValue as HSSFRichTextString;
                            if (str == null || str.Length <= 0)
                                return false;

                            value = str.ToString();
                            break;
                        case CellType.NUMERIC:
                            HSSFCellStyle style = cell.CellStyle as HSSFCellStyle;
                            if (style == null)
                            {
                                return false;
                            }

                            value = (_formatter.FormatRawCellContents(cell.NumericCellValue, style.DataFormat, style.GetDataFormatString()));
                            break;
                        case CellType.BOOLEAN:
                            value = cell.BooleanCellValue.ToString();
                            break;
                        case CellType.ERROR:
                            value = ErrorEval.GetText(cell.ErrorCellValue);
                            break;
                        default:
                            value = string.Empty;
                            break;
                    }
                    break;
                case CellType.BLANK:
                    value = string.Empty;
                    break;
                case CellType.NUMERIC:
                    value = _formatter.FormatCellValue(cell);
                    break;
                case CellType.BOOLEAN:
                    value = cell.BooleanCellValue.ToString();
                    break;
                case CellType.ERROR:
                    value = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    return true;
            }

            return string.IsNullOrEmpty(value);
        }
Example #38
0
        /// <summary>
        /// Create a high level Cell object from an existing low level record.  Should
        /// only be called from HSSFSheet or HSSFRow itself.
        /// </summary>
        /// <param name="cell">The low level cell to Create the high level representation from</param>
        /// <returns> the low level record passed in</returns>
        public ICell CreateCellFromRecord(CellValueRecordInterface cell)
        {
            ICell hcell = new HSSFCell(book, sheet, cell);

            AddCell(hcell);
            int colIx = cell.Column;
            if (row.IsEmpty)
            {
                row.FirstCol=(colIx);
                row.LastCol=(colIx + 1);
            }
            else
            {
                if (colIx < row.FirstCol)
                {
                    row.FirstCol = (colIx);
                }
                else if (colIx > row.LastCol)
                {
                    row.LastCol = (colIx + 1);
                }
                else
                {
                    // added cell is within first and last cells
                }
            }
            // TODO - RowRecord column boundaries need to be updated for cell comments too
            return hcell;
        }
Example #39
0
        protected bool ProcessCell(HSSFCell cell, XmlElement tableCellElement,
                int normalWidthPx, int maxSpannedWidthPx, float normalHeightPt)
        {
            HSSFCellStyle cellStyle = cell.CellStyle as HSSFCellStyle;

            string value;
            switch (cell.CellType)
            {
                case CellType.STRING:
                    // XXX: enrich
                    value = cell.RichStringCellValue.String;
                    break;
                case CellType.FORMULA:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.STRING:
                            HSSFRichTextString str = cell.RichStringCellValue as HSSFRichTextString;
                            if (str != null && str.Length > 0)
                            {
                                value = (str.String);
                            }
                            else
                            {
                                value = string.Empty;
                            }
                            break;
                        case CellType.NUMERIC:
                            HSSFCellStyle style = cellStyle;
                            if (style == null)
                            {
                                value = cell.NumericCellValue.ToString();
                            }
                            else
                            {
                                value = (_formatter.FormatRawCellContents(cell.NumericCellValue, style.DataFormat, style.GetDataFormatString()));
                            }
                            break;
                        case CellType.BOOLEAN:
                            value = cell.BooleanCellValue.ToString();
                            break;
                        case CellType.ERROR:
                            value = ErrorEval.GetText(cell.ErrorCellValue);
                            break;
                        default:
                            logger.Log(POILogger.WARN, "Unexpected cell cachedFormulaResultType (" + cell.CachedFormulaResultType.ToString() + ")");
                            value = string.Empty;
                            break;
                    }
                    break;
                case CellType.BLANK:
                    value = string.Empty;
                    break;
                case CellType.NUMERIC:
                    value = _formatter.FormatCellValue(cell);
                    break;
                case CellType.BOOLEAN:
                    value = cell.BooleanCellValue.ToString();
                    break;
                case CellType.ERROR:
                    value = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    logger.Log(POILogger.WARN, "Unexpected cell type (" + cell.CellType.ToString() + ")");
                    return true;
            }

            bool noText = string.IsNullOrEmpty(value);
            bool wrapInDivs = !noText && UseDivsToSpan && !cellStyle.WrapText;

            short cellStyleIndex = cellStyle.Index;
            if (cellStyleIndex != 0)
            {
                HSSFWorkbook workbook = cell.Row.Sheet.Workbook as HSSFWorkbook;
                string mainCssClass = GetStyleClassName(workbook, cellStyle);
                if (wrapInDivs)
                {
                    tableCellElement.SetAttribute("class", mainCssClass + " "
                            + cssClassContainerCell);
                }
                else
                {
                    tableCellElement.SetAttribute("class", mainCssClass);
                }

                if (noText)
                {
                    /*
                     * if cell style is defined (like borders, etc.) but cell text
                     * is empty, add "&nbsp;" to output, so browser won't collapse
                     * and ignore cell
                     */
                    value = "\u00A0"; //“ ”全角空格
                }
            }

            if (OutputLeadingSpacesAsNonBreaking && value.StartsWith(" "))
            {
                StringBuilder builder = new StringBuilder();
                for (int c = 0; c < value.Length; c++)
                {
                    if (value[c] != ' ')
                        break;
                    builder.Append('\u00a0');
                }

                if (value.Length != builder.Length)
                    builder.Append(value.Substring(builder.Length));

                value = builder.ToString();
            }

            XmlText text = htmlDocumentFacade.CreateText(value);

            if (wrapInDivs)
            {
                XmlElement outerDiv = htmlDocumentFacade.CreateBlock();
                outerDiv.SetAttribute("class", this.cssClassContainerDiv);

                XmlElement innerDiv = htmlDocumentFacade.CreateBlock();
                StringBuilder innerDivStyle = new StringBuilder();
                innerDivStyle.Append("position:absolute;min-width:");
                innerDivStyle.Append(normalWidthPx);
                innerDivStyle.Append("px;");
                if (maxSpannedWidthPx != int.MaxValue)
                {
                    innerDivStyle.Append("max-width:");
                    innerDivStyle.Append(maxSpannedWidthPx);
                    innerDivStyle.Append("px;");
                }
                innerDivStyle.Append("overflow:hidden;max-height:");
                innerDivStyle.Append(normalHeightPt);
                innerDivStyle.Append("pt;white-space:nowrap;");
                ExcelToHtmlUtils.AppendAlign(innerDivStyle, cellStyle.Alignment);
                htmlDocumentFacade.AddStyleClass(outerDiv, "d", innerDivStyle.ToString());

                innerDiv.AppendChild(text);
                outerDiv.AppendChild(innerDiv);
                tableCellElement.AppendChild(outerDiv);
            }
            else
            {
                tableCellElement.AppendChild(text);
            }

            return string.IsNullOrEmpty(value) && cellStyleIndex == 0;
        }
        /**
         * Return a FormatBase for the given cell if one exists, otherwise try to
         * Create one. This method will return <c>null</c> if the any of the
         * following is true:
         * <ul>
         * <li>the cell's style is null</li>
         * <li>the style's data FormatBase string is null or empty</li>
         * <li>the FormatBase string cannot be recognized as either a number or date</li>
         * </ul>
         *
         * @param cell The cell to retrieve a FormatBase for
         * @return A FormatBase for the FormatBase String
         */
        private FormatBase GetFormat(HSSFCell cell)
        {
            if (cell.CellStyle == null)
            {
                return null;
            }

            int formatIndex = cell.CellStyle.DataFormat;
            String formatStr = cell.CellStyle.DataFormatString;
            if (formatStr == null || formatStr.Trim().Length == 0)
            {
                return null;
            }
            return GetFormat(cell.NumericCellValue, formatIndex, formatStr);
        }
 protected void WriteCellTypeValue(double Value, HSSFCell cell, bool hasStyle)
 {
     cell.SetCellType(CellType.NUMERIC);
     cell.SetCellValue(Value);
 }
 /**
  * Returns a default FormatBase for a cell.
  * @param cell The cell
  * @return a default FormatBase
  */
 public FormatBase GetDefaultFormat(HSSFCell cell)
 {
     return GetDefaultFormat(cell.NumericCellValue);
 }
 protected void WriteCellTypeValue(DateTime Value, HSSFCell cell, bool hasStyle)
 {
     cell.SetCellType(CellType.NUMERIC);
     if(!hasStyle)
         cell.CellStyle = _dateStyle;
     cell.SetCellValue(Value);
 }
        /**
         * Returns the Formatted value of an Excel number as a <tt>String</tt>
         * based on the cell's <c>DataFormat</c>. Supported Formats include
         * currency, percents, decimals, phone number, SSN, etc.:
         * "61.54%", "$100.00", "(800) 555-1234".
         *
         * @param cell The cell
         * @return a Formatted number string
         */
        private String GetFormattedNumberString(HSSFCell cell)
        {

            FormatBase numberFormat = GetFormat(cell);
            double d = cell.NumericCellValue;
            if (numberFormat == null)
            {
                return d.ToString();
            }
            return numberFormat.Format(d);
        }
 protected void WriteCellTypeValue(string Value, HSSFCell cell, bool hasStyle)
 {
     cell.SetCellValue(Value);
 }