ExcelHelper.cs 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558
  1. using System;
  2. using System.IO;
  3. using System.Data;
  4. using System.Collections;
  5. using System.Data.OleDb;
  6. using System.Web;
  7. using System.Web.UI;
  8. using System.Web.UI.WebControls;
  9. namespace Ant.Service.Utilities
  10. {
  11. /// <summary>
  12. /// Excel操作类
  13. /// </summary>
  14. /// Microsoft Excel 11.0 Object Library
  15. public class ExcelHelper
  16. {
  17. #region 数据导出至Excel文件
  18. /// </summary>
  19. /// 导出Excel文件,自动返回可下载的文件流
  20. /// </summary>
  21. public static void DataTable1Excel(System.Data.DataTable dtData)
  22. {
  23. GridView gvExport = null;
  24. HttpContext curContext = HttpContext.Current;
  25. StringWriter strWriter = null;
  26. HtmlTextWriter htmlWriter = null;
  27. if (dtData != null)
  28. {
  29. curContext.Response.ContentType = "application/vnd.ms-excel";
  30. curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
  31. curContext.Response.Charset = "utf-8";
  32. strWriter = new StringWriter();
  33. htmlWriter = new HtmlTextWriter(strWriter);
  34. gvExport = new GridView();
  35. gvExport.DataSource = dtData.DefaultView;
  36. gvExport.AllowPaging = false;
  37. gvExport.DataBind();
  38. gvExport.RenderControl(htmlWriter);
  39. curContext.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=gb2312\"/>" + strWriter.ToString());
  40. curContext.Response.End();
  41. }
  42. }
  43. /// <summary>
  44. /// 导出Excel文件,转换为可读模式
  45. /// </summary>
  46. public static void DataTable2Excel(System.Data.DataTable dtData)
  47. {
  48. DataGrid dgExport = null;
  49. HttpContext curContext = HttpContext.Current;
  50. StringWriter strWriter = null;
  51. HtmlTextWriter htmlWriter = null;
  52. if (dtData != null)
  53. {
  54. curContext.Response.ContentType = "application/vnd.ms-excel";
  55. curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
  56. curContext.Response.Charset = "";
  57. strWriter = new StringWriter();
  58. htmlWriter = new HtmlTextWriter(strWriter);
  59. dgExport = new DataGrid();
  60. dgExport.DataSource = dtData.DefaultView;
  61. dgExport.AllowPaging = false;
  62. dgExport.DataBind();
  63. dgExport.RenderControl(htmlWriter);
  64. curContext.Response.Write(strWriter.ToString());
  65. curContext.Response.End();
  66. }
  67. }
  68. /// <summary>
  69. /// 导出Excel文件,并自定义文件名
  70. /// </summary>
  71. public static void DataTable3Excel(System.Data.DataTable dtData, String FileName)
  72. {
  73. GridView dgExport = null;
  74. HttpContext curContext = HttpContext.Current;
  75. StringWriter strWriter = null;
  76. HtmlTextWriter htmlWriter = null;
  77. if (dtData != null)
  78. {
  79. HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
  80. curContext.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
  81. curContext.Response.ContentType = "application nd.ms-excel";
  82. curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
  83. curContext.Response.Charset = "GB2312";
  84. strWriter = new StringWriter();
  85. htmlWriter = new HtmlTextWriter(strWriter);
  86. dgExport = new GridView();
  87. dgExport.DataSource = dtData.DefaultView;
  88. dgExport.AllowPaging = false;
  89. dgExport.DataBind();
  90. dgExport.RenderControl(htmlWriter);
  91. curContext.Response.Write(strWriter.ToString());
  92. curContext.Response.End();
  93. }
  94. }
  95. /// <summary>
  96. /// 将数据导出至Excel文件
  97. /// </summary>
  98. /// <param name="Table">DataTable对象</param>
  99. /// <param name="ExcelFilePath">Excel文件路径</param>
  100. public static bool OutputToExcel(DataTable Table, string ExcelFilePath)
  101. {
  102. if (File.Exists(ExcelFilePath))
  103. {
  104. throw new Exception("该文件已经存在!");
  105. }
  106. if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
  107. {
  108. Table.TableName = "Sheet1";
  109. }
  110. //数据表的列数
  111. int ColCount = Table.Columns.Count;
  112. //用于记数,实例化参数时的序号
  113. int i = 0;
  114. //创建参数
  115. OleDbParameter[] para = new OleDbParameter[ColCount];
  116. //创建表结构的SQL语句
  117. string TableStructStr = @"Create Table " + Table.TableName + "(";
  118. //连接字符串
  119. string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
  120. OleDbConnection objConn = new OleDbConnection(connString);
  121. //创建表结构
  122. OleDbCommand objCmd = new OleDbCommand();
  123. //数据类型集合
  124. ArrayList DataTypeList = new ArrayList();
  125. DataTypeList.Add("System.Decimal");
  126. DataTypeList.Add("System.Double");
  127. DataTypeList.Add("System.Int16");
  128. DataTypeList.Add("System.Int32");
  129. DataTypeList.Add("System.Int64");
  130. DataTypeList.Add("System.Single");
  131. //遍历数据表的所有列,用于创建表结构
  132. foreach (DataColumn col in Table.Columns)
  133. {
  134. //如果列属于数字列,则设置该列的数据类型为double
  135. if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
  136. {
  137. para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
  138. objCmd.Parameters.Add(para[i]);
  139. //如果是最后一列
  140. if (i + 1 == ColCount)
  141. {
  142. TableStructStr += col.ColumnName + " double)";
  143. }
  144. else
  145. {
  146. TableStructStr += col.ColumnName + " double,";
  147. }
  148. }
  149. else
  150. {
  151. para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
  152. objCmd.Parameters.Add(para[i]);
  153. //如果是最后一列
  154. if (i + 1 == ColCount)
  155. {
  156. TableStructStr += col.ColumnName + " varchar)";
  157. }
  158. else
  159. {
  160. TableStructStr += col.ColumnName + " varchar,";
  161. }
  162. }
  163. i++;
  164. }
  165. //创建Excel文件及文件结构
  166. try
  167. {
  168. objCmd.Connection = objConn;
  169. objCmd.CommandText = TableStructStr;
  170. if (objConn.State == ConnectionState.Closed)
  171. {
  172. objConn.Open();
  173. }
  174. objCmd.ExecuteNonQuery();
  175. }
  176. catch (Exception exp)
  177. {
  178. throw exp;
  179. }
  180. //插入记录的SQL语句
  181. string InsertSql_1 = "Insert into " + Table.TableName + " (";
  182. string InsertSql_2 = " Values (";
  183. string InsertSql = "";
  184. //遍历所有列,用于插入记录,在此创建插入记录的SQL语句
  185. for (int colID = 0; colID < ColCount; colID++)
  186. {
  187. if (colID + 1 == ColCount) //最后一列
  188. {
  189. InsertSql_1 += Table.Columns[colID].ColumnName + ")";
  190. InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
  191. }
  192. else
  193. {
  194. InsertSql_1 += Table.Columns[colID].ColumnName + ",";
  195. InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
  196. }
  197. }
  198. InsertSql = InsertSql_1 + InsertSql_2;
  199. //遍历数据表的所有数据行
  200. for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
  201. {
  202. for (int colID = 0; colID < ColCount; colID++)
  203. {
  204. if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
  205. {
  206. para[colID].Value = 0;
  207. }
  208. else
  209. {
  210. para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
  211. }
  212. }
  213. try
  214. {
  215. objCmd.CommandText = InsertSql;
  216. objCmd.ExecuteNonQuery();
  217. }
  218. catch (Exception exp)
  219. {
  220. string str = exp.Message;
  221. }
  222. }
  223. try
  224. {
  225. if (objConn.State == ConnectionState.Open)
  226. {
  227. objConn.Close();
  228. }
  229. }
  230. catch (Exception exp)
  231. {
  232. throw exp;
  233. }
  234. return true;
  235. }
  236. /// <summary>
  237. /// 将数据导出至Excel文件
  238. /// </summary>
  239. /// <param name="Table">DataTable对象</param>
  240. /// <param name="Columns">要导出的数据列集合</param>
  241. /// <param name="ExcelFilePath">Excel文件路径</param>
  242. public static bool OutputToExcel(DataTable Table, ArrayList Columns, string ExcelFilePath)
  243. {
  244. if (File.Exists(ExcelFilePath))
  245. {
  246. throw new Exception("该文件已经存在!");
  247. }
  248. //如果数据列数大于表的列数,取数据表的所有列
  249. if (Columns.Count > Table.Columns.Count)
  250. {
  251. for (int s = Table.Columns.Count + 1; s <= Columns.Count; s++)
  252. {
  253. Columns.RemoveAt(s); //移除数据表列数后的所有列
  254. }
  255. }
  256. //遍历所有的数据列,如果有数据列的数据类型不是 DataColumn,则将它移除
  257. DataColumn column = new DataColumn();
  258. for (int j = 0; j < Columns.Count; j++)
  259. {
  260. try
  261. {
  262. column = (DataColumn)Columns[j];
  263. }
  264. catch (Exception)
  265. {
  266. Columns.RemoveAt(j);
  267. }
  268. }
  269. if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
  270. {
  271. Table.TableName = "Sheet1";
  272. }
  273. //数据表的列数
  274. int ColCount = Columns.Count;
  275. //创建参数
  276. OleDbParameter[] para = new OleDbParameter[ColCount];
  277. //创建表结构的SQL语句
  278. string TableStructStr = @"Create Table " + Table.TableName + "(";
  279. //连接字符串
  280. string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
  281. OleDbConnection objConn = new OleDbConnection(connString);
  282. //创建表结构
  283. OleDbCommand objCmd = new OleDbCommand();
  284. //数据类型集合
  285. ArrayList DataTypeList = new ArrayList();
  286. DataTypeList.Add("System.Decimal");
  287. DataTypeList.Add("System.Double");
  288. DataTypeList.Add("System.Int16");
  289. DataTypeList.Add("System.Int32");
  290. DataTypeList.Add("System.Int64");
  291. DataTypeList.Add("System.Single");
  292. DataColumn col = new DataColumn();
  293. //遍历数据表的所有列,用于创建表结构
  294. for (int k = 0; k < ColCount; k++)
  295. {
  296. col = (DataColumn)Columns[k];
  297. //列的数据类型是数字型
  298. if (DataTypeList.IndexOf(col.DataType.ToString().Trim()) >= 0)
  299. {
  300. para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double);
  301. objCmd.Parameters.Add(para[k]);
  302. //如果是最后一列
  303. if (k + 1 == ColCount)
  304. {
  305. TableStructStr += col.Caption.Trim() + " Double)";
  306. }
  307. else
  308. {
  309. TableStructStr += col.Caption.Trim() + " Double,";
  310. }
  311. }
  312. else
  313. {
  314. para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar);
  315. objCmd.Parameters.Add(para[k]);
  316. //如果是最后一列
  317. if (k + 1 == ColCount)
  318. {
  319. TableStructStr += col.Caption.Trim() + " VarChar)";
  320. }
  321. else
  322. {
  323. TableStructStr += col.Caption.Trim() + " VarChar,";
  324. }
  325. }
  326. }
  327. //创建Excel文件及文件结构
  328. try
  329. {
  330. objCmd.Connection = objConn;
  331. objCmd.CommandText = TableStructStr;
  332. if (objConn.State == ConnectionState.Closed)
  333. {
  334. objConn.Open();
  335. }
  336. objCmd.ExecuteNonQuery();
  337. }
  338. catch (Exception exp)
  339. {
  340. throw exp;
  341. }
  342. //插入记录的SQL语句
  343. string InsertSql_1 = "Insert into " + Table.TableName + " (";
  344. string InsertSql_2 = " Values (";
  345. string InsertSql = "";
  346. //遍历所有列,用于插入记录,在此创建插入记录的SQL语句
  347. for (int colID = 0; colID < ColCount; colID++)
  348. {
  349. if (colID + 1 == ColCount) //最后一列
  350. {
  351. InsertSql_1 += Columns[colID].ToString().Trim() + ")";
  352. InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ")";
  353. }
  354. else
  355. {
  356. InsertSql_1 += Columns[colID].ToString().Trim() + ",";
  357. InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ",";
  358. }
  359. }
  360. InsertSql = InsertSql_1 + InsertSql_2;
  361. //遍历数据表的所有数据行
  362. DataColumn DataCol = new DataColumn();
  363. for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
  364. {
  365. for (int colID = 0; colID < ColCount; colID++)
  366. {
  367. //因为列不连续,所以在取得单元格时不能用行列编号,列需得用列的名称
  368. DataCol = (DataColumn)Columns[colID];
  369. if (para[colID].DbType == DbType.Double && Table.Rows[rowID][DataCol.Caption].ToString().Trim() == "")
  370. {
  371. para[colID].Value = 0;
  372. }
  373. else
  374. {
  375. para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim();
  376. }
  377. }
  378. try
  379. {
  380. objCmd.CommandText = InsertSql;
  381. objCmd.ExecuteNonQuery();
  382. }
  383. catch (Exception exp)
  384. {
  385. string str = exp.Message;
  386. }
  387. }
  388. try
  389. {
  390. if (objConn.State == ConnectionState.Open)
  391. {
  392. objConn.Close();
  393. }
  394. }
  395. catch (Exception exp)
  396. {
  397. throw exp;
  398. }
  399. return true;
  400. }
  401. #endregion
  402. /// <summary>
  403. /// 获取Excel文件数据表列表
  404. /// </summary>
  405. public static ArrayList GetExcelTables(string ExcelFileName)
  406. {
  407. DataTable dt = new DataTable();
  408. ArrayList TablesList = new ArrayList();
  409. if (File.Exists(ExcelFileName))
  410. {
  411. using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
  412. {
  413. try
  414. {
  415. conn.Open();
  416. dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
  417. }
  418. catch (Exception exp)
  419. {
  420. throw exp;
  421. }
  422. //获取数据表个数
  423. int tablecount = dt.Rows.Count;
  424. for (int i = 0; i < tablecount; i++)
  425. {
  426. string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
  427. if (TablesList.IndexOf(tablename) < 0)
  428. {
  429. TablesList.Add(tablename);
  430. }
  431. }
  432. }
  433. }
  434. return TablesList;
  435. }
  436. /// <summary>
  437. /// 将Excel文件导出至DataTable(第一行作为表头)
  438. /// </summary>
  439. /// <param name="ExcelFilePath">Excel文件路径</param>
  440. /// <param name="TableName">数据表名,如果数据表名错误,默认为第一个数据表名</param>
  441. public static DataTable InputFromExcel(string ExcelFilePath, string TableName)
  442. {
  443. if (!File.Exists(ExcelFilePath))
  444. {
  445. throw new Exception("Excel文件不存在!");
  446. }
  447. //如果数据表名不存在,则数据表名为Excel文件的第一个数据表
  448. ArrayList TableList = new ArrayList();
  449. TableList = GetExcelTables(ExcelFilePath);
  450. if (TableName.IndexOf(TableName) < 0)
  451. {
  452. TableName = TableList[0].ToString().Trim();
  453. }
  454. DataTable table = new DataTable();
  455. OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");
  456. OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);
  457. OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
  458. try
  459. {
  460. if (dbcon.State == ConnectionState.Closed)
  461. {
  462. dbcon.Open();
  463. }
  464. adapter.Fill(table);
  465. }
  466. catch (Exception exp)
  467. {
  468. throw exp;
  469. }
  470. finally
  471. {
  472. if (dbcon.State == ConnectionState.Open)
  473. {
  474. dbcon.Close();
  475. }
  476. }
  477. return table;
  478. }
  479. /// <summary>
  480. /// 获取Excel文件指定数据表的数据列表
  481. /// </summary>
  482. /// <param name="ExcelFileName">Excel文件名</param>
  483. /// <param name="TableName">数据表名</param>
  484. public static ArrayList GetExcelTableColumns(string ExcelFileName, string TableName)
  485. {
  486. DataTable dt = new DataTable();
  487. ArrayList ColsList = new ArrayList();
  488. if (File.Exists(ExcelFileName))
  489. {
  490. using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
  491. {
  492. conn.Open();
  493. dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
  494. //获取列个数
  495. int colcount = dt.Rows.Count;
  496. for (int i = 0; i < colcount; i++)
  497. {
  498. string colname = dt.Rows[i]["Column_Name"].ToString().Trim();
  499. ColsList.Add(colname);
  500. }
  501. }
  502. }
  503. return ColsList;
  504. }
  505. }
  506. }