Ejemplo n.º 1
0
        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();
        }
Ejemplo n.º 2
0
        /// <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();
        }
Ejemplo n.º 3
0
        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();
        }
Ejemplo n.º 4
0
        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();
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        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();
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
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();
        }
Ejemplo n.º 9
0
        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();
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
0
        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;
            }
        }