C# 实现Oracle中的数据与Excel之间的转换
最近项目要求实现数据库之间数据在各个数据库之间导入导出，在此做个笔记
1. 将Oracle中的表导入到Excel中，反之亦然
private static readonly string connectionString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
1 public void print(DataGridView dataGridView1) 2 { 3 //导出到execl 4 try 5 { 6 SaveFileDialog saveFileDialog = new SaveFileDialog(); 7 saveFileDialog.Filter = "导出Excel2003~2007 (*.xls)|*.xls|导出Excel2010~2013 (*.xlsx)|*.xlsx"; 8 saveFileDialog.FilterIndex = 0; 9 saveFileDialog.RestoreDirectory = true; 10 saveFileDialog.CreatePrompt = true; 11 saveFileDialog.Title = "导出文件保存路径"; 12 saveFileDialog.ShowDialog(); 13 string strName = saveFileDialog.FileName; 14 if (strName.Length != 0) 15 { 16 //没有数据的话就不往下执行 17 if (dataGridView1.Rows.Count == 0) 18 return; 19 20 // toolStripProgressBar1.Visible = true; 21 System.Reflection.Missing miss = System.Reflection.Missing.Value; 22 //实例化一个Excel.Application对象 23 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); 24 excel.Application.Workbooks.Add(true); 25 excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。 26 if (excel == null) 27 { 28 MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); 29 return; 30 } 31 Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks; 32 Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss)); 33 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; 34 sheet.Name = "test"; 35 int m = 0, n = 0; 36 //生成Excel中列头名称 37 for (int i = 0; i < dataGridView1.Columns.Count; i++) 38 { 39 excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名 40 } 41 //把DataGridView当前页的数据保存在Excel中 42 for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) 43 { 44 for (int j = 0; j < dataGridView1.Columns.Count; j++) 45 { 46 if (dataGridView1[j, i].ValueType == typeof(string)) 47 { 48 excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); 49 } 50 else 51 { 52 excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString(); 53 } 54 } 55 } 56 sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss); 57 book.Close(false, miss, miss); 58 books.Close(); 59 excel.Quit(); 60 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); 61 System.Runtime.InteropServices.Marshal.ReleaseComObject(book); 62 System.Runtime.InteropServices.Marshal.ReleaseComObject(books); 63 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); 64 GC.Collect(); 65 MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 66 // toolStripProgressBar1.Value = 0; 67 System.Diagnostics.Process.Start(strName); 68 } 69 } 70 catch (Exception ex) 71 { 72 MessageBox.Show(ex.Message, "错误提示"); 73 } 74 }
1 public void printAll(System.Data.DataTable dt) 2 { 3 //导出到execl 4 try 5 { 6 SaveFileDialog saveFileDialog = new SaveFileDialog(); 7 saveFileDialog.Filter = "导出Excel (*.xls)|*.xls"; 8 saveFileDialog.FilterIndex = 0; 9 saveFileDialog.RestoreDirectory = true; 10 saveFileDialog.CreatePrompt = true; 11 saveFileDialog.Title = "导出文件保存路径"; 12 saveFileDialog.ShowDialog(); 13 string strName = saveFileDialog.FileName; 14 if (strName.Length != 0) 15 { 16 //没有数据的话就不往下执行 17 if (dt.Rows.Count == 0) 18 return; 19 20 // toolStripProgressBar1.Visible = true; 21 System.Reflection.Missing miss = System.Reflection.Missing.Value; 22 //实例化一个Excel.Application对象 23 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); 24 excel.Application.Workbooks.Add(true); 25 excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。 26 if (excel == null) 27 { 28 MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); 29 return; 30 } 31 Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks; 32 Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss)); 33 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; 34 sheet.Name = "test"; 38 //生成Excel中列头名称 39 for (int i = 0; i < dt.Columns.Count; i++) 40 { 41 excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名 42 } 43 44 //把DataGridView当前页的数据保存在Excel中 45 if (dt.Rows.Count > 0) 46 { 47 for (int i = 0; i < dt.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完 48 { 49 for (int j = 0; j < dt.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完 50 { 51 string str = dt.Rows[i][j].ToString(); 52 excel.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制 53 } 54 } 55 } 56 sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss); 57 book.Close(false, miss, miss); 58 books.Close(); 59 excel.Quit(); 60 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); 61 System.Runtime.InteropServices.Marshal.ReleaseComObject(book); 62 System.Runtime.InteropServices.Marshal.ReleaseComObject(books); 63 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); 64 65 GC.Collect(); 66 MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 67 // toolStripProgressBar1.Value = 0; 68 System.Diagnostics.Process.Start(strName); 69 } 70 } 71 catch (Exception ex) 72 { 73 MessageBox.Show(ex.Message, "错误提示"); 74 } 75 }
这两个方法都可以将Oracle导出到Excel中。转自http://www.cnblogs.com/cpcpc/archive/2012/11/13/2767934.html
1 class Excel2Oracle 2 { 3 private static readonly string connectionString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString; 4 public void InsertData2Oracle(DataSet ds) 5 { 6 using (OleDbConnection oraCon = new OleDbConnection(connectionString)) 7 { 8 oraCon.Open(); 9 using (OleDbCommand cmd = new OleDbCommand()) 10 { 11 cmd.Connection = oraCon; 12 cmd.CommandType = CommandType.Text; 13 14 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) 15 { 16 List<string> list_ = new List<string>(); 17 for (int j = 0; j < ds.Tables[0].Columns.Count; j++) 18 { 19 list_.Add(ds.Tables[0].Rows[i][j].ToString()); 20 } 21 string temp = ""; 22 string t = ","; 23 24 for (int k = 0; k < list_.Count; k++) 25 { 26 if (k == list_.Count - 1) t = ""; 27 temp = temp + "'" + list_[k] + "'" + t + ""; 28 cmd.CommandText = string.Format("Insert into TBUSER values(" + temp + ")"); 29 } 30 31 cmd.ExecuteNonQuery(); 32 } 33 34 } 35 oraCon.Close(); 36 37 } 38 }
}
代码比废话更要迷人，我就不多说什么了
