datatable 導入到Excel_跪求 在數據庫中撈一批資料,導入到Excel中,現在要求Excel某欄位中的資料換行,請問這換行怎麼實現?謝謝!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 1 class ExportToExcel 2 { 3 4 public void SaveExcel(DataTable dt, string Filter, string FileName, string SheetName) 5 { 6 7 if (FileName == "") 8 { 9 SaveFileDialog a = new SaveFileDialog(); 10 a.Filter = "Excel 工作簿 (*.xls)|*.xls"; 11 if (a.ShowDialog() == DialogResult.OK) 12 { 13 FileName = a.FileName; 14 } 15 else 16 { 17 return; 18 } 19 } 20 21 try 22 { 23 System.IO.File.Delete(FileName); 24 } 25 catch (Exception) 26 { 27 MessageBox.Show("该文件已经存在,删除文件时出错!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); 28 return; 29 } 30 31 string ConnStr; 32 ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + FileName + "\";Extended Properties=\"Excel 8.0;HDR=YES\""; 33 34 OleDbConnection conn_excel = new OleDbConnection(); 35 conn_excel.ConnectionString = ConnStr; 36 37 OleDbCommand cmd_excel = new OleDbCommand(); 38 39 string sql; 40 sql = SqlCreate(dt, SheetName); 41 42 conn_excel.Open(); 43 cmd_excel.Connection = conn_excel; 44 cmd_excel.CommandText = sql; 45 cmd_excel.ExecuteNonQuery(); 46 47 conn_excel.Close(); 48 49 OleDbDataAdapter da_excel = new OleDbDataAdapter("Select * From [" + SheetName + "$]", conn_excel); 50 DataTable dt_excel = new DataTable(); 51 da_excel.Fill(dt_excel); 52 53 da_excel.InsertCommand = SqlInsert(SheetName, dt, conn_excel); 54 55 DataRow dr_excel; 56 string ColumnName; 57 58 foreach (DataRow dr in dt.Select(Filter)) 59 { 60 dr_excel = dt_excel.NewRow(); 61 62 foreach (DataColumn dc in dt.Columns) 63 { 64 ColumnName = dc.ColumnName; 65 dr_excel[ColumnName] = dr[ColumnName]; 66 67 } 68 dt_excel.Rows.Add(dr_excel); 69 70 } 71 72 da_excel.Update(dt_excel); 73 conn_excel.Close(); 74 75 if (MessageBox.Show("数据成功导出到『" + FileName + "』,是否现在打开?", "导出", 76 MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) 77 { 78 System.Diagnostics.Process.Start(FileName); 79 } 80 } 81 82 private void CheckColumn(DataTable dt, DataTable dt_v) 83 { 84 foreach (DataRow dr in dt_v.Select()) 85 { 86 if (!dt.Columns.Contains(dr["列名"].ToString())) 87 { 88 dr.Delete(); 89 } 90 } 91 dt_v.AcceptChanges(); 92 } 93 94 private string GetDataType(Type i) 95 { 96 string s; 97 98 switch (i.Name) 99 {100 case "String":101 s = "Char";102 break;103 case "Int32":104 s = "Int";105 break;106 case "Int64":107 s = "Int";108 break;109 case "Int16":110 s = "Int";111 break;112 case "Double":113 s = "Double";114 break;115 case "Decimal":116 s = "Double";117 break;118 default:119 s = "Char";120 break;121122 }123 return s;124 }125126 private OleDbType StringToOleDbType(Type i)127 {128 OleDbType s;129130 switch (i.Name)131 {132 case "String":133 s = OleDbType.Char;134 break;135 case "Int32":136 s = OleDbType.Integer;137 break;138 case "Int64":139 s = OleDbType.Integer;140 break;141 case "Int16":142 s = OleDbType.Integer;143 break;144 case "Double":145 s = OleDbType.Double;146 break;147 case "Decimal":148 s = OleDbType.Decimal;149 break;150 default:151 s = OleDbType.Char;152 break;153154 }155 return s;156157 }158159160 private string SqlCreate(DataTable dt, string SheetName)161 {162 string sql;163164 sql = "CREATE TABLE " + SheetName + " (";165166 foreach (DataColumn dc in dt.Columns)167 {168 sql += "[" + dc.ColumnName + "] " + GetDataType(dc.DataType) + " ,";169 }170 171 //sql = "CREATE TABLE [" + SheetName + "] (";172173 //foreach (C1.Win.C1TrueDBGrid.C1DataColumn dc in grid.Columns)174 //{175 // sql += "[" + dc.Caption + "] " + GetDataType(dc.DataType) + ",";176 //}177 //sql = sql.Substring(0, sql.Length - 1);178 //sql += ")";179 180 sql = sql.Substring(0, sql.Length - 1);181 sql += ")";182183 return sql;184 }185186187 // 生成 InsertCommand 并设置参数188 private OleDbCommand SqlInsert(string SheetName, DataTable dt, OleDbConnection conn_excel)189 {190 OleDbCommand i;191 string sql;192193 sql = "INSERT INTO [" + SheetName + "$] (";194 foreach (DataColumn dc in dt.Columns)195 {196 sql += "[" + dc.ColumnName + "] ";197 sql += ",";198 }199 sql = sql.Substring(0, sql.Length - 1);200 sql += ") VALUES (";201 foreach (DataColumn dc in dt.Columns)202 {203 sql += "?,";204 }205 sql = sql.Substring(0, sql.Length - 1);206 sql += ")";207208 i = new OleDbCommand(sql, conn_excel);209210 foreach (DataColumn dc in dt.Columns)211 {212 i.Parameters.Add("@" + dc.Caption, StringToOleDbType(dc.DataType), 0, dc.Caption);213 }214215 return i;216 }217 218 } 换行?Excel没有换行吧,只有设置Excel单元格的宽度 如果是那种换行 Microsoft.Office.Interop.Excel 里面 Range 有个WrapText 属性 lz设置一下看看range 对象 用 sheet 的 get range 获得 string strText = String.Empty;strText = strText + "\r\n" + "123" 先根据查询条件,然后在调用方法 public static void OutExcel(System.Web.UI.Control gv, System.Web.UI.Page myPage, string FileName) { myPage.Response.Clear(); myPage.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">"); myPage.Response.AddHeader("content-disposition", "attachment;filename=" + FileName + ".xls"); myPage.Response.Charset = "GB2312"; myPage.Response.ContentType = "application/vnd.xls"; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); gv.RenderControl(htmlWrite); myPage.Response.Write(stringWrite.ToString()); myPage.Response.End(); } 我比较懒,给楼主一个建议,直接生成csv文件吧,反正excel也能读。 .NET导出Excel汇总 换行<br style='mso-data-placement:same-cell;'/>参见http://dotnet.aspx.cc/article/7ca98e44-fe9f-4f96-b115-e1998c0bf080/read.aspxhttp://dotnet.aspx.cc/article/e9e795ab-8fb5-47e8-a586-2c943a8e5408/read.aspx http://download.csdn.net/source/2647486简单易懂应该可以解决你的问题 博客功能 母板页CustomValidator验证出错的问题 帮忙看一下怎么不掉 js呀 关于radiobuttonlist的两个问题:滚动条和显示问题 请教:奇怪的OCI-22053问题 XP中如何启用Everyone的文件夹权限? 怎么用一个事务控制不同数据库中的表操作? Page.FindControl的问题? CheckBox的Checked字段应该怎样帮定? 虚拟目录....................... sqlserver 使用with nolock无效 还是出现进程* 发生死锁 熟悉打印HTML的高手帮忙
2 {
3
4 public void SaveExcel(DataTable dt, string Filter, string FileName, string SheetName)
5 {
6
7 if (FileName == "")
8 {
9 SaveFileDialog a = new SaveFileDialog();
10 a.Filter = "Excel 工作簿 (*.xls)|*.xls";
11 if (a.ShowDialog() == DialogResult.OK)
12 {
13 FileName = a.FileName;
14 }
15 else
16 {
17 return;
18 }
19 }
20
21 try
22 {
23 System.IO.File.Delete(FileName);
24 }
25 catch (Exception)
26 {
27 MessageBox.Show("该文件已经存在,删除文件时出错!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
28 return;
29 }
30
31 string ConnStr;
32 ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + FileName + "\";Extended Properties=\"Excel 8.0;HDR=YES\"";
33
34 OleDbConnection conn_excel = new OleDbConnection();
35 conn_excel.ConnectionString = ConnStr;
36
37 OleDbCommand cmd_excel = new OleDbCommand();
38
39 string sql;
40 sql = SqlCreate(dt, SheetName);
41
42 conn_excel.Open();
43 cmd_excel.Connection = conn_excel;
44 cmd_excel.CommandText = sql;
45 cmd_excel.ExecuteNonQuery();
46
47 conn_excel.Close();
48
49 OleDbDataAdapter da_excel = new OleDbDataAdapter("Select * From [" + SheetName + "$]", conn_excel);
50 DataTable dt_excel = new DataTable();
51 da_excel.Fill(dt_excel);
52
53 da_excel.InsertCommand = SqlInsert(SheetName, dt, conn_excel);
54
55 DataRow dr_excel;
56 string ColumnName;
57
58 foreach (DataRow dr in dt.Select(Filter))
59 {
60 dr_excel = dt_excel.NewRow();
61
62 foreach (DataColumn dc in dt.Columns)
63 {
64 ColumnName = dc.ColumnName;
65 dr_excel[ColumnName] = dr[ColumnName];
66
67 }
68 dt_excel.Rows.Add(dr_excel);
69
70 }
71
72 da_excel.Update(dt_excel);
73 conn_excel.Close();
74
75 if (MessageBox.Show("数据成功导出到『" + FileName + "』,是否现在打开?", "导出",
76 MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
77 {
78 System.Diagnostics.Process.Start(FileName);
79 }
80 }
81
82 private void CheckColumn(DataTable dt, DataTable dt_v)
83 {
84 foreach (DataRow dr in dt_v.Select())
85 {
86 if (!dt.Columns.Contains(dr["列名"].ToString()))
87 {
88 dr.Delete();
89 }
90 }
91 dt_v.AcceptChanges();
92 }
93
94 private string GetDataType(Type i)
95 {
96 string s;
97
98 switch (i.Name)
99 {
100 case "String":
101 s = "Char";
102 break;
103 case "Int32":
104 s = "Int";
105 break;
106 case "Int64":
107 s = "Int";
108 break;
109 case "Int16":
110 s = "Int";
111 break;
112 case "Double":
113 s = "Double";
114 break;
115 case "Decimal":
116 s = "Double";
117 break;
118 default:
119 s = "Char";
120 break;
121
122 }
123 return s;
124 }
125
126 private OleDbType StringToOleDbType(Type i)
127 {
128 OleDbType s;
129
130 switch (i.Name)
131 {
132 case "String":
133 s = OleDbType.Char;
134 break;
135 case "Int32":
136 s = OleDbType.Integer;
137 break;
138 case "Int64":
139 s = OleDbType.Integer;
140 break;
141 case "Int16":
142 s = OleDbType.Integer;
143 break;
144 case "Double":
145 s = OleDbType.Double;
146 break;
147 case "Decimal":
148 s = OleDbType.Decimal;
149 break;
150 default:
151 s = OleDbType.Char;
152 break;
153
154 }
155 return s;
156
157 }
158
159
160 private string SqlCreate(DataTable dt, string SheetName)
161 {
162 string sql;
163
164 sql = "CREATE TABLE " + SheetName + " (";
165
166 foreach (DataColumn dc in dt.Columns)
167 {
168 sql += "[" + dc.ColumnName + "] " + GetDataType(dc.DataType) + " ,";
169 }
170
171 //sql = "CREATE TABLE [" + SheetName + "] (";
172
173 //foreach (C1.Win.C1TrueDBGrid.C1DataColumn dc in grid.Columns)
174 //{
175 // sql += "[" + dc.Caption + "] " + GetDataType(dc.DataType) + ",";
176 //}
177 //sql = sql.Substring(0, sql.Length - 1);
178 //sql += ")";
179
180 sql = sql.Substring(0, sql.Length - 1);
181 sql += ")";
182
183 return sql;
184 }
185
186
187 // 生成 InsertCommand 并设置参数
188 private OleDbCommand SqlInsert(string SheetName, DataTable dt, OleDbConnection conn_excel)
189 {
190 OleDbCommand i;
191 string sql;
192
193 sql = "INSERT INTO [" + SheetName + "$] (";
194 foreach (DataColumn dc in dt.Columns)
195 {
196 sql += "[" + dc.ColumnName + "] ";
197 sql += ",";
198 }
199 sql = sql.Substring(0, sql.Length - 1);
200 sql += ") VALUES (";
201 foreach (DataColumn dc in dt.Columns)
202 {
203 sql += "?,";
204 }
205 sql = sql.Substring(0, sql.Length - 1);
206 sql += ")";
207
208 i = new OleDbCommand(sql, conn_excel);
209
210 foreach (DataColumn dc in dt.Columns)
211 {
212 i.Parameters.Add("@" + dc.Caption, StringToOleDbType(dc.DataType), 0, dc.Caption);
213 }
214
215 return i;
216 }
217
218 }
里面 Range 有个WrapText 属性 lz设置一下看看range 对象 用 sheet 的 get range 获得
strText = strText + "\r\n" + "123"
public static void OutExcel(System.Web.UI.Control gv, System.Web.UI.Page myPage, string FileName)
{
myPage.Response.Clear();
myPage.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
myPage.Response.AddHeader("content-disposition", "attachment;filename=" + FileName + ".xls");
myPage.Response.Charset = "GB2312";
myPage.Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
gv.RenderControl(htmlWrite);
myPage.Response.Write(stringWrite.ToString());
myPage.Response.End();
}
<br style='mso-data-placement:same-cell;'/>
参见
http://dotnet.aspx.cc/article/7ca98e44-fe9f-4f96-b115-e1998c0bf080/read.aspx
http://dotnet.aspx.cc/article/e9e795ab-8fb5-47e8-a586-2c943a8e5408/read.aspx
简单易懂应该可以解决你的问题