Exemple #1
1
        private static HSSFWorkbook CreateWBA()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet("Sheet1");
            ISheet sheet2 = wb.CreateSheet("Sheet2");
            ISheet sheet3 = wb.CreateSheet("John's sales");

            CreateDataRow(sheet1, 0, 11, 12, 13, 14);
            CreateDataRow(sheet1, 1, 21, 22, 23, 24);
            CreateDataRow(sheet1, 2, 31, 32, 33, 34);

            CreateDataRow(sheet2, 0, 50, 55, 60, 65);
            CreateDataRow(sheet2, 1, 51, 56, 61, 66);
            CreateDataRow(sheet2, 2, 52, 57, 62, 67);

            CreateDataRow(sheet3, 0, 30, 31, 32);
            CreateDataRow(sheet3, 1, 33, 34, 35);

            IName name1 = wb.CreateName();
            name1.NameName = ("sales1");
            name1.RefersToFormula = ("Sheet1!A1:D1");

            IName name2 = wb.CreateName();
            name2.NameName = ("sales2");
            name2.RefersToFormula = ("Sheet2!B1:C3");

            IRow row = sheet1.CreateRow(3);
            row.CreateCell(0).SetCellValue("sales1");  //A4
            row.CreateCell(1).SetCellValue("sales2");  //B4

            return wb;
        }
Exemple #2
0
        static void Main(string[] args)
        {
            IWorkbook workbook = new HSSFWorkbook();

            ISheet s1=workbook.CreateSheet("Sheet1");
            //set A2
            s1.CreateRow(1).CreateCell(0).SetCellValue(-5);
            //set B2
            s1.GetRow(1).CreateCell(1).SetCellValue(1111);
            //set C2
            s1.GetRow(1).CreateCell(2).SetCellValue(7.623);
            //set A3
            s1.CreateRow(2).CreateCell(0).SetCellValue(2.2);

            //set A4=A2+A3
            s1.CreateRow(3).CreateCell(0).CellFormula = "A2+A3";
            //set D2=SUM(A2:C2);
            s1.GetRow(1).CreateCell(3).CellFormula = "SUM(A2:C2)";
            //set A5=cos(5)+sin(10)
            s1.CreateRow(4).CreateCell(0).CellFormula="cos(5)+sin(10)";


            //create another sheet
            ISheet s2 = workbook.CreateSheet("Sheet2");
            //set cross-sheet reference
            s2.CreateRow(0).CreateCell(0).CellFormula = "Sheet1!A2+Sheet1!A3";
            IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(workbook);
            var cell = e.Evaluate(cell);
            WriteToFile();
        }
Exemple #3
0
        static void Main(string[] args)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;

            //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
            //So we insert three sheet just like what Excel does
            hssfworkbook.CreateSheet("Sheet1");
            hssfworkbook.CreateSheet("Sheet2");
            hssfworkbook.CreateSheet("Sheet3");
            hssfworkbook.CreateSheet("Sheet4");

            ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
            ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;

            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
        }
Exemple #4
0
        public static MemoryStream CreateExcel2003StreamMulti(List <ExcelDataWapperEntity> dataentity, bool inOneSheet)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            int tick = 1;

            if (inOneSheet)
            {
                NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
                for (int i = 0; i < dataentity.Count; i++)
                {
                    int datastartrow = 0;
                    if (i > 0)
                    {
                        datastartrow = dataentity[i - 1].DataSource.Rows.Count + (string.IsNullOrEmpty(dataentity[i - 1].DataSourceDesc) ? 2 : 3);
                    }
                    WriteData(ref sheet1, dataentity[i].DataSource, dataentity[i].ColumnInfo, dataentity[i].DataSourceDesc, datastartrow);
                }
            }
            else
            {
                foreach (ExcelDataWapperEntity w in dataentity)
                {
                    NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(w.DataSourceTitle ?? string.Format("Sheet{0}", tick));
                    WriteData(ref sheet1, w.DataSource, w.ColumnInfo, w.DataSourceDesc);
                }
            }
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            return(ms);
        }
        public void ProcessRequest(HttpContext context)
        {
            string projectName = context.Request["pname"].Trim();

            HSSFWorkbook workbook = new HSSFWorkbook();

            HSSFSheet projectSheet = workbook.CreateSheet("项目各报销项汇总");
            HSSFSheet projectReimSummarySheet = workbook.CreateSheet("项目报销项金额汇总");
            HSSFSheet projectSummarySheet = workbook.CreateSheet("项目报销总金额汇总");

            List<ProjectCategory> queryCategoryList = new List<ProjectCategory>();

            DalOperationAboutReimItem dalReimItem = new DalOperationAboutReimItem();

            List<ReimItem> reimItemList;
            if (!string.IsNullOrWhiteSpace(projectName))
            {
                reimItemList = dalReimItem.GetReimItems(projectName.Trim(), 0, 0);
            }
            else
            {
                reimItemList = dalReimItem.GetAllDistinctReimItems();
            }

            List<ReimItem> reimItemSummaryList = dalReimItem.GetReimItemSummaryValues(projectName, 0, 0, null);

            List<ReimItem> projectReimSummaryList = dalReimItem.GetProjectReimItems(projectName, 0);

            SetProjectReimSheet(projectSheet, reimItemList);

            SetProjectReimSummarySheet(projectReimSummarySheet, reimItemSummaryList);
            SetProjectSummarySheet(projectSummarySheet, projectReimSummaryList);

            string fileName = "项目报销项汇总";

            System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(fileName + ".xls"), System.IO.FileMode.Create);
            workbook.Write(file);
            file.Dispose();

            ////插入值
            FileInfo DownloadFile = new FileInfo(context.Server.MapPath(fileName + ".xls"));

            context.Response.Clear();
            context.Response.ClearHeaders();
            context.Response.Buffer = false;
            Encoding code = Encoding.GetEncoding("gb2312");
            context.Response.ContentEncoding = Encoding.UTF8;
            context.Response.HeaderEncoding = code;//这句很重要
            context.Response.ContentType = "application/octet-stream";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            context.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
            context.Response.WriteFile(DownloadFile.FullName);

            if (File.Exists(context.Server.MapPath(fileName + ".xls")))
            {
                File.Delete(context.Server.MapPath(fileName + ".xls"));
            }
            context.Response.Flush();
        }
Exemple #6
0
        public void NpoiExcel(DataTable dt, DataTable dt2, string title)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("物料汇总");
            NPOI.SS.UserModel.ISheet         sheet2 = book.CreateSheet("类别汇总");

            NPOI.SS.UserModel.IRow headerrow1 = sheet1.CreateRow(0);
            NPOI.SS.UserModel.IRow headerrow2 = sheet2.CreateRow(0);
            ICellStyle             style      = book.CreateCellStyle();

            style.Alignment         = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;


            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = headerrow1.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            for (int I = 0; I <= dt.Rows.Count - 1; I++)
            {
                HSSFRow row2 = (HSSFRow)sheet1.CreateRow(I + 1);
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    string DgvValue = dt.Rows[I][j].ToString();
                    row2.CreateCell(j).SetCellValue(DgvValue);
                    sheet1.SetColumnWidth(j, 20 * 150);
                }
            }
            for (int i = 0; i < dt2.Columns.Count; i++)
            {
                ICell cell = headerrow2.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt2.Columns[i].ColumnName);
            }
            for (int I = 0; I <= dt2.Rows.Count - 1; I++)
            {
                HSSFRow row2 = (HSSFRow)sheet2.CreateRow(I + 1);
                for (int j = 0; j <= dt2.Columns.Count - 1; j++)
                {
                    string DgvValue = dt2.Rows[I][j].ToString();
                    row2.CreateCell(j).SetCellValue(DgvValue);
                    sheet2.SetColumnWidth(j, 20 * 150);
                }
            }
            MemoryStream ms = new MemoryStream();

            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemple #7
0
 public void TestSetRepeatingRowsAndColumnsBug29747()
 {
     HSSFWorkbook wb = new HSSFWorkbook();
     wb.CreateSheet();
     wb.CreateSheet();
     HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet();
     sheet2.RepeatingRows = (CellRangeAddress.ValueOf("1:2"));
     NameRecord nameRecord = wb.Workbook.GetNameRecord(0);
     Assert.AreEqual(3, nameRecord.SheetNumber);
 }
Exemple #8
0
 public void TestSetRepeatingRowsAndColumns()
 {
     // Test bug 29747
     HSSFWorkbook b = new HSSFWorkbook();
     b.CreateSheet();
     b.CreateSheet();
     b.CreateSheet();
     b.SetRepeatingRowsAndColumns(2, 0, 1, -1, -1);
     NameRecord nameRecord = b.Workbook.GetNameRecord(0);
     Assert.AreEqual(3, nameRecord.SheetNumber);
 }
 static void Main(string[] args)
 {
     hssfworkbook = new HSSFWorkbook();
     ISheet sheet1 = hssfworkbook.CreateSheet("new sheet");
     ISheet sheet2 = hssfworkbook.CreateSheet("second sheet");
     //Create a split with the lower left side being the active quadrant
     sheet2.CreateSplitPane(2000, 2000, 0, 0, PanePosition.LowerLeft);
     //Write the stream data of workbook to the root directory
     FileStream file = new FileStream(@"output-Split.xls", FileMode.Create);
     hssfworkbook.Write(file);
     file.Close();
 }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <returns></returns>
        public void ExportData()
        {
            string       ProductBZ = System.Web.HttpContext.Current.Request.Params["ProductBZ"];
            HSSFWorkbook book      = new NPOI.HSSF.UserModel.HSSFWorkbook();
            ISheet       sheet     = book.CreateSheet("sheet1");
            IRow         row1      = sheet.CreateRow(0);

            row1.CreateCell(0).SetCellValue("账号");
            row1.CreateCell(1).SetCellValue("密码");
            row1.CreateCell(2).SetCellValue("姓名");

            Dictionary <string, object> dicWhere = new Dictionary <string, object>();

            new CreateCriteria(dicWhere).Add(ORMRestrictions <string> .Eq("ProductBZ", ProductBZ));
            IList <QTProductUserModel> qTProductUsers = QTProductUserService.ServiceHelper.LoadWithPageInfinity("GQT.QT.ALLProductUsers", dicWhere).Results;

            //List<BudgetAccountsModel> models = budgetAccounts.ToList();
            for (int i = 0; i < qTProductUsers.Count; i++)
            {
                QTProductUserModel model = qTProductUsers[i];
                IRow row = sheet.CreateRow(i + 1);
                row.CreateCell(0).SetCellValue(model.ProductUserCode);
                row.CreateCell(1).SetCellValue(model.ProductUserPwd);
                row.CreateCell(2).SetCellValue(model.ProductUserName);
            }
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemple #11
0
        //excel导出,最多只能导出65535条记录,没有考虑分sheet页,后续如果数据量较多再扩充
        public static void writeExcel(DataTable dt, ExcelInfo ei, String[] colTitle, int[] colWidth)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ei.sheetName);

            HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0);
            for (int j = 0; j < colTitle.Length;j++)
            {
                //设置列宽
                sheet.SetColumnWidth(j, colWidth[j] * 255);
                rowtitle.CreateCell(j).SetCellValue(colTitle[j]);
            }

            int i = 1;
            foreach (DataRow row in dt.Rows)
            {
                HSSFRow newrow = (HSSFRow)sheet.CreateRow(i);
                int col = 0;
                for (; col < dt.Columns.Count; col++)
                    newrow.CreateCell(col).SetCellValue(Convert.ToString(row[col]));
                i++;
            }
            try
            {
                using (Stream stream = File.Open(ei.filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook.Write(stream);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
Exemple #12
0
        public void TestPolygonPoints()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;

            HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
            polygon.SetPolygonDrawArea(100, 100);
            polygon.SetPoints(new int[] { 0, 90, 50, 90 }, new int[] { 5, 5, 44, 88 });

            PolygonShape polygonShape = HSSFTestModelHelper.CreatePolygonShape(0, polygon);

            EscherArrayProperty verticesProp1 = polygon.GetOptRecord().Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
            EscherArrayProperty verticesProp2 = ((EscherOptRecord)polygonShape.SpContainer.GetChildById(EscherOptRecord.RECORD_ID))
                    .Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;

            Assert.AreEqual(verticesProp1.NumberOfElementsInArray, verticesProp2.NumberOfElementsInArray);
            Assert.AreEqual(verticesProp1.ToXml(""), verticesProp2.ToXml(""));

            polygon.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
            Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 1, 2, 3 }));
            Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 4, 5, 6 }));

            polygonShape = HSSFTestModelHelper.CreatePolygonShape(0, polygon);
            verticesProp1 = polygon.GetOptRecord().Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
            verticesProp2 = ((EscherOptRecord)polygonShape.SpContainer.GetChildById(EscherOptRecord.RECORD_ID))
                    .Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;

            Assert.AreEqual(verticesProp1.NumberOfElementsInArray, verticesProp2.NumberOfElementsInArray);
            Assert.AreEqual(verticesProp1.ToXml(""), verticesProp2.ToXml(""));
        }
        public override void Write(Stream outStream)
        {
            Workbook workbook = new HSSFWorkbook();

            IEnumerable<User> users = User.SelectAll();
            foreach (User user in users)
            {
                IEnumerable<TimeBlock> timeBlocks = TimeBlock.SelectByUserAndDateRange(user.UserId, FromDate, ToDate);
                if (timeBlocks.Count() > 0)
                {
                    Sheet sheet = workbook.CreateSheet(user.Name);
                    Row header = sheet.CreateRow(0);
                    header.CreateCell(0).SetCellValue("Resource");
                    header.CreateCell(1).SetCellValue("Date");
                    header.CreateCell(2).SetCellValue("Client");
                    header.CreateCell(3).SetCellValue("Work Package");
                    header.CreateCell(4).SetCellValue("Hours");

                    int i = 1;
                    foreach (TimeBlock timeBlock in timeBlocks)
                    {
                        Row row = sheet.CreateRow(i ++);
                        row.CreateCell(0).SetCellValue(timeBlock.User.Name);
                        row.CreateCell(1).SetCellValue(timeBlock.Date.ToString("dd/MM/yy"));
                        row.CreateCell(2).SetCellValue(timeBlock.Job.Client.Name);
                        row.CreateCell(3).SetCellValue(timeBlock.JobId);
                        row.CreateCell(4).SetCellValue(Convert.ToDouble(timeBlock.Time));
                    }
                }
            }

            workbook.Write(outStream);
        }
Exemple #14
0
        public static Stream RenderDataTableToExcel(string[] headers, DataTable SourceTable)
        {
            MemoryStream ms = new MemoryStream();

            NPOI.HSSF.UserModel.HSSFWorkbook workbook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet     = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow           headerRow = sheet.CreateRow(0);
            for (int i = 0; i < headers.Length; i++)
            {
                headerRow.CreateCell(i).SetCellValue(headers[i]);
            }
            int rowIndex = 1;

            foreach (DataRow row in SourceTable.Rows)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                dataRow.CreateCell(0).SetCellValue(row["ItemName"].ToString());
                dataRow.CreateCell(1).SetCellValue(row["Count"].ToString());
                dataRow.CreateCell(2).SetCellValue(row["Percent"].ToString());
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet       = null;
            headerRow   = null;
            workbook    = null;
            return(ms);
        }
Exemple #15
0
        public byte[] GetExcelBufferFromDataTable(DataTable dt)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            Sheet sheet = hssfworkbook.CreateSheet("Sheet1");

            sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 1; i <= dt.Rows.Count; i++)
            {
                sheet.CreateRow(i);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i - 1][dt.Columns[j].ColumnName].ToString());
                }
            }

            MemoryStream memstream = new MemoryStream();
            try
            {
                hssfworkbook.Write(memstream);

                byte[] buffer = memstream.GetBuffer();

                return buffer;
            }
            finally
            {
                memstream.Close();
            }
        }
Exemple #16
0
        public void Test44916()
        {

            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();

            // 1. Create drawing patriarch
            IDrawing patr = sheet.CreateDrawingPatriarch();

            // 2. Try to re-get the patriarch
            IDrawing existingPatr;
            try
            {
                existingPatr = sheet.DrawingPatriarch;
            }
            catch (NullReferenceException)
            {
                throw new AssertionException("Identified bug 44916");
            }

            // 3. Use patriarch
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 245, (short)1, 1, (short)1, 2);
            anchor.AnchorType = (AnchorType)(3);
            byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png");
            int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
            patr.CreatePicture(anchor, idx1);

            // 4. Try to re-use patriarch later
            existingPatr = sheet.DrawingPatriarch;
            Assert.IsNotNull(existingPatr);
        }
Exemple #17
0
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <returns></returns>
        public void ExportData()
        {
            HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            ISheet       sheet = book.CreateSheet("sheet1");
            IRow         row1  = sheet.CreateRow(0);

            row1.CreateCell(0).SetCellValue("科目代码");
            row1.CreateCell(1).SetCellValue("科目名称");
            row1.CreateCell(2).SetCellValue("科目类别");

            IList <BudgetAccountsModel> budgetAccounts = BudgetAccountsService.ExportData();

            //List<BudgetAccountsModel> models = budgetAccounts.ToList();
            for (int i = 0; i < budgetAccounts.Count; i++)
            {
                BudgetAccountsModel model = budgetAccounts[i];
                IRow row = sheet.CreateRow(i + 1);
                row.CreateCell(0).SetCellValue(model.KMDM);
                row.CreateCell(1).SetCellValue(model.KMMC);
                row.CreateCell(2).SetCellValue(model.KMLB == "0" ? "收入" : "支出");
            }
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
        public static HSSFWorkbook CreateExecl(List <string> title, List <AppUserPerson> list)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            //row1.RowStyle.FillBackgroundColor = "";
            int i = 0;

            foreach (var item in title)
            {
                row1.CreateCell(i).SetCellValue(item);
                i++;
            }
            //将数据逐步写入sheet1各个行
            for (int j = 0; j < list.Count; j++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1);
                rowtemp.CreateCell(0).SetCellValue(list[j].adnm);
                rowtemp.CreateCell(1).SetCellValue(list[j].userName);
                rowtemp.CreateCell(2).SetCellValue(list[j].phone);
            }
            return(book);
        }
Exemple #19
0
 public static MemoryStream ExportExcel(DataTable dt)
 {
     NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
     //添加一个sheet
     NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
     //给sheet1添加第一行的头部标题
     NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
     //row1.RowStyle.FillBackgroundColor = "";
     for (int i = 0; i < dt.Columns.Count; i++)
     {
         row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
     }
     //将数据逐步写入sheet1各个行
     for (int i = 0; i < dt.Rows.Count; i++)
     {
         NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
         for (int j = 0; j < dt.Columns.Count; j++)
         {
             rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
         }
     }
     // 写入到客户端
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     ms.Seek(0, SeekOrigin.Begin);
     return(ms);
 }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <returns></returns>
        public void ExportData()
        {
            //string ProductBZ = System.Web.HttpContext.Current.Request.Params["ProductBZ"];
            HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            ISheet       sheet = book.CreateSheet("sheet1");
            IRow         row1  = sheet.CreateRow(0);

            row1.CreateCell(0).SetCellValue("代码");
            row1.CreateCell(1).SetCellValue("名称");
            //row1.CreateCell(2).SetCellValue("备注");

            Dictionary <string, object> dicWhere = new Dictionary <string, object>();

            new CreateCriteria(dicWhere)
            .Add(ORMRestrictions <System.Int64> .NotEq("PhId", 0));
            IList <ProcurementCatalogModel> procurementCatalogs = ProcurementCatalogService.Find(dicWhere).Data;

            for (int i = 0; i < procurementCatalogs.Count; i++)
            {
                ProcurementCatalogModel model = procurementCatalogs[i];
                IRow row = sheet.CreateRow(i + 1);
                row.CreateCell(0).SetCellValue(model.FCode);
                row.CreateCell(1).SetCellValue(model.FName);
                //row.CreateCell(2).SetCellValue(model.FRemark);
            }
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
        public void NpoiExcel(DataTable dt, string title, string s)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("Sheet1");

            NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
            ICellStyle             style     = book.CreateCellStyle();

            style.Alignment         = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;

            string[] ss = s.Split(',');
            for (int i = 0; i < ss.Length; i++)
            {
                ICell cell = headerrow.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(ss[i]);
            }
            for (int I = 0; I <= dt.Rows.Count - 1; I++)
            {
                HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    //double a = 0;
                    //string b = "";
                    //try
                    //{
                    //    a = Convert.ToDouble(dt.Rows[I][j].ToString());
                    //    row2.CreateCell(j).SetCellValue(a);
                    //    sheet.SetColumnWidth(j, 20 * 150);
                    //}
                    //catch
                    //{
                    //    b = dt.Rows[I][j].ToString();
                    //    row2.CreateCell(j).SetCellValue(b);
                    //    sheet.SetColumnWidth(j, 20 * 150);
                    //}
                    string DgvValue = dt.Rows[I][j].ToString();
                    if (j == 8 || j == 9)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue));
                    }
                    else
                    {
                        row2.CreateCell(j).SetCellValue(DgvValue);
                    }

                    sheet.SetColumnWidth(j, 20 * 150);
                }
            }
            MemoryStream ms = new MemoryStream();

            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemple #22
0
        public static void WriteExcel(DataTable dt, string filePath)
        {
            if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(dt.TableName);

                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                }
                // 写入到客户端
                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }
        }
Exemple #23
0
        public void TestEvaluateInSheetExample2()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);

            sheet.CreateRow(1).CreateCell(0).SetCellValue(0.08d);
            sheet.CreateRow(2).CreateCell(0).SetCellValue(-40000d);
            sheet.CreateRow(3).CreateCell(0).SetCellValue(8000d);
            sheet.CreateRow(4).CreateCell(0).SetCellValue(9200d);
            sheet.CreateRow(5).CreateCell(0).SetCellValue(10000d);
            sheet.CreateRow(6).CreateCell(0).SetCellValue(12000d);
            sheet.CreateRow(7).CreateCell(0).SetCellValue(14500d);

            ICell cell = row.CreateCell(8);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            // Enumeration
            cell.CellFormula = ("NPV(A2, A4,A5,A6,A7,A8)+A3");
            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            double res = cell.NumericCellValue;
            Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);

            // Range
            cell.CellFormula = ("NPV(A2, A4:A8)+A3");

            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            res = cell.NumericCellValue;
            Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
        }
        public static void WriteExcel(List <DataTable> dts, string filePath)
        {
            try
            {
                if (!string.IsNullOrEmpty(filePath)) // && null != dt && dt.Rows.Count > 0)
                {
                    NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
                    NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("sheet1");
                    createColumnName(book, sheet);

                    createCells(book, sheet, dts[0], 0, 1);
                    createCells(book, sheet, dts[1], 3, 4);
                    createCells(book, sheet, dts[2], 6, 7);
                    createCells(book, sheet, dts[3], 9, 10);
                    createCells(book, sheet, dts[4], 12, 14);

                    // 写入到客户端
                    using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                    {
                        book.Write(ms);
                        using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                        {
                            byte[] data = ms.ToArray();
                            fs.Write(data, 0, data.Length);
                            fs.Flush();
                        }
                        book = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Exemple #25
0
        public void TestCellWithMerges()
        {
            IWorkbook wb = new HSSFWorkbook();
            ISheet s = wb.CreateSheet();

            // Create some test data
            IRow r2 = s.CreateRow(1);
            r2.CreateCell(0).SetCellValue(10);
            r2.CreateCell(1).SetCellValue(11);
            IRow r3 = s.CreateRow(2);
            r3.CreateCell(0).SetCellValue(20);
            r3.CreateCell(1).SetCellValue(21);

            s.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0));
            s.AddMergedRegion(new CellRangeAddress(2, 2, 1, 4));

            // With a cell that isn't defined, we'll Get null
            Assert.AreEqual(null, SheetUtil.GetCellWithMerges(s, 0, 0));

            // With a cell that's not in a merged region, we'll Get that
            Assert.AreEqual(10.0, SheetUtil.GetCellWithMerges(s, 1, 0).NumericCellValue);
            Assert.AreEqual(11.0, SheetUtil.GetCellWithMerges(s, 1, 1).NumericCellValue);

            // With a cell that's the primary one of a merged region, we Get that cell
            Assert.AreEqual(20.0, SheetUtil.GetCellWithMerges(s, 2, 0).NumericCellValue);
            Assert.AreEqual(21, SheetUtil.GetCellWithMerges(s, 2, 1).NumericCellValue);

            // With a cell elsewhere in the merged region, Get top-left
            Assert.AreEqual(20.0, SheetUtil.GetCellWithMerges(s, 3, 0).NumericCellValue);
            Assert.AreEqual(21.0, SheetUtil.GetCellWithMerges(s, 2, 2).NumericCellValue);
            Assert.AreEqual(21.0, SheetUtil.GetCellWithMerges(s, 2, 3).NumericCellValue);
            Assert.AreEqual(21.0, SheetUtil.GetCellWithMerges(s, 2, 4).NumericCellValue);
        }
Exemple #26
0
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <returns></returns>
        public void ExportData()
        {
            HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            ISheet       sheet = book.CreateSheet("sheet1");
            IRow         row1  = sheet.CreateRow(0);

            row1.CreateCell(0).SetCellValue("项目库组织代码");
            row1.CreateCell(1).SetCellValue("老G6H组织代码");

            Dictionary <string, object> dicwhere = new Dictionary <string, object>();

            new CreateCriteria(dicwhere)
            .Add(ORMRestrictions <System.Int64> .NotEq("PhId", 0));
            IList <QtOrgDygxModel> qtOrgDygxes = QtOrgDygxService.Find(dicwhere).Data;//数据库的所有数据

            for (int i = 0; i < qtOrgDygxes.Count; i++)
            {
                QtOrgDygxModel model = qtOrgDygxes[i];
                IRow           row   = sheet.CreateRow(i + 1);
                row.CreateCell(0).SetCellValue(model.Xmorg);
                row.CreateCell(1).SetCellValue(model.Oldorg);
            }
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemple #27
0
        public IWorkbook GenerateSheet(DataTable dt, string sheetname)
        {
            IWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet(sheetname);

            IRow row = sheet1.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName == "ID")
                {
                    continue;
                }
                row.CreateCell(i, CellType.STRING).SetCellValue(dt.Columns[i].ColumnName);
            }
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                IRow row1 = sheet1.CreateRow(j+1);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName=="ID")
                    {
                        continue;
                    }
                     row1.CreateCell(i, CellType.STRING).SetCellValue(dt.Rows[j ][i].ToString());

                }
            }

            return hssfworkbook;
        }
        public void TestCreateCFRuleRecord()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
            CFRuleRecord record = CFRuleRecord.Create(sheet, "7");
            TestCFRuleRecord1(record);

            // Serialize
            byte[] SerializedRecord = record.Serialize();

            // Strip header
            byte[] recordData = new byte[SerializedRecord.Length - 4];
            Array.Copy(SerializedRecord, 4, recordData, 0, recordData.Length);

            // DeSerialize
            record = new CFRuleRecord(TestcaseRecordInputStream.Create(CFRuleRecord.sid, recordData));

            // Serialize again
            byte[] output = record.Serialize();

            // Compare
            Assert.AreEqual(recordData.Length + 4, output.Length, "Output size"); //includes sid+recordlength

            for (int i = 0; i < recordData.Length; i++)
            {
                Assert.AreEqual(recordData[i], output[i + 4], "CFRuleRecord doesn't match");
            }
        }
Exemple #29
0
        /// <summary>
        /// 批量导出需要导出的列表
        /// </summary>
        /// <returns></returns>
        public static MemoryStream ExportMemory(DataTable source, List <string> caption)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("Sheet1");
            //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            for (int i = 0; i < caption.Count; i++)
            {
                var element = caption[i];
                row1.CreateCell(i).SetCellValue(element);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < source.Rows.Count; i++)
            {
                var dataRow = source.Rows[i];
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                for (int j = 0; j < caption.Count; j++)
                {
                    var keyValue = caption[j];
                    rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            DateTime dt       = DateTime.Now;
            string   dateTime = dt.ToString("yyMMddHHmmssfff");
            string   fileName = "查询结果" + dateTime + ".xls";

            return(ms);
            //return File(ms, "application/vnd.ms-excel", fileName);
        }
        //数据导出为Excel
        private void btnExport_Click(object sender, EventArgs e)
        {
            string sql = "select CC_AutoId,CC_CustomerName,CC_CellPhone,CC_Landline,CC_BuyDate,CC_CarNum,CC_BracketNum from T_Customers";
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
            {
                if (reader.HasRows)
                {

                    IWorkbook wb = new HSSFWorkbook();
                    ISheet sheet = wb.CreateSheet("customers");
                    int rowIndex = 0;//手动处理行索引
                    while (reader.Read())
                    {
                        int autoId = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        string cellPhone = reader.GetString(2);
                        //数据为空,那么就不能Get..,所以要在这里判断
                        string landLine = reader.IsDBNull(3) ? null : reader.GetString(3);//可为空

                        DateTime BuyDate = reader.GetDateTime(4);
                        string carNum = reader.GetString(5);
                        string bracketNum = reader.GetString(6);
                        IRow row = sheet.CreateRow(rowIndex);
                        rowIndex++;

                        row.CreateCell(0).SetCellValue(autoId);
                        row.CreateCell(1).SetCellValue(name);
                        row.CreateCell(2).SetCellValue(cellPhone);
                        //如果为空或Null,把Excel这个单元格设置为值为空
                        if (string.IsNullOrEmpty(landLine))
                        {
                            row.CreateCell(3).SetCellType(CellType.BLANK);
                        }
                        else
                        {
                            row.CreateCell(3).SetCellValue(landLine);
                        }

                        //============设置日期格式=======
                        //创建单元格样式对象
                        ICellStyle cellStyle = wb.CreateCellStyle();
                        //设置单元格样式对象属性
                        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
                        ICell BuyDateRow = row.CreateCell(4);
                        BuyDateRow.CellStyle = cellStyle;
                        BuyDateRow.SetCellValue(BuyDate);
                        //==============设置完成========

                        row.CreateCell(5).SetCellValue(carNum);
                        row.CreateCell(6).SetCellValue(bracketNum);

                    }
                    using (FileStream fs = File.OpenWrite("Customers.xls"))
                    {
                        wb.Write(fs);
                        MessageBox.Show("ok");
                    }
                }
            }
        }
        //将一个集合中的数据写入到Excel
        private void btnInclude_Click(object sender, EventArgs e)
        {
            //对象初始化器
            List<Person> list = new List<Person>(){
                new Person(){Name="路人甲",Age=18,Mail="*****@*****.**"},
                new Person(){Name="路人乙",Age=28,Mail="*****@*****.**"},
                new Person(){Name="路人丙",Age=38,Mail="*****@*****.**"},
            };

            //创建工作簿
            IWorkbook wb = new HSSFWorkbook();
            //创建工作表
            ISheet sheet = wb.CreateSheet("测试");
            //创建行
            for (int i = 0; i < list.Count();i++ )
            {
                IRow row=sheet.CreateRow(i);
                //创建单元格
                row.CreateCell(0).SetCellValue(list[i].Name);
                row.CreateCell(1).SetCellValue(list[i].Age);
                row.CreateCell(2).SetCellValue(list[i].Mail);
            }
            using (FileStream fs=File.OpenWrite("NewTestExcel.xls"))
            {
                wb.Write(fs);
            }
            MessageBox.Show("Ok");
        }
Exemple #32
0
        public static Workbook CreateWorkBookWithSheet()
        {
            Workbook workbook = new HSSFWorkbook();
            workbook.CreateSheet();

            return workbook;
        }
Exemple #33
0
        public void TestEvaluateMissingArgs()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            ISheet sheet = wb.CreateSheet("Sheet1");
            ICell cell = sheet.CreateRow(0).CreateCell(0);

            cell.CellFormula=("if(true,)");
            fe.ClearAllCachedResultValues();
            CellValue cv;
            try
            {
                cv = fe.Evaluate(cell);
            }
            catch (Exception e)
            {
                Console.Error.WriteLine(e.Message);
                throw new AssertionException("Missing args Evaluation not implemented (bug 43354");
            }
            // MissingArg -> BlankEval -> zero (as formula result)
            Assert.AreEqual(0.0, cv.NumberValue, 0.0);

            // MissingArg -> BlankEval -> empty string (in concatenation)
            cell.CellFormula=("\"abc\"&if(true,)");
            fe.ClearAllCachedResultValues();
            Assert.AreEqual("abc", fe.Evaluate(cell).StringValue);
        }
Exemple #34
0
        public void TestWriteSheetFont()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow r = null;
            //ICell c = null;
            IFont fnt = wb.CreateFont();
            NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();

            fnt.Color=(NPOI.HSSF.Util.HSSFColor.RED.index);
            fnt.Boldweight=(short)FontBoldWeight.BOLD;
            cs.SetFont(fnt);
            for (short rownum = (short)0; rownum < 100; rownum++)
            {
                r = s.CreateRow(rownum);
                r.RowStyle=(cs);
                r.CreateCell(0);
            }
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            SanityChecker sanityChecker = new SanityChecker();
            sanityChecker.CheckHSSFWorkbook(wb);
            Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
            Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
        }
        public void TestSetGetCoordinates()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
            Assert.AreEqual(group.X1, 0);
            Assert.AreEqual(group.Y1, 0);
            Assert.AreEqual(group.X2, 1023);
            Assert.AreEqual(group.Y2, 255);

            group.SetCoordinates(1, 2, 3, 4);

            Assert.AreEqual(group.X1, 1);
            Assert.AreEqual(group.Y1, 2);
            Assert.AreEqual(group.X2, 3);
            Assert.AreEqual(group.Y2, 4);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sh.DrawingPatriarch as HSSFPatriarch;

            group = (HSSFShapeGroup)patriarch.Children[(0)];
            Assert.AreEqual(group.X1, 1);
            Assert.AreEqual(group.Y1, 2);
            Assert.AreEqual(group.X2, 3);
            Assert.AreEqual(group.Y2, 4);
        }
        public void TestAddToExistingFile()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFShapeGroup group1 = patriarch.CreateGroup(new HSSFClientAnchor());
            HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor());

            group1.SetCoordinates(1, 2, 3, 4);
            group2.SetCoordinates(5, 6, 7, 8);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sh.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(patriarch.Children.Count, 2);

            HSSFShapeGroup group3 = patriarch.CreateGroup(new HSSFClientAnchor());
            group3.SetCoordinates(9, 10, 11, 12);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sh.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(patriarch.Children.Count, 3);
        }
Exemple #37
0
        /// <summary>
        /// 生成导会员卡信息的excel文件,并返回导出的url地址
        /// </summary>
        /// <param name="entities">会员卡信息集合</param>
        /// <returns>导出excel的url</returns>
        public string Export <T>(IList <T> entities, string sheetName = "导出数据")
        {
            SetPropertyInfo(typeof(T));
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            _book = book;
            ISheet sheet = book.CreateSheet(sheetName);

            if (OnSheetCreated != null)
            {
                OnSheetCreated(sheet);
            }

            FillHead(sheet);
            for (var i = 0; i < entities.Count(); i++)
            {
                FillRow(entities[i], sheet, i + 1);
            }
            using (var ms = new MemoryStream())
            {
                book.Write(ms);
                var fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
                using (var fs = new FileStream(ExportTempPath(fileName), FileMode.Create))
                {
                    book.Write(fs);
                }
                return(ExportTempUrl(fileName));
            }
        }
Exemple #38
0
 /// <summary>
 /// 服务商结算订单导出
 /// </summary>
 /// <param name="fileName"></param>
 /// <param name="title"></param>
 /// <param name="sheetName"></param>
 /// <param name="dt"></param>
 public static System.IO.MemoryStream ExcelSettlementOrder(string[] fileName, string[] title, string sheetName, DataTable dt)
 {
     NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
     NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetName);
     NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
     for (int i = 0; i < title.Length; i++)
     {
         row.CreateCell(i).SetCellValue(title[i]);
     }
     if (dt != null && dt.Rows.Count > 0)
     {
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
             for (int j = 0; j < fileName.Length; j++)
             {
                 row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
             }
         }
     }
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     //ms.Seek(0, SeekOrigin.Begin);
     ms.Flush();
     ms.Position = 0;//流位置归零
     return(ms);
     //写入到客户端
 }
        public void TestGetAnchorHeightInPoints()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Test");
            HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
            float p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(12.7, p, 0.001);

            sheet.CreateRow(0).HeightInPoints = (14);
            a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(13.945, p, 0.001);

            a = new HSSFClientAnchor(0, 0, 1023, 127, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(6.945, p, 0.001);

            a = new HSSFClientAnchor(0, 126, 1023, 127, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(0.054, p, 0.001);

            a = new HSSFClientAnchor(0, 0, 1023, 0, (short)0, 0, (short)0, 1);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(14.0, p, 0.001);

            sheet.CreateRow(0).HeightInPoints = (12);
            a = new HSSFClientAnchor(0, 127, 1023, 127, (short)0, 0, (short)0, 1);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(12.372, p, 0.001);

        }
Exemple #40
0
        public void TestClean1()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ICell cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            String[] asserts = {
            "aniket\u0007\u0017\u0019", "aniket",
            "\u0011aniket\u0007\u0017\u0010", "aniket",
            "\u0011aniket\u0007\u0017\u007F", "aniket\u007F",
            "\u2116aniket\u2211\uFB5E\u2039", "\u2116aniket\u2211\uFB5E\u2039",
        };

            for (int i = 0; i < asserts.Length; i += 2)
            {
                String formulaText = "CLEAN(\"" + asserts[i] + "\")";
                ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
            }

            asserts = new String[] {
            "CHAR(7)&\"text\"&CHAR(7)", "text",
            "CHAR(7)&\"text\"&CHAR(17)", "text",
            "CHAR(181)&\"text\"&CHAR(190)", "\u00B5text\u00BE",
            "\"text\"&CHAR(160)&\"'\"", "text\u00A0'",
        };
            for (int i = 0; i < asserts.Length; i += 2)
            {
                String formulaText = "CLEAN(" + asserts[i] + ")";
                ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
            }
        }
Exemple #41
0
        public void TestInSpreadSheet()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.CellFormula=("B1%");
            row.CreateCell(1).SetCellValue(50.0);

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            CellValue cv;
            try
            {
                cv = fe.Evaluate(cell);
            }
            catch (SystemException e)
            {
                if (e.InnerException is NullReferenceException)
                {
                    throw new AssertionException("Identified bug 44608");
                }
                // else some other unexpected error
                throw e;
            }
            Assert.AreEqual(CellType.NUMERIC, cv.CellType);
            Assert.AreEqual(0.5, cv.NumberValue, 0.0);
        }
Exemple #42
0
        /**
         * Set up a calculation workbook with input cells nicely segregated on a
         * sheet called "Inputs"
         */
        private static HSSFWorkbook CreateWorkbook()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet("Inputs");
            ISheet sheet2 = wb.CreateSheet("Calculations");
            IRow row;
            row = sheet2.CreateRow(0);
            row.CreateCell(0).CellFormula = ("B1*Inputs!A1-Inputs!B1");
            row.CreateCell(1).SetCellValue(5.0); // Calculations!B1

            // some default input values
            row = sheet1.CreateRow(0);
            row.CreateCell(0).SetCellValue(2.0); // Inputs!A1
            row.CreateCell(1).SetCellValue(3.0); // Inputs!B1
            return wb;
        }
Exemple #43
0
        public void TestEvaluate()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); 
            
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sh = wb.CreateSheet();
            ICell cell1 = sh.CreateRow(0).CreateCell(0);
            cell1.CellFormula = ("MROUND(10, 3)");
            ICell cell2 = sh.CreateRow(0).CreateCell(0);
            cell2.CellFormula = ("MROUND(-10, -3)");
            ICell cell3 = sh.CreateRow(0).CreateCell(0);
            cell3.CellFormula = ("MROUND(1.3, 0.2)");
            ICell cell4 = sh.CreateRow(0).CreateCell(0);
            cell4.CellFormula = ("MROUND(5, -2)");
            ICell cell5 = sh.CreateRow(0).CreateCell(0);
            cell5.CellFormula = ("MROUND(5, 0)");

            double accuracy = 1E-9;

            IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            Assert.AreEqual(9.0, Evaluator.Evaluate(cell1).NumberValue, accuracy, "Rounds 10 to a nearest multiple of 3 (9)");

            Assert.AreEqual(-9.0, Evaluator.Evaluate(cell2).NumberValue, accuracy, "Rounds -10 to a nearest multiple of -3 (-9)");

            Assert.AreEqual(1.4, Evaluator.Evaluate(cell3).NumberValue, accuracy, "Rounds 1.3 to a nearest multiple of 0.2 (1.4)");

            Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, Evaluator.Evaluate(cell4).ErrorValue, "Returns an error, because -2 and 5 have different signs (#NUM!)");

            Assert.AreEqual(0.0, Evaluator.Evaluate(cell5).NumberValue, "Returns 0 because the multiple is 0");
        }
        public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
        {

            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
            wb.SetSheetName(0, "Sheet1");

            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell((short)0);
            cell.CellFormula = ("SUM(A32769:A32770)");

            // put some values in the cells to make the evaluation more interesting
            sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
            sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);

            //HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            NPOI.SS.UserModel.CellValue result;
            try
            {
                result = fe.Evaluate(cell);
            }
            catch (Exception e)
            {
                if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
                {
                    Assert.Fail("Identifed bug 44539");
                }
                throw;
            }
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, result.CellType);
            Assert.AreEqual(42.0, result.NumberValue, 0.0);
        }
Exemple #45
0
        public void TestWriteSheetSimple()  {
        string            filepath = TempFile.GetTempFilePath("TestWriteSheetSimple",
                                                    ".xls");
        FileStream out1  = new FileStream(filepath,FileMode.OpenOrCreate);
        HSSFWorkbook     wb   = new HSSFWorkbook();
        NPOI.SS.UserModel.ISheet        s    = wb.CreateSheet();
        IRow          r    = null;
        ICell         c    = null;

        for (int rownum = 0; rownum < 100; rownum++) {
            r = s.CreateRow(rownum);

            for (int cellnum = 0; cellnum < 50; cellnum += 2) {
                c = r.CreateCell(cellnum);
                c.SetCellValue(rownum * 10000 + cellnum
                               + ((( double ) rownum / 1000)
                                  + (( double ) cellnum / 10000)));
                c = r.CreateCell(cellnum + 1);
                c.SetCellValue(new HSSFRichTextString("TEST"));
            }
        }
        wb.Write(out1);
        out1.Close();
        sanityChecker.CheckHSSFWorkbook(wb);
        Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
        Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
    }
Exemple #46
0
 /// <summary>
 /// 批量导出需要导出的列表
 /// </summary>
 /// <returns></returns>
 public static MemoryStream GetMemoryStream(DataTable source, List <string> caption = null)
 {
     //创建Excel文件的对象
     NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
     NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("Sheet1");
     //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了
     NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
     if (caption == null)
     {
         caption = source.Columns.Cast <DataColumn>().ToList().Select(item => item.Caption).ToList();
     }
     for (int i = 0; i < caption.Count; i++)
     {
         var element = caption[i];
         row1.CreateCell(i).SetCellValue(element);
     }
     //将数据逐步写入sheet1各个行
     for (int i = 0; i < source.Rows.Count; i++)
     {
         var dataRow = source.Rows[i];
         NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
         for (int j = 0; j < caption.Count; j++)
         {
             var keyValue = caption[j];
             rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
         }
     }
     // 写入到客户端
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     ms.Seek(0, SeekOrigin.Begin);
     return(ms);
     //return File(ms, "application/vnd.ms-excel", fileName);
 }
Exemple #47
0
        /// <summary>
        /// Excel导出返回文件路径
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="title"></param>
        /// <param name="sheetName"></param>
        /// <param name="dt"></param>
        /// <param name="FilePath"></param>
        /// <param name="ext"></param>
        /// <returns></returns>
        public static string ExcelFilePath(string[] fileName, string[] title, string sheetName, DataTable dt, string FilePath, string ext)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetName);
            NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
            for (int i = 0; i < title.Length; i++)
            {
                row.CreateCell(i).SetCellValue(title[i]);
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < fileName.Length; j++)
                    {
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
                    }
                }
            }

            if (!Directory.Exists(HttpContext.Current.Server.MapPath(FilePath)))
            {
                Directory.CreateDirectory(HttpContext.Current.Server.MapPath(FilePath));
            }
            FilePath += "/" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000) + Path.GetExtension(ext);
            FileStream fs2 = File.Create(HttpContext.Current.Server.MapPath(FilePath));

            book.Write(fs2);
            fs2.Close();


            return(FilePath);
            //写入到客户端
        }
Exemple #48
0
        /// <summary>
        /// 批量导出本校第一批派位学生
        /// </summary>
        /// <returns></returns>
        public FileResult ExportStu2()
        {
            // schoolname = "401";
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            List <Staff> listRainInfo = db.Staffs.ToList();

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("电脑号");
            row1.CreateCell(1).SetCellValue("姓名");
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < listRainInfo.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].StaffNumber.ToString());
                rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].Name.ToString());
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", "第一批电脑派位生名册.xls"));
        }
        /// <summary>
        /// 下载当前最新配置
        /// </summary>
        /// <returns></returns>
        public ActionResult DownLoadFile()
        {
            var sourceList = _iswcmgr.GetAllSearchWord();
            //创建Excel文件的对象
            var book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            var sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            //给sheet1添加第一行的头部标题
            var row1     = sheet1.CreateRow(0);
            var fileName = "最新配置-" + DateTime.Now.ToString("yyyyMMdd") + "-" + new Random().Next(999) + ".xls";

            row1.CreateCell(0).SetCellValue("关键词");
            row1.CreateCell(1).SetCellValue("同义词");
            row1.CreateCell(2).SetCellValue("是否删除");
            for (var i = 0; i < sourceList.Count; i++)
            {
                var rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(sourceList[i].TargetWord);
                rowtemp.CreateCell(1).SetCellValue(sourceList[i].SourceWord);
                rowtemp.CreateCell(2).SetCellValue("1");
            }

            // 写入到客户端
            byte[] file;
            using (var ms = new System.IO.MemoryStream())
            {
                book.Write(ms);
                ms.Seek(0, SeekOrigin.Begin);
                file = ms.ToArray();
            }
            return(File(file, "application/vnd.ms-excel", fileName));
        }
Exemple #50
0
        public void NpoiExcel(System.Data.DataTable dt, string file)
        {
            try
            {
                double sheetCountdbl = double.Parse(dt.Rows.Count.ToString()) / 60000;
                int    sheetCount    = (int)(Math.Ceiling(sheetCountdbl));


                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

                for (int c = 0; c < sheetCount; c++)
                {
                    string sheetname = "Sheet" + (c + 1).ToString();

                    NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);

                    NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
                    ICellStyle             style     = book.CreateCellStyle();
                    style.Alignment         = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ICell cell = headerrow.CreateCell(i);
                        cell.CellStyle = style;
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                    }

                    int js = 0;//计数
                    //表内容
                    for (int i = c * 60000; i < dt.Rows.Count; i++)
                    {
                        if (js > 59999)
                        {
                            break;
                        }
                        IRow row = sheet.CreateRow(js + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = row.CreateCell(j);
                            cell.CellStyle = style;
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }

                        js++;
                    }
                }


                FileStream fs = File.Create(file);
                book.Write(fs);
                fs.Flush();
                fs.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
Exemple #51
0
 public FileResult ShunFengExcel()
 {
     try
     {
         //创建Excel文件的对象
         NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
         //添加一个sheet
         NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
         ExamUserInfoModel        models = new ExamUserInfoModel();
         models.GetUserInfo(null);
         //获取list数据
         var tlst = models.ListUserInfo;
         //给sheet1添加第一行的头部标题
         NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
         row1.CreateCell(0).SetCellValue("工号");
         row1.CreateCell(1).SetCellValue("姓名");
         row1.CreateCell(2).SetCellValue("入职日期");
         row1.CreateCell(3).SetCellValue("职等");
         row1.CreateCell(4).SetCellValue("职位");
         row1.CreateCell(5).SetCellValue("本职等对应技能等级");
         row1.CreateCell(6).SetCellValue("已经考取技能等级");
         row1.CreateCell(7).SetCellValue("最近一次理论考试成绩");
         row1.CreateCell(8).SetCellValue("最近一次通过理论时间");
         row1.CreateCell(9).SetCellValue("最近一次实践成绩");
         row1.CreateCell(10).SetCellValue("最近一次实践考核通过时间");
         row1.CreateCell(11).SetCellValue("最高可考技能等级");
         row1.CreateCell(12).SetCellValue("可申请技能等级");
         row1.CreateCell(13).SetCellValue("最近一次绩效成绩要求");
         //将数据逐步写入sheet1各个行
         for (int i = 0; i < tlst.Count; i++)
         {
             NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
             rowtemp.CreateCell(0).SetCellValue(tlst[i].UserCode);                          //工号
             rowtemp.CreateCell(1).SetCellValue(tlst[i].UserName);                          //姓名
             rowtemp.CreateCell(2).SetCellValue(tlst[i].EntryDate.ToString());              //入职日期
             rowtemp.CreateCell(3).SetCellValue(tlst[i].RankName);                          //职等
             rowtemp.CreateCell(4).SetCellValue(tlst[i].PostName);                          //职位
             rowtemp.CreateCell(5).SetCellValue(tlst[i].SkillLevel);                        //本职等对应技能等级
             rowtemp.CreateCell(6).SetCellValue(tlst[i].CurrentSkillLevel);                 //已经考取技能等级
             rowtemp.CreateCell(7).SetCellValue(tlst[i].ExamScore);                         //最近一次理论考试成绩
             rowtemp.CreateCell(8).SetCellValue(tlst[i].LastExamTime.ToString());           //最近一次通过理论时间
             rowtemp.CreateCell(9).SetCellValue(tlst[i].TheoreticalAchievement.ToString()); //最近一次实践成绩
             rowtemp.CreateCell(10).SetCellValue(tlst[i].PracticeTime.ToString());          //最近一次实践考核通过时间
             rowtemp.CreateCell(11).SetCellValue(tlst[i].HighestTestSkill);                 //最高可考技能等级
             rowtemp.CreateCell(12).SetCellValue(tlst[i].ApplicationLevel);                 //可申请技能等级
             rowtemp.CreateCell(13).SetCellValue(tlst[i].Achievement);                      //最近一次绩效成绩要求
         }
         // 写入到客户端
         System.IO.MemoryStream ms = new System.IO.MemoryStream();
         book.Write(ms);
         ms.Seek(0, SeekOrigin.Begin);
         return(File(ms, "application/vnd.ms-excel", "人员信息" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
     }
     catch (Exception)
     {
         throw;
     }
 }
Exemple #52
0
        private void btnOutput_Click(object sender, EventArgs e)
        {
            List <MODEL.Classes> lists = cm.GetAllClass(false);

            /*
             * 1.创建出工作薄
             * 2.为这个工作薄创建出工作表
             * 3.为这个表创建出行
             * 4.为这个行创建出每一列(单元格cell)--添加数据
             * 5.文件的写入
             */
            //创建一个工作薄
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            //创建一张工作表

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

            //  workbook.CreateSheet("Sh1");

            ///NPOI.HSSF. .SS .UserModel.HSSFSheet sheet1 = workbook.CreateSheet(); //.CreateSheet("sh1");
            ////workbook.CreateSheet("sh1");  //       workbook.CreateSheet("sh1");
            //需要导出的数据在集合中,每一个对象对应着后期表的一行数据
            for (int i = 0; i < lists.Count; i++)
            {
                //创建行
                IRow row = sheet1.CreateRow(i);
                //创建第一个单元格
                ICell cell1 = row.CreateCell(0);
                cell1.SetCellValue(lists[i].CID);
                //创建第2个单元格
                ICell cell2 = row.CreateCell(1);
                cell2.SetCellValue(lists[i].CName);
                //创建第3个单元格
                ICell cell3 = row.CreateCell(2);
                cell3.SetCellValue(lists[i].CCount);
                //创建第4个单元格
                ICell cell4 = row.CreateCell(3);
                cell4.SetCellValue(lists[i].CImg);
                //创建第5个单元格
                ICell cell5 = row.CreateCell(4);
                cell5.SetCellValue(lists[i].CIsDel);
                //创建第6个单元格
                ICell cell6 = row.CreateCell(5);
                //cell6.SetCellValue(lists[i].CAddTime.ToString("yyyy年MM月dd日"));  //日期值会被当成double
                cell6.SetCellValue(lists[i].CAddTime);
                //如何修改日期类型的格式
                ICellStyle  cs = workbook.CreateCellStyle();
                IDataFormat df = workbook.CreateDataFormat();
                cs.DataFormat   = df.GetFormat("yyyy年MM月dd日");
                cell6.CellStyle = cs;
            }
            using (FileStream fs = new FileStream("aa.xls", FileMode.Create))
            {
                workbook.Write(fs);
                MessageBox.Show("ok");
            }
        }
Exemple #53
0
        public FileResult OmitCheckCountExportExcel()
        {
            PatrolRouteStat_SW sw = new PatrolRouteStat_SW();

            sw.orgNo     = Request.Params["BYORGNO"];
            sw.TopORGNO  = Request.Params["BYORGNO"];
            sw.DateBegin = Request.Params["TIMEBegin"];
            sw.DateEnd   = Request.Params["TIMEEnd"];
            var list = HUReportCls.getPatrolRouteStatModel(sw);

            NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
            sheet1.IsPrintGridlines = true;                                       //打印时显示网格线
            sheet1.DisplayGridlines = true;                                       //查看时显示网格线
            sheet1.SetColumnWidth(0, 30 * 256);
            sheet1.SetColumnWidth(1, 10 * 256);
            sheet1.SetColumnWidth(2, 10 * 256);
            sheet1.SetColumnWidth(3, 10 * 256);
            sheet1.SetColumnWidth(4, 10 * 256);
            IRow row = sheet1.CreateRow(0);

            row.CreateCell(0).SetCellValue("未巡统计表");
            row.GetCell(0).CellStyle = getCellStyleTitle(book);
            row = sheet1.CreateRow(1);
            row.CreateCell(0).SetCellValue("单位/姓名");
            row.CreateCell(1).SetCellValue("应巡");
            row.CreateCell(2).SetCellValue("已巡");
            row.CreateCell(3).SetCellValue("未巡");
            row.CreateCell(4).SetCellValue("完成率");
            row.GetCell(0).CellStyle = getCellStyleHead(book);
            row.GetCell(1).CellStyle = getCellStyleHead(book);
            row.GetCell(2).CellStyle = getCellStyleHead(book);
            row.GetCell(3).CellStyle = getCellStyleHead(book);
            row.GetCell(4).CellStyle = getCellStyleHead(book);
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4));

            int rowI = 0;

            foreach (var v in list)
            {
                row = sheet1.CreateRow(rowI + 2);

                row.CreateCell(0).SetCellValue(v.ORGName);
                row.CreateCell(1).SetCellValue(v.PointCount);
                row.CreateCell(2).SetCellValue(v.PointCount0);
                row.CreateCell(3).SetCellValue(v.PointCount1);
                row.CreateCell(4).SetCellValue(v.PointCount2);
                rowI++;
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string fileName = "未巡统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";

            return(File(ms, "application/vnd.ms-excel", fileName));
        }
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="response"></param>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetname"></param>
        public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetname);
            NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
            NPOI.SS.UserModel.ICell          cell  = null;
            NPOI.SS.UserModel.IFont          font  = book.CreateFont();
            NPOI.SS.UserModel.ICellStyle     style = book.CreateCellStyle();
            font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            font.FontName   = "微软雅黑";

            style.SetFont(font);

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cell           = row.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string strColDataType = dt.Columns[j].DataType.ToString();
                    if (strColDataType.Equals("System.Int32"))
                    {
                        int intValue = 0;
                        int.TryParse(dt.Rows[i][j].ToString(), out intValue);
                        row2.CreateCell(j).SetCellValue(intValue);
                    }
                    else if (strColDataType.Equals("System.String"))
                    {
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                    else if (strColDataType.Equals("System.Double"))
                    {
                        double dblValue = 0;
                        double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
                        row2.CreateCell(j).SetCellValue(dblValue);
                    }
                }
            }
            //写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
            response.ContentType = "application/vnd.ms-excel";
            //response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemple #55
0
        //导出 dt,和表头都传
        public Stream ExportExcel(DataTable dt, List <EColName> list)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);



            //组建表头
            int index = 0;

            foreach (var i in list)
            {
                row1.CreateCell(index).SetCellValue(i.col);
                i.sortseq = index;
                index     = index + 1;
            }

            //将数据逐步写入sheet1各个行
            double d0 = 0;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);

                for (int jj = 0; jj < list.Count; jj++)
                {
                    string colname = (from EColName a in list where a.sortseq == jj select a.colname).ToArray()[0];
                    string coldata = dt.Rows[i][colname].ToString();
                    string dbtype  = dt.Columns[colname].DataType.ToString();
                    if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
                    {
                        if (double.TryParse(coldata, out d0))
                        {
                            d0 = Convert.ToDouble(coldata);
                        }
                        rowtemp.CreateCell(jj).SetCellValue(d0);
                    }
                    else
                    {
                        rowtemp.CreateCell(jj).SetCellValue(coldata);
                    }
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);

            return(ms);
        }
Exemple #56
0
        public FileResult Excel2()
        {
            MyElectrCheck_DBDataContext mdc = new MyElectrCheck_DBDataContext();

            List <DataEliminate> list = new List <DataEliminate>();

            if (Session["solvelist"] != null)
            {
                //将session转为集合
                list = (List <DataEliminate>)Session["solvelist"];
            }

            NPOI.HSSF.UserModel.HSSFWorkbook Book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet1 = Book.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow           row1   = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("任务编号");
            row1.CreateCell(1).SetCellValue("任务名称");
            row1.CreateCell(2).SetCellValue("线路编号");
            row1.CreateCell(3).SetCellValue("线路编号");
            row1.CreateCell(4).SetCellValue("有无故障");
            row1.CreateCell(5).SetCellValue("缺陷级别");
            row1.CreateCell(6).SetCellValue("缺陷类型");
            row1.CreateCell(7).SetCellValue("消缺时间");
            row1.CreateCell(8).SetCellValue("发现时间");
            row1.CreateCell(9).SetCellValue("缺陷描述");
            for (int i = 0; i < list.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].solveTaskCode);
                rowtemp.CreateCell(1).SetCellValue(list[i].solveTaskName);
                rowtemp.CreateCell(2).SetCellValue(list[i].lineCode);
                if (list[i].isBug == 1)
                {
                    rowtemp.CreateCell(3).SetCellValue("有");
                }
                else
                {
                    rowtemp.CreateCell(3).SetCellValue("无");
                }
                rowtemp.CreateCell(4).SetCellValue(list[i].bugLevelName);
                rowtemp.CreateCell(5).SetCellValue(list[i].bugLevelName);
                rowtemp.CreateCell(6).SetCellValue(list[i].bugTypeName);
                rowtemp.CreateCell(7).SetCellValue(list[i].finishTime.ToString());
                rowtemp.CreateCell(8).SetCellValue(list[i].discoverTime.ToString());
                rowtemp.CreateCell(9).SetCellValue(list[i].bugDesc);
            }
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            Book.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);
            DateTime dt       = DateTime.Now;
            string   dateTime = dt.ToString("yyMMddHHmmssff");
            string   fileName = "查询结果" + dateTime + ".xls";

            return(File(ms, "application/vnd.ms-excel", fileName));
        }
Exemple #57
0
        public static MemoryStream CreateExcel2003Stream(DataTable dt, Dictionary <string, string> columnInfo)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            WriteData(ref sheet1, dt, columnInfo, string.Empty);

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            return(ms);
        }
Exemple #58
0
        //导出 只传dt
        public Stream ExportExcel(DataTable dt)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);

            //组建表头
            DataColumnCollection dcc = dt.Columns;
            int index = 0;

            foreach (DataColumn i in dcc)
            {
                row1.CreateCell(index).SetCellValue(i.ColumnName);
                index = index + 1;
            }

            double d0 = 0;

            //将数据逐步写入sheet1各个行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);

                for (int jj = 0; jj < dcc.Count; jj++)
                {
                    string dbtype  = dcc[jj].DataType.ToString();
                    string coldata = dt.Rows[i][dcc[jj].ColumnName].ToString();
                    if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
                    {
                        if (double.TryParse(coldata, out d0))
                        {
                            d0 = Convert.ToDouble(coldata);
                        }
                        rowtemp.CreateCell(jj).SetCellValue(d0);
                    }
                    else
                    {
                        rowtemp.CreateCell(jj).SetCellValue(coldata);
                    }
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);

            return(ms);
        }
        /// <summary>
        /// Creates the excel worksheet.
        /// </summary>
        /// <param name="name">The sheet name.</param>
        public void CreateWorksheet(string name)
        {
            if (_workbook == null)
            {
                CreateWorkbook("");
            }


            //Creating the Sheet which will show us all data
            NPOI.SS.UserModel.Sheet sheet = _workbook.CreateSheet(name);

            _sheet = sheet;
        }
        /// <summary>
        /// 设置样式
        /// </summary>
        private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
        {
            NPOI.SS.UserModel.ISheet sheet   = book.CreateSheet(sheetname);
            NPOI.SS.UserModel.IRow   row     = sheet.CreateRow(0);
            NPOI.SS.UserModel.ICell  cell    = null;
            NPOI.SS.UserModel.ICell  newCell = null;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cell           = row.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string strColDataType = dt.Columns[j].DataType.ToString();
                    if (strColDataType.Equals("System.Int32"))
                    {
                        int intValue = 0;
                        int.TryParse(dt.Rows[i][j].ToString(), out intValue);
                        row2.CreateCell(j).SetCellValue(intValue);
                    }
                    else if (strColDataType.Equals("System.String"))
                    {
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                    else if (strColDataType.Equals("System.Double"))
                    {
                        double dblValue = 0;
                        double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
                        row2.CreateCell(j).SetCellValue(dblValue);
                    }
                    else if (strColDataType.Equals("System.DateTime"))
                    {
                        DateTime dateV;
                        DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
                        newCell = row2.CreateCell(j);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        HSSFCellStyle  cellStyle = (HSSFCellStyle)book.CreateCellStyle();
                        HSSFDataFormat format    = (HSSFDataFormat)book.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
                        newCell.CellStyle    = cellStyle;
                    }
                }
            }
        }