public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet)
        {
            //Create a Title row
            var titleFont = workbook.CreateFont();
            titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            titleFont.FontHeightInPoints = 11;
            titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;

            var titleStyle = workbook.CreateCellStyle();
            titleStyle.SetFont(titleFont);

            var row = sheet.CreateRow(rowCount++);
            var cell = row.CreateCell(0);
            cell.CellStyle = titleStyle;
            cell.SetCellValue("Date Range");

            row = sheet.CreateRow(rowCount++);
            cell = row.CreateCell(0);
            var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy"));
            cell.SetCellValue(value);
            row = sheet.CreateRow(rowCount++);
            cell = row.CreateCell(0);
            value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy"));
            cell.SetCellValue(value);
        }
Exemplo n.º 2
4
        public void TestDoesNoHarmIfNothingToDo()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            IFont f = wb.CreateFont();
            f.FontName = ("Testing");
            NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
            s.SetFont(f);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            // Optimise fonts
            HSSFOptimiser.OptimiseFonts(wb);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            Assert.AreEqual(f, s.GetFont(wb));

            // Optimise styles
            HSSFOptimiser.OptimiseCellStyles(wb);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            Assert.AreEqual(f, s.GetFont(wb));
        }
Exemplo n.º 3
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);
        }
Exemplo n.º 4
0
        public void NpoiExcel(DataTable dt, string title)
        {
            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;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = headerrow.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            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++)
                {
                    string DgvValue = dt.Rows[I][j].ToString();
                    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();
        }
Exemplo n.º 5
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();
        }
Exemplo n.º 6
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;
                }
            }
        }
Exemplo n.º 7
0
        private static void createColumnName(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet)
        {
            SetCellRangeAddress(sheet, 0, 0, 0, 1);
            SetCellRangeAddress(sheet, 0, 0, 3, 4);
            SetCellRangeAddress(sheet, 0, 0, 6, 7);
            SetCellRangeAddress(sheet, 0, 0, 9, 10);
            SetCellRangeAddress(sheet, 0, 0, 12, 14);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("输入功率标定");
            row.CreateCell(3).SetCellValue("输出功率标定");
            row.CreateCell(6).SetCellValue("反射功率标定");
            row.CreateCell(9).SetCellValue("ALC功率标定");
            row.CreateCell(12).SetCellValue("衰减补偿");
            row.GetCell(0).CellStyle  = GetCellStyle(book);
            row.GetCell(3).CellStyle  = GetCellStyle(book);
            row.GetCell(6).CellStyle  = GetCellStyle(book);
            row.GetCell(9).CellStyle  = GetCellStyle(book);
            row.GetCell(12).CellStyle = GetCellStyle(book);

            row = sheet.CreateRow(1);
            for (int i = 0; i < 4; i++)
            {
                row.CreateCell(3 * i, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("采样电压");
                row.CreateCell(3 * i + 1, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("定标点");
                row.GetCell(3 * i).CellStyle     = GetCellStyle(book);
                row.GetCell(3 * i + 1).CellStyle = GetCellStyle(book);
            }
            row.CreateCell(12, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("起始值");
            row.CreateCell(13, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("结束值");
            row.CreateCell(14, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("补偿值");
            row.GetCell(12).CellStyle = GetCellStyle(book);
            row.GetCell(13).CellStyle = GetCellStyle(book);
            row.GetCell(14).CellStyle = GetCellStyle(book);
        }
Exemplo n.º 8
0
        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;
            }
        }
Exemplo n.º 9
0
    /// <summary>
    /// 加边框
    /// </summary>
    /// <param Name="rowindex">1开始</param>
    /// <param Name="cellIndex">1开始</param>
    public void AddBorder( ISheet sheet, HSSFWorkbook workbook)
    {
        ICellStyle styel = workbook.CreateCellStyle();
        styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
        IFont font1 = workbook.CreateFont();
        font1.FontHeightInPoints = 11;

        font1.Boldweight = 600;
        font1.FontName = "宋体";
        styel.SetFont(font1);
        for (int rowindex=1;rowindex<sheet.LastRowNum+1;rowindex++)
        {
            for (int cellIndex =0; cellIndex < dcs.Count;cellIndex++ )
            {
                sheet.GetRow(rowindex).RowStyle = styel;
                ICell cell = sheet.GetRow(rowindex ).GetCell(cellIndex );

                HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle;

                Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                Style.VerticalAlignment = VerticalAlignment.Center;
                Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.DataFormat = 0;
                Style.SetFont(font1);
                cell.CellStyle = Style;
            }
         }
    }
Exemplo n.º 10
0
        //将一个集合中的数据写入到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");
        }
Exemplo n.º 11
0
        /// <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();
        }
Exemplo n.º 12
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();
        }
Exemplo n.º 13
0
        /// <summary>
        /// 获取多个isheet
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public List <ISheet> GetSheet(string filePath)
        {
            List <ISheet> sheet1 = new List <ISheet>();

            using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                {
                    NPOI.XSSF.UserModel.XSSFWorkbook workBook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
                    for (int i = 0; i < workBook.NumberOfSheets; i++)
                    {
                        sheet1.Add(workBook.GetSheetAt(i));
                    }
                }
                else
                {
                    NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
                    for (int i = 0; i < workBook.NumberOfSheets; i++)
                    {
                        sheet1.Add(workBook.GetSheetAt(i));
                    }
                }
            }
            return(sheet1);
        }
        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);
        }
Exemplo n.º 15
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);
 }
Exemplo n.º 16
0
        /// <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();
        }
Exemplo n.º 17
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);
        }
Exemplo n.º 18
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);
 }
        /// <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));
        }
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            HSSFSheet sheet = workbook.GetSheet(SheetName);

            DataTable table = new DataTable();

            HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }
Exemplo n.º 21
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);
        }
Exemplo n.º 22
0
 private static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
 {
     //Write the stream data of workbook to the root directory
     MemoryStream file = new MemoryStream();
     hssfworkbook.Write(file);
     return file;
 }
Exemplo n.º 23
0
        /// <summary>
        /// 导出Excel
        /// 将 DataTable 内容导出到Excel文件中
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="filePath">文件路径</param>
        /// <param name="dataSetName">默认为:DataSet1</param>
        /// <returns>导出结果</returns>
        public static bool DataSet2Excel(DataSet ds, string filePath, ExcelWriterConfig config = null)
        {
            bool      isSucceed = false;
            IWorkbook iWorkbook = null;

            if (filePath.EndsWith("xlsx"))
            {
                iWorkbook = new XSSFWorkbook();
            }
            else if (filePath.EndsWith("xls"))
            {
                iWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }
            else
            {
                throw new Exception("必须保存为Excel标准后缀(.xls, .xlsx)。");
            }

            int sheetCount = 1;

            foreach (DataTable dt in ds.Tables)
            {
                string sheetName = string.Format("Sheet{0}", sheetCount.ToString());
                if (config != null && config.SheetNameList != null && config.SheetNameList.Count >= sheetCount)
                {
                    sheetName = config.SheetNameList[sheetCount - 1];
                }
                sheetCount = sheetCount + 1;

                ISheet workSheet = iWorkbook.CreateSheet(sheetName);
                //表头
                IRow headerRow = workSheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }

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

            using (FileStream fOut = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite)) // 写入流
            {
                fOut.Flush();
                iWorkbook.Write(fOut);//写入文件
                isSucceed = true;
            }

            iWorkbook = null;
            return(isSucceed);
        }
        public void AddListToExcelSheet(HSSFWorkbook workbook, ISheet sheet, string Title, Dictionary<string, bool> list)
        {
            //Create a Title row
            var titleFont = workbook.CreateFont();
            titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            titleFont.FontHeightInPoints = 11;
            titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;

            var titleStyle = workbook.CreateCellStyle();
            titleStyle.SetFont(titleFont);

            var row = sheet.CreateRow(rowCount++);
            row = sheet.CreateRow(rowCount++);
            var cell = row.CreateCell(0);
            cell.CellStyle = titleStyle;
            cell.SetCellValue(Title);
            foreach (var org in list)
            {
                if (org.Value == true)
                {
                    row = sheet.CreateRow(rowCount++);
                    cell = row.CreateCell(0);
                    cell.SetCellValue(org.Key);
                }
            }
        }
Exemplo n.º 25
0
        public static Workbook CreateWorkBookWithSheet()
        {
            Workbook workbook = new HSSFWorkbook();
            workbook.CreateSheet();

            return workbook;
        }
Exemplo n.º 26
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);
        }
Exemplo n.º 27
0
        	/**
	 * Makes sure that a formula referring to the named range parses properly
	 */
        private static void ConfirmParseFormula(HSSFWorkbook workbook)
        {
            Ptg[] ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
            Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
            Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
            Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
        }
Exemplo n.º 28
0
 public CMixExcel(Stream s)
 {
     if (IsUpperVer2003)
         _excelPackage = new ExcelPackage(s);
     else
         _HSSFWorkbook = new HSSFWorkbook(s);
 }
Exemplo n.º 29
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");
    }
Exemplo n.º 30
0
        /// <summary>
        /// Creates the excel workbook.
        /// </summary>
        /// <param name="subject">The subject.</param>
        public void CreateWorkbook(string subject)
        {
            //Creating the excel workbook
            NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();

            //Creating summary information to the document
            NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "Thoris";

            //Applying summary information to the document
            wb.DocumentSummaryInformation = dsi;

            //Creating summary information for the data
            NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
            si.Subject = subject;

            //Applying summary information to the data
            wb.SummaryInformation = si;

            _workbook = wb;



            //Creating the current styles
            Font font = _workbook.CreateFont();

            font.Boldweight = (short)FontBoldWeight.BOLD;

            NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
            cellStyle.SetFont(font);

            _headerStyle = CreateHeaderStyle(_workbook);
            _dataStyle   = CreateStyle(_workbook, false);
        }
Exemplo n.º 31
0
        /// <summary>
        /// 读取2007以上版本.xlsx
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static string Read2003ToString(string path)
        {
            HSSFWorkbook hssfworkbook;
            path = HttpContext.Current.Server.MapPath(path);

            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }

            HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            StringBuilder sb = new StringBuilder();
            int irow = 0;
            sb.Append("<table>");
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                irow++;
                sb.Append("<tr>");
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    HSSFCell cell = (HSSFCell)row.GetCell(i);
                    string dr = "";
                    if (cell == null) { dr = ""; }
                    else { dr = cell.ToString(); }
                    sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
                }
                sb.Append("</tr>");
            }
            sb.Append("</table>");
            return sb.ToString();
        }
Exemplo n.º 32
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);
     //写入到客户端
 }
Exemplo n.º 33
0
        public void TestAvg()
        {

            IWorkbook wb = new HSSFWorkbook();

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

            ISheet sh = wb.CreateSheet();
            ICell a1 = sh.CreateRow(1).CreateCell(1);
            a1.SetCellValue(1);
            ICell a2 = sh.CreateRow(2).CreateCell(1);
            a2.SetCellValue(3);
            ICell a3 = sh.CreateRow(3).CreateCell(1);
            a3.CellFormula = ("SUBTOTAL(1,B2:B3)");
            ICell a4 = sh.CreateRow(4).CreateCell(1);
            a4.SetCellValue(1);
            ICell a5 = sh.CreateRow(5).CreateCell(1);
            a5.SetCellValue(7);
            ICell a6 = sh.CreateRow(6).CreateCell(1);
            a6.CellFormula = ("SUBTOTAL(1,B2:B6)*2 + 2");
            ICell a7 = sh.CreateRow(7).CreateCell(1);
            a7.CellFormula = ("SUBTOTAL(1,B2:B7)");

            fe.EvaluateAll();

            Assert.AreEqual(2.0, a3.NumericCellValue);
            Assert.AreEqual(8.0, a6.NumericCellValue);
            Assert.AreEqual(3.0, a7.NumericCellValue);
        }
        public void TestRecord()
        {
            POIFSFileSystem fs = new POIFSFileSystem(
                    HSSFTestDataSamples.OpenSampleFileStream("WithFormattedGraphTitle.xls"));

            // Check we can Open the file via usermodel
            HSSFWorkbook hssf = new HSSFWorkbook(fs);

            // Now process it through eventusermodel, and
            //  look out for the title records
            ChartTitleFormatRecordGrabber grabber = new ChartTitleFormatRecordGrabber();
            Stream din = fs.CreateDocumentInputStream("Workbook");
            HSSFRequest req = new HSSFRequest();
            req.AddListenerForAllRecords(grabber);
            HSSFEventFactory factory = new HSSFEventFactory();
            factory.ProcessEvents(req, din);
            din.Close();

            // Should've found one
            Assert.AreEqual(1, grabber.chartTitleFormatRecords.Count);
            // And it should be of something interesting
            AlRunsRecord r =
                (AlRunsRecord)grabber.chartTitleFormatRecords[0];
            Assert.AreEqual(3, r.GetFormatCount());
        }
Exemplo n.º 35
0
        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);

        }
Exemplo n.º 36
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));
            }
        }
Exemplo n.º 37
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();
        }
Exemplo n.º 38
0
        public HSSFCreationHelper(HSSFWorkbook wb)
        {
            workbook = wb;

            // Create the things we only ever need one of
            dataFormat = new HSSFDataFormat(workbook.Workbook);
        }
Exemplo n.º 39
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);
            //写入到客户端
        }
        public void TestOpenWORKBOOK()
        {
            Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(xlsA);

            POIFSFileSystem fs = new POIFSFileSystem(is1);

            // Ensure that we have a WORKBOOK entry
            fs.Root.GetEntry("WORKBOOK");
            // And a summary
            fs.Root.GetEntry("\x0005SummaryInformation");
            Assert.IsTrue(true);

            // But not a Workbook one
            try
            {
                fs.Root.GetEntry("Workbook");
                Assert.Fail();
            }
            catch (FileNotFoundException)
            {

            }

            // Try to Open the workbook
            HSSFWorkbook wb = new HSSFWorkbook(fs);
        }
Exemplo n.º 41
0
        /*
        * 响应到客户端
        *
        * Param fileName 文件名
        */
        public static void WriteToClient(String fileName, HSSFWorkbook workbook)
        {
            //Write the stream data of workbook to the root directory
            //FileStream file = new FileStream(@"c:/test.xls", FileMode.Create);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();

            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.Expires = 0;
            HttpContext.Current.Response.CacheControl = "no-cache";

            HttpContext.Current.Response.ContentType = "application/x-excel";
            //inline
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + UTF_FileName(fileName));
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            workbook.Write(HttpContext.Current.Response.OutputStream);

            Sheet sheet = workbook.GetSheetAt(0);
            sheet = null;
            workbook.Dispose();
            workbook = null;

            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
            //file.Close();
        }
Exemplo n.º 42
0
        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="sheetCount"></param>
        //protected override void FillData(Microsoft.Office.Interop.Excel.Workbook workBook, int sheetCount)
        //{
        //    int rowCount = Dt.Rows.Count;
        //    int colCount = Dt.Columns.Count;
        //    for (int i = 1; i <= sheetCount; i++)
        //    {
        //        int startRow = (i - 1) * Rows;
        //        int endRow = i * Rows;
        //        if (i == sheetCount)
        //        {
        //            endRow = rowCount;
        //        }
        //        Worksheet sheet = (Worksheet)workBook.Worksheets.get_Item(i);
        //        sheet.Name = this.SheetPrefixName + "-" + i.ToString();
        //        for (int j = 0; j < endRow - startRow; j++)
        //        {
        //            for (int k = 0; k < colCount; k++)
        //            {
        //                if (k < 2)
        //                {
        //                    sheet.Cells[Top + j, Left + k] = Dt.Rows[startRow + j][k].ToString();
        //                }
        //                else
        //                {
        //                    sheet.Cells[Top + j, Left + k + 2] = Dt.Rows[startRow + j][k].ToString();
        //                }
        //            }
        //        }
        //        SetCellParameters(sheet);
        //    }
        //}

        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="sheetCount"></param>
        protected override void FillData(NPOI.HSSF.UserModel.HSSFWorkbook workBook, int sheetCount)
        {
            int rowCount = Dt.Rows.Count;
            int colCount = Dt.Columns.Count;

            for (int i = 0; i < sheetCount; i++)
            {
                int startRow = i * Rows;
                int endRow   = (i + 1) * Rows;
                if ((i + 1) == sheetCount)
                {
                    endRow = rowCount;
                }
                HSSFSheet sheet = (HSSFSheet)workBook.GetSheetAt(i);
                workBook.SetSheetName(i, this.SheetPrefixName + "-" + i.ToString());
                for (int j = 0; j < endRow - startRow; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        if (k < 2)
                        {
                            sheet.GetRow(Top + j).GetCell(Left + k).SetCellValue(Dt.Rows[startRow + j][k].ToString());
                        }
                        else
                        {
                            sheet.GetRow(Top + j).GetCell(Left + k + 2).SetCellValue(Dt.Rows[startRow + j][k].ToString());
                        }
                    }
                }
                SetCellParameters(sheet);
            }
        }
Exemplo n.º 43
0
        //private static POILogger log = POILogFactory.GetLogger(typeof(HSSFSheet));

        /// <summary>
        /// Creates new HSSFSheet - called by HSSFWorkbook to create a _sheet from
        /// scratch. You should not be calling this from application code (its protected anyhow).
        /// </summary>
        /// <param name="workbook">The HSSF Workbook object associated with the _sheet.</param>
        /// <see cref="NPOI.HSSF.UserModel.HSSFWorkbook.CreateSheet()"/>
        public HSSFSheet(HSSFWorkbook workbook)
        {
            _sheet = InternalSheet.CreateSheet();
            rows = new Dictionary<int, NPOI.SS.UserModel.IRow>();
            this._workbook = workbook;
            this.book = workbook.Workbook;
        }
Exemplo n.º 44
0
 public NpoiLib(string FileTemplate)
 {
     using (FileStream file = new FileStream(FileTemplate, FileMode.Open, FileAccess.Read))
     {
         _workbook = new HSSFWorkbook(file);
     }
 }
Exemplo n.º 45
0
        private static bool addRecord(float data)
        {
            try
            {
                //close the FileStream before specifying any changes in the HSSFSheet object
                FileStream fs = new FileStream(pathExcel, FileMode.Open, FileAccess.ReadWrite);
                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
                HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("Arkusz1");
                fs.Close();

                DateTime current = DateTime.Now;
                DateTime thisDay = DateTime.Today;

                Row row = sheet.CreateRow(sheet.LastRowNum + 1);
                row.CreateCell(0).SetCellValue(data);
                row.CreateCell(1).SetCellValue(DateTime.Now.ToShortTimeString());
                row.CreateCell(2).SetCellValue(thisDay.ToString("d"));

                //reinitialize data before having the workbook object write to the Stream
                sheet.ForceFormulaRecalculation = true;
                fs = new FileStream(pathExcel, FileMode.Open, FileAccess.ReadWrite);
                templateWorkbook.Write(fs);
                fs.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
Exemplo n.º 46
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"));
        }
Exemplo n.º 47
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);
        }
Exemplo n.º 48
0
        /// <summary>
        /// Formats the COBie data into an Excel XLS file
        /// </summary>
        /// <param name="cobie"></param>
        public void Serialise(COBieWorkbook workbook, ICOBieValidationTemplate ValidationTemplate = null)
        {
            if (workbook == null) { throw new ArgumentNullException("COBie", "COBieXLSSerialiser.Serialise does not accept null as the COBie data parameter."); }

            if (!File.Exists(TemplateFileName))
                throw new Exception("COBie creation error. Could not locate template file " + TemplateFileName);
            // Load template file
            FileStream excelFile = File.Open(TemplateFileName, FileMode.Open, FileAccess.Read);

            XlsWorkbook = new HSSFWorkbook(excelFile, true);

            CreateFormats();

            foreach (var sheet in workbook)
            {
                WriteSheet(sheet);
            }

            UpdateInstructions();

            ReportErrors(workbook, ValidationTemplate);

            ReportRules();

            using (FileStream exportFile = File.Open(FileName, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None))
            {
                XlsWorkbook.Write(exportFile);
            }
        }
Exemplo n.º 49
0
        static void Main(string[] args)
        {
            //Excel worksheet combine example
            //You will be prompted to select two Excel files. test.xls will be created that combines the sheets
			//Note: This example does not check for duplicate sheet names. Your test files should have different sheet names.
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel document (*.xls)|*.xls";
            ofd.Title = "Select first Excel document";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                HSSFWorkbook book1 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
                ofd.Title = "Select second Excel document";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    HSSFWorkbook book2 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
                    HSSFWorkbook product = new HSSFWorkbook();

                    for (int i = 0; i < book1.NumberOfSheets; i++)
                    {
                        HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;
                        sheet1.CopyTo(product, sheet1.SheetName, true, true);
                    }
                    for (int j = 0; j < book2.NumberOfSheets; j++)
                    {
                        HSSFSheet sheet2 = book2.GetSheetAt(j) as HSSFSheet;
                        sheet2.CopyTo(product, sheet2.SheetName, true, true);
                    }
                    product.Write(new FileStream("test.xls", FileMode.Create, FileAccess.ReadWrite));
                }
                
            }
        }
Exemplo n.º 50
0
        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();
        }
Exemplo n.º 51
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]);
            }
        }
 public ServiceLineExplorerExplorerExcelExporter(string serviceGroupName, MemoryStream output)
 {
     this.serviceGroupName = serviceGroupName;
     this.output = output;
     workbook = new HSSFWorkbook();
     filterLists = new Dictionary<string, Dictionary<string, bool>>();
 }
Exemplo n.º 53
0
 public static IWorkbook WriteOutAndReadBack(IWorkbook wb)
 {
     IWorkbook result;
     try
     {
         using (MemoryStream baos = new MemoryStream(8192))
         {
             wb.Write(baos);
             using (Stream is1 = new MemoryStream(baos.ToArray()))
             {
                 if (wb is HSSFWorkbook)
                 {
                     result = new HSSFWorkbook(is1);
                 }
                 else if (wb is XSSFWorkbook)
                 {
                     result = new XSSFWorkbook(is1);
                 }
                 else
                 {
                     throw new RuntimeException("Unexpected workbook type ("
                             + wb.GetType().Name + ")");
                 }
             }
         }
     }
     catch (IOException e)
     {
         throw new RuntimeException(e);
     }
     return result;
 }
Exemplo n.º 54
0
        public void Test27349()
        {
            // 27349-vLookupAcrossSheets.xls is bugzilla/attachment.cgi?id=10622
            Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vLookupAcrossSheets.xls");
            HSSFWorkbook wb;
            try
            {
                // original bug may have thrown exception here, or output warning to
                // stderr
                wb = new HSSFWorkbook(is1);
            }
            catch (IOException e)
            {
                throw new SystemException(e.Message);
            }

            ISheet sheet = wb.GetSheetAt(0);
            IRow row = sheet.GetRow(1);
            ICell cell = row.GetCell(0);

            // this defInitely would have failed due to 27349
            Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell
                    .CellFormula);

            // We might as well Evaluate the formula
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            CellValue cv = fe.Evaluate(cell);

            Assert.AreEqual(CellType.Numeric, cv.CellType);
            Assert.AreEqual(3.0, cv.NumberValue, 0.0);
        }
Exemplo n.º 55
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);
            }
        }
Exemplo n.º 56
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");
            }
        }
Exemplo n.º 57
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;
     }
 }
 public static ISheet GetExcelSheetForXLS(string file, string sheetName)
 {
     using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
     {
         XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs);
         ISheet           sheet        = hssfworkbook.GetSheet(sheetName);
         return(sheet);
     }
 }
Exemplo n.º 59
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));
        }
Exemplo n.º 60
0
        private void button1_Click(object sender, EventArgs e)
        {
            DataSet   ds = new DataSet();
            DataTable dt = null;

            OpenFileDialog sflg = new OpenFileDialog();

            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return;
            }
            FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read);

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
            int sheetCount = book.NumberOfSheets;

            for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
            {
                NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
                if (sheet == null)
                {
                    continue;
                }

                NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
                if (row == null)
                {
                    continue;
                }

                int firstCellNum = row.FirstCellNum;
                int lastCellNum  = row.LastCellNum;
                if (firstCellNum == lastCellNum)
                {
                    continue;
                }

                dt = new DataTable(sheet.SheetName);
                for (int i = firstCellNum; i < lastCellNum; i++)
                {
                    dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow newRow = dt.Rows.Add();
                    for (int j = firstCellNum; j < lastCellNum; j++)
                    {
                        newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
                    }
                }

                ds.Tables.Add(dt);
            }
        }