public void TestClonePictures() { IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithImages.xls"); InternalWorkbook iwb = ((HSSFWorkbook)wb).Workbook; iwb.FindDrawingGroup(); for (int pictureIndex = 1; pictureIndex <= 4; pictureIndex++) { EscherBSERecord bse = iwb.GetBSERecord(pictureIndex); Assert.AreEqual(1, bse.Ref); } wb.CloneSheet(0); for (int pictureIndex = 1; pictureIndex <= 4; pictureIndex++) { EscherBSERecord bse = iwb.GetBSERecord(pictureIndex); Assert.AreEqual(2, bse.Ref); } wb.CloneSheet(0); for (int pictureIndex = 1; pictureIndex <= 4; pictureIndex++) { EscherBSERecord bse = iwb.GetBSERecord(pictureIndex); Assert.AreEqual(3, bse.Ref); } }
/// <summary> /// Writes course schedules for each room in the excel file. /// </summary> /// <param name="courseRepo">The collection of courses.</param> /// <param name="roomRepo">The collection of rooms.</param> public void Print(ICourseRepository courseRepo, IRoomRepository roomRepo) { List <Course> courses = courseRepo.Courses.ToList(); var coursesInRoom = from course in courses where course.State == Course.CourseState.Assigned && course.MeetingDays != null group course by course.RoomAssignment; foreach (var courseGroup in coursesInRoom) { Room room = courseGroup.Key; ISheet sheet = _workbook.CloneSheet(_workbook.GetSheetIndex(_scheduleTemplate)); var sheetIndex = _workbook.GetSheetIndex(sheet); _workbook.SetSheetName(sheetIndex, room.RoomName + " " + room.RoomType); _workbook.SetSheetHidden(sheetIndex, SheetState.Visible); ICell cell = sheet.GetRow(RoomNameLocation.Item1).GetCell(RoomNameLocation.Item2); cell.SetCellValue(room.RoomName); cell = sheet.GetRow(RoomCapacityLocation.Item1).GetCell(RoomCapacityLocation.Item2); cell.SetCellValue(string.Format("Cap: {0}", room.Capacity)); PrintCourses(sheet, courseGroup.ToList()); printLegend(sheet); } _workbook.SortWorksheets(); _workbook.SetActiveSheet(0); _workbook.WriteToFile(_outputFile); }
public void TestCloneSheetMultipleTimes() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ICreationHelper factory = workbook.GetCreationHelper(); ISheet sheet = workbook.CreateSheet("Test Clone"); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); cell.SetCellValue(factory.CreateRichTextString("Clone_test")); //Clone the sheet multiple times workbook.CloneSheet(0); workbook.CloneSheet(0); Assert.IsNotNull(workbook.GetSheet("Test Clone")); Assert.IsNotNull(workbook.GetSheet("Test Clone (2)")); Assert.AreEqual("Test Clone (3)", workbook.GetSheetName(2)); Assert.IsNotNull(workbook.GetSheet("Test Clone (3)")); workbook.RemoveSheetAt(0); workbook.RemoveSheetAt(0); workbook.RemoveSheetAt(0); workbook.CreateSheet("abc ( 123)"); workbook.CloneSheet(0); Assert.AreEqual("abc (124)", workbook.GetSheetName(1)); }
/// <summary> /// 生成工作簿 /// </summary> /// <param name="fileName"></param> /// <param name="dtData"></param> /// <returns></returns> private string BuildTableWorkbook(ExportTemplateEntity helpEntity, IEnumerable <CellHelpEntity> list, params DynamicEntity[] entitys) { string errorMessage = string.Empty; List <ISheet> listSheet = new List <ISheet>(); ISheet sheet = list.FirstOrDefault().Cell.Sheet; int sheetNum = workBook.GetSheetIndex(sheet.SheetName); listSheet.Add(sheet); int len = entitys.Length; if (len > 1) { for (var i = 0; i < len - 1; i++) { ISheet tempSheet = workBook.CloneSheet(sheetNum); listSheet.Add(tempSheet); } } int index = 0; foreach (var entity in entitys) { BuildTableSheet(listSheet[index], list, entity); index++; } return(errorMessage); }
public void Test53691() { ISheetConditionalFormatting cf; IWorkbook wb = HSSFITestDataProvider.Instance.OpenSampleWorkbook("53691.xls"); /* * FileInputStream s = new FileInputStream("C:\\temp\\53691bbadfixed.xls"); * try { * wb = new HSSFWorkbook(s); * } finally { * s.Close(); * } * * wb.RemoveSheetAt(1);*/ // Initially it is good WriteTemp53691(wb, "agood"); // clone sheet corrupts it ISheet sheet = wb.CloneSheet(0); WriteTemp53691(wb, "bbad"); // removing the sheet Makes it good again wb.RemoveSheetAt(wb.GetSheetIndex(sheet)); WriteTemp53691(wb, "cgood"); // cloning again and removing the conditional formatting Makes it good again sheet = wb.CloneSheet(0); RemoveConditionalFormatting(sheet); WriteTemp53691(wb, "dgood"); // cloning the conditional formatting manually Makes it bad again cf = sheet.SheetConditionalFormatting; ISheetConditionalFormatting scf = wb.GetSheetAt(0).SheetConditionalFormatting; for (int j = 0; j < scf.NumConditionalFormattings; j++) { cf.AddConditionalFormatting(scf.GetConditionalFormattingAt(j)); } WriteTemp53691(wb, "ebad"); // remove all conditional formatting for comparing BIFF output RemoveConditionalFormatting(sheet); RemoveConditionalFormatting(wb.GetSheetAt(0)); WriteTemp53691(wb, "fgood"); wb.Close(); }
/** * cloning sheets with feat records */ public void TestCloneSheetWithFeatRecord() { IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("46136-WithWarnings.xls"); wb.CloneSheet(0); }
public void TestCloneSheet() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ICreationHelper factory = workbook.GetCreationHelper(); ISheet sheet = workbook.CreateSheet("Test Clone"); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); ICell cell2 = row.CreateCell(1); cell.SetCellValue(factory.CreateRichTextString("Clone_test")); cell2.CellFormula = (/*setter*/ "SIN(1)"); ISheet clonedSheet = workbook.CloneSheet(0); IRow clonedRow = clonedSheet.GetRow(0); //Check for a good clone Assert.AreEqual(clonedRow.GetCell(0).RichStringCellValue.String, "Clone_test"); //Check that the cells are not somehow linked cell.SetCellValue(factory.CreateRichTextString("Difference Check")); cell2.CellFormula = (/*setter*/ "cos(2)"); if ("Difference Check".Equals(clonedRow.GetCell(0).RichStringCellValue.String)) { Assert.Fail("string cell not properly Cloned"); } if ("COS(2)".Equals(clonedRow.GetCell(1).CellFormula)) { Assert.Fail("formula cell not properly Cloned"); } Assert.AreEqual(clonedRow.GetCell(0).RichStringCellValue.String, "Clone_test"); Assert.AreEqual(clonedRow.GetCell(1).CellFormula, "SIN(1)"); }
public void Test22720() { IWorkbook workBook = _testDataProvider.CreateWorkbook(); workBook.CreateSheet("TEST"); ISheet template = workBook.GetSheetAt(0); template.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2)); template.AddMergedRegion(new CellRangeAddress(1, 2, 0, 2)); ISheet clone = workBook.CloneSheet(0); int originalMerged = template.NumMergedRegions; Assert.AreEqual(2, originalMerged, "2 merged regions"); //remove merged regions from clone for (int i = template.NumMergedRegions - 1; i >= 0; i--) { clone.RemoveMergedRegion(i); } Assert.AreEqual(originalMerged, template.NumMergedRegions, "Original Sheet's Merged Regions were Removed"); //check if template's merged regions are OK if (template.NumMergedRegions > 0) { // fetch the first merged region...EXCEPTION OCCURS HERE template.GetMergedRegion(0); } //make sure we dont exception }
public void Bug48495() { try { IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48495.xlsx"); assertSheetOrder(wb, "Sheet1"); ISheet sheet = wb.GetSheetAt(0); sheet.ShiftRows(2, sheet.LastRowNum, 1, true, false); IRow newRow = sheet.GetRow(2); if (newRow == null) { newRow = sheet.CreateRow(2); } newRow.CreateCell(0).SetCellValue(" Another Header"); wb.CloneSheet(0); assertSheetOrder(wb, "Sheet1", "Sheet1 (2)"); IWorkbook read = XSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.IsNotNull(read); assertSheetOrder(read, "Sheet1", "Sheet1 (2)"); } catch (Exception) { } }
public void Print(ICourseRepository courseRepo, IRoomRepository roomRepo) { List <Course> courses = courseRepo.Courses; var test = courses.FindAll(x => x.RoomAssignment?.Equals("PKI 157") == true); List <Course> roomedCourses = courses.FindAll(x => x.AlreadyAssignedRoom && x.MeetingDays != null); Dictionary <string, List <Course> > roomCourseMap = getRoomNameToCoursesMap(roomedCourses); List <string> roomWithCourses = roomCourseMap.Keys.ToList <string>(); foreach (string room in roomWithCourses) { ISheet sheet = _workbook.CloneSheet(_workbook.GetSheetIndex(_scheduleTemplate)); var sheetIndex = _workbook.GetSheetIndex(sheet); _workbook.SetSheetName(sheetIndex, room); _workbook.SetSheetHidden(sheetIndex, SheetState.Visible); ICell cell = sheet.GetRow(RoomNameLocation.Item1).GetCell(RoomNameLocation.Item2); cell.SetCellValue(room); PrintCourses(sheet, roomCourseMap[room]); printLegend(sheet); } SortWorkbookSheets(); using (var fileStream = File.OpenWrite(_outputFile)) { _workbook.Write(fileStream); } }
public void CloneSheet() { IWorkbook book = _testDataProvider.CreateWorkbook(); ISheet sheet = book.CreateSheet("TEST"); sheet.CreateRow(0).CreateCell(0).SetCellValue("Test"); sheet.CreateRow(1).CreateCell(0).SetCellValue(36.6); sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2)); sheet.AddMergedRegion(new CellRangeAddress(2, 3, 0, 2)); Assert.IsTrue(sheet.IsSelected); ISheet ClonedSheet = book.CloneSheet(0); Assert.AreEqual("TEST (2)", ClonedSheet.SheetName); Assert.AreEqual(2, ClonedSheet.PhysicalNumberOfRows); Assert.AreEqual(2, ClonedSheet.NumMergedRegions); Assert.IsFalse(ClonedSheet.IsSelected); //Cloned sheet is a deep copy, Adding rows or merged regions in the original does not affect the clone sheet.CreateRow(2).CreateCell(0).SetCellValue(1); sheet.AddMergedRegion(new CellRangeAddress(4, 5, 0, 2)); Assert.AreEqual(2, ClonedSheet.PhysicalNumberOfRows); Assert.AreEqual(2, ClonedSheet.NumMergedRegions); ClonedSheet.CreateRow(2).CreateCell(0).SetCellValue(1); ClonedSheet.AddMergedRegion(new CellRangeAddress(6, 7, 0, 2)); Assert.AreEqual(3, ClonedSheet.PhysicalNumberOfRows); Assert.AreEqual(3, ClonedSheet.NumMergedRegions); book.Close(); }
public void TestCloneSheetIntValid() { IWorkbook wb = _testDataProvider.CreateWorkbook(); wb.CreateSheet("Sheet01"); wb.CloneSheet(0); Assert.AreEqual(2, wb.NumberOfSheets); try { wb.CloneSheet(2); Assert.Fail("ShouldFail"); } catch (ArgumentException e) { // expected here } }
public int CloneSheet(int arg, string name) { var s = book.CloneSheet(arg); var idx = book.GetSheetIndex(s.SheetName); SetSheetName(idx, name); return(idx); }
/// <summary> /// 将一个工作薄克隆出多个工作薄 /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="sheetCount"></param> public static void CloneOneToManySheet(IWorkbook workbook, ISheet sheet, int sheetCount) { int sheetIndex = workbook.GetSheetIndex(sheet); for (int i = 1; i <= sheetCount - 1; i++) { workbook.CloneSheet(sheetIndex); workbook.SetSheetName(sheetIndex + i, sheet.SheetName + (i + 1).ToString()); } }
public void TestCloneSheetBasic() { IWorkbook b = _testDataProvider.CreateWorkbook(); ISheet s = b.CreateSheet("Test"); s.AddMergedRegion(new CellRangeAddress(0, 1, 0, 1)); ISheet ClonedSheet = b.CloneSheet(0); Assert.AreEqual(1, ClonedSheet.NumMergedRegions, "One merged area"); b.Close(); }
public void Bug47090c() { IWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("47090.xlsx"); assertSheetOrder(workbook, "Sheet1", "Sheet2"); workbook.RemoveSheetAt(0); assertSheetOrder(workbook, "Sheet2"); workbook.CloneSheet(0); assertSheetOrder(workbook, "Sheet2", "Sheet2 (2)"); IWorkbook read = XSSFTestDataSamples.WriteOutAndReadBack(workbook); assertSheetOrder(read, "Sheet2", "Sheet2 (2)"); }
public void Test35084() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet s = wb.CreateSheet("Sheet1"); IRow r = s.CreateRow(0); r.CreateCell(0).SetCellValue(1); r.CreateCell(1).CellFormula = (/*setter*/ "A1*2"); ISheet s1 = wb.CloneSheet(0); r = s1.GetRow(0); Assert.AreEqual(r.GetCell(0).NumericCellValue, 1, 0, "double"); // sanity check Assert.IsNotNull(r.GetCell(1)); Assert.AreEqual(r.GetCell(1).CellFormula, "A1*2", "formula"); }
public void Test57165() { IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("57171_57163_57165.xlsx"); Assert.AreEqual(5, wb.ActiveSheetIndex); RemoveAllSheetsBut(3, wb); Assert.AreEqual(0, wb.ActiveSheetIndex); wb.CreateSheet("New Sheet1"); Assert.AreEqual(0, wb.ActiveSheetIndex); wb.CloneSheet(0); // Throws exception here wb.SetSheetName(1, "New Sheet"); Assert.AreEqual(0, wb.ActiveSheetIndex); //wb.Write(new FileOutputStream("/tmp/57165.xls")); }
public void TestClone() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet = wb.CreateSheet(); String formula = "7"; ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(formula); IFontFormatting fontFmt = rule1.CreateFontFormatting(); fontFmt.SetFontStyle(true, false); IPatternFormatting patternFmt = rule1.CreatePatternFormatting(); patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index); IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2"); IConditionalFormattingRule[] cfRules = { rule1, rule2 }; short col = 1; CellRangeAddress[] regions = { new CellRangeAddress(0, 65535, col, col) }; sheetCF.AddConditionalFormatting(regions, cfRules); try { wb.CloneSheet(0); } catch (Exception e) { if (e.Message.IndexOf("needs to define a clone method") > 0) { Assert.Fail("Indentified bug 45682"); } throw e; } Assert.AreEqual(2, wb.NumberOfSheets); }
public void Test18800() { IWorkbook book = _testDataProvider.CreateWorkbook(); book.CreateSheet("TEST"); ISheet sheet = book.CloneSheet(0); book.SetSheetName(1, "CLONE"); sheet.CreateRow(0).CreateCell(0).SetCellValue("Test"); book = _testDataProvider.WriteOutAndReadBack(book); sheet = book.GetSheet("CLONE"); IRow row = sheet.GetRow(0); ICell cell = row.GetCell(0); Assert.AreEqual("Test", cell.RichStringCellValue.String); }
public void TestPageBreakClones() { IWorkbook b = _testDataProvider.CreateWorkbook(); ISheet s = b.CreateSheet("Test"); s.SetRowBreak(3); s.SetColumnBreak((short)6); ISheet clone = b.CloneSheet(0); Assert.IsTrue(clone.IsRowBroken(3), "Row 3 not broken"); Assert.IsTrue(clone.IsColumnBroken((short)6), "Column 6 not broken"); s.RemoveRowBreak(3); Assert.IsTrue(clone.IsRowBroken(3), "Row 3 still should be broken"); b.Close(); }
public void SheetClone() { // First up, try a simple file IWorkbook b = _testDataProvider.CreateWorkbook(); Assert.AreEqual(0, b.NumberOfSheets); b.CreateSheet("Sheet One"); b.CreateSheet("Sheet Two"); Assert.AreEqual(2, b.NumberOfSheets); b.CloneSheet(0); Assert.AreEqual(3, b.NumberOfSheets); // Now try a problem one with drawing records in it IWorkbook bBack = HSSFTestDataSamples.OpenSampleWorkbook("SheetWithDrawing.xls"); Assert.AreEqual(1, bBack.NumberOfSheets); bBack.CloneSheet(0); Assert.AreEqual(2, bBack.NumberOfSheets); bBack.Close(); b.Close(); }
private void renderByTemplate(DataTable data, int maxrow, Dictionary <string, string> keyValues, List <FillSetting> settings, ISheet sheet, IWorkbook workbook) { foreach (var s in settings) { if (s.Flag == FlagType.DataSource) { ICellStyle style = null; IRow row = sheet.GetRow(s.StartRowIndex); ISheet oldsheet = sheet; int rdrowindex = s.StartRowIndex; for (int p = 0; p < data.Rows.Count; p++, rdrowindex++) { DataRow dr = data.Rows[p]; if (p >= maxrow) { int idx = workbook.GetSheetIndex(oldsheet); sheet = workbook.CloneSheet(idx); for (int yy = 0; yy < sheet.PhysicalNumberOfRows; yy++) { sheet.RemoveRowBreak(yy); } rdrowindex = s.StartRowIndex + 1; } if (p == 0) { foreach (var col in s.Columns) { var cell = row.GetCell(col.ColumnIndex); if (col.ColumnType != CellType.FORMULA) { if (!data.Columns.Contains(col.FieldName)) { cell.SetCellValue(string.Empty); } else { SetCellValue(cell, data.Columns[col.FieldName], dr[col.FieldName].ToString()); } } } } else { sheet.ShiftRows(rdrowindex, rdrowindex + 1, 1, true, false); IRow newrow = sheet.CreateRow(rdrowindex); foreach (var col in s.Columns) { if (col.ColumnType != CellType.FORMULA) { var newcell = newrow.CreateCell(col.ColumnIndex); newcell.CellStyle = row.GetCell(col.ColumnIndex).CellStyle; if (!data.Columns.Contains(col.FieldName)) { newcell.SetCellValue(string.Empty); } else { SetCellValue(newcell, data.Columns[col.FieldName], dr[col.FieldName].ToString()); } } else { var newcell = newrow.CreateCell(col.ColumnIndex); newcell.CellStyle = row.GetCell(col.ColumnIndex).CellStyle; newcell.SetCellFormula(col.FieldName.Replace("{0}", (rdrowindex + 1).ToString())); } } } } for (int zz = 0; zz < s.Columns.Count; zz++) { sheet.AutoSizeColumn(s.Columns[zz].ColumnIndex); } sheet.ForceFormulaRecalculation = true; } } }
private void renderWageList(DataTable data, int maxrow, List <FillSetting> settings, ISheet sheet, IWorkbook workbook, int colHeaderRowIndex, int splitterRowIdx) { IRow repeatHrow = colHeaderRowIndex >= 0 ? sheet.GetRow(colHeaderRowIndex) : null; IRow repeatFrow = splitterRowIdx >= 0 ? sheet.GetRow(splitterRowIdx) : null; foreach (var s in settings) { if (s.Flag == FlagType.DataSource) { ICellStyle style = null; IRow row = sheet.GetRow(s.StartRowIndex); ISheet oldsheet = sheet; int rdrowindex = s.StartRowIndex; for (int p = 0; p < data.Rows.Count; p++, rdrowindex++) { if (repeatHrow != null) { sheet.ShiftRows(rdrowindex, sheet.LastRowNum, 1, true, false); NPOIHelper.CopyRow(sheet, sheet, colHeaderRowIndex, rdrowindex); rdrowindex++; } DataRow dr = data.Rows[p]; if (p >= maxrow) { int idx = workbook.GetSheetIndex(oldsheet); sheet = workbook.CloneSheet(idx); for (int yy = 0; yy < sheet.PhysicalNumberOfRows; yy++) { sheet.RemoveRowBreak(yy); } rdrowindex = s.StartRowIndex + 1; } if (p == 0) { foreach (var col in s.Columns) { var cell = row.GetCell(col.ColumnIndex); if (col.ColumnType != CellType.FORMULA) { if (!data.Columns.Contains(col.FieldName)) { break; } SetCellValue(cell, data.Columns[col.FieldName], dr[col.FieldName].ToString()); } } } else { sheet.ShiftRows(rdrowindex, sheet.LastRowNum, 1, true, false); IRow newrow = sheet.CreateRow(rdrowindex); foreach (var col in s.Columns) { if (col.ColumnType != CellType.FORMULA) { if (!data.Columns.Contains(col.FieldName)) { break; } var newcell = newrow.CreateCell(col.ColumnIndex); newcell.CellStyle = row.GetCell(col.ColumnIndex).CellStyle; SetCellValue(newcell, data.Columns[col.FieldName], dr[col.FieldName].ToString()); } else { var newcell = newrow.CreateCell(col.ColumnIndex); newcell.CellStyle = row.GetCell(col.ColumnIndex).CellStyle; newcell.SetCellFormula(col.FieldName.Replace("{0}", (rdrowindex + 1).ToString())); } } } if (repeatFrow != null) { sheet.ShiftRows(rdrowindex, sheet.LastRowNum, 1, true, false); NPOIHelper.CopyRow(sheet, sheet, splitterRowIdx, rdrowindex); rdrowindex++; } } for (int zz = 0; zz < s.Columns.Count; zz++) { sheet.AutoSizeColumn(s.Columns[zz].ColumnIndex); } sheet.ForceFormulaRecalculation = true; } } }
/// <summary> /// output the processData to excel /// excel is genrate accorint to input templatefile /// </summary> /// <param name="templatefile"></param> /// <param name="outputpath"></param> public void generateExcel(string templatefile, string outputpath) { const int templatepage = 3; const int formatline = 11; const int datastartline = 11; const int notestartline = 12; const int existdataline = notestartline - datastartline; string outputFullName = Path.Combine(outputpath, Path.GetFileName(templatefile)); if (false == File.Exists(templatefile)) { TraceMethod.Record(TraceMethod.TraceKind.ERROR, $"CI-LEU report templelate file is missing {templatefile}, please check!"); return; } using (FileStream sw = new FileStream(templatefile, FileMode.Open, FileAccess.ReadWrite)) { workBook = createworkbook(sw); if (null == workBook) { return; } getstyles(workBook, templatepage, formatline); int sheetidx = 1; foreach (var page in processData) { ISheet sheet = workBook.CloneSheet(templatepage); workBook.SetSheetName(templatepage + sheetidx, $"{page.Key}联锁区"); ++sheetidx; int recordnum = processData[page.Key].Count(); sheet.ShiftRows(notestartline, sheet.LastRowNum, recordnum - existdataline, true, true); for (int wor = 0; wor < recordnum; ++wor) { IRow row = sheet.CreateRow(datastartline + wor); if (null == processData[page.Key][wor]) { //empty row need do nothing } else if (defaultmsgcols.Count() == processData[page.Key][wor].Count) { setDefaultMsgRow(row, processData[page.Key][wor]); } else if (cols.Count() == processData[page.Key][wor].Count) { setRow(row, processData[page.Key][wor], cols); } else { TraceMethod.Record(TraceMethod.TraceKind.ERROR, $"get invalid data reocrd cols count {processData[page.Key][wor].Count}\n" + $"{processData[page.Key][wor].ToString()}"); } } //set MergedRegion, but the existed line should not set again or excel will raise error while open for (int rowidx = 0; rowidx < recordnum - existdataline; rowidx++) { for (int mergedidx = 0; mergedidx < mergedcolinfo.Count() / 2; ++mergedidx) { CellRangeAddress region = new CellRangeAddress( datastartline + existdataline + rowidx, datastartline + existdataline + rowidx, mergedcolinfo[mergedidx * 2], mergedcolinfo[mergedidx * 2 + 1]); sheet.AddMergedRegion(region); } } } workBook.RemoveSheetAt(templatepage); FileStream sw1 = new FileStream(outputFullName, FileMode.Create, FileAccess.ReadWrite); workBook.Write(sw1); sw.Close(); sw1.Close(); } }