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