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();
}