/// <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)); } }
/// <summary> /// Saves all data created to the file. /// </summary> /// <param name="fileName">Name of the file.</param> public void SaveFile(string fileName) { if (System.IO.File.Exists(fileName)) { System.IO.File.Delete(fileName); } //Saving all data in the physical excel file FileStream excelFile = new FileStream(fileName, FileMode.Create); _workbook.Write(excelFile); excelFile.Close(); }
static void WriteToFile() { //Write the stream data of workbook to the root directory System.IO.FileStream file = new System.IO.FileStream(@"test.xls", System.IO.FileMode.Create); hssfworkbook.Write(file); file.Close(); }
public ActionResult CreateExcel() { HSSFWorkbook wk = new HSSFWorkbook(); ISheet tb = wk.CreateSheet("MyFirst"); //创建一行,此行为第二行:创建行是从0开始 IRow row = tb.CreateRow(1); ICell cell; for (int i = 0; i < 20; i++) { cell = row.CreateCell(i); //在第二行中创建单元格 cell.SetCellValue("This is a test");//循环往第二行的单元格中添加数据 } // ISheet tb = wk.CreateSheet("MyFirst"); // HSSFPatriarch patr = sheet.CreateDrawingPatriarch(); // HSSFComment comment1 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4)); // comment1.String = new HSSFRichTextString("Hello World"); // comment1.Author = "NPOI Team" //; // cell.CellComment = comment1; FileStream fs = new FileStream(@"f:/test.xls", FileMode.Create); wk.Write(fs); fs.Close(); return View(); }
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> /// 导出WOrd文件 /// </summary> public void Generate() { //首先在内存中创建一个Excel文件 var fileStream = new FileStream(outputPath, FileMode.OpenOrCreate); var workbook = new HSSFWorkbook(); //创建一个表(sheet)--并获取他 var sheet = (HSSFSheet) workbook.CreateSheet("华中科技大学工程"); //首先把需要多少个cell创建好----有多少个数据就有多少行--每行有3个数据 for (var i = 0; i <= dataList.Count; i++) { sheet.CreateRow(i); } //创建表头 GenerateHeaderRow(workbook, sheet); //然后给每行---创建单元格---添加数据 GenerateDataRow(workbook, sheet); try { //获取目标文件流--写入文件数据 workbook.Write(fileStream); fileStream.Close(); } catch (Exception e) { Console.Error.WriteLine(e.StackTrace); MessageBox.Show("文件导出失败", "失败"); return; } finally { if (fileStream != null) fileStream.Close(); } MessageBox.Show("Excel文件导出成功", "完成"); }
/// <summary> /// 由DataSet导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="sheetName">工作表名称</param> /// <returns>Excel工作表</returns> private static Stream ExportDataSetToExcel(DataSet sourceDs) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); for (int i = 0; i < sourceDs.Tables.Count; i++) { HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sourceDs.Tables[i].TableName); HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); // handling header. foreach (DataColumn column in sourceDs.Tables[i].Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); // handling value. int rowIndex = 1; foreach (DataRow row in sourceDs.Tables[i].Rows) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceDs.Tables[i].Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; return ms; }
/// <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; } } }
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; } }
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 override void Write(Stream outStream) { Workbook workbook = new HSSFWorkbook(); IEnumerable<User> users = User.SelectAll(); foreach (User user in users) { IEnumerable<TimeBlock> timeBlocks = TimeBlock.SelectByUserAndDateRange(user.UserId, FromDate, ToDate); if (timeBlocks.Count() > 0) { Sheet sheet = workbook.CreateSheet(user.Name); Row header = sheet.CreateRow(0); header.CreateCell(0).SetCellValue("Resource"); header.CreateCell(1).SetCellValue("Date"); header.CreateCell(2).SetCellValue("Client"); header.CreateCell(3).SetCellValue("Work Package"); header.CreateCell(4).SetCellValue("Hours"); int i = 1; foreach (TimeBlock timeBlock in timeBlocks) { Row row = sheet.CreateRow(i ++); row.CreateCell(0).SetCellValue(timeBlock.User.Name); row.CreateCell(1).SetCellValue(timeBlock.Date.ToString("dd/MM/yy")); row.CreateCell(2).SetCellValue(timeBlock.Job.Client.Name); row.CreateCell(3).SetCellValue(timeBlock.JobId); row.CreateCell(4).SetCellValue(Convert.ToDouble(timeBlock.Time)); } } } workbook.Write(outStream); }
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 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 void ExportData() { HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet sheet = book.CreateSheet("sheet1"); IRow row1 = sheet.CreateRow(0); row1.CreateCell(0).SetCellValue("科目代码"); row1.CreateCell(1).SetCellValue("科目名称"); row1.CreateCell(2).SetCellValue("科目类别"); IList <BudgetAccountsModel> budgetAccounts = BudgetAccountsService.ExportData(); //List<BudgetAccountsModel> models = budgetAccounts.ToList(); for (int i = 0; i < budgetAccounts.Count; i++) { BudgetAccountsModel model = budgetAccounts[i]; IRow row = sheet.CreateRow(i + 1); row.CreateCell(0).SetCellValue(model.KMDM); row.CreateCell(1).SetCellValue(model.KMMC); row.CreateCell(2).SetCellValue(model.KMLB == "0" ? "收入" : "支出"); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public 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(); }
//数据导出为Excel private void btnExport_Click(object sender, EventArgs e) { string sql = "select CC_AutoId,CC_CustomerName,CC_CellPhone,CC_Landline,CC_BuyDate,CC_CarNum,CC_BracketNum from T_Customers"; using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text)) { if (reader.HasRows) { IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("customers"); int rowIndex = 0;//手动处理行索引 while (reader.Read()) { int autoId = reader.GetInt32(0); string name = reader.GetString(1); string cellPhone = reader.GetString(2); //数据为空,那么就不能Get..,所以要在这里判断 string landLine = reader.IsDBNull(3) ? null : reader.GetString(3);//可为空 DateTime BuyDate = reader.GetDateTime(4); string carNum = reader.GetString(5); string bracketNum = reader.GetString(6); IRow row = sheet.CreateRow(rowIndex); rowIndex++; row.CreateCell(0).SetCellValue(autoId); row.CreateCell(1).SetCellValue(name); row.CreateCell(2).SetCellValue(cellPhone); //如果为空或Null,把Excel这个单元格设置为值为空 if (string.IsNullOrEmpty(landLine)) { row.CreateCell(3).SetCellType(CellType.BLANK); } else { row.CreateCell(3).SetCellValue(landLine); } //============设置日期格式======= //创建单元格样式对象 ICellStyle cellStyle = wb.CreateCellStyle(); //设置单元格样式对象属性 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); ICell BuyDateRow = row.CreateCell(4); BuyDateRow.CellStyle = cellStyle; BuyDateRow.SetCellValue(BuyDate); //==============设置完成======== row.CreateCell(5).SetCellValue(carNum); row.CreateCell(6).SetCellValue(bracketNum); } using (FileStream fs = File.OpenWrite("Customers.xls")) { wb.Write(fs); MessageBox.Show("ok"); } } } }
/// <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)); }
private Stream RenderDataSetToExcel(DataSet ds) { MemoryStream ms = new MemoryStream(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet; HSSFRow headerRow; //for (int k = ds.Tables.Count - 1, len = 0; len <= k; k--) { // DataTable dt = ds.Tables[k]; foreach(DataTable dt in ds.Tables) { sheet = (HSSFSheet)workbook.CreateSheet(dt.TableName); headerRow = (HSSFRow)sheet.CreateRow(0); foreach (DataColumn column in dt.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); int rowIndex = 1; foreach (DataRow row in dt.Rows) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return ms; }
public static void Main(String[] args) { WriteJavaDoc(); HSSFWorkbook wb = new HSSFWorkbook(); SheetWriter sw = new SheetWriter(wb); NumberToTextConversionExamples.ExampleConversion[] exampleValues = NumberToTextConversionExamples.GetExampleConversions(); for (int i = 0; i < exampleValues.Length; i++) { TestCases.SS.Util.NumberToTextConversionExamples.ExampleConversion example = exampleValues[i]; sw.AddTestRow(example.RawDoubleBits, example.ExcelRendering); } MemoryStream baos = new MemoryStream(); wb.Write(baos); byte[] fileContent = baos.ToArray(); ReplaceNaNs(fileContent, sw.GetReplacementNaNs()); FileInfo outputFile = new FileInfo("ExcelNumberRendering.xls"); FileStream os = File.OpenWrite(outputFile.FullName); os.Write(fileContent, 0, fileContent.Length); os.Close(); Console.WriteLine("Finished writing '" + outputFile.FullName + "'"); }
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; } }
public static string ExportEasy(string[] heads, List<string> data) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); //填充表头 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i < heads.Length; i++) { dataRow.CreateCell(i).SetCellValue(heads[i]); } //填充内容 for (int i = 0; i < data.Count; i++) { dataRow = (HSSFRow)sheet.CreateRow(i + 1); string[] str = data[i].Split('|'); for (int j = 0; j < heads.Length; j++) { dataRow.CreateCell(j).SetCellValue(str[j]); } } //保存 using (MemoryStream ms = new MemoryStream()) { var name = @"C:\tempfile\" + Guid.NewGuid().ToString() + ".xls"; try { using (FileStream fs2 = new FileStream(name, FileMode.Create, FileAccess.Write)) { workbook.Write(fs2); } #region 下载 //以字符流的形式下载文件 FileStream fs = new FileStream(name, FileMode.Open); byte[] bytes = new byte[(int)fs.Length]; fs.Read(bytes, 0, bytes.Length); fs.Close(); HttpContext.Current.Response.ContentType = "application/octet-stream"; //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("report.xls", System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); #endregion return "下载成功"; } catch (Exception ex) { return ex.Message; } finally { File.Delete(name); } } }
/// <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> /// <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 TestFail() { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("Simple.xls"); POIFSFileSystem fs = new POIFSFileSystem(is1); HSSFWorkbook wb = new HSSFWorkbook(fs); //set POIFS properties after constructing HSSFWorkbook //(a piece of code that used to work up to POI 3.0.2) SummaryInformation summary1 = (SummaryInformation)PropertySetFactory.Create(fs.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME)); summary1.Title=(title); //Write the modified property back to POIFS fs.Root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME).Delete(); fs.CreateDocument(summary1.ToStream(), SummaryInformation.DEFAULT_STREAM_NAME); //save the workbook and read the property MemoryStream out1 = new MemoryStream(); wb.Write(out1); out1.Close(); POIFSFileSystem fs2 = new POIFSFileSystem(new MemoryStream(out1.ToArray())); SummaryInformation summary2 = (SummaryInformation)PropertySetFactory.Create(fs2.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME)); //Assert.Failing assertion Assert.AreEqual(title, summary2.Title); }
public void TestWrite() { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(xlsA); POIFSFileSystem fs = new POIFSFileSystem(is1); // Open the workbook, not preserving nodes HSSFWorkbook wb = new HSSFWorkbook(fs); MemoryStream out1 = new MemoryStream(); wb.Write(out1); // Check now MemoryStream in1 = new MemoryStream(out1.ToArray()); POIFSFileSystem fs2 = new POIFSFileSystem(in1); // Check that we have the new entries fs2.Root.GetEntry("Workbook"); try { fs2.Root.GetEntry("WORKBOOK"); Assert.Fail(); } catch (FileNotFoundException) { } // And it can be Opened HSSFWorkbook wb2 = new HSSFWorkbook(fs2); }
/// <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); }
public FileResult Generate(SearchEntity searchEntity) { int num; var arrayData = this.GetArrayData(searchEntity, out num); var str = "根基对账单"; using (var stream = System.IO.File.OpenRead(base.Server.MapPath(string.Format("~/Excel/Market/{0}.xls", str)))) { var xlBook = new HSSFWorkbook(stream); this.GenerateM1(arrayData, xlBook); this.GenerateM2(arrayData, xlBook); this.GenerateM3(arrayData, xlBook); if (xlBook.NumberOfSheets > 3) { xlBook.RemoveSheetAt(0); xlBook.RemoveSheetAt(0); xlBook.RemoveSheetAt(0); xlBook.FirstVisibleTab = 0; } var str3 = string.Format("{0}{1:yyMMddHHmmss}.xls", str, DateTime.Now); var path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3); if (System.IO.File.Exists(path)) { str3 = str3 + Guid.NewGuid().ToString() + ".xls"; path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3); } using (var stream2 = System.IO.File.OpenWrite(path)) { xlBook.Write(stream2); } return this.File(path, "application/vnd.ms-excel", str3); } }
/// <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> /// 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); //写入到客户端 }
/* * 响应到客户端 * * 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(); }
public static Boolean GerarArquivo_Excel(DataSet dados, String path, String fileName, String template, String[] planilhas) { Boolean sucesso = false; FileStream file = null; FileStream fileTemplate = null; HSSFWorkbook xlPackage = null; try { fileTemplate = new FileStream(path + template, FileMode.Open, FileAccess.Read); xlPackage = new HSSFWorkbook(fileTemplate); Int32 index = 0; foreach (String planilha in planilhas) { PreencherDados_Excel(xlPackage, dados.Tables[index], planilha); index++; } file = new FileStream(path + fileName, FileMode.Create); xlPackage.Write(file); file.Close(); sucesso = true; } catch (Exception ex) { throw ex; } return sucesso; }
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; }
MemoryStream GenerateData() { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); var movies = (from m in db.Movies select m).ToList(); ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); IRow headerRow = sheet1.CreateRow(0); headerRow.CreateCell(0).SetCellValue("ID"); headerRow.CreateCell(1).SetCellValue("电影名"); headerRow.CreateCell(2).SetCellValue("发布日期"); headerRow.CreateCell(3).SetCellValue("类型"); headerRow.CreateCell(4).SetCellValue("价格"); for (int i = 0; i < movies.Count; i++) { IRow Row = sheet1.CreateRow(i + 1); Row.CreateCell(0).SetCellValue(movies[i].ID); Row.CreateCell(1).SetCellValue(movies[i].Title); Row.CreateCell(2).SetCellValue(movies[i].ReleaseDate); Row.CreateCell(3).SetCellValue(movies[i].Genre); Row.CreateCell(4).SetCellValue(movies[i].Price.ToString()); } hssfworkbook.Write(ms); return ms; }
public void SaveFile(string path, List<string> text) { var wb = new HSSFWorkbook(); var sheet = wb.CreateSheet("Dictionary"); int rowIndex = 0; var row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue("Word"); row.CreateCell(1).SetCellValue("Count"); rowIndex++; foreach(var str in text) { var first = str.Split(new char[] {' '}, StringSplitOptions.RemoveEmptyEntries)[0]; var second = str.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)[1]; row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(first); row.CreateCell(1).SetCellValue(second); rowIndex++; } using(var fileStr = new FileStream(path, FileMode.Create)) { wb.Write(fileStr); } }
public byte[] GetExcelBufferFromDataTable(DataTable dt) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet = hssfworkbook.CreateSheet("Sheet1"); sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { sheet.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } for (int i = 1; i <= dt.Rows.Count; i++) { sheet.CreateRow(i); for (int j = 0; j < dt.Columns.Count; j++) { sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i - 1][dt.Columns[j].ColumnName].ToString()); } } MemoryStream memstream = new MemoryStream(); try { hssfworkbook.Write(memstream); byte[] buffer = memstream.GetBuffer(); return buffer; } finally { memstream.Close(); } }
public static MemoryStream getExcel(DataTable dt, HttpContextBase content) { var book = new HSSFWorkbook(); ISheet sheet = book.CreateSheet("Sheet1"); 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++) { IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } //写入到客户端 var ms = new MemoryStream(); book.Write(ms); book = null; ms.Close(); ms.Dispose(); return ms; }
static void Main(string[] args) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ////create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file' //So we insert three sheet just like what Excel does hssfworkbook.CreateSheet("Sheet1"); hssfworkbook.CreateSheet("Sheet2"); hssfworkbook.CreateSheet("Sheet3"); hssfworkbook.CreateSheet("Sheet4"); ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false; ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false; //Write the stream data of workbook to the root directory FileStream file = new FileStream(@"test.xls", FileMode.Create); hssfworkbook.Write(file); file.Close(); }
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); }
//excel导出,最多只能导出65535条记录,没有考虑分sheet页,后续如果数据量较多再扩充 public static void writeExcel(DataTable dt, ExcelInfo ei, String[] colTitle, int[] colWidth) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ei.sheetName); HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0); for (int j = 0; j < colTitle.Length;j++) { //设置列宽 sheet.SetColumnWidth(j, colWidth[j] * 255); rowtitle.CreateCell(j).SetCellValue(colTitle[j]); } int i = 1; foreach (DataRow row in dt.Rows) { HSSFRow newrow = (HSSFRow)sheet.CreateRow(i); int col = 0; for (; col < dt.Columns.Count; col++) newrow.CreateCell(col).SetCellValue(Convert.ToString(row[col])); i++; } try { using (Stream stream = File.Open(ei.filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { workbook.Write(stream); } } catch (Exception e) { throw e; } }
/// <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 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); } }
public void ProcessRequest(HttpContext context) { string projectName = context.Request["pname"].Trim(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet projectSheet = workbook.CreateSheet("项目各报销项汇总"); HSSFSheet projectReimSummarySheet = workbook.CreateSheet("项目报销项金额汇总"); HSSFSheet projectSummarySheet = workbook.CreateSheet("项目报销总金额汇总"); List<ProjectCategory> queryCategoryList = new List<ProjectCategory>(); DalOperationAboutReimItem dalReimItem = new DalOperationAboutReimItem(); List<ReimItem> reimItemList; if (!string.IsNullOrWhiteSpace(projectName)) { reimItemList = dalReimItem.GetReimItems(projectName.Trim(), 0, 0); } else { reimItemList = dalReimItem.GetAllDistinctReimItems(); } List<ReimItem> reimItemSummaryList = dalReimItem.GetReimItemSummaryValues(projectName, 0, 0, null); List<ReimItem> projectReimSummaryList = dalReimItem.GetProjectReimItems(projectName, 0); SetProjectReimSheet(projectSheet, reimItemList); SetProjectReimSummarySheet(projectReimSummarySheet, reimItemSummaryList); SetProjectSummarySheet(projectSummarySheet, projectReimSummaryList); string fileName = "项目报销项汇总"; System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(fileName + ".xls"), System.IO.FileMode.Create); workbook.Write(file); file.Dispose(); ////插入值 FileInfo DownloadFile = new FileInfo(context.Server.MapPath(fileName + ".xls")); context.Response.Clear(); context.Response.ClearHeaders(); context.Response.Buffer = false; Encoding code = Encoding.GetEncoding("gb2312"); context.Response.ContentEncoding = Encoding.UTF8; context.Response.HeaderEncoding = code;//这句很重要 context.Response.ContentType = "application/octet-stream"; context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); context.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString()); context.Response.WriteFile(DownloadFile.FullName); if (File.Exists(context.Server.MapPath(fileName + ".xls"))) { File.Delete(context.Server.MapPath(fileName + ".xls")); } context.Response.Flush(); }
public static void ExportFile(DataSet ds, string filepath) { if(string.IsNullOrEmpty(filepath)) { return; } if(ds == null || ds.Tables.Count == 0) { return; } try { IWorkbook hssfworkbook = new HSSFWorkbook(); using (FileStream fs = File.Create(filepath))//path=mmm.xls; { foreach (DataTable table in ds.Tables) { ISheet sheet = hssfworkbook.CreateSheet(table.TableName); //head IRow firstRow = sheet.CreateRow(0); int cellIndex = 0; foreach (DataColumn col in table.Columns) { cellIndex = InsertCell(firstRow, cellIndex, col.ColumnName); } //rows int rowIndex = 1; foreach (DataRow dr in table.Rows) { cellIndex = 0; IRow row = sheet.CreateRow(rowIndex); for (int i = 0; i < table.Columns.Count; i++) { string val = ""; if(dr[i] is byte[]) { val = (dr[i] as byte[]).ToHexString(); } else { val = dr[i].ToString(); } cellIndex = InsertCell(row, cellIndex, val); } rowIndex++; } } hssfworkbook.Write(fs);//保存文件 } } catch (Exception) { //LogHelper } }
/** * Writes a spReadsheet to a <c>MemoryStream</c> and Reads it back * from a <c>ByteArrayStream</c>.<p/> * Useful for verifying that the serialisation round trip */ public static HSSFWorkbook WriteOutAndReadBack(HSSFWorkbook original) { using (MemoryStream baos = new MemoryStream(4096)) { original.Write(baos); return new HSSFWorkbook(baos); } }
public FileResult ExportExcel(HSSFWorkbook book, string fileName) { // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return File(ms, "application/vnd.ms-excel", string.Format("{0}.xls", fileName)); }
/// <summary> /// 把DataTable的数据写入Stream. /// </summary> /// <param name="stream"></param> /// <param name="dt"></param> public static void Write(Stream stream, DataTable dt) { HSSFWorkbook book = new HSSFWorkbook(); string sheetName = dt.TableName.IsNotEmpty() ? dt.TableName : "sheet1"; ISheet sheet = book.CreateSheet(sheetName); Write(sheet, dt); book.Write(stream); }
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 override void ExecuteResult(ControllerContext context) { var fs = new FileStream(context.HttpContext.Server.MapPath( @"\Content\UpdatePeople.xls"), FileMode.Open, FileAccess.Read); var wb = new HSSFWorkbook(fs, true); var sheet = wb.GetSheet("Sheet1"); var r = 1; foreach (var p in UpdatePeopleRows()) { var row = sheet.CreateRow(r++); var c = 0; row.CreateCell(c++).SetCellValue(p.PeopleId); row.CreateCell(c++).SetCellValue(p.Title); row.CreateCell(c++).SetCellValue(p.First); row.CreateCell(c++).SetCellValue(p.GoesBy); row.CreateCell(c++).SetCellValue(p.Last); row.CreateCell(c++).SetCellValue(p.Suffix); row.CreateCell(c++).SetCellValue(p.Email1); row.CreateCell(c++).SetCellValue(p.Email2); row.CreateCell(c++).SetCellValue(p.Gender); if (p.BirthDate.HasValue) row.CreateCell(c++).SetCellValue(p.BirthDate.Value); else row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank); if (p.Anniversary.HasValue) row.CreateCell(c++).SetCellValue(p.Anniversary.Value); else row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank); if (p.Joined.HasValue) row.CreateCell(c++).SetCellValue(p.Joined.Value); else row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank); row.CreateCell(c++).SetCellValue(p.Cell); row.CreateCell(c++).SetCellValue(p.Work); row.CreateCell(c++).SetCellValue(p.Member); if (p.Grade.HasValue) row.CreateCell(c++).SetCellValue(p.Grade.Value); else row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank); row.CreateCell(c++).SetCellValue(p.Marital); row.CreateCell(c++).SetCellValue(p.FamilyPos); row.CreateCell(c++).SetCellValue(p.AltName); row.CreateCell(c++).SetCellValue(p.Campus); row.CreateCell(c++).SetCellValue(p.School); row.CreateCell(c++).SetCellValue(p.Occupation); row.CreateCell(c++).SetCellValue(p.Employer); if (p.Deceased.HasValue) row.CreateCell(c++).SetCellValue(p.Deceased.Value); else row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank); } var Response = context.HttpContext.Response; Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment;filename=UpdatePeople.xls"); Response.Charset = ""; wb.Write(Response.OutputStream); }
public void Test48832() { HSSFWorkbook wb = new HSSFWorkbook(); // Starts empty Assert.IsNull(wb.DocumentSummaryInformation); Assert.IsNull(wb.SummaryInformation); // Add new properties wb.CreateInformationProperties(); Assert.IsNotNull(wb.DocumentSummaryInformation); Assert.IsNotNull(wb.SummaryInformation); // Set Initial values wb.SummaryInformation.Author = (/*setter*/"Apache POI"); wb.SummaryInformation.Keywords = (/*setter*/"Testing POI"); wb.SummaryInformation.CreateDateTime = DateUtil.GetJavaDate(12345); wb.DocumentSummaryInformation.Company = (/*setter*/"Apache"); // Save and reload MemoryStream baos = new MemoryStream(); wb.Write(baos); MemoryStream bais = new MemoryStream(baos.ToArray()); wb = new HSSFWorkbook(bais); // Ensure Changes were taken Assert.IsNotNull(wb.DocumentSummaryInformation); Assert.IsNotNull(wb.SummaryInformation); Assert.AreEqual("Apache POI", wb.SummaryInformation.Author); Assert.AreEqual("Testing POI", wb.SummaryInformation.Keywords); Assert.AreEqual(12345, DateUtil.GetExcelDate(wb.SummaryInformation.CreateDateTime.Value)); Assert.AreEqual("Apache", wb.DocumentSummaryInformation.Company); // Set some more, save + reload wb.SummaryInformation.Comments = (/*setter*/"Resaved"); baos = new MemoryStream(); wb.Write(baos); bais = new MemoryStream(baos.ToArray()); wb = new HSSFWorkbook(bais); // Check again Assert.IsNotNull(wb.DocumentSummaryInformation); Assert.IsNotNull(wb.SummaryInformation); Assert.AreEqual("Apache POI", wb.SummaryInformation.Author); Assert.AreEqual("Testing POI", wb.SummaryInformation.Keywords); Assert.AreEqual("Resaved", wb.SummaryInformation.Comments); Assert.AreEqual(12345, DateUtil.GetExcelDate(wb.SummaryInformation.CreateDateTime.Value)); Assert.AreEqual("Apache", wb.DocumentSummaryInformation.Company); }
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 void ProcessRequest(HttpContext context) { CommonFunction.CheckUser(); context.Response.CacheControl = "no-cache"; context.Response.ContentType = "text/plain"; string term = context.Request["term"]; string searchKey = context.Request["searchKey"].Trim(); string teacherType = context.Request["teacherType"].Trim(); DalOperationNorm dalnorm = new DalOperationNorm(); DataSet ds = dalnorm.getTeacherLoad(term, searchKey, teacherType); HSSFWorkbook workbook = new HSSFWorkbook(); for (int tableIndex = 0; tableIndex < ds.Tables.Count; tableIndex++) { HSSFSheet sheet = workbook.CreateSheet(ds.Tables[tableIndex].TableName); DataRowCollection drc = ds.Tables[tableIndex].Rows; for (int i = 0; i < ds.Tables[tableIndex].Columns.Count; i++) { sheet.CreateRow(0).CreateCell(i).SetCellValue(ds.Tables[tableIndex].Columns[i].ColumnName); } for (int i = 0; i < drc.Count; i++) { for (int j = 0; j < ds.Tables[tableIndex].Columns.Count; j++) { sheet.CreateRow(i + 1).CreateCell(j).SetCellValue(drc[i][j].ToString()); } } } string fileName = "norm"; System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(fileName + ".xls"), System.IO.FileMode.Create); workbook.Write(file); file.Dispose(); ////插入值 FileInfo DownloadFile = new FileInfo(context.Server.MapPath(fileName + ".xls")); context.Response.Clear(); context.Response.ClearHeaders(); context.Response.Buffer = false; Encoding code = Encoding.GetEncoding("gb2312"); context.Response.ContentEncoding = Encoding.UTF8; context.Response.HeaderEncoding = code;//这句很重要 context.Response.ContentType = "application/octet-stream"; context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); context.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString()); context.Response.WriteFile(DownloadFile.FullName); if (File.Exists(context.Server.MapPath(fileName + ".xls"))) { File.Delete(context.Server.MapPath(fileName + ".xls")); } context.Response.Flush(); }
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)); }
public FileResult Excel2() { MyElectrCheck_DBDataContext mdc = new MyElectrCheck_DBDataContext(); List <DataEliminate> list = new List <DataEliminate>(); if (Session["solvelist"] != null) { //将session转为集合 list = (List <DataEliminate>)Session["solvelist"]; } NPOI.HSSF.UserModel.HSSFWorkbook Book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = Book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("任务编号"); row1.CreateCell(1).SetCellValue("任务名称"); row1.CreateCell(2).SetCellValue("线路编号"); row1.CreateCell(3).SetCellValue("线路编号"); row1.CreateCell(4).SetCellValue("有无故障"); row1.CreateCell(5).SetCellValue("缺陷级别"); row1.CreateCell(6).SetCellValue("缺陷类型"); row1.CreateCell(7).SetCellValue("消缺时间"); row1.CreateCell(8).SetCellValue("发现时间"); row1.CreateCell(9).SetCellValue("缺陷描述"); for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].solveTaskCode); rowtemp.CreateCell(1).SetCellValue(list[i].solveTaskName); rowtemp.CreateCell(2).SetCellValue(list[i].lineCode); if (list[i].isBug == 1) { rowtemp.CreateCell(3).SetCellValue("有"); } else { rowtemp.CreateCell(3).SetCellValue("无"); } rowtemp.CreateCell(4).SetCellValue(list[i].bugLevelName); rowtemp.CreateCell(5).SetCellValue(list[i].bugLevelName); rowtemp.CreateCell(6).SetCellValue(list[i].bugTypeName); rowtemp.CreateCell(7).SetCellValue(list[i].finishTime.ToString()); rowtemp.CreateCell(8).SetCellValue(list[i].discoverTime.ToString()); rowtemp.CreateCell(9).SetCellValue(list[i].bugDesc); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); Book.Write(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); DateTime dt = DateTime.Now; string dateTime = dt.ToString("yyMMddHHmmssff"); string fileName = "查询结果" + dateTime + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
/// <summary> /// 导出excel /// </summary> /// <param name="response"></param> /// <param name="dt"></param> /// <param name="fileName"></param> /// <param name="sheetname"></param> public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell = null; NPOI.SS.UserModel.IFont font = book.CreateFont(); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "微软雅黑"; style.SetFont(font); for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { string strColDataType = dt.Columns[j].DataType.ToString(); if (strColDataType.Equals("System.Int32")) { int intValue = 0; int.TryParse(dt.Rows[i][j].ToString(), out intValue); row2.CreateCell(j).SetCellValue(intValue); } else if (strColDataType.Equals("System.String")) { row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } else if (strColDataType.Equals("System.Double")) { double dblValue = 0; double.TryParse(dt.Rows[i][j].ToString(), out dblValue); row2.CreateCell(j).SetCellValue(dblValue); } } } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls")); response.ContentType = "application/vnd.ms-excel"; //response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
//导出 dt,和表头都传 public Stream ExportExcel(DataTable dt, List <EColName> list) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); //组建表头 int index = 0; foreach (var i in list) { row1.CreateCell(index).SetCellValue(i.col); i.sortseq = index; index = index + 1; } //将数据逐步写入sheet1各个行 double d0 = 0; for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); for (int jj = 0; jj < list.Count; jj++) { string colname = (from EColName a in list where a.sortseq == jj select a.colname).ToArray()[0]; string coldata = dt.Rows[i][colname].ToString(); string dbtype = dt.Columns[colname].DataType.ToString(); if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double") { if (double.TryParse(coldata, out d0)) { d0 = Convert.ToDouble(coldata); } rowtemp.CreateCell(jj).SetCellValue(d0); } else { rowtemp.CreateCell(jj).SetCellValue(coldata); } } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); return(ms); }
public static MemoryStream CreateExcel2003Stream(DataTable dt, Dictionary <string, string> columnInfo) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); WriteData(ref sheet1, dt, columnInfo, string.Empty); System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); return(ms); }
//导出 只传dt public Stream ExportExcel(DataTable dt) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); //组建表头 DataColumnCollection dcc = dt.Columns; int index = 0; foreach (DataColumn i in dcc) { row1.CreateCell(index).SetCellValue(i.ColumnName); index = index + 1; } double d0 = 0; //将数据逐步写入sheet1各个行 for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); for (int jj = 0; jj < dcc.Count; jj++) { string dbtype = dcc[jj].DataType.ToString(); string coldata = dt.Rows[i][dcc[jj].ColumnName].ToString(); if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double") { if (double.TryParse(coldata, out d0)) { d0 = Convert.ToDouble(coldata); } rowtemp.CreateCell(jj).SetCellValue(d0); } else { rowtemp.CreateCell(jj).SetCellValue(coldata); } } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); return(ms); }
private void ExcelExport() { string sheet1Title = "Sheet1"; NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(sheet1Title); NPOI.SS.UserModel.IRow headerRow4Sheet1 = sheet1.CreateRow(0); ExcelCellStyle cellStyle = new ExcelCellStyle(book); ICell cell;//初始化ICell准备设值 #region 设定Sheet1的头行 cell = headerRow4Sheet1.CreateCell(0); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户ID"); cell = headerRow4Sheet1.CreateCell(1); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户名"); cell = headerRow4Sheet1.CreateCell(2); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户地址"); #endregion /*0行已经设为表头行,内容从第1行开始设值*/ int sheet1RowID = 1; Customer[] listCustomer = client.GetCustomer(""); foreach (Customer customer in listCustomer) { IRow r = sheet1.CreateRow(sheet1RowID); cell = r.CreateCell(0); cell.SetCellValue(customer.customerID); cell.CellStyle = cellStyle.style; cell = r.CreateCell(1); cell.SetCellValue(customer.customerName); cell.CellStyle = cellStyle.style; cell = r.CreateCell(2); cell.SetCellValue(customer.customerAddress); cell.CellStyle = cellStyle.style; sheet1RowID += 1;//每做一个循环就到下一行设置 } /*以下是做自适应宽度*/ ChangeStyle(book, sheet1); /*最后作输出流导出文件*/ MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("客户列表信息", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); Response.End(); book = null; ms.Close(); ms.Dispose(); }