标签：des style blog http color io os ar 使用
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
{
///
/// 集合装换DataTable
///
/// 集合
///
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 )
{
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;
}
///
/// 分Sheet导出Excel文件
///
/// 需导出的DataView
/// 导出文件的路径
///
/// 分Sheet导出Excel文件
///
/// 需要导出的数据集 可包含多个Table
/// 导出的文件名(不能有横线-,也不能有空格)
///
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 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();
}
}
}
------------------------------
///
/// 文件查询页导出功能
///
public void SelectDoc_Export2(string data)
{
var input = data.DeserializeObject();
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 revList = results.GetResult().ToList();
List sendList = results.GetResult().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);
}
}
}
c# excel sheep 导出
标签：des style blog http color io os ar 使用
原文地址：http://www.cnblogs.com/niaowo/p/4051045.html