コード例 #1
0
        //UTILS================================================================================================

        private XSSFWorkbook OpenXLSXFile(string fileName)
        {
retry:
            try
            {
                string       newFileName = Path.GetFullPath(Path.Combine(Directory.GetCurrentDirectory(), filePath + fileName));
                FileStream   file        = new FileStream(newFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                XSSFWorkbook workbook    = new XSSFWorkbook(file);
                file.Close();

                //WORKAROUND: Replace values for unsupported formulas @"Utils" worksheet with the exact formula stored as string
                TempUtils(workbook.GetSheet("Utils"));

                workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
                return(workbook);
            }
            catch (IOException)
            {
                MessageBox.Show(new Form()
                {
                    TopMost = true
                }, "Please close the test case file: " + fileName + " and click on OK.", "File Access Conflict", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                goto retry;
            }
        }
コード例 #2
0
        /// <summary>
        /// Создаем файл с путешественниками
        /// </summary>
        /// <param name="codeMO"></param>
        /// <param name="listTraveller"></param>
        public static async Task CreateFileWithTravellerAsync(string codeMO, List <Traveller> listTraveller)
        {
            string nameMO = await GetNameMOAsync(codeMO);

            PropertyInfo[] properties = new Traveller().ReturnType();
            //выкидываем невалидные для имени файла символы
            nameMO = string.Join("_", nameMO.Split(Path.GetInvalidFileNameChars()));
            string path = $@"C:\Working\{nameMO}.xlsx";

            using (FileStream stream = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                IWorkbook       wb       = new XSSFWorkbook();
                ISheet          sheet    = wb.CreateSheet("Sheet1");
                ICreationHelper cH       = wb.GetCreationHelper();
                IRow            hederRow = sheet.CreateRow(0);
                //заполняем первую строку с названиями полей
                for (int i = 0; i < properties.Length; i++)
                {
                    ICell cell = hederRow.CreateCell(i);
                    cell.CellStyle.FillBackgroundColor = 1;
                    cell.SetCellValue(properties[i].Name);
                }
                //заполняем строки с данными о прибывших
                for (int i = 0; i < listTraveller.Count; i++)
                {
                    IRow row = sheet.CreateRow(i);
                    for (int j = 0; j < properties.Length; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellValue(listTraveller[i].GetType().GetProperty(properties[j].Name).GetValue(listTraveller[i], null).ToString());
                    }
                }
                wb.Write(stream);
            }
        }
コード例 #3
0
        public void TestBug55843b()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet sheet  = wb.CreateSheet("test") as XSSFSheet;
                XSSFRow   row    = sheet.CreateRow(0) as XSSFRow;
                XSSFRow   row2   = sheet.CreateRow(1) as XSSFRow;
                XSSFCell  cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
                XSSFCell  cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
                cellB1.SetCellValue(10);
                XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;

                cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
                CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
                System.Console.WriteLine(Evaluate);
                Assert.AreEqual("2", Evaluate.FormatAsString());

                cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
                CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
                System.Console.WriteLine(EvaluateN);

                Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
                Assert.AreEqual("2", EvaluateN.FormatAsString());
            }
            finally
            {
                wb.Close();
            }
        }
コード例 #4
0
        public void TestSharedFormulas_EvaluateInCell()
        {
            XSSFWorkbook      wb        = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("49872.xlsx");
            IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
            ISheet            sheet     = wb.GetSheetAt(0);

            double result = 3.0;

            // B3 is a master shared formula, C3 and D3 don't have the formula written in their f element.
            // Instead, the attribute si for a particular cell is used to figure what the formula expression
            // should be based on the cell's relative location to the master formula, e.g.
            // B3:        <f t="shared" ref="B3:D3" si="0">B1+B2</f>
            // C3 and D3: <f t="shared" si="0"/>

            // Get B3 and Evaluate it in the cell
            ICell b3 = sheet.GetRow(2).GetCell(1);

            Assert.AreEqual(result, Evaluator.EvaluateInCell(b3).NumericCellValue);

            //at this point the master formula is gone, but we are still able to Evaluate dependent cells
            ICell c3 = sheet.GetRow(2).GetCell(2);

            Assert.AreEqual(result, Evaluator.EvaluateInCell(c3).NumericCellValue);

            ICell d3 = sheet.GetRow(2).GetCell(3);

            Assert.AreEqual(result, Evaluator.EvaluateInCell(d3).NumericCellValue);
        }
コード例 #5
0
        public void TestEvaluateColumnGreaterThan255()
        {
            XSSFWorkbook      wb        = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("50096.xlsx");
            IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            /**
             *  The first row simply Contains the numbers 1 - 300.
             *  The second row simply refers to the cell value above in the first row by a simple formula.
             */
            for (int i = 245; i < 265; i++)
            {
                ICell cell_noformula = wb.GetSheetAt(0).GetRow(0).GetCell(i);
                ICell cell_formula   = wb.GetSheetAt(0).GetRow(1).GetCell(i);

                CellReference ref_noformula = new CellReference(cell_noformula.RowIndex, cell_noformula.ColumnIndex);
                CellReference ref_formula   = new CellReference(cell_noformula.RowIndex, cell_noformula.ColumnIndex);
                String        fmla          = cell_formula.CellFormula;
                // assure that the formula refers to the cell above.
                // the check below is 'deep' and involves conversion of the shared formula:
                // in the sample file a shared formula in GN1 is spanned in the range GN2:IY2,
                Assert.AreEqual(ref_noformula.FormatAsString(), fmla);

                CellValue cv_noformula = Evaluator.Evaluate(cell_noformula);
                CellValue cv_formula   = Evaluator.Evaluate(cell_formula);
                Assert.AreEqual(cv_noformula.NumberValue, cv_formula.NumberValue, "Wrong Evaluation result in " + ref_formula.FormatAsString());
            }
        }
コード例 #6
0
 public static MemoryStream CreateExcelFromDataTable(DataTable dt)
 {
     using (var stream = new MemoryStream())
     {
         IWorkbook       wb     = new XSSFWorkbook();
         ISheet          sheet  = wb.CreateSheet("Sheet1");
         ICreationHelper cH     = wb.GetCreationHelper();
         IRow            header = sheet.CreateRow(0);
         for (int h = 0; h < dt.Columns.Count; h++)
         {
             ICell cell = header.CreateCell(h);
             cell.SetCellValue(cH.CreateRichTextString(dt.Columns[h].ColumnName));
         }
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             IRow row = sheet.CreateRow(i + 1);
             for (int j = 0; j < dt.Columns.Count; j++)
             {
                 ICell cell = row.CreateCell(j);
                 cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i].ItemArray[j].ToString()));
             }
         }
         for (int i = 0; i < dt.Columns.Count; i++)
         {
             sheet.AutoSizeColumn(i);
         }
         wb.Write(stream);
         return(stream);
     }
 }
コード例 #7
0
        static void Main(string[] args)
        {
            IWorkbook wb     = new XSSFWorkbook();
            ISheet    sheet1 = wb.CreateSheet("First Sheet");

            //add picture data to this workbook.
            byte[] bytes      = File.ReadAllBytes("../../data/aspose.png");
            int    pictureIdx = wb.AddPicture(bytes, PictureType.PNG);

            ICreationHelper helper = wb.GetCreationHelper();

            // Create the drawing patriarch.  This is the top level container for all shapes.
            IDrawing drawing = sheet1.CreateDrawingPatriarch();

            // add a picture shape
            IClientAnchor anchor = helper.CreateClientAnchor();

            //set top-left corner of the picture,
            //subsequent call of Picture#resize() will operate relative to it
            anchor.Col1 = 3;
            anchor.Row1 = 2;
            IPicture pict = drawing.CreatePicture(anchor, pictureIdx);

            //auto-size picture relative to its top-left corner
            pict.Resize();

            FileStream sw = File.Create("../../data/image.xlsx");

            wb.Write(sw);
            sw.Close();
        }
コード例 #8
0
        void Serialize()
        {
            using (FileStream stream = new FileStream(@"Cust_Data.xlsx", FileMode.Create, FileAccess.Write))
            {
                IWorkbook       wb = new XSSFWorkbook();
                ICreationHelper cH = wb.GetCreationHelper();
                int             k  = 0;
                foreach (String subSchema in subSchemas)
                {
                    ISheet sheet = wb.CreateSheet(subSchema);

                    for (int i = 0; i < worksheets[k].Rows.Count; i++)
                    {
                        IRow row = sheet.CreateRow(i);

                        int j = 0;
                        foreach (Object cellVal in worksheets[k].Rows[i].ItemArray)
                        {
                            ICell cell = row.CreateCell(j);
                            cell.SetCellValue(cH.CreateRichTextString(worksheets[k].Rows[i].ItemArray[j].ToString()));
                            j++;
                        }
                    }
                    k++;
                }
                wb.Write(stream);
            }
        }
コード例 #9
0
        private void exportXLSX_Click(object sender, RoutedEventArgs e)
        {
            var newFile = @"newbook.core.xlsx";

            using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
            {
                // XSSFWorkbook : *.xlsx >= Excel2007
                // HSSFWorkbook : *.xls  < Excel2007
                IWorkbook workbook = new XSSFWorkbook();

                ISheet sheet1 = workbook.CreateSheet("Sheet Name");

                // 所有索引都从0开始

                // 合并单元格
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));

                var  rowIndex = 0;
                IRow row      = sheet1.CreateRow(rowIndex); //创建行
                row.Height = 30 * 80;
                row.CreateCell(0).SetCellValue("this is content");
                sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小
                rowIndex++;

                // 插入图片
                byte[]             data    = File.ReadAllBytes(@"image.jpg");
                int                picInd  = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG);
                XSSFCreationHelper helper  = workbook.GetCreationHelper() as XSSFCreationHelper;
                XSSFDrawing        drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
                XSSFClientAnchor   anchor  = helper.CreateClientAnchor() as XSSFClientAnchor;
                anchor.Col1 = 10;
                anchor.Row1 = 0;
                XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
                pict.Resize();

                // 新建sheet
                var sheet2 = workbook.CreateSheet("Sheet2");
                // 更改样式
                var style1 = workbook.CreateCellStyle();
                style1.FillForegroundColor = HSSFColor.Blue.Index2;
                style1.FillPattern         = FillPattern.SolidForeground;

                var style2 = workbook.CreateCellStyle();
                style2.FillForegroundColor = HSSFColor.Yellow.Index2;
                style2.FillPattern         = FillPattern.SolidForeground;

                var cell2 = sheet2.CreateRow(0).CreateCell(0);
                cell2.CellStyle = style1;
                cell2.SetCellValue(0);

                cell2           = sheet2.CreateRow(1).CreateCell(0);
                cell2.CellStyle = style2;
                cell2.SetCellValue(1);


                //保存
                workbook.Write(fs);
            }
            txtStatus.Text = "writing xlsx successful!";
        }
コード例 #10
0
ファイル: ExcelHelper.cs プロジェクト: 18316541141/myhelper
        /// <summary>
        /// 导出xls结尾的Excel数据,每个实体必须含有ExcelColNameAttr
        /// </summary>
        /// <param name="dataList"></param>
        /// <param name="outputStream"></param>
        public static void ListToExcelXlsx <T>(List <T>[] dataListArrays, Stream outputStream, string groupName = null)
        {
            XSSFWorkbook    xssfWorkbook   = new XSSFWorkbook();
            ICellStyle      dateCellStyle  = xssfWorkbook.CreateCellStyle();
            ICreationHelper creationHelper = xssfWorkbook.GetCreationHelper();

            dateCellStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat("yyyy-MM-dd hh:mm:ss");
            foreach (List <T> dataList in dataListArrays)
            {
                if (dataList == null || dataList.Count == 0)
                {
                    throw new Exception("数据列表为空,未能导出数据!");
                }
                List <ExcelColInfo> excelColInfoList = ColFilter(typeof(T), groupName);
                if (excelColInfoList.Count == 0)
                {
                    throw new Exception("字段列表为空,不能导出数据!");
                }
                XSSFSheet xssfSheet = (XSSFSheet)xssfWorkbook.CreateSheet(GetSheetName(typeof(T), groupName));
                XSSFRow   xssfRow   = (XSSFRow)xssfSheet.CreateRow(0);
                ExcelCol  excelCol;
                int       width;
                int[]     colWidths = new int[excelColInfoList.Count];
                foreach (ExcelColInfo excelColInfo in excelColInfoList)
                {
                    excelCol = excelColInfo.ExcelCol;
                    width    = SetCellValue(xssfRow.CreateCell(excelCol.ColIndex), excelCol.ColName);
                    if (width > colWidths[excelCol.ColIndex])
                    {
                        colWidths[excelCol.ColIndex] = width;
                    }
                }
                object temp;
                for (int i = 0, len = dataList.Count; i < len; i++)
                {
                    temp    = dataList[i];
                    xssfRow = (XSSFRow)xssfSheet.CreateRow(i + 1);
                    foreach (ExcelColInfo excelColInfo in excelColInfoList)
                    {
                        excelCol = excelColInfo.ExcelCol;
                        width    = SetCellValue(
                            (XSSFCell)xssfRow.CreateCell(excelCol.ColIndex),
                            excelColInfo.PropertyInfo.GetValue(temp),
                            dateCellStyle
                            );
                        if (width > colWidths[excelCol.ColIndex])
                        {
                            colWidths[excelCol.ColIndex] = width;
                        }
                    }
                }
                UpdateColWidth(xssfSheet, colWidths);
            }
            xssfWorkbook.Write(outputStream);
        }
コード例 #11
0
        private void CreateReportSheet(XSSFWorkbook workbook, string sectionName, IEnumerable <WorkItemDetail> workItems, WorkItemsReportViewModel prReport, bool includeTags = false)
        {
            var excelSheet     = workbook.CreateSheet(sectionName);
            var creationHelper = workbook.GetCreationHelper();

            var hlinkstyle = workbook.CreateCellStyle();
            var hlinkfont  = workbook.CreateFont();

            hlinkfont.Underline = FontUnderlineType.Single;
            hlinkfont.Color     = HSSFColor.Blue.Index;
            hlinkstyle.SetFont(hlinkfont);

            SetHeader(excelSheet, includeTags);
            int rowIdx = 1, cellIdx;

            foreach (var reportEntry in workItems)
            {
                cellIdx = 0;
                var row = excelSheet.CreateRow(rowIdx);

                var idCell = row.CreateCell(cellIdx++, CellType.String);
                idCell.SetCellValue(reportEntry.WorkItemId);
                var link = creationHelper.CreateHyperlink(HyperlinkType.Url);
                link.Address     = $"https://dynamicscrm.visualstudio.com/{reportEntry.WorkItemProject}/_workitems/edit/{reportEntry.WorkItemId}";
                idCell.Hyperlink = link;

                row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.WorkItemTitle);
                row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.WorkItemType);
                if (includeTags)
                {
                    row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.Tags);
                }

                row.CreateCell(cellIdx++, CellType.Numeric).SetCellValue(reportEntry.EstimatedToComplete);
                row.CreateCell(cellIdx, CellType.Numeric).SetCellValue(reportEntry.TimeSpent);
                rowIdx++;
            }

            var summaryRow = excelSheet.CreateRow(rowIdx);

            cellIdx = 0;
            summaryRow.CreateCell(cellIdx++, CellType.String).SetCellValue("Total: ");
            summaryRow.CreateCell(cellIdx++, CellType.String).SetCellValue($"{prReport.GetTotalBugs(workItems)} bugs / {prReport.GetTotalTasks(workItems)} tasks");
            summaryRow.CreateCell(cellIdx++, CellType.String);
            if (includeTags)
            {
                _ = summaryRow.CreateCell(cellIdx++, CellType.String);
            }

            summaryRow.CreateCell(cellIdx++, CellType.Numeric).SetCellValue(prReport.GetTotalEstimated(workItems));
            summaryRow.CreateCell(cellIdx, CellType.Numeric).SetCellValue(prReport.GetTotalTimeSpent(workItems));

            // AutosizeCells(excelSheet, summaryRow.Cells.Count);
        }
コード例 #12
0
        public void TestMultisheetFormulaEval()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet;
                XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet;
                XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet;

                // sheet1 A1
                XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(1.0);

                // sheet2 A1
                cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(1.0);

                // sheet2 B1
                cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell;
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(1.0);

                // sheet3 A1
                cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(1.0);

                // sheet1 A2 formulae
                cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Formula);
                cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1)");

                // sheet1 A3 formulae
                cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Formula);
                cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1:B1)");

                wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

                cell = sheet1.GetRow(1).GetCell(0) as XSSFCell;
                Assert.AreEqual(3.0, cell.NumericCellValue);

                cell = sheet1.GetRow(2).GetCell(0) as XSSFCell;
                Assert.AreEqual(4.0, cell.NumericCellValue);
            }
            finally
            {
                wb.Close();
            }
        }
コード例 #13
0
        private void gv_CellParsing(object sender, DataGridViewCellParsingEventArgs e)
        {
            var gvRow  = gv.Rows[e.RowIndex];
            var gvCell = gv.Rows[e.RowIndex].Cells[e.ColumnIndex];
            var value  = e.Value.ToString();
            var vitem  = gvRow.Tag as VItem;
            var vcell  = gvCell.Tag as ICell;
            var oldVal = GetCellValue(vcell);

            // if using a formula
            if (value.StartsWith("="))
            {
                vcell.SetCellType(CellType.Formula);
                vcell.SetCellFormula(value.Substring(1, value.Length - 1));
            }
            else
            {
                // only consider number/string here
                double v;
                if (double.TryParse(value, out v))
                {
                    vcell.SetCellType(CellType.Numeric);
                    vcell.SetCellValue(v);
                }
                else
                {
                    vcell.SetCellValue(value.ToString());
                }
            }

            var eval = workbook.GetCreationHelper().CreateFormulaEvaluator();

            for (int i = 0; i < vitem.VCells.Count; i++)
            {
                // re-evaluate the row
                eval.EvaluateFormulaCell(vitem.VCells[i]);
            }

            if (string.IsNullOrWhiteSpace(oldVal))
            {
                oldVal = "空值";
            }
            var node = treeView1.SelectedNode;

            while (node.Parent != null)
            {
                node = node.Parent;
            }
            listBox1.Items.Add(new MyListBoxItem(string.Format("{0}!{1}从【{2}】改变到【{3}】\r\n", vitem.SheetName, GetCellPosition(vcell), oldVal, GetCellValue(vcell)), node));
            gvCell.Style.BackColor = Color.SkyBlue;
            //RefreshRow(gvRow);
            ChangedCells.Add(vcell);
        }
コード例 #14
0
        public void EvaluateInCellReturnsSameDataType()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            wb.CreateSheet().CreateRow(0).CreateCell(0);
            XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
            XSSFCell             cell      = wb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell;
            XSSFCell             same      = evaluator.EvaluateInCell(cell) as XSSFCell;

            //assertSame(cell, same);
            Assert.AreSame(cell, same);
            wb.Close();
        }
コード例 #15
0
        public void TestOffsetWithEmpty23Arguments()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ICell     cell     = workbook.CreateSheet().CreateRow(0).CreateCell(0);

            cell.SetCellFormula("OFFSET(B1,,)");
            String value     = "EXPECTED_VALUE";
            ICell  valueCell = cell.Row.CreateCell(1);

            valueCell.SetCellValue(value);
            workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
            Assert.AreEqual(CellType.String, cell.CachedFormulaResultType);
            Assert.AreEqual(value, cell.StringCellValue);
        }
コード例 #16
0
        internal static void FillSingleCells(DataSet dataSet, XSSFWorkbook workBook)
        {
            var creationHelper = workBook.GetCreationHelper();

            foreach (ISheet sheet in workBook)
            {
                foreach (IRow row in sheet)
                {
                    foreach (ICell cell in row.Cells)
                    {
                        FillCell(dataSet, cell, creationHelper);
                    }
                }
            }
        }
コード例 #17
0
        public void TestBug56655()
        {
            IWorkbook wb    = new XSSFWorkbook();
            ISheet    sheet = wb.CreateSheet();

            setCellFormula(sheet, 0, 0, "#VALUE!");
            setCellFormula(sheet, 0, 1, "SUMIFS(A:A,A:A,#VALUE!)");

            wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

            Assert.AreEqual(CellType.Error, getCell(sheet, 0, 0).CachedFormulaResultType);
            Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 0, 0).ErrorCellValue);
            Assert.AreEqual(CellType.Error, getCell(sheet, 0, 1).CachedFormulaResultType);
            Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 0, 1).ErrorCellValue);

            wb.Close();
        }
コード例 #18
0
        public void SaveFileInSpecifiedPathXLSX(DataTable dt, string[] columns
                                                , string fullPath)
        {
            using (FileStream stream = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
            {
                string[]        headers      = columns;
                int             columnsCount = headers.Count();
                IWorkbook       wb           = new XSSFWorkbook();
                ISheet          sheet        = wb.CreateSheet("Details");
                ICreationHelper cH           = wb.GetCreationHelper();

                #region Cellstyle with bold and background color
                XSSFCellStyle cellStyle = (XSSFCellStyle)wb.CreateCellStyle();
                XSSFColor     color     = new XSSFColor(System.Drawing.Color.Lime);
                ((XSSFCellStyle)cellStyle).SetFillBackgroundColor(color);
                cellStyle.FillPattern = FillPattern.SolidForeground;
                cellStyle.SetFillForegroundColor(color);

                XSSFFont yourFont = (XSSFFont)wb.CreateFont();
                yourFont.FontHeightInPoints = (short)10.5;
                yourFont.FontName           = "Arial";
                yourFont.IsBold             = true;
                yourFont.IsItalic           = false;
                cellStyle.SetFont(yourFont);
                #endregion

                IRow headerRow = sheet.CreateRow(0);
                for (int i = 0; i < columns.Length; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(columns[i]);
                    cell.CellStyle = cellStyle;
                }
                for (int i = 1; i <= dt.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(i);
                    for (int j = 0; j < columnsCount; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i - 1].ItemArray[j].ToString()));
                    }
                }
                wb.Write(stream);
            }
        }
コード例 #19
0
ファイル: ExcelExporterEditor.cs プロジェクト: kimch2/Unity-1
    private void Export(string fileName, string exportDir)
    {
        XSSFWorkbook xssfWorkbook;
        IFormulaEvaluator formulaEvaluator;

        using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        {
            xssfWorkbook = new XSSFWorkbook(file);
            formulaEvaluator = xssfWorkbook.GetCreationHelper().CreateFormulaEvaluator();
            Dictionary<string, IFormulaEvaluator> evaluators = new Dictionary<string, IFormulaEvaluator>
            {
                {Path.GetFileName(fileName),formulaEvaluator }
            };
            foreach (string linkedFileName in xssfWorkbook.ExternalLinksTable.Select(p => $"{ExcelPath}/{p.LinkedFileName}"))
            {
                XSSFWorkbook linkedWorkBook;
                using (FileStream linkedFile = new FileStream(linkedFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    linkedWorkBook = new XSSFWorkbook(linkedFile);
                }
                evaluators.Add(Path.GetFileName(linkedFileName), linkedWorkBook.GetCreationHelper().CreateFormulaEvaluator());
            }
            formulaEvaluator.SetupReferencedWorkbooks(evaluators);
        }
        string protoName = Path.GetFileNameWithoutExtension(fileName);
        Log.Info($"{protoName}导表开始");
        string exportPath = Path.Combine(exportDir, $"{protoName}.txt");
        using (FileStream txt = new FileStream(exportPath, FileMode.Create))
        using (StreamWriter sw = new StreamWriter(txt))
        {
            for (int i = 0; i < xssfWorkbook.NumberOfSheets; ++i)
            {
                ISheet sheet = xssfWorkbook.GetSheetAt(i);
                if (sheet.SheetName.StartsWith("~"))
                {
                    continue;
                }
                ExportSheet(sheet, sw, formulaEvaluator);
            }
        }
        Log.Info($"{protoName}导表完成");
    }
コード例 #20
0
        private static void SaveMoviesToXLSX(IEnumerable <IMDbResponse> movies)
        {
            List <string> colNames = new List <string>();

            foreach (PropertyInfo prop in typeof(IMDbResponse).GetProperties())
            {
                colNames.Add(prop.Name);
            }

            using (FileStream stream = new FileStream("truefilms.xlsx", FileMode.Create))
            {
                IWorkbook       wb    = new XSSFWorkbook();
                ISheet          sheet = wb.CreateSheet("TrueFilms");
                ICreationHelper cH    = wb.GetCreationHelper();

                IRow topRow = sheet.CreateRow(0);
                int  ic     = 0;
                foreach (var col in colNames)
                {
                    ICell cell = topRow.CreateCell(ic);
                    cell.SetCellValue(col);
                    ic++;
                }

                int im         = 1;
                var properties = typeof(IMDbResponse).GetProperties();
                foreach (var movie in movies)
                {
                    IRow row = sheet.CreateRow(im);
                    for (int j = 0; j < colNames.Count; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        var   prop = properties.SingleOrDefault(p => string.Equals(p.Name, colNames[j]));
                        cell.SetCellValue(prop == null ? "" : prop.GetValue(movie)?.ToString());
                    }
                    im++;
                }
                wb.Write(stream);
            }
        }
コード例 #21
0
        static void CreateExportData(List <VendeurData> vendeursData, string fileFullName)
        {
            // https://stackoverflow.com/questions/19838743/trying-to-create-a-new-xlsx-file-using-npoi-and-write-to-it



            using (FileStream stream = new FileStream(fileFullName, FileMode.Create, FileAccess.Write))
            {
                IWorkbook       wb       = new XSSFWorkbook();
                ISheet          sheet    = wb.CreateSheet(SHEET_NAME);
                ICreationHelper cH       = wb.GetCreationHelper();
                int             rowIndex = FIRST_ROW;
                foreach (var vendeur in vendeursData)
                {
                    var   vendeurCourant = vendeur.VendeurName;
                    IRow  rowVendeur     = sheet.CreateRow(rowIndex);
                    ICell cellVendeur    = rowVendeur.CreateCell(0);
                    cellVendeur.SetCellValue(cH.CreateRichTextString(vendeurCourant));
                    //merged region https://scottstoecker.wordpress.com/2011/05/18/merging-excel-cells-with-npoi/
                    NPOI.SS.Util.CellRangeAddress craVendeur = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 4);
                    sheet.AddMergedRegion(craVendeur);
                    rowIndex++;
                    foreach (var clientDataAllYears in vendeur.Clients)
                    {
                        IRow  rowClient      = sheet.CreateRow(rowIndex);
                        ICell cellClientName = rowClient.CreateCell(1);
                        cellClientName.SetCellValue(cH.CreateRichTextString(clientDataAllYears.Name));
                        NPOI.SS.Util.CellRangeAddress craClient = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 1, 3);
                        sheet.AddMergedRegion(craClient);
                        InsertCellForYear(cH, clientDataAllYears, rowClient, "2016", 4);
                        InsertCellForYear(cH, clientDataAllYears, rowClient, "2017", 5);
                        InsertCellForYear(cH, clientDataAllYears, rowClient, "2018", 6);
                        rowIndex++;
                    }

                    rowIndex += 2;
                }
                wb.Write(stream);
            }
        }
コード例 #22
0
        public void TestBug56655a()
        {
            IWorkbook wb    = new XSSFWorkbook();
            ISheet    sheet = wb.CreateSheet();

            setCellFormula(sheet, 0, 0, "B1*C1");
            sheet.GetRow(0).CreateCell(1).SetCellValue("A");
            setCellFormula(sheet, 1, 0, "B1*C1");
            sheet.GetRow(1).CreateCell(1).SetCellValue("A");
            setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");

            wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

            Assert.AreEqual(CellType.Error, getCell(sheet, 0, 0).CachedFormulaResultType);
            Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 0, 0).ErrorCellValue);
            Assert.AreEqual(CellType.Error, getCell(sheet, 1, 0).CachedFormulaResultType);
            Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 1, 0).ErrorCellValue);
            Assert.AreEqual(CellType.Error, getCell(sheet, 0, 3).CachedFormulaResultType);
            Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 0, 3).ErrorCellValue);

            wb.Close();
        }
コード例 #23
0
        internal static void FillNamedArea(DataTable dataTable, IName areaName, XSSFWorkbook workBook)
        {
            if (dataTable == null)
            {
                return;
            }

            var creationHelper = workBook.GetCreationHelper();

            var(area, lastIndex) = GetAreaByName(workBook, areaName);
            var rowCount = dataTable.Rows.Count;

            if (rowCount == 0)
            {
                FillRange(area, creationHelper, dataTable.Columns);
                return;
            }

            if (rowCount == 1)
            {
                FillRange(area, creationHelper, dataTable.Columns, dataTable.Rows[0]);
                return;
            }

            var(nextArea, nextIndex) = (area, lastIndex);

            for (int i = 0; i < rowCount; i++)
            {
                if (i < rowCount - 1)
                {
                    (nextArea, nextIndex) = CopyArea(area, lastIndex + 1);
                }

                FillRange(area, creationHelper, dataTable.Columns, dataTable.Rows[i]);

                (area, lastIndex) = (nextArea, nextIndex);
            }
        }
コード例 #24
0
        public void TestInvalidURLs()
        {
            XSSFWorkbook       workbook     = new XSSFWorkbook();
            XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;

            String[] invalidURLs =
            {
                "http:\\apache.org",
                "www.apache .org",
                "c:\\temp",
                "\\poi"
            };
            foreach (String s in invalidURLs)
            {
                try
                {
                    CreateHelper.CreateHyperlink(HyperlinkType.Url).Address = (s);
                    Assert.Fail("expected ArgumentException: " + s);
                }
                catch (ArgumentException)
                {
                }
            }
        }
コード例 #25
0
ファイル: TestXSSFWorkbook.cs プロジェクト: ruo2012/Npoi.Core
        public void SaveLoadNew()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();

            //check that the default date system is Set to 1900
            CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;

            Assert.IsNotNull(pr);
            Assert.IsTrue(pr.IsSetDate1904());
            Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");

            ISheet sheet1 = workbook.CreateSheet("sheet1");
            ISheet sheet2 = workbook.CreateSheet("sheet2");

            workbook.CreateSheet("sheet3");

            IRichTextString rts = workbook.GetCreationHelper().CreateRichTextString("hello world");

            sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
            sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
            sheet2.CreateRow(0);

            Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
            Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);

            FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
            Stream   out1 = File.OpenWrite(file.Name);

            workbook.Write(out1);
            out1.Close();

            // Check the namespace Contains what we'd expect it to
            OPCPackage  pkg       = OPCPackage.Open(file.ToString());
            PackagePart wbRelPart =
                pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));

            Assert.IsNotNull(wbRelPart);
            Assert.IsTrue(wbRelPart.IsRelationshipPart);
            Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);

            PackagePart wbPart =
                pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));

            // Links to the three sheets, shared strings and styles
            Assert.IsTrue(wbPart.HasRelationships);
            Assert.AreEqual(5, wbPart.Relationships.Size);

            // Load back the XSSFWorkbook
            workbook = new XSSFWorkbook(pkg);
            Assert.AreEqual(3, workbook.NumberOfSheets);
            Assert.IsNotNull(workbook.GetSheetAt(0));
            Assert.IsNotNull(workbook.GetSheetAt(1));
            Assert.IsNotNull(workbook.GetSheetAt(2));

            Assert.IsNotNull(workbook.GetSharedStringSource());
            Assert.IsNotNull(workbook.GetStylesSource());

            Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
            Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);

            sheet1 = workbook.GetSheetAt(0);
            Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
            Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);

            pkg.Close();
        }
コード例 #26
0
        public void TestCreate()
        {
            XSSFWorkbook       workbook     = new XSSFWorkbook();
            XSSFSheet          sheet        = workbook.CreateSheet() as XSSFSheet;
            XSSFRow            row          = sheet.CreateRow(0) as XSSFRow;
            XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;

            String[] urls =
            {
                "http://apache.org/",
                "www.apache.org",
                "/temp",
                "file:///c:/temp",
                "http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
            };
            for (int i = 0; i < urls.Length; i++)
            {
                String        s    = urls[i];
                XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
                link.Address = (s);

                XSSFCell cell = row.CreateCell(i) as XSSFCell;
                cell.Hyperlink = (link);
            }
            workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
            sheet    = workbook.GetSheetAt(0) as XSSFSheet;
            PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;

            Assert.AreEqual(urls.Length, rels.Size);
            for (int i = 0; i < rels.Size; i++)
            {
                PackageRelationship rel = rels.GetRelationship(i);
                if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
                {
                    Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
                }
                else
                {
                    // there should be a relationship for each URL
                    Assert.AreEqual(urls[i], rel.TargetUri.ToString());
                }
            }

            // Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
            workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
            sheet    = workbook.GetSheetAt(0) as XSSFSheet;
            rels     = sheet.GetPackagePart().Relationships;
            Assert.AreEqual(urls.Length, rels.Size);
            for (int i = 0; i < rels.Size; i++)
            {
                PackageRelationship rel = rels.GetRelationship(i);
                if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
                {
                    Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
                }
                else
                {
                    // there should be a relationship for each URL
                    Assert.AreEqual(urls[i], rel.TargetUri.ToString());
                }
            }
        }
コード例 #27
0
        public void TestLoadSave()
        {
            XSSFWorkbook    workbook     = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx");
            ICreationHelper CreateHelper = workbook.GetCreationHelper();

            Assert.AreEqual(3, workbook.NumberOfSheets);
            XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);

            // Check hyperlinks
            Assert.AreEqual(4, sheet.NumHyperlinks);
            doTestHyperlinkContents(sheet);


            // Write out, and check

            // Load up again, check all links still there
            XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);

            Assert.AreEqual(3, wb2.NumberOfSheets);
            Assert.IsNotNull(wb2.GetSheetAt(0));
            Assert.IsNotNull(wb2.GetSheetAt(1));
            Assert.IsNotNull(wb2.GetSheetAt(2));

            sheet = (XSSFSheet)wb2.GetSheetAt(0);


            // Check hyperlinks again
            Assert.AreEqual(4, sheet.NumHyperlinks);
            doTestHyperlinkContents(sheet);


            // Add one more, and re-check
            IRow  r17  = sheet.CreateRow(17);
            ICell r17c = r17.CreateCell(2);

            IHyperlink hyperlink = CreateHelper.CreateHyperlink(HyperlinkType.Url);

            hyperlink.Address = ("http://poi.apache.org/spreadsheet/");
            hyperlink.Label   = "POI SS Link";
            r17c.Hyperlink    = (hyperlink);

            Assert.AreEqual(5, sheet.NumHyperlinks);
            doTestHyperlinkContents(sheet);

            Assert.AreEqual(HyperlinkType.Url,
                            sheet.GetRow(17).GetCell(2).Hyperlink.Type);
            Assert.AreEqual("POI SS Link",
                            sheet.GetRow(17).GetCell(2).Hyperlink.Label);
            Assert.AreEqual("http://poi.apache.org/spreadsheet/",
                            sheet.GetRow(17).GetCell(2).Hyperlink.Address);


            // Save and re-load once more

            XSSFWorkbook wb3 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb2);

            Assert.AreEqual(3, wb3.NumberOfSheets);
            Assert.IsNotNull(wb3.GetSheetAt(0));
            Assert.IsNotNull(wb3.GetSheetAt(1));
            Assert.IsNotNull(wb3.GetSheetAt(2));

            sheet = (XSSFSheet)wb3.GetSheetAt(0);

            Assert.AreEqual(5, sheet.NumHyperlinks);
            doTestHyperlinkContents(sheet);

            Assert.AreEqual(HyperlinkType.Url,
                            sheet.GetRow(17).GetCell(2).Hyperlink.Type);
            Assert.AreEqual("POI SS Link",
                            sheet.GetRow(17).GetCell(2).Hyperlink.Label);
            Assert.AreEqual("http://poi.apache.org/spreadsheet/",
                            sheet.GetRow(17).GetCell(2).Hyperlink.Address);
        }
コード例 #28
0
        public ActionResult RelatorioEntradasFiltro(DateTime txt_data_inicio, DateTime txt_data_fim, int cbx_tipo)
        {
            //cbx_tipo = 1, significa gerar PDF
            //cbx_tipo = 2, significa gerar XLSX

            if (cbx_tipo == 1)
            {
                //Criando o PDF
                var relatorioPDF = new ViewAsPdf
                {
                    ViewName    = "RelatorioEntradasPDF",
                    IsGrayScale = false,
                    FileName    = "RelatorioEntradasPDF.pdf",
                    Model       = EntradaESaidaRelatorioDao.GetEntradasRelatorioFiltro(txt_data_inicio, txt_data_fim)
                };
                return(relatorioPDF);
            }
            else
            {
                List <EntradaGraficos> entradas = EntradaESaidaRelatorioDao.GetEntradasRelatorioFiltro(txt_data_inicio, txt_data_fim);
                //Criando o XLSX
                IWorkbook workbook = new XSSFWorkbook();
                ISheet    sheet    = workbook.CreateSheet("Relatório Entradas");
                int       rowNumer = 0;

                //---- HEADER
                IRow       row = sheet.CreateRow(rowNumer);
                ICell      cellData;
                ICell      cellTotal;
                ICellStyle styleData = workbook.CreateCellStyle();
                styleData.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Violet.Index;
                styleData.Alignment           = HorizontalAlignment.Center;

                ICellStyle styleTotal = workbook.CreateCellStyle();
                styleTotal.Alignment = HorizontalAlignment.Right;
                ICreationHelper createHelper = workbook.GetCreationHelper();
                styleTotal.DataFormat = (createHelper.CreateDataFormat().GetFormat("R$ #.##0;-R$ #.##0"));


                cellData = row.CreateCell(0);
                cellData.SetCellValue("Data");
                cellData.CellStyle = styleData;

                cellTotal = row.CreateCell(1);
                cellTotal.SetCellValue("Total");
                cellTotal.CellStyle = styleTotal;
                if (entradas.Count > 0)
                {
                    foreach (var item in entradas)
                    {
                        rowNumer++;
                        row      = sheet.CreateRow(rowNumer);
                        cellData = row.CreateCell(0);
                        cellData.SetCellValue(item.Data.ToString("dd/MM/yyyy"));
                        cellData.CellStyle = styleData;

                        cellTotal = row.CreateCell(1);
                        cellTotal.SetCellValue((double)item.total);
                        cellTotal.CellStyle = styleTotal;
                    }
                }
                else
                {
                    rowNumer++;
                    row      = sheet.CreateRow(rowNumer);
                    cellData = row.CreateCell(0);
                    cellData.SetCellValue("SEM DADOS NESSE PERIODO");
                    cellData.CellStyle = styleData;

                    cellTotal = row.CreateCell(1);
                    cellTotal.SetCellValue(0.00);
                    cellTotal.CellStyle = styleTotal;
                }
                //Tamanho das colunas
                sheet.SetColumnWidth(0, 40 * 256);
                sheet.SetColumnWidth(1, 20 * 256);



                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                return(File(stream.ToArray(),                                                                            //The binary data of the XLS file
                            "application/vnd.ms-excel",                                                                  //MIME type of Excel files
                            string.Format("RelatorioEntradas_{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")))); //Suggested file name in the "Save as" dialog which will be displayed to the end user
            }
        }
コード例 #29
0
    public static void ExportToExcelNopi(string FilePath, string SqlQuery)
    {
        DataTable dt = new DataTable();

        OracleConnection conn = WebTools.GetIpmsConnection();
        OracleCommand    cmd  = new OracleCommand(SqlQuery, conn);

        // create data adapter
        OracleDataAdapter da = new OracleDataAdapter(cmd);

        // this will query your database and return the result to your datatable
        da.Fill(dt);

        // close objects
        conn.Close();
        da.Dispose();
        cmd.Dispose();

        using (FileStream stream = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
        {
            IWorkbook       wb    = new XSSFWorkbook();
            ISheet          sheet = wb.CreateSheet("Sheet1");
            ICreationHelper cH    = wb.GetCreationHelper();

            // create header
            IRow header_row = sheet.CreateRow(0); // first row = 0

            // style for heading
            IFont boldFont = wb.CreateFont();
            boldFont.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle header_style = wb.CreateCellStyle();
            header_style.SetFont(boldFont);

            //boldStyle.BorderBottom = BorderStyle.Medium;
            header_style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            header_style.FillPattern         = FillPattern.SolidForeground;
            header_style.Alignment           = HorizontalAlignment.Center;
            header_style.VerticalAlignment   = VerticalAlignment.Center;

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ICell cell = header_row.CreateCell(j);
                cell.SetCellValue(cH.CreateRichTextString(dt.Columns[j].ColumnName));
                cell.CellStyle = header_style;
            }


            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    DataRow drow      = dt.Rows[i];
                    string  str_value = drow.ItemArray[j].ToString();
                    ICell   cell      = row.CreateCell(j);
                    if (dt.Columns[j].DataType == typeof(DateTime) && drow.ItemArray[j] != DBNull.Value)
                    {
                        cell.SetCellValue(DateTime.Now);
                        IDataFormat dataFormatCustom = wb.CreateDataFormat();

                        DateTime?date_field = drow.Field <DateTime?>(j);

                        //cell.CellStyle.DataFormat = dataFormatCustom.GetFormat("yyyyMMdd HH:mm:ss");
                        //DateTime.ParseExact(str_value, "dd/M/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture)
                        cell.SetCellValue(date_field.Value);
                        ICellStyle date_style = wb.CreateCellStyle();
                        date_style.DataFormat = dataFormatCustom.GetFormat("dd-mmm-yyyy;@");
                        cell.CellStyle        = date_style;
                    }
                    {
                        cell.SetCellValue(cH.CreateRichTextString(str_value.ToString()));
                    }
                }
            }
            wb.Write(stream);
        }
    }
コード例 #30
0
        public AlertMessage ExportTagihToExcel(JDatatableViewModel model)
        {
            AlertMessage alert = new AlertMessage();

            model.Length = -1;

            JDatatableResponse response = GetDatatableByQuery(model);

            IWorkbook workbook = new XSSFWorkbook();

            int rowIndex = 0;

            ISheet sheet1 = workbook.CreateSheet("Sheet 1");

            string dateFormat = "yyyy/MM/dd";

            ICreationHelper creationHelper = workbook.GetCreationHelper();

            ICellStyle cellStyleDate = workbook.CreateCellStyle();

            cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);

            ICellStyle cellStyleText = workbook.CreateCellStyle();

            cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");

            ICellStyle cellStyleNumber = workbook.CreateCellStyle();

            cellStyleNumber.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");

            var fontBold = workbook.CreateFont();

            fontBold.FontHeightInPoints = 11;
            fontBold.Boldweight         = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;

            ICellStyle cellStyleHeader = workbook.CreateCellStyle();

            cellStyleHeader.SetFont(fontBold);
            cellStyleHeader.BorderBottom        = BorderStyle.Thin;
            cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
            cellStyleHeader.FillPattern         = FillPattern.SolidForeground;

            IRow row = sheet1.CreateRow(rowIndex);

            row = sheet1.CreateRow(rowIndex);

            int cellIndex = 0;

            ICell cell = null;

            #region cell header
            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("RayonCode");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("Plant");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("KaCab NIK");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("KaCab Name");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("ASM NIK");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("ASM Name");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("FSS NIK");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("FSS Name");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("SLM NIK");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("SLM Name");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("Collector NIK");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("Collector Name");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("AR Collector NIK");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("AR Collector Name");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("SPV AR Collector NIK");
            cell.CellStyle = cellStyleHeader;

            cell = row.CreateCell(cellIndex++);
            cell.SetCellValue("SPV AR Collector Name");
            cell.CellStyle = cellStyleHeader;
            #endregion

            rowIndex++;

            #region cell data
            List <HierTagihViewModel> listData = response.Data as List <HierTagihViewModel>;

            if (listData != null)
            {
                foreach (var item in listData)
                {
                    row       = sheet1.CreateRow(rowIndex);
                    cellIndex = 0;

                    // RayonCode
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.RayonCode);
                    cell.CellStyle = cellStyleText;

                    // Plant
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.PlantCode);
                    cell.CellStyle = cellStyleNumber;

                    // NSM NIK
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.NSMNik);
                    cell.CellStyle = cellStyleNumber;

                    // NSM Name
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.NSMFullname);
                    cell.CellStyle = cellStyleText;

                    // ASM NIK
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.ASMNik);
                    cell.CellStyle = cellStyleNumber;

                    // ASM Name
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.ASMFullname);
                    cell.CellStyle = cellStyleText;

                    // FSS NIK
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.FSSNik);
                    cell.CellStyle = cellStyleNumber;

                    // FSS Name
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.FSSFullname);
                    cell.CellStyle = cellStyleText;

                    // SLM NIK
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.SLMNik);
                    cell.CellStyle = cellStyleNumber;

                    // SLM Name
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.SLMFullname);
                    cell.CellStyle = cellStyleText;

                    // Collector NIK
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.CollectorNik ?? 0);
                    cell.CellStyle = cellStyleNumber;

                    // Collector Name
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.CollectorFullname);
                    cell.CellStyle = cellStyleText;

                    // AR Collector NIK
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.FakturisNik ?? 0);
                    cell.CellStyle = cellStyleNumber;

                    // AR Collector Name
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.FakturisFullname);
                    cell.CellStyle = cellStyleText;

                    // SPV AR Collector NIK
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.SPVFakturisNik ?? 0);
                    cell.CellStyle = cellStyleNumber;

                    // SPV AR Collector Name
                    cell = row.CreateCell(cellIndex++);
                    cell.SetCellValue(item.SPVFakturisFullname);
                    cell.CellStyle = cellStyleText;

                    rowIndex++;
                }
            }
            #endregion

            #region auto size column
            for (int i = 0; i < 16; i++)
            {
                sheet1.AutoSizeColumn(i);
            }
            #endregion

            using (var ms = new MemoryStream())
            {
                workbook.Write(ms);

                alert.Data = ms.ToArray();
            }

            alert.Status = 1;

            return(alert);
        }