导出Excel文件 NPOI导出Excel文件 集合转datatable DataTable导出excel GridView导出excel

代码如下： 
datatable转为Excel文件或文件流的方法，已经 GridView导出excel 文件流的方法

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.UI.WebControls;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
/*----------------------------------------------------------------
* 描述：excel导出工具类
* 作 者 ：谢佳祥_Alvin
* 修改时间 ：2020年8月3日
----------------------------------------------------------------*/
namespace prjEEDLims.Report
{
    public static class ReportTool
    {
        /// <summary>
        /// 将DataTable导出Excel
        /// </summary>
        /// <returns></returns>
        public static string ExportDataTable(ref MemoryStream ms, DataTable dt, int type = 0, string path = "")
        {
            try
            {
                //创建Excel文件的对象
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                //添加一个sheet
                NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
                //给sheet1添加第一行的头部标题
                NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
                int i = 0;
                //自动换行给内容较多的单元格用的样式
                ICellStyle styleTitle = book.CreateCellStyle();
                styleTitle.WrapText = true;//自动换行
                styleTitle.Alignment = HorizontalAlignment.CenterSelection;
                //设置标题列
                foreach (DataColumn column in dt.Columns)
                {
                    row1.CreateCell(i).SetCellValue(column.ColumnName);
                    i++;
                }
                int j = 0;
                //把DataTable写入到Excel
                foreach (DataRow dr in dt.Rows)
                {
                    i = 0;
                    NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1);
                    for (; i < dt.Columns.Count; i++)
                    {
                        //判断是数字还是字符串的格式导入
                        double vsNum;
                        if (double.TryParse(dr[i].ToString(), System.Globalization.NumberStyles.Float, System.Globalization.NumberFormatInfo.InvariantInfo, out vsNum))
                            rowtemp.CreateCell(i).SetCellValue(vsNum);
                        else rowtemp.CreateCell(i).SetCellValue(dr[i].ToString());
                    }
                    rowtemp.Cells[3].CellStyle = styleTitle;
                    rowtemp.HeightInPoints = 50;
                    j++;
                }
                最后加入函数列（我这里是做统计）
                //NPOI.SS.UserModel.IRow rowtemp1 = sheet1.CreateRow(j + 1);
                //for (int k = 0; k < dt.Columns.Count; k++)
                //{
                //    rowtemp1.CreateCell(k).SetCellValue("");
                //}
                对需要统计的列添加函数
                //rowtemp1.Cells[5].SetCellValue("总 计");
                //rowtemp1.Cells[6].SetCellFormula("sum(G2:G" + (dt.Rows.Count + 1) + ")");
                //rowtemp1.Cells[8].SetCellFormula("sum(I2:I" + (dt.Rows.Count + 1) + ")");
                //rowtemp1.Cells[10].SetCellFormula("sum(K2:K" + (dt.Rows.Count + 1) + ")");
                //rowtemp1.Cells[11].SetCellFormula("sum(L2:L" + (dt.Rows.Count + 1) + ")");
                //rowtemp1.Cells[14].SetCellFormula("sum(O2:O" + (dt.Rows.Count + 1) + ")");
                if (type == 0)
                {
                    //设置列宽
                    sheet1.SetColumnWidth(3, 40 * 256);
                    sheet1.AutoSizeColumn(4);
                    sheet1.AutoSizeColumn(0);
                    sheet1.DefaultColumnWidth = 8;
                    MemoryStream ms1 = new MemoryStream();
                    book.Write(ms1);
                    ms1.Seek(0, SeekOrigin.Begin);
                    ms = ms1;
                }
                else
                {
                    FileStream fs = new FileStream(path, FileMode.Create);
                    book.Write(fs);
                    fs.Close();
                    ms = null;
                }
                return "成功";
            }
            catch (Exception ex)
            {
                ms = null;
                return ex.ToString();
            }
        }
        /// <summary>    
        /// 将集合类转换成DataTable    
        /// </summary>    
        /// <param name="list">集合</param>    
        /// <returns></returns>    
        public static DataTable ToDataTable(IList list)
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();

                foreach (PropertyInfo pi in propertys)
                {
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }
                foreach (object t in list)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(t, null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }
        /// <summary>
        /// 将GridView表格转为excel文件流
        /// </summary>
        /// <param name="dgv"></param>
        /// <returns></returns>
        public static MemoryStream GridToExcel( GridView dgv)
        {
                    
             HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1");
            HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
                headCell.SetCellValue(dgv.Columns[i].HeaderText);
            }
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
                for (int j = 0; j < dgv.Columns.Count; j++)
                {
                    HSSFCell cell = (HSSFCell)row.CreateCell(j);
                    if (dgv.Rows[i].Cells[j].Text == "")
                    {
                        
                        if ( (dgv.Rows[i].Cells[j].Controls[1] as Label)!=null)
                        {
                            cell.SetCellValue((dgv.Rows[i].Cells[j].Controls[1] as Label).Text.Trim());
                        }
                        if ((dgv.Rows[i].Cells[j].Controls[1] as TextBox) != null)
                        {
                            cell.SetCellValue((dgv.Rows[i].Cells[j].Controls[1] as TextBox).Text.Trim());
                        }

                    }
                    else
                    {
                      
                            cell.SetCellValue(dgv.Rows[i].Cells[j].Text.ToString());
                      
                    }

                }

            }
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            MemoryStream ms1 = new MemoryStream();
            wb.Write(ms1);
            ms1.Seek(0, SeekOrigin.Begin);
            return ms1;
            
        }
    }
}

下面是将文件流导出文件的方法：
 

   MemoryStream buffer = ReportTool.GridToExcel(gvOpenProject);
            Response.Clear();
            Response.AppendHeader("Content-Disposition", "attachment; filename=" +_g.ReportNmae+ "_" + DateTime.Now.ToString("yyMMddss") + "_" + Session["UID"].ToString() + ".xls");
            Response.ContentType = "application/octet-stream";
            Response.BinaryWrite(buffer.ToArray());
            Response.OutputStream.Flush();
            Response.OutputStream.Close();
            Response.Flush();
            Response.End();