从数据库取出记录绑定到datagrid,在本地机生成EXECEL表,将记录导入EXECEL.是不是先使用datatable的select取出记录,将取出的记录数组存在SESSION中,然后建立EXECEL,将SESSION值写入EXECEL我的想法可行吗?
求建立EXECEL的代码.如果可以,最好可以把导入部分写全.
除了要导入SYSTEM.IO 还要导入身摸?
求建立EXECEL的代码.如果可以,最好可以把导入部分写全.
除了要导入SYSTEM.IO 还要导入身摸?
解决方案 »
- 客户端请求页面时,网站的哪些文件是下载到客户端的,保存在哪里?
- 如何使用一条语句将数据库里的一列数据修改为不同值[新手问题]
- 如何建设wap网站?
- Lucene如何进行查询?
- 在webserivce 中如何传入大容量的数据?
- 请问我在.net 2003 写的asp.net程序,在framwork1.0的服务器上能跑起来吗?
- 巨难:在一网站中不管用户在网页中点击什么连接或打开什么新窗口我要URL始终只显示http://www.域名.com,如何做?
- net中的错误提示框在当前窗口跳出的代码
- 关于VS.net 2003 安装问题 在线等
- asp.net
- asp.net多語言問題
- 在线等,对你们说来小小的问题,谢谢各位
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace TestAccess
{
class Program
{
static void Main(string[] args)
{
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";
strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
OleDbConnection objConnection = new OleDbConnection(strConnection);
objConnection.Open();
OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);
DataSet ds = new DataSet();
myCommandd.Fill(ds, "[Sheet1$]");
System.Data.DataTable dt = ds.Tables["[Sheet1$]"];
Console.WriteLine(dt.Columns[0].ToString());
Console.WriteLine(dt.Columns[1].ToString());
DataRow drDisplay = dt.Rows[0];
int[] num = new int[dt.Columns.Count];
for (int j = 0; ; )
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (drDisplay[i] is DBNull) ;
else
num[i] += Convert.ToInt32(drDisplay[i]);
}
if (++j >= dt.Rows.Count) break;
drDisplay = dt.Rows[j];
}
objConnection.Close();
object MissingValue = Type.Missing;
Microsoft.Office.Interop.Excel.Application app = new Application();
Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue);
Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
//注意下面是i+1,,excel小标默认从1开始
wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();
}
wbook.Save();
wbook.Close(true, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wsheet = null;
wbook = null;
app = null;
GC.Collect();
}
}
}
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/LCL_data/archive/2009/05/06/4154784.aspx
==========================
先使用datatable的select取出记录 然后就读入execl就可~~
【1】添加Interop Excel.dll【2】using Excel;【2】普通DataTable导入EXCELprotected void IBtnDownExcel_Click(object sender, ImageClickEventArgs e) { SQLPeople SqlPeopleInfo = new SQLPeople(); DataTable PeopleTable = new DataTable(); PeopleTable = SqlPeopleInfo.SelectMemberList(); CreateExcel(PeopleTable, "1", "PeopleExcel"); } #region 普通的Excel导出 /// <summary> /// DataTable To Excel /// </summary> /// <param name="dt">DataTable Name</param> /// <param name="typeid">1,Excel 2,XML</param> /// <param name="FileName">文件名</param> public void CreateExcel(System.Data.DataTable dt, string typeid, string FileName) { HttpResponse resp; resp = Page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.ContentType = "application/ms-excel";
resp.AddHeader("Content-Disposition","attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
this.EnableViewState = false;
string colHeaders = "", Is_item = ""; int i = 0;
//定义表对象与行对象,同时使用DataSet对其值进行初始化 DataRow[] myRow = dt.Select(""); //typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML文件 if (typeid == "1") {
//取得数据表各列标题,标题之间以\t分割,最后一个列标题后加回车符 for (i = 0; i < dt.Columns.Count; i++) { colHeaders += dt.Columns[i].Caption.ToString() + "\t"; } colHeaders += "\n";
resp.Write(colHeaders); //逐行处理数据 foreach (DataRow row in myRow) { //在当前行中,逐列取得数据,数据之间以\t分割,结束时加回车符\n for (i = 0; i < dt.Columns.Count; i++) { Is_item += row[i].ToString() + "\t"; } Is_item += "\n"; resp.Write(Is_item); Is_item = ""; } } else { if (typeid == "2") { //从DataSet中直接导出XML数据并且写到HTTP输出流中 resp.Write(dt.DataSet.GetXml()); } } //写缓冲区中的数据到HTTP头文件中 resp.End(); } #endregion
' Create the Excel Application object
Dim excelApp As New ApplicationClass() ' Create a new Excel Workbook
Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing) Dim sheetIndex As Integer = 0
Dim col, row As Integer
Dim excelSheet As Worksheet ' Copy each DataTable as a new Sheet
For Each dt As System.Data.DataTable In dataSet.Tables sheetIndex += 1 ' Copy the DataTable to an object array
Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object ' Copy the column names to the first row of the object array
For col = 0 To dt.Columns.Count - 1
rawData(0, col) = dt.Columns(col).ColumnName
Next ' Copy the values to the object array
For col = 0 To dt.Columns.Count - 1
For row = 0 To dt.Rows.Count - 1
rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
Next
Next ' Calculate the final column letter
Dim finalColLetter As String = String.Empty
Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim colCharsetLen As Integer = colCharset.Length If dt.Columns.Count > colCharsetLen Then
finalColLetter = colCharset.Substring( _
(dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
End If finalColLetter += colCharset.Substring( _
(dt.Columns.Count - 1) Mod colCharsetLen, 1) ' Create a new Sheet
excelSheet = CType( _
excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex), _
Type.Missing, 1, XlSheetType.xlWorksheet), Worksheet) excelSheet.Name = dt.TableName ' Fast data export to Excel
Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
excelSheet.Range(excelRange, Type.Missing).Value2 = rawData ' Mark the first row as BOLD
CType(excelSheet.Rows(1, Type.Missing), Range).Font.Bold = True excelSheet = Nothing
Next ' Save and Close the Workbook
excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, _
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing) excelWorkbook.Close(True, Type.Missing, Type.Missing) excelWorkbook = Nothing ' Release the Application object
excelApp.Quit()
excelApp = Nothing ' Collect the unreferenced objects
GC.Collect()
GC.WaitForPendingFinalizers() End SubEnd Module网上有很多代码 ~~楼主懒
不过这个是dataset转execl和datatable转execl是一样的~~
Private Sub WriteDataGrid2Excel()
Dim xlsheet As New SpreadsheetClass()
cnn.Open()
Dim reader As OleDbDataReader = Me.sql.ExecuteReader()
Dim numbercols As Integer = reader.FieldCount
Dim row As Integer = 2
Dim i As Integer = 0
' 输出标题
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(1, i + 1) = reader.GetName(i).ToString()
Next ' 输出字段内容
While (reader.Read())
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(row, i + 1) = reader.GetValue(i).ToString()
Next
row = row + 1
End While
reader.Close()
cnn.Close()
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As System.Runtime.InteropServices.COMException
Response.Write("错误:" + e.Message)
End Try
http://topic.csdn.net/u/20090411/11/49374dac-48d6-484b-9e9e-21737404dcdd.html
End Sub