C#操作excel sheet
首页 本类
c# winform 中如何把datagrid中查询出的数据导入excel?或有什么ESAY方法把数据拷出来也行,就像ASP.NET中的DataGrid一样显示的数据可拷出(Ctrl+V)来?
高手如知如何导请附一下原码,非常感谢!!
:)
-
这里有个VB.NET利用数组的例子，自己改改好了
.NET下 从 DataView DataSet DataTable 导出数据至Excel
Public Sub Exports2Excel(ByVal Dtg As DataGrid)
If Dtg.VisibleRowCount > 0 Then
Try
Me.Cursor = Cursors.WaitCursor
Dim datav As New DataView
If TypeOf Dtg.DataSource Is DataView Then
datav = CType(Dtg.DataSource, DataView)
ElseIf TypeOf Dtg.DataSource Is DataSet Then
datav = CType(Dtg.DataSource, DataSet).Tables(0).DefaultView
ElseIf TypeOf Dtg.DataSource Is DataTable Then
datav = CType(Dtg.DataSource, DataTable).DefaultView
End If
Dim i, j As Integer
Dim rows As Integer = datav.Table.Rows.Count
Dim cols As Integer = datav.Table.Columns.Count
Dim DataArray(rows - 1, cols - 1) As String
Dim myExcel As Excel.Application = New Excel.Application
For i = 0 To rows - 1
For j = 0 To cols - 1
DataArray(i, j) = datav.Table.Rows(i).Item(j)
Next
Next
myExcel.Application.Workbooks.Add(True)
myExcel.Visible = True
For j = 0 To cols - 1
myExcel.Cells(1, j + 1) = datav.Table.Columns(j).ColumnName
Next
myExcel.Range("A2").Resize(rows, cols).Value = DataArray
Catch exp As Exception
MessageBox.Show("数据导出失败!请查看是否已经安装了Excel", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)
Finally
Me.Cursor = Cursors.Default
End Try
Else
MessageBox.Show("没有数据!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub
帮顶！
谢谢longdr(龙卷风)!这是我在ASP.NET下写的一个程序想转成WINFROM,偶对这个不熟,现找到一段源码:Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,path,null);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
运行说缺using指令,请问需加载什么指令空间,并:这段代码能实现吗?
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
工程需引用MS-EXCEL
asxulong(假如再有约会) ,这是ASP.NET的代码吧,偶想要在WINFORM下的,谢了!!
longdr(龙卷风),怎么写?using MS-EXCEL?出错,提示"-"处应输入";".
HttpResponse res = HttpContext.Current.Response;
res.Clear();
res.ContentType = "application/vnd.ms-excel";
res.Charset = "";
res.ContentEncoding= System.Text.Encoding.UTF8;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
this.mytd2.RenderControl(hw);
string httpHeader="attachment;filename=report.Xls";
res.AppendHeader("Content-Disposition", httpHeader);
res.Write(tw.ToString());
res.End();
呵呵，首先在工程引用微软的Excel库，如9.0或者10.0等
如果运行报错的话，可以考虑在源程序上方书写
using Excel=Microsoft.Office.Excel;
试一试，刚才那个导出程序在9.0是能跑的。
longdr(龙卷风),现在可以运行了,但点击Button后没有生成EXCEL文档,没什么反应,以下是源码,看看是哪出错了,非常感谢!
private void Form1_Load(object sender, System.EventArgs e)
{
string oleinfo="Provider=Microsoft.Jet.OleDb.4.0; data source=D:\\项目备份\\ok\\database\\ok.xls;Extended Properties=Excel 8.0;";
string strsel="SELECT 经理,[1$].站号,脂肪,蛋白,干物质,酸度,煮后酸度,温度,细菌,搀假,搀假备注,感官,感官备注,酒精试验,酒精试验备注,煮沸试验,判定,收奶,收奶量 FROM [1$] inner join [2$] on [1$].站号 like '%'+[2$].站号+'%' where 收奶='拒收'order by 经理";
//string strsel="SELECT * FROM [1$]";
OleDbConnection Myconn=new OleDbConnection(oleinfo);
Myconn.Open();
OleDbDataAdapter Mycomm = new OleDbDataAdapter (strsel,Myconn) ;
DataSet ds=new DataSet();
Mycomm.Fill(ds);
Myconn.Close();
dataGrid1.DataMember= "[1$]" ;
dataGrid1.DataSource =ds;
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,"ok",null);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
}
oWB.Close(false,"ok",null); 是什么意思?
找到的源码是oWB.Close(false,path,null);我看path处该是表名,所以改了一下,对吗?
高手快来救命啊!!自己顶!!
呵呵，你都没有把数据导出到Excel去，
那又怎么会有反应呢？
//datagrid内容到excel
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
{
DataTable table = (DataTable)grid.DataSource;
int sheetRow = 1;
//导出列头
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Columns[col].ColumnName;
}
sheetRow++;
//导出内容
for(int row = 0; row < table.Rows.Count; row++)
{
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Rows[row][col];
}
sheetRow++;
}
}
我也写了一个，自己试过了可以的
在mytable类中写
public void Binding(string sql,DataGrid dg)
{
DataSet ds = new DataSet();
//OleDbConnection MyConnection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + System.Web.HttpContext.Current.Server.MapPath(".")+"/test.mdb");
OleDbConnection MyConnection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + "VB.NET (F:):\et1\\lianxi\\test.mdb");
OleDbDataAdapter MyCommand = new OleDbDataAdapter(sql,MyConnection);
MyCommand.Fill(ds,"ta");
dg.DataSource = ds.Tables["ta"].DefaultView;
dg.DataBind();
}
然后private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
MkTable mt = new MkTable();
mt.Binding("Select * from login",MyDataGrid);
}
// ページを初期化するユーザー コードをここに挿入します。
}
private void button1_Click(object sender, System.EventArgs e)
{
MyDataGrid.AllowPaging = false;
MkTable mt = new MkTable();
mt.Binding("Select * from login",MyDataGrid);
// MyDataGrid.SelectedItemStyle.BackColor=Color.white
// MyDataGrid.AlternatingItemStyle.BackColor=Color.white
// MyDataGrid.ItemStyle.BackColor=Color.white
// MyDataGrid.HeaderStyle.BackColor=Color.white
// MyDataGrid.HeaderStyle.ForeColor=Color.red
HttpResponse resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
//Me.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
MyDataGrid.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
MyDataGrid.AllowPaging = true;
mt.Binding("Select * from login",MyDataGrid);
}
OK了
http://www.mscenter.edu.cn/blog/dragon1982/archive/2005/01/25/766.aspx
生成表格,然后将其保存了Excle文件
//////////////////////////////////////////////////////////////////////////////生成报表
string contstr1="<html><head><meta http-equiv=Content-Type content=text/html; charset=utf-8><title>定期统计报告</title></head><body>";
contstr1 += "<p align=center><strong>定 期 统 计 报 告</strong></p>";
string contstr="<table width=600 border=1 align=center cellspacing=0 bordercolor=#000000>";
contstr += "<tr>";
contstr += "<td>工号</td>";
contstr += "<td>姓名</td>";
contstr += "<td>部门</td>";
contstr += "<td>"+getyymmddfang(enddatestr)+"日均</td>";
if (bjj=="1")
{
contstr += "<td>"+getyymmddfang(enddatestr2)+"日均</td>";
contstr += "<td>日均差</td>";
}
contstr += "</tr>";
string filename="temp.htm";//
string strInsert7 = "select * from gonghaobao";
if(pxxx=="1")
{
if(bjj=="0")
{
strInsert7 +=" order by first_money desc,bm asc";
}
else
{
strInsert7 +=" order by zengzhang_money desc,first_money desc,bm asc";
}
}
else if(pxxx=="3")
{
if(bjj=="0")
{
strInsert7 +=" order by bm desc,first_money desc";
}
else
{
strInsert7 +=" order by bm desc,zengzhang_money desc";
}
}
else
{
strInsert7 +=" order by gonghao asc";
}
myConn.Open ( ) ;
if(myConn.State==System.Data.ConnectionState.Open)//判断myConn是否打开
{
//int gonghaoint;
OleDbCommand mycmd998=new OleDbCommand (strInsert7,myConn);
OleDbDataReader sdr5566=mycmd998.ExecuteReader();
//第一层循环,按照工号进行读取
while(sdr5566.Read())
{
//gonghaostr=sdr["gonghao"].ToString();
contstr += "<tr>";
contstr += "<td>"+sdr5566["gonghao"].ToString()+"</td>";
contstr += "<td>"+sdr5566["zgname"].ToString()+"</td>";
contstr += "<td>"+sdr5566["bm"].ToString()+"</td>";
contstr += "<td>"+sdr5566["first_money"].ToString()+"</td>";
if (bjj=="1")
{
contstr += "<td>"+sdr5566["end_money"].ToString()+"</td>";
contstr += "<td>"+sdr5566["zengzhang_money"].ToString()+"</td>";
}
contstr += "</tr>";
}
//统计
//查询语句
mycmd998.Dispose();
mycmd998=null;
}
contstr1=contstr1 + contstr;
contstr1 += "</table>";
if(bjj=="1")
{
contstr1 += "<p align=center>备注:统计是:从"+getyymmddfang(stardatestr)+"起"+getyymmddfang(enddatestr)+"与"+getyymmddfang(enddatestr2)+"的之间的日均</p>";
}
else
{
contstr1 += "<p align=center>备注:统计是:从"+getyymmddfang(stardatestr)+"起计算"+getyymmddfang(enddatestr)+"的日均</p>";
}
contstr1 += "<div align=center><SCRIPT LANGUAGE=JavaScript>";
contstr1 += "if (window.print) {document.write('<form>'+ '<input type=button name=print value=打印 '";
contstr1 +="+ 'onClick=javascript:window.print()></form>');}</script></div>";
contstr1 += "</body></html>";
makehtm(contstr,"temp.xls");//生成Excel
其中makehtm函数为:
private void makehtm(string contenstr,string filename)
{
string fisrtstr=Application.StartupPath+"//temp//";
using (StreamWriter sw = new StreamWriter(fisrtstr+filename))
{
// Add some text to the file.
sw.Write(contenstr);
}
}
这种方法是对简单的报表就合适.
lldwolf(铁背苍狼),你的程序我大概看懂了，只是在调用时
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
第二个参数怎么写，我想新开一个ＥＸＣＥＬ文档。
using System;
using System.Data;
using Excel;
using System.Reflection;
using System.Runtime.InteropServices;
using System.IO;
using System.Web;
using System.Diagnostics;
namespace HLDXS.NET.Comm
{
/// <summary>
/// excel_down 的摘要说明。
/// </summary>
public class excel_down:System.IDisposable
{
DataSet Excel_DS;
public excel_down(DataSet Excel_DS)
{
this.Excel_DS=Excel_DS;
}
public void Export_Excel(System.Web.UI.Page excel)
{
Excel.Application MyApp;
if(this.Excel_DS.Tables.Count<1)
{
return;
}
int table_count=this.Excel_DS.Tables.Count;
object oMissiong = System.Reflection.Missing.Value;
MyApp=new Excel.ApplicationClass();
Workbooks workbooks = MyApp.Workbooks;
_Workbook MyBook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Excel.Sheets mysheet=null;
mysheet=MyApp.Worksheets;
for(int i=0;i<table_count;i++)
{
Excel._Worksheet MySheet1=new Excel.WorksheetClass();
mysheet.Add(oMissiong,oMissiong,1,oMissiong);
}
int Columns=1;
Excel._Worksheet MySheets=null;
for(int i=0;i<table_count;i++)
{
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(i+1);
MySheets.Name=this.Excel_DS.Tables[i].TableName;
MySheets.Cells[1,Columns]=this.Excel_DS.Tables[i].TableName;
for(int k=0;k<this.Excel_DS.Tables[i].Columns.Count;k++)
{
MySheets.Cells[2,Columns+k]=this.Excel_DS.Tables[i].Columns[k].Caption;
for(int j=0;j<this.Excel_DS.Tables[i].Rows.Count;j++)
{
MySheets.Cells[j+3,Columns+k]=this.Excel_DS.Tables[i].Rows[j][k];
}
}
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Merge(0);
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Font.Size=15;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Font.Bold=true;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).RowHeight=24;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count]).Borders.Weight=(OWC.LineWeightEnum.owcLineWeightThin);
MySheets.get_Range(MySheets.Cells[2,Columns],MySheets.Cells[2,Columns+Excel_DS.Tables[i].Columns.Count]).ColumnWidth=10;
}
//删除第一个sheet
int tt=MyApp.Worksheets.Count;
MySheets=new Excel.WorksheetClass();
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(tt);
MySheets.Delete();
MySheets=null;
//
string filename=excel.MapPath("")+"/"+this.Excel_DS.DataSetName+".xls";
MySheets=new Excel.WorksheetClass();
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(1);
MyApp.Visible=false;
MySheets.SaveAs(filename,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
MyApp.Workbooks.Close();
MyApp.Quit();
MyApp=null;
GC.Collect();
HttpResponse resp;
resp =excel.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
string DownFileName=this.Excel_DS.DataSetName+".xls";
resp.AppendHeader("Content-Disposition", "attachment;filename=" +DownFileName);
FileInfo MyFileInfo;
long StartPos = 0, FileSize;
MyFileInfo = new FileInfo(filename);
FileSize = MyFileInfo.Length;
resp.WriteFile(filename, StartPos, FileSize);
resp.Flush();
MyFileInfo.Delete();
excel.Response.End();
}
public void Dispose()
{
this.Excel_DS=null;
}
}
}
完整的一个 dataset 导出一个excel 有几个表就导出几个sheet
我是这样调用的，点Ｂutton后报错：＂指定转换失败，错误行：
DataTable table = (DataTable)grid.DataSource;
这样调用对吗？该怎么改？
>>>>>>>>>>>>>>>>>>private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,"D:\\项目备份\\ok\\database\\ok.xls",null);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
DatagridToExcel(dataGrid1,oSheet);
}
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
{
DataTable table = (DataTable)grid.DataSource;
int sheetRow = 1;
//导出列头
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Columns[col].ColumnName;
}
sheetRow++;
//导出内容
for(int row = 0; row < table.Rows.Count; row++)
{
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Rows[row][col];
}
sheetRow++;
}
}
忘了说两句上面的最好用于web
忘了说两句上面的最好用于web
slon3dmax(slon3dmax)， hky5_com(绿源人) ，非常感谢，但很不幸，我在ＷＥＢ下以实现，这个正是ＷＥＢ换ＷＩＮＦＯＲＭ！唉～～
xjaifly(tiantian) ，非常感谢你提供的资料，已收藏，只是没找到这个要用的．
c# winform 中如何把datagrid中查询出的数据导入excel?或有什么ESAY方法把数据拷出来也行,就像ASP.NET中的DataGrid一样显示的数据可拷出(Ctrl+V)来?
高手如知如何导请附一下原码,非常感谢!!
:)
-
这里有个VB.NET利用数组的例子，自己改改好了
.NET下 从 DataView DataSet DataTable 导出数据至Excel
Public Sub Exports2Excel(ByVal Dtg As DataGrid)
If Dtg.VisibleRowCount > 0 Then
Try
Me.Cursor = Cursors.WaitCursor
Dim datav As New DataView
If TypeOf Dtg.DataSource Is DataView Then
datav = CType(Dtg.DataSource, DataView)
ElseIf TypeOf Dtg.DataSource Is DataSet Then
datav = CType(Dtg.DataSource, DataSet).Tables(0).DefaultView
ElseIf TypeOf Dtg.DataSource Is DataTable Then
datav = CType(Dtg.DataSource, DataTable).DefaultView
End If
Dim i, j As Integer
Dim rows As Integer = datav.Table.Rows.Count
Dim cols As Integer = datav.Table.Columns.Count
Dim DataArray(rows - 1, cols - 1) As String
Dim myExcel As Excel.Application = New Excel.Application
For i = 0 To rows - 1
For j = 0 To cols - 1
DataArray(i, j) = datav.Table.Rows(i).Item(j)
Next
Next
myExcel.Application.Workbooks.Add(True)
myExcel.Visible = True
For j = 0 To cols - 1
myExcel.Cells(1, j + 1) = datav.Table.Columns(j).ColumnName
Next
myExcel.Range("A2").Resize(rows, cols).Value = DataArray
Catch exp As Exception
MessageBox.Show("数据导出失败!请查看是否已经安装了Excel", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)
Finally
Me.Cursor = Cursors.Default
End Try
Else
MessageBox.Show("没有数据!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub
帮顶！
谢谢longdr(龙卷风)!这是我在ASP.NET下写的一个程序想转成WINFROM,偶对这个不熟,现找到一段源码:Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,path,null);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
运行说缺using指令,请问需加载什么指令空间,并:这段代码能实现吗?
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
工程需引用MS-EXCEL
asxulong(假如再有约会) ,这是ASP.NET的代码吧,偶想要在WINFORM下的,谢了!!
longdr(龙卷风),怎么写?using MS-EXCEL?出错,提示"-"处应输入";".
HttpResponse res = HttpContext.Current.Response;
res.Clear();
res.ContentType = "application/vnd.ms-excel";
res.Charset = "";
res.ContentEncoding= System.Text.Encoding.UTF8;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
this.mytd2.RenderControl(hw);
string httpHeader="attachment;filename=report.Xls";
res.AppendHeader("Content-Disposition", httpHeader);
res.Write(tw.ToString());
res.End();
呵呵，首先在工程引用微软的Excel库，如9.0或者10.0等
如果运行报错的话，可以考虑在源程序上方书写
using Excel=Microsoft.Office.Excel;
试一试，刚才那个导出程序在9.0是能跑的。
longdr(龙卷风),现在可以运行了,但点击Button后没有生成EXCEL文档,没什么反应,以下是源码,看看是哪出错了,非常感谢!
private void Form1_Load(object sender, System.EventArgs e)
{
string oleinfo="Provider=Microsoft.Jet.OleDb.4.0; data source=D:\\项目备份\\ok\\database\\ok.xls;Extended Properties=Excel 8.0;";
string strsel="SELECT 经理,[1$].站号,脂肪,蛋白,干物质,酸度,煮后酸度,温度,细菌,搀假,搀假备注,感官,感官备注,酒精试验,酒精试验备注,煮沸试验,判定,收奶,收奶量 FROM [1$] inner join [2$] on [1$].站号 like '%'+[2$].站号+'%' where 收奶='拒收'order by 经理";
//string strsel="SELECT * FROM [1$]";
OleDbConnection Myconn=new OleDbConnection(oleinfo);
Myconn.Open();
OleDbDataAdapter Mycomm = new OleDbDataAdapter (strsel,Myconn) ;
DataSet ds=new DataSet();
Mycomm.Fill(ds);
Myconn.Close();
dataGrid1.DataMember= "[1$]" ;
dataGrid1.DataSource =ds;
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,"ok",null);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
}
oWB.Close(false,"ok",null); 是什么意思?
找到的源码是oWB.Close(false,path,null);我看path处该是表名,所以改了一下,对吗?
高手快来救命啊!!自己顶!!
呵呵，你都没有把数据导出到Excel去，
那又怎么会有反应呢？
//datagrid内容到excel
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
{
DataTable table = (DataTable)grid.DataSource;
int sheetRow = 1;
//导出列头
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Columns[col].ColumnName;
}
sheetRow++;
//导出内容
for(int row = 0; row < table.Rows.Count; row++)
{
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Rows[row][col];
}
sheetRow++;
}
}
我也写了一个，自己试过了可以的
在mytable类中写
public void Binding(string sql,DataGrid dg)
{
DataSet ds = new DataSet();
//OleDbConnection MyConnection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + System.Web.HttpContext.Current.Server.MapPath(".")+"/test.mdb");
OleDbConnection MyConnection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + "VB.NET (F:):\et1\\lianxi\\test.mdb");
OleDbDataAdapter MyCommand = new OleDbDataAdapter(sql,MyConnection);
MyCommand.Fill(ds,"ta");
dg.DataSource = ds.Tables["ta"].DefaultView;
dg.DataBind();
}
然后private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
MkTable mt = new MkTable();
mt.Binding("Select * from login",MyDataGrid);
}
// ページを初期化するユーザー コードをここに挿入します。
}
private void button1_Click(object sender, System.EventArgs e)
{
MyDataGrid.AllowPaging = false;
MkTable mt = new MkTable();
mt.Binding("Select * from login",MyDataGrid);
// MyDataGrid.SelectedItemStyle.BackColor=Color.white
// MyDataGrid.AlternatingItemStyle.BackColor=Color.white
// MyDataGrid.ItemStyle.BackColor=Color.white
// MyDataGrid.HeaderStyle.BackColor=Color.white
// MyDataGrid.HeaderStyle.ForeColor=Color.red
HttpResponse resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
//Me.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
MyDataGrid.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
MyDataGrid.AllowPaging = true;
mt.Binding("Select * from login",MyDataGrid);
}
OK了
http://www.mscenter.edu.cn/blog/dragon1982/archive/2005/01/25/766.aspx
生成表格,然后将其保存了Excle文件
//////////////////////////////////////////////////////////////////////////////生成报表
string contstr1="<html><head><meta http-equiv=Content-Type content=text/html; charset=utf-8><title>定期统计报告</title></head><body>";
contstr1 += "<p align=center><strong>定 期 统 计 报 告</strong></p>";
string contstr="<table width=600 border=1 align=center cellspacing=0 bordercolor=#000000>";
contstr += "<tr>";
contstr += "<td>工号</td>";
contstr += "<td>姓名</td>";
contstr += "<td>部门</td>";
contstr += "<td>"+getyymmddfang(enddatestr)+"日均</td>";
if (bjj=="1")
{
contstr += "<td>"+getyymmddfang(enddatestr2)+"日均</td>";
contstr += "<td>日均差</td>";
}
contstr += "</tr>";
string filename="temp.htm";//
string strInsert7 = "select * from gonghaobao";
if(pxxx=="1")
{
if(bjj=="0")
{
strInsert7 +=" order by first_money desc,bm asc";
}
else
{
strInsert7 +=" order by zengzhang_money desc,first_money desc,bm asc";
}
}
else if(pxxx=="3")
{
if(bjj=="0")
{
strInsert7 +=" order by bm desc,first_money desc";
}
else
{
strInsert7 +=" order by bm desc,zengzhang_money desc";
}
}
else
{
strInsert7 +=" order by gonghao asc";
}
myConn.Open ( ) ;
if(myConn.State==System.Data.ConnectionState.Open)//判断myConn是否打开
{
//int gonghaoint;
OleDbCommand mycmd998=new OleDbCommand (strInsert7,myConn);
OleDbDataReader sdr5566=mycmd998.ExecuteReader();
//第一层循环,按照工号进行读取
while(sdr5566.Read())
{
//gonghaostr=sdr["gonghao"].ToString();
contstr += "<tr>";
contstr += "<td>"+sdr5566["gonghao"].ToString()+"</td>";
contstr += "<td>"+sdr5566["zgname"].ToString()+"</td>";
contstr += "<td>"+sdr5566["bm"].ToString()+"</td>";
contstr += "<td>"+sdr5566["first_money"].ToString()+"</td>";
if (bjj=="1")
{
contstr += "<td>"+sdr5566["end_money"].ToString()+"</td>";
contstr += "<td>"+sdr5566["zengzhang_money"].ToString()+"</td>";
}
contstr += "</tr>";
}
//统计
//查询语句
mycmd998.Dispose();
mycmd998=null;
}
contstr1=contstr1 + contstr;
contstr1 += "</table>";
if(bjj=="1")
{
contstr1 += "<p align=center>备注:统计是:从"+getyymmddfang(stardatestr)+"起"+getyymmddfang(enddatestr)+"与"+getyymmddfang(enddatestr2)+"的之间的日均</p>";
}
else
{
contstr1 += "<p align=center>备注:统计是:从"+getyymmddfang(stardatestr)+"起计算"+getyymmddfang(enddatestr)+"的日均</p>";
}
contstr1 += "<div align=center><SCRIPT LANGUAGE=JavaScript>";
contstr1 += "if (window.print) {document.write('<form>'+ '<input type=button name=print value=打印 '";
contstr1 +="+ 'onClick=javascript:window.print()></form>');}</script></div>";
contstr1 += "</body></html>";
makehtm(contstr,"temp.xls");//生成Excel
其中makehtm函数为:
private void makehtm(string contenstr,string filename)
{
string fisrtstr=Application.StartupPath+"//temp//";
using (StreamWriter sw = new StreamWriter(fisrtstr+filename))
{
// Add some text to the file.
sw.Write(contenstr);
}
}
这种方法是对简单的报表就合适.
lldwolf(铁背苍狼),你的程序我大概看懂了，只是在调用时
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
第二个参数怎么写，我想新开一个ＥＸＣＥＬ文档。
using System;
using System.Data;
using Excel;
using System.Reflection;
using System.Runtime.InteropServices;
using System.IO;
using System.Web;
using System.Diagnostics;
namespace HLDXS.NET.Comm
{
/// <summary>
/// excel_down 的摘要说明。
/// </summary>
public class excel_down:System.IDisposable
{
DataSet Excel_DS;
public excel_down(DataSet Excel_DS)
{
this.Excel_DS=Excel_DS;
}
public void Export_Excel(System.Web.UI.Page excel)
{
Excel.Application MyApp;
if(this.Excel_DS.Tables.Count<1)
{
return;
}
int table_count=this.Excel_DS.Tables.Count;
object oMissiong = System.Reflection.Missing.Value;
MyApp=new Excel.ApplicationClass();
Workbooks workbooks = MyApp.Workbooks;
_Workbook MyBook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Excel.Sheets mysheet=null;
mysheet=MyApp.Worksheets;
for(int i=0;i<table_count;i++)
{
Excel._Worksheet MySheet1=new Excel.WorksheetClass();
mysheet.Add(oMissiong,oMissiong,1,oMissiong);
}
int Columns=1;
Excel._Worksheet MySheets=null;
for(int i=0;i<table_count;i++)
{
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(i+1);
MySheets.Name=this.Excel_DS.Tables[i].TableName;
MySheets.Cells[1,Columns]=this.Excel_DS.Tables[i].TableName;
for(int k=0;k<this.Excel_DS.Tables[i].Columns.Count;k++)
{
MySheets.Cells[2,Columns+k]=this.Excel_DS.Tables[i].Columns[k].Caption;
for(int j=0;j<this.Excel_DS.Tables[i].Rows.Count;j++)
{
MySheets.Cells[j+3,Columns+k]=this.Excel_DS.Tables[i].Rows[j][k];
}
}
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Merge(0);
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Font.Size=15;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Font.Bold=true;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).RowHeight=24;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count]).Borders.Weight=(OWC.LineWeightEnum.owcLineWeightThin);
MySheets.get_Range(MySheets.Cells[2,Columns],MySheets.Cells[2,Columns+Excel_DS.Tables[i].Columns.Count]).ColumnWidth=10;
}
//删除第一个sheet
int tt=MyApp.Worksheets.Count;
MySheets=new Excel.WorksheetClass();
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(tt);
MySheets.Delete();
MySheets=null;
//
string filename=excel.MapPath("")+"/"+this.Excel_DS.DataSetName+".xls";
MySheets=new Excel.WorksheetClass();
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(1);
MyApp.Visible=false;
MySheets.SaveAs(filename,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
MyApp.Workbooks.Close();
MyApp.Quit();
MyApp=null;
GC.Collect();
HttpResponse resp;
resp =excel.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
string DownFileName=this.Excel_DS.DataSetName+".xls";
resp.AppendHeader("Content-Disposition", "attachment;filename=" +DownFileName);
FileInfo MyFileInfo;
long StartPos = 0, FileSize;
MyFileInfo = new FileInfo(filename);
FileSize = MyFileInfo.Length;
resp.WriteFile(filename, StartPos, FileSize);
resp.Flush();
MyFileInfo.Delete();
excel.Response.End();
}
public void Dispose()
{
this.Excel_DS=null;
}
}
}
完整的一个 dataset 导出一个excel 有几个表就导出几个sheet
我是这样调用的，点Ｂutton后报错：＂指定转换失败，错误行：
DataTable table = (DataTable)grid.DataSource;
这样调用对吗？该怎么改？
>>>>>>>>>>>>>>>>>>private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,"D:\\项目备份\\ok\\database\\ok.xls",null);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
DatagridToExcel(dataGrid1,oSheet);
}
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
{
DataTable table = (DataTable)grid.DataSource;
int sheetRow = 1;
//导出列头
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Columns[col].ColumnName;
}
sheetRow++;
//导出内容
for(int row = 0; row < table.Rows.Count; row++)
{
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Rows[row][col];
}
sheetRow++;
}
}
忘了说两句上面的最好用于web
忘了说两句上面的最好用于web
slon3dmax(slon3dmax)， hky5_com(绿源人) ，非常感谢，但很不幸，我在ＷＥＢ下以实现，这个正是ＷＥＢ换ＷＩＮＦＯＲＭ！唉～～
xjaifly(tiantian) ，非常感谢你提供的资料，已收藏，只是没找到这个要用的．
