将数据库SQL Server中的数据导出到Excel表中（By ADO）——节省时间
数据库的设计：
在文件中存在Excel表Vendors。
在页面层中，有一个文件浏览控件，id=“inputFile”，插入到数据库中时候要用到。beginTime和endTime分别记录开始和结束的时间，CostTime计算时间差。以方便综合考量。
下面是导出到Excel的代码：
代码
1 #region 将database中的数据导出到Excel
2
3 /// <summary>
4 /// create an excel file and write vendor list
5 /// </summary>
6 /// <param name="filePath">export file path</param>
7 /// <returns>sucessfully write or not</returns>
8 public bool WriteExcel(string filePath)
9 {
10 List<VendorInfo> vendorList = GetAllVendors();
11 if (vendorList != null)
12 {
13 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
14 excel.Visible = false;
15 excel.Application.Workbooks.Add(true);
16 Microsoft.Office.Interop.Excel.Sheets excelSheets = excel.Worksheets;
17 excel.Cells[1, 1] = "VendorID";
18 excel.Cells[1, 2] = "VendorName";
19 excel.Cells[1, 3] = "Address";
20 excel.Cells[1, 4] = "Phone";
21 excel.Columns.ColumnWidth = 20;
22 excel.DisplayAlerts = false;
23 excel.AlertBeforeOverwriting = false;
24 excel.ActiveWorkbook.SaveCopyAs(filePath);
25 excel.Workbooks.Close();
26 excel.Quit();
27 excel = null;
28 bool isSucess = false;
29 isSucess = InsertExcelByADO(filePath, vendorList);
30 return isSucess;
31 }
32 else
33 { return false; }
34 }
35
36 /// <summary>
37 /// use OleDb to insert record into excel
38 /// </summary>
39 /// <param name="filePath">file export path</param>
40 /// <param name="vendorList">list of record</param>
41 /// <returns>insert sucessfully or not</returns>
42 private static bool InsertExcelByADO(string filePath, List<VendorInfo> vendorList)
43 {
44 string excelConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filePath + "; Extended Properties=\"Excel 12.0;HDR=YES\"";
45 OleDbConnection conn = new OleDbConnection(excelConn);
46 OleDbCommand cmd = new OleDbCommand();
47 cmd.Connection = conn;
48 try
49 {
50 conn.Open();
51 for (int i = 0; i < vendorList.Count; i++)
52 {
53 StringBuilder sb = new StringBuilder();
54 sb.Append("INSERT INTO [Sheet1$]([VendorID],[VendorName],[Address],[Phone]) VALUES('");
55 sb.Append(vendorList[i].VendorID + "','");
56 sb.Append(vendorList[i].VendorName + "','");
57 sb.Append(vendorList[i].Address + "','");
58 sb.Append(vendorList[i].Phone + "')");
59 cmd.CommandText = sb.ToString();
60 cmd.ExecuteNonQuery();
61 }
62 }
63 catch
64 { return false; }
65 finally
66 { conn.Close(); }
67 return true;
68 }
69
70 /// <summary>
71 /// get all vendors from db
72 /// </summary>
73 /// <returns>VendorInfo list</returns>
74 private static List<VendorInfo> GetAllVendors()
75 {
76 SqlConnection _con = new SqlConnection(@"Data Source=STKWX028\SQLEXPRESS;Initial Catalog=Eva;Integrated Security=True");
77 List<VendorInfo> vendorList = new List<VendorInfo>();
78 string queryStr = @"SELECT vendorID
79 ,vendorName
80 ,vendorAddress
81 ,vendorPhone
82 FROM Suppliers"; //TOP 10
83 SqlCommand cmd = new SqlCommand(queryStr, _con);
84 try
85 {
86 _con.Open();
87 SqlDataReader reader = cmd.ExecuteReader();
88 while (reader.Read())
89 {
90 VendorInfo vendorInfo = new VendorInfo();
91 vendorInfo.VendorID = (int)reader["vendorID"];
92 vendorInfo.VendorName = reader["vendorName"].ToString();
93 vendorInfo.Address = reader["vendorAddress"].ToString();
94 vendorInfo.Phone = reader["vendorPhone"].ToString();
95 vendorList.Add(vendorInfo);
96 }
97 }
98 catch
99 {
100 vendorList = null;
101 return vendorList;
102 }
103 finally { _con.Close(); }
104 return vendorList;
105 }
106
107 #endregion
Excel插入到SQLServer的代码：
代码
1 #region select excel data insert to Database
2
3 /// <summary>
4 /// use SqlClient to insert record into db
5 /// </summary>
6 /// <param name="dt">datatable</param>
7 /// <returns>insert sucessfully or not</returns>
8 public bool InsertDbByADO(List<VendorInfo> list)
9 {
10 SqlConnection _con = new SqlConnection(@"Data Source=STKWX028\SQLEXPRESS;Initial Catalog=Eva;Integrated Security=True");
11 SqlCommand cmd = new SqlCommand();
12 cmd.Connection = _con;
13 try
14 {
15 _con.Open();
16 for (int i = 0; i < list.Count; i++)
17 {
18 StringBuilder sb = new StringBuilder();
19 sb.Append("INSERT INTO [Vendors]([vendorID],[vendorName],[vendorAddress],[vendorPhone]) VALUES('");
20 sb.Append(list[i].VendorID + "','");
21 sb.Append(list[i].VendorName + "','");
22 sb.Append(list[i].Address + "','");
23 sb.Append(list[i].Phone + "')");
24 cmd.CommandText = sb.ToString();
25 cmd.ExecuteNonQuery();
26 }
27 }
28 catch
29 { return false; }
30 finally
31 { _con.Close(); }
32 return true;
33 }
34
35 /// <summary>
36 /// get the list of vendor record from excel
37 /// </summary>
38 /// <param name="filePath">the excel file Path</param>
39 /// <returns>list of vendor record</returns>
40 public List<VendorInfo> GetVendorsFromExcel(string filePath)
41 {
42 string excelConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filePath + "; Extended Properties=\"Excel 12.0;HDR=YES\"";
43 OleDbConnection conn = new OleDbConnection(excelConn);
44 List<VendorInfo> vendorList = new List<VendorInfo>();
45 string queryStr = @"SELECT VendorID,VendorName,Address,Phone FROM [Sheet1$]";
46 OleDbCommand cmd = new OleDbCommand(queryStr, conn);
47 try
48 {
49 conn.Open();
50 OleDbDataReader reader = cmd.ExecuteReader();
51 while (reader.Read())
52 {
53 VendorInfo vendorInfo = new VendorInfo();
54 vendorInfo.VendorID = Convert.ToInt32(reader["VendorID"]);
55 vendorInfo.VendorName = reader["VendorName"].ToString();
56 vendorInfo.Address = reader["Address"].ToString();
57 vendorInfo.Phone = reader["Phone"].ToString();
58 vendorList.Add(vendorInfo);
59 }
60 }
61 catch (Exception ex)
62 {
63 throw ex;
64 }
65 finally
66 {
67 conn.Close();
68
69 }
70 return vendorList;
71 }
72
73 #endregion
另外，作为一个小知识点，计算时间差：
代码
DateTime endTime = DateTime.Now;
lblEndTime.Text = endTime.ToString();
TimeSpan costTime = endTime.Subtract(beginTime);
//double time = endTime.Subtract(beginTime).TotalSeconds;
lblCostTime.Text =costTime.TotalSeconds.ToString();
Dataset方法的Excel导入导出，参考：ASP.NET Excel导入导出SQL Server整理
Be the change you want to see in the world.
