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 > )
{
PropertyInfo[] propertys = p_List[].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
_DataTable.Columns.Add(pi.Name, pi.PropertyType);
}
for (int i = ; 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 = ;
int colIndex = ;
int sheetCount = ;
//对全部Sheet进行操作
foreach (System.Data.DataTable dt in ds.Tables)
{
//初始化Sheet中的变量
rowIndex = ;
colIndex = ;
//创建一个Sheet
if (null == xSt)
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, , Type.Missing);
}
else
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, , Type.Missing);
}
//设置Sheet的名称
if (SheetName.Length > )
{
xSt.Name = SheetName[sheetCount - ];
}
//取得标题
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 = ; drvIndex <= dt.Rows.Count - ; drvIndex++)
{
DataRow row = dt.Rows[drvIndex];
//新起一行，当前单元格移至行首
rowIndex++;
colIndex = ;
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[, ], excel.Cells[rowIndex, colIndex - ]);
allDataWithTitleRange.Select();
allDataWithTitleRange.Columns.AutoFit();
allDataWithTitleRange.Borders.LineStyle = ;//将导出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 > )
{
ds.Tables.Add(CommonUtil.ListToDataTable(revList));
}
if (sendList != null && sendList.Count > )
{
ds.Tables.Add(CommonUtil.ListToDataTable(sendList));
}
if (ds.Tables.Count > )
{
string fileName = "DocExport.csv";
string[] sheetName = new string[] { "收文", "发文" };
ExcelExportHelper.DataView2ExcelBySheet(sheetName, ds, fileName);
}
}
}