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();
}
/// <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 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 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 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 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 trackAndUntrackAllColumns()
{
Assume.That(tracker.TrackedColumns.Count == 0);
tracker.TrackAllColumns();
Assert.IsTrue(tracker.TrackedColumns.Count == 0);
IRow row = sheet.CreateRow(0);
foreach (int column in columns)
{
row.CreateCell(column);
}
// implicitly track the columns
tracker.UpdateColumnWidths(row);
Assert.AreEqual(columns, tracker.TrackedColumns);
tracker.UntrackAllColumns();
Assert.IsTrue(tracker.TrackedColumns.Count == 0);
}
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 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 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 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);
}
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;
}
}
