//文件流转换
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");
}