//创建不同版本的文件， excel2003版 或2007+版

 

public IWorkbook BuildWorkbook(DataTable dt, string file)

    {

        IWorkbook book;

        string fileExt = Path.GetExtension(file).ToLower();

        if (fileExt == ".xlsx")

        { book = new XSSFWorkbook(); }

        else if (fileExt == ".xls")

        { book = new HSSFWorkbook(); }

        else { book = null; }

        //var book = new HSSFWorkbook();

        ISheet sheet1 = book.CreateSheet("Sheet1");

        ISheet sheet2 = book.CreateSheet("Sheet2");

        //填充数据

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

        {

            if (i < 65536)

            {

                IRow drow = sheet1.CreateRow(i);

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                    ICell cell = drow.CreateCell(j, CellType.String);

                    cell.SetCellValue(dt.Rows[i][j].ToString());

                }

            }

            if (i >= 65536) //再创建一个sheet

            {

                IRow drow = sheet2.CreateRow(i - 65536);

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                    ICell cell = drow.CreateCell(j, CellType.String);

                    cell.SetCellValue(dt.Rows[i][j].ToString());

                }

            }

        }

        //自动列宽

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

        {

            sheet1.AutoSizeColumn(i, true);

            sheet2.AutoSizeColumn(i, true);

        }

        return book;

    }

 

//导出至excel文件

public void ExportExcel(DataTable dt, string fileName = "")

    {

        生成Excel

        IWorkbook book = BuildWorkbook(dt, fileName);

       

       //web 下载

        if (fileName == "")

            fileName = string.Format("{0:yyyyMMddHHmmssffff}", DateTime.Now) + ".xls";

 

        fileName = fileName.Trim();

        HttpResponse httpResponse = HttpContext.Current.Response;

        httpResponse.Clear();

        httpResponse.Buffer = true;

        httpResponse.Charset = Encoding.UTF8.BodyName;

        httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);

        httpResponse.ContentEncoding = Encoding.UTF8;

        httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";

        book.Write(httpResponse.OutputStream);

        httpResponse.End(); 

    }