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
}