第一种
//数据
IEnumerable<Book> result = _BookBO.QueryList(condition);
DataTable dt = new DataTable();
dt.Columns.Add("编号");
dt.Columns.Add("书名");
dt.Columns.Add("作者");
dt.Columns.Add("价格");
foreach (Book item in result)
{
DataRow newRow = dt.NewRow();
newRow["编号"] = item.Account;
newRow["书名"] = item.Account;
newRow["作者"] = item.Account;
newRow["价格"] = item.Account;
dt.Rows.Add(newRow);
}
StringBuilder sbData = new StringBuilder();
sbData.Append("<table><tr>");
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
sbData.Append("<td>" + ds.Tables[0].Columns[i].ColumnName + "</td>");
}
sbData.Append("</tr>");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sbData.Append("<tr>");
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
sbData.Append("<td style='vnd.ms-excel.numberformat:@'>" + ds.Tables[0].Rows[i][j].ToString() + "</td>");
}
sbData.Append("</tr>");
}
sbData.Append("</table>");
string fileName = "书籍清单_" + DateTime.Now.ToString("yyyy-MM-dd");//文件的名称
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
StringWriter sw = new StringWriter();
sw.WriteLine("<html xmlns:x="urn:schemas-microsoft-com:office:excel">");
sw.WriteLine("<head>");
sw.WriteLine("<!--[if gte mso 9]>");
sw.WriteLine("<xml>");
sw.WriteLine(" <x:ExcelWorkbook>");
sw.WriteLine(" <x:ExcelWorksheets>");
sw.WriteLine(" <x:ExcelWorksheet>");
sw.WriteLine(" <x:Name>书籍清单</x:Name>");
sw.WriteLine(" <x:WorksheetOptions>");
sw.WriteLine(" <x:Print>");
sw.WriteLine(" <x:ValidPrinterInfo />");
sw.WriteLine(" </x:Print>");
sw.WriteLine(" </x:WorksheetOptions>");
sw.WriteLine(" </x:ExcelWorksheet>");
sw.WriteLine(" </x:ExcelWorksheets>");
sw.WriteLine("</x:ExcelWorkbook>");
sw.WriteLine("</xml>");
sw.WriteLine("<![endif]-->");
sw.WriteLine("</head>");
sw.WriteLine("<body>");
sw.WriteLine(sbData.ToString());
sw.WriteLine("</body>");
sw.WriteLine("</html>");
// 设置编码和附件格式
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AppendHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8).ToString() + ".xls");
curContext.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
// 返回客户端
curContext.Response.Write(sw);
sw.Close();
curContext.Response.Flush();
curContext.Response.End();
第二种：NPOI
protected void btnExport_Click(object sender, EventArgs e)
{
try
{
DataSet dsResult = _BookBO.QueryList(condition...);
NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook();
HSSFPalette palette = excel.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((short)8, (byte)48, (byte)84, (byte)150);
HSSFColor hssFColor = palette.FindColor((byte)48, (byte)84, (byte)150);
NPOI.SS.UserModel.ICellStyle styleRowHeader = excel.CreateCellStyle();
styleRowHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleRowHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleRowHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleRowHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleRowHeader.FillForegroundColor = hssFColor.GetIndex();
styleRowHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
NPOI.SS.UserModel.IFont fontRowHeader = excel.CreateFont();
fontRowHeader.IsItalic = false;
fontRowHeader.FontHeightInPoints = 10;
fontRowHeader.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
fontRowHeader.Boldweight = short.MaxValue;
styleRowHeader.SetFont(fontRowHeader);
NPOI.SS.UserModel.ICellStyle styleRowData = excel.CreateCellStyle();
styleRowData.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
styleRowData.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleRowData.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleRowData.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleRowData.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
IEnumerable<IGrouping<int, DataRow>> gourpResult = dsResult.Tables[0].Rows.Cast<DataRow>().GroupBy(r => Convert.ToDateTime(r["PublishTime"]).Year);
foreach (IGrouping<int, DataRow> groupItem in gourpResult)
{
NPOI.SS.UserModel.ISheet sheet = excel.CreateSheet(groupItem.Key + "年");
NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0);
rowHeader.CreateCell(0).CellStyle = styleRowHeader;
rowHeader.CreateCell(1).CellStyle = styleRowHeader;
rowHeader.CreateCell(2).CellStyle = styleRowHeader;
rowHeader.CreateCell(3).CellStyle = styleRowHeader;
rowHeader.CreateCell(4).CellStyle = styleRowHeader;
sheet.GetRow(0).GetCell(0).SetCellValue("编号");
sheet.GetRow(0).GetCell(1).SetCellValue("书名");
sheet.GetRow(0).GetCell(2).SetCellValue("作者");
sheet.GetRow(0).GetCell(3).SetCellValue("价格");
sheet.GetRow(0).GetCell(4).SetCellValue("出版时间");
for (int i = 0; i < groupItem.Count(); i++)
{
DataRow rowItem = groupItem.ElementAt(i);
NPOI.SS.UserModel.IRow rowData = sheet.CreateRow(i + 1);
rowData.CreateCell(0).CellStyle = styleRowData;
rowData.CreateCell(1).CellStyle = styleRowData;
rowData.CreateCell(2).CellStyle = styleRowData;
rowData.CreateCell(3).CellStyle = styleRowData;
rowData.CreateCell(4).CellStyle = styleRowData;
sheet.GetRow(i + 1).GetCell(0).SetCellValue(rowItem["No"].ToString());
sheet.GetRow(i + 1).GetCell(1).SetCellValue(rowItem["BookName"].ToString());
sheet.GetRow(i + 1).GetCell(2).SetCellValue(rowItem["Author"].ToString());
sheet.GetRow(i + 1).GetCell(3).SetCellValue(rowItem["Price"].ToString());
sheet.GetRow(i + 1).GetCell(4).SetCellValue(Convert.ToDateTime(rowItem["PublishTime"]).ToString("yyyy-MM-dd"));
}
//for (int columnNum = 0; columnNum < 13; columnNum++)
//{
// int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
// for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行
// {
// IRow currentRow = sheet.GetRow(rowNum);
// ICell currentCell = currentRow.GetCell(columnNum);
// int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
// if (columnWidth < length + 1)
// {
// columnWidth = length + 1;
// }//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
// }
// if (columnWidth > 255)
// columnWidth = 255;
// sheet.SetColumnWidth(columnNum, columnWidth * 256);
//}
for (int i = 0; i < 5; i++)
{
sheet.AutoSizeColumn(i);
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
excel.Write(ms);
Response.ContentType = "application/vnd.ms-excel";
#region FireFox&Safari浏览器不编码,其余皆编码
if (Request.ServerVariables["http_user_agent"].ToString().ToLower().IndexOf("firefox") != -1 || Request.ServerVariables["http_user_agent"].ToString().ToLower().IndexOf("apple") != -1)
{
Response.AddHeader("Content-Disposition", String.Format("attachment; filename=书籍清单{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
}
else
{
Response.AddHeader("Content-Disposition", String.Format("attachment; filename={0}", HttpUtility.UrlEncode(String.Format("书籍清单{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")), System.Text.Encoding.UTF8)));
}
#endregion
Response.BinaryWrite(ms.ToArray());
excel = null;
ms.Close();
ms.Dispose();
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(GetType(), "ErrorTips", "<script type='text/javascript'>art.dialog({ content: '导出异常！" + ex.Message + "',ok: function() { this.close(); },cancel: true});</script>");
}
}
