コード例 #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;
    }
    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;
            }
        }
    }
コード例 #3
0
        /**
         * Loops over all cells in all sheets of the supplied
         *  workbook.
         * For cells that contain formulas, their formulas are
         *  Evaluated, and the results are saved. These cells
         *  remain as formula cells.
         * For cells that do not contain formulas, no Changes
         *  are made.
         * This is a helpful wrapper around looping over all
         *  cells, and calling EvaluateFormulaCell on each one.
         */
        public static void EvaluateAllFormulaCells(HSSFWorkbook wb)
        {
            for (int i = 0; i < wb.NumberOfSheets; i++)
            {
                NPOI.SS.UserModel.Sheet sheet     = wb.GetSheetAt(i);
                HSSFFormulaEvaluator    evaluator = new HSSFFormulaEvaluator(sheet, wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
                {
                    HSSFRow r = (HSSFRow)rit.Current;
                    //evaluator.SetCurrentRow(r);

                    for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext();)
                    {
                        NPOI.SS.UserModel.Cell c = (HSSFCell)cit.Current;
                        if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
                        {
                            evaluator.EvaluateFormulaCell(c);
                        }
                    }
                }
            }
        }
コード例 #4
0
ファイル: HSSFCellUtil.cs プロジェクト: ChiangHanLung/PIC_VDS
 /// <summary>
 /// Create a cell, and give it a value.
 /// </summary>
 /// <param name="row">the row to Create the cell in</param>
 /// <param name="column">the column index to Create the cell in</param>
 /// <param name="value">The value of the cell</param>
 /// <returns>A new HSSFCell.</returns>
 public static HSSFCell CreateCell(HSSFRow row, int column, String value)
 {
     return CreateCell(row, column, value, null);
 }
コード例 #5
0
 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;
     }
 }
コード例 #6
0
ファイル: HSSFOptimiser.cs プロジェクト: purehzj/npoi-1
        /// <summary>
        /// Goes through the Wokrbook, optimising the cell styles
        /// by removing duplicate ones.
        /// 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[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                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;
                }
            }

            // 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
            for (int i = 21; i < newPos.Length; i++)
            {
                if (zapRecords[i])
                {
                    workbook.Workbook.RemoveExFormatRecord(
                        xfrs[i]
                        );
                }
            }

            // 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())
                {
                    HSSFRow     row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetEnumerator();
                    while (cIt.MoveNext())
                    {
                        ICell cell  = (HSSFCell)cIt.Current;
                        short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
                        NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
                            newPos[oldXf]
                            );
                        cell.CellStyle = (newStyle);
                    }
                }
            }
        }
コード例 #7
0
ファイル: HSSFCellUtil.cs プロジェクト: ChiangHanLung/PIC_VDS
        /// <summary>
        /// Get a specific cell from a row. If the cell doesn't exist,
        /// </summary>
        /// <param name="row">The row that the cell is part of</param>
        /// <param name="column">The column index that the cell is in.</param>
        /// <returns>The cell indicated by the column.</returns>
        public static HSSFCell GetCell(HSSFRow row, int column)
        {
            HSSFCell cell = row.GetCell(column);

            if (cell == null)
            {
                cell = row.CreateCell(column);
            }
            return cell;
        }
コード例 #8
0
ファイル: ExcelToHtmlConverter.cs プロジェクト: ctddjyds/npoi
 private string GetRowName(HSSFRow row)
 {
     return (row.RowNum + 1).ToString();
 }
コード例 #9
0
ファイル: NPOIHelper.cs プロジェクト: alejsherion/gggets
        /// <summary>
        /// 插入Excel行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex"></param>
        /// <param name="count"></param>
        /// <param name="row"></param>
        private static void MyInsertRow(HSSFSheet sheet, int rowIndex, int count, HSSFRow row)
        {
            #region 批量移动行
            sheet.ShiftRows(
                                rowIndex,                                 //--开始行
                                sheet
                                .LastRowNum,                            //--结束行
                                count,                             //--移动大小(行数)--往下移动
                                true,                                   //是否复制行高
                                false,                                  //是否重置行高
                                true                                    //是否移动批注
                            );
            #endregion

            #region 对批量移动后空出的空行插,创建相应的行,并以插入行的上一行为格式源(即:插入行-1的那一行)
            for (int i = rowIndex; i < rowIndex + count - 1; i++)
            {
                HSSFRow targetRow;
                HSSFCell sourceCell;
                HSSFCell targetCell;

                targetRow = sheet.CreateRow(i + 1);
                for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
                {
                    sourceCell = row.GetCell(m);
                    if (sourceCell == null)
                        continue;
                    targetCell = targetRow.CreateCell(m);

                    targetCell.Encoding = sourceCell.Encoding;
                    targetCell.CellStyle = sourceCell.CellStyle;
                    targetCell.SetCellType(sourceCell.CellType);

                }
                //CopyRow(sourceRow, targetRow);

                //Util.CopyRow(sheet, sourceRow, targetRow);
            }

            HSSFRow firstTargetRow = sheet.GetRow(rowIndex);
            HSSFCell firstSourceCell;
            HSSFCell firstTargetCell = null;
            for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
            {
                firstSourceCell = row.GetCell(m);
                if (firstSourceCell == null)
                    continue;
                firstTargetCell = firstTargetRow.CreateCell(m);
                firstTargetCell.Encoding = firstSourceCell.Encoding;
                firstTargetCell.CellStyle = firstSourceCell.CellStyle;
                firstTargetCell.SetCellType(firstSourceCell.CellType);
            }
            sheet.AddMergedRegion(new Region(firstTargetRow.RowNum, 3, firstTargetRow.RowNum, 4));
            #endregion
        }
コード例 #10
0
 /// <summary>
 /// Créer une Cellule avec un Style en paramètre
 /// </summary>
 /// <param name="_sheet"></param>
 /// <param name="_cellStyle"></param>
 /// <param name="_numCol"></param>
 /// <param name="_numRow"></param>
 /// <param name="_text"></param>
 public void createCell(HSSFRow _row, HSSFCellStyle _cellStyle, int _numCol, string _text)
 {
     NPOI.SS.UserModel.ICell _newCell = _row.CreateCell(_numCol);
     _newCell.SetCellValue(_text);
     _newCell.CellStyle = _cellStyle;
 }
コード例 #11
0
    private void createWorkSheetHeaderRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet,
                                           List <EHierarchyLevel> hLevelList, int countDay, DateTime periodFromDate, DateTime periodToDate)
    {
        // Create column header styles
        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyle.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
        columnHeaderStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
        NPOI.HSSF.UserModel.HSSFFont columnHeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
        columnHeaderFont.Boldweight = 900;
        columnHeaderStyle.SetFont(columnHeaderFont);
        columnHeaderStyle.BorderTop    = NPOI.SS.UserModel.BorderStyle.THIN;
        columnHeaderStyle.BorderLeft   = NPOI.SS.UserModel.BorderStyle.THIN;
        columnHeaderStyle.BorderRight  = NPOI.SS.UserModel.BorderStyle.THIN;
        columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;

        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyleFirstTop.CloneStyleFrom(columnHeaderStyle);
        columnHeaderStyleFirstTop.BorderTop  = NPOI.SS.UserModel.BorderStyle.MEDIUM;
        columnHeaderStyleFirstTop.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;

        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyleMiddleTop.CloneStyleFrom(columnHeaderStyle);
        columnHeaderStyleMiddleTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;

        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyleLastTop.CloneStyleFrom(columnHeaderStyle);
        columnHeaderStyleLastTop.BorderTop   = NPOI.SS.UserModel.BorderStyle.MEDIUM;
        columnHeaderStyleLastTop.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;

        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyleFirstBottom.CloneStyleFrom(columnHeaderStyle);
        columnHeaderStyleFirstBottom.BorderLeft   = NPOI.SS.UserModel.BorderStyle.MEDIUM;
        columnHeaderStyleFirstBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;

        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyleMiddleBottom.CloneStyleFrom(columnHeaderStyle);
        columnHeaderStyleMiddleBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;

        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyleLastBottom.CloneStyleFrom(columnHeaderStyle);
        columnHeaderStyleLastBottom.BorderRight  = NPOI.SS.UserModel.BorderStyle.MEDIUM;
        columnHeaderStyleLastBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;

        // Create column header rows
        const int COLUMN_HEADER_ROW = 2;

        NPOI.HSSF.UserModel.HSSFRow  columnHeaderRow1 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW);
        NPOI.HSSF.UserModel.HSSFRow  columnHeaderRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + 1);
        NPOI.HSSF.UserModel.HSSFCell columnHeaderCell;

        int colpos = 0;

        foreach (EHierarchyLevel hLevel in hLevelList)
        {
            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
            columnHeaderCell.SetCellValue(hLevel.HLevelDesc);
            columnHeaderCell.CellStyle = columnHeaderStyleFirstTop;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
            columnHeaderCell.SetCellValue(string.Empty);
            columnHeaderCell.CellStyle = columnHeaderStyleFirstBottom;

            colpos++;
        }
        columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
        columnHeaderCell.SetCellValue("Name");
        columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
        columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
        columnHeaderCell.SetCellValue(string.Empty);
        columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;

        colpos++;

        columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
        columnHeaderCell.SetCellValue("Title");
        columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
        columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
        columnHeaderCell.SetCellValue(string.Empty);
        columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;

        colpos++;

        columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
        columnHeaderCell.SetCellValue("Staff No.");
        columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
        columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
        columnHeaderCell.SetCellValue(string.Empty);
        columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;

        colpos++;

        for (int colIdx = 0; colIdx < colpos; colIdx++)
        {
            workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colIdx, colIdx));
        }

        for (int dayIdx = 0; dayIdx < countDay; dayIdx++)
        {
            DateTime currentDate = periodFromDate.AddDays(dayIdx);

            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos + dayIdx);
            columnHeaderCell.SetCellValue(currentDate.ToString("dd-MMM-yy"));
            columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;

            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos + dayIdx);
            columnHeaderCell.SetCellValue(currentDate.ToString("ddd"));
            columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
        }
        colpos += countDay;

        columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
        columnHeaderCell.SetCellValue("Signature");
        columnHeaderCell.CellStyle = columnHeaderStyleLastTop;
        columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
        columnHeaderCell.SetCellValue(string.Empty);
        columnHeaderCell.CellStyle = columnHeaderStyleLastBottom;

        workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colpos, colpos));
    }
コード例 #12
0
ファイル: ExcelHelper.cs プロジェクト: XiaoQiJun/BPS
 /// <summary>
 /// 写入单元格
 /// </summary>
 public static HSSFCell WriteCell(HSSFRow row, object obj, int columnIndex)
 {
     HSSFCell cell = row.CreateCell(columnIndex);
     if (obj is string)
     {
         cell.SetCellValue((string)obj);
     }
     else if (obj is DateTime)
     {
         cell.SetCellValue(((DateTime)obj).ToShortDateString());
     }
     else if (obj is decimal)
     {
         cell.SetCellValue(Convert.ToDouble(obj));
     }
     else if (obj is bool)
     {
         cell.SetCellValue(obj.ToString());
     }
     else if (obj.GetType().IsValueType)
     {
         cell.SetCellValue(Convert.ToDouble(obj));
     }
     else
     {
         cell.SetCellValue(obj.ToString());
     }
     return cell;
 }
    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();
    }
コード例 #14
0
ファイル: HSSFSheet.cs プロジェクト: ChiangHanLung/PIC_VDS
        /// <summary>
        /// Remove a row from this sheet.  All cells contained in the row are Removed as well
        /// </summary>
        /// <param name="row">the row to Remove.</param>
        public void RemoveRow(HSSFRow row)
        {
            
            if (rows.Count > 0)
            {
                int key = row.RowNum;
                HSSFRow removedRow = (HSSFRow)rows[key];
                rows.Remove(key);

                if (removedRow != row)
                {
                    if (removedRow != null)
                    {
                        rows[key]= removedRow;
                    }
                    throw new InvalidOperationException("Specified row does not belong to this sheet");
                }

                if (row.RowNum == LastRowNum)
                {
                    lastrow = FindLastRow(lastrow);
                }
                if (row.RowNum == FirstRowNum)
                {
                    firstrow = FindFirstRow(firstrow);
                }

                sheet.RemoveRow(row.RowRecord);
            }
        }
コード例 #15
0
ファイル: HSSFSheet.cs プロジェクト: ChiangHanLung/PIC_VDS
        /// <summary>
        /// Create a new row within the sheet and return the high level representation
        /// </summary>
        /// <param name="rownum">The row number.</param>
        /// <returns></returns>
        /// @see org.apache.poi.hssf.usermodel.HSSFRow
        /// @see #RemoveRow(HSSFRow)
        public HSSFRow CreateRow(int rownum)
        {
            HSSFRow row = new HSSFRow(workbook, this, rownum);

            AddRow(row, true);
            return row;
        }
コード例 #16
0
    private void createWorkSheetDetailRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, int RowPos,
                                           List <string> hElementDescList, EEmpPersonalInfo empInfo, string positionTitle, int countDay, DateTime periodFromDate, int empID)
    {
        // Create column detail styles
        NPOI.HSSF.UserModel.HSSFCellStyle detailStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        detailStyle.BorderLeft   = NPOI.SS.UserModel.BorderStyle.THIN;
        detailStyle.BorderRight  = NPOI.SS.UserModel.BorderStyle.THIN;
        detailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;

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

        NPOI.HSSF.UserModel.HSSFCellStyle detailStyleFirst = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        detailStyleFirst.CloneStyleFrom(detailStyle);
        detailStyleFirst.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;

        NPOI.HSSF.UserModel.HSSFCellStyle detailStyleLast = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        detailStyleLast.CloneStyleFrom(detailStyle);
        detailStyleLast.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;

        // Create column detail rows
        const int DETAIL_START_ROW = 4;

        NPOI.HSSF.UserModel.HSSFRow detailRow  = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(DETAIL_START_ROW + RowPos);
        NPOI.HSSF.UserModel.HSSFRow detailRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(DETAIL_START_ROW + RowPos + 1);   // second row of record

        NPOI.HSSF.UserModel.HSSFCell detailCell;
        NPOI.HSSF.UserModel.HSSFCell detailCell2;
        int colpos = 0;

        foreach (string hElementDesc in hElementDescList)
        {
            workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
            detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
            detailCell.SetCellValue(hElementDesc);
            detailCell.CellStyle = detailStyleFirst;

            detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
            detailCell2.SetCellValue(hElementDesc);
            detailCell2.CellStyle = detailStyleFirst;

            colpos++;
        }

        workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
        detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
        detailCell.SetCellValue(empInfo.EmpEngFullName);
        detailCell.CellStyle = detailStyleFirst;

        detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
        detailCell2.SetCellValue(empInfo.EmpEngFullName);
        detailCell2.CellStyle = detailStyleFirst;

        colpos++;

        workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
        detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
        detailCell.SetCellValue(positionTitle);
        detailCell.CellStyle = detailStyle;

        detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
        detailCell2.SetCellValue(positionTitle);
        detailCell2.CellStyle = detailStyle;

        colpos++;

        workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
        detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
        detailCell.SetCellValue(empInfo.EmpNo);
        detailCell.CellStyle = detailStyle;

        detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
        detailCell2.SetCellValue(empInfo.EmpNo);
        detailCell2.CellStyle = detailStyle;

        colpos++;

        for (int dayIdx = 0; dayIdx < countDay; dayIdx++)
        {
            //first line of employee record
            detailCell           = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos + dayIdx);
            detailCell.CellStyle = detailStyleCenterAlignment;
            // second line of employee data
            detailCell2           = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos + dayIdx);
            detailCell2.CellStyle = detailStyleCenterAlignment;

            string currentTimeSpanRangeStr = getCurrentTimeSpanRangeStr(periodFromDate, dayIdx, empID);
            if (currentTimeSpanRangeStr != null)
            {
                detailCell.SetCellValue(currentTimeSpanRangeStr);
            }
            else
            {
                string currentRosterCodeDesc = getCurrentRosterCodeDesc(periodFromDate, dayIdx, empID);
                if (currentRosterCodeDesc != null)
                {
                    detailCell.SetCellValue(currentRosterCodeDesc);
                }
            }

            string s = getCurrentLeaveCodeDesc(periodFromDate, dayIdx, empID);
            if (s != "")
            {
                detailCell2.SetCellValue(s);
            }
        }

        colpos += countDay;
        workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));

        detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
        detailCell.SetCellValue(string.Empty);
        detailCell.CellStyle = detailStyleLast;

        detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
        detailCell2.SetCellValue(string.Empty);
        detailCell2.CellStyle = detailStyleLast;
    }
コード例 #17
0
        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
        {
            for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
            {
                //列名称
                string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
                HSSFCell newCell = null;
                System.Type rowType = drSource[columnsName].GetType();
                string drValue = drSource[columnsName].ToString().Trim();
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
                        HSSFDataFormat format = excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        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 = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + ":类型数据无法处理!"));
                }
            }
        }
コード例 #18
0
    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();
    }
コード例 #19
0
 /// <summary>
 /// Créer une Cellule avec un Style en paramètre
 /// </summary>
 /// <param name="_sheet"></param>
 /// <param name="_cellStyle"></param>
 /// <param name="_numCol"></param>
 /// <param name="_numRow"></param>
 /// <param name="_text"></param>
 public void createCellRichText(HSSFRow _row, HSSFCellStyle _cellStyle, int _numCol, string _text)
 {
     NPOI.SS.UserModel.ICell _newCell = _row.CreateCell(_numCol);
     HSSFRichTextString richString = new HSSFRichTextString(_text);
     _newCell.SetCellValue(richString);
     _cellStyle.WrapText = true;
     _newCell.CellStyle = _cellStyle;
 }
コード例 #20
0
ファイル: NPOIHelper.cs プロジェクト: alejsherion/gggets
 /// <summary>
 /// 删除行
 /// </summary>
 /// <param name="sheet"></param>
 /// <param name="row"></param>
 static void DeleteRow(HSSFSheet sheet,HSSFRow row)
 {
     if (row == null) return;
     row.RemoveAllCells();
     sheet.RemoveRow(row);
     sheet.ShiftRows(row.RowNum + 1, sheet.LastRowNum, -1);
 }
コード例 #21
0
 /// <summary>
 /// Créer une Cellule avec un Style en paramètre
 /// </summary>
 /// <param name="_sheet"></param>
 /// <param name="_cellStyle"></param>
 /// <param name="_numCol"></param>
 /// <param name="_numRow"></param>
 /// <param name="_text"></param>
 public void createCell(HSSFRow _row, HSSFCellStyle _cellStyle, int _numCol, int _text)
 {
     NPOI.SS.UserModel.ICell _newCell = _row.CreateCell(_numCol);
     if (_text > 0) { _newCell.SetCellValue(_text); }
     else { _newCell.SetCellValue(String.Empty); }
     _newCell.CellStyle = _cellStyle;
 }
コード例 #22
0
 public void SetProperty(HSSFRow row, int column)
 {
     HSSFCell cell = HSSFCellUtil.GetCell(row, column);
     HSSFCellUtil.SetCellStyleProperty(cell, _workbook, _propertyName, _propertyValue);
 }
コード例 #23
0
ファイル: ExcelToHtmlConverter.cs プロジェクト: ctddjyds/npoi
        /**
     * @return maximum 1-base index of column that were rendered, zero if none
     */
        protected int ProcessRow(CellRangeAddress[][] mergedRanges, HSSFRow row,
                XmlElement tableRowElement)
        {
            HSSFSheet sheet = (HSSFSheet)row.Sheet;
            int maxColIx = row.LastCellNum;
            if (maxColIx <= 0)
                return 0;

            List<XmlElement> emptyCells = new List<XmlElement>(maxColIx);

            if (OutputRowNumbers)
            {
                XmlElement tableRowNumberCellElement = htmlDocumentFacade.CreateTableHeaderCell();
                ProcessRowNumber(row, tableRowNumberCellElement);
                emptyCells.Add(tableRowNumberCellElement);
            }

            int maxRenderedColumn = 0;
            for (int colIx = 0; colIx < maxColIx; colIx++)
            {
                if (!OutputHiddenColumns && sheet.IsColumnHidden(colIx))
                    continue;

                CellRangeAddress range = ExcelToHtmlUtils.GetMergedRange(
                        mergedRanges, row.RowNum, colIx);

                if (range != null && (range.FirstColumn != colIx || range.FirstRow != row.RowNum))
                    continue;

                HSSFCell cell = (HSSFCell)row.GetCell(colIx);

                int divWidthPx = 0;
                if (UseDivsToSpan)
                {
                    divWidthPx = GetColumnWidth(sheet, colIx);

                    bool hasBreaks = false;
                    for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++)
                    {
                        if (!OutputHiddenColumns && sheet.IsColumnHidden(nextColumnIndex))
                            continue;

                        if (row.GetCell(nextColumnIndex) != null && !IsTextEmpty((HSSFCell)row.GetCell(nextColumnIndex)))
                        {
                            hasBreaks = true;
                            break;
                        }

                        divWidthPx += GetColumnWidth(sheet, nextColumnIndex);
                    }

                    if (!hasBreaks)
                        divWidthPx = int.MaxValue;
                }

                XmlElement tableCellElement = htmlDocumentFacade.CreateTableCell();

                if (range != null)
                {
                    if (range.FirstColumn != range.LastColumn)
                        tableCellElement.SetAttribute("colspan", (range.LastColumn - range.FirstColumn + 1).ToString());
                    if (range.FirstRow != range.LastRow)
                        tableCellElement.SetAttribute("rowspan", (range.LastRow - range.FirstRow + 1).ToString());
                }

                bool emptyCell;
                if (cell != null)
                {
                    emptyCell = ProcessCell(cell, tableCellElement, GetColumnWidth(sheet, colIx), divWidthPx, row.Height / 20f);
                }
                else
                {
                    emptyCell = true;
                }

                if (emptyCell)
                {
                    emptyCells.Add(tableCellElement);
                }
                else
                {
                    foreach (XmlElement emptyCellElement in emptyCells)
                    {
                        tableRowElement.AppendChild(emptyCellElement);
                    }
                    emptyCells.Clear();

                    tableRowElement.AppendChild(tableCellElement);
                    maxRenderedColumn = colIx;
                }
            }

            return maxRenderedColumn + 1;
        }
コード例 #24
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");
        }
    }
コード例 #25
0
ファイル: ExcelToHtmlConverter.cs プロジェクト: ctddjyds/npoi
 protected void ProcessRowNumber(HSSFRow row, XmlElement tableRowNumberCellElement)
 {
     tableRowNumberCellElement.SetAttribute("class", "rownumber");
     XmlText text = htmlDocumentFacade.CreateText(GetRowName(row));
     tableRowNumberCellElement.AppendChild(text);
 }
コード例 #26
0
ファイル: ExportExcel.cs プロジェクト: sylTest/WMS
        /// <summary>填充内容</summary>
        static void FillContent(HSSFRow hssfRow
            , DataColumn column, DataRow row
            , HSSFCellStyle contentStyle, HSSFFont contentFont
            , HSSFCellStyle contentDateStyle
            , HSSFCellStyle contentStyleDailyBalance, HSSFFont fontDailyBalance
            , string colHeadFont, short colHeadSize, short colHeadColor
            , short contentColor, bool contentBorder
            , string contentChangeColorFrom, short contentChangeColor
            , HSSFSheet sheet, string headTextStrA)
        {
            HSSFCell newCell = hssfRow.CreateCell(column.Ordinal) as HSSFCell;

            #region 当数据访问转换DataTime时生效
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            contentDateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
            #endregion

            string columnF = row[column].ToString();
            decimal i;
            bool b = decimal.TryParse(columnF, out i);

            #region 判断如果是仓库库存日结核对
            if (contentChangeColorFrom == "DailyBalance" && sheet == workbook.GetSheet(headTextStrA)
                    && ((column.Ordinal == 5 && b == false)
                    || (column.Ordinal == 6 && b == false)
                    || (column.Ordinal == 7 && b == false)
                    || (column.Ordinal == 8 && b == false)
                    || (column.Ordinal == 9 && b == false)
                    || (column.Ordinal == 10 && b == false)))
            {
                fontDailyBalance.FontName = colHeadFont;
                fontDailyBalance.FontHeightInPoints = colHeadSize;
                fontDailyBalance.Color = contentChangeColor;
                contentStyleDailyBalance.SetFont(fontDailyBalance);
                if (contentBorder == true)
                {
                    contentStyleDailyBalance.BorderBottom = BorderStyle.THIN;
                    contentStyleDailyBalance.BorderLeft = BorderStyle.THIN;
                    contentStyleDailyBalance.BorderRight = BorderStyle.THIN;
                    contentStyleDailyBalance.BorderTop = BorderStyle.THIN;
                }

                hssfRow.GetCell(column.Ordinal).CellStyle = contentStyleDailyBalance;
            }
            #endregion
            else
            {
                contentFont.FontName = colHeadFont;
                contentFont.FontHeightInPoints = colHeadSize;
                contentFont.Color = contentColor;
                contentStyle.SetFont(contentFont);
                //画边框
                if (contentBorder == true)
                {
                    contentStyle.BorderBottom = BorderStyle.THIN;
                    contentStyle.BorderLeft = BorderStyle.THIN;
                    contentStyle.BorderRight = BorderStyle.THIN;
                    contentStyle.BorderTop = BorderStyle.THIN;
                }
                hssfRow.GetCell(column.Ordinal).CellStyle = contentStyle;
            }
            string drValue = row[column].ToString();
            ChangeFormat(column,drValue,newCell,contentDateStyle);
        }
コード例 #27
0
    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;
    }
コード例 #28
0
        public ActionResult ExportReportExcel(DateTime selected_date)
        {
            String company_name = "DANGOTE CEMENT";

            List <view_message_dto> _sms_list = TempData["sms_list"] as List <view_message_dto>;
            // Opening the Excel template...
            FileStream fs =
                new FileStream(Server.MapPath(@"\Content\template\reporting_sms_List_Selected.xls"), FileMode.Open, FileAccess.Read);


            MemoryStream ms = new MemoryStream();
            // Getting the complete workbook...
            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

            try
            {
                // Getting the worksheet by its name...
                NPOI.HSSF.UserModel.HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("SmsList");
                //RO's Name

                NPOI.HSSF.UserModel.HSSFRow dataRow = (HSSFRow)sheet.GetRow(3);

                ICell cell_default = dataRow.Cells[0];

                ICellStyle boldStyle = cell_default.CellStyle;

                dataRow = (HSSFRow)sheet.GetRow(0);
                dataRow.Cells[0].SetCellValue(company_name);

                int Row = 3;
                foreach (view_message_dto item in _sms_list)
                {
                    dataRow = (HSSFRow)sheet.CreateRow(Row); //
                    dataRow.CreateCell(0).SetCellValue(Row - 2);
                    dataRow.CreateCell(1).SetCellValue(item.atc_no);
                    dataRow.CreateCell(2).SetCellValue(item.delivery_no);
                    dataRow.CreateCell(3).SetCellValue(item.customer_no);
                    dataRow.CreateCell(4).SetCellValue(item.phone_no);
                    dataRow.CreateCell(5).SetCellValue(item.waybill_no);
                    dataRow.CreateCell(6).SetCellValue(item.customer_name);
                    dataRow.CreateCell(7).SetCellValue(item.ip_address);
                    dataRow.CreateCell(8).SetCellValue(item.code_a);
                    dataRow.CreateCell(9).SetCellValue(item.code_b);
                    dataRow.CreateCell(10).SetCellValue(item.sms_status);
                    dataRow.CreateCell(11).SetCellValue(item.response);
                    dataRow.CreateCell(12).SetCellValue(item.create_datetime.ToString());
                    dataRow.CreateCell(13).SetCellValue(item.response_datetime.ToString());


                    if (Row % 2 == 0)
                    {
                        for (int i = 0; i < 5; i++)
                        {
                            ICell cell = dataRow.Cells[i];
                            cell.CellStyle = boldStyle;
                        }
                    }

                    Row++;
                }

                sheet.ForceFormulaRecalculation = true;


                TempData["Message"] = "Excel report created successfully!";
            }
            catch (Exception ex)
            {
                TempData["Message"] = "Oops! Something went wrong." + "<br/>" + ex.Message.ToString();
            }



            String FileName = "sms_list_report_" + selected_date.ToString("-yyyy-MM-dd") + ".xls";

            // Writing the workbook content to the FileStream...
            templateWorkbook.Write(ms);
            return(File(ms.ToArray(), "application/vnd.ms-excel", FileName));
        }
コード例 #29
0
ファイル: HSSFOptimiser.cs プロジェクト: purehzj/npoi-1
        /// <summary>
        /// Goes through the Workbook, optimising the fonts by
        /// removing duplicate ones.
        /// For now, only works on fonts used in HSSFCellStyle
        /// and HSSFRichTextString. Any other font uses
        /// (eg charts, pictures) may well end up broken!
        /// This can be a slow operation, especially if you have
        /// lots of cells, cell styles or rich text strings
        /// </summary>
        /// <param name="workbook">The workbook in which to optimise the fonts</param>
        public static void OptimiseFonts(HSSFWorkbook workbook)
        {
            // Where each font has ended up, and if we need to
            //  delete the record for it. Start off with no change
            short[] newPos =
                new short[workbook.Workbook.NumberOfFontRecords + 1];
            bool[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                newPos[i]     = (short)i;
                zapRecords[i] = false;
            }

            // Get each font record, so we can do deletes
            //  without Getting confused
            FontRecord[] frecs = new FontRecord[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                // There is no 4!
                if (i == 4)
                {
                    continue;
                }

                frecs[i] = workbook.Workbook.GetFontRecordAt(i);
            }

            // Loop over each font, 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
            // Note - don't change built in fonts (those before 5)
            for (int i = 5; 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++)
                {
                    if (j == 4)
                    {
                        continue;
                    }

                    FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
                    if (frCheck.SameProperties(frecs[i]))
                    {
                        earlierDuplicate = j;
                    }
                }

                // If we got a duplicate, mark it as such
                if (earlierDuplicate != -1)
                {
                    newPos[i]     = (short)earlierDuplicate;
                    zapRecords[i] = true;
                }
            }

            // Update the new positions based on
            //  deletes that have occurred between
            //  the start and them
            // Only need to worry about user fonts
            for (int i = 5; 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 font records
            for (int i = 5; i < newPos.Length; i++)
            {
                if (zapRecords[i])
                {
                    workbook.Workbook.RemoveFontRecord(
                        frecs[i]
                        );
                }
            }

            // Tell HSSFWorkbook that it needs to
            //  re-start its HSSFFontCache
            workbook.ResetFontCache();

            // Update the cell styles to point at the
            //  new locations of the fonts
            for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
            {
                ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
                xfr.FontIndex = (
                    newPos[xfr.FontIndex]
                    );
            }

            // Update the rich text strings to point at
            //  the new locations of the fonts
            // Remember that one underlying unicode string
            //  may be shared by multiple RichTextStrings!
            ArrayList doneUnicodeStrings = new ArrayList();

            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                NPOI.SS.UserModel.ISheet s = workbook.GetSheetAt(sheetNum);
                IEnumerator rIt            = s.GetRowEnumerator();
                while (rIt.MoveNext())
                {
                    HSSFRow     row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetEnumerator();
                    while (cIt.MoveNext())
                    {
                        ICell cell = (HSSFCell)cIt.Current;
                        if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
                        {
                            HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
                            UnicodeString      u   = rtr.RawUnicodeString;

                            // Have we done this string already?
                            if (!doneUnicodeStrings.Contains(u))
                            {
                                // Update for each new position
                                for (short i = 5; i < newPos.Length; i++)
                                {
                                    if (i != newPos[i])
                                    {
                                        u.SwapFontUse(i, newPos[i]);
                                    }
                                }

                                // Mark as done
                                doneUnicodeStrings.Add(u);
                            }
                        }
                    }
                }
            }
        }
コード例 #30
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();
    }
コード例 #31
0
ファイル: HSSFCellUtil.cs プロジェクト: ChiangHanLung/PIC_VDS
        /// <summary>
        /// Creates a cell, gives it a value, and applies a style if provided
        /// </summary>
        /// <param name="row">the row to Create the cell in</param>
        /// <param name="column">the column index to Create the cell in</param>
        /// <param name="value">The value of the cell</param>
        /// <param name="style">If the style is not null, then Set</param>
        /// <returns>A new HSSFCell</returns>
        public static HSSFCell CreateCell(HSSFRow row, int column, String value, HSSFCellStyle style)
        {
            HSSFCell cell = GetCell(row, column);

            cell.SetCellValue(new HSSFRichTextString(value));
            if (style != null)
            {
                cell.CellStyle = (style);
            }

            return cell;
        }
コード例 #32
0
ファイル: ExcelManager.cs プロジェクト: kapolb/Test
 private void SetRowFormula(HSSFRow row, string column, string formula)
 {
     row.GetCell(Convert.ToInt32(column)).CellFormula = formula;
 }
コード例 #33
0
ファイル: HSSFSheet.cs プロジェクト: babywzazy/Server
 /// <summary>
 /// Create a new row within the _sheet and return the high level representation
 /// </summary>
 /// <param name="rownum">The row number.</param>
 /// <returns></returns>
 /// @see org.apache.poi.hssf.usermodel.HSSFRow
 /// @see #RemoveRow(HSSFRow)
 public NPOI.SS.UserModel.Row CreateRow(int rownum)
 {
     HSSFRow row = new HSSFRow(_workbook, this, rownum);
     AddRow(row, true);
     return row;
 }
コード例 #34
0
ファイル: ExcelManager.cs プロジェクト: kapolb/Test
 private void SetRowNumericValue(HSSFRow row, string column, double value)
 {
     ((HSSFCell)row.GetCell(Convert.ToInt32(column))).SetCellValue(value);
 }
コード例 #35
0
        /// <summary>
        /// 利用office组件导出Excel表格文件
        /// </summary> 
        //public static void ExportExcelFile(DataTable ExcelTable)
        //{
        //    try
        //    {

        //        string SaveExcelName = string.Empty;//保存的Excel文件名称
        //        SaveFileDialog SFDialog = new SaveFileDialog();
        //        SFDialog.DefaultExt = "xls";
        //        SFDialog.Filter = "Excel文件(*.xls)|*.xls";
        //        SFDialog.ShowDialog();
        //        SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
        //        if (SaveExcelName.IndexOf(":") < 0) return;
        //        Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序

        //        object missing = System.Reflection.Missing.Value;
        //        if (XlsApp == null)
        //        {
        //            MessageBoxEx.Show("无法创建Excel表格文件,您的电脑可能未安装Excel软件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        //            return;
        //        }
        //        else
        //        {

        //            Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像
        //            Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板
        //            Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1;
        //            Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格
        //            int TotalCount = ExcelTable.Rows.Count;
        //            //int rowRead = 0;//读取行数
        //            //float PercentRead = 0;//导入百分比
        //            //写入字段名
        //            for (int i = 0; i < ExcelTable.Columns.Count; i++)
        //            {
        //                WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称
        //                Ran = (Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1, i + 1];//列名称写入单元格
        //                Ran.Interior.ColorIndex = 15;
        //                Ran.Font.Bold = true;//标题加粗

        //            }

        //            //ProgressBarMsg ProgBarMsg = new ProgressBarMsg();
        //            //ProgBarMsg.MaxNum = TotalCount;//获取总记录行项
        //            //ProgBarMsg.ShowDialog();//显示进度条
        //            //写字段值
        //            for (int j = 0; j < ExcelTable.Rows.Count; j++)
        //            {
        //                for (int k = 0; k < ExcelTable.Columns.Count; k++)
        //                {
        //                    WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值
        //                }
        //                //rowRead++;
        //                //PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比
        //                //ProgressBarMsg.PercentMsg = rowRead;
        //                //Thread.Sleep(200);
        //                Application.DoEvents();//处理当前在消息队列中所有windows消息
        //            }

        //            WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing, missing);
        //            Ran = WkSheet.get_Range((object)WkSheet.Cells[2, 1], (object)WkSheet.Cells[ExcelTable.Rows.Count + 1, ExcelTable.Columns.Count]);//给工作表指定区域

        //            //设置Excel表格边框样式
        //            Ran.BorderAround2(missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
        //            Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, missing, missing);
        //            Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色
        //            Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框
        //            Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度

        //            if (ExcelTable.Columns.Count > 1)
        //            {//设置垂直表格颜色索引
        //                Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
        //            }
        //            WkBk.Save();//保存Excel表数据
        //            //关闭表格对像,并退出应用程序域
        //            WkBk.Close(missing, missing, missing);
        //            XlsApp.Quit();
        //            XlsApp = null;
        //            GC.Collect();//强制关闭
        //            MessageBoxEx.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);

        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
        //    }
        //}
        /// <summary>
        /// 设置导出Excel标题样式
        /// </summary>
        /// <param name="HeaderRow">标题行</param>
        /// <param name="WkBk">工作表</param>
        /// <param name="ColNum">列序号</param>
        private static void SetXlsHeaderStyle(HSSFRow HeaderRow, HSSFWorkbook WkBk, int ColNum)
        {
            try
            {
                HSSFCellStyle HeaderStyle = (HSSFCellStyle)WkBk.CreateCellStyle();//创建Excel表格标题样式
                HSSFFont fontStyle = (HSSFFont)WkBk.CreateFont();//创建Excel表格字体样式
                HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//设置居中
                HeaderStyle.FillForegroundColor = HSSFColor.Lime.Index;//设置背景色
                HeaderStyle.FillPattern = FillPattern.SolidForeground;//设置填充样式
                HeaderStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0");//设置数据格式
                fontStyle.FontHeightInPoints = 11;
                fontStyle.Boldweight = 700;
                HeaderStyle.SetFont(fontStyle);
                HeaderRow.GetCell(ColNum).CellStyle = (ICellStyle)HeaderStyle;//设置列样式

            }
            catch (Exception ex)
            {
                MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
            }

        }
コード例 #36
0
ファイル: ExcelManager.cs プロジェクト: kapolb/Test
 private void SetRowStringValue(HSSFRow row, string column, string value)
 {
     ((HSSFCell)row.GetCell(Convert.ToInt32(column))).SetCellValue(value);
 }
コード例 #37
0
ファイル: TestHSSFRow.cs プロジェクト: Reinakumiko/npoi
        public void TestLastAndFirstColumns_bug46654()
        {
            int ROW_IX = 10;
            int COL_IX = 3;
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
            RowRecord rowRec = new RowRecord(ROW_IX);
            rowRec.FirstCol=((short)2);
            rowRec.LastCol=((short)5);

            BlankRecord br = new BlankRecord();
            br.Row=(ROW_IX);
            br.Column=((short)COL_IX);

            sheet.Sheet.AddValueRecord(ROW_IX, br);
            HSSFRow row = new HSSFRow(workbook,sheet, rowRec);
            ICell cell = row.CreateCellFromRecord(br);

            if (row.FirstCellNum == 2 && row.LastCellNum == 5)
            {
                throw new AssertionException("Identified bug 46654a");
            }
            Assert.AreEqual(COL_IX, row.FirstCellNum);
            Assert.AreEqual(COL_IX + 1, row.LastCellNum);
            row.RemoveCell(cell);
            Assert.AreEqual(-1, row.FirstCellNum);
            Assert.AreEqual(-1, row.LastCellNum);
        }
コード例 #38
0
ファイル: HSSFSheet.cs プロジェクト: bluebirdtech/npoi
        /**
         * Add a row to the _sheet
         *
         * @param AddLow whether to Add the row to the low level model - false if its already there
         */

        private void AddRow(HSSFRow row, bool addLow)
        {
            rows[row.RowNum] = row;

            if (addLow)
            {
                _sheet.AddRow(row.RowRecord);
            }
            bool firstRow = rows.Count == 1;
            if (row.RowNum > LastRowNum || firstRow)
            {
                lastrow = row.RowNum;
            }
            if (row.RowNum < FirstRowNum || firstRow)
            {
                firstrow = row.RowNum;
            }
        }
コード例 #39
0
    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);
        }
    }
コード例 #40
0
ファイル: HSSFSheet.cs プロジェクト: bluebirdtech/npoi
        private static void CopyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, bool keepFormulas)
        {
            List<SS.Util.CellRangeAddress> mergedRegions = destSheet.Sheet.MergedRecords.MergedRegions;
            destRow.Height = srcRow.Height;
            destRow.IsHidden = srcRow.IsHidden;
            destRow.RowRecord.OptionFlags = srcRow.RowRecord.OptionFlags;
            for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
            {
                HSSFCell oldCell = (HSSFCell)srcRow.GetCell(j);
                HSSFCell newCell = (HSSFCell)destRow.GetCell(j);
                if (srcSheet.Workbook == destSheet.Workbook)
                {
                    newCell = (HSSFCell)destRow.GetCell(j);
                }
                if (oldCell != null)
                {
                    if (newCell == null)
                    {
                        newCell = (HSSFCell)destRow.CreateCell(j);
                    }
                    HSSFCellUtil.CopyCell(oldCell, newCell, styleMap, paletteMap, keepFormulas);
                    CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum, (short)oldCell.ColumnIndex);
                    if (mergedRegion != null)
                    {
                        CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
                                mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);

                        if (IsNewMergedRegion(newMergedRegion, mergedRegions))
                        {
                            mergedRegions.Add(newMergedRegion);
                        }
                    }
                }
            }
        }
コード例 #41
0
ファイル: HSSFSheet.cs プロジェクト: bluebirdtech/npoi
        /// <summary>
        /// Used internally to Create a high level Row object from a low level row object.
        /// USed when Reading an existing file
        /// </summary>
        /// <param name="row">low level record to represent as a high level Row and Add to _sheet.</param>
        /// <returns>HSSFRow high level representation</returns>
        private HSSFRow CreateRowFromRecord(RowRecord row)
        {
            HSSFRow hrow = new HSSFRow(_workbook, this, row);

            AddRow(hrow, false);
            return hrow;
        }
コード例 #42
0
ファイル: HSSFSheet.cs プロジェクト: bluebirdtech/npoi
 /// <summary>
 /// Create a new row within the _sheet and return the high level representation
 /// </summary>
 /// <param name="rownum">The row number.</param>
 /// <returns></returns>
 /// @see org.apache.poi.hssf.usermodel.HSSFRow
 /// @see #RemoveRow(HSSFRow)
 public NPOI.SS.UserModel.IRow CreateRow(int rownum)
 {
     HSSFRow row = new HSSFRow(_workbook, this, rownum);
     // new rows inherit default height from the sheet
     row.Height = (DefaultRowHeight);
     row.RowRecord.BadFontHeight = (false);
     AddRow(row, true);
     return row;
 }
コード例 #43
0
ファイル: HSSFSheet.cs プロジェクト: bluebirdtech/npoi
 private void NotifyRowShifting(HSSFRow row)
 {
     String msg = "Row[rownum=" + row.RowNum + "] contains cell(s) included in a multi-cell array formula. " +
             "You cannot change part of an array.";
     foreach (ICell cell in row.Cells)
     {
         HSSFCell hcell = (HSSFCell)cell;
         if (hcell.IsPartOfArrayFormulaGroup)
         {
             hcell.NotifyArrayFormulaChanging(msg);
         }
     }
 }
コード例 #44
-1
        public static bool CheckValidDataRow(HSSFRow row, int startColIndex, int endColIndex)
        {
            for (int i = startColIndex; i < endColIndex; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null && cell.CellType != NPOI.SS.UserModel.CellType.BLANK)
                {
                    return true;
                }
            }

            return false;
        }