ASP.NET中数据导入至Excel
把asp.net中的数据直接导入至excel表中的例子。
1.web.config中加入
<identity impersonate="true"/>
2.用Microsoft.Excel 11.0 Object Library COM组件
3.下面代码。
1.web.config中加入
<identity impersonate="true"/>
2.用Microsoft.Excel 11.0 Object Library COM组件
3.下面代码。
1using System;
2using System.Collections;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Web;
7using System.Web.SessionState;
8using System.Web.UI;
9using System.Web.UI.WebControls;
10using System.Web.UI.HtmlControls;
11using System.Data.SqlClient;
12using System.Configuration;
13using System.Runtime.InteropServices;
14using Excel;
15using System.Reflection;
16
17namespace CSharpNET_ExcelTest
18{
19 /// <summary>
20 /// WebForm1 的摘要说明。
21 /// </summary>
22 public class WebForm1 : System.Web.UI.Page
23 {
24 private void Page_Load(object sender, System.EventArgs e)
25 {
26 Excel.Application oExcel=new Excel.Application();
27 Excel.Workbooks oBooks;
28 Excel.Workbook oBook;
29 Excel.Sheets oSheets;
30 Excel.Worksheet oSheet;
31 Excel.Range oCells;
32 string sFile,sTemplate;
33 DataSet objDataset=new DataSet();
34
35 System.Data.DataTable dt = ((System.Data.DataTable)(CreateDataSource().Tables[0]));
36
37 sFile=Server.MapPath(Request.ApplicationPath)+@"\MyExcel.xls";
38 sTemplate=Server.MapPath(Request.ApplicationPath) +@"\MyTemplate.xls";
39 oExcel.DisplayAlerts=false;
40 oBooks=oExcel.Workbooks;
41
42 try
43 {
44 // 在VB.net中一般用open
45 oBooks.Add(Server.MapPath(Request.ApplicationPath) + @"\MyTemplate.xls") ;
46 }
47 catch
48 {
49 }
50
51 oBook=oBooks[1];
52 oSheets=oBook.Worksheets;
53 oSheet=(Excel.Worksheet) oSheets[1];
54
55 oBook = oBooks[1];
56 oSheet.Name="First Sheet";
57 oCells=oSheet.Cells;
58 DumpData(dt,oCells);
59
60 // 注意此处为10个参数
61 oSheet.SaveAs(sFile,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
62 Excel.XlSaveAsAccessMode.xlExclusive,Missing.Value,Missing.Value,
63 Missing.Value);
64
65 oBook.Close(Missing.Value,Missing.Value,Missing.Value);
66
67 oExcel.Quit();
68 Marshal.ReleaseComObject(oCells);
69 Marshal.ReleaseComObject(oSheet);
70
71 Marshal.ReleaseComObject(oSheets) ;
72 Marshal.ReleaseComObject(oBook);
73 Marshal.ReleaseComObject(oBooks);
74 Marshal.ReleaseComObject(oExcel);
75
76 oExcel = null;
77 oBooks = null;
78 oBook = null;
79
80 oSheets = null;
81 oSheet = null ;
82 oCells = null;
83 System.GC.Collect();
84 Response.Redirect(sFile);
85
86 }
87
88 private void DumpData(System.Data.DataTable dt,Excel.Range oCells)
89 {
90 int iCol;
91 int iRow;
92 DataRow dr;
93 object[] ary;
94 for (iCol=0;iCol<dt.Columns.Count;iCol++)
95 {
96 oCells[2,iCol+1]=dt.Columns[iCol].ToString();
97 }
98
99 for (iRow=0 ;iRow<dt.Rows.Count;iRow++)
100 {
101 dr=dt.Rows[iRow];
102 ary=dr.ItemArray;
103 for(iCol=0 ;iCol<ary.GetUpperBound(0);iCol++)
104 {
105 oCells[iRow+3,iCol+1]=ary[iCol].ToString();
106 Response.Write(ary[iCol].ToString()+"\t");
107 }
108 }
109 }
110
111 public System.Data.DataSet CreateDataSource()
112 {
113 System.Data.DataSet ds = new System.Data.DataSet();
114 System.Data.DataTable dt = new System.Data.DataTable();
115 System.Data.DataRow dr;
116 dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));
117 dt.Columns.Add(new DataColumn("图书单价", typeof(float)));
118 dt.Columns.Add(new DataColumn("购买数量", typeof(Int32)));
119 dt.Columns.Add(new DataColumn("总价格", typeof(float)));
120 for (Int32 i = 0; i <= 10; i++)
121 {
122 dr = dt.NewRow();
123 dr[0] = "123456789123456789";
124 dr[1] = 100 * i / 3;
125 dr[2] = i + 5;
126 dr[3] = (float)dr[1] * (Int32) dr[2];
127 dt.Rows.Add(dr);
128 }
129 ds.Tables.Add(dt);
130 return ds;
131 }
132
133
134 Web 窗体设计器生成的代码
153 }
154}
155
2using System.Collections;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Web;
7using System.Web.SessionState;
8using System.Web.UI;
9using System.Web.UI.WebControls;
10using System.Web.UI.HtmlControls;
11using System.Data.SqlClient;
12using System.Configuration;
13using System.Runtime.InteropServices;
14using Excel;
15using System.Reflection;
16
17namespace CSharpNET_ExcelTest
18{
19 /// <summary>
20 /// WebForm1 的摘要说明。
21 /// </summary>
22 public class WebForm1 : System.Web.UI.Page
23 {
24 private void Page_Load(object sender, System.EventArgs e)
25 {
26 Excel.Application oExcel=new Excel.Application();
27 Excel.Workbooks oBooks;
28 Excel.Workbook oBook;
29 Excel.Sheets oSheets;
30 Excel.Worksheet oSheet;
31 Excel.Range oCells;
32 string sFile,sTemplate;
33 DataSet objDataset=new DataSet();
34
35 System.Data.DataTable dt = ((System.Data.DataTable)(CreateDataSource().Tables[0]));
36
37 sFile=Server.MapPath(Request.ApplicationPath)+@"\MyExcel.xls";
38 sTemplate=Server.MapPath(Request.ApplicationPath) +@"\MyTemplate.xls";
39 oExcel.DisplayAlerts=false;
40 oBooks=oExcel.Workbooks;
41
42 try
43 {
44 // 在VB.net中一般用open
45 oBooks.Add(Server.MapPath(Request.ApplicationPath) + @"\MyTemplate.xls") ;
46 }
47 catch
48 {
49 }
50
51 oBook=oBooks[1];
52 oSheets=oBook.Worksheets;
53 oSheet=(Excel.Worksheet) oSheets[1];
54
55 oBook = oBooks[1];
56 oSheet.Name="First Sheet";
57 oCells=oSheet.Cells;
58 DumpData(dt,oCells);
59
60 // 注意此处为10个参数
61 oSheet.SaveAs(sFile,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
62 Excel.XlSaveAsAccessMode.xlExclusive,Missing.Value,Missing.Value,
63 Missing.Value);
64
65 oBook.Close(Missing.Value,Missing.Value,Missing.Value);
66
67 oExcel.Quit();
68 Marshal.ReleaseComObject(oCells);
69 Marshal.ReleaseComObject(oSheet);
70
71 Marshal.ReleaseComObject(oSheets) ;
72 Marshal.ReleaseComObject(oBook);
73 Marshal.ReleaseComObject(oBooks);
74 Marshal.ReleaseComObject(oExcel);
75
76 oExcel = null;
77 oBooks = null;
78 oBook = null;
79
80 oSheets = null;
81 oSheet = null ;
82 oCells = null;
83 System.GC.Collect();
84 Response.Redirect(sFile);
85
86 }
87
88 private void DumpData(System.Data.DataTable dt,Excel.Range oCells)
89 {
90 int iCol;
91 int iRow;
92 DataRow dr;
93 object[] ary;
94 for (iCol=0;iCol<dt.Columns.Count;iCol++)
95 {
96 oCells[2,iCol+1]=dt.Columns[iCol].ToString();
97 }
98
99 for (iRow=0 ;iRow<dt.Rows.Count;iRow++)
100 {
101 dr=dt.Rows[iRow];
102 ary=dr.ItemArray;
103 for(iCol=0 ;iCol<ary.GetUpperBound(0);iCol++)
104 {
105 oCells[iRow+3,iCol+1]=ary[iCol].ToString();
106 Response.Write(ary[iCol].ToString()+"\t");
107 }
108 }
109 }
110
111 public System.Data.DataSet CreateDataSource()
112 {
113 System.Data.DataSet ds = new System.Data.DataSet();
114 System.Data.DataTable dt = new System.Data.DataTable();
115 System.Data.DataRow dr;
116 dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));
117 dt.Columns.Add(new DataColumn("图书单价", typeof(float)));
118 dt.Columns.Add(new DataColumn("购买数量", typeof(Int32)));
119 dt.Columns.Add(new DataColumn("总价格", typeof(float)));
120 for (Int32 i = 0; i <= 10; i++)
121 {
122 dr = dt.NewRow();
123 dr[0] = "123456789123456789";
124 dr[1] = 100 * i / 3;
125 dr[2] = i + 5;
126 dr[3] = (float)dr[1] * (Int32) dr[2];
127 dt.Rows.Add(dr);
128 }
129 ds.Tables.Add(dt);
130 return ds;
131 }
132
133
134 Web 窗体设计器生成的代码
153 }
154}
155