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