using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Reflection; using System.Collections; using Microsoft.Office.Interop.Excel; namespace Common { public class ExcelExportHelper { /// <summary> /// 集合装换DataTable /// </summary> /// <param name="list">集合</param> /// <returns></returns> public static System.Data.DataTable ToDataSet(IList p_List) { System.Data.DataTable _DataTable = new System.Data.DataTable(); if (p_List.Count > 0) { PropertyInfo[] propertys = p_List[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { _DataTable.Columns.Add(pi.Name, pi.PropertyType); } for (int i = 0; i < p_List.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { object obj = pi.GetValue(p_List[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); _DataTable.LoadDataRow(array, true); } } return _DataTable; } /// <summary> /// 分Sheet导出Excel文件 /// </summary> /// <param name="dv">需导出的DataView</param> /// <returns>导出文件的路径</returns> /// <summary> /// 分Sheet导出Excel文件 /// </summary> /// <param name="ds">需要导出的数据集 可包含多个Table</param> /// <param name="fileName">导出的文件名(不能有横线-,也不能有空格)</param> /// <returns></returns> public static void DataView2ExcelBySheet(string[] SheetName, DataSet ds, string fileName) { GC.Collect();//垃圾回收 Application excel; _Workbook xBk; _Worksheet xSt = null; excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); //定义循环中要使用的变量 int rowIndex = 0; int colIndex = 0; int sheetCount = 1; //对全部Sheet进行操作 foreach (System.Data.DataTable dt in ds.Tables) { //初始化Sheet中的变量 rowIndex = 1; colIndex = 1; //创建一个Sheet if (null == xSt) { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置Sheet的名称 if (SheetName.Length > 0) { xSt.Name = SheetName[sheetCount - 1]; } //取得标题 foreach (DataColumn col in dt.Columns) { //设置标题格式 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;//设置标题为粗体 //填值，并进行下一列 excel.Cells[rowIndex, colIndex++] = col.ColumnName; } //取得表格中数量 int drvIndex; for (drvIndex = 0; drvIndex <= dt.Rows.Count - 1; drvIndex++) { DataRow row = dt.Rows[drvIndex]; //新起一行，当前单元格移至行首 rowIndex++; colIndex = 1; foreach (DataColumn col in dt.Columns) { if (col.DataType == System.Type.GetType("System.DateTime")) { excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); } else if (col.DataType == System.Type.GetType("System.String")) { if (row[col.ColumnName].ToString().Contains("http")) { excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); Range tempRange = xSt.get_Range(xSt.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]); string strHyperlinks = row[col.ColumnName].ToString(); xSt.Hyperlinks.Add(tempRange, strHyperlinks, Missing.Value, Missing.Value, Missing.Value); } else { excel.Cells[rowIndex, colIndex] = "‘" + row[col.ColumnName].ToString(); } } else { excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } colIndex++; } } //使用最佳宽度 Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]); allDataWithTitleRange.Select(); allDataWithTitleRange.Columns.AutoFit(); allDataWithTitleRange.Borders.LineStyle = 1;//将导出Excel加上边框 sheetCount++; } //设置导出文件在服务器上的文件夹 string exportDir = "~/ExcelFile/";//注意:该文件夹您须事先在服务器上建好才行 string strPath = System.IO.Path.Combine(exportDir, fileName); //设置文件在服务器上的路径 string absFileName = HttpContext.Current.Server.MapPath(exportDir) + fileName; xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); HttpResponse resp; resp = System.Web.HttpContext.Current.Response; resp.Charset = "GB2312"; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-Disposition", ("attachment;filename=" + fileName)); resp.WriteFile(absFileName, false); resp.End(); } } } ------------------------------ /// <summary> /// 文件查询页导出功能 /// </summary> public void SelectDoc_Export2(string data) { var input = data.DeserializeObject<StructSelectDoc>(); using (var context = DOCDBHelper.DataContext) { var results = context.Usp_DOC_SelectDoc_Export(input.docNo, input.docName, input.docFlag, input.docCatagoryID, input.docSenderName, input.docSenderDep, input.docRecvDepName, input.createDate1, input.createDate2); List<Usp_DOC_SelectDoc_ExportResult1> revList = results.GetResult<Usp_DOC_SelectDoc_ExportResult1>().ToList(); List<Usp_DOC_SelectDoc_ExportResult2> sendList = results.GetResult<Usp_DOC_SelectDoc_ExportResult2>().ToList(); DataSet ds = new DataSet(); if (revList != null && revList.Count > 0) { ds.Tables.Add(CommonUtil.ListToDataTable(revList)); } if (sendList != null && sendList.Count > 0) { ds.Tables.Add(CommonUtil.ListToDataTable(sendList)); } if (ds.Tables.Count > 0) { string fileName = "DocExport.csv"; string[] sheetName = new string[] { "收文", "发文" }; ExcelExportHelper.DataView2ExcelBySheet(sheetName, ds, fileName); } } }