数据导出到EXCEL 我的程序要求从数据库中查询到的数据直接导入到EXCEL中去,并且把这个EXCEL是在客户端,数据不要在页面显示出来,都在后台实现!!!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 把excel当数据库去立连,然后数据操作http://www.connectionstrings.com http://xuzq.cnblogs.com/archive/2005/10/11/ExportToExcel.html http://dotnet.aspx.cc/ShowDetail.aspx?id=EC5E84EC-68F9-4CD7-9E11-6F5C92027F0B 好象这些都不是我想要的,这些会了,只是,不要显示在页面上,这些什么table.datagrid都是要显示在页面上的 http://community.csdn.net/Expert/topic/4744/4744431.xml?temp=.7390253 这个的话,把读取出来的数据用DATATABLE保存,然后遍历一下写到excel中.具体代码看我刚回答的帖子http://community.csdn.net/Expert/TopicView.asp?id=4744903 1using System; 2using System.IO; 3using System.Data; 4using System.Reflection; 5using System.Diagnostics; 6using cfg = System.Configuration; 7//using Excel; 8 9namespace Excelgo 10{ 11 /**//// <summary> 12 /// 模板输出Excel,并对数据进行分页 13 /// 作 者:cbcd 14 /// 创建日期:2006-5-1 15 /// </summary> 16 public class Excelgo 17 { 18 protected string templetFile = null; 19 protected string outputFile = null; 20 protected object missing = Missing.Value; 21 22 /**//// <summary> 23 /// 构造函数,需指定模板文件和输出文件完整路径 24 /// </summary> 25 /// <param name="templetFilePath">Excel模板文件路径</param> 26 /// <param name="outputFilePath">输出Excel文件路径</param> 27 public ExcelHelper(string templetFilePath,string outputFilePath) 28 { 29 if(templetFilePath == null) 30 throw new Exception("Excel模板文件路径不能为空!"); 31 32 if(outputFilePath == null) 33 throw new Exception("输出Excel文件路径不能为空!"); 34 35 if(!File.Exists(templetFilePath)) 36 throw new Exception("指定路径的Excel模板文件不存在!"); 37 38 this.templetFile = templetFilePath; 39 this.outputFile = outputFilePath; 40 41 } 42 43 /**//// <summary> 44 /// 将DataTable数据写入Excel文件(套用模板并分页) 45 /// </summary> 46 /// <param name="dt">DataTable</param> 47 /// <param name="rows">每个WorkSheet写入多少行数据</param> 48 /// <param name="top">行索引</param> 49 /// <param name="left">列索引</param> 50 /// <param name="sheetPrefixName">WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2”</param> 51 public void DataTableToExcel(DataTable dt,int rows,int top,int left,string sheetPrefixName) 52 { 53 int rowCount = dt.Rows.Count; //源DataTable行数 54 int colCount = dt.Columns.Count; //源DataTable列数 55 int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数 56 DateTime beforeTime; 57 DateTime afterTime; 58 59 if(sheetPrefixName == null || sheetPrefixName.Trim() == "") 60 sheetPrefixName = "Sheet"; 61 62 //创建一个Application对象并使其可见 63 beforeTime = DateTime.Now; 64 Excel.Application app = new Excel.ApplicationClass(); 65 app.Visible = true; 66 afterTime = DateTime.Now; 67 68 //打开模板文件,得到WorkBook对象 69 Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing, 70 missing,missing,missing,missing,missing,missing,missing); 71 72 //得到WorkSheet对象 73 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); 74 75 //复制sheetCount-1个WorkSheet对象 76 for(int i=1;i<sheetCount;i++) 77 { 78 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]); 79 } 80 81 将源DataTable数据写入Excel#region 将源DataTable数据写入Excel 82 for(int i=1;i<=sheetCount;i++) 83 { 84 int startRow = (i - 1) * rows; //记录起始行索引 85 int endRow = i * rows; //记录结束行索引 86 87 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 88 if(i == sheetCount) 89 endRow = rowCount; 90 91 //获取要写入数据的WorkSheet对象,并重命名 92 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); 93 sheet.Name = sheetPrefixName + "-" + i.ToString(); 94 95 //将dt中的数据写入WorkSheet 96 for(int j=0;j<endRow-startRow;j++) 97 { 98 for(int k=0;k<colCount;k++) 99 {100 sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();101 }102 }103104 //写文本框数据105 Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");106 Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");107 Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");108109 txtAuthor.Text = "KLY.NET的Blog";110 txtDate.Text = DateTime.Now.ToShortDateString();111 txtVersion.Text = "1.0.0.0";112 }113 #endregion114115 //输出Excel文件并退出116 try117 {118 workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);119 workBook.Close(null,null,null);120 app.Workbooks.Close();121 app.Application.Quit();122 app.Quit();123124 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);125 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);126 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);127128 workSheet=null;129 workBook=null;130 app=null;131132 GC.Collect();133 }134 catch(Exception e)135 {136 throw e;137 }138 finally139 {140 Process[] myProcesses;141 DateTime startTime;142 myProcesses = Process.GetProcessesByName("Excel");143144 //得不到Excel进程ID,暂时只能判断进程启动时间145 foreach(Process myProcess in myProcesses)146 {147 startTime = myProcess.StartTime;148149 if(startTime > beforeTime && startTime < afterTime)150 {151 myProcess.Kill();152 }153 }154 }155 156 }157158 159 /**//// <summary>160 /// 获取WorkSheet数量161 /// </summary>162 /// <param name="rowCount">记录总行数</param>163 /// <param name="rows">每WorkSheet行数</param>164 private int GetSheetCount(int rowCount,int rows)165 {166 int n = rowCount % rows; //余数167168 if(n == 0)169 return rowCount / rows;170 else171 return Convert.ToInt32(rowCount / rows) + 1;172 } sql语句中怎样只要一个字段的前三位数。具体如下 如何防止SQl注入进攻 求大神解救,正则表达式 请问水晶报表能做到这样的效果吗? vs2005中com组建无法加到工具箱里,vs2003正常,这是怎么回事??? 如何读取Sql Server中image类型并保存为本地文件? 请问如何取得一个URL图片并把它下载后生成缩略图? 求助,有没有好用的combobox? 我想了解一下在上海应届毕业生做.net程序员的第一年月薪大约多少? 怎么在*.aspx.cs里设置embed的src值?? 发现一个好东西!自动生成代码,散分! 送分贴
http://www.connectionstrings.com
http://community.csdn.net/Expert/TopicView.asp?id=4744903
2using System.IO;
3using System.Data;
4using System.Reflection;
5using System.Diagnostics;
6using cfg = System.Configuration;
7//using Excel;
8
9namespace Excelgo
10{
11 /**//// <summary>
12 /// 模板输出Excel,并对数据进行分页
13 /// 作 者:cbcd
14 /// 创建日期:2006-5-1
15 /// </summary>
16 public class Excelgo
17 {
18 protected string templetFile = null;
19 protected string outputFile = null;
20 protected object missing = Missing.Value;
21
22 /**//// <summary>
23 /// 构造函数,需指定模板文件和输出文件完整路径
24 /// </summary>
25 /// <param name="templetFilePath">Excel模板文件路径</param>
26 /// <param name="outputFilePath">输出Excel文件路径</param>
27 public ExcelHelper(string templetFilePath,string outputFilePath)
28 {
29 if(templetFilePath == null)
30 throw new Exception("Excel模板文件路径不能为空!");
31
32 if(outputFilePath == null)
33 throw new Exception("输出Excel文件路径不能为空!");
34
35 if(!File.Exists(templetFilePath))
36 throw new Exception("指定路径的Excel模板文件不存在!");
37
38 this.templetFile = templetFilePath;
39 this.outputFile = outputFilePath;
40
41 }
42
43 /**//// <summary>
44 /// 将DataTable数据写入Excel文件(套用模板并分页)
45 /// </summary>
46 /// <param name="dt">DataTable</param>
47 /// <param name="rows">每个WorkSheet写入多少行数据</param>
48 /// <param name="top">行索引</param>
49 /// <param name="left">列索引</param>
50 /// <param name="sheetPrefixName">WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2”</param>
51 public void DataTableToExcel(DataTable dt,int rows,int top,int left,string sheetPrefixName)
52 {
53 int rowCount = dt.Rows.Count; //源DataTable行数
54 int colCount = dt.Columns.Count; //源DataTable列数
55 int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
56 DateTime beforeTime;
57 DateTime afterTime;
58
59 if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
60 sheetPrefixName = "Sheet";
61
62 //创建一个Application对象并使其可见
63 beforeTime = DateTime.Now;
64 Excel.Application app = new Excel.ApplicationClass();
65 app.Visible = true;
66 afterTime = DateTime.Now;
67
68 //打开模板文件,得到WorkBook对象
69 Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
70 missing,missing,missing,missing,missing,missing,missing);
71
72 //得到WorkSheet对象
73 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
74
75 //复制sheetCount-1个WorkSheet对象
76 for(int i=1;i<sheetCount;i++)
77 {
78 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
79 }
80
81 将源DataTable数据写入Excel#region 将源DataTable数据写入Excel
82 for(int i=1;i<=sheetCount;i++)
83 {
84 int startRow = (i - 1) * rows; //记录起始行索引
85 int endRow = i * rows; //记录结束行索引
86
87 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
88 if(i == sheetCount)
89 endRow = rowCount;
90
91 //获取要写入数据的WorkSheet对象,并重命名
92 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
93 sheet.Name = sheetPrefixName + "-" + i.ToString();
94
95 //将dt中的数据写入WorkSheet
96 for(int j=0;j<endRow-startRow;j++)
97 {
98 for(int k=0;k<colCount;k++)
99 {
100 sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
101 }
102 }
103
104 //写文本框数据
105 Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
106 Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
107 Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
108
109 txtAuthor.Text = "KLY.NET的Blog";
110 txtDate.Text = DateTime.Now.ToShortDateString();
111 txtVersion.Text = "1.0.0.0";
112 }
113 #endregion
114
115 //输出Excel文件并退出
116 try
117 {
118 workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
119 workBook.Close(null,null,null);
120 app.Workbooks.Close();
121 app.Application.Quit();
122 app.Quit();
123
124 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
125 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
126 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
127
128 workSheet=null;
129 workBook=null;
130 app=null;
131
132 GC.Collect();
133 }
134 catch(Exception e)
135 {
136 throw e;
137 }
138 finally
139 {
140 Process[] myProcesses;
141 DateTime startTime;
142 myProcesses = Process.GetProcessesByName("Excel");
143
144 //得不到Excel进程ID,暂时只能判断进程启动时间
145 foreach(Process myProcess in myProcesses)
146 {
147 startTime = myProcess.StartTime;
148
149 if(startTime > beforeTime && startTime < afterTime)
150 {
151 myProcess.Kill();
152 }
153 }
154 }
155
156 }
157
158
159 /**//// <summary>
160 /// 获取WorkSheet数量
161 /// </summary>
162 /// <param name="rowCount">记录总行数</param>
163 /// <param name="rows">每WorkSheet行数</param>
164 private int GetSheetCount(int rowCount,int rows)
165 {
166 int n = rowCount % rows; //余数
167
168 if(n == 0)
169 return rowCount / rows;
170 else
171 return Convert.ToInt32(rowCount / rows) + 1;
172 }