我写了段代码(C#),但是有错误,正确的应该怎么写啊,哪位高手帮我解决啊.先谢谢了
string con="server=.;database=hnkjxy;uid=sa;pwd=";
string com="select * from MangersInfo";
SqlDataAdapter da=new SqlDataAdapter(com,con);
DataSet ds=new DataSet();
da.Fill(ds);
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
DataRow row;
DataColumn coll;int colIndex=0;
int rowIndex=1;
for( int j=0; j<ds.Tables[0].Rows.Count;j++ )
{
colIndex = colIndex + 1;
for( int k=0; k<ds.Tables[0].Columns.Count; k++ )
{
colIndex = colIndex + 1;
xlApp.Cells(rowIndex,colIndex)=row(coll.ColumnName);
}
}
xlApp.Application.Workbooks.Add(true);
xlApp.Visible=true;
string con="server=.;database=hnkjxy;uid=sa;pwd=";
string com="select * from MangersInfo";
SqlDataAdapter da=new SqlDataAdapter(com,con);
DataSet ds=new DataSet();
da.Fill(ds);
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
DataRow row;
DataColumn coll;int colIndex=0;
int rowIndex=1;
for( int j=0; j<ds.Tables[0].Rows.Count;j++ )
{
colIndex = colIndex + 1;
for( int k=0; k<ds.Tables[0].Columns.Count; k++ )
{
colIndex = colIndex + 1;
xlApp.Cells(rowIndex,colIndex)=row(coll.ColumnName);
}
}
xlApp.Application.Workbooks.Add(true);
xlApp.Visible=true;
http://www.microsoft.com/china/community/Column/24.mspx
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders= "", ls_item="";
int i=0;
//定义表对象与行对像,同时用DataSet对其值进行初始化
DataTable dt=ds.Tables[0];
DataRow[] myRow=dt.Select("");
// typeid=="1"时导出为EXCEL格式文件;typeid=="2"时导出为XML格式文件
if(typeid=="1")
{
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for(i=0;i<dt.Columns.Count-1;i++)
colHeaders+=dt.Columns[i].Caption.ToString()+"\t";
colHeaders +=dt.Columns[i].Caption.ToString() +"\n";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach(DataRow row in myRow)
{
//在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n
for(i=0;i<row.Table.Columns.Count-1;i++)
ls_item +=row[i].ToString() + "\t";
ls_item += row[i].ToString() +"\n";
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item="";
}
}
}
DataTable dt = (DataTable)this.Session["TempTest"] ;
this.dg_save.DataSource = dt ;
this.dg_save.DataBind() ;
//---------------------------------------------------------
HttpContext.Current.Response.Charset ="";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8 ;
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=result.xls");
// HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
//关闭控件的视图状态
this.dg_save.Page.EnableViewState =false;
//初始化writer
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
this.dg_save.RenderControl(hw);
//输出
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();===================================================================================
前面的我就不翻译了。老外操作的是Excel 2000。所有Excel的程序操作都来源于Excel的对象库Excel9.olb.本例也只是对这个东东做一个简单的操作了解。有告诫待于朋友们的具体了解:)也算是为我们站点上天天为Excel烦的兄弟们,指一条“明路”吧:)
首先的一步就是使用Tlbimp这个工具将Excel9.0的对象库文件Excel8.olb转换成为dll,这样才能做为.Net平台Assembly来使用:)操作如下: TlbImp Excel9.olb Excel.dll 只要有了这个Excel.dll,现在我们就能使用Excel的各种操作函数了。
下面就让我们具体看看C#是如何使用这些东东吧。 1. 创建一个新Excel的Application:
Application exc = new Application();
if (exc == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started");
return 0;
}
2. 让这个工程可见: exc.set_Visible(0, true); 3. 获取WorkBooks集合: Workbooks workbooks = exc.Workbooks; 4. 加入新的WorkBook: _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0); 5. 获取WorkSheets集合:
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null) {
Console.WriteLine ("ERROR in worksheet == null");
}
6. 给单元格设置变量:
Range range1 = worksheet.get_Range("C1", Missing.Value);
if (range1 == null) {
Console.WriteLine ("ERROR: range == null");
}
const int nCells = 1;
Object[] args1 = new Object[1];
args1[0] = nCells;
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range1, args1);
例程:
using System;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel; class Excel {
public static int Main() {
Application exc = new Application();
if (exc == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started!");
return 0;
} exc.set_Visible(0, true);
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0);
Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null) {
Console.WriteLine ("ERROR: worksheet == null");
} Range range1 = worksheet.get_Range("C1", Missing.Value);
if (range1 == null) {
Console.WriteLine ("ERROR: range == null");
}
const int nCells = 1;
Object[] args1 = new Object[1];
args1[0] = nCells;
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,range1, args1);
return 100;
}
} 现在我们来看看如何使用数组,他有些类似于设置单元格。仅仅需要的改变只是args2[0] = array2;
const int nCell = 5;
Range range2 = worksheet.get_Range("A1", "E1");
int[] array2 = new int [nCell];
for (int i=0; i < array2.GetLength(0); i++) {
array2[i] = i+1;
}
Object[] args2 = new Object[1];
args2[0] = array2;
range2.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range2, args2);
输出结果: 大家需要了解Tlbimp这个工具的使用啊:)这个东东很有用,可以将普通Win32程序移植到.Net下面来:)
我用代码试过的,没有问题!
或者考虑直接导出成为csv,文件名保存位xsl就可以了:0偷懒的办法
ActiveX控件在实际中会降低浏览器的安全等级???要是会就好了...呵呵,不是会降低,是你要手工去降低以后才能正常使用....我怎么没想起Excel是Sun出的???? 微软的东西微软自己不能完美控制,反是Sun的东西可以....搞笑哦,呵呵.
提醒各位几句,其实除了常规方法,思维放开些,还有很多简单得多的非常规方法:
1 Excel不只是xls一种,如果是csv格式,都解决了.
2 普通的html的Table标签的txt(或htm,asp,aspx...)等,直接改后缀名为xls, Excel可以直接当普通的xls打开的.
3上面的第2点的那种xls(假xls)如果要变成真的,只要把xls文件用Excel打开,"另存为"就行了.当然这件事可以由程序来做,就几句.
4 事先准备一个用来放数据的空模板(如果没有格式要求,其实就是一个空的xls文件),需要导出的时候把这个文件Copy一份(System.IO 呵呵),然后直接向里面用Sql语句Insert数据(System.Data.OleDb).... 连Excel组件都不需要引用......完全托管代码,呵呵.
但有个地方要改一下
public DataSet GetDataSetFromExcel(string filePath)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;";
strConn += "data source=" + filepath + ";";//filepath改为:Server.MapPath(filePath)
strConn += "Extended Properties=Excel 8.0;";
//strConn += "HDR=Yes;IMEX=1"; OleDbConnection objConn = new OleDbConnection(strConn);
ArrayList SheetNameList = new ArrayList();
try
{
objConn.Open(); DataTable dtExcelSchema = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
string SheetName=""; for(int i=0;i<dtExcelSchema.Rows.Count;i++)
{
SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
SheetNameList.Add( SheetName );
}
}
catch(Exception ex)
{
throw ex;
}
finally
{
objConn.Close();
} DataSet dsExcel = new DataSet();
try
{
string strSql = "";
for(int i=0; i<SheetNameList.Count; i++)
{
//string strSql ="select * From [Sheet1$e5:g23]";
//string strSql ="select [F1],[F2],[F3] From [Sheet2$3:100] ";
strSql = "select * from [" + (string)SheetNameList[i] + "]";
OleDbDataAdapter oleExcelDataAdapter = new OleDbDataAdapter(strSql, objConn);
DataTable dtExcel = new DataTable((string)SheetNameList[i]);
oleExcelDataAdapter.Fill(dtExcel);
dsExcel.Tables.Add(dtExcel);
}
return dsExcel;
}
catch(Exception ex)
{
throw ex;
}
}