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); }
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)); }
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); }
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(); }
/// <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(); }
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; } } }
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); }
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; } }
/// <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; } } }
//将一个集合中的数据写入到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"); }
/// <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(); }
/// <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(); }
/// <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); }
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(); }
/// <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); }
/// <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; }
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); }
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; }
/// <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); } } }
public static Workbook CreateWorkBookWithSheet() { Workbook workbook = new HSSFWorkbook(); workbook.CreateSheet(); return workbook; }
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); }
/** * 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()); }
public CMixExcel(Stream s) { if (IsUpperVer2003) _excelPackage = new ExcelPackage(s); else _HSSFWorkbook = new HSSFWorkbook(s); }
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"); }
/// <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); }
/// <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(); }
/// <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 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()); }
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); }
/// <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)); } }
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(); }
public HSSFCreationHelper(HSSFWorkbook wb) { workbook = wb; // Create the things we only ever need one of dataFormat = new HSSFDataFormat(workbook.Workbook); }
/// <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); }
/* * 响应到客户端 * * 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(); }
/// <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); } }
//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; }
public NpoiLib(string FileTemplate) { using (FileStream file = new FileStream(FileTemplate, FileMode.Open, FileAccess.Read)) { _workbook = new HSSFWorkbook(file); } }
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; } }
/// <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")); }
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); }
/// <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); } }
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)); } } }
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(); }
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>>(); }
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; }
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); }
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); } }
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"); } }
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); } }
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)); }
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); } }