需要一个excel.dll,有需要的可以qq联系我415548110
这个方法要求服务器端安装Excel,目的是速度快,适合大量数据的导出,主要是把一个表做为参数,直接导出。
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Reflection;
using System.IO;
using System.Runtime.InteropServices;
using Excel;
/// <summary>
/// ExcelOperator 的摘要说明
/// </summary>
public class ExcelOperator
{
private int _minRowIndex = 0;
private int _minColIndex = 0;
private Application oXL = null;
private Workbook oWB = null;
private Worksheet oSheet = null;
/// <summary>
/// Excel句柄
/// </summary>
private IntPtr iPtr;
/// <summary>
/// Excel进程ID
/// </summary>
private int iProcessId;
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public ExcelOperator(string Str_FileName)
{
oXL = new ApplicationClass();
// 打开模板文件
oWB = oXL.Workbooks.Open(Str_FileName,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
// 得到Excel句柄
iPtr = new IntPtr(oXL.Hwnd);
// 得到Excel进程ID
GetWindowThreadProcessId(iPtr, out iProcessId);
// 激活文档
oSheet = (Worksheet)oWB.ActiveSheet;
}
/// <summary>
/// Excel句柄
/// </summary>
/// <param name="Str_FileName">Excel文件名字</param>
/// <param name="SheetNum">Sheet数</param>
public ExcelOperator(string Str_FileName, int SheetNum)
{
try
{
oXL = new ApplicationClass();
// 打开模板文件
oWB = oXL.Workbooks.Open(Str_FileName,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
// 得到Excel句柄
iPtr = new IntPtr(oXL.Hwnd);
// 得到Excel进程ID
GetWindowThreadProcessId(iPtr, out iProcessId);
// 激活文档
SetActiveSheet(SheetNum);
}
catch (Exception ex)
{
ReleaseComObject(oSheet);
ReleaseComObject(oWB);
ReleaseComObject(oXL);
KillExcel();
throw ex;
}
}
/// <summary>
/// 设置当前活动的Sheet
/// </summary>
/// <param name="SctiveSheetIndex">当前活动的Sheet</param>
public void SetActiveSheet(int ActiveSheetIndex)
{
oSheet = (Worksheet)oWB.Sheets[ActiveSheetIndex];
oSheet.Activate();
}
/// <summary>
/// 设置单元格值
/// </summary>
/// <param name="Int_Row">行号</param>
/// <param name="Int_Col">列号</param>
/// <param name="obj">值</param>
public void SetExcelCell(int Int_Row, int Int_Col, object obj)
{
oSheet.Cells[Int_Row, Int_Col] = (String)obj;
}
public void SetExcelPic(string strPicName)
{
Excel.Pictures pics = (Excel.Pictures)oSheet.Pictures(Type.Missing);
Excel.Picture pic = pics.Insert(strPicName, Type.Missing);
}
/// <summary>
/// 设置单元格颜色
/// </summary>
/// <param name="rowIndex">行</param>
/// <param name="colIndex">列</param>
/// <param name="colorIndex">颜色索引值</param>
public void SetExcelCellColor(int rowIndex, int colIndex, int colorIndex)
{
oSheet.get_Range(oXL.Cells[rowIndex, colIndex], oXL.Cells[rowIndex, colIndex]).Interior.ColorIndex = colorIndex;
}
/// <summary>
/// 设置单元格字体颜色
/// </summary>
/// <param name="rowIndex">行</param>
/// <param name="colIndex">列</param>
/// <param name="colorIndex">颜色索引值</param>
public void SetExcelCellFontColor(int rowIndex, int colIndex, int colorIndex)
{
oSheet.get_Range(oXL.Cells[rowIndex, colIndex], oXL.Cells[rowIndex, colIndex]).Font.ColorIndex = colorIndex;
}
/// <summary>
/// 设置单元格边框
/// </summary>
/// <param name="rowIndex">行</param>
/// <param name="rowIndex2">行</param>
/// <param name="colIndex">列</param>
/// <param name="colIndex2">列</param>
/// <param name="style">线型</param>
/// <param name="colorIndex">线颜色</param>
/// <param name="weight">线重</param>
public void SetExcelCellBorder(int rowIndex, int rowIndex2, int colIndex, int colIndex2, cls_WebCS.LineStyle style, cls_WebCS.LineColorIndex colorIndex, cls_WebCS.LineWeight weight)
{
//设置外框线
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).BorderAround(style, (XlBorderWeight)weight, (XlColorIndex)colorIndex, null);
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[XlBordersIndex.xlInsideHorizontal].ColorIndex = colorIndex;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = style;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[XlBordersIndex.xlInsideHorizontal].Weight = weight;
//设置内框线
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = style;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[XlBordersIndex.xlInsideVertical].ColorIndex = colorIndex;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[XlBordersIndex.xlInsideVertical].Weight = weight;
}
/// <summary>
/// 设置数据区域的水平线和垂直线
/// </summary>
/// <param name="rowIndex">开始行</param>
/// <param name="rowIndex2">结束行</param>
/// <param name="colIndex">开始列</param>
/// <param name="colIndex2">列</param>
public void SetExcelHVBorder(int rowIndex, int rowIndex2, int colIndex, int colIndex2)
{
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
/// <summary>
/// 设置数据区域的横线
/// </summary>
/// <param name="rowIndex">开始行</param>
/// <param name="rowIndex2">结束行</param>
/// <param name="colIndex">开始列</param>
/// <param name="colIndex2">结束行</param>
public void SetExcelHBorder(int rowIndex, int rowIndex2, int colIndex, int colIndex2)
{
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
oSheet.get_Range(oSheet.Cells[rowIndex, colIndex], oSheet.Cells[rowIndex2, colIndex2]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
}
/// <summary>
/// 设置指定范围值
/// </summary>
/// <param name="sCell">[开始单元格] 例如 "A1"</param>
/// <param name="dCell">[截止单元格] 例如 "G1"</param>
/// <param name="dt">数据表</param>
public void SetExcelRange(string sCell, string dCell, System.Data.DataTable dt)
{
object[,] values = new object[dt.Rows.Count, dt.Columns.Count];
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
values[j, i] = dt.Rows[j][i];
}
}
SetExcelRange(sCell, dCell, values);
}
/// <summary>
/// 设置指定范围值
/// </summary>
/// <param name="sCell">开始单元格</param>
/// <param name="dCell">截止单元格</param>
/// <param name="values">数组</param>
public void SetExcelRange(string sCell, string dCell, object[,] values)
{
oSheet.get_Range(sCell, dCell).set_Value(Missing.Value, values);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">数据表</param>
public void ExportExcel(System.Data.DataTable dt)
{
try
{
// 生成Excel
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
SetExcelCell(j + MinRowIndex, i + MinColIndex, dt.Rows[j][i].ToString());
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
/// <summary>
/// 下载Excel
/// </summary>
/// <param name="downLoadFileName">下载文件</param>
public void ExcelOutPut(string downLoadFileName)
{
try
{
// 不可视化
oXL.Visible = false;
oWB.Saved = false;
if (File.Exists(downLoadFileName))
{
File.Delete(downLoadFileName);
}
oWB.SaveAs(downLoadFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close(false, null, null);
oXL.Quit();
ReleaseComObject(oSheet);
ReleaseComObject(oWB);
ReleaseComObject(oXL);
KillExcel();
System.IO.FileInfo file = new System.IO.FileInfo(downLoadFileName);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpContext.Current.Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
HttpContext.Current.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
HttpContext.Current.Response.WriteFile(file.FullName);
// 刷新页面
HttpContext.Current.Response.Flush();
}
catch (Exception ex)
{ throw ex; }
}
/// <summary>
/// 结束Excel进程
/// </summary>
private void KillExcel()
{
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(iProcessId);
p.Kill();
}
/// <summary>
/// 资源释放
/// </summary>
/// <param name="obj">对象</param>
private void ReleaseComObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
finally
{ obj = null; }
}
/// <summary>
/// 最小行索引
/// </summary>
public int MinRowIndex
{
get
{
return this._minRowIndex;
}
set
{
this._minRowIndex = value;
}
}
/// <summary>
/// 最小列索引
/// </summary>
public int MinColIndex
{
get
{
return this._minColIndex;
}
set
{
this._minColIndex = value;
}
}
}
具体引用方法:
//定义一个EXCEL生成类
Cls_Templete Obj_Templete;
DataTable dtbut = new DataTable();
String[] columns = new String[2];
columns[0] = "fdsafasfasf";
columns[1] = "1,6";
//填充DataTable
dt = dv.ToTable();
//为EXCEL生成类赋初值
Obj_Templete = new Cls_Templete("2009.xls", dt, columns, "AB", this.Id.Text+"_");
//生成并下载EXCEL文件
Obj_Templete.ToExcel();
//清空对象
Obj_Templete = null;
dtbut.Dispose();