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