Dataset export Excel

 /// <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 static string DataSetToLocalExcel(DataSet dataSet, string fileName, string saveDirectoryName, bool deleteOldFile)        {            if (dataSet==null)            {                return "count:0";            }            //设置导出文件在服务器上的文件夹               //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 + "UploadFiles\\ExportExcelFile";            }            else            {                directoryPath = rootPath + saveDirectoryName;            }            if (!Directory.Exists(directoryPath))            {                Directory.CreateDirectory(directoryPath);            }            exportDir = directoryPath;            //设置文件在服务器上的路径               string outputPath = exportDir + "\\" + fileName;            string dowloadUrl = "/UploadFiles/ExportExcelFile/" + fileName;            if (deleteOldFile)            {                if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }            }            object missing = System.Reflection.Missing.Value;            Excel.Application app = new Excel.ApplicationClass();            app.Application.Workbooks.Add(true);            Excel.Workbook excelWorkbook =  (Excel.Workbook)app.ActiveWorkbook;//获取添加的workbook            //创建Excel应用实例            //Application excelApp = new Application();            //创建Excel文档              //Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);            //for (int i = 0; i < dataSet.Tables.Count; i++)            //{            //    //excelWorkbook.Sheets.Add();            //    excelWorkbook.Worksheets.Add(Type.Missing,Type.Missing,1,Type.Missing);            //}                        int sheetIndex = 0;            foreach (System.Data.DataTable dt in dataSet.Tables)            {                try                {                     //每个table都要放到数组中以便于Excel绑定                    object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];                    for (int col = 0; col < dt.Columns.Count; col++)                    {                        //把列明放到数组的第一行中                         rawData[0, col] = dt.Columns[col].ColumnName;                        //将数据放到数组中中每一列都添加到数组                        for (int row = 0; row < dt.Rows.Count; row++)                        {                            rawData[row + 1, col] = dt.Rows[row].ItemArray[col];                        }                    }                    //创建列标                       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);                    ++sheetIndex;                                       //这种方式会自动创建一个sheet，因此先用了默认的，然后再添加                    Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Worksheets[sheetIndex];                                        //下一个页小于等于表格总数就加一个sheet                    if (sheetIndex+1<=dataSet.Tables.Count)                    {                        excelWorkbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);                    }                                                            excelSheet.Name = dt.TableName;                    //工作区中的范围设定                     string excelRange = string.Format("A1:{0}{1}",                        finalColLetter, dt.Rows.Count + 1);                    excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;                    // 第一行变成粗体                      ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;                    //调整列宽为自适应                    Range allColumn = excelSheet.Columns;                     allColumn.AutoFit();                 }                catch (Exception)                {                    throw;                }                #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            }                         app.Application.DisplayAlerts = false;//过程中的提示不弹出            //保存文档            excelWorkbook.SaveAs(outputPath);                        excelWorkbook.Close(true, missing,missing);            excelWorkbook = null;            //释放应用             app.Quit();            app = null;            //回收对象            GC.Collect();            GC.WaitForPendingFinalizers();            //DownExcelFromServer(outputPath, fileName);            return dowloadUrl;        }

 

Dataset export Excel