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"); }
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); }
/** * 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; } }
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"); }
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; } }
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(); }
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++; } } }
public CFHeaderRecord(CellRangeAddress[] regions, int nRules) { CellRangeAddress[] unmergedRanges = regions; CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(unmergedRanges); CellRanges= mergeCellRanges; field_1_numcf = nRules; }
/// <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); }
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); }
/** * 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; }
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); }
/** * 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; }
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); } }
/** * 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++; } }
/** * 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 ); }
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; } } }
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"); } }
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); }
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); }
private static CellRangeAddress[] ToArray(ArrayList temp) { CellRangeAddress[] result = new CellRangeAddress[temp.Count]; result = (CellRangeAddress[])temp.ToArray(typeof(CellRangeAddress)); return result; }
// 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); }
/** * 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); }
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; } } } }
/** * 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); }
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); } }
public void AddCellRangeAddress(CellRangeAddress cra) { _list.Add(cra); }
/// <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; }
/** * @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)); }
public static bool ContainsCell(CellRangeAddress cr, int rowIx, int colIx) { return(cr.IsInRange(rowIx, colIx)); }
/** * 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); }
/** * 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 }
/// <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); }
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); }
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); }
//////////////////////////////////////////////////////////////////// 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); } }
/** * @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 + ")"); }
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); }
/** * @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); }
private static Region ConvertToRegion(CellRangeAddress cr) { return(new Region(cr.FirstRow, cr.FirstColumn, cr.LastRow, cr.LastColumn)); }
/// <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); }
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); }
/** * 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; } }