Net用DataTable导出Excel通用函数

用户名密码

少年辛苦终身事,莫向光阴惰寸功!

DataTable导出EXCEL

2008年03月14日星期五22:50

.Net用DataTable导出Excel通用函数

[ 2007-3-19 9:58:00 | By: fangyc ] 1,首先要导入Com文件Microsoft Excel 11.0 Object Library.

2.执行下面步骤

///

/// 导出Excel

///

/// 要导出的DataTable

public void ExportToExcel(System.Data.DataTable dt)

{

if (dt == null) return;

Excel.Application xlApp = new Excel.Application();

if (xlApp == null)

{

lblMsg.Text = "无法创建Excel对象，可能您的机子未安装Excel";

return;

}

Excel.Workbooks workbooks = xlApp.Workbooks;

Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

Excel.Range range = null;

long totalCount = dt.Rows.Count;

long rowRead = 0;

float percent = 0;

//写入标题

for (int i = 0; i < dt.Columns.Count; i++)

{

worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;

range = (Excel.Range)worksheet.Cells[1, i + 1];

//range.Interior.ColorIndex = 15;//背景颜色

range.Font.Bold = true;//粗体

range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中

//加边框

range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel. XlColorIndex.xlColorIndexAutomatic, null);

//range.ColumnWidth = 4.63;//设置列宽

//range.EntireColumn.AutoFit();//自动调整列宽

//r1.EntireRow.AutoFit();//自动调整行高

}

//写入内容

for (int r = 0; r < dt.Rows.Count; r++)

{

for (int i = 0; i < dt.Columns.Count; i++)

{

worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];

range = (Excel.Range)worksheet.Cells[r + 2, i + 1];

range.Font.Size = 9;//字体大小

//加边框

range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Exc el.XlColorIndex.xlColorIndexAutomatic, null);

range.EntireColumn.AutoFit();//自动调整列宽

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

System.Windows.Forms.Application.DoEvents();

}

range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlT hin;

if (dt.Columns.Count > 1)

{

range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlT hin;

}

try

{

workbook.Saved = true;

workbook.SaveCopyAs(System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\Edu ceWordFiles\\" + https://www.360docs.net/doc/9117983621.html, + ".xls");

}

catch (Exception ex)

{

lblMsg.Text = "导出文件时出错,文件可能正被打开！\n" + ex.Message;

}

xlApp.Quit();

GC.Collect();//强行销毁

//这个是从服务器中下载文件,(请参考我另外一个文章)

//参考网址https://www.360docs.net/doc/9117983621.html,/ghostljj/archive/2007/01/24/629293.html

BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.x ls"

, System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.

https://www.360docs.net/doc/9117983621.html, + ".xls", 1024000);

}

//--------------------------------------------------------

3.如果是放在IIS中,现在是不能到出的,还要配置一下

方案一:在Web.config中添加

方案二:

(1)在运行->dcomcnfg打开组件服务

(2) 在控制台根目录->组件服务->计算机->我的电脑->DCOM配置->Microsoft Excel应用程

序->属性->安全

(3)启动和激活权限->使用自定义->添加一个ASPNET用户,还有打开本地启动和本地激活

访问权限->使用自定义->添加一个ASPNET用户,还有打开本地访问和远程访问

| | (1)

?https://www.360docs.net/doc/9117983621.html,中利用GridView或DataTab...?通过NPOI把DataTable导出到Excel ?通过MyXls把DataTable导出到Exce...?泛型分页类导出Excel文件的Exce...?c# DataTable针对xml,excel,csv...?WinForm 中,从DataTable中导出数...?将DataTable 导出到Excel?DataTable导出到Excel,GSV

?自动获取sheet名字Excel导出数...?datatable中导出excel文件

更多>>