[译]使用ASP.NET2.0生成Excel报表
作者:admin 日期:2008-10-13
文章来源：
http://www.beansoftware.com/asp.net-tutorials/generating-excel-reports.aspx
文章作者：Sushmita Kumari
这是一篇很老的文章了,很长时间没有翻译文章，拿来练一练. 同时把作者的VB版，改成了C#版
源文件：
点击下载此文件
Excel几乎是所有公司都要用来分析数据的一个超级工具,然而十年后的互联网已不可日而语,几乎所有的呈现的数据都是动态生成的,也就是说通过一个数据库里的数据交互产生所要的数据结果.有时,我们可能需在一个Excel的文件中,通过使用Excel的某些特性让分析的数据更好的适应我们的需要并呈现出来.在这篇文章中,我们可以学习到如何与Excel文件中的数据进行包括读写的交互操作.
相关源文件下载:
点击下载此文件
相关的文件:为了更好的演示本文章，我们先找到了一个一个包含学生分数数据的一个Excel文件.里面的数据都是随机的.
Excel - ASP.NET Scenarios
让我们新建一个接口从Excel文件中获取数据.如图
注意引入以下组件及命名空间:
VB Code
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String
strSQL = "Select * FROM [" & cmbSheets.SelectedItem.ToString() & "$]"
dbConn.Open()
Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)
daExcel.Fill(dsExcel)
dgResults.DataSource = dsExcel
dgResults.DataBind()
Catch ex As Exception
Throw ex
End Try
End Sub
C# Code
protected void btnSearch_Click(object sender, System.EventArgs e)
{
try {
string strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString();
OleDbConnection dbConn = new OleDbConnection(strExcelConn);
string strSQL;
strSQL = "Select * FROM [" + cmbSheets.SelectedItem.ToString() + "$]";
dbConn.Open();
OleDbCommand cmd = new OleDbCommand(strSQL, dbConn);
DataSet dsExcel = new DataSet();
OleDbDataAdapter daExcel = new OleDbDataAdapter(cmd);
daExcel.Fill(dsExcel);
dgResults.DataSource = dsExcel;
dgResults.DataBind();
}
catch (Exception ex) {
throw ex;
}
}
值得注意的是:在查询中的表名应该是[TableName$]格式进行命名,必须要以[]和$结尾
Oledb连接已经这个Excel文件已经转变成了一个普通的数据库. 同时,第一行的已成包含了各列的字段名.现在我们可以根据降序对学生的数学,地理及总分产生报表.
Protected Sub btnGenerateReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateReport.Click
Try
Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String
strSQL = "Select S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total "& _
"FROM [Students$] S, [Mathematics$] M, [Geography$] G "& _
"Where(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " & _
"ORDER BY (M.Marks+G.Marks) DESC"
dbConn.Open()
Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)
daExcel.Fill(dsExcel)
dgReports.DataSource = dsExcel
dgReports.DataBind()
Catch ex As Exception
Throw ex
End Try
End Sub
C# Code
protected void btnGenerateReport_Click(object sender, System.EventArgs e)
{
try {
string strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString();
OleDbConnection dbConn = new OleDbConnection(strExcelConn);
string strSQL;
strSQL = "Select S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total " + "FROM [Students$] S, [Mathematics$] M, [Geography$] G " + "Where(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " + "ORDER BY (M.Marks+G.Marks) DESC";
dbConn.Open();
OleDbCommand cmd = new OleDbCommand(strSQL, dbConn);
DataSet dsExcel = new DataSet();
OleDbDataAdapter daExcel = new OleDbDataAdapter(cmd);
daExcel.Fill(dsExcel);
dgReports.DataSource = dsExcel;
dgReports.DataBind();
}
catch (Exception ex) {
throw ex;
}
}
Generating Excel Reports 产生Excel报表
我们可以通过两种方法产生Excel的表,一种是使用”Response类和复杂的Microsoft Excel 11.0 实现.
下面的代码就是实现代码
VB Code
Protected Sub btnToExcelByResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByResponse.Click
' Variables declaration
Dim dsExport As New DataSet()
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim dgGrid As New DataGrid()
dgGrid.DataSource = getData()
' Report Header
hw.WriteLine(" Student Marking Report ")
' Get the HTML for the control.
dgGrid.HeaderStyle.Font.Bold = True
dgGrid.DataBind()
dgGrid.RenderControl(hw)
' Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel"
Me.EnableViewState = False
Response.Write(tw.ToString())
Response.End()
End Sub
C# Code
protected void btnToExcelByResponse_Click(object sender, System.EventArgs e)
{
// Variables declaration
DataSet dsExport = new DataSet();
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
DataGrid dgGrid = new DataGrid();
dgGrid.DataSource = getData();
// Report Header
hw.WriteLine(" Student Marking Report ");
// Get the HTML for the control.
dgGrid.HeaderStyle.Font.Bold = true;
dgGrid.DataBind();
dgGrid.RenderControl(hw);
// Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel";
this.EnableViewState = false;
Response.Write(tw.ToString());
Response.End();
}
首先,我们需要创建一个dataset对象来装载这个数据.之后,通过StringWrite对象把一个HtmlTextWrite 看作一个参数写进对象中. 我们也可以使用一个datagrid对象来存储数据.而代码中的getData()方法只包括了前面的返回的数据. 由hw.WriteLine(" Student Marking Report ") 我们可以看到,可通过使用HTML代码来达到所需要呈现的效果,在我们的这个例子中,字体的大小是5,下划线并加粗. dgGrid.RenderControl(hw)则render了一个datagrid所包含的HTML Object.所得到的结果则是一个格式化好了的表格. Response.ContentType = "application/vnd.ms-excel" 将Response对象输出至Excel. Response.Write(tw.ToString()) 则是将已经格式化好的对象输入至Excel.
第二种方法是通过Microsoft Excel 11.0 Objects来产生报表.这个方法主要用于需要更复杂的功能或报表中使用.
VB Code
Protected Sub btnToExcelByObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByObjects.Click
Try
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
xlWorkBook.Application.Visible = True
xlWorkSheet = xlWorkBook.ActiveSheet
' Gets the dataset containing the data
Dim dsData As DataSet = getData()
Dim i As Integer = 2
' Outputting the fieldnames in pink bold color
xlWorkSheet.Cells(1, 1) = "Student ID"
xlWorkSheet.Cells(1, 2) = "Student Name"
xlWorkSheet.Cells(1, 3) = "Mathematics"
xlWorkSheet.Cells(1, 4) = "Geography"
xlWorkSheet.Cells(1, 5) = "Total"
xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
xlWorkSheet.Range("$A1:$E1").Font.Bold = True
' Outputting the data
For Each dr As DataRow In dsData.Tables(0).Rows
xlWorkSheet.Cells(i, 1) = dr(0)
xlWorkSheet.Cells(i, 2) = dr(1)
xlWorkSheet.Cells(i, 3) = dr(2)
xlWorkSheet.Cells(i, 4) = dr(3)
' Building the formula for calculating the sum
xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())
' Going to the next row
i = i + 1
Next
' Auto fit the columns
xlWorkSheet.Columns.AutoFit()
' Generating the graph
Dim chart As Excel.Chart
chart = xlWorkBook.Charts.Add()
With chart
.ChartType = Excel.XlChartType.xlColumnClustered
.SetSourceData(xlWorkSheet.Range("A1:E11"), 2)
.HasTitle = True
.ChartTitle.Characters.Text = "Students' marks"
.Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
.Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
End With
Catch ex As Exception
Throw ex
End Try
End Sub
C# Code
protected void btnToExcelByObjects_Click(object sender, System.EventArgs e)
{
try {
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlWorkBook = new Excel.Application().Workbooks.Add(Missing.Value);
xlWorkBook.Application.Visible = true;
xlWorkSheet = xlWorkBook.ActiveSheet;
// Gets the dataset containing the data
DataSet dsData = getData();
int i = 2;
// Outputting the fieldnames in pink bold color
xlWorkSheet.Cells(1, 1) = "Student ID";
xlWorkSheet.Cells(1, 2) = "Student Name";
xlWorkSheet.Cells(1, 3) = "Mathematics";
xlWorkSheet.Cells(1, 4) = "Geography";
xlWorkSheet.Cells(1, 5) = "Total";
xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1;
xlWorkSheet.Range("$A1:$E1").Font.Bold = true;
// Outputting the data
foreach (DataRow dr in dsData.Tables(0).Rows) {
xlWorkSheet.Cells(i, 1) = dr(0);
xlWorkSheet.Cells(i, 2) = dr(1);
xlWorkSheet.Cells(i, 3) = dr(2);
xlWorkSheet.Cells(i, 4) = dr(3);
// Building the formula for calculating the sum
xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString());
// Going to the next row
i = i + 1;
}
// Auto fit the columns
xlWorkSheet.Columns.AutoFit();
// Generating the graph
Excel.Chart chart;
chart = xlWorkBook.Charts.Add();
// ERROR: Not supported in C#: WithStatement
}
catch (Exception ex) {
throw ex;
}
}
首先创建了个Excel的工作簿,之后将这已经激活的工作簿通过xlWorkSheet = xlWorkBook.ActiveShee 之后,我们将数据放入dataset中等待处理.标题可以通过合适的字体呈现.而Excel中的公式则被用来计算每个学生的总分: xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString()).
同样,可以通过这个对象生成图生.X坐标表球每个学生的ID和姓名,而Y坐标则是不同学生的分数.可以通过这个图表将数据分析呈现出来.
在这篇文章中,我们学习到使用Response对象或Microsoft Excel 11.0 objects provided从Excel中读取数据.文章不可能把所有的功能都一一介绍到,但很多重新的技术点都可以帮助你达到你的目的.
搜索了一下其它相关的文章，估计下面的也对大家有所帮助：
http://topic.csdn.net/u/20080114/17/efd61025-1a59-411e-8fee-763581305d5d.html
方法1:
3种方法
1:
C# code
2:使用MS的组件和服务
3:Excel的XML格式
C# code
http://www.beansoftware.com/asp.net-tutorials/generating-excel-reports.aspx
文章作者：Sushmita Kumari
这是一篇很老的文章了,很长时间没有翻译文章，拿来练一练. 同时把作者的VB版，改成了C#版
源文件：
点击下载此文件Excel几乎是所有公司都要用来分析数据的一个超级工具,然而十年后的互联网已不可日而语,几乎所有的呈现的数据都是动态生成的,也就是说通过一个数据库里的数据交互产生所要的数据结果.有时,我们可能需在一个Excel的文件中,通过使用Excel的某些特性让分析的数据更好的适应我们的需要并呈现出来.在这篇文章中,我们可以学习到如何与Excel文件中的数据进行包括读写的交互操作.
相关源文件下载:
点击下载此文件相关的文件:为了更好的演示本文章，我们先找到了一个一个包含学生分数数据的一个Excel文件.里面的数据都是随机的.
Excel - ASP.NET Scenarios
让我们新建一个接口从Excel文件中获取数据.如图
注意引入以下组件及命名空间:
复制内容到剪贴板
程序代码
程序代码Imports System.Data.OleDb
Imports System.Data
Imports System.Data
VB Code
复制内容到剪贴板 程序代码
程序代码Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String
strSQL = "Select * FROM [" & cmbSheets.SelectedItem.ToString() & "$]"
dbConn.Open()
Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)
daExcel.Fill(dsExcel)
dgResults.DataSource = dsExcel
dgResults.DataBind()
Catch ex As Exception
Throw ex
End Try
End Sub
C# Code
复制内容到剪贴板 程序代码
程序代码protected void btnSearch_Click(object sender, System.EventArgs e)
{
try {
string strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString();
OleDbConnection dbConn = new OleDbConnection(strExcelConn);
string strSQL;
strSQL = "Select * FROM [" + cmbSheets.SelectedItem.ToString() + "$]";
dbConn.Open();
OleDbCommand cmd = new OleDbCommand(strSQL, dbConn);
DataSet dsExcel = new DataSet();
OleDbDataAdapter daExcel = new OleDbDataAdapter(cmd);
daExcel.Fill(dsExcel);
dgResults.DataSource = dsExcel;
dgResults.DataBind();
}
catch (Exception ex) {
throw ex;
}
}
值得注意的是:在查询中的表名应该是[TableName$]格式进行命名,必须要以[]和$结尾
Oledb连接已经这个Excel文件已经转变成了一个普通的数据库. 同时,第一行的已成包含了各列的字段名.现在我们可以根据降序对学生的数学,地理及总分产生报表.
复制内容到剪贴板 程序代码
程序代码Protected Sub btnGenerateReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateReport.Click
Try
Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String
strSQL = "Select S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total "& _
"FROM [Students$] S, [Mathematics$] M, [Geography$] G "& _
"Where(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " & _
"ORDER BY (M.Marks+G.Marks) DESC"
dbConn.Open()
Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)
daExcel.Fill(dsExcel)
dgReports.DataSource = dsExcel
dgReports.DataBind()
Catch ex As Exception
Throw ex
End Try
End Sub
C# Code
复制内容到剪贴板 程序代码
程序代码protected void btnGenerateReport_Click(object sender, System.EventArgs e)
{
try {
string strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString();
OleDbConnection dbConn = new OleDbConnection(strExcelConn);
string strSQL;
strSQL = "Select S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total " + "FROM [Students$] S, [Mathematics$] M, [Geography$] G " + "Where(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " + "ORDER BY (M.Marks+G.Marks) DESC";
dbConn.Open();
OleDbCommand cmd = new OleDbCommand(strSQL, dbConn);
DataSet dsExcel = new DataSet();
OleDbDataAdapter daExcel = new OleDbDataAdapter(cmd);
daExcel.Fill(dsExcel);
dgReports.DataSource = dsExcel;
dgReports.DataBind();
}
catch (Exception ex) {
throw ex;
}
}
Generating Excel Reports 产生Excel报表
我们可以通过两种方法产生Excel的表,一种是使用”Response类和复杂的Microsoft Excel 11.0 实现.
下面的代码就是实现代码
VB Code
复制内容到剪贴板 程序代码
程序代码Protected Sub btnToExcelByResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByResponse.Click
' Variables declaration
Dim dsExport As New DataSet()
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim dgGrid As New DataGrid()
dgGrid.DataSource = getData()
' Report Header
hw.WriteLine(" Student Marking Report ")
' Get the HTML for the control.
dgGrid.HeaderStyle.Font.Bold = True
dgGrid.DataBind()
dgGrid.RenderControl(hw)
' Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel"
Me.EnableViewState = False
Response.Write(tw.ToString())
Response.End()
End Sub
C# Code
复制内容到剪贴板 程序代码
程序代码protected void btnToExcelByResponse_Click(object sender, System.EventArgs e)
{
// Variables declaration
DataSet dsExport = new DataSet();
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
DataGrid dgGrid = new DataGrid();
dgGrid.DataSource = getData();
// Report Header
hw.WriteLine(" Student Marking Report ");
// Get the HTML for the control.
dgGrid.HeaderStyle.Font.Bold = true;
dgGrid.DataBind();
dgGrid.RenderControl(hw);
// Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel";
this.EnableViewState = false;
Response.Write(tw.ToString());
Response.End();
}
首先,我们需要创建一个dataset对象来装载这个数据.之后,通过StringWrite对象把一个HtmlTextWrite 看作一个参数写进对象中. 我们也可以使用一个datagrid对象来存储数据.而代码中的getData()方法只包括了前面的返回的数据. 由hw.WriteLine(" Student Marking Report ") 我们可以看到,可通过使用HTML代码来达到所需要呈现的效果,在我们的这个例子中,字体的大小是5,下划线并加粗. dgGrid.RenderControl(hw)则render了一个datagrid所包含的HTML Object.所得到的结果则是一个格式化好了的表格. Response.ContentType = "application/vnd.ms-excel" 将Response对象输出至Excel. Response.Write(tw.ToString()) 则是将已经格式化好的对象输入至Excel.
第二种方法是通过Microsoft Excel 11.0 Objects来产生报表.这个方法主要用于需要更复杂的功能或报表中使用.
VB Code
复制内容到剪贴板 程序代码
程序代码Protected Sub btnToExcelByObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByObjects.Click
Try
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
xlWorkBook.Application.Visible = True
xlWorkSheet = xlWorkBook.ActiveSheet
' Gets the dataset containing the data
Dim dsData As DataSet = getData()
Dim i As Integer = 2
' Outputting the fieldnames in pink bold color
xlWorkSheet.Cells(1, 1) = "Student ID"
xlWorkSheet.Cells(1, 2) = "Student Name"
xlWorkSheet.Cells(1, 3) = "Mathematics"
xlWorkSheet.Cells(1, 4) = "Geography"
xlWorkSheet.Cells(1, 5) = "Total"
xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
xlWorkSheet.Range("$A1:$E1").Font.Bold = True
' Outputting the data
For Each dr As DataRow In dsData.Tables(0).Rows
xlWorkSheet.Cells(i, 1) = dr(0)
xlWorkSheet.Cells(i, 2) = dr(1)
xlWorkSheet.Cells(i, 3) = dr(2)
xlWorkSheet.Cells(i, 4) = dr(3)
' Building the formula for calculating the sum
xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())
' Going to the next row
i = i + 1
Next
' Auto fit the columns
xlWorkSheet.Columns.AutoFit()
' Generating the graph
Dim chart As Excel.Chart
chart = xlWorkBook.Charts.Add()
With chart
.ChartType = Excel.XlChartType.xlColumnClustered
.SetSourceData(xlWorkSheet.Range("A1:E11"), 2)
.HasTitle = True
.ChartTitle.Characters.Text = "Students' marks"
.Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
.Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
End With
Catch ex As Exception
Throw ex
End Try
End Sub
C# Code
复制内容到剪贴板 程序代码
程序代码protected void btnToExcelByObjects_Click(object sender, System.EventArgs e)
{
try {
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlWorkBook = new Excel.Application().Workbooks.Add(Missing.Value);
xlWorkBook.Application.Visible = true;
xlWorkSheet = xlWorkBook.ActiveSheet;
// Gets the dataset containing the data
DataSet dsData = getData();
int i = 2;
// Outputting the fieldnames in pink bold color
xlWorkSheet.Cells(1, 1) = "Student ID";
xlWorkSheet.Cells(1, 2) = "Student Name";
xlWorkSheet.Cells(1, 3) = "Mathematics";
xlWorkSheet.Cells(1, 4) = "Geography";
xlWorkSheet.Cells(1, 5) = "Total";
xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1;
xlWorkSheet.Range("$A1:$E1").Font.Bold = true;
// Outputting the data
foreach (DataRow dr in dsData.Tables(0).Rows) {
xlWorkSheet.Cells(i, 1) = dr(0);
xlWorkSheet.Cells(i, 2) = dr(1);
xlWorkSheet.Cells(i, 3) = dr(2);
xlWorkSheet.Cells(i, 4) = dr(3);
// Building the formula for calculating the sum
xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString());
// Going to the next row
i = i + 1;
}
// Auto fit the columns
xlWorkSheet.Columns.AutoFit();
// Generating the graph
Excel.Chart chart;
chart = xlWorkBook.Charts.Add();
// ERROR: Not supported in C#: WithStatement
}
catch (Exception ex) {
throw ex;
}
}
首先创建了个Excel的工作簿,之后将这已经激活的工作簿通过xlWorkSheet = xlWorkBook.ActiveShee 之后,我们将数据放入dataset中等待处理.标题可以通过合适的字体呈现.而Excel中的公式则被用来计算每个学生的总分: xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString()).
同样,可以通过这个对象生成图生.X坐标表球每个学生的ID和姓名,而Y坐标则是不同学生的分数.可以通过这个图表将数据分析呈现出来.
在这篇文章中,我们学习到使用Response对象或Microsoft Excel 11.0 objects provided从Excel中读取数据.文章不可能把所有的功能都一一介绍到,但很多重新的技术点都可以帮助你达到你的目的.
搜索了一下其它相关的文章，估计下面的也对大家有所帮助：
http://topic.csdn.net/u/20080114/17/efd61025-1a59-411e-8fee-763581305d5d.html
方法1:
3种方法
1:
C# code
复制内容到剪贴板 程序代码
程序代码Feli.Data.Stock.CheckStockSubDAO objCheckStockSubDAO = new Feli.Data.Stock.CheckStockSubDAO();
Feli.Data.Stock.CheckStockSubCollection objCheckStockSubCol = objCheckStockSubDAO.GetList();
string strPath = Server.MapPath("..\\Excel\\CheckStock");
string strName = strPath + "\\" + "CheckStock" + "_" + strChkNo + ".xls";
System.IO.FileStream fs = new System.IO.FileStream( strName, System.IO.FileMode.Create,System.IO.FileAccess.Write );
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine(@"商品编号" + "\t" + "商品名称" + "\t" + "可出库数" + "\t" + "单价");
foreach(Feli.Data.Stock.CheckStockSub objCheckStockSub in objCheckStockSubCol)
{
sw.WriteLine( objCheckStockSub.GdCd + "\t" + objCheckStockSub.GdName + "\t" +
objCheckStockSub.StkQty.ToString() + objCheckStockSub.SalePrc.ToString());
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(strName));
Response.ContentType = "application/msexcel";//指定返回的是一个不能被客户端读取的流，必须被下载
Response.WriteFile(strName); //把文件流发送到客户端
Response.End();
Feli.Data.Stock.CheckStockSubCollection objCheckStockSubCol = objCheckStockSubDAO.GetList();
string strPath = Server.MapPath("..\\Excel\\CheckStock");
string strName = strPath + "\\" + "CheckStock" + "_" + strChkNo + ".xls";
System.IO.FileStream fs = new System.IO.FileStream( strName, System.IO.FileMode.Create,System.IO.FileAccess.Write );
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine(@"商品编号" + "\t" + "商品名称" + "\t" + "可出库数" + "\t" + "单价");
foreach(Feli.Data.Stock.CheckStockSub objCheckStockSub in objCheckStockSubCol)
{
sw.WriteLine( objCheckStockSub.GdCd + "\t" + objCheckStockSub.GdName + "\t" +
objCheckStockSub.StkQty.ToString() + objCheckStockSub.SalePrc.ToString());
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(strName));
Response.ContentType = "application/msexcel";//指定返回的是一个不能被客户端读取的流，必须被下载
Response.WriteFile(strName); //把文件流发送到客户端
Response.End();
2:使用MS的组件和服务
复制内容到剪贴板 程序代码
程序代码System.Data.DataTable dt = CreateExcelTable();
if (dt.Rows.Count <= 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "");
return;
}
Application Excel;
int rowIndex = 2;
int colIndex = 0;
_Workbook xBk;
_Worksheet xSt;
Excel = new ApplicationClass();
xBk = Excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
//打印横向
xSt.PageSetup.Orientation = XlPageOrientation.xlLandscape;
//第一行特殊样式
xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).MergeCells = true;//合并单元格
xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[3, 26]).Borders.LineStyle = 1;//加边框
xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置居中对齐
xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).Font.Size = 18;//设置字体
Excel.Cells[1, 1] = "人才夹：" + ddlHeroFolder.SelectedItem.Text;
//取得标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Excel.Cells[2, colIndex] = col.ColumnName;
xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignLeft;//设置标题格式为居中对齐
xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Bold = true;
//xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Italic = Convert.ToBoolean(this.chkIta.Checked);
xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Size = 12;
//xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Name = this.ddlFont.SelectedValue;
//xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Width = 150;
}
//取得表格中的数据
//
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(Excel.Cells[rowIndex, colIndex], Excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
Range tempRange = xSt.get_Range(xSt.Cells[rowIndex, 26], xSt.Cells[rowIndex, 27]);
string strHyperlinks = row["查看简历"].ToString();
xSt.Hyperlinks.Add(tempRange, strHyperlinks, "", "", "查看简历");
}
}
xSt.Columns.AutoFit();
Excel.Visible = true;
//IO
xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "FileName" + ".xls");
/////回收
//ds = null;
xBk.Close(false, null, null);
Excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel);
xBk = null;
xSt = null;
Excel = null;
GC.Collect();
//Dispose();
//////
string path = Server.MapPath("FileName.xls");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-Excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
if (dt.Rows.Count <= 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "");
return;
}
Application Excel;
int rowIndex = 2;
int colIndex = 0;
_Workbook xBk;
_Worksheet xSt;
Excel = new ApplicationClass();
xBk = Excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
//打印横向
xSt.PageSetup.Orientation = XlPageOrientation.xlLandscape;
//第一行特殊样式
xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).MergeCells = true;//合并单元格
xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[3, 26]).Borders.LineStyle = 1;//加边框
xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置居中对齐
xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).Font.Size = 18;//设置字体
Excel.Cells[1, 1] = "人才夹：" + ddlHeroFolder.SelectedItem.Text;
//取得标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Excel.Cells[2, colIndex] = col.ColumnName;
xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignLeft;//设置标题格式为居中对齐
xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Bold = true;
//xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Italic = Convert.ToBoolean(this.chkIta.Checked);
xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Size = 12;
//xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Name = this.ddlFont.SelectedValue;
//xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Width = 150;
}
//取得表格中的数据
//
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
Excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(Excel.Cells[rowIndex, colIndex], Excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
Range tempRange = xSt.get_Range(xSt.Cells[rowIndex, 26], xSt.Cells[rowIndex, 27]);
string strHyperlinks = row["查看简历"].ToString();
xSt.Hyperlinks.Add(tempRange, strHyperlinks, "", "", "查看简历");
}
}
xSt.Columns.AutoFit();
Excel.Visible = true;
//IO
xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "FileName" + ".xls");
/////回收
//ds = null;
xBk.Close(false, null, null);
Excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel);
xBk = null;
xSt = null;
Excel = null;
GC.Collect();
//Dispose();
//////
string path = Server.MapPath("FileName.xls");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-Excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
3:Excel的XML格式
C# code
复制内容到剪贴板 程序代码
程序代码using System;
using System.Text;
using System.IO;
namespace Test
{
public partial class XmlExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//存放模板文件内容
string fileContent = string.Empty;
//模板文件位置
string modleFileName = Server.MapPath(".") + "\\ExcelModleFile.xls";
//生成文件位置
string renderFileName = Server.MapPath(".") + "\\ExcelFile.xls";
//读出并保存模板文件内容
StreamReader sr = new StreamReader(modleFileName, System.Text.Encoding.GetEncoding("gb2312"));
fileContent = sr.ReadToEnd();
sr.Close();
//循环生成数据行
StringBuilder sbRowsText = new StringBuilder(1024);
sbRowsText.Append("");
sbRowsText.Append(" //设置超链接地址
sbRowsText.Append("http://www.126.com/");
sbRowsText.Append("\">View | ");
sbRowsText.Append("");
//设置内容
sbRowsText.Append("Content");
sbRowsText.Append(" | ");
sbRowsText.Append("
");
//保存完整Excel内容的字符串
StringBuilder sbRender = new StringBuilder();
//获得模板内容
sbRender.Append(fileContent);
//设置Excel数据行
sbRender.Replace(@"[RowCount]", "3");
//设置Excel标题
sbRender.Replace(@"[Header]", "Title");
//添加数据行
sbRender.Replace(@"[DataRows]", sbRowsText.ToString());
lblXml.Text = sbRender.ToString();
//将内容写入文件
StreamWriter sw = new StreamWriter(renderFileName);
sw.Write(sbRender.ToString());
sw.Close();
//将文件输出到客户端
Response.Charset = "GB2312";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(renderFileName));
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(renderFileName);
Response.End();
}
}
}
using System.Text;
using System.IO;
namespace Test
{
public partial class XmlExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//存放模板文件内容
string fileContent = string.Empty;
//模板文件位置
string modleFileName = Server.MapPath(".") + "\\ExcelModleFile.xls";
//生成文件位置
string renderFileName = Server.MapPath(".") + "\\ExcelFile.xls";
//读出并保存模板文件内容
StreamReader sr = new StreamReader(modleFileName, System.Text.Encoding.GetEncoding("gb2312"));
fileContent = sr.ReadToEnd();
sr.Close();
//循环生成数据行
StringBuilder sbRowsText = new StringBuilder(1024);
sbRowsText.Append("
sbRowsText.Append("
sbRowsText.Append("http://www.126.com/");
sbRowsText.Append("\">View
sbRowsText.Append("
//设置内容
sbRowsText.Append("Content");
sbRowsText.Append("
sbRowsText.Append("
//保存完整Excel内容的字符串
StringBuilder sbRender = new StringBuilder();
//获得模板内容
sbRender.Append(fileContent);
//设置Excel数据行
sbRender.Replace(@"[RowCount]", "3");
//设置Excel标题
sbRender.Replace(@"[Header]", "Title");
//添加数据行
sbRender.Replace(@"[DataRows]", sbRowsText.ToString());
lblXml.Text = sbRender.ToString();
//将内容写入文件
StreamWriter sw = new StreamWriter(renderFileName);
sw.Write(sbRender.ToString());
sw.Close();
//将文件输出到客户端
Response.Charset = "GB2312";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(renderFileName));
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(renderFileName);
Response.End();
}
}
}
上一篇: 《沉思录》读书笔记
下一篇: 海南.三亚.海口
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: ASP.NET2.0生成Excel报表 Net.Excel报表 
相关日志:
评论: 0 | 引用: 0 | 查看次数: 7778
发表评论
