C#.NET导出Excel

//数据
IEnumerable 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("");
for (int i = 0; i 0].Columns.Count; i++)
{
    sbData.Append("");
}
sbData.Append("");
for (int i = 0; i 0].Rows.Count; i++)
{
    sbData.Append("");
    for (int j = 0; j 0].Columns.Count; j++)
    {
        sbData.Append("");
    }
    sbData.Append("");
}
sbData.Append("



" + ds.Tables[0].Columns[i].ColumnName + "





" + ds.Tables[0].Rows[i][j].ToString() + "



");
string fileName = "书籍清单_" + DateTime.Now.ToString("yyyy-MM-dd");//文件的名称
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
StringWriter sw = new StringWriter();
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine("");
sw.WriteLine(sbData.ToString());
sw.WriteLine("");
sw.WriteLine("");
// 设置编码和附件格式 
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("");
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
// 返回客户端 
curContext.Response.Write(sw);
sw.Close();
curContext.Response.Flush();
curContext.Response.End();

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;


        IEnumerableint, DataRow>> gourpResult = dsResult.Tables[0].Rows.Cast().GroupBy(r => Convert.ToDateTime(r["PublishTime"]).Year);
        foreach (IGroupingint, 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 )
            {
                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 //{
            //    int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度  
            //    for (int rowNum = 0; rowNum //在这一列上循环行  
            //    {
            //        IRow currentRow = sheet.GetRow(rowNum);
            //        ICell currentCell = currentRow.GetCell(columnNum);
            //        int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度  
            //        if (columnWidth //        {
            //            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", "" + ex.Message + "‘,ok: function() { this.close(); },cancel: true});");
    }
}