public void AutoSizeColumn_trackAllColumns()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackAllColumnsForAutoSizing();
sheet.AutoSizeColumn(0, useMergedCells);
sheet.UntrackAllColumnsForAutoSizing();
try
{
sheet.AutoSizeColumn(0, useMergedCells);
Assert.Fail("Should not be able to auto-size an implicitly untracked column");
}
catch (InvalidOperationException)
{
// expected
}
}
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 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 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 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);
}