DataTable 导出Excel

1、引用：NPOI.dll 文件
使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。
NPOI使用手册：https://pan.baidu.com/s/1c1QjDba

2、导出Excel
调用方法：

DataTableToExcel.ExportByWeb(dt, null, true, "test.xls");

具体实现代码：

public class DataTableToExcel{    #region  导出excel    /// <summary>    /// DataTable导出到Excel的MemoryStream    /// </summary>    /// <param name="dtSource">源DataTable</param>    /// <param name="strHeaderText">表头文本</param>    private static MemoryStream Export(DataTable dtSource)    {        //1        HSSFWorkbook workbook = new HSSFWorkbook();//创建一个新的excel        ISheet sheet = workbook.CreateSheet();//创建sheet页        //2        #region 右击文件 属性信息        {            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();            dsi.Company = "zrf";            workbook.DocumentSummaryInformation = dsi;            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();            si.Author = "zrf"; //填加xls文件作者信息            si.CreateDateTime = DateTime.Now;            workbook.SummaryInformation = si;        }        #endregion        //3 设置样式        ICellStyle dateStyle = workbook.CreateCellStyle();// Sheet样式           dateStyle.Alignment = HorizontalAlignment.Center;//设置单元格的样式：水平对齐居中          dateStyle.VerticalAlignment = VerticalAlignment.Center;//设置单元格样式：垂直对齐居中          IDataFormat format = workbook.CreateDataFormat();        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");        //4 获取字符数        int[] arrColWidth = new int[dtSource.Columns.Count];//取得列宽   标题 占字符数        foreach (DataColumn item in dtSource.Columns)        {            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;        }        //单元格内容占的字符数大于  标题  占字符数  就修改 标题数组的值        for (int i = 0; i < dtSource.Rows.Count; i++)        {            for (int j = 0; j < dtSource.Columns.Count; j++)            {                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;                if (intTemp > arrColWidth[j])                {                    arrColWidth[j] = intTemp;                }            }        }        //5  循环遍历DataTable 往Excel sheet页面 赋值        int rowIndex = 0;        int count = 0;        foreach (DataRow row in dtSource.Rows)        {            //6            #region 新建表，填充表头，填充列头，样式            if (rowIndex == 65535 || rowIndex == 0)            {                if (rowIndex != 0)                {                    sheet = workbook.CreateSheet();                }                #region 列头及样式                {                    IRow headerRow = sheet.CreateRow(0);　//设置第一行为Header                    headerRow.HeightInPoints = 15;                    //样式                    ICellStyle headStyle = workbook.CreateCellStyle();                    headStyle.Alignment = HorizontalAlignment.Center;                    IFont font = workbook.CreateFont();                    font.FontHeightInPoints = 10;                    font.Boldweight = 700;                    headStyle.SetFont(font);                    foreach (DataColumn column in dtSource.Columns)                    {                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                        //设置列宽                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);                    }                }                #endregion                rowIndex = 1;            }            #endregion            #region 7 填充内容            IRow dataRow = sheet.CreateRow(rowIndex);//创建数据行            dataRow.HeightInPoints = 15;            foreach (DataColumn column in dtSource.Columns)            {                ICell newCell = dataRow.CreateCell(column.Ordinal);//创建单元格                string drValue = row[column].ToString();                newCell.CellStyle = dateStyle;                switch (column.DataType.ToString())                {                    case "System.String"://字符串类型                                                    newCell.SetCellValue(drValue);//单元格赋值                        break;                    case "System.DateTime"://日期类型                        DateTime dateV;                        DateTime.TryParse(drValue, out dateV);                        newCell.SetCellValue(dateV);//单元格赋值                        newCell.CellStyle = dateStyle;//格式化显示                        break;                    case "System.Boolean"://布尔型                        bool boolV = false;                        bool.TryParse(drValue, out boolV);                        newCell.SetCellValue(boolV);//单元格赋值                        break;                    case "System.Int16"://整型                    case "System.Int32":                    case "System.Int64":                    case "System.Byte":                        int intV = 0;                        int.TryParse(drValue, out intV);                        newCell.SetCellValue(intV);//单元格赋值                        break;                    case "System.Decimal"://浮点型                    case "System.Double":                        double doubV = 0;                        double.TryParse(drValue, out doubV);                        newCell.SetCellValue(doubV);//单元格赋值                        break;                    case "System.DBNull"://空值处理                        newCell.SetCellValue("");                        break;                    default:                        newCell.SetCellValue("");//单元格赋值                        break;                }            }            #endregion            //8、合并行            DataRow[] drrr = dtSource.Select("班级='" + row["班级"] + "'");            if (drrr != null && rowIndex >= count)            {                count = drrr.Count()+ rowIndex;                //CellRangeAddress四个参数：起始行、结束行、起始列、结束列                  sheet.AddMergedRegion(new CellRangeAddress(rowIndex, count-1, 0, 0));            }            rowIndex++;        }        using (MemoryStream ms = new MemoryStream())        {            workbook.Write(ms);            ms.Flush();            ms.Position = 0;            return ms;        }    }    /// <summary>    /// web页面导出Excel    /// </summary>    /// <param name="dtSource">源数据</param>    /// <param name="TableHead">Excel文件的表头和源数据的表头对应关系表</param>    /// <param name="displayOther">是否导出没有在对应关系表中的列，false不导出，true导出</param>    /// <param name="strFileName">导出的excel文件名字</param>    public static void ExportByWeb(DataTable dtSource, List<DictionaryEntry> TableHead, bool displayOther, string strFileName)    {        HttpContext curContext = HttpContext.Current;        // 设置编码和附件格式        curContext.Response.ContentType = "application/vnd.ms-excel";        curContext.Response.ContentEncoding = Encoding.UTF8;        curContext.Response.Charset = "UTF-8";        if (string.IsNullOrEmpty(strFileName))            strFileName = DateTime.Now.ToString("yyyyMMddHHmmssffff.xls");        #region        if (curContext.Request.UserAgent.ToLower().IndexOf("firefox") > -1)        {            curContext.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", strFileName));        }        else        {            curContext.Response.AppendHeader("Content-Disposition",           "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));        }        #endregion        if (TableHead == null)        {            TableHead = new List<DictionaryEntry>();        }        DataTable dt = GetTable(dtSource, TableHead, displayOther);        Byte[] bytes = Export(dt).GetBuffer();        curContext.Response.BinaryWrite(bytes);        curContext.Response.End();    }    /// <summary>    ///     /// </summary>    /// <param name="dtSource"></param>    /// <param name="TableHead"></param>    /// <param name="displayOther"></param>    /// <returns></returns>    private static DataTable GetTable(DataTable dtSource, List<DictionaryEntry> TableHead, bool displayOther)    {        if (displayOther)        {            foreach (DataColumn col in dtSource.Columns)            {                foreach (DictionaryEntry dic in TableHead)                {                    if (dic.Key.ToString() == col.ColumnName)                    {                        col.ColumnName = dic.Value.ToString();                        break;                    }                }            }            return dtSource;        }        else        {            DataTable dt = new DataTable();            foreach (DictionaryEntry dic in TableHead)            {                dt.Columns.Add(dic.Value.ToString());            }            foreach (DataRow dr in dtSource.Rows)            {                DataRow newRow = dt.NewRow();                foreach (DictionaryEntry dic in TableHead)                {                    newRow[dic.Value.ToString()] = dr[dic.Key.ToString()];                }                dt.Rows.Add(newRow);            }            return dt;        }    }    #endregion}