Asp.Net Mvc导出Excel

//文件流转换

public static Stream DecompressStream(Stream sourceStream)

{

MemoryStream responseStream = new MemoryStream();

using (System.IO.Compression.GZipStream compressedStream = new System.IO.Compression.GZipStream(responseStream, System.IO.Compression.CompressionMode.Compress, true))

{

byte[] buffer = new byte[sourceStream.Length];

int checkCounter = sourceStream.Read(buffer, 0, buffer.Length);

if (checkCounter != buffer.Length) throw new ApplicationException();

compressedStream.Write(buffer, 0, buffer.Length);

}

responseStream.Position = 0;

return responseStream;

}

#region 汇款查询导出

public ActionResult DownLoadRemit(string sh, string hy, string fw, string dh, string hybh, string sz, string xjzh, string bh, string start, string end, string way)

{

//创建Excel文件的对象

NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

//添加一个sheet

NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

//设置单元的宽度

sheet1.SetColumnWidth(0, 20 * 256);

sheet1.SetColumnWidth(1, 20 * 256);

sheet1.SetColumnWidth(2, 20 * 256);

sheet1.SetColumnWidth(3, 20 * 420);

sheet1.SetColumnWidth(4, 20 * 256);

sheet1.SetColumnWidth(5, 20 * 256);

sheet1.SetColumnWidth(6, 20 * 320);

sheet1.SetColumnWidth(7, 20 * 256);

sheet1.SetColumnWidth(8, 20 * 256);

sheet1.SetColumnWidth(9, 20 * 320);

sheet1.SetColumnWidth(10, 20 * 256);

sheet1.SetColumnWidth(11, 20 * 256);

sheet1.SetColumnWidth(12, 20 * 256);

/// 练习合并单元格

sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 4, 4));

//获取list数据

List<RemitBrowseVm> listRainInfo = remit.DCRemit();//Bll建一个方法，查询需要的列名

//给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("财务备注");

//将数据逐步写入sheet1各个行

for (int i = 0; i < listRainInfo.Count; i++)

{

NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);

rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].hyBh.ToString());

rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].HkName.ToString());

rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].HkYt.ToString());

rowtemp.CreateCell(3).SetCellValue(listRainInfo[i].Zfstate.ToString());

rowtemp.CreateCell(4).SetCellValue(listRainInfo[i].shoujiPhone.ToString());

rowtemp.CreateCell(5).SetCellValue(listRainInfo[i].HkMoney.ToString());

rowtemp.CreateCell(6).SetCellValue(listRainInfo[i].Hkfs.ToString());

rowtemp.CreateCell(7).SetCellValue(listRainInfo[i].QurenFs.ToString());

rowtemp.CreateCell(8).SetCellValue(listRainInfo[i].HkYt.ToString());

rowtemp.CreateCell(9).SetCellValue(listRainInfo[i].Hkdate.ToString());

rowtemp.CreateCell(10).SetCellValue(listRainInfo[i].ZfDate == null ? "" : listRainInfo[i].ZfDate.ToString());

rowtemp.CreateCell(11).SetCellValue(listRainInfo[i].FukuanQs.ToString() == null ? "" : listRainInfo[i].FukuanQs.ToString());

rowtemp.CreateCell(12).SetCellValue(listRainInfo[i].Hkremark == null ? "" : listRainInfo[i].Hkremark.ToString());

}

//创建内存流

System.IO.MemoryStream ms = new System.IO.MemoryStream();

book.Write(ms);

ms.Seek(0, SeekOrigin.Begin);

//调用压缩流方法

Stream NewStream = DecompressStream(ms);

//生成压缩文件，下载

return File(NewStream, "application/zip", "汇款查询" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls.zip");

}