예제 #1
0
        public bool CrearArchivoEmail()
        {
            bool resultado = true;

            try
            {
                string tempFile = Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx";

                XSSFWorkbook wb = new XSSFWorkbook();

                using (var ms = new MemoryStream()) {
                    ISheet sheet = wb.CreateSheet("Destinatarios");

                    int indexCabecera = 0;

                    var rowCabecera = sheet.CreateRow(indexCabecera);

                    XSSFCellStyle cellStyle = (XSSFCellStyle)wb.CreateCellStyle();

                    cellStyle.FillBackgroundColor = HSSFColor.DarkBlue.Index;

                    XSSFFont font  = (XSSFFont)wb.CreateFont();
                    var      color = new XSSFColor(new byte[] { 255, 255, 255 });
                    font.SetColor(color);
                    cellStyle.SetFont(font);

                    cellStyle.FillPattern = FillPattern.SolidForeground;

                    var cell1 = rowCabecera.CreateCell(0);
                    var cell2 = rowCabecera.CreateCell(1);

                    cell1.CellStyle = cellStyle;
                    cell1.SetCellValue(Enumeradores.EXCEL_CABECERA_CORREO);
                    cell2.CellStyle = cellStyle;
                    cell2.SetCellValue(Enumeradores.EXCEL_CABECERA_NOMBRE);

                    wb.Write(ms);

                    File.WriteAllBytes(tempFile, ms.ToArray());
                }



                System.Diagnostics.Process.Start(tempFile);
            }
            catch (Exception)
            {
                throw;
            }


            return(resultado);
        }
        public string setCellData(string sheetName, string colName, int rowNum, string data)
        {
            fs       = new FileStream(path, FileMode.Open, FileAccess.Read);
            workbook = new XSSFWorkbook(fs);
            if (rowNum <= 0)
            {
                return("");
            }
            int colNum = -1;
            int index  = workbook.GetSheetIndex(sheetName);

            if (index == -1)
            {
                return("");
            }
            sheet = workbook.GetSheetAt(index);
            row   = sheet.GetRow(0);
            for (int i = 0; i < row.LastCellNum; i++)
            {
                if (row.GetCell(i).StringCellValue.Equals(colName))
                {
                    colNum = i;
                }
            }
            if (colNum == -1)
            {
                return("");
            }
            row = sheet.GetRow(rowNum - 1);
            if (row == null)
            {
                row = sheet.CreateRow(rowNum - 1);
            }
            cell = row.GetCell(colNum - 1);
            if (cell == null)
            {
                cell = row.CreateCell(colNum);
            }

            ICellStyle cs = workbook.CreateCellStyle();

            cs.WrapText    = true;
            cell.CellStyle = cs;
            cell.SetCellValue(data);

            FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);

            workbook.Write(f);
            f.Close();
            fs.Close();

            return(data);
        }
예제 #3
0
        /// <summary>
        /// 给单元格加边框,居中
        /// </summary>
        private ICellStyle AddStytle(XSSFWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();// 样式

            // 设置单元格的样式:水平对齐居中
            style.Alignment    = NPOI.SS.UserModel.HorizontalAlignment.Center;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; // 下边框为细线边框
            style.BorderLeft   = NPOI.SS.UserModel.BorderStyle.Thin; // 左边框
            style.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin; // 上边框
            style.BorderTop    = NPOI.SS.UserModel.BorderStyle.Thin; // 右边框
            return(style);
        }
예제 #4
0
        private void setCellStyle(XSSFWorkbook workbook, ICell cell)
        {
            var fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();

            fCellStyle.FillForegroundXSSFColor = new XSSFColor(Color.Green);
            fCellStyle.FillPattern             = FillPattern.SolidForeground;

            fCellStyle.VerticalAlignment = VerticalAlignment.Center;
            fCellStyle.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;

            cell.CellStyle = fCellStyle;
        }
        private void CreateReportSheet(XSSFWorkbook workbook, string sectionName, IList <WeeklyStatusReport.WorkItemDetail> workItems, 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);
                idCell.CellStyle = hlinkstyle;
                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($"{workItems.Count(i => i.WorkItemType == WorkItemTypes.Bug)} bugs / {workItems.Count(i => i.WorkItemType == WorkItemTypes.Task)} tasks");
            summaryRow.CreateCell(cellIdx++, CellType.String);
            if (includeTags)
            {
                summaryRow.CreateCell(cellIdx++, CellType.String);
            }
            summaryRow.CreateCell(cellIdx++, CellType.Numeric).SetCellValue(workItems.Sum(i => i.EstimatedToComplete));
            summaryRow.CreateCell(cellIdx, CellType.Numeric).SetCellValue(workItems.Sum(i => i.TimeSpent));

            AutosizeCells(excelSheet, summaryRow.Cells.Count);
        }
예제 #6
0
        private ICellStyle CreatColumnRowStyle(XSSFWorkbook workbook)
        {
            ICellStyle headStyle = workbook.CreateCellStyle();

            headStyle.Alignment = HorizontalAlignment.CENTER;
            IFont font = workbook.CreateFont();

            font.FontHeightInPoints = 10;
            font.Boldweight         = 700;
            headStyle.SetFont(font);
            return(headStyle);
        }
        private XSSFCellStyle CreateDefStyle(XSSFWorkbook workbook)
        {
            var defStyle = (XSSFCellStyle)workbook.CreateCellStyle();

            defStyle.SetFont(CreateDefaultFont(workbook));

            defStyle.WrapText          = true;
            defStyle.Alignment         = HorizontalAlignment.Left;
            defStyle.VerticalAlignment = VerticalAlignment.Center;

            return(defStyle);
        }
예제 #8
0
        public async Task <FileResult> GetPdf()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet    sheet1   = workbook.CreateSheet("Sheet1");

            var titleFont = workbook.CreateFont();

            titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;

            var titleStyle = workbook.CreateCellStyle();

            titleStyle.SetFont(titleFont);

            IRow row = sheet1.CreateRow(0);

            string[] headers = new string[] { "Word", "Meaning", "UK", "US", "Example", "Subcategory", "Category", "Date add" };
            for (var i = 0; i < headers.Length; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = titleStyle;
            }

            var filter = new WordQuery();

            filter.WithoutPagination = true;
            var queryResult = await repository.GetWords(filter);

            for (var i = 0; i < queryResult.Items.Count(); i++)
            {
                row = sheet1.CreateRow(i + 1);
                var word = queryResult.Items.ElementAt(i);
                row.CreateCell(0).SetCellValue(word.Name);
                row.CreateCell(1).SetCellValue(word.Meaning);
                row.CreateCell(2).SetCellValue(word.PronunciationUK);
                row.CreateCell(3).SetCellValue(word.PronunciationUS);
                row.CreateCell(4).SetCellValue(Regex.Replace(word.Example, "<.*?>", String.Empty));
                row.CreateCell(5).SetCellValue(word.Subcategory.Name);
                row.CreateCell(6).SetCellValue(word.Subcategory.Category.Name);
                row.CreateCell(7).SetCellValue(String.Format("{0:MM-dd-yyyy}", word.CreatedAt));
            }

            for (var i = 0; i < headers.Length; i++)
            {
                sheet1.AutoSizeColumn(i);
            }

            var stream = new MemoryStream();

            workbook.Write(stream);

            return(File(new MemoryStream(stream.ToArray()), "application/vnd.ms-excel", "plik.xls"));
        }
        private XSSFCellStyle CreateTimeFormatStyle(XSSFWorkbook workbook, IFont font)
        {
            var timeStyle = (XSSFCellStyle)workbook.CreateCellStyle();

            timeStyle.SetFont(font ?? CreateDefaultFont(workbook));
            timeStyle.WrapText          = true;
            timeStyle.Alignment         = HorizontalAlignment.Left;
            timeStyle.VerticalAlignment = VerticalAlignment.Center;
            timeStyle.DataFormat        = workbook.CreateDataFormat().GetFormat("hh:mm");

            return(timeStyle);
        }
예제 #10
0
        /// <summary>
        /// 获取表头样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        public static XSSFCellStyle GetHeadStyle(this XSSFWorkbook workbook)
        {
            XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();

            headStyle.Alignment = HorizontalAlignment.Center;
            XSSFFont font = (XSSFFont)workbook.CreateFont();

            font.FontHeightInPoints = 12;
            font.Boldweight         = 700;
            headStyle.SetFont(font);
            return(headStyle);
        }
예제 #11
0
        private void CreateEmptyExcel(string excelName)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();

            ICellStyle style = workbook.CreateCellStyle();

            style.FillPattern              = FillPattern.SolidForeground;
            style.FillForegroundColor      = 42;
            style.BorderLeft               =
                style.BorderTop            =
                    style.BorderRight      =
                        style.BorderBottom = BorderStyle.Thin;
            style.Alignment             = HorizontalAlignment.Center;
            sheet.CreateRow(0).RowStyle = style;
            sheet.CreateRow(1).RowStyle = style;

            XSSFDataValidationHelper     helper     = new XSSFDataValidationHelper((XSSFSheet)sheet);
            XSSFDataValidationConstraint constraint =
                (XSSFDataValidationConstraint)helper.CreateExplicitListConstraint(Enum.GetNames(typeof(DataConverter.DataType)));
            CellRangeAddressList range = new CellRangeAddressList(1, 1, 0, 255);

            sheet.AddValidationData((XSSFDataValidation)helper.CreateValidation(constraint, range));

            style                       = workbook.CreateCellStyle();
            style.FillPattern           = FillPattern.SolidForeground;
            style.FillForegroundColor   = 42;
            style.BorderLeft            =
                style.BorderTop         =
                    style.BorderRight   = BorderStyle.Thin;
            style.BorderBottom          = BorderStyle.Medium;
            style.Alignment             = HorizontalAlignment.Center;
            sheet.CreateRow(2).RowStyle = style;

            using (FileStream fs = new FileStream(ExcelDirectory + excelName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            workbook.Close();
        }
예제 #12
0
        public void TestCloneStyleSameWB()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            Assert.AreEqual(1, wb.NumberOfFonts);

            XSSFFont fnt = (XSSFFont)wb.CreateFont();

            fnt.FontName = ("TestingFont");
            Assert.AreEqual(2, wb.NumberOfFonts);

            XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();

            orig.Alignment = (HorizontalAlignment.Right);
            orig.SetFont(fnt);
            orig.DataFormat = (short)18;

            Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
            Assert.AreEqual(fnt, orig.GetFont());
            Assert.AreEqual(18, orig.DataFormat);

            XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();

            Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
            Assert.AreNotEqual(fnt, clone.GetFont());
            Assert.AreNotEqual(18, clone.DataFormat);

            clone.CloneStyleFrom(orig);
            Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
            Assert.AreEqual(fnt, clone.GetFont());
            Assert.AreEqual(18, clone.DataFormat);
            Assert.AreEqual(2, wb.NumberOfFonts);

            clone.Alignment  = HorizontalAlignment.Left;
            clone.DataFormat = 17;
            Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
            Assert.AreEqual(18, orig.DataFormat);

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
        }
예제 #13
0
        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xlsx)
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static MemoryStream TableToExcelForXLSX(DataTable dt)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet       sheet        = xssfworkbook.CreateSheet("sheet1");

            //加工 只导出Caption不为空的数据
            List <DataColumn> cols = new List <DataColumn>();

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (!string.IsNullOrEmpty(dt.Columns[i].Caption))
                {
                    cols.Add(dt.Columns[i]);
                }
            }

            //表头
            IRow row = sheet.CreateRow(0);

            for (int i = 0; i < cols.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(cols[i].Caption);
                ICellStyle style = xssfworkbook.CreateCellStyle(); //创建样式对象
                IFont      font  = xssfworkbook.CreateFont();      //创建一个字体样式对象
                font.Color = new HSSFColor.White().Indexed;
                font.FontHeightInPoints   = 13;                    //字体大小
                style.FillBackgroundColor = HSSFColor.RoyalBlue.Index;
                style.FillForegroundColor = HSSFColor.RoyalBlue.Index;
                style.FillPattern         = FillPattern.SolidForeground;
                style.SetFont(font);    //将字体样式赋给样式对象
                cell.CellStyle = style; //把样式赋给单元格
            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < cols.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][cols[j]].ToString());
                }
            }

            using (MemoryStream ms = new MemoryStream())
            {
                xssfworkbook.Write(ms);
                ms.Flush();
                return(ms);
            }
        }
예제 #14
0
        /// <summary>
        /// 导出教师
        /// </summary>
        /// <param name="list"></param>
        /// <param name="title"></param>
        /// <returns></returns>
        public byte[] ExportTeacher(List <ExportTeacherModel> list, string title)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet(title);
            DataTable    tblDatas = new DataTable("Datas");
            //设置边框样式
            ICellStyle style0 = workbook.CreateCellStyle();

            BasicStyles(style0);
            Title(6, sheet, title, workbook);
            List <string> TableHead = new List <string>
            {
                "帐号",
                "姓名",
                "性别",
                "学科",
                "年级",
                "班级"
            };

            DataList(tblDatas, TableHead, sheet, workbook, 1);
            //数据
            for (int i = 0; i < list.Count; i++)
            {
                IRow  row1 = sheet.CreateRow(i + 2);
                ICell cell = row1.CreateCell(0);
                cell.SetCellValue(i + 1);
                cell = row1.CreateCell(1);
                cell.SetCellValue(list[i].LoginName);
                cell = row1.CreateCell(2);
                cell.SetCellValue(list[i].UserName);
                cell = row1.CreateCell(3);
                cell.SetCellValue(list[i].Gender);
                cell = row1.CreateCell(4);
                cell.SetCellValue(list[i].SubjectName);
                cell = row1.CreateCell(5);
                cell.SetCellValue(list[i].GradeName);
                cell = row1.CreateCell(6);
                cell.SetCellValue(list[i].ClassName);

                TraverseStyle(6, row1, style0);
            }
            sheet.SetColumnWidth(0, 6 * 256);
            sheet.SetColumnWidth(1, 34 * 256);
            ColumnWidth(2, 6, 15, sheet);
            MemoryStream stream = new MemoryStream();

            workbook.Write(stream);
            var buf = stream.ToArray();

            return(buf);
        }
예제 #15
0
        private XSSFCellStyle GetNormalRowStyleHeader(XSSFWorkbook hssfworkbook)
        {
            if (normalRowStyleHeader == null)
            {
                IFont font = hssfworkbook.CreateFont();
                font.FontHeightInPoints = 8;
                font.Color    = NPOI.HSSF.Util.HSSFColor.White.Index;
                font.IsBold   = true;
                font.FontName = "Verdana";
                //byte[] rgb = new byte[3] { 192, 0, 0 };

                byte[] rgb = new byte[3] {
                    0, 68, 132
                };
                //XSSFCellStyle normalRowStyleHeaders = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
                //normalRowStyleHeaders.SetFillForegroundColor(new XSSFColor(System.Drawing.Color.Azure));
                //normalRowStyleHeaders.SetFillBackgroundColor(new XSSFColor(System.Drawing.Color.Black));

                //CellStyle style = workbook.createCellStyle();
                //style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
                //style.setFillPattern(CellStyle.SOLID_FOREGROUND);

                XSSFCellStyle normalRowStyleHeaders = (XSSFCellStyle)hssfworkbook.CreateCellStyle();

                normalRowStyleHeaders.SetFillForegroundColor(new XSSFColor(rgb));

                normalRowStyleHeaders.FillPattern = FillPattern.SolidForeground;
                //normalRowStyleHeaders.SetFillBackgroundColor(new XSSFColor(rgb));
                //normalRowStyleHeaders.setFillPattern(XSSFCellStyle.);
                //normalRowStyleHeaders.setFill;

                normalRowStyleHeaders.Alignment         = HorizontalAlignment.Center;
                normalRowStyleHeaders.VerticalAlignment = VerticalAlignment.Center;
                normalRowStyleHeaders.SetFont(font);

                //XSSFCellStyle HeaderCellStyle1 = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
                //normalRowStyleHeader.SetFillForegroundColor(new XSSFColor(rgb));



                //normalRowStyleHeader.bac = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;

                //normalRowStyle.LeftBorderColor =

                normalRowStyleHeaders.BorderTop    = BorderStyle.None;
                normalRowStyleHeaders.BorderRight  = BorderStyle.None;
                normalRowStyleHeaders.BorderBottom = BorderStyle.None;
                normalRowStyleHeaders.BorderLeft   = BorderStyle.None;
                normalRowStyleHeader = normalRowStyleHeaders;
            }
            return(normalRowStyleHeader);
        }
예제 #16
0
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheetName">页名</param>
        /// <param name="cellHead">属性的名称(key)和显示名称(value)</param>
        /// <param name="data">导出到excel数据源</param>
        /// <returns></returns>
        public XSSFWorkbook Export <T>(string sheetName, Dictionary <string, string> cellHead, IQueryable <T> data)
        {
            //HSSF使用于2007之前的xls版本,XSSF适用于2007及其之后的xlsx版本
            XSSFWorkbook xk    = new XSSFWorkbook();
            ISheet       sheet = xk.CreateSheet(sheetName);

            ICellStyle style = xk.CreateCellStyle();

            style.WrapText = true;
            IFont font = xk.CreateFont();

            font.Boldweight = (short)FontBoldWeight.Bold;
            style.SetFont(font);

            IRow row = sheet.CreateRow(0);

            for (int i = 0; i < cellHead.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(cellHead.ElementAt(i).Value);
                cell.SetCellType(CellType.String);
                cell.CellStyle = style;
            }

            int rowIndex = 0;

            foreach (var entity in data)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                for (int j = 0; j < cellHead.Count; j++)
                {
                    var cellValue = string.Empty;
                    var property  = entity.GetType().GetProperties().FirstOrDefault(e => e.Name == cellHead.ElementAt(j).Key);

                    if (property != null)
                    {
                        cellValue = property.GetValue(entity).ToString();
                        //对时间初始值赋值为空
                        if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59")
                        {
                            cellValue = "";
                        }
                    }

                    row.CreateCell(j).SetCellValue(cellValue);
                }
            }

            return(xk);
        }
예제 #17
0
        public string setCellData(string sheetName, int colNum, int rowNum, string data)
        {
            fs       = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
            workbook = new XSSFWorkbook(fs);
            if (rowNum <= 0)
            {
                return("");
            }
            int index = workbook.GetSheetIndex(sheetName);

            if (index == -1)
            {
                return("");
            }
            sheet = workbook.GetSheetAt(index);
            row   = sheet.GetRow(rowNum - 1);
            if (row == null)
            {
                row = sheet.CreateRow(rowNum - 1);
            }
            cell = row.GetCell(colNum - 1);
            if (cell == null)
            {
                cell = row.CreateCell(colNum - 1);
            }

            //for (int rownum = 1; rownum <= 1000; rownum++)
            //{
            //    row = sheet.CreateRow(rownum);

            //        cell = row.CreateCell(colNum-1);
            //        ICellStyle cs = workbook.CreateCellStyle();
            //        cs.WrapText = true;
            //        cell.CellStyle = cs;
            //        cell.SetCellValue(data);

            //}

            ICellStyle cs = workbook.CreateCellStyle();

            cs.WrapText    = true;
            cell.CellStyle = cs;
            cell.SetCellValue(data);

            FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);

            workbook.Write(f);
            f.Close();
            fs.Close();

            return(data);
        }
예제 #18
0
        private static void Facturas(string saveAs)
        {
            using (var fs = new FileStream(saveAs, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook      = new XSSFWorkbook();
                ISheet    current_sheet = workbook.CreateSheet("Results");

                var headerStyle = workbook.CreateCellStyle();
                headerStyle.FillForegroundColor = HSSFColor.Grey80Percent.Index;
                headerStyle.FillPattern         = FillPattern.SolidForeground;
                var headerFont = workbook.CreateFont();
                headerFont.Color  = HSSFColor.White.Index;
                headerFont.IsBold = true;

                IRow headers = current_sheet.CreateRow(0);
                headers.CreateCell(0).SetCellValue("Producto");
                headers.CreateCell(1).SetCellValue("Fraccion");
                headers.CreateCell(2).SetCellValue("Costo");
                headers.CreateCell(3).SetCellValue("Valor Agregado");
                headers.CreateCell(4).SetCellValue("Peso");
                headers.CreateCell(5).SetCellValue("Medida");
                headers.CreateCell(6).SetCellValue("Po");
                headers.CreateCell(7).SetCellValue("Factura");

                int row_count = 1;
                foreach (SO job in jobsList)
                {
                    IRow current_row = current_sheet.CreateRow(row_count);
                    current_row.CreateCell(0).SetCellValue(job.Product);    //producto (0)
                    current_row.CreateCell(2).SetCellValue(job.Cost);       // costo (2)
                    current_row.GetCell(2).SetCellType(CellType.Numeric);
                    current_row.CreateCell(3).SetCellValue(job.AddedValue); //valor agregado (3)
                    current_row.GetCell(3).SetCellType(CellType.Numeric);
                    current_row.CreateCell(4).SetCellValue(job.Weight);     // peso (4)
                    current_row.GetCell(4).SetCellType(CellType.Numeric);
                    current_row.CreateCell(5).SetCellValue(job.UM);         //medida!!
                    current_row.CreateCell(6).SetCellValue(job.PO);         //po_only!!
                    current_row.CreateCell(7).SetCellValue(job.Factura);    //factura!!
                    row_count += 1;
                }

                IRow headersRow = current_sheet.GetRow(0);
                for (int i = 0; i < 8; i++)
                {
                    current_sheet.AutoSizeColumn(i);
                    var cellToFormat = headersRow.GetCell(i);
                    cellToFormat.CellStyle = headerStyle;
                    cellToFormat.CellStyle.SetFont(headerFont);
                }
                workbook.Write(fs);
            }
        }
        //3 - > 10
        public static void Helper()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet    sheet1   = workbook.CreateSheet("Sheet1");
            IFont     font1    = workbook.CreateFont();

            font1.Color = IndexedColors.Red.Index;
            int x = 1;

            for (int i = 0; i < 15; i++)
            {
                IRow row = sheet1.CreateRow(i);
                for (int j = 0; j < 15; j++)
                {
                    ICell cell = row.CreateCell(j);
                    if (x % 2 == 0)
                    {
                        //fill background with blue
                        ICellStyle style1 = workbook.CreateCellStyle();
                        style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
                        style1.FillPattern         = FillPattern.SolidForeground;
                        cell.CellStyle             = style1;
                    }
                    else
                    {
                        //fill background with yellow
                        ICellStyle style1 = workbook.CreateCellStyle();
                        style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index2;
                        style1.FillPattern         = FillPattern.SolidForeground;
                        cell.CellStyle             = style1;
                    }
                    x++;
                }
            }
            FileStream sw = File.Create("asd");

            workbook.Write(sw);
            sw.Close();
        }
예제 #20
0
        public static void testc()
        {
            //1.创建EXCEL中的Workbook
            IWorkbook myworkbook = new XSSFWorkbook();

            //2.创建Workbook中的Sheet
            ISheet mysheet = myworkbook.CreateSheet("sheet1");

            mysheet.SetColumnWidth(0, 40 * 256);

            //3.创建Row中的Cell并赋值
            IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue("130925199662080044");
            IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue("" + DateTime.Now + "");

            //4.创建CellStyle与DataFormat并加载格式样式
            IDataFormat dataformat = myworkbook.CreateDataFormat();

            //【Tips】
            // 1.使用@ 或 text 都可以
            // 2.再也不用为身份证号发愁了

            ICellStyle style0 = myworkbook.CreateCellStyle();

            style0.DataFormat = dataformat.GetFormat("@");

            ICellStyle style1 = myworkbook.CreateCellStyle();

            style1.DataFormat = dataformat.GetFormat("text");

            //5.将CellStyle应用于具体单元格
            row0.GetCell(0).CellStyle = style0;
            row1.GetCell(0).CellStyle = style1;

            //6.保存
            FileStream file = new FileStream(@"c:\myworkbook9.xlsx", FileMode.Create);

            myworkbook.Write(file);
            file.Close();
        }
예제 #21
0
        public static ICellStyle ColorMainStoreJP(XSSFWorkbook templateWorkbook)
        {
            ICellStyle styleColor = templateWorkbook.CreateCellStyle();

            styleColor.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index;
            styleColor.FillBackgroundColor = IndexedColors.Grey80Percent.Index;
            styleColor.FillPattern         = FillPattern.SolidForeground;
            styleColor.BorderBottom        = BorderStyle.Thin;
            styleColor.BorderLeft          = BorderStyle.Thin;
            styleColor.BorderRight         = BorderStyle.Thin;
            styleColor.BorderTop           = BorderStyle.Thin;
            return(styleColor);
        }
예제 #22
0
        public void TestBug52348()
        {
            XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("52348.xlsx");
            StylesTable  st       = workbook.GetStylesSource();

            Assert.AreEqual(0, st.StyleXfsSize);

            XSSFCellStyle style = workbook.CreateCellStyle() as XSSFCellStyle; // no exception at this point

            Assert.IsNull(style.GetStyleXf());

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(workbook));
        }
예제 #23
0
        static void Main(string[] args)
        {
            var newFile = @"newbook.core.xlsx";

            using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook();

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

                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, very long content, very long content, very long content, very long content");
                sheet1.AutoSizeColumn(0);
                rowIndex++;


                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);
            }
        }
예제 #24
0
        public XlsxResultsWriter(string resultsXlsxFileName)
        {
            this.resultsXlsxFileName = resultsXlsxFileName;
            this.wb        = new XSSFWorkbook();
            this.workSheet = wb.CreateSheet();

            this.cellStyleComment          = wb.CreateCellStyle();
            this.cellStyleComment.WrapText = true;

            this.cellStyleFailure = wb.CreateCellStyle();
            this.cellStyleFailure.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightOrange.Index;
            this.cellStyleFailure.FillPattern         = FillPattern.SolidForeground;
            this.cellStyleFailure.WrapText            = true;

            this.cellStyleInconclusive = wb.CreateCellStyle();
            this.cellStyleInconclusive.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index;
            this.cellStyleInconclusive.FillPattern         = FillPattern.SolidForeground;
            this.cellStyleInconclusive.WrapText            = true;

            this.headerRow = workSheet.CreateRow(0);
            headerRow.CreateCell(0, CellType.String).SetCellValueWithSanitize("Student ID");
        }
예제 #25
0
        private ICellStyle getStyleFromColor(Color color, XSSFWorkbook wb)
        {
            short col = (short)ColorTranslator.ToOle(color);

            if (!colorMap.ContainsKey(col))
            {
                ICellStyle s = wb.CreateCellStyle();
                s.FillForegroundColor = col;
                s.FillPattern         = FillPattern.SolidForeground;
                colorMap.Add(col, s);
            }
            return(colorMap[col]);
        }
예제 #26
0
        public void TestGetFillForegroundColor()
        {
            XSSFWorkbook wb     = new XSSFWorkbook();
            StylesTable  styles = wb.GetStylesSource();

            Assert.AreEqual(1, wb.NumCellStyles);
            Assert.AreEqual(2, styles.GetFills().Count);

            XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);

            Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
            Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
            Assert.AreEqual(FillPattern.NoFill, defaultStyle.FillPattern);

            XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();

            customStyle.FillPattern = (FillPattern.SolidForeground);
            Assert.AreEqual(FillPattern.SolidForeground, customStyle.FillPattern);
            Assert.AreEqual(3, styles.GetFills().Count);

            customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
            Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
            Assert.AreEqual(4, styles.GetFills().Count);

            for (int i = 0; i < 3; i++)
            {
                XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();

                style.FillPattern = (FillPattern.SolidForeground);
                Assert.AreEqual(FillPattern.SolidForeground, style.FillPattern);
                Assert.AreEqual(4, styles.GetFills().Count);

                style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
                Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
                Assert.AreEqual(4, styles.GetFills().Count);
            }

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
        }
예제 #27
0
        public static bool AppendExcel(DataTable dt, string fileName)
        {
            bool       result = true;
            FileStream fs     = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读
            //POIFSFileSystem ps = new POIFSFileSystem(fs);
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet    sheet    = workbook.GetSheetAt(0);                                                                  //获取工作表

            FileStream fsAppend         = new FileStream(fileName, FileMode.Open, FileAccess.Write, FileShare.ReadWrite); //读
            var        lastRow          = sheet.LastRowNum;
            var        _doubleCellStyle = workbook.CreateCellStyle();

            _doubleCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row     = sheet.CreateRow((lastRow + i + 1));
                var  dataRow = dt.Rows[i];
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var    cellValue = dataRow[j];
                    string value     = string.Empty;
                    if (cellValue is decimal)
                    {
                        var temp = Convert.ToDouble(dataRow[j]);
                        // value = temp.ToString("#0.##");
                        var cell = row.CreateCell(j);
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(temp);
                        //transfer
                        row.Cells[j].CellStyle = _doubleCellStyle;
                    }
                    else if (cellValue is DateTime)
                    {
                        value = dataRow[j] == null ? "" : Convert.ToDateTime(dataRow[j]).ToString("yyyy-MM-dd");
                        row.CreateCell(j).SetCellValue(value);
                    }
                    else
                    {
                        value = dataRow[j] == null ? "" : dataRow[j].ToString();
                        row.CreateCell(j).SetCellValue(value);
                    }
                }
            }

            //fs.Write();
            fsAppend.Flush();
            workbook.Write(fsAppend);//写入文件
            workbook = null;
            fsAppend.Close();
            return(result);
        }
예제 #28
0
        /// <summary>
        /// 导出中奖的名单
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button1_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            //设置文件标题
            saveFileDialog.Title = "导出Excel文件";
            //设置文件类型
            saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
            //设置默认文件类型显示顺序
            saveFileDialog.FilterIndex = 1;
            //是否自动在文件名中添加扩展名
            saveFileDialog.AddExtension = true;
            //是否记忆上次打开的目录
            saveFileDialog.RestoreDirectory = true;
            //设置默认文件名
            saveFileDialog.FileName = "中奖名单表.xlsx";
            //按下确定选择的按钮
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                string       localFilePath = saveFileDialog.FileName.ToString();
                XSSFWorkbook excelBook     = new XSSFWorkbook();
                ICellStyle   style         = excelBook.CreateCellStyle();
                //创建Excel工作表 Sheet=故障码信息
                ISheet sheet1 = excelBook.CreateSheet("某某");
                IRow   row1   = sheet1.CreateRow(0);
                //给标题的每一个单元格赋值
                row1.CreateCell(0).SetCellValue("编号");   //0
                row1.CreateCell(1).SetCellValue("奖项");   //0
                row1.CreateCell(2).SetCellValue("中奖人名"); //0
                for (int i = 0; i < list_win.Count; i++)
                {
                    //sheet1.CreateRow(i).
                    //创建行
                    IRow rowTemp = sheet1.CreateRow(i + 1);

                    //故障码DTC
                    rowTemp.CreateCell(0).SetCellValue(list_win[i].Id);
                    rowTemp.CreateCell(1).SetCellValue(list_win[i].Awards);
                    rowTemp.CreateCell(2).SetCellValue(list_win[i].UserName);
                }
                using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
                {
                    using (MemoryStream stream = new MemoryStream())
                    {
                        excelBook.Write(stream);
                        var buf = stream.ToArray();
                        fs.Write(buf, 0, buf.Length);
                    }
                }
            }
        }
예제 #29
0
        /// <summary>
        /// Render DataTable to NPOI Excel 2003 MemoryStream
        /// NOTE:  Limitation of 65,536 rows suppored by XLS
        /// </summary>
        /// <param name="sourceTable">Source DataTable</param>
        /// <returns>MemoryStream containing NPOI Excel workbook</returns>
        public static Stream RenderDataTableToExcelXSSF(DataTable sourceTable)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            MemoryStream memoryStream = new MemoryStream();
            // By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
            XSSFSheet sheet     = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
            XSSFRow   headerRow = (XSSFRow)sheet.CreateRow(0);

            // Header Row
            foreach (DataColumn column in sourceTable.Columns)
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }

            // Detail Rows
            int rowIndex = 1;

            // 建立儲存格樣式。
            XSSFCellStyle style1 = (XSSFCellStyle)xssfworkbook.CreateCellStyle();//workbook.CreateCellStyle();

            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
            style1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
            style1.FillPattern         = NPOI.SS.UserModel.FillPattern.SolidForeground;//HSSFCellStyle.SOLID_FOREGROUND;

            XSSFFont f = (XSSFFont)xssfworkbook.CreateFont();

            f.Color    = NPOI.HSSF.Util.HSSFColor.Red.Index;
            f.FontName = "宋体";

            foreach (DataRow row in sourceTable.Rows)
            {
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    //HSSFCell cell1 = dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());

                    XSSFCell cell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
                    cell.CellStyle = style1;
                    cell.CellStyle.SetFont(f);
                    cell.SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }

            xssfworkbook.Write(memoryStream);
            memoryStream.Flush();
            memoryStream.Position = 0;
            return(memoryStream);
        }
예제 #30
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="savePath"></param>
        /// <param name="mbPath"></param>
        /// <param name="dataList"></param>
        /// <returns></returns>
        public static bool ToSingleMbExcel(string savePath, string mbPath, List <SingleMb> dataList)
        {
            try
            {
                IWorkbook workbook;

                try
                {
                    using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
                    {
                        workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003
                    }
                }
                catch (Exception ex)
                {
                    using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
                    {
                        workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007
                    }
                }

                ISheet     sheet     = workbook.GetSheetAt(0);
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.WrapText = true;//换行
                foreach (SingleMb item in dataList)
                {
                    IRow row = sheet.GetRow(item.rowIndex - 1);
                    if (row == null)
                    {
                        row = sheet.CreateRow(item.rowIndex - 1);
                    }

                    ICell cell = row.GetCell(item.cellIndex - 1, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                    //cell.CellStyle = cellStyle;
                    cell.SetCellValue(item.value);                                                        //循环往第二行的单元格中添加数据
                }

                using (MemoryStream ms = new MemoryStream())
                {
                    using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fs);
                    }
                }
            }
            catch (Exception)
            {
                return(false);
            }
            return(true);
        }