让多个DataTable中的数据导入到一个excel中sheet中

一、需求

　　　　遇到这样一个需求，主管要对多个客服的工作质量审核，要求审核完成后，对应的客服的好中差评的审核结果对应到一个表格的不同sheet单元表中。

二、思路

　　　　先声明一个DataSet，把每个客服的DataTable通过DataSet的Add方法来添加进来,并且把每个表的名字命名为客服的名字（通过TableName方法实现），然后通过遍历DataSet的每个表以及每个表的行列来添加到Excel里面，在把数据保存到服务器上，然后把Exce文件下载下来，最后删除Excel文件。

三、准备

　　　　运用到一个dll，在设计导出类之前，先引入dll（我会把dll 共享一下，注意其版本，我用的是10.0），下载地址：

　　　　https://files.cnblogs.com/lzhp/microsoft.office.interop.excel%E5%90%84%E4%B8%AA%E7%89%88%E6%9C%AC.7z

　　　　，以及命名空间：　　　　　　　　　　　　　　　　　　　　　　　　

using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

 

四、主要代码

　　　　只要列出导出Excel的方法

    #region Excel导出多个客服，不同的客服在不同的sheet里

    public static void ToManySheetExcel(DataSet ds, string strExcelFileName, System.Web.UI.Page page)
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        string path = "";
        if (excel == null)
        {
            MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
            return;
        }
        try
        {
          excel.Visible = false;
            //设置禁止弹出保存和覆盖的询问提示框
            excel.DisplayAlerts = false;
          excel.AlertBeforeOverwriting = true;
            //增加一个工作簿
            Workbook book = excel.Workbooks.Add(true);
            //添加工作表         其中ds.Tables.Count.ToString()为要创建的sheet的个数
            Worksheet sheets = (Microsoft.Office.Interop.Excel.Worksheet)
                book.Worksheets.Add(Missing.Value, Missing.Value, Convert.ToInt32(ds.Tables.Count.ToString()), Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
            //开始遍历ds中的各个dataTable
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                System.Data.DataTable table = ds.Tables[i];
                //HttpContext.Current.Response.Write(ds.Tables[0].Rows[0][0].ToString() + "<p/>");
                //获取一个工作表
                Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
                int rowIndex = 1;
                int colIndex = 0;
                //为各个sheet添加列名
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    sheet.Cells[1, colIndex] = col.ColumnName;
                }
                //开始添加数据
                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        //控制excel格式
                        String typeName = row[col.ColumnName].GetType().ToString();
                        sheet.Cells[rowIndex, colIndex] = typeCheckAdd(row[col.ColumnName].ToString(), typeName);
                    }
                }
                //将各个sheet的名字改为datatable.TableName
                sheet.Name =ds.Tables[i].TableName.ToString();
            }

            //删除多余Sheet
            for (int g = 1; g <= book.Worksheets.Count; g++)
            {
                Worksheet sheet = book.Worksheets[g] as Worksheet;
                if (Convert.ToInt32(sheet.Name.Length.ToString())>5&&sheet.Name.Substring(0, 5) == "Sheet")
                {
                    sheet.Delete();
                    g--;
                }
            }
            path = page.Server.MapPath("../") + @"9_Tool/" + strExcelFileName + ".xls";

            //book.Save();
            book.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            book.Close();// 注意关闭，如果不关闭，执行的时间会出现 另一个进程在使用此文件
            excel.Quit();
            excel = null;
            GC.Collect();

           //准备把文件转化成二进制文件下载下来并删除服务器上的那个Excel文件　　            
            FileStream fs = new FileStream(path, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            File.Delete(path);
            page.Response.ContentType = "application/octet-stream";
            //通知浏览器下载文件而不是打开
            page.Response.AddHeader("Content-Disposition", "attachment;  filename=" + HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmssffff") + "客服审评表.xls"));
            page.Response.BinaryWrite(bytes);
            page.Response.Flush();
            page.Response.End();


        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
    }
    #region 用来处理excel的异常显示，有必要的话可以可以添加
    public static String typeCheckAdd(String cellContent, String strType)
    {
        String cellContentAdd;
        
        switch (strType)
        {
            case "System.Int64":
                cellContentAdd = "'" + cellContent;
                break;
            case "System.DateTime":
                cellContentAdd = (Convert.ToDateTime(cellContent)).ToString("MM-dd"); 
                break;
            default:
                cellContentAdd = cellContent;
                break;
        }
        return cellContentAdd;
    }


    #endregion

五、总结

　　　　对于导出Excel，本方法还是比较慢的，还有下载时间，只能对于比较小的文件来使用，还有一种方法是操作Xml，具体实现方法可以先在网上搜索一下，有时间补上。

　　　　希望本文能给你带来帮助，谢谢阅读，欢迎大家指正。