public void ExportXls(string filepath) { IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); Export(workbook, filepath); workbook.Close(); }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="dt"></param> /// <param name="filePath"></param> 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(); try { 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; } } catch (Exception ex) { throw new Exception(ex.Message); } finally { book.Close(); book = null; } } }
public ActionResult Export(string data_date) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); YG_GLJYTABLE gliy = new YG_GLJYTABLE(); gliy.DATA_DATE = data_date; PageInfo pageInfo = new PageInfo(); pageInfo.limit = 5000; pageInfo.page = 1; JsonResult js = Json(GLJYTABLEService.GetListByFilter(gliy, pageInfo)); string json = Newtonsoft.Json.JsonConvert.SerializeObject(js); JObject jo = JObject.Parse(json); IEnumerable <dynamic> dynamics = jo.Values().Values().Values().Children(); int count = dynamics.Count(); dynamic[] arrays = dynamics.ToArray(); //给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 < count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(arrays[i]["DATA_DATE"].ToString().Split(' ')[0]); rowtemp.CreateCell(1).SetCellValue(arrays[i]["INSIDERNM"].ToString()); rowtemp.CreateCell(2).SetCellValue(arrays[i]["CERTTYPE"].ToString()); rowtemp.CreateCell(3).SetCellValue(arrays[i]["CERT_NO"].ToString()); rowtemp.CreateCell(4).SetCellValue(arrays[i]["SHOUXIN"].ToString()); rowtemp.CreateCell(5).SetCellValue(arrays[i]["LOAN"].ToString()); rowtemp.CreateCell(6).SetCellValue(arrays[i]["XINYONG_LOAN"].ToString()); rowtemp.CreateCell(7).SetCellValue(arrays[i]["CRET_CARD"].ToString()); rowtemp.CreateCell(8).SetCellValue(arrays[i]["INTER_FINANCE"].ToString()); rowtemp.CreateCell(9).SetCellValue(arrays[i]["BILL_ACCEPT"].ToString()); rowtemp.CreateCell(10).SetCellValue(arrays[i]["BILL_DISCOUNTED"].ToString()); rowtemp.CreateCell(11).SetCellValue(arrays[i]["DANBAO"].ToString()); rowtemp.CreateCell(12).SetCellValue(arrays[i]["ZHIYA"].ToString()); rowtemp.CreateCell(13).SetCellValue(arrays[i]["GRANT_BALANCE"].ToString()); } string path = Server.MapPath("/Download/GLJYTABLE/"); //获取保存目录的物理路径 DateTime dt = DateTime.Now; string dateTime = dt.ToString("yyyyMMddHHmmssfff"); string FileName = "关联交易情况表" + dateTime + ".xls"; string saveFileName = path + FileName; try { using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write)) { book.Write(fs); //写入文件 book.Close(); //关闭 } } catch (Exception ex) { Response.Write("<script>alert('错误信息:" + ex.Message + "');</script>"); } return(Content("/Download/GLJYTABLE/" + FileName)); }
/// <summary> /// 获取EXCEL数组 /// </summary> /// <param name="dt"></param> public static byte[] GetExcelFileByte(DataSet ds) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //设置样式 NPOI.SS.UserModel.ICellStyle style1 = book.CreateCellStyle(); NPOI.SS.UserModel.IFont font1 = book.CreateFont(); style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; font1.IsBold = true; style1.SetFont(font1); NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle(); style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; byte[] fileByte = null; for (int k = 0; k < ds.Tables.Count; k++) { try { DataTable dt = ds.Tables[k]; if (null != dt && dt.Rows.Count > 0) { if (dt.TableName == null || dt.TableName == "") { dt.TableName = "Sheet1"; } NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName); sheet.DefaultColumnWidth = 20; 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); row.Cells[i].CellStyle = style1; } 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])); row2.Cells[j].CellStyle = style2; } } } } catch (Exception ex) { throw new Exception(ex.Message); } } try { // 写入到客户端 using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); fileByte = ms.GetBuffer(); //using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) //{ // byte[] data = ms.ToArray(); // fs.Write(data, 0, data.Length); // fs.Flush(); //} } } catch (Exception) { throw; } finally { if (null != book) { book.Close(); book = null; } } return(fileByte); }
public void Dispose() { obook.Close(); GC.Collect(); }