C#数据库数据导入导出系列之四 WinForm数据库导入导出到Excel
在日常的项目中，Excel，Word，txt等格式的数据导入到数据库中是很常见的，我在这里做一下总结
这里将分为Asp.net导入Sql Server，Oracle数据库和WinForm导入Sql Server，Oracle数据库。
这里将的数据库数据库导入导出，其实对Sql Server 和Oracle都是通用的
如果使用ADO.Net连接Oracle数据库，需要在引用里添加“System.Data.OracleClient ”，其他方面与连接Sql Server数据库是一样的
SqlConnection cn = new SqlConnection();OracleConnection oraleCn = new OracleConnection();
如果使用诸如Ibatis等持久层框架的话，唯一的区别就是在数据库连接语句上的差别而已。下面是两个例子
Oracle：Data Source=192.168.0.11/Contact;User ID=system;Password=ss;Unicode=True
Sql Server： Data Source=Contact；Server=localhost；uid=sa；pwd=ss
1，数据库导出到Excel
先看界面
然后是代码
View Code 1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Diagnostics; 10 using Microsoft.Office.Interop.Excel; 11 using System.Reflection; 12 using System.IO; 13 using System.Data.SqlClient; 14 using System.Data.OracleClient; 15 16 namespace SqlServer__Excel 17 { 18 public partial class SqlDB_To_Excel : Form 19 { 20 public SqlDB_To_Excel() 21 { 22 InitializeComponent(); 23 } 24 25 private Microsoft.Office.Interop.Excel.Application myExcel = null; 26 27 private void button1_Click(object sender, EventArgs e) 28 { 29 print(dataGridView1); 30 } 31 32 public void print(DataGridView dataGridView1) 33 { 34 //导出到execl 35 try 36 { 37 SaveFileDialog saveFileDialog = new SaveFileDialog(); 38 saveFileDialog.Filter = "导出Excel (*.xls)|*.xls"; 39 saveFileDialog.FilterIndex = 0; 40 saveFileDialog.RestoreDirectory = true; 41 saveFileDialog.CreatePrompt = true; 42 saveFileDialog.Title = "导出文件保存路径"; 43 saveFileDialog.ShowDialog(); 44 string strName = saveFileDialog.FileName; 45 if(strName.Length != 0) 46 { 47 //没有数据的话就不往下执行 48 if(dataGridView1.Rows.Count == 0) 49 return; 50 51 // toolStripProgressBar1.Visible = true; 52 System.Reflection.Missing miss = System.Reflection.Missing.Value; 53 //实例化一个Excel.Application对象 54 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); 55 excel.Application.Workbooks.Add(true); 56 excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。 57 if(excel == null) 58 { 59 MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); 60 return; 61 } 62 Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks; 63 Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss)); 64 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; 65 sheet.Name = "test"; 66 int m = 0, n = 0; 67 //生成Excel中列头名称 68 for(int i = 0; i < dataGridView1.Columns.Count; i++) 69 { 70 excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名 71 } 72 //把DataGridView当前页的数据保存在Excel中 73 for(int i = 0; i < dataGridView1.Rows.Count - 1; i++) 74 { 75 for(int j = 0; j < dataGridView1.Columns.Count; j++) 76 { 77 if(dataGridView1[j, i].ValueType == typeof(string)) 78 { 79 excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); 80 } 81 else 82 { 83 excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString(); 84 } 85 } 86 } 87 sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss); 88 book.Close(false, miss, miss); 89 books.Close(); 90 excel.Quit(); 91 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); 92 System.Runtime.InteropServices.Marshal.ReleaseComObject(book); 93 System.Runtime.InteropServices.Marshal.ReleaseComObject(books); 94 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); 95 GC.Collect(); 96 MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 97 // toolStripProgressBar1.Value = 0; 98 System.Diagnostics.Process.Start(strName); 99 }100 }101 catch(Exception ex)102 {103 MessageBox.Show(ex.Message, "错误提示");104 }105 }106 107 public void printAll(System.Data.DataTable dt)108 {109 //导出到execl110 try111 {112 SaveFileDialog saveFileDialog = new SaveFileDialog();113 saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";114 saveFileDialog.FilterIndex = 0;115 saveFileDialog.RestoreDirectory = true;116 saveFileDialog.CreatePrompt = true;117 saveFileDialog.Title = "导出文件保存路径";118 saveFileDialog.ShowDialog();119 string strName = saveFileDialog.FileName;120 if(strName.Length != 0)121 {122 //没有数据的话就不往下执行123 if(dt.Rows.Count == 0)124 return;125 126 // toolStripProgressBar1.Visible = true;127 System.Reflection.Missing miss = System.Reflection.Missing.Value;128 //实例化一个Excel.Application对象129 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();130 excel.Application.Workbooks.Add(true);131 excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。132 if(excel == null)133 {134 MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);135 return;136 }137 Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;138 Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));139 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;140 sheet.Name = "test";141 int m = 0, n = 0;142 143 144 //生成Excel中列头名称145 for(int i = 0; i < dt.Columns.Count; i++)146 {147 excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名148 }149 150 //把DataGridView当前页的数据保存在Excel中151 if(dt.Rows.Count > 0)152 {153 for(int i = 0; i < dt.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完154 {155 for(int j = 0; j < dt.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完156 {157 string str = dt.Rows[i][j].ToString();158 excel.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制159 }160 }161 }162 sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);163 book.Close(false, miss, miss);164 books.Close();165 excel.Quit();166 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);167 System.Runtime.InteropServices.Marshal.ReleaseComObject(book);168 System.Runtime.InteropServices.Marshal.ReleaseComObject(books);169 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);170 171 GC.Collect();172 MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);173 // toolStripProgressBar1.Value = 0;174 System.Diagnostics.Process.Start(strName);175 }176 }177 catch(Exception ex)178 {179 MessageBox.Show(ex.Message, "错误提示");180 }181 }182 183 private void SqlDB_To_Excel_Load(object sender, EventArgs e)184 {185 dataGridView1.DataSource = GetDataTableFromSqlServer();186 }187 188 private void button2_Click(object sender, EventArgs e)189 {190 printAll(GetDataTableFromSqlServer());191 }192 193 private System.Data.DataTable GetDataTableFromSqlServer()194 {195 string sqlconn = "database=database1;server=localhost;uid=sa;pwd=sa";196 SqlConnection cn = new SqlConnection(sqlconn);197 string cmdText = "select * from users";198 SqlDataAdapter da = new SqlDataAdapter(cmdText, cn);199 DataSet ds = new DataSet();200 da.Fill(ds, "table1");201 return ds.Tables[0];202 }203 204 private System.Data.DataTable GetDataTableFromOracle()205 {206 string oracleconn = "Data Source=192.168.2.105/Database1;User ID=system;Password=ss;Unicode=True";207 OracleConnection cn = new OracleConnection(oracleconn);208 string cmdText = "select * from users";209 OracleDataAdapter da = new OracleDataAdapter(cmdText, cn);210 DataSet ds = new DataSet();211 da.Fill(ds, "table1");212 return ds.Tables[0];213 }214 }215 } 
代码的思想就是将数据从数据库中读出到DataTable或者DataGridView中，然后遍历他们的每一个单元格的值，给Excel对应的单元格赋值。
使用DataTable作为遍历的对象，是为了去除分页的困扰。
看到这里，如果换一个List<T>对象集合，也应该能导出到数据库中了。
2，Excel数据导入到数据库
在WinForm中Excel导入到数据库中和在WebForm中的导入过程是一样的。可参见前面的内容。