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();
}
}