c#动态导出

1.把List集合转成DataTable

当没有实体对象时

//列名集合
 string[] strs = obj["selectStr"].ToString().Split(',');
 //ToDataTableTow(集合,列名);
 DataTable dt = iDataTableExtensions.ToDataTableTow(DocumentList, strs); 
 string  path = iExcel.ExportExecl(cols, dt);
 iExcel.ExportExcel(path);

    /// 将List转换成DataTable
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="data"></param>
    /// <returns></returns>
    /// 
    public static class iDataTableExtensions
    {
        /// <summary>
        /// list转dt
        /// </summary>
        /// <param name="list">list数据</param>
        /// <param name="strs">需要的列名</param>
        /// <returns></returns>        
        public static DataTable ToDataTableTow(IList list, string[] strs)
        {
            DataTable result = new DataTable("Datas");
            if (list.Count > 0)
            {
                //循环创建列头
                foreach (var str in strs)
                {
                    result.Columns.Add(str, Type.GetType("System.String"));
                }
                //循环创建内容
                foreach (object item in list)
                {
                    JObject jobj = JObject.FromObject(item);
                    ArrayList tempList = new ArrayList();
                    //循环需要的列名
                    foreach (var str in strs)
                    {
                        object obj = jobj[str];
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }
        
    }
}

2.导出帮助类

 public class excelCell
    {
        public string location;
        public object value;

        public excelCell(string _location, object _value)
        {
            location = _location;
            value = _value;
        }
    }


    public class Cols
    {
        public Cols() { }
        public Cols(string Caption, string ColumnName,int Width)
        {
            this.Caption = Caption;
            this.ColumnName = ColumnName;
            this.Width = Width;
        }

        public string Caption { set; get; }
        public string ColumnName { set; get; }
        public int Width { set; get; }
    }

    public class iExcel
    {
        public static string ExportExecl(Cols[] cols, DataTable dt)
        {
            string dir = iPublic.ProjectPath(string.Format("/Upload/{0}/", "xlsx"));
            if (!Directory.Exists(dir)) Directory.CreateDirectory(dir);
            string path = string.Format("/Upload/{0}/{0}", "xlsx") + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            //匹配标题
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                for (int j = 0; j < cols.Length; j++)
                {
                    if (dt.Columns[i].ColumnName == cols[j].ColumnName)
                    {
                        dt.Columns[i].Caption = cols[j].Caption;
                        sheet.SetColumnWidthInPixels(i + 1, cols[j].Width);
                        break;
                    }
                }
            }

            sheet.InsertDataTable(dt, true, 1, 1);
            sheet.Rows[0].Style.Color = System.Drawing.Color.LightBlue;
            sheet.Rows[0].RowHeight = 18;
            sheet.Rows[0].Style.Font.Size = 12;

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.SaveToStream(ms, FileFormat.Version2010);
            }
            workbook.SaveToFile(iPublic.ProjectPath(path), ExcelVersion.Version2007);
            return dir.Replace("Upload\\xlsx\\", "") + path;
        }

        /// <summary>
        /// 生成Excel
        /// </summary>
        public static void CreateExcel(DataTable dt, string path)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);//创建工作表

            #region 标题
            IRow row = sheet.CreateRow(0);//在工作表中添加一行
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);//在行中添加一列
                cell.SetCellValue(dt.Columns[i].ColumnName);//设置列的内容	 
            }
            #endregion

            #region 填充数据
            for (int i = 1; i <= dt.Rows.Count; i++)//遍历DataTable行
            {
                DataRow dataRow = dt.Rows[i - 1];
                row = sheet.CreateRow(i);//在工作表中添加一行

                for (int j = 0; j < dt.Columns.Count; j++)//遍历DataTable列
                {
                    ICell cell = row.CreateCell(j);//在行中添加一列
                    cell.SetCellValue(dataRow[j].ToString());//设置列的内容	 
                }
            }
            #endregion

            #region 输出到Excel
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);

            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                byte[] bArr = ms.ToArray();
                fs.Write(bArr, 0, bArr.Length);
                fs.Flush();
            }
            #endregion

        }


        public static void ExportXml(DataTable dtData, string path)
        {
            XmlElement xeItem;
            XmlDocument xmlDoc = new XmlDocument();

            XmlElement xeRoot = xmlDoc.CreateElement("root");

            foreach (DataRow dr in dtData.Rows)
            {
                xeItem = xmlDoc.CreateElement("item");
                for (int i = 0; i < dtData.Columns.Count; i++)
                {
                    DataColumn dc = dtData.Columns[i];
                    xeItem.SetAttribute(dc.ColumnName.Trim().Replace(" ", "_").Replace("(", "-").Replace(")", "").Replace("（", "-").Replace("）", ""), dr[i].ToString().Trim());
                }

                xeRoot.AppendChild(xeItem);
            }

            xmlDoc.AppendChild(xeRoot);
            xmlDoc.Save(path);
        }

        public static void ExportExcel(string path)
        {
            System.IO.FileInfo filet = new System.IO.FileInfo(path);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filet.Name));
            // 添加头信息，指定文件大小，让浏览器能够显示下载进度 
            HttpContext.Current.Response.AddHeader("Content-Length", filet.Length.ToString());
            // 指定返回的是一个不能被客户端读取的流，必须被下载 
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            // 把文件流发送到客户端 
            HttpContext.Current.Response.WriteFile(filet.FullName);
            // 停止页面的执行 
            HttpContext.Current.Response.End();
        }
        public static void ExportExcel(Page xpage, DataTable dtData, string ExcelTitle)
        {
            xpage.Response.Clear();
            xpage.Response.Buffer = true;
            xpage.Response.ContentEncoding = System.Text.Encoding.UTF8;
            xpage.Response.HeaderEncoding = System.Text.Encoding.UTF8;
            xpage.Response.Charset = "UTF-8";
            xpage.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(ExcelTitle)) + ".xls");
            xpage.Response.ContentEncoding = Encoding.GetEncoding("UTF-8");//设置输出流为简体中文 
            xpage.Response.ContentType = "application/ms-excel";
            StringWriter oStringWriter = new StringWriter();

            HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
            GridView gv = new GridView();
            gv.DataSource = dtData;
            gv.DataBind();
            gv.RenderControl(oHtmlTextWriter);
            xpage.Response.Write(oStringWriter.ToString());
            xpage.Response.End();
        }


        public static DataTable ReadExcelSheets(string PathExcel)
        {
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + PathExcel))
            {
                conn.Open();
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                conn.Close();
                return dtSheetName;
            }
        }
        public static DataTable ExcelGet(string PathExcel, string strSQL)
        {
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + PathExcel))
            {
                DataSet ds = new DataSet();
                OleDbDataAdapter oleAdMaster = new OleDbDataAdapter(strSQL, conn);
                oleAdMaster.Fill(ds, "dsExcel");
                oleAdMaster.Dispose();
                conn.Close();

                return ds.Tables[0];
            }
        }
        public static void ExcelExec(string PathExcel, string strSQL)
        {
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + PathExcel))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        public static DataSet ExportCsvIn(string PathCsv)
        {
            string filePath = PathCsv.Substring(0, PathCsv.LastIndexOf(@"\"));
            string fileName = PathCsv.Substring(PathCsv.LastIndexOf(@"\") + 1);

            DataSet ds = new DataSet();
            string strConnCSV = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;'";
            System.Data.OleDb.OleDbConnection objConn = new OleDbConnection(strConnCSV);
            objConn.Open();
            string strSql = "SELECT * FROM " + fileName;

            System.Data.OleDb.OleDbDataAdapter odbcCSVDataAdapter = new OleDbDataAdapter(strSql, strConnCSV);
            odbcCSVDataAdapter.Fill(ds);
            return ds;
        }
        public static DataSet ExportExcelIn(string PathExcel)
        {
            return ExportExcelIn(PathExcel, "Sheet1$");
        }
        public static DataSet ExportExcelIn(string PathExcel, string SheetName)
        {
            string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"", PathExcel);

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            conn.Open();

            string strExcel = "select * from [" + SheetName + "]";
            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "table1");

            return ds;
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        private static DataTable ExcelToDataTable(ISheet sheet, bool isFirstRowColumn)
        {
            var data = new DataTable(); int startRow = 0;

            try
            {
                if (sheet != null)
                {
                    var firstRow = sheet.GetRow(0);
                    if (firstRow == null)
                        return data;
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                    startRow = isFirstRowColumn ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;

                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        //.StringCellValue;
                        var column = new DataColumn(Convert.ToChar(((int)'A') + i).ToString());
                        if (isFirstRowColumn)
                        {
                            var columnName = firstRow.GetCell(i).StringCellValue;
                            column = new DataColumn(columnName);
                        }
                        data.Columns.Add(column);
                    }


                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j, MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                else throw new Exception("Don not have This Sheet");

                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 读取excel的数据(第一个工作表　
        /// </summary>
        /// <param name="PathExcel">excel文件地址</param>　
        /// <returns></returns>
        public static DataTable ExportExcelIn_ByNPOI(string PathExcel)
        {
            return ExportExcelIn_ByNPOI(PathExcel, 0);
        }
        /// <summary>
        /// 读取excel的数据　
        /// </summary>
        /// <param name="PathExcel">excel文件地址</param>
        /// <param name="SheetIndex">工作表序号，从第0开始</param>
        /// <returns></returns>
        public static DataTable ExportExcelIn_ByNPOI(string PathExcel, int SheetIndex)
        {
            using (FileStream fs = File.OpenRead(PathExcel))
            {
                HSSFWorkbook wk = new HSSFWorkbook(fs);   //把xls文件中的数据写入wk中

                if (SheetIndex >= wk.NumberOfSheets)
                {
                    throw new Exception("读取的excel表不对");
                }

                ISheet sheet = wk.GetSheetAt(SheetIndex);   //读取当前表数据
                DataTable dt = ExcelToDataTable(sheet, true);
                return dt;
            }
        }
        /// <summary>
        /// 读取excel的数据,并替换数据，再生成excel
        /// </summary>
        /// <param name="PathExcel">excel文件地址</param>
        /// <param name="SheetIndex">工作表序号，从第0开始</param>
        /// <returns></returns>
        public static string ExportExcel_ByNPOI(string PathExcel, string PathExcelTo, int SheetIndex, List<excelCell> ReplaceData)
        {
            try
            {

                using (FileStream fs = File.OpenRead(PathExcel))
                {
                    HSSFWorkbook wk = new HSSFWorkbook(fs);   //把xls文件中的数据写入wk中
                    if (SheetIndex >= wk.NumberOfSheets)
                    {
                        throw new Exception("读取的excel表不对");
                    }

                    ISheet sheet = wk.GetSheetAt(SheetIndex);   //读取当前表数据 
                    foreach (excelCell cells in ReplaceData)
                    {
                        string[] cell = cells.location.Split(',');

                        if (sheet.GetRow(cell[0].ConvertToIntSafe()) == null) sheet.CreateRow(cell[0].ConvertToIntSafe());
                        if (sheet.GetRow(cell[0].ConvertToIntSafe()).GetCell(cell[1].ConvertToIntSafe()) == null)
                            sheet.GetRow(cell[0].ConvertToIntSafe()).CreateCell(cell[1].ConvertToIntSafe());

                        sheet.GetRow(cell[0].ConvertToIntSafe()).GetCell(cell[1].ConvertToIntSafe()).SetCellValue(cells.value.ToStringSafe());
                    }

                    FileStream file = new FileStream(PathExcelTo, FileMode.OpenOrCreate);
                    wk.Write(file);
                    file.Flush();
                    file.Close();

                    return string.Empty;
                }
            }
            catch (Exception ex)
            {
                return "error:" + ex.Message;
            }
        }
    }

3.已经有Excel模板怎么导出

1.把Excel文档保存在web项目中一个文件夹里面

        /// <summary>
        /// 文件下载
        /// </summary>
        /// <param name="type">下载文件类型  1.工人离场模板 2.基本信息模板</param>
        /// <returns></returns>
        [HttpPost]
        [Route("FileDownload")]
        [Description("文件下载")]
        public HttpResponseMessage FileDownload(int type)
        {
            string path = null;
            switch (type)
            {
                case 1:
                    path = "/ExportFile/批量导入工人离场模板文件 .xls";
                    break;
                case 2:
                    path = "/ExportFile/批量导入模板文件.xls";
                    break;
                default:
                    break;
            }
            string fileDownloadPath = HostingEnvironment.MapPath(path);
            iExcel.ExportExcel(fileDownloadPath);
            return ReturnMessageSuccess();
        }