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