public void TestZipBombNotTriggeredOnUselessContent() { SXSSFWorkbook swb = new SXSSFWorkbook(null, 1, true, true); SXSSFSheet s = swb.CreateSheet() as SXSSFSheet; char[] useless = new char[32767]; Arrays.Fill(useless, ' '); for (int row = 0; row < 1; row++) { IRow r = s.CreateRow(row); for (int col = 0; col < 10; col++) { char[] prefix = HexDump.ToHex(row * 1000 + col).ToCharArray(); Arrays.Fill(useless, 0, 10, ' '); Array.Copy(prefix, 0, useless, 0, prefix.Length); String ul = new String(useless); r.CreateCell(col, CellType.String).SetCellValue(ul); ul = null; } } ByteArrayOutputStream bos = new ByteArrayOutputStream(); swb.Write(bos); swb.Dispose(); swb.Close(); }
public void TestEvaluateRefOutsideWindowFails() { SXSSFWorkbook wb = new SXSSFWorkbook(5); SXSSFSheet s = wb.CreateSheet() as SXSSFSheet; s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2"); Assert.AreEqual(false, s.AllRowsFlushed); Assert.AreEqual(-1, s.LastFlushedRowNumber); for (int i = 1; i <= 19; i++) { s.CreateRow(i); } ICell c = s.CreateRow(20).CreateCell(0); c.CellFormula = (/*setter*/ "A1+100"); Assert.AreEqual(false, s.AllRowsFlushed); Assert.AreEqual(15, s.LastFlushedRowNumber); IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator(); try { eval.EvaluateFormulaCell(c); Assert.Fail("Evaluate shouldn't work, as reference outside the window"); } catch (RowFlushedException) { // Expected } wb.Close(); }
public void Test_EmptySheet_NoException() { workbook = new SXSSFWorkbook(); sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackAllColumnsForAutoSizing(); for (int i = 0; i < 10; i++) { sheet.AutoSizeColumn(i, useMergedCells); } }
public void TestEvaluateAllFails() { SXSSFWorkbook wb = new SXSSFWorkbook(5); SXSSFSheet s = wb.CreateSheet() as SXSSFSheet; IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator(); s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2"); s.CreateRow(1).CreateCell(0).CellFormula = (/*setter*/ "A21"); for (int i = 2; i < 19; i++) { s.CreateRow(i); } // Cells outside window will fail, whether referenced or not s.CreateRow(19).CreateCell(0).CellFormula = (/*setter*/ "A1+A2"); s.CreateRow(20).CreateCell(0).CellFormula = (/*setter*/ "A1+A11+100"); try { eval.EvaluateAll(); Assert.Fail("Evaluate All shouldn't work, as some cells outside the window"); } catch (RowFlushedException) { // Expected } // Inactive sheets will fail XSSFWorkbook xwb = new XSSFWorkbook(); xwb.CreateSheet("Open"); xwb.CreateSheet("Closed"); wb.Close(); wb = new SXSSFWorkbook(xwb, 5); s = wb.GetSheet("Closed") as SXSSFSheet; s.FlushRows(); s = wb.GetSheet("Open") as SXSSFSheet; s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2"); eval = wb.GetCreationHelper().CreateFormulaEvaluator(); try { eval.EvaluateAll(); Assert.Fail("Evaluate All shouldn't work, as sheets flushed"); } catch (SheetsFlushedException) { } wb.Close(); }
public void AutoSizeColumn_isColumnTrackedForAutoSizing() { workbook = new SXSSFWorkbook(); sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackColumnsForAutoSizing(columns); foreach (int column in columns) { Assert.IsTrue(sheet.IsColumnTrackedForAutoSizing(column)); Assume.That(!columns.Contains(column + 10)); Assert.IsFalse(sheet.IsColumnTrackedForAutoSizing(column + 10)); } }
/// <summary> /// datatable导出 /// </summary> /// <param name="dataTable"></param> /// <param name="col"></param> public static void ToExcelByNpoi(DataTable dataTable, string col) { XSSFWorkbook work = new XSSFWorkbook(); IWorkbook workbook = new SXSSFWorkbook(work, 1000); //创建一个Sheet1 SXSSFSheet sheet1 = (SXSSFSheet)workbook.CreateSheet("Sheet1"); DataTable dt = dataTable; IRow rowhead = sheet1.CreateRow(0); string str = col; string[] strings = str.Split(','); if (!strings.Any(x => string.IsNullOrEmpty(x))) { for (int i = 0; i < strings.Length; i++) { rowhead.CreateCell(i, CellType.String).SetCellValue(strings[i]); } } else { for (int i = 0; i < dt.Columns.Count; i++) { rowhead.CreateCell(i, CellType.String).SetCellValue(dt.Columns[i].ColumnName); } } int index = 1; for (int i = 0; i < dt.Rows.Count; i++) { DataRow dtRow = dt.Rows[i]; IRow excelRow = sheet1.CreateRow(index++); for (int j = 0; j < dtRow.ItemArray.Length; j++) { excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString()); } } MemoryStream ms = new MemoryStream(); workbook.Write(ms); string title = "报表"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xlsx", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); work = null; ms.Close(); ms.Dispose(); }
public void Test_WindowSizeEqualsOne_lastRowIsWidest() { workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackAllColumnsForAutoSizing(); ICell cellRow0 = CreateRowWithCellValues(sheet, 0, SHORT_CELL_VALUE); assumeRequiredFontsAreInstalled(workbook, cellRow0); CreateRowWithCellValues(sheet, 1, LONG_CELL_VALUE); sheet.AutoSizeColumn(0, useMergedCells); assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH); }
public void Test_WindowSizeDefault_AllRowsFitIntoWindowSize() { workbook = new SXSSFWorkbook(); sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackAllColumnsForAutoSizing(); ICell cellRow0 = CreateRowWithCellValues(sheet, 0, LONG_CELL_VALUE); assumeRequiredFontsAreInstalled(workbook, cellRow0); CreateRowWithCellValues(sheet, 1, SHORT_CELL_VALUE); sheet.AutoSizeColumn(0, useMergedCells); assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH); }
public void ChangeRowNum() { SXSSFWorkbook wb = new SXSSFWorkbook(3); SXSSFSheet sheet = wb.CreateSheet() as SXSSFSheet; SXSSFRow row0 = sheet.CreateRow(0) as SXSSFRow; SXSSFRow row1 = sheet.CreateRow(1) as SXSSFRow; sheet.ChangeRowNum(row0, 2); Assert.AreEqual(1, row1.RowNum, "Row 1 knows its row number"); Assert.AreEqual(2, row0.RowNum, "Row 2 knows its row number"); Assert.AreEqual(1, sheet.GetRowNum(row1), "Sheet knows Row 1's row number"); Assert.AreEqual(2, sheet.GetRowNum(row0), "Sheet knows Row 2's row number"); Assert.AreEqual(row1, sheet.GetEnumerator().Current, "Sheet row iteratation order should be ascending"); wb.Close(); }
public void TestEvaluateAllInWindow() { SXSSFWorkbook wb = new SXSSFWorkbook(5); SXSSFSheet s = wb.CreateSheet() as SXSSFSheet; s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2"); s.CreateRow(1).CreateCell(1).CellFormula = (/*setter*/ "A1+10"); s.CreateRow(2).CreateCell(2).CellFormula = (/*setter*/ "B2+100"); IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator(); eval.EvaluateAll(); Assert.AreEqual(3, (int)s.GetRow(0).GetCell(0).NumericCellValue); Assert.AreEqual(13, (int)s.GetRow(1).GetCell(1).NumericCellValue); Assert.AreEqual(113, (int)s.GetRow(2).GetCell(2).NumericCellValue); wb.Close(); }
public void TestEvaluateSimple() { SXSSFWorkbook wb = new SXSSFWorkbook(5); SXSSFSheet s = wb.CreateSheet() as SXSSFSheet; IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator(); SXSSFCell c = s.CreateRow(0).CreateCell(0) as SXSSFCell; c.CellFormula = (/*setter*/ "1+2"); Assert.AreEqual(0, (int)c.NumericCellValue); eval.EvaluateFormulaCell(c); Assert.AreEqual(3, (int)c.NumericCellValue); c = s.CreateRow(1).CreateCell(0) as SXSSFCell; c.CellFormula = (/*setter*/ "CONCATENATE(\"hello\",\" \",\"world\")"); eval.EvaluateFormulaCell(c); Assert.AreEqual("hello world", c.StringCellValue); }
public void AutoSizeColumn_trackAllColumns_explicitUntrackColumn() { workbook = new SXSSFWorkbook(); sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackColumnsForAutoSizing(columns); sheet.TrackAllColumnsForAutoSizing(); sheet.UntrackColumnForAutoSizing(0); try { sheet.AutoSizeColumn(0, useMergedCells); Assert.Fail("Should not be able to auto-size an explicitly untracked column"); } catch (InvalidOperationException) { // expected } }
public void TestEvaluateRefInsideWindow() { SXSSFWorkbook wb = new SXSSFWorkbook(5); SXSSFSheet s = wb.CreateSheet() as SXSSFSheet; IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator(); SXSSFCell c = s.CreateRow(0).CreateCell(0) as SXSSFCell; c.SetCellValue(1.5); c = s.CreateRow(1).CreateCell(0) as SXSSFCell; c.CellFormula = (/*setter*/ "A1*2"); Assert.AreEqual(0, (int)c.NumericCellValue); eval.EvaluateFormulaCell(c); Assert.AreEqual(3, (int)c.NumericCellValue); wb.Close(); }
public void AutoSizeColumn_untrackColumnForAutoSizing() { workbook = new SXSSFWorkbook(); sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackColumnsForAutoSizing(columns); sheet.UntrackColumnForAutoSizing(columns.First()); Assume.That(sheet.TrackedColumnsForAutoSizing.Contains(columns.Last())); sheet.AutoSizeColumn(columns.Last(), useMergedCells); try { Assume.That(!sheet.TrackedColumnsForAutoSizing.Contains(columns.First())); sheet.AutoSizeColumn(columns.First(), useMergedCells); Assert.Fail("Should not be able to auto-size an untracked column"); } catch (InvalidOperationException) { // expected } }
public void Test_WindowSizeEqualsOne_flushedRowHasMergedCell() { workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackAllColumnsForAutoSizing(); ICell a1 = CreateRowWithCellValues(sheet, 0, LONG_CELL_VALUE); assumeRequiredFontsAreInstalled(workbook, a1); sheet.AddMergedRegion(CellRangeAddress.ValueOf("A1:B1")); CreateRowWithCellValues(sheet, 1, SHORT_CELL_VALUE, SHORT_CELL_VALUE); /** * A B * 1 LONGMERGED * 2 SHORT SHORT */ sheet.AutoSizeColumn(0, useMergedCells); sheet.AutoSizeColumn(1, useMergedCells); if (useMergedCells) { // Excel and LibreOffice behavior: ignore merged cells for auto-sizing. // POI behavior: evenly distribute the column width among the merged columns. // each column must be auto-sized in order for the column widths // to add up to the best fit width. int colspan = 2; int expectedWidth = (10000 + 1000) / colspan; //average of 1_000 and 10_000 int minExpectedWidth = expectedWidth / 2; int maxExpectedWidth = expectedWidth * 3 / 2; assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), minExpectedWidth, maxExpectedWidth); //short } else { assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH); //long } assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(1), 0, COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG); //short }
public void AutoSizeColumn_trackColumnForAutoSizing() { workbook = new SXSSFWorkbook(); sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackColumnForAutoSizing(0); SortedSet <int> expected = new SortedSet <int>(); expected.Add(0); Assert.AreEqual(expected, sheet.TrackedColumnsForAutoSizing); sheet.AutoSizeColumn(0, useMergedCells); try { sheet.AutoSizeColumn(1, useMergedCells); Assert.Fail("Should not be able to auto-size an untracked column"); } catch (InvalidOperationException) { // expected } }
public void AutoSizeColumn_trackColumnsForAutoSizing() { workbook = new SXSSFWorkbook(); sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackColumnsForAutoSizing(columns); SortedSet <int> sorted = new SortedSet <int>(columns); Assert.AreEqual(sorted, sheet.TrackedColumnsForAutoSizing); sheet.AutoSizeColumn(sorted.First(), useMergedCells); try { //assumeFalse(columns.Contains(5)); Assume.That(!columns.Contains(5)); sheet.AutoSizeColumn(5, useMergedCells); Assert.Fail("Should not be able to auto-size an untracked column"); } catch (InvalidOperationException) { // expected } }
public void SheetdataWriter() { SXSSFWorkbook wb = new SXSSFWorkbook(); SXSSFSheet sh = wb.CreateSheet() as SXSSFSheet; SheetDataWriter wr = sh.SheetDataWriter; Assert.IsTrue(wr.GetType() == typeof(SheetDataWriter)); FileInfo tmp = wr.TempFileInfo; Assert.IsTrue(tmp.Name.StartsWith("poi-sxssf-sheet")); Assert.IsTrue(tmp.Name.EndsWith(".xml")); Assert.IsTrue(wb.Dispose()); wb.Close(); wb = new SXSSFWorkbook(); wb.CompressTempFiles = (/*setter*/ true); sh = wb.CreateSheet() as SXSSFSheet; wr = sh.SheetDataWriter; Assert.IsTrue(wr.GetType() == typeof(GZIPSheetDataWriter)); tmp = wr.TempFileInfo; Assert.IsTrue(tmp.Name.StartsWith("poi-sxssf-sheet-xml")); Assert.IsTrue(tmp.Name.EndsWith(".gz")); Assert.IsTrue(wb.Dispose()); wb.Close(); //Test escaping of Unicode control characters wb = new SXSSFWorkbook(); wb.CreateSheet("S1").CreateRow(0).CreateCell(0).SetCellValue("value\u0019"); XSSFWorkbook xssfWorkbook = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb) as XSSFWorkbook; ICell cell = xssfWorkbook.GetSheet("S1").GetRow(0).GetCell(0); Assert.AreEqual("value?", cell.StringCellValue); Assert.IsTrue(wb.Dispose()); wb.Close(); xssfWorkbook.Close(); }
protected static void assertWorkbookDispose(SXSSFWorkbook wb) { int rowNum = 1000; int sheetNum = 5; for (int i = 0; i < sheetNum; i++) { ISheet sh = wb.CreateSheet("sheet" + i); for (int j = 0; j < rowNum; j++) { IRow row = sh.CreateRow(j); ICell cell1 = row.CreateCell(0); cell1.SetCellValue(new CellReference(cell1).FormatAsString()); ICell cell2 = row.CreateCell(1); cell2.SetCellValue(i); ICell cell3 = row.CreateCell(2); cell3.SetCellValue(j); } } foreach (ISheet sheet in wb) { SXSSFSheet sxSheet = (SXSSFSheet)sheet; Assert.IsTrue(sxSheet.SheetDataWriter.TempFileInfo.Exists); } Assert.IsTrue(wb.Dispose()); foreach (ISheet sheet in wb) { SXSSFSheet sxSheet = (SXSSFSheet)sheet; Assert.IsFalse(sxSheet.SheetDataWriter.TempFileInfo.Exists); } }
void SetCell(IWorkbook workbook, SXSSFSheet sheet, int r, int tor, int c, int toc, dynamic value, short fontSize = 11, bool isBorder = false, BorderStyle borderStyle = BorderStyle.Medium, bool isCenter = false, short?rowHeight = null, short?color = null) { var rang = new CellRangeAddress(r, tor, c, toc); sheet.AddMergedRegion(rang); var fff = sheet.GetType().GetField("_rows").GetValue(sheet); var row = (fff as SortedDictionary <int, SXSSFRow>).ContainsKey(r) ? sheet.GetRow(r) : sheet.CreateRow(r);// sheet.GetRow(r) == null ? sheet.CreateRow(r) : sheet.GetRow(r); if (rowHeight.HasValue) { row.Height = rowHeight.Value; } var cell = row.CreateCell(c); if (value != null) { cell.SetCellValue(value); } var style = workbook.CreateCellStyle(); //设置颜色 if (color.HasValue) { style.FillForegroundColor = color.Value; style.FillPattern = FillPattern.SolidForeground; } //设置字体 var font = workbook.CreateFont(); font.FontHeightInPoints = fontSize; style.SetFont(font); //设置边框 //if (isBorder) //{ // style.BorderLeft = borderStyle; // style.BorderRight = borderStyle; // style.BorderTop = borderStyle; // style.BorderBottom = borderStyle; // for (int i = rang.FirstRow; i <= rang.LastRow; i++) // { // var borderRow = CellUtil.GetRow(i, sheet); // for (int j = rang.FirstColumn; j <= rang.LastColumn; j++) // { // var singleCell = CellUtil.GetCell(borderRow, (short)j); // singleCell.CellStyle = style; // } // } //} //else //{ // cell.CellStyle = style; //} cell.CellStyle = style; //设置内容居中 if (isCenter) { style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Center; } }
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements) { Document doc = commandData.Application.ActiveUIDocument.Document; // Check if the open document is a Family document try { var check = doc.FamilyManager; TaskDialog.Show("Warning", "Family document opened, please open a project"); return(Result.Cancelled); } catch { // TODO: refactor Family document check } // Retrieve current date string currentDate = DateTime.Today.ToString("dd/MM/yyyy"); string[] columnNames = { "Priority", "Warning", "Element Ids", "Date Detected", "Date Solved", "Fixed by" }; string warningJSONPath = @"C:\ProgramData\Autodesk\Revit\Addins\BIMicon\WarningsReport\RevitWarningsClassified.json"; string warningsJsonString = Helpers.Helpers.WriteSafeReadAllLines(warningJSONPath); var warningsJObject = JObject.Parse(warningsJsonString); string critical = string.Join("", warningsJObject.Value <JArray>("Critical").ToObject <string[]>()); string high = string.Join("", warningsJObject.Value <JArray>("High").ToObject <string[]>()); string medium = string.Join("", warningsJObject.Value <JArray>("Medium").ToObject <string[]>()); string low = string.Join("", warningsJObject.Value <JArray>("Low").ToObject <string[]>()); IList <FailureMessage> docWarnings = doc.GetWarnings(); // Check if there is any warning in the document if (docWarnings.Count == 0) { TaskDialog.Show("Warning", "This project doesn't contain any warnings. Congratulations!"); return(Result.Succeeded); } // Store data to transfer to database List <string[]> dataTransfer = new List <string[]>(); foreach (FailureMessage failMessage in docWarnings) { string failDescription = failMessage.GetDescriptionText(); ICollection <ElementId> failWarningElementIds = failMessage.GetFailingElements(); string failElementIds = string.Join(", ", failWarningElementIds); string priorityCat = ""; if (critical.Contains(failDescription)) { priorityCat = "Critical"; } else if (high.Contains(failDescription)) { priorityCat = "High"; } else if (low.Contains(failDescription)) { priorityCat = "Low"; } else { priorityCat = "Medium"; } dataTransfer.Add(new string[] { priorityCat, failDescription, failElementIds, currentDate }); } // Path to output data string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); string excelPath = desktopPath + @"\Warnings Report.xlsx"; // Create excel file SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet excelSheet = (SXSSFSheet)workbook.CreateSheet("Sheet1"); excelSheet.SetRandomAccessWindowSize(100); //Create a header row IRow row = excelSheet.CreateRow(0); // Style for header var titleHeader = workbook.CreateFont(); titleHeader.FontHeightInPoints = 12; titleHeader.IsBold = true; ICellStyle boldStyle = workbook.CreateCellStyle(); boldStyle.SetFont(titleHeader); // Write to excel using (var fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write)) { // Write header for (int i = 0; i < columnNames.Count(); i++) { var cell = row.CreateCell(i); cell.SetCellValue(columnNames[i]); cell.CellStyle = boldStyle; } // Write content for (int i = 0; i < dataTransfer.Count; i++) { int numberElements = dataTransfer[i].Count(); row = excelSheet.CreateRow(i + 1); for (int j = 0; j < numberElements; j++) { row.CreateCell(j).SetCellValue(dataTransfer[i][j]); } } // Size columns excelSheet.TrackAllColumnsForAutoSizing(); for (int i = 0; i < columnNames.Count(); i++) { if (i == 1) { excelSheet.SetColumnWidth(i, 3800); } // Autosize needs to be after column has some data excelSheet.AutoSizeColumn(i); } excelSheet.UntrackAllColumnsForAutoSizing(); // Write to file workbook.Write(fs); TaskDialog.Show("Success", "Warnings report created in: " + excelPath); } return(Result.Succeeded); }
protected override void TrackColumnsForAutoSizingIfSXSSF(ISheet sheet) { SXSSFSheet sxSheet = (SXSSFSheet)sheet; sxSheet.TrackAllColumnsForAutoSizing(); }
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements) { Document doc = commandData.Application.ActiveUIDocument.Document; // Variables to store user input List <int> listIds; string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); // Variables to store log var schedSuccess = new List <string>(); var schedFail = new List <string>(); // Prompt window to collect user input using (BrowserCheckboxes customWindow = new BrowserCheckboxes(commandData)) { customWindow.ShowDialog(); listIds = customWindow.listIds; } // Check there are schedules selected if (listIds != null) { using (BrowserWindow browserWindow = new BrowserWindow()) { browserWindow.ShowDialog(); // Variables string fullPath = browserWindow.selectedPath; // CHeck that path is not empty and path is a folder if (fullPath == null || !Directory.Exists(fullPath)) { TaskDialog.Show("Warning", "No folder has been selected"); return(Result.Cancelled); } else { // Loop through each selected schedule foreach (int id in listIds) { // Extract data from schedule ViewSchedule sched = doc.GetElement(new ElementId(id)) as ViewSchedule; TableData tD = sched.GetTableData(); TableSectionData sectionData = tD.GetSectionData(SectionType.Body); int numbRows = sectionData.NumberOfRows; int numbCols = sectionData.NumberOfColumns; // Name of the file string excelPath = fullPath + @"\" + sched.Name + ".xlsx"; // Create excel file SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet excelSheet = (SXSSFSheet)workbook.CreateSheet(sched.Name); excelSheet.SetRandomAccessWindowSize(100); //Create a header row IRow row = excelSheet.CreateRow(0); // Define format for cells var fontStyle = workbook.CreateFont(); fontStyle.IsBold = true; fontStyle.FontHeightInPoints = 12; var titleStyle = workbook.CreateCellStyle(); titleStyle.SetFont(fontStyle); // Write to excel using (var fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write)) { // Write content for (int i = 0; i < numbRows; i++) { row = excelSheet.CreateRow(i); for (int j = 0; j < numbCols; j++) { string content = sched.GetCellText(SectionType.Body, i, j); var cell = row.CreateCell(j); cell.SetCellValue(content); if (i == 0) { cell.CellStyle = titleStyle; } } } // Size columns excelSheet.TrackAllColumnsForAutoSizing(); for (int i = 0; i < numbCols; i++) { excelSheet.AutoSizeColumn(i); } excelSheet.UntrackAllColumnsForAutoSizing(); // Write to file try { workbook.Write(fs); // Log success export schedule name schedSuccess.Add(sched.Name); } catch { schedFail.Add(sched.Name); } } } TaskDialog.Show("Success", "The following schedules have been exported: " + string.Join("\n", schedSuccess.ToArray())); return(Result.Succeeded); } } } return(Result.Cancelled); }
public void SetUpSheetAndWorkbook() { workbook = new SXSSFWorkbook(); sheet = workbook.CreateSheet() as SXSSFSheet; tracker = new AutoSizeColumnTracker(sheet); }
void SetCell(IWorkbook workbook, SXSSFSheet sheet, int r, int tor, int c, int toc, dynamic value, short fontSize = 11, bool isBorder = false, BorderStyle borderStyle = BorderStyle.Medium, bool isCenter = false, short?rowHeight = null, short?color = null) { var rang = new CellRangeAddress(r, tor, c, toc); sheet.AddMergedRegion(rang); var row = sheet._rows[r]; if (rowHeight.HasValue) { row.Height = rowHeight.Value; } var cell = row.Cells[c]; if (value != null) { cell.SetCellValue(value); } //设置边框 if (isBorder) { // if (r == tor && c == toc) { //var brow = sheet._rows[r]; //var sigleCell = brow.Cells[c]; //var sigleStyle = workbook.CreateCellStyle(); //sigleStyle.BorderLeft = borderStyle; //sigleCell.CellStyle = sigleStyle; } // else { Parallel.For(r, tor + 1, firstR => { var brow = sheet._rows[firstR]; var leftCell = brow.Cells[c]; var leftStyle = workbook.CreateCellStyle(); leftStyle.BorderLeft = borderStyle; leftCell.CellStyle = leftStyle; var rightCell = brow.Cells[toc]; var rightStyle = workbook.CreateCellStyle(); rightStyle.BorderRight = borderStyle; rightCell.CellStyle = rightStyle; }); //for (var firstR = r; firstR <= tor; firstR++) //{ // var brow = sheet._rows[firstR]; // var leftCell = brow.Cells[c]; // var leftStyle = workbook.CreateCellStyle(); // leftStyle.BorderLeft = borderStyle; // leftCell.CellStyle = leftStyle; // var rightCell = brow.Cells[toc]; // var rightStyle = workbook.CreateCellStyle(); // rightStyle.BorderRight = borderStyle; // rightCell.CellStyle = rightStyle; //} Parallel.For(c, toc + 1, firstC => { var topRow = sheet._rows[r]; var topCell = topRow.Cells[firstC]; var topStyle = workbook.CreateCellStyle(); topStyle.BorderTop = borderStyle; if (firstC == c) { topStyle.BorderLeft = borderStyle; } if (firstC == toc) { topStyle.BorderRight = borderStyle; } topCell.CellStyle = topStyle; var bottomRow = sheet._rows[tor]; var bottomCell = bottomRow.Cells[firstC]; var bottomStyle = workbook.CreateCellStyle(); bottomStyle.BorderBottom = borderStyle; if (firstC == c) { bottomStyle.BorderLeft = borderStyle; } if (firstC == toc) { bottomStyle.BorderRight = borderStyle; } bottomCell.CellStyle = bottomStyle; }); // for (var firstC = c; firstC <= toc; firstC++) //{ // var topRow = sheet._rows[r]; // var topCell = topRow.Cells[firstC]; // var topStyle = workbook.CreateCellStyle(); // topStyle.BorderTop = borderStyle; // if (firstC == c) // { // topStyle.BorderLeft = borderStyle; // } // if (firstC == toc) // { // topStyle.BorderRight = borderStyle; // } // topCell.CellStyle = topStyle; // var bottomRow = sheet._rows[tor]; // var bottomCell = bottomRow.Cells[firstC]; // var bottomStyle = workbook.CreateCellStyle(); // bottomStyle.BorderBottom = borderStyle; // if (firstC == c) // { // bottomStyle.BorderLeft = borderStyle; // } // if (firstC == toc) // { // bottomStyle.BorderRight = borderStyle; // } // bottomCell.CellStyle = bottomStyle; //} } } var style = isBorder ? cell.CellStyle : workbook.CreateCellStyle(); //设置颜色 if (color.HasValue) { style.FillForegroundColor = color.Value; style.FillPattern = FillPattern.SolidForeground; } //设置字体 var font = workbook.CreateFont(); font.FontHeightInPoints = fontSize; style.SetFont(font); //设置内容居中 if (isCenter) { style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Center; } cell.CellStyle = style; }