DataSet中多表导出到同一Excel的不同sheet中

1.封装代码如下

 

using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace ExcelHelper
{
    public class DataTableShowLinkSettingsModel
    {
        private bool _IsShowLink = false; 
        public bool IsShowLink
        {
            get
            {
                return _IsShowLink;
            }
            set
            {
                _IsShowLink = value;
            }
        }
        private int[] _ShowLinkColumns = null;
        public int[] ShowLinkColumns
        {
            get
            {
                return _ShowLinkColumns;
            }
            set
            {
                _ShowLinkColumns = value;
            }
        }
    }
    public class ExcelHelper
    {
        public void DownExcelFromServer(string fileURL, string fileName)
        {
            HttpContext curContext = System.Web.HttpContext.Current;
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
            curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
            curContext.Response.WriteFile(fileURL);
            curContext.Response.Flush();
            curContext.Response.End();

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataSet">要导出的数据来源</param>
        /// <param name="fileName">导出的Excel名称</param>
        /// <param name="saveDirectoryName">要保存到服务器上文件夹的名称</param>
        /// <param name="deleteOldFile">指示是否删除旧文件</param>
        /// [特别说明] 当指定DataTable某列加入链接时，需在数据源给DataTable增设两个扩展属性  如下格式：
        ///    DataTableShowLinkSettingsModel dsm=new DataTableShowLinkSettingsModel();
        ///    dsm.IsShowLink = true;//设置该表是否显示超链接
        ///    dsm.ShowLinkColumns = new int[] { 2};//设置哪一列显示为超链接
        ///    dt2.ExtendedProperties.Add("DataTableShowLinkSettings", dsm);//放入扩展属性
        public void DataSetToLocalExcel(DataSet dataSet, string fileName, string saveDirectoryName, bool deleteOldFile)
        {

            //设置导出文件在服务器上的文件夹
            saveDirectoryName = string.Empty;
            deleteOldFile = true;
            string exportDir = string.Empty;
            string directoryPath = string.Empty;
            string rootPath = HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath);
            if (string.IsNullOrEmpty(saveDirectoryName))
            {
                directoryPath = rootPath + "ExcelFile";
            }
            else
            {
                directoryPath = rootPath + saveDirectoryName;
            }
            if (!Directory.Exists(directoryPath))
            {
                Directory.CreateDirectory(directoryPath);
            }
            exportDir = directoryPath;
            //设置文件在服务器上的路径
            string outputPath = exportDir + "\\" + fileName;

            if (deleteOldFile)
            {
                if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }
            }
            // Create the Excel Application object
            Application excelApp = new Application();

            // Create a new Excel Workbook
            Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

            int sheetIndex = 0;

            // Copy each DataTable
            foreach (System.Data.DataTable dt in dataSet.Tables)
            {

                // Copy the DataTable to an object array
                object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                // Copy the column names to the first row of the object array
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    rawData[0, col] = dt.Columns[col].ColumnName;
                }

                // Copy the values to the object array
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    for (int row = 0; row < dt.Rows.Count; row++)
                    {
                        rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
                    }
                }

                // Calculate the final column letter
                string finalColLetter = string.Empty;
                string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                int colCharsetLen = colCharset.Length;

                if (dt.Columns.Count > colCharsetLen)
                {
                    finalColLetter = colCharset.Substring(
                        (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                }

                finalColLetter += colCharset.Substring(
                        (dt.Columns.Count - 1) % colCharsetLen, 1);

                // Create a new Sheet
                Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
                    excelWorkbook.Sheets.get_Item(++sheetIndex),
                    Type.Missing, 1, XlSheetType.xlWorksheet);

                excelSheet.Name = dt.TableName;

                // Fast data export to Excel
                string excelRange = string.Format("A1:{0}{1}",
                    finalColLetter, dt.Rows.Count + 1);

                excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

                // Mark the first row as BOLD
                ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
                #region 设置超链接
                DataTableShowLinkSettingsModel dsm=dt.ExtendedProperties["DataTableShowLinkSettings"]==null?null:(DataTableShowLinkSettingsModel)dt.ExtendedProperties["DataTableShowLinkSettings"];
                if (dsm != null)
                {
                    bool flag = dsm.IsShowLink;
                    int[] addHyperlinksColumnsList = dsm.ShowLinkColumns;
                    if (flag)
                    {
                        if (addHyperlinksColumnsList != null && addHyperlinksColumnsList.Length > 0)
                        {
                            int rowsCount = excelSheet.UsedRange.Rows.Count;
                            int columnCount = excelSheet.UsedRange.Columns.Count;
                            for (int i = 1; i < rowsCount; i++)
                            {
                                for (int j = 0; j < addHyperlinksColumnsList.Length; j++)
                                {
                                    Range range = (Range)excelSheet.Cells[i + 1, addHyperlinksColumnsList[j]];
                                    //要添加的单元格位置
                                    string link = range.Cells.Value2 == null ? "" : range.Cells.Value2.ToString();
                                    excelSheet.Hyperlinks.Add(range, link, Type.Missing, Type.Missing, Type.Missing);
                                }
                            }
                        }
                    }
                }
                #endregion
            }
            //excelApp.Application.AlertBeforeOverwriting = false;
            excelApp.Application.DisplayAlerts = false;
            // Save and Close the Workbook
            excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            excelWorkbook.Close(true, Type.Missing, Type.Missing);
            excelWorkbook = null;

            // Release the Application object
            excelApp.Quit();
            excelApp = null;

            // Collect the unreferenced objects
            GC.Collect();
            GC.WaitForPendingFinalizers();
            DownExcelFromServer(outputPath,fileName);

        }
    }
}

 

2.可以将上面代码编译为dll文件，方便以后调用，如下

DataTableShowLinkSettingsModel dsm=new DataTableShowLinkSettingsModel();
            dsm.IsShowLink = true;//设置该表是否显示超链接
            dsm.ShowLinkColumns = new int[] { 2};//设置哪一列显示为超链接
            dt2.ExtendedProperties.Add("DataTableShowLinkSettings", dsm);//放入扩展属性
ExcelHelper eh=new ExcelHelper ();
eh.DataSetToLocalExcel(ds, "坐席评分数据" + DateTime.Now.ToString("yyyyMMdd") + ".xls", null, true);