protected void ApplyPercentageFormatting(int column, int rowFromInclusive, int rowtoInclusive)
 {
     string colString = CellReference.ConvertNumToColString(column);
     string range = String.Format("{0}{1}:{0}{2}", colString, rowFromInclusive, rowtoInclusive);
     var region = new CellRangeAddress[] { CellRangeAddress.ValueOf(range) };
     sheet.SheetConditionalFormatting.AddConditionalFormatting(region, PercentageFormattingRules);
 }
        private void CreateAdresseArmoire()
        {
            var merge = new CellRangeAddress(6, 7, 1, 5);
            _cadreCreator.Create(6, 1, 7, 5, new byte[3] { 255, 255, 255 }, BorderStyle.Medium, merge, true, true);

            _sheet.GetRow(6).GetCell(1).SetCellValue(" - 69400 VILLEFRANCHE-SUR-SAONE");
        }
Example #3
0
        public void Test_47701()
        {
            byte[] data = HexRead.ReadFromString(
                            "15, 00, 12, 00, 12, 00, 02, 00, 11, 20, " +
                            "00, 00, 00, 00, 80, 3D, 03, 05, 00, 00, " +
                            "00, 00, 0C, 00, 14, 00, 00, 00, 00, 00, " +
                            "00, 00, 00, 00, 00, 00, 01, 00, 0A, 00, " +
                            "00, 00, 10, 00, 01, 00, 13, 00, EE, 1F, " +
                            "10, 00, 09, 00, 40, 9F, 74, 01, 25, 09, " +
                            "00, 0C, 00, 07, 00, 07, 00, 07, 04, 00, " +
                            "00, 00, 08, 00, 00, 00");
            RecordInputStream in1 = TestcaseRecordInputStream.Create(ObjRecord.sid, data);
            // check read OK
            ObjRecord record = new ObjRecord(in1);
            Assert.AreEqual(3, record.SubRecords.Count);
            SubRecord sr = record.SubRecords[(2)];
            Assert.IsTrue(sr is LbsDataSubRecord);
            LbsDataSubRecord lbs = (LbsDataSubRecord)sr;
            Assert.AreEqual(4, lbs.NumberOfItems);

            Assert.IsTrue(lbs.Formula is AreaPtg);
            AreaPtg ptg = (AreaPtg)lbs.Formula;
            CellRangeAddress range = new CellRangeAddress(
                    ptg.FirstRow, ptg.LastRow, ptg.FirstColumn, ptg.LastColumn);
            Assert.AreEqual("H10:H13", range.FormatAsString());

            // check that it re-Serializes to the same data
            byte[] ser = record.Serialize();
            TestcaseRecordInputStream.ConfirmRecordEncoding(ObjRecord.sid, data, ser);
        }
Example #4
0
        /**
         * Intersect this range with the specified range.
         * 
         * @param crB - the specified range
         * @return code which reflects how the specified range is related to this range.<br/>
         * Possible return codes are:	
         * 		NO_INTERSECTION - the specified range is outside of this range;<br/> 
         * 		OVERLAP - both ranges partially overlap;<br/>
         * 		INSIDE - the specified range is inside of this one<br/>
         * 		ENCLOSES - the specified range encloses (possibly exactly the same as) this range<br/>
         */
        public static int Intersect(CellRangeAddress crA, CellRangeAddress crB)
        {

            int firstRow = crB.FirstRow;
            int lastRow = crB.LastRow;
            int firstCol = crB.FirstColumn;
            int lastCol = crB.LastColumn;

            if
            (
                    gt(crA.FirstRow, lastRow) ||
                    lt(crA.LastRow, firstRow) ||
                    gt(crA.FirstColumn, lastCol) ||
                    lt(crA.LastColumn, firstCol)
            )
            {
                return NO_INTERSECTION;
            }
            else if (Contains(crA, crB))
            {
                return INSIDE;
            }
            else if (Contains(crB, crA))
            {
                return ENCLOSES;
            }
            else
            {
                return OVERLAP;
            }

        }
Example #5
0
        private void CreateAdresse()
        {
            var merge = new CellRangeAddress(6, 7, BeginCadreInformation, BeginCadreInformation + LongeurCadre);
            _cadreCreator.Create(6, BeginCadreInformation, 7, BeginCadreInformation + LongeurCadre, new byte[3] { 255, 255, 255 }, BorderStyle.Medium, merge, true, true);

            _sheet.GetRow(6).GetCell(BeginCadreInformation).SetCellValue(" - 69400 VILLEFRANCHE-SUR-SAONE");
        }
Example #6
0
        public FeatRecord(RecordInputStream in1)
        {
            futureHeader = new FtrHeader(in1);

            isf_sharedFeatureType = in1.ReadShort();
            reserved1 = (byte)in1.ReadByte();
            reserved2 = in1.ReadInt();
            int cref = in1.ReadUShort();
            cbFeatData = in1.ReadInt();
            reserved3 = in1.ReadShort();

            cellRefs = new CellRangeAddress[cref];
            for (int i = 0; i < cellRefs.Length; i++)
            {
                cellRefs[i] = new CellRangeAddress(in1);
            }

            switch (isf_sharedFeatureType)
            {
                case FeatHdrRecord.SHAREDFEATURES_ISFPROTECTION:
                    sharedFeature = new FeatProtection(in1);
                    break;
                case FeatHdrRecord.SHAREDFEATURES_ISFFEC2:
                    sharedFeature = new FeatFormulaErr2(in1);
                    break;
                case FeatHdrRecord.SHAREDFEATURES_ISFFACTOID:
                    sharedFeature = new FeatSmartTag(in1);
                    break;
                default:
                    System.Console.WriteLine("Unknown Shared Feature " + isf_sharedFeatureType + " found!");
                    break;
            }
        }
Example #7
0
        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet sheet = hssfworkbook.CreateSheet("new sheet");

            IRow row = sheet.CreateRow(0);
            row.HeightInPoints = 30;

            ICell cell = row.CreateCell(0);
            //set the title of the sheet
            cell.SetCellValue("Sales Report");

           ICellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;
            //create a font style
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20*20;
            style.SetFont(font);
            cell.CellStyle = style;
            
            //merged cells on single row
            //ATTENTION: don't use Region class, which is obsolete
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));

            //merged cells on mutiple rows
            CellRangeAddress region = new CellRangeAddress(2, 4, 2, 4);
            sheet.AddMergedRegion(region);

            //set enclosed border for the merged region
            ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.DOTTED, NPOI.HSSF.Util.HSSFColor.RED.index);

            WriteToFile();
        }
Example #8
0
        private void CreateRow(IEnumerable<Cell> row, Row currentRow)
        {
            int columnOrdinal = 0;

            foreach (var cell in row)
            {
                if (cell.ColumnSpan > 1)
                {
                    int rangeStartColumn = columnOrdinal;

                    for (int i = 0; i < cell.ColumnSpan; i++)
                    {
                        NPOI.SS.UserModel.Cell current = CreateCell(cell, currentRow, columnOrdinal);
                        if (i == 0) current.SetCellValue(cell.Value);
                        columnOrdinal++;
                    }

                    var cra = new CellRangeAddress(currentRow.RowNum, currentRow.RowNum, rangeStartColumn,
                                                   rangeStartColumn + (cell.ColumnSpan - 1));
                    _sheet.AddMergedRegion(cra);
                }
                else
                {
                    CreateCell(cell, currentRow, columnOrdinal).SetCellValue(cell.Value);
                    columnOrdinal++;
                }
            }
        }
Example #9
0
 public CFHeaderRecord(CellRangeAddress[] regions, int nRules)
 {
     CellRangeAddress[] unmergedRanges = regions;
     CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(unmergedRanges);
     CellRanges= mergeCellRanges;
     field_1_numcf = nRules;
 }
Example #10
0
    /// <summary>
    /// 合并单元格
    /// </summary>
    /// <param name="sh">工作簿</param>
    /// <param name="firstRow">开始行号</param>
    /// <param name="lastRow">结束行号</param>
    /// <param name="firstCol">开始列号</param>
    /// <param name="lastCol">结束列号</param>
    /// <returns></returns>
    public static NPOI.SS.Util.CellRangeAddress CellRange(ISheet sh, int firstRow, int lastRow, int firstCol, int lastCol)
    {
        NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sh.AddMergedRegion(region);

        return(region);
    }
Example #11
0
        public CFHeaderRecord(RecordInputStream in1)
        {
            field_1_numcf = in1.ReadShort();
            field_2_need_recalculation = in1.ReadShort();
            field_3_enclosing_cell_range = new CellRangeAddress(in1);
            field_4_cell_ranges = new CellRangeAddressList(in1);

        }
Example #12
0
 /**
  *  Check if the specified range is located inside of this cell range.
  *
  * @param crB
  * @return true if this cell range Contains the argument range inside if it's area
  */
 public static bool Contains(CellRangeAddress crA, CellRangeAddress crB)
 {
     int firstRow = crB.FirstRow;
     int lastRow = crB.LastRow;
     int firstCol = crB.FirstColumn;
     int lastCol = crB.LastColumn;
     return le(crA.FirstRow, firstRow) && ge(crA.LastRow, lastRow)
             && le(crA.FirstColumn, firstCol) && ge(crA.LastColumn, lastCol);
 }
        private void CreateAdresseChambrePB(ref int hauteur)
        {
            var bleu = new byte[3] { 153, 204, 255 };
            var endHauteur = hauteur + 1;
            var merge = new CellRangeAddress(hauteur, endHauteur, 20, 23);
            _cadreCreator.Create(hauteur, 20, endHauteur, 23, bleu, BorderStyle.Medium, merge, true);

            _sheet.GetRow(hauteur).GetCell(20).SetCellValue(" - 69400 VILLEFRANCHE-SUR-SAONE");
            hauteur = hauteur + 2;
        }
Example #14
0
        public void TestLoad()
        {
            CellRangeAddress cref = new CellRangeAddress(
                 TestcaseRecordInputStream.Create(0x000, data)
           );
            Assert.AreEqual(2, cref.FirstRow);
            Assert.AreEqual(4, cref.LastRow);
            Assert.AreEqual(0, cref.FirstColumn);
            Assert.AreEqual(3, cref.LastColumn);

            Assert.AreEqual(8, CellRangeAddress.ENCODED_SIZE);
        }
Example #15
0
        /**
         * Constructs a MergedCellsRecord and Sets its fields appropriately
         * @param in the RecordInputstream to Read the record from
         */

        public MergeCellsRecord(RecordInputStream in1)
        {
            int nRegions = in1.ReadUShort();
    	    CellRangeAddress[] cras = new CellRangeAddress[nRegions];
    	    for (int i = 0; i < nRegions; i++) 
            {
			    cras[i] = new CellRangeAddress(in1);
		    }
    	    _numberOfRegions = nRegions;
    	    _startIndex = 0;
    	    _regions = cras;
        }
Example #16
0
        public void Render(ICell cell)
        {
            NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
            ISheet        sheet  = cell.Sheet;
            IDrawing      draw   = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
            IClientAnchor anchor = region != null?
                                   draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
                                       PicArea != null?
                                       draw.CreateAnchor(20, 20, 0, 0, PicArea.ColIndex, PicArea.RowIndex, PicArea.ColIndex + PicArea.ColCount, PicArea.RowIndex + PicArea.RowCount) :
                                           draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);

            draw.CreatePicture(anchor, sheet.Workbook.AddPicture(PicSource, PictureType.JPEG));
        }
        //[Obsolete]
        //public static void SetBorderLeft(NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
        //        HSSFWorkbook workbook)
        //{
        //    SetBorderLeft(border, toCRA(region), sheet, workbook);
        //}
        /// <summary>
        /// Sets the left border for a region of cells by manipulating the cell style
        /// of the individual cells on the left
        /// </summary>
        /// <param name="border">The new border</param>
        /// <param name="region">The region that should have the border</param>
        /// <param name="sheet">The sheet that the region is on.</param>
        /// <param name="workbook">The workbook that the region is on.</param>
        public static void SetBorderLeft(NPOI.SS.UserModel.CellBorderType border, CellRangeAddress region, HSSFSheet sheet,
                HSSFWorkbook workbook)
        {
            int rowStart = region.FirstRow;
            int rowEnd = region.LastRow;
            int column = region.FirstColumn;

            CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_LEFT, (int)border);
            for (int i = rowStart; i <= rowEnd; i++)
            {
                cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
            }
        }
Example #18
0
        /**
         * Sets the left border for a region of cells by manipulating the cell style of the individual
         * cells on the left
         *
         * @param border The new border
         * @param region The region that should have the border
         * @param workbook The workbook that the region is on.
         * @param sheet The sheet that the region is on.
         */
        public static void SetBorderLeft(int border, CellRangeAddress region, ISheet sheet,
                IWorkbook workbook)
        {
            int rowStart = region.FirstRow;
            int rowEnd = region.LastRow;
            int column = region.FirstColumn;

            CellPropertySetter cps = new CellPropertySetter(workbook, CellUtil.BORDER_LEFT, border);
            for (int i = rowStart; i <= rowEnd; i++)
            {
                cps.SetProperty(CellUtil.GetRow(i, sheet), column);
            }
        }
        public static CellRangeAddress MergeCellsRange(this ISheet worksheet, int firstRow, int lastRow, string firstColumn, string lastColumn)
        {
            if (worksheet.LastRowNum < lastRow)
            {
                worksheet.CreateRow(lastRow);
            }
            int iFirstColumn = firstColumn.ToCharArray().Select(c => c - 'A' + 1).Reverse().Select((v, i) => v * (int)Math.Pow(26, i)).Sum() - 1;
            int iLastColumn  = lastColumn.ToCharArray().Select(c => c - 'A' + 1).Reverse().Select((v, i) => v * (int)Math.Pow(26, i)).Sum() - 1;

            var cra1 = new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, iFirstColumn, iLastColumn);

            worksheet.AddMergedRegion(cra1);
            return(cra1);
        }
        public void Create(
            byte[] color,
            int line,
            int beginLongeurCadre,
            int endLongeurCadre,
            string[] values,
            bool isMerged,
            BorderStyle tailleBorder,
            bool withCadre = true,
            byte[] fontColor = null)
        {
            if(fontColor == null)
            {
                fontColor = new byte[3] { 0, 0, 0 };
            }

            _tailleBorder = tailleBorder;

            var merged = new CellRangeAddress(line, line, beginLongeurCadre, endLongeurCadre);

            var longeur = beginLongeurCadre;

            foreach (var value in values)
            {
                var cell = _sheet.GetRow(line).GetCell(longeur);

                var style = CreateStyle(color, fontColor);

                if (withCadre)
                {
                    SetStyleWithCadre(longeur, beginLongeurCadre, endLongeurCadre, style);
                }
                else
                {
                    SetStyleWithoutCadre(longeur, beginLongeurCadre, endLongeurCadre, style);
                }

                cell.CellStyle = style;

                if (isMerged)
                {
                    _sheet.AddMergedRegion(merged);
                }

                cell.SetCellValue(value);

                longeur++;
            }
        }
Example #21
0
        /**
         * Create an enclosing CellRange for the two cell ranges.
         *
         * @return enclosing CellRange
         */
        public static CellRangeAddress CreateEnclosingCellRange(CellRangeAddress crA, CellRangeAddress crB)
        {
            if (crB == null)
            {
                return crA.Copy();
            }

            return
                new CellRangeAddress(
                    lt(crB.FirstRow, crA.FirstRow) ? crB.FirstRow : crA.FirstRow,
                    gt(crB.LastRow, crA.LastRow) ? crB.LastRow : crA.LastRow,
                    lt(crB.FirstColumn, crA.FirstColumn) ? crB.FirstColumn : crA.FirstColumn,
                    gt(crB.LastColumn, crA.LastColumn) ? crB.LastColumn : crA.LastColumn
                );
        }
Example #22
0
        public void Create(
            int beginHauteurCadre,
            int beginLongeurCadre,
            int endHauteurCadre,
            int endLongeurCadre,
            byte[] color,
            BorderStyle tailleBorder,
            CellRangeAddress merge = null,
            bool wrapText = false,
            bool center = false,
            bool forcedRightBorder = false)
        {
            _tailleBorder = tailleBorder;

            if (merge != null)
            {
                _sheet.AddMergedRegion(merge);
            }

            for (var hauteur = beginHauteurCadre; hauteur <= endHauteurCadre; hauteur++)
            {
                var row = _sheet.GetRow(hauteur);

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

                for (var longeur = beginLongeurCadre; longeur <= endLongeurCadre; longeur++)
                {
                    var cell = row.CreateCell(longeur);

                    var style = CreateStyle(color);
                    SetStyle(hauteur, longeur, beginHauteurCadre, beginLongeurCadre, endHauteurCadre, endLongeurCadre, style, forcedRightBorder);

                    style.WrapText = wrapText;

                    if (center)
                    {
                        style.VerticalAlignment = VerticalAlignment.Center;
                        style.Alignment = HorizontalAlignment.Center;
                    }

                    cell.CellStyle = style;
                }
            }
        }
Example #23
0
        public void testMCTable_bug46009()
        {
            MergedCellsTable mct = new MergedCellsTable();
            ArrayList recList = new ArrayList();
            CellRangeAddress[] cras = new CellRangeAddress[] {
				new CellRangeAddress(0, 0, 0, 3), 
		};
            recList.Add(new MergeCellsRecord(cras, 0, 1));
            RecordStream rs = new RecordStream(recList, 0);
            mct.Read(rs);
            try
            {
                mct.VisitContainedRecords(dummyRecordVisitor);
            }
            catch (Exception)
            {
                throw new AssertionException("Identified bug 46009");
            }
        }
Example #24
0
        public void TestStore()
        {
            CellRangeAddress cref = new CellRangeAddress(0, 0, 0, 0);

            byte[] recordBytes;
            //ByteArrayOutputStream baos = new ByteArrayOutputStream();
            MemoryStream baos = new MemoryStream();
            LittleEndianOutputStream output = new LittleEndianOutputStream(baos);
            try
            {
                // With nothing set
                cref.Serialize(output);
                recordBytes = baos.ToArray();
                Assert.AreEqual(recordBytes.Length, data.Length);
                for (int i = 0; i < data.Length; i++)
                {
                    Assert.AreEqual(0, recordBytes[i], "At offset " + i);
                }

                // Now set the flags
                cref.FirstRow = ((short)2);
                cref.LastRow = ((short)4);
                cref.FirstColumn = ((short)0);
                cref.LastColumn = ((short)3);

                // Re-test
                //baos.reset();
                baos.Seek(0, SeekOrigin.Begin);
                cref.Serialize(output);
                recordBytes = baos.ToArray();

                Assert.AreEqual(recordBytes.Length, data.Length);
                for (int i = 0; i < data.Length; i++)
                {
                    Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
                }
            }
            finally
            {
                //output.Close();
            }
        }
        public void TestHSSFSetArrayFormula_SingleCell()
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2);
            ICell[] cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells;
            Assert.AreEqual(1, cells.Length);

            // sheet.SetArrayFormula Creates rows and cells for the designated range
            Assert.IsNotNull(sheet.GetRow(2));
            ICell cell = sheet.GetRow(2).GetCell(2);
            Assert.IsNotNull(cell);

            Assert.IsTrue(cell.IsPartOfArrayFormulaGroup);
            //retrieve the range and check it is the same
            Assert.AreEqual(range.FormatAsString(), cell.GetArrayFormulaRange().FormatAsString());

            FormulaRecordAggregate agg = (FormulaRecordAggregate)(((HSSFCell)cell).CellValueRecord);
            Assert.AreEqual(range.FormatAsString(), agg.GetArrayFormulaRange().FormatAsString());
            Assert.IsTrue(agg.IsPartOfArrayFormula);
        }
Example #26
0
        public static int MergeTo(this ICell self, ICell target)
        {
            int firstRow = self.RowIndex;
            int lastRow = target.RowIndex;
            int firstCol = self.ColumnIndex;
            int lastCol = target.ColumnIndex;

            if (self.RowIndex > target.RowIndex)
            {
                firstRow = target.RowIndex;
                lastRow = self.RowIndex;
            }

            if (self.ColumnIndex > target.ColumnIndex)
            {
                firstCol = target.ColumnIndex;
                lastCol = self.ColumnIndex;
            }

            CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            return self.Sheet.AddMergedRegion(cellRangeAddress);
        }
Example #27
0
 private static CellRangeAddress[] ToArray(ArrayList temp)
 {
     CellRangeAddress[] result = new CellRangeAddress[temp.Count];
     result = (CellRangeAddress[])temp.ToArray(typeof(CellRangeAddress));
     return result;
 }
Example #28
0
 // TODO - write junit test for this
 static CellRangeAddress[] ResolveRangeOverlap(CellRangeAddress rangeA, CellRangeAddress rangeB)
 {
     if (rangeA.IsFullColumnRange)
     {
         if (rangeA.IsFullRowRange)
         {
             // Excel seems to leave these unresolved
             return null;
         }
         return SliceUp(rangeA, rangeB);
     }
     if (rangeA.IsFullRowRange)
     {
         if (rangeB.IsFullColumnRange)
         {
             // Excel seems to leave these unresolved
             return null;
         }
         return SliceUp(rangeA, rangeB);
     }
     if (rangeB.IsFullColumnRange)
     {
         return SliceUp(rangeB, rangeA);
     }
     if (rangeB.IsFullRowRange)
     {
         return SliceUp(rangeB, rangeA);
     }
     return SliceUp(rangeA, rangeB);
 }
Example #29
0
 /**
  * Do all possible cell merges between cells of the list so that:
  * 	if a cell range is completely inside of another cell range, it s removed from the list
  * 	if two cells have a shared border, merge them into one bigger cell range
  * @param cellRangeList
  * @return updated List of cell ranges
  */
 public static CellRangeAddress[] MergeCellRanges(CellRangeAddress[] cellRanges)
 {
     if (cellRanges.Length < 1)
     {
         return cellRanges;
     }
     ArrayList temp = MergeCellRanges(NPOI.Util.Arrays.AsList(cellRanges));
     return ToArray(temp);
 }
Example #30
0
        private MemoryStream ListToStream <T>(IList <T> list, IDictionary <string, string> propertyNameList, string title)
        {
            //创建内存流
            using (MemoryStream ms = new MemoryStream())
            {
                //将控制excel表头的参数写入到一个临时集合
                //List<IDictionary<string, string>> propertyNameList = new List<IDictionary<string, string>>();
                //if (propertyName != null)
                //{
                //    propertyNameList.AddRange(propertyName);
                //}

                //创建NOPI对象
                IWorkbook workbook = new HSSFWorkbook();
                ISheet    sheet    = workbook.CreateSheet();
                //表头的行号  如果有标题就是1 没有标题就是0
                int headerRowNum = 0;
                if (!string.IsNullOrEmpty(title))
                {
                    headerRowNum = 1;
                    IRow titleRow = sheet.CreateRow(0);
                    titleRow.Height = 999;

                    ICell titleCell = titleRow.CreateCell(0);
                    titleCell.SetCellValue(title);
                    ICellStyle cellStyle = workbook.CreateCellStyle();
                    //设置字体
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 18;
                    font.FontName           = "微软雅黑";
                    cellStyle.SetFont(font);
                    //对齐方式
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    titleCell.CellStyle = cellStyle;
                }
                IRow headerRow = sheet.CreateRow(headerRowNum);
                if (list.Count <= 0)
                {
                    return(null);
                }
                //通过反射得到对象的属性集合
                PropertyInfo[] propertys = list[0].GetType().GetProperties();

                //遍历属性集合生成excel的表头标题
                int cellIndex = 0;
                if (propertyNameList == null || propertyNameList.Count == 0)
                {
                    //如果没有传入自定义的导出表头
                    for (int i = 0; i < propertys.Count(); i++)
                    {
                        headerRow.CreateCell(i).SetCellValue(propertys[i].Name);
                    }
                }
                else
                {
                    //用户自定义的
                    foreach (KeyValuePair <string, string> item in propertyNameList)
                    {
                        for (int i = 0; i < propertys.Count(); i++)
                        {
                            if (propertys[i].Name.Equals(item.Key))
                            {
                                headerRow.CreateCell(cellIndex).SetCellValue(item.Value);
                                cellIndex++;
                                break;
                            }
                        }
                    }
                }
                #region  面的方式可以根据传入的列的顺序导入
                //for (int i = 0; i < propertys.Count(); i++)
                //{
                //    //判断excel表头是否是用户定义
                //    if (propertyNameList.Count == 0)
                //    {
                //        headerRow.CreateCell(i).SetCellValue(propertys[i].Name);
                //    }
                //    else
                //    {
                //        foreach (KeyValuePair<string, string> item in propertyNameList)
                //        {
                //            if (item.Key.Equals(propertys[i].Name))
                //            {
                //                headerRow.CreateCell(cellIndex).SetCellValue(item.Value);
                //                cellIndex++;
                //                break;
                //            }
                //        }
                //    }
                //}
                #endregion
                //遍历生成excel的行集数据
                int rowIndex = headerRowNum + 1;
                if (propertyNameList == null || propertyNameList.Count == 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);
                        for (int j = 0; j < propertys.Count(); j++)
                        {
                            object obj = propertys[j].GetValue(list[i], null);
                            dataRow.CreateCell(j).SetCellValue(obj == null ? "" : obj.ToString());
                        }
                        rowIndex++;
                    }
                }
                else
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        int  cellIndex2 = 0;
                        IRow dataRow    = sheet.CreateRow(rowIndex);
                        foreach (KeyValuePair <string, string> item in propertyNameList)
                        {
                            for (int j = 0; j < propertys.Count(); j++)
                            {
                                if (item.Key.Equals(propertys[j].Name))
                                {
                                    object obj = propertys[j].GetValue(list[i], null);
                                    dataRow.CreateCell(cellIndex2).SetCellValue(obj == null ? "" : obj.ToString());
                                    cellIndex2++;
                                    break;
                                }
                            }
                        }
                        rowIndex++;
                    }
                }
                #region  面的方式可以根据传入的列的顺序导入
                //int rowIndex = 1;
                //for (int i = 0; i < list.Count; i++)
                //{
                //    IRow dataRow = sheet.CreateRow(rowIndex);
                //    int cellIndex2 = 0;
                //    for (int j = 0; j < propertys.Count(); j++)
                //    {
                //        //指定了excel表头信息
                //        if (propertyNameList.Count == 0)
                //        {
                //            object obj = propertys[j].GetValue(list[i], null);
                //            dataRow.CreateCell(j).SetCellValue(obj == null ? "" : obj.ToString());
                //        }
                //        else
                //        {
                //            foreach (KeyValuePair<string, string> item in propertyNameList)
                //            {
                //                if (item.Key.Equals(propertys[j].Name))
                //                {
                //                    object obj = propertys[j].GetValue(list[i], null);
                //                    dataRow.CreateCell(cellIndex2).SetCellValue(obj == null ? "" : obj.ToString());
                //                    cellIndex2++;
                //                    break;
                //                }
                //            }
                //        }
                //    }
                //    rowIndex++;
                //}
                #endregion

                //求合并的列数
                int rangesize = headerRow.Cells.Count;
                //合并单元格
                NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, rangesize - 1);
                sheet.AddMergedRegion(cellRangeAddress);
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return(ms);
            }
        }
        private void CreateRowValueSheet_2(ParkingParserItem item)
        {
            NPOI.SS.UserModel.IRow row;
            ICell  cell;
            int    type;
            String nametype = "";

            int start_row, end_row;

            if (!(item != null && item.GetListItem() != null && item.GetListItem().Count > 0))
            {
                return;
            }

            //Clear header value
            row = sheet_2.CreateRow(sheet_2_rowIndex);
            sheet_2_rowIndex++;
            start_row = sheet_2_rowIndex + 1;

            if (CheckExistsSize_2(item.GetListItem().ElementAt(0).GetProductSize()) < 0)
            {
                type = 1;
            }
            else
            {
                type = 2;
            }

            for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++)
            {
                cell = row.CreateCell(run);

                cell.SetCellValue(Sheet_2_GetHeaderString(type, run));

                if (item.GetNameProduct().StartsWith("R"))
                {
                    nametype = " - BRASSIERES";
                }
                else
                {
                    nametype = " - BRIEFS";
                }

                if (run == 0)
                {
                    cell.SetCellValue(item.GetNameProduct() + nametype);


                    cell.CellStyle = sheet_2_headerStyle_1;
                }
                else
                {
                    cell.CellStyle = sheet_2_headerStyle_2;
                }
            }

            NPOI.SS.Util.CellRangeAddress range = new NPOI.SS.Util.CellRangeAddress(sheet_2_rowIndex - 1, sheet_2_rowIndex - 1, 0, 4);
            sheet_2.AddMergedRegion(range);


            foreach (PackingListItem packItem in item.GetListItem())
            {
                if (packItem.GetMergerList() != null && packItem.GetMergerList().Count > 0)
                {
                    CreateMergerItem(type, item, packItem);
                }
                else
                {
                    CreateNotMergerItem(type, item, packItem);
                }
            }

            end_row = sheet_2_rowIndex;

            //sum row
            row = sheet_2.CreateRow(sheet_2_rowIndex);
            sheet_2_rowIndex++;

            for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++)
            {
                cell = row.CreateCell(run);
                if (run == 2)
                {
                    cell.CellStyle = sheet_2_bottomStyle_2;
                }
                else
                {
                    cell.CellStyle = sheet_2_bottomStyle_1;
                }
                switch (run)
                {
                case 0:
                    cell.SetCellValue("P'KGS");
                    break;

                case 2:
                    cell.SetCellValue("SUB TOTAL:");
                    break;

                case 3:
                case 4:
                    break;

                default:
                {
                    String colum  = Sheet_2_GetStringOfColum(run + 1);
                    String fomula = "SUM(" + colum + start_row + ":" + colum + end_row + ")";
                    cell.SetCellFormula(fomula);
                    break;
                }
                }
            }
        }
Example #32
0
        /**
         * Add a cell range structure.
         *
         * @param firstRow - the upper left hand corner's row
         * @param firstCol - the upper left hand corner's col
         * @param lastRow - the lower right hand corner's row
         * @param lastCol - the lower right hand corner's col
         * @return the index of this ADDR structure
         */
        public void AddCellRangeAddress(int firstRow, int firstCol, int lastRow, int lastCol)
        {
            CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);

            AddCellRangeAddress(region);
        }
Example #33
0
    public static void CopyRow(ref XSSFSheet worksheet, int sourceRowNum, int destinationRowNum, bool IsCoverRow = false, bool IsRemoveSrcRow = false, bool copyRowHeight = true, bool resetOriginalRowHeight = true)
    {
        XSSFRow  newRow = worksheet.GetRow(destinationRowNum) as XSSFRow;
        XSSFRow  sourceRow = worksheet.GetRow(sourceRowNum) as XSSFRow;
        XSSFCell oldCell, newCell;
        int      i;

        if (newRow == null)
        {
            newRow = worksheet.CreateRow(destinationRowNum) as XSSFRow;
        }
        else
        {
            if (!IsCoverRow)
            {
                ShiftRows(ref worksheet, destinationRowNum, worksheet.LastRowNum, 1);
            }
        }

        // Loop through source columns to add to new row
        for (i = 0; i < sourceRow.LastCellNum; i++)
        {
            // Grab a copy of the old/new cell
            oldCell = sourceRow.GetCell(i) as XSSFCell;
            newCell = newRow.GetCell(i) as XSSFCell;

            if (newCell == null)
            {
                newCell = newRow.CreateCell(i) as XSSFCell;
            }

            // If the old cell is null jump to next cell
            if (oldCell == null)
            {
                newCell = null;
                continue;
            }

            // Copy style from old cell and apply to new cell
            newCell.CellStyle = oldCell.CellStyle;

            // If there is a cell comment, copy
            if (newCell.CellComment != null)
            {
                newCell.CellComment = oldCell.CellComment;
            }

            // If there is a cell hyperlink, copy
            if (oldCell.Hyperlink != null)
            {
                newCell.Hyperlink = oldCell.Hyperlink;
            }

            // Set the cell data value
            switch (oldCell.CellType)
            {
            case CellType.Blank:
                newCell.SetCellValue(oldCell.StringCellValue);
                break;

            case CellType.Boolean:
                newCell.SetCellValue(oldCell.BooleanCellValue);
                break;

            case CellType.Error:
                newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                break;

            case CellType.Formula:
                newCell.CellFormula = oldCell.CellFormula;
                break;

            case CellType.Numeric:
                newCell.SetCellValue(oldCell.NumericCellValue);
                break;

            case CellType.String:
                newCell.SetCellValue(oldCell.RichStringCellValue);
                break;

            case CellType.Unknown:
                newCell.SetCellValue(oldCell.StringCellValue);
                break;
            }
        }

        // If there are are any merged regions in the source row, copy to new row
        List <NPOI.SS.Util.CellRangeAddress> cellRangeList = new List <NPOI.SS.Util.CellRangeAddress>();

        NPOI.SS.Util.CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
        for (i = 0; i < worksheet.NumMergedRegions; i++)
        {
            cellRangeAddress = worksheet.GetMergedRegion(i);

            if (cellRangeAddress.FirstRow == sourceRow.RowNum)
            {
                cellRangeList.Add(cellRangeAddress);
            }
        }

        if (cellRangeList.Any())
        {
            foreach (var cellRange in cellRangeList)
            {
                newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(newRow.RowNum, (newRow.RowNum + (cellRange.LastRow - cellRange.FirstRow)), cellRange.FirstColumn, cellRange.LastColumn);
                worksheet.AddMergedRegion(newCellRangeAddress);
            }
        }

        //複製行高到新列
        if (copyRowHeight)
        {
            newRow.Height = sourceRow.Height;
        }
        //重製原始列行高
        if (resetOriginalRowHeight)
        {
            sourceRow.Height = worksheet.DefaultRowHeight;
        }
        //清掉原列
        if (IsRemoveSrcRow == true)
        {
            worksheet.RemoveRow(sourceRow);

            bool next = true;
            do
            {
                next = false;
                for (i = 0; i < worksheet.NumMergedRegions; i++)
                {
                    if (worksheet.GetMergedRegion(i).FirstRow == sourceRow.RowNum)
                    {
                        worksheet.RemoveMergedRegion(i);
                        next = true;
                    }
                }
            } while (next);
        }
    }
Example #34
0
 public void AddCellRangeAddress(CellRangeAddress cra)
 {
     _list.Add(cra);
 }
Example #35
0
 /// <summary>
 /// constructor
 /// </summary>
 /// <param name="sheet">the sheet where data located.</param>
 /// <param name="range">the range within that sheet.</param>
 public DataMarker(ISheet sheet, CellRangeAddress range)
 {
     this.sheet = sheet;
     this.range = range;
 }
Example #36
0
 /**
  * @return the total size of for the specified number of ranges,
  *  including the initial 2 byte range count
  */
 public static int GetEncodedSize(int numberOfRanges)
 {
     return(2 + CellRangeAddress.GetEncodedSize(numberOfRanges));
 }
Example #37
0
 public static bool ContainsCell(CellRangeAddress cr, int rowIx, int colIx)
 {
     return(cr.IsInRange(rowIx, colIx));
 }
Example #38
0
        /**
         * Compute width of a single cell
         *
         * @param cell the cell whose width is to be calculated
         * @param defaultCharWidth the width of a single character
         * @param formatter formatter used to prepare the text to be measured
         * @param useMergedCells    whether to use merged cells
         * @return  the width in pixels
         */
        public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells)
        {
            ISheet    sheet  = cell.Sheet;
            IWorkbook wb     = sheet.Workbook;
            IRow      row    = cell.Row;
            int       column = cell.ColumnIndex;

            int colspan = 1;

            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress region = sheet.GetMergedRegion(i);
                if (ContainsCell(region, row.RowNum, column))
                {
                    if (!useMergedCells)
                    {
                        // If we're not using merged cells, skip this one and move on to the next.
                        return(-1);
                    }
                    cell    = row.GetCell(region.FirstColumn);
                    colspan = 1 + region.LastColumn - region.FirstColumn;
                }
            }

            ICellStyle style       = cell.CellStyle;
            CellType   cellType    = cell.CellType;
            IFont      defaultFont = wb.GetFontAt((short)0);
            Font       windowsFont = IFont2Font(defaultFont);

            // for formula cells we compute the cell width for the cached formula result
            if (cellType == CellType.Formula)
            {
                cellType = cell.CachedFormulaResultType;
            }

            IFont font = wb.GetFontAt(style.FontIndex);

            //AttributedString str;
            //TextLayout layout;

            double width = -1;

            using (Bitmap bmp = new Bitmap(2048, 100))
            {
                Graphics g = Graphics.FromImage(bmp);
                if (cellType == CellType.String)
                {
                    IRichTextString rt    = cell.RichStringCellValue;
                    String[]        lines = rt.String.Split("\n".ToCharArray());
                    for (int i = 0; i < lines.Length; i++)
                    {
                        String txt = lines[i] + defaultChar;

                        //str = new AttributedString(txt);
                        //copyAttributes(font, str, 0, txt.length());
                        windowsFont = IFont2Font(font);
                        if (rt.NumFormattingRuns > 0)
                        {
                            // TODO: support rich text fragments
                        }

                        //layout = new TextLayout(str.getIterator(), fontRenderContext);
                        if (style.Rotation != 0)
                        {
                            /*
                             * Transform the text using a scale so that it's height is increased by a multiple of the leading,
                             * and then rotate the text before computing the bounds. The scale results in some whitespace around
                             * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
                             * is added by the standard Excel autosize.
                             */
                            //AffineTransform trans = new AffineTransform();
                            //trans.concatenate(AffineTransform.getRotateInstance(style.Rotation*2.0*Math.PI/360.0));
                            //trans.concatenate(
                            //    AffineTransform.getScaleInstance(1, fontHeightMultiple)
                            //    );
                            double angle = style.Rotation * 2.0 * Math.PI / 360.0;
                            SizeF  sf    = g.MeasureString(txt, windowsFont);
                            double x1    = Math.Abs(sf.Height * Math.Sin(angle));
                            double x2    = Math.Abs(sf.Width * Math.Cos(angle));
                            double w     = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
                            width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
                            //width = Math.Max(width,
                            //                 ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) +
                            //                 cell.getCellStyle().getIndention());
                        }
                        else
                        {
                            //width = Math.Max(width,
                            //                 ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
                            //                 cell.getCellStyle().getIndention());
                            double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven);
                            width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
                        }
                    }
                }
                else
                {
                    String sval = null;
                    if (cellType == CellType.Numeric)
                    {
                        // Try to get it formatted to look the same as excel
                        try
                        {
                            sval = formatter.FormatCellValue(cell, dummyEvaluator);
                        }
                        catch (Exception)
                        {
                            sval = cell.NumericCellValue.ToString();
                        }
                    }
                    else if (cellType == CellType.Boolean)
                    {
                        sval = cell.BooleanCellValue.ToString().ToUpper();
                    }
                    if (sval != null)
                    {
                        String txt = sval + defaultChar;
                        //str = new AttributedString(txt);
                        //copyAttributes(font, str, 0, txt.length());
                        windowsFont = IFont2Font(font);
                        //layout = new TextLayout(str.getIterator(), fontRenderContext);
                        if (style.Rotation != 0)
                        {
                            /*
                             * Transform the text using a scale so that it's height is increased by a multiple of the leading,
                             * and then rotate the text before computing the bounds. The scale results in some whitespace around
                             * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
                             * is added by the standard Excel autosize.
                             */
                            //AffineTransform trans = new AffineTransform();
                            //trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
                            //trans.concatenate(
                            //    AffineTransform.getScaleInstance(1, fontHeightMultiple)
                            //    );
                            //width = Math.max(width,
                            //                 ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) +
                            //                 cell.getCellStyle().getIndention());
                            double angle = style.Rotation * 2.0 * Math.PI / 360.0;
                            SizeF  sf    = g.MeasureString(txt, windowsFont);
                            double x1    = sf.Height * Math.Sin(angle);
                            double x2    = sf.Width * Math.Cos(angle);
                            double w     = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
                            width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
                        }
                        else
                        {
                            //width = Math.max(width,
                            //                 ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
                            //                 cell.getCellStyle().getIndention());
                            double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven);
                            width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
                        }
                    }
                }
            }
            return(width);
        }
Example #39
0
        /**
         * Compute width of a single cell
         *
         * @param cell the cell whose width is to be calculated
         * @param defaultCharWidth the width of a single character
         * @param formatter formatter used to prepare the text to be measured
         * @param useMergedCells    whether to use merged cells
         * @return  the width in pixels or -1 if cell is empty
         */
        public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells)
        {
            ISheet    sheet  = cell.Sheet;
            IWorkbook wb     = sheet.Workbook;
            IRow      row    = cell.Row;
            int       column = cell.ColumnIndex;

            int colspan = 1;

            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress region = sheet.GetMergedRegion(i);
                if (ContainsCell(region, row.RowNum, column))
                {
                    if (!useMergedCells)
                    {
                        // If we're not using merged cells, skip this one and move on to the next.
                        return(-1);
                    }
                    cell    = row.GetCell(region.FirstColumn);
                    colspan = 1 + region.LastColumn - region.FirstColumn;
                }
            }

            ICellStyle style       = cell.CellStyle;
            CellType   cellType    = cell.CellType;
            IFont      defaultFont = wb.GetFontAt((short)0);
            Font       windowsFont = IFont2Font(defaultFont);

            // for formula cells we compute the cell width for the cached formula result
            if (cellType == CellType.Formula)
            {
                cellType = cell.CachedFormulaResultType;
            }

            IFont font = wb.GetFontAt(style.FontIndex);

            double width = -1;

            using (Bitmap bmp = new Bitmap(1, 1))
                using (Graphics g = Graphics.FromImage(bmp))
                {
                    if (cellType == CellType.String)
                    {
                        IRichTextString rt    = cell.RichStringCellValue;
                        String[]        lines = rt.String.Split("\n".ToCharArray());
                        for (int i = 0; i < lines.Length; i++)
                        {
                            String txt = lines[i] + defaultChar;

                            //AttributedString str = new AttributedString(txt);
                            //copyAttributes(font, str, 0, txt.length());
                            windowsFont = IFont2Font(font);
                            if (rt.NumFormattingRuns > 0)
                            {
                                // TODO: support rich text fragments
                            }

                            width = GetCellWidth(defaultCharWidth, colspan, style, width, txt, g, windowsFont, cell);
                        }
                    }
                    else
                    {
                        String sval = null;
                        if (cellType == CellType.Numeric)
                        {
                            // Try to get it formatted to look the same as excel
                            try
                            {
                                sval = formatter.FormatCellValue(cell, dummyEvaluator);
                            }
                            catch (Exception)
                            {
                                sval = cell.NumericCellValue.ToString();
                            }
                        }
                        else if (cellType == CellType.Boolean)
                        {
                            sval = cell.BooleanCellValue.ToString().ToUpper();
                        }
                        if (sval != null)
                        {
                            String txt = sval + defaultChar;
                            //str = new AttributedString(txt);
                            //copyAttributes(font, str, 0, txt.length());
                            windowsFont = IFont2Font(font);
                            width       = GetCellWidth(defaultCharWidth, colspan, style, width, txt, g, windowsFont, cell);
                        }
                    }
                }
            return(width);
        }
        public void UpdateExcelWithNPOI(DataTable dt, DateTime fromDate, DateTime toDate, String accountNo, String accountName)
        {
            // Open Template
            FileStream fs = new FileStream("C:\\DRRTemplate\\CorporateFinance\\Corporate_Account_Performance_Master.xlsx", FileMode.Open, FileAccess.Read);
            //FileStream fs = new FileStream(Server.MapPath(@"\Reports\DRR_Grogol_Master.xlsx"), FileMode.Open, FileAccess.Read);
            // Load the template into a NPOI workbook
            XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs);

            // Load the sheet you are going to use as a template into NPOI
            ISheet sheetMacro = templateWorkbook.GetSheet("Macro");

            #region Cell Style
            IDataFormat format = templateWorkbook.CreateDataFormat();

            var fontBold = templateWorkbook.CreateFont();
            fontBold.FontHeightInPoints = 10;
            fontBold.FontName           = "Calibri";
            fontBold.Boldweight         = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;

            var fontSmall = templateWorkbook.CreateFont();
            fontSmall.FontHeightInPoints = 10;
            fontSmall.FontName           = "Calibri";
            //fontSmall.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;


            ICellStyle dateStyle = templateWorkbook.CreateCellStyle();
            dateStyle.DataFormat        = format.GetFormat("dd/MM/yyyy");
            dateStyle.Alignment         = HorizontalAlignment.Right;
            dateStyle.VerticalAlignment = VerticalAlignment.Center;
            dateStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            dateStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            dateStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            dateStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            dateStyle.SetFont(fontSmall);


            ICellStyle amountStyle = templateWorkbook.CreateCellStyle();
            amountStyle.DataFormat        = format.GetFormat("#,##0"); // #.00
            amountStyle.Alignment         = HorizontalAlignment.Right;
            amountStyle.VerticalAlignment = VerticalAlignment.Center;
            amountStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            amountStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            amountStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            amountStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            amountStyle.SetFont(fontSmall);

            ICellStyle percentageStyle = templateWorkbook.CreateCellStyle();
            percentageStyle.DataFormat        = format.GetFormat("#,##0.00"); // #.00
            percentageStyle.Alignment         = HorizontalAlignment.Right;
            percentageStyle.VerticalAlignment = VerticalAlignment.Center;
            percentageStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            percentageStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            percentageStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            percentageStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            percentageStyle.SetFont(fontSmall);

            ICellStyle normalStyleLeft = templateWorkbook.CreateCellStyle();
            normalStyleLeft.Alignment         = HorizontalAlignment.Left;
            normalStyleLeft.VerticalAlignment = VerticalAlignment.Center;
            normalStyleLeft.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            normalStyleLeft.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            normalStyleLeft.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            normalStyleLeft.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            normalStyleLeft.SetFont(fontSmall);

            ICellStyle normalStyleCenter = templateWorkbook.CreateCellStyle();
            normalStyleCenter.Alignment         = HorizontalAlignment.Center;
            normalStyleCenter.VerticalAlignment = VerticalAlignment.Center;
            normalStyleCenter.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            normalStyleCenter.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            normalStyleCenter.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            normalStyleCenter.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            normalStyleCenter.SetFont(fontSmall);

            var CenterBoldStyle = templateWorkbook.CreateCellStyle();
            CenterBoldStyle.VerticalAlignment = VerticalAlignment.Center;
            CenterBoldStyle.Alignment         = HorizontalAlignment.Center;
            CenterBoldStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            CenterBoldStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            CenterBoldStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            CenterBoldStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            CenterBoldStyle.SetFont(fontBold);

            #endregion

            #region Merged Column Header

            //make merged header row for month and year name
            int  mergedRowNumber = 5;
            IRow rowMonthName    = sheetMacro.CreateRow(mergedRowNumber);

            //create merged column based on number of month
            int totalColumn = dt.Columns.Count;
            int numberOfColumnInOneMonth = 4;
            int numberOfMonth            = (totalColumn - 2) / numberOfColumnInOneMonth;//exclude 2 first column (CustNo and CustName)

            int startCol = 2, endCol = 5;
            for (int j = 0; j < numberOfMonth; j++)
            {
                var cra = new NPOI.SS.Util.CellRangeAddress(mergedRowNumber, mergedRowNumber, startCol, endCol);
                sheetMacro.AddMergedRegion(cra);

                ICell cell = rowMonthName.CreateCell(startCol);
                cell.SetCellType(CellType.String);

                //add border in merged cells
                RegionUtil.SetBorderBottom(1, cra, sheetMacro, templateWorkbook);
                RegionUtil.SetBorderTop(1, cra, sheetMacro, templateWorkbook);
                RegionUtil.SetBorderLeft(1, cra, sheetMacro, templateWorkbook);
                RegionUtil.SetBorderRight(1, cra, sheetMacro, templateWorkbook);

                cell.CellStyle = CenterBoldStyle;

                cell.SetCellValue(fromDate.AddMonths(j).ToString("MMM yyyy"));

                startCol += numberOfColumnInOneMonth;
                endCol   += numberOfColumnInOneMonth;


                //to remove
                //<start>
                //var cra1 = new NPOI.SS.Util.CellRangeAddress(5, 5, 2, 5);
                //var cra2 = new NPOI.SS.Util.CellRangeAddress(5, 5, 6, 9);
                //sheetMacro.AddMergedRegion(cra1);
                //sheetMacro.AddMergedRegion(cra2);

                //ICell cell1 = rowMonthName.CreateCell(2);
                ////ICell cell1 = sheetMacro.GetRow(5).GetCell(2);
                //cell1.SetCellType(CellType.String);
                //cell1.SetCellValue("Month 1");

                //ICell cell2 = rowMonthName.CreateCell(6);
                ////ICell cell2 = sheetMacro.GetRow(5).GetCell(6);
                //cell2.SetCellType(CellType.String);
                //cell2.SetCellValue("Month 2");
                //<end>
            }

            #endregion

            #region Column Name

            //make a header row  for colmun name
            //starting row 6 (zero based index=6) >
            //CustNo, CustName, InvoiceM1, InvoiceM2,..InvoiceM[n], PaymentM1, PaymentM2,..PaymentM[n], LastPostingDateM1,LastPostingDateM2,..LastPostingDateM[n]

            IRow row1 = sheetMacro.CreateRow(6);

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ICell cell = row1.CreateCell(j);

                cell.CellStyle = CenterBoldStyle;

                String columnName = dt.Columns[j].ToString().Split('-')[0].ToString();
                if (columnName == "CustNo")
                {
                    columnName = "Customer No.";
                }
                if (columnName == "CustName")
                {
                    columnName = "Name";
                }
                else if (columnName == "Invoice")
                {
                    columnName = "Actual";
                }
                else if (columnName == "Payment")
                {
                    columnName = "Collection";
                }
                else if (columnName == "LastPostingDate")
                {
                    columnName = "Col Date";
                }
                else if (columnName == "Percentage")
                {
                    columnName = "%";
                }
                cell.SetCellValue(columnName);
            }

            #endregion

            #region Data

            //loops through data
            //get column lastPostingDate
            int dateColumnStart = 4, percentageColumnStart = 5;
            int numberofColumnPerMonth = 4;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheetMacro.CreateRow(i + 7);//starting row 7, for data
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    //set decimal for invoice and payment
                    //date for last posting date
                    //text for custNo and custName
                    if (j <= 1)
                    {
                        //text
                        ICell  cell       = row.CreateCell(j);
                        String columnName = dt.Columns[j].ToString();
                        cell.SetCellType(CellType.String);
                        cell.CellStyle = normalStyleLeft;
                        cell.SetCellValue(dt.Rows[i][columnName].ToString());
                    }
                    else if ((j - dateColumnStart) % numberofColumnPerMonth == 0)//column 4 8 12 ..
                    {
                        //date
                        ICell  cell       = row.CreateCell(j);
                        String columnName = dt.Columns[j].ToString();
                        cell.SetCellType(CellType.String);
                        cell.CellStyle = dateStyle;
                        if (dt.Rows[i][columnName] != DBNull.Value)
                        {
                            cell.SetCellValue(Convert.ToDateTime(dt.Rows[i][columnName]));
                        }
                    }
                    else if ((j - percentageColumnStart) % numberofColumnPerMonth == 0) //column 5 9 13 ..
                    {
                        //percentage
                        ICell  cell       = row.CreateCell(j);
                        String columnName = dt.Columns[j].ToString();
                        cell.SetCellType(CellType.Numeric);
                        cell.CellStyle = percentageStyle;
                        if (dt.Rows[i][columnName] != DBNull.Value)
                        {
                            cell.SetCellValue(Convert.ToDouble(dt.Rows[i][columnName]));
                        }
                        else
                        {
                            cell.SetCellValue(0);
                        }
                    }
                    else
                    {
                        //decimal amount
                        ICell  cell       = row.CreateCell(j);
                        String columnName = dt.Columns[j].ToString();
                        cell.SetCellType(CellType.Numeric);
                        cell.CellStyle = amountStyle;

                        if (dt.Rows[i][columnName] != DBNull.Value)
                        {
                            cell.SetCellValue(Convert.ToDouble(dt.Rows[i][columnName]));
                        }
                        else
                        {
                            cell.SetCellValue(0);
                        }
                    }
                }
            }

            #endregion


            //Auto size column
            for (int j = 2; j < dt.Columns.Count; j++)
            {
                sheetMacro.AutoSizeColumn(j);
            }

            #region Parameter

            //set parameter value
            int  columnParameter = 1;
            IRow rowFromDate     = sheetMacro.GetRow(1);
            IRow rowToDate       = sheetMacro.GetRow(2);
            IRow rowAccountNo    = sheetMacro.GetRow(3);
            IRow rowAccountName  = sheetMacro.GetRow(4);
            //cellstyle
            //rowFromDate.GetCell(columnParameter).CellStyle
            rowFromDate.GetCell(columnParameter).SetCellValue(fromDate);
            rowToDate.GetCell(columnParameter).SetCellValue(toDate);
            rowAccountNo.GetCell(columnParameter).SetCellValue(accountNo);
            rowAccountName.GetCell(columnParameter).SetCellValue(accountName);


            #endregion

            #region Save to File


            XSSFFormulaEvaluator.EvaluateAllFormulaCells(templateWorkbook);
            using (var exportData = new MemoryStream())
            {
                Response.Clear();
                templateWorkbook.Write(exportData);
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "Corporate_AccountSummary_" + fromDate.ToString("ddMMMyyyy") + "_" + toDate.ToString("ddMMMyyyy") + ".xlsx"));
                Response.BinaryWrite(exportData.ToArray());
                Response.End();
            }

            #endregion
        }
Example #41
0
    /// <summary>
    /// 为Excel添加表头
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="headerRow">GridView的HeaderRow属性</param>
    /// <param name="headerCellStyle">表头格式</param>
    /// <param name="flagNewLine">转行标志</param>
    /// <param name="colCount">Excel表列数</param>
    /// <returns>Excel表格行数</returns>
    private int AddSheetHeader(ISheet sheet, GridViewRow headerRow, ICellStyle headerCellStyle, string flagNewLine, out int colCount)
    {
        //int
        colCount = 0;    //记录GridView列数
        int rowInex = 0; //记录表头的行数

        IRow  row = sheet.CreateRow(0);
        ICell cell;

        int groupCount = 0; //记录分组数
        int colIndex   = 0; //记录列索引,并于结束表头遍历后记录总列数

        for (int i = 0; i < headerRow.Cells.Count; i++)
        {
            if (rowInex != groupCount)//新增了标题行时重新创建
            {
                row        = sheet.CreateRow(rowInex);
                groupCount = rowInex;
            }

            #region 是否跳过当前单元格

            for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
            {
                NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
                //当前单元格是处于合并区域内
                if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex &&
                    a.FirstRow <= rowInex && a.LastRow >= rowInex)
                {
                    colIndex++;
                    m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域
                }
            }


            #endregion

            cell           = row.CreateCell(colIndex);
            cell.CellStyle = headerCellStyle;

            TableCell tablecell = headerRow.Cells[i];

            //跨列属性可能为添加了html属性colspan,也可能是由cell的ColumnSpan属性指定
            int colSpan = 0;
            int rowSpan = 0;

            #region 获取跨行跨列属性值
            //跨列
            if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"]))
            {
                colSpan = int.Parse(tablecell.Attributes["colspan"].ToString());
                colSpan--;
            }
            if (tablecell.ColumnSpan > 1)
            {
                colSpan = tablecell.ColumnSpan;
                colSpan--;
            }

            //跨行
            if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"]))
            {
                rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString());
                rowSpan--;
            }
            if (tablecell.RowSpan > 1)
            {
                rowSpan = tablecell.RowSpan;
                rowSpan--;
            }
            #endregion

            //添加excel合并区域
            if (colSpan > 0 || rowSpan > 0)
            {
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan));
                colIndex += colSpan + 1;//重新设置列索引
            }
            else
            {
                colIndex++;
            }
            string strHeader = headerRow.Cells[i].Text;

            if (strHeader.Contains(flagNewLine))//换行标记,当只存在一行标题时不存在</th></tr><tr>,此时colCount无法被赋值
            {
                rowInex++;
                colCount = colIndex;
                colIndex = 0;

                strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>"));
            }
            cell.SetCellValue(strHeader);
        }
        if (groupCount == 0)//只有一行标题时另外为colCount赋值
        {
            colCount = colIndex;
        }
        rowInex++;//表头结束后另起一行开始记录控件数据行索引

        return(rowInex);
    }
Example #42
0
    public static IRow CopyRow(this IRow dstRow, ISheet srcSheet, int srcRowNum)
    {
        IRow  sourceRow = srcSheet.GetRow(srcRowNum) as IRow;
        ICell oldCell, newCell;
        int   i;

        // Loop through source columns to add to new row
        for (i = 0; i < sourceRow.LastCellNum; i++)
        {
            // Grab a copy of the old/new cell
            oldCell = sourceRow.GetCell(i) as ICell;
            newCell = dstRow.GetCell(i) as ICell;

            if (newCell == null)
            {
                newCell = dstRow.CreateCell(i) as ICell;
            }

            // If the old cell is null jump to next cell
            if (oldCell == null)
            {
                newCell = null;
                continue;
            }

            // Copy style from old cell and apply to new cell
            newCell.CellStyle = oldCell.CellStyle;

            // If there is a cell comment, copy
            if (newCell.CellComment != null)
            {
                newCell.CellComment = oldCell.CellComment;
            }

            // If there is a cell hyperlink, copy
            if (oldCell.Hyperlink != null)
            {
                newCell.Hyperlink = oldCell.Hyperlink;
            }

            // Set the cell data value
            switch (oldCell.CellType)
            {
            case CellType.Blank:
                newCell.SetCellValue(oldCell.StringCellValue);
                break;

            case CellType.Boolean:
                newCell.SetCellValue(oldCell.BooleanCellValue);
                break;

            case CellType.Error:
                newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                break;

            case CellType.Formula:
                newCell.CellFormula = oldCell.CellFormula;
                break;

            case CellType.Numeric:
                newCell.SetCellValue(oldCell.NumericCellValue);
                break;

            case CellType.String:
                newCell.SetCellValue(oldCell.RichStringCellValue);
                break;

            case CellType.Unknown:
                newCell.SetCellValue(oldCell.StringCellValue);
                break;
            }
        }

        //If there are are any merged regions in the source row, copy to new row
        NPOI.SS.Util.CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
        for (i = 0; i < srcSheet.NumMergedRegions; i++)
        {
            cellRangeAddress = srcSheet.GetMergedRegion(i);
            if (cellRangeAddress.FirstRow == sourceRow.RowNum)
            {
                newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(dstRow.RowNum,
                                                                        (dstRow.RowNum + (cellRangeAddress.LastRow - cellRangeAddress.FirstRow)),
                                                                        cellRangeAddress.FirstColumn,
                                                                        cellRangeAddress.LastColumn);
                dstRow.Sheet.AddMergedRegion(newCellRangeAddress);
            }
        }

        //複製行高到新列
        //if (copyRowHeight)
        dstRow.Height = sourceRow.Height;
        ////重製原始列行高
        //if (resetOriginalRowHeight)
        //    sourceRow.Height = worksheet.DefaultRowHeight;
        ////清掉原列
        //if (IsRemoveSrcRow == true)
        //    worksheet.RemoveRow(sourceRow);

        return(dstRow);
    }
Example #43
0
        public void SetRepeatingRowsAndColumns(int sheetIndex,
                                               int startColumn, int endColumn,
                                               int startRow, int endRow)
        {
            XSSFSheet sheet = (XSSFSheet)GetSheetAt(sheetIndex);

            CellRangeAddress rows = null;
            CellRangeAddress cols = null;

            if (startRow != -1)
            {
                rows = new CellRangeAddress(startRow, endRow, -1, -1);
            }
            if (startColumn != -1)
            {
                cols = new CellRangeAddress(-1, -1, startColumn, endColumn);
            }

            sheet.RepeatingRows=(rows);
            sheet.RepeatingColumns=(cols);

        }
Example #44
0
        ////////////////////////////////////////////////////////////////////
        private void CreateRowValueSheet_1(PackingListItem item, DatabaseItem databaseItem)
        {
            NPOI.SS.UserModel.IRow r;

            if (item.GetQuantity() < databaseItem.GetMaxPacketSize())
            {
                r = sheet_1.CreateRow(rowIndex_sheet_1);
                rowIndex_sheet_1++;

                numberPacket_sheet_1++;

                double valueMerger = item.GetQuantity();

                if (item.GetMergerList() != null && item.GetMergerList().Count > 0)
                {
                    foreach (MergerItem merger in item.GetMergerList())
                    {
                        valueMerger += merger.GetQuantity();
                    }
                }

                for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
                {
                    ICell cell = r.CreateCell(idex);
                    cell.CellStyle = sheet_1_headerStyle;
                    switch (idex)
                    {
                    case 0:
                        cell.SetCellValue(this.packingParser.GetDateTime());
                        break;

                    case 1:
                        cell.SetCellValue(item.GetNameProduct());
                        break;

                    case 2:
                        cell.SetCellValue(item.GetColor1());
                        break;

                    case 3:
                        cell.SetCellValue(item.GetColor2());
                        break;

                    case 4:
                        cell.SetCellValue(item.GetProductSize());
                        break;

                    case 5:
                        cell.SetCellValue(item.GetQuantity());
                        if (valueMerger > item.GetQuantity())
                        {
                            cell.CellStyle = sheet_1_headerStyle_2;
                        }
                        break;

                    case 6:
                        cell.SetCellValue(1);
                        break;

                    case 7:
                        cell.SetCellValue(numberPacket_sheet_1);
                        break;

                    case 8:
                        if (valueMerger <= item.GetQuantity())
                        {
                            cell.SetCellValue(valueMerger);
                        }
                        break;

                    case 9:
                        cell.SetCellValue(databaseItem.GetNetWeight());
                        break;

                    case 10:
                        cell.SetCellValue(databaseItem.GetAllWeight());
                        break;

                    case 11:
                        cell.SetCellValue(databaseItem.GetPacketInformation());
                        break;

                    default:
                        break;
                    }
                }
            }
            else
            {
                for (int tmp = 0; tmp < (int)(item.GetQuantity() / databaseItem.GetMaxPacketSize()); tmp++)
                {
                    numberPacket_sheet_1++;

                    r = sheet_1.CreateRow(rowIndex_sheet_1);
                    rowIndex_sheet_1++;

                    for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
                    {
                        ICell cell = r.CreateCell(idex);
                        cell.CellStyle = sheet_1_headerStyle;

                        switch (idex)
                        {
                        case 0:
                            cell.SetCellValue(this.packingParser.GetDateTime());
                            break;

                        case 1:
                            cell.SetCellValue(item.GetNameProduct());
                            break;

                        case 2:
                            cell.SetCellValue(item.GetColor1());
                            break;

                        case 3:
                            cell.SetCellValue(item.GetColor2());
                            break;

                        case 4:
                            cell.SetCellValue(item.GetProductSize());
                            break;

                        case 5:
                            cell.SetCellValue(databaseItem.GetMaxPacketSize());
                            break;

                        case 6:
                            cell.SetCellValue(1);
                            break;

                        case 7:
                            cell.SetCellValue(numberPacket_sheet_1);
                            break;

                        case 8:
                            cell.SetCellValue(databaseItem.GetMaxPacketSize());
                            break;

                        case 9:
                            cell.SetCellValue(databaseItem.GetNetWeight());
                            break;

                        case 10:
                            cell.SetCellValue(databaseItem.GetAllWeight());
                            break;

                        case 11:
                            cell.SetCellValue(databaseItem.GetPacketInformation());
                            break;

                        default:
                            break;
                        }
                    }
                }
            }

            if (item.GetMergerList() != null && item.GetMergerList().Count > 0)
            {
                foreach (MergerItem merger in item.GetMergerList())
                {
                    r = sheet_1.CreateRow(rowIndex_sheet_1);
                    rowIndex_sheet_1++;

                    for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
                    {
                        ICell cell = r.CreateCell(idex);
                        cell.CellStyle = sheet_1_headerStyle;

                        switch (idex)
                        {
                        case 0:
                            cell.SetCellValue(this.packingParser.GetDateTime());
                            break;

                        case 1:
                            cell.SetCellValue(merger.GetNameProduct());
                            break;

                        case 2:
                            cell.SetCellValue(merger.GetColor1());
                            break;

                        case 3:
                            cell.SetCellValue(merger.GetColor2());
                            break;

                        case 4:
                            cell.SetCellValue(merger.GetProductSize());
                            break;

                        case 5:
                            cell.SetCellValue(merger.GetQuantity());
                            cell.CellStyle = sheet_1_headerStyle_2;
                            break;

                        case 6:
                            break;

                        case 7:
                            cell.SetCellValue(numberPacket_sheet_1);
                            break;

                        case 8:
                            break;

                        case 9:
                            //cell.SetCellValue(databaseItem.GetNetWeight());
                            break;

                        case 10:
                            //cell.SetCellValue(databaseItem.GetAllWeight());
                            break;

                        case 11:
                            //cell.SetCellValue(databaseItem.GetPacketInformation());
                            break;

                        default:
                            break;
                        }
                    }
                }

                r = sheet_1.GetRow(rowIndex_sheet_1 - (1 + item.GetMergerList().Count));
                String fomular = "SUM(F" + (rowIndex_sheet_1 - item.GetMergerList().Count) + ":F" + rowIndex_sheet_1 + ")";
                if (r.GetCell(8) != null)
                {
                    r.GetCell(8).CellFormula = fomular;
                }

                NPOI.SS.Util.CellRangeAddress range_1 = new NPOI.SS.Util.CellRangeAddress(rowIndex_sheet_1 - (1 + item.GetMergerList().Count),
                                                                                          rowIndex_sheet_1 - 1, 6, 6);

                NPOI.SS.Util.CellRangeAddress range_2 = new NPOI.SS.Util.CellRangeAddress(rowIndex_sheet_1 - (1 + item.GetMergerList().Count),
                                                                                          rowIndex_sheet_1 - 1, 8, 8);
                sheet_1.AddMergedRegion(range_1);
                sheet_1.AddMergedRegion(range_2);
            }
        }
Example #45
0
 /**
  * @return the new range(s) to replace the supplied ones.  <c>null</c> if no merge is possible
  */
 private static CellRangeAddress[] MergeRanges(CellRangeAddress range1, CellRangeAddress range2)
 {
     int x = Intersect(range1, range2);
     switch (x)
     {
         case CellRangeUtil.NO_INTERSECTION:
             if (HasExactSharedBorder(range1, range2))
             {
                 return new CellRangeAddress[] { CreateEnclosingCellRange(range1, range2), };
             }
             // else - No intersection and no shared border: do nothing
             return null;
         case CellRangeUtil.OVERLAP:
             return ResolveRangeOverlap(range1, range2);
         case CellRangeUtil.INSIDE:
             // Remove range2, since it is completely inside of range1
             return new CellRangeAddress[] { range1, };
         case CellRangeUtil.ENCLOSES:
             // range2 encloses range1, so replace it with the enclosing one
             return new CellRangeAddress[] { range2, };
     }
     throw new InvalidOperationException("unexpected intersection result (" + x + ")");
 }
Example #46
0
        public static IRow CopyRow(ISheet sheet, int sourceRowIndex, int targetRowIndex)
        {
            if (sourceRowIndex == targetRowIndex)
            {
                throw new ArgumentException("sourceIndex and targetIndex cannot be same");
            }
            // Get the source / new row
            IRow newRow    = sheet.GetRow(targetRowIndex);
            IRow sourceRow = sheet.GetRow(sourceRowIndex);

            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                sheet.ShiftRows(targetRowIndex, sheet.LastRowNum, 1);
            }
            else
            {
                newRow = sheet.CreateRow(targetRowIndex);
            }

            // Loop through source columns to add to new row
            for (int i = sourceRow.FirstCellNum; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    continue;
                }
                ICell newCell = newRow.CreateCell(i);

                if (oldCell.CellStyle != null)
                {
                    // apply style from old cell to new cell
                    newCell.CellStyle = oldCell.CellStyle;
                }

                // If there is a cell comment, copy
                if (oldCell.CellComment != null)
                {
                    newCell.CellComment = oldCell.CellComment;
                }

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null)
                {
                    newCell.Hyperlink = oldCell.Hyperlink;
                }

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                case CellType.Blank:
                    newCell.SetCellValue(oldCell.StringCellValue);
                    break;

                case CellType.Boolean:
                    newCell.SetCellValue(oldCell.BooleanCellValue);
                    break;

                case CellType.Error:
                    newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                    break;

                case CellType.Formula:
                    newCell.SetCellFormula(oldCell.CellFormula);
                    break;

                case CellType.Numeric:
                    newCell.SetCellValue(oldCell.NumericCellValue);
                    break;

                case CellType.String:
                    newCell.SetCellValue(oldCell.RichStringCellValue);
                    break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                                                                                (newRow.RowNum +
                                                                                 (cellRangeAddress.LastRow - cellRangeAddress.FirstRow
                                                                                 )),
                                                                                cellRangeAddress.FirstColumn,
                                                                                cellRangeAddress.LastColumn);
                    sheet.AddMergedRegion(newCellRangeAddress);
                }
            }
            return(newRow);
        }
Example #47
0
        /**
         * @param crB never a full row or full column range
         * @return an array including <b>this</b> <tt>CellRange</tt> and all parts of <tt>range</tt>
         * outside of this range
         */
        private static CellRangeAddress[] SliceUp(CellRangeAddress crA, CellRangeAddress crB)
        {
            ArrayList temp = new ArrayList();

            // Chop up range horizontally and vertically
            temp.Add(crB);
            if (!crA.IsFullColumnRange)
            {
                temp = CutHorizontally(crA.FirstRow, temp);
                temp = CutHorizontally(crA.LastRow + 1, temp);
            }
            if (!crA.IsFullRowRange)
            {
                temp = CutVertically(crA.FirstColumn, temp);
                temp = CutVertically(crA.LastColumn + 1, temp);
            }
            CellRangeAddress[] crParts = ToArray(temp);

            // form result array
            temp.Clear();
            temp.Add(crA);

            for (int i = 0; i < crParts.Length; i++)
            {
                CellRangeAddress crPart = crParts[i];
                // only include parts that are not enclosed by this
                if (Intersect(crA, crPart) != ENCLOSES)
                {
                    temp.Add(crPart);
                }
            }
            return ToArray(temp);
        }
Example #48
0
 private static Region ConvertToRegion(CellRangeAddress cr)
 {
     return(new Region(cr.FirstRow, cr.FirstColumn, cr.LastRow, cr.LastColumn));
 }
Example #49
0
        /// <summary>
        /// 读取上传文件中的Excel(返回表格,如果需要导入,还需要格式化表头)
        /// </summary>
        /// <param name="workbook">Excel工作本</param>
        /// <param name="sheetIndex">第N个Sheet表格</param>
        /// <param name="isMergedCellName">是否把合并列头的名称连接起来</param>
        /// <param name="startIndex">开始索引</param>
        /// <param name="headCrossRowNum">头部跨行数(为0时自动识别)</param>
        /// <returns></returns>
        private static MDataTable ReadExcel(IWorkbook workbook, Stream stream = null, int sheetIndex = 0, int startIndex = 0, int headCrossRowNum = 0, bool isMergedCellName = false)
        {
            MDataTable dt = new MDataTable();

            try
            {
                if (workbook != null)
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    dt.TableName   = sheet.SheetName;
                    dt.DynamicData = sheet;
                    IRow excelRow     = sheet.GetRow(startIndex);
                    int  dataRowStart = startIndex;

                    if (headCrossRowNum <= 0)
                    {
                        dataRowStart += 1;
                        #region 遍历、找出(头部跨行数)最大行。用最大行进行遍历列(如果为空,往上一级找)
                        int mIndex = 0;
                        for (int i = 0; i < excelRow.Cells.Count; i++)
                        {
                            ICell cell = excelRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);// .Cells[i];
                            if (cell.IsMergedCell)
                            {
                                NPOI.SS.Util.CellRangeAddress range = sheet.GetCellRange(cell);//获取范围块。
                                if (range != null)
                                {
                                    dataRowStart = Math.Max(dataRowStart, range.LastRow + 1);//设置数据的读取行数。
                                    mIndex++;
                                    i += range.LastColumn - range.FirstColumn;
                                }
                            }
                        }
                        #endregion
                    }
                    else
                    {
                        dataRowStart += headCrossRowNum;
                    }

                    //读取列头。
                    if (dataRowStart > 1)
                    {
                        excelRow = sheet.GetRow(dataRowStart - 1);
                    }
                    dt.RecordsAffected        = dataRowStart;
                    dt.Columns.CheckDuplicate = false;

                    #region 读取列头
                    //if (excelRow.FirstCellNum > 0)
                    //{
                    //    for (int i = 0; i < excelRow.FirstCellNum; i++)
                    //    {
                    //        string columnName = "该列头为空_" + i;
                    //        dt.Columns.Add(columnName);
                    //    }
                    //}
                    int emptyCellCount = 0;//兼容处理错误的Excel格式(读了256个空格列)
                    for (int i = 0; i < excelRow.Cells.Count; i++)
                    {
                        string columnName = string.Empty;
                        for (int j = dataRowStart; j > startIndex; j--)
                        {
                            #region MyRegion
                            IRow  row = sheet.GetRow(j - 1);
                            ICell cell;
                            try
                            {
                                cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);//不能用GetCell(i),会多出一行导致下面错误位。
                            }
                            catch (Exception)
                            {
                                continue;
                            }
                            string name = cell.ToString().Trim();                          // .StringCellValue.Trim();
                            if (!string.IsNullOrEmpty(name) && !columnName.Contains(name)) //
                            {
                                columnName += name + "_";
                            }
                            else if (j != dataRowStart && cell.IsMergedCell)
                            {
                                cell = sheet.GetMergedRegion(cell);//获取范围块。
                                if (cell != null)
                                {
                                    name = cell.ToString().Trim();
                                    if (!string.IsNullOrEmpty(name) && !columnName.Contains(name))
                                    {
                                        columnName += name + "_";
                                    }
                                }
                            }
                            if (!isMergedCellName && !string.IsNullOrEmpty(columnName))
                            {
                                break;
                            }
                            #endregion
                        }
                        columnName = columnName.TrimEnd('_').Trim();
                        if (string.IsNullOrEmpty(columnName))
                        {
                            if (emptyCellCount > 30)//连续30次空格列
                            {
                                break;
                            }
                            emptyCellCount++;
                            columnName = "该列头为空_" + i;
                        }
                        else
                        {
                            emptyCellCount = 0;//只要一个正常,即回归索引
                        }
                        if (dt.Columns.Contains(columnName))
                        {
                            columnName += "_" + i;
                        }
                        dt.Columns.Add(columnName);
                    }
                    //移除空格列
                    if (emptyCellCount > 0)
                    {
                        dt.Columns.RemoveRange(dt.Columns.Count - emptyCellCount, emptyCellCount);
                    }
                    if (dt.Columns.Count > 0)
                    {
                        dt.Conn = dt.Columns.Count.ToString();//找个变量存储实际的列的长度,在SetError中使用。(dt可能在SetError前列被变更)
                    }
                    #endregion

                    ICell sheetCell;
                    int   emptyCount = 0;
                    for (int i = dataRowStart; i <= sheet.LastRowNum; i++)
                    {
                        excelRow = sheet.GetRow(i);
                        if (excelRow == null)
                        {
                            break;
                        }
                        MDataRow tbRow = dt.NewRow();
                        bool     isOk  = false;
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            #region 读一行
                            sheetCell = excelRow.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL);
                            if (sheetCell != null)
                            {
                                string value = string.Empty;
                                if (sheetCell.CellType == CellType.Numeric)
                                {
                                    try
                                    {
                                        if (sheetCell.ToString().Split('/', '-').Length > 1)
                                        {
                                            value = sheetCell.DateCellValue.ToString();
                                        }
                                        else
                                        {
                                            value = sheetCell.NumericCellValue.ToString();
                                        }
                                    }
                                    catch
                                    {
                                        value = sheetCell.ToString();
                                    }
                                }
                                else if (sheetCell.CellType == CellType.Formula)
                                {
                                    try
                                    {
                                        /*公式不一定是Numeric的取值,也有可能是=G4 这种,然而单元格G4不是数字;
                                         * 公式单元格也有可能读取错误 #VALUE!  #REF! 等*/
                                        CellType resultType = sheetCell.CachedFormulaResultType;
                                        switch (resultType)
                                        {
                                        case CellType.Boolean:
                                            value = sheetCell.BooleanCellValue.ToString();
                                            break;

                                        case CellType.Numeric:
                                            value = sheetCell.NumericCellValue.ToString();
                                            break;

                                        case CellType.Blank:
                                        case CellType.Error:
                                        case CellType.Unknown:
                                            value = string.Empty;
                                            break;

                                        default:
                                            value = sheetCell.StringCellValue;
                                            break;
                                        }


                                        //value = sheetCell.NumericCellValue.ToString(); //由公式取值
                                    }
                                    catch
                                    {
                                    }
                                }
                                else
                                {
                                    value = sheetCell.ToString();
                                }
                                value = value.Trim();
                                if (!isOk && !string.IsNullOrEmpty(value))
                                {
                                    isOk = true;
                                }
                                if (!string.IsNullOrEmpty(value))//空值当Null值处理,避免字段有Check对空值的约束
                                {
                                    tbRow.Set(sheetCell.ColumnIndex, value);
                                }
                            }
                            #endregion
                        }
                        if (isOk)//忽略空行数据。
                        {
                            dt.Rows.Add(tbRow);
                        }
                        else
                        {
                            if (dt.Rows.Count == 0)
                            {
                                dt.RecordsAffected++;
                            }
                            emptyCount++;
                            if (emptyCount > 1)//超过2次空格行,跳出。
                            {
                                break;
                            }
                        }
                    }
                    workbook.Close();//关闭了,dt.DynamicData带出了Sheet,还是可以后续使用(估计NPOI的没处理)
                }
            }
            catch (Exception err)
            {
                Log.WriteLogToTxt(err);
            }
            return(dt);
        }
Example #50
0
    public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str, NPOI.SS.Util.CellRangeAddress region, HSSFSheet sh)
    {
        ICellStyle cellStyle = wb.CreateCellStyle();

        //定义几种字体
        //也可以一种字体,写一些公共属性,然后在下面需要时加特殊的
        IFont font12 = wb.CreateFont();

        font12.FontHeightInPoints = 12;
        font12.Boldweight         = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
        font12.FontName           = "微软雅黑";

        IFont font = wb.CreateFont();

        font.FontName = "微软雅黑";
        //font.Underline = 1;下划线

        IFont fontcolorblue = wb.CreateFont();

        fontcolorblue.Color    = HSSFColor.OliveGreen.Black.Index;
        fontcolorblue.IsItalic = true;//下划线
        fontcolorblue.FontName = "微软雅黑";

        //边框
        cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyle.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin; //BorderLeft
        if (region != null)
        {
            for (int i = region.FirstRow; i <= region.LastRow; i++)
            {
                IRow row = HSSFCellUtil.GetRow(i, sh);
                for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                {
                    ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
                    singleCell.CellStyle = cellStyle;
                }
            }
        }

        //边框颜色
        cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
        cellStyle.TopBorderColor    = HSSFColor.OliveGreen.Black.Index;
        cellStyle.LeftBorderColor   = HSSFColor.OliveGreen.Black.Index;
        cellStyle.RightBorderColor  = HSSFColor.OliveGreen.Black.Index;

        //背景图形,我没有用到过。感觉很丑
        //cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.Black.Index;
        //cellStyle.FillForegroundColor = HSSFColor.OliveGreen.Black.Index;
        //cellStyle.FillPattern = FillPatternType.NO_FILL;
        //cellStyle.FillForegroundColor = HSSFColor.White.Index;
        //cellStyle.FillBackgroundColor = HSSFColor.Black.Index;

        //水平对齐
        cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
        //垂直对齐
        cellStyle.VerticalAlignment = VerticalAlignment.Center;
        //自动换行
        cellStyle.WrapText = true;

        //缩进;当设置为1时,前面留的空白太大了。希旺官网改进。或者是我设置的不对
        //cellStyle.Indention = 0;

        //上面基本都是设共公的设置
        //下面列出了常用的字段类型
        switch (str)
        {
        case stylexls.头:
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            HSSFPalette palette = ((HSSFWorkbook)wb).GetCustomPalette();
            //HSSFColor newColor = palette.AddColor((byte)153, (byte)204, (byte)255);
            palette.SetColorAtIndex((short)10, (byte)227, (byte)232, (byte)227);
            cellStyle.FillPattern         = FillPattern.SolidForeground; // NoFill;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;

            cellStyle.SetFont(font12);
            break;

        case stylexls.时间:
            IDataFormat datastyle = wb.CreateDataFormat();

            cellStyle.DataFormat = datastyle.GetFormat("yyyy-mm-dd");
            cellStyle.SetFont(font);
            break;

        case stylexls.数字:
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
            cellStyle.SetFont(font);
            break;

        case stylexls.钱:
            //IDataFormat format = wb.CreateDataFormat();
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("¥#,##0");
            cellStyle.SetFont(font);
            break;

        case stylexls.url:
            fontcolorblue.Underline = FontUnderlineType.None;
            cellStyle.SetFont(fontcolorblue);
            break;

        case stylexls.百分比:
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
            cellStyle.SetFont(font);
            break;

        case stylexls.中文大写:
            IDataFormat format1 = wb.CreateDataFormat();
            cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
            cellStyle.SetFont(font);
            break;

        case stylexls.科学计数法:
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
            cellStyle.SetFont(font);
            break;

        case stylexls.默认:
            cellStyle.SetFont(font);
            break;

        case stylexls.居中:
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.SetFont(font);
            break;

        case stylexls.商品导入头:
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            HSSFPalette pale = ((HSSFWorkbook)wb).GetCustomPalette();
            pale.SetColorAtIndex((short)30, (byte)11, (byte)87, (byte)235);
            cellStyle.FillPattern         = FillPattern.SolidForeground; // NoFill;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;

            cellStyle.SetFont(font12);
            break;

        case stylexls.导入说明:

            HSSFPalette pale1 = ((HSSFWorkbook)wb).GetCustomPalette();
            pale1.SetColorAtIndex((short)61, (byte)227, (byte)232, (byte)227);
            cellStyle.FillPattern         = FillPattern.SolidForeground; // NoFill;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
            IFont font2 = wb.CreateFont();
            font2.FontName           = "微软雅黑";
            font2.FontHeightInPoints = 9;
            cellStyle.SetFont(font2);
            break;

        case stylexls.导入红字:
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            HSSFPalette palebgss = ((HSSFWorkbook)wb).GetCustomPalette();
            palebgss.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
            cellStyle.FillPattern         = FillPattern.SolidForeground; // NoFill;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            IFont fontRed = wb.CreateFont();
            fontRed.FontName = "微软雅黑";
            fontRed.Color    = HSSFColor.OliveGreen.Red.Index;
            cellStyle.SetFont(fontRed);
            break;

        case stylexls.导入错误提示:
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            HSSFPalette paleError = ((HSSFWorkbook)wb).GetCustomPalette();
            paleError.SetColorAtIndex((short)10, (byte)255, (byte)0, (byte)0);
            cellStyle.FillPattern         = FillPattern.SolidForeground; // NoFill;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;

            cellStyle.SetFont(font);
            break;

        case stylexls.导入背景色:
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            HSSFPalette palebgs = ((HSSFWorkbook)wb).GetCustomPalette();
            palebgs.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
            cellStyle.FillPattern         = FillPattern.SolidForeground; // NoFill;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            cellStyle.SetFont(font);
            break;
        }
        return(cellStyle);
    }
Example #51
0
        /**
         * Check if the specified cell range has a shared border with the current range.
         *
         * @return <c>true</c> if the ranges have a complete shared border (i.e.
         * the two ranges toher make a simple rectangular region.
         */
        public static bool HasExactSharedBorder(CellRangeAddress crA, CellRangeAddress crB)
        {
            int oFirstRow = crB.FirstRow;
            int oLastRow = crB.LastRow;
            int oFirstCol = crB.FirstColumn;
            int oLastCol = crB.LastColumn;

            if (crA.FirstRow > 0 && crA.FirstRow - 1 == oLastRow ||
                oFirstRow > 0 && oFirstRow - 1 == crA.LastRow)
            {
                // ranges have a horizontal border in common
                // make sure columns are identical:
                return crA.FirstColumn == oFirstCol && crA.LastColumn == oLastCol;
            }

            if (crA.FirstColumn > 0 && crA.FirstColumn - 1 == oLastCol ||
                oFirstCol > 0 && crA.LastColumn == oFirstCol - 1)
            {
                // ranges have a vertical border in common
                // make sure rows are identical:
                return crA.FirstRow == oFirstRow && crA.LastRow == oLastRow;
            }
            return false;
        }
        private void CreateMergerItem(int type, ParkingParserItem item, PackingListItem packItem)
        {
            NPOI.SS.UserModel.IRow row;
            int start_index;
            int end_index;

            int total = Sheet_2_GetNumberOfColum();

            CombineMergerListItem combine = new CombineMergerListItem(packItem);

            combine.RunCombineMergerListItem();

            List <CombineItem> listCombine = combine.GetListCombineItem();

            ICell cell;
            ICell cellSum = null;

            start_index = sheet_2_rowIndex;

            for (int itemIndex = 0; itemIndex < listCombine.Count; itemIndex++)
            {
                //create new row
                row = sheet_2.CreateRow(sheet_2_rowIndex);
                sheet_2_rowIndex++;

                for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++)
                {
                    cell = row.CreateCell(run);

                    cell.CellStyle = sheet_2_mainStyle_2;

                    switch (run)
                    {
                    case 0:
                        if (itemIndex == 0)
                        {
                            sheet_2_numberPacket++;
                            cell.SetCellValue(sheet_2_numberPacket + "");
                        }
                        break;

                    case 1:
                        if (itemIndex == 0)
                        {
                            cell.SetCellValue(1);
                        }
                        break;

                    case 2:
                        cell.SetCellValue(packItem.GetNameProduct());
                        break;

                    case 3:
                        cell.SetCellValue(listCombine.ElementAt(itemIndex).GetColor1());
                        break;

                    case 4:
                        cell.SetCellValue(listCombine.ElementAt(itemIndex).GetColor2());
                        break;

                    default:
                        if ((total - 4) == run)
                        {
                            if (itemIndex == 0)
                            {
                                cellSum = cell;
                            }
                        }
                        else if ((total - 3) == run)
                        {
                            if (itemIndex == 0)
                            {
                                cell.SetCellValue("PCS");
                            }
                        }
                        else if ((total - 2) == run)
                        {
                            if (itemIndex == 0)
                            {
                                String formula = item.GetDatabaseItem().GetNetWeight() + "*B" + sheet_2_rowIndex;
                                cell.SetCellFormula(formula);
                            }
                        }
                        else if ((total - 1) == run)
                        {
                            if (itemIndex == 0)
                            {
                                String formula = item.GetDatabaseItem().GetAllWeight() + "*B" + sheet_2_rowIndex;
                                cell.SetCellFormula(formula);
                            }
                        }
                        else
                        {
                            cell.SetCellValue("");
                        }
                        break;
                    }
                }

                foreach (CombineEle element in listCombine.ElementAt(itemIndex).GetListElement())
                {
                    int size_index = Sheet_2_GetColumIndex(type, element.GetSize());

                    ICell cellSize = row.GetCell(size_index);

                    cellSize.SetCellValue(element.GetQuality());
                }
            }

            end_index = sheet_2_rowIndex - 1;

            String columName  = Sheet_2_GetStringOfColum(Sheet_2_GetNumberOfColum() - 4);
            String formulaSum = "SUM(F" + (start_index + 1) + ":" + columName + (end_index + 1) + ")";

            if (cellSum != null)
            {
                cellSum.SetCellFormula(formulaSum);
            }

            if (end_index != start_index)
            {
                NPOI.SS.Util.CellRangeAddress range_1 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, 0, 0);
                NPOI.SS.Util.CellRangeAddress range_2 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, 1, 1);
                NPOI.SS.Util.CellRangeAddress range_3 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 4, total - 4);
                NPOI.SS.Util.CellRangeAddress range_4 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 3, total - 3);
                NPOI.SS.Util.CellRangeAddress range_5 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 2, total - 2);
                NPOI.SS.Util.CellRangeAddress range_6 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 1, total - 1);

                sheet_2.AddMergedRegion(range_1);
                sheet_2.AddMergedRegion(range_2);
                sheet_2.AddMergedRegion(range_3);
                sheet_2.AddMergedRegion(range_4);
                sheet_2.AddMergedRegion(range_5);
                sheet_2.AddMergedRegion(range_6);

                sheet_2.GetRow(start_index).GetCell(0).CellStyle         = sheet_2_mergerStyle;
                sheet_2.GetRow(start_index).GetCell(1).CellStyle         = sheet_2_mergerStyle;
                sheet_2.GetRow(start_index).GetCell(total - 4).CellStyle = sheet_2_mergerStyle;
                sheet_2.GetRow(start_index).GetCell(total - 3).CellStyle = sheet_2_mergerStyle;
                sheet_2.GetRow(start_index).GetCell(total - 2).CellStyle = sheet_2_mergerStyle;
                sheet_2.GetRow(start_index).GetCell(total - 1).CellStyle = sheet_2_mergerStyle;
            }
        }