对Excel表格数据的导出

一：有Excel文件模板的导出
（1）判断模板文件是否存在
1.先通过Server.MapPath() 将相对的路径转为实际的物理路径
2.然后在通过IO流判断文件是否存在 例：System.IO.File.Exists()
（2）使用文件流打开模板文件System.IO.File.Open()
1.打开Excel模板，得到一个工作簿 例：NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(templateStream);
2.通过GetSheetAt(0)获取第一个工作表 NumberOfSheets判断有没有工作表
3. PhysicalNumberOfRows 获取的是物理行数，也就是不包括那些空行（隔行）的情况。
4. 通过CreateCellStyle().构建单元格样式
例：style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;水平居中
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;垂直居中
5.设置边框为实线 例：style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
6.通过遍历循环填充查询出来的数据
(3)以流的方式返回
例：String name=”文件名称”+”.xls”;
(4)把Excel转化为流
1. 定义内存流
例：MemoryStream Book=new MemoryStream();定义内存流
2. 将工作簿写入内存流
例：book.Write(Book);将工作簿写入内存流
3.最后调用Seek()（偏移量，游标位置）方法：获取文件流的长度
(5)返回文件类型
File(fileStream要发送到响应的流,contentType内容类型（MIME 类型）,fileDownloadName浏览器中显示的文件下载对话框内要使用的文件名。)
例：return File(Book, “application/vnd.ms-excel”, “文件名称”)
从页面请求到控制器
//导出Excel(数据)
function exportExcel() {
var startEndDate = $("#startEndDate").val();
window.open("@Url.Content("~/OthersMaintenance/ExportARdata/ExportARData?startEndDate=")" + startEndDate);

控制器里查数据
public ActionResult ExportARData(string startEndDate)
{
#region 筛选导出数据
var query = from tbOrder in myModel.B_Order
join tbPNR in myModel.B_PNR on tbOrder.PNRID equals tbPNR.PNRID
join tbUser in myModel.S_User on tbOrder.operatorID equals tbUser.userID
join tbUserGroup in myModel.S_UserGroup on tbUser.userGroupID equals tbUserGroup.userGroupID
orderby tbOrder.orderID
select new ExportARdataVo
{
orderNo = tbOrder.orderNo,//订单编号
payTime = tbOrder.payTime,//支付时间(筛选数据)
payTimeStr = tbOrder.payTime.ToString(),//支付时间(字符串)
totalPrice = tbOrder.totalPrice,//总票价
agencyFee = tbOrder.agencyFee,//代理费
payMoney = tbOrder.payMoney,//支付金额
userGroup = tbUserGroup.userGroupNumber,//用户组号
jobNumber = tbUser.jobNumber,//工号
PNR = tbPNR.PNRNo,//PNR编号
orderStatus = tbOrder.orderStatus//订单状态
};
//判断是否选择时间段
if (!string.IsNullOrEmpty(startEndDate))
{
startEndDate = startEndDate.Replace(" - “, “~”);
string[] strs = startEndDate.Split(’~’);//根据 " - “分割字符串
if (strs.Length == 2)
{
DateTime dtStart = Convert.ToDateTime(strs[0]);
DateTime dtEnd = Convert.ToDateTime(strs[1]);
query = query.Where(m => m.payTime >= dtStart && m.payTime <= dtEnd);
}
}
List list = query.ToList();
#endregion
#region 根据模板文件的Excel导出
//==1-检查模板文件是否存在
// Server.MapPath 将相对的路径转为实际的物理路径
string templatePath = Server.MapPath(”_{/Document/ARDataTemplate.xls");//("}/位置/文件名字”)
//判断模板是否存在
if (!System.IO.File.Exists(templatePath))
{
//如果不存在，就返回失败信息
return Content(“导出失败，请联系网站管理员”);
}
//==2-使用NPOI打开模板Excel
//=2.1-使用文件打开模板文件
FileStream templateStream = System.IO.File.Open(templatePath, FileMode.Open);
//=2.2-使用NPOI打开模板Excel 得到一个工作簿
NPOI.HSSF.UserModel.HSSFWorkbook excelBookTemplate = new NPOI.HSSF.UserModel.HSSFWorkbook(templateStream);
//==3-打开模板所在第一个工作表
NPOI.SS.UserModel.ISheet sheet = excelBookTemplate.GetSheetAt(0);
//构建单元格样式
NPOI.SS.UserModel.ICellStyle style= excelBookTemplate.CreateCellStyle();
//==4-设置标题，如果筛选时间段不为空就拼接上筛选时间段
if (!string.IsNullOrEmpty(startEndDate))
{
//startEndDate（时间段）
//提取模板标题
NPOI.SS.UserModel.IRow rowTitle = sheet.GetRow(0);
rowTitle.GetCell(0).SetCellValue("订单数据 " + startEndDate);
}
//==5-往模板中填充数据
//=5.1-设置数据单元格的样式
//水平垂直居中对齐
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
//设置边框为实线
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
//==5.2-开始填充数据
int index = 2;//目前这个模板数据开始填充数据的行索引为2
//遍历查询出的数据 填充Excel单元格
for (int i = 0; i < list.Count(); i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(index);//给sheet添加一行
row.Height = 22 * 20;//设置行高
//设置单元格数据
row.CreateCell(0).SetCellValue(i+1);
row.CreateCell(1).SetCellValue(list[i].orderNo);
row.CreateCell(2).SetCellValue(list[i].payTime.ToString());
row.CreateCell(3).SetCellValue(list[i].totalPrice.ToString());
row.CreateCell(4).SetCellValue(list[i].agencyFee.ToString());
row.CreateCell(5).SetCellValue(list[i].payMoney.ToString());
row.CreateCell(6).SetCellValue(list[i].userGroup.ToString());
row.CreateCell(7).SetCellValue(list[i].jobNumber.ToString());
row.CreateCell(8).SetCellValue(list[i].PNR.ToString());
//设置单元格样式
for (int j = 0; j < row.Cells.Count; j++)
{
row.GetCell(j).CellStyle = style;
}
index++;
}
//以流的方式返回
string fileName = “订单信息” + DateTime.Now.ToString(“yyyy-MM-dd-HH-mm-ss-ffff”) + “.xls”;
//把Excel转化为流，输出
MemoryStream BookStream = new MemoryStream();//定义内存流
excelBookTemplate.Write(BookStream);//将工作薄写入内存流
BookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek（偏移量，游标位置）方法：获取文件流的长度
/*参数:File(fileStream要发送到响应的流,
contentType内容类型（MIME 类型）,
fileDownloadName浏览器中显示的文件下载对话框内要使用的文件名。) */
//“application/vnd.ms-excel”,fileName格式，名称
return File(BookStream,“application/vnd.ms-excel”,fileName);
#endregion
} }

二：没有模板的导出

1. 创建Excel对象
   （1） 创建一个工作簿 例：NPOI.HSSF.UserModel.HSSFWorkbook book
   （2） 在工作簿里创建一个工作表 例：NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
2. 设置样式
   （1） ICellStyle 声明单元格样式
   （2） IFont 声明字体样式
   （3） SetFont() 把字体样式加入到单元格
3. 合并单元格（第几行开始，到第几行结束，第几列开始，到第几列结束）
   例：sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 6));
4. 创建第一行（表头）
   （1） 给sheet添加第一行的头部标题 CreateRow(“索引”) 创建行
   例：NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);第一行，索引为0

（2） 设置行高
例：row1.Height = 22 * 20;
5. 创建单元格并设置单元格的值
（1） CreateCell() 创建单元格
（2） SetCellValue() 设置单元格的值
例：row1.CreateCell(0).SetCellValue(“序号”);
6. 通过for循环遍历查询出来的数据
7. 设置列宽为自动适应
例：sheet.AutoSizeColumn();
8. 最后在通过把Excel转化为文件流，输出
（1） 定义内存流 MemoryStream
（2） 将工作簿写入内存流 Write()
（3） 最后调用Seek()（偏移量，游标位置）方法：获取文件流的长度 Seek()
9. return File(fileStream要发送到响应的流,contentType内容类型（MIME 类型）,fileDownloadName浏览器中显示的文件下载对话框内要使用的文件名。)
10.
function exportExcel() {
var startEndDate = $("#startEndDate").val();
window.open("@Url.Content("~/OthersMaintenance/ExportLKdata/ExportLKDATA?startEndDate=")" + startEndDate);

    }

public ActionResult ExportLKDATA(string startEndDate)
{
#region 数据查询
var query = from tabPNRPassenger in myModel.B_PNRPassenger
join tbPassengerType in myModel.S_PassengerType
on tabPNRPassenger.passengerTypeID equals tbPassengerType.passengerTypeID
join tbCertificatesType in myModel.S_CertificatesType
on tabPNRPassenger.certificatesTypeID equals tbCertificatesType.certificatesTypeID
join tbPNR in myModel.B_PNR
on tabPNRPassenger.PNRID equals tbPNR.PNRID
orderby tabPNRPassenger.PNRPassengerID
select new PassengerVo
{
passengerName = tabPNRPassenger.passengerName,
passengerType = tbPassengerType.passengerType,
certificatesType = tbCertificatesType.certificatesType,
certificatesCode = tabPNRPassenger.certificatesCode,
contactName = tbPNR.contactName,
contactPhone = tbPNR.contactPhone,
createTime = tbPNR.createTime.Value,
};
//判断是否选择时间段
if (!string.IsNullOrEmpty(startEndDate))
{
startEndDate = startEndDate.Replace(" - ", “~”);
string[] strs = startEndDate.Split(’~’);//根据 " - "分割字符串
if (strs.Length == 2)
{
DateTime dtStart = Convert.ToDateTime(strs[0]);
DateTime dtEnd = Convert.ToDateTime(strs[1]);
query = query.Where(o => o.createTime >= dtStart && o.createTime <= dtEnd);
}
}
var list = query.ToList();
#endregion
#region 导出Excel表头
//==1、声明一个Workbook对象
//NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
HSSFWorkbook workbook = new HSSFWorkbook();
//==2、创建工作表 Sheet 并设置名称
//创建工作表
ISheet sheet = workbook.CreateSheet();
//设置工作表名称
workbook.SetSheetName(0, “旅客信息”);
//==3、设置标题 合并单元格、设置样式
//=3.1、创建第一行 索引0，设置行高
//创建行
IRow row0 = sheet.CreateRow(0);
//=3.2、创建一个单元格 索引0
//创建单元格
ICell cell0 = row0.CreateCell(0);
//string strDate = DateTime.Now.ToString(“yyyy-MM-dd HH:mm:ss”);
//string strTime = “旅客信息”+strDate;
string strTime = “旅客信息”;
if (!string.IsNullOrEmpty(startEndDate))
{
strTime = strTime + " " + startEndDate;
}
//赋值
cell0.SetCellValue(strTime);
//=3.2.1、创建一个格样式
//声明并创建样式
ICellStyle cellStyle_Title = workbook.CreateCellStyle();
//水平居中
cellStyle_Title.Alignment = HorizontalAlignment.Center;
//垂直居中
cellStyle_Title.VerticalAlignment = VerticalAlignment.Center;
//声明字体
IFont font_title = workbook.CreateFont();
//设置字体颜色
font_title.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
//加粗
font_title.Boldweight = (Int16)FontBoldWeight.Bold;
//字体大小
font_title.FontHeightInPoints = 18;
//设置单元格字体
cellStyle_Title.SetFont(font_title);
//=3.2、合并单元格（第几行开始，到第几行结束，第几列开始，到第几列结束）
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));
//=3.2.2、设置单元格样式
cell0.CellStyle = cellStyle_Title;
//==4、设置表头
//=4.1-创建一行 索引 1
//给sheet添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(1);
//设置行高
row1.Height = 22 * 20;
//=4.2-创建单元格 并设置值
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(“联系人电话”);
//=4.3-创建表头的样式
ICellStyle cellStyle_header = workbook.CreateCellStyle();//声明样式
cellStyle_header.Alignment = HorizontalAlignment.Center;//水平居中
cellStyle_header.VerticalAlignment = VerticalAlignment.Center;//垂直居中
//设置背景颜色
cellStyle_header.FillPattern = FillPattern.SolidForeground;
cellStyle_header.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Aqua.Index;
//设置边框线为实线
cellStyle_header.BorderBottom = BorderStyle.Thin;
cellStyle_header.BorderLeft = BorderStyle.Thin;
cellStyle_header.BorderRight = BorderStyle.Thin;
cellStyle_header.BorderTop = BorderStyle.Thin;
//声明字体
IFont font_header = workbook.CreateFont();
//加粗
font_header.Boldweight = (Int16)FontBoldWeight.Bold;
//字体大小
font_header.FontHeightInPoints = 10;
//加入单元格
cellStyle_header.SetFont(font_header);
//=4.4-给每个单元格添加样式
for (int i = 0; i < row1.Cells.Count; i++)
{
row1.GetCell(i).CellStyle = cellStyle_header;
}
#endregion
#region 导出Excel数据
//==5-遍历查询到的数据，设置Excel表格数据
//=5.1-创建数据内部部分 单元格样式
ICellStyle cellstyle_value = workbook.CreateCellStyle();//声明样式
cellstyle_value.Alignment = HorizontalAlignment.Center;//水平居中
cellstyle_value.VerticalAlignment = VerticalAlignment.Center;//垂直居中
//设置边框线为实线
cellstyle_value.BorderLeft = BorderStyle.Thin;
cellstyle_value.BorderTop = BorderStyle.Thin;
cellstyle_value.BorderRight = BorderStyle.Thin;
cellstyle_value.BorderBottom = BorderStyle.Thin;
//=5.2-遍历数据，创建数据部分行列
for (int i = 0; i < list.Count; i++)
{
//5.2.1-创建行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 2);//给sheet添加一行
row.Height = 22 * 20;//设置行高
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].passengerName);
row.CreateCell(2).SetCellValue(list[i].passengerType);
row.CreateCell(3).SetCellValue(list[i].certificatesType);
row.CreateCell(4).SetCellValue(list[i].certificatesCode);
row.CreateCell(5).SetCellValue(list[i].contactName);
row.CreateCell(6).SetCellValue(list[i].contactPhone);
//5.2.3-给每个单元格添加样式
for (int j = 0; j < row.Cells.Count; j++)
{
row.GetCell(j).CellStyle = cellstyle_value;
}
}
//==6-设置列宽为自动适应
for (int i = 0; i < sheet.GetRow(1).Cells.Count; i++)
{
sheet.AutoSizeColumn(i);
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) * 17 / 10);
}
#endregion
//==7-将excel通过文件流返回到浏览 浏览器下载
string fileName = “旅客信息” + DateTime.Now.ToString(“yyyy-MM-dd-HH-mm-ss-ffff”) + “.xls”;
//把Excel转化为文件流，输出
MemoryStream BookStream = new MemoryStream();//定义文件流
workbook.Write(BookStream);//将工作薄写入文件流
BookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek（偏移量，游标位置）方法：获取文件流的长度
return File(BookStream, “application/vnd.ms-excel”, fileName); // 文件类型/文件名称/

    }