1.新建一个工程,需要添加引用Microsoft.Office.Interop.Excel.dll
2.usingSystem.Data;
usingSystem.Data.OleDb;
还有个是Sys.confi...
3.数据库操作的类:
using System.Data.OracleClient;
public class DataBaseHelper
{
public static DataTable ExecuterQuery(string connectionString, string commandSql)
{
DataTable dataTable = new DataTable();
try
{
using (OracleConnection oracleConnection =
new OracleConnection(connectionString))
{
oracleConnection.Open();
using (OracleDataAdapter oracleDataAdapter =
new OracleDataAdapter(commandSql,oracleConnection))
{
oracleDataAdapter.Fill(dataTable);
}
oracleConnection.Close();
}
}
catch
{
return null;
}
return dataTable;
}
}
public class DataBaseDao
{
public static DataTable GetDataBaseTable()
{
///如果有APP.config的话,就是
string strConn =System.Configuration.ConfigurationManager.ConnectionStrings["DTASDataBase"].ConnectionString.Replace(";Unicode=True", "");
//string sql = "SELECT * FROM table";
return DataBaseHelper.ExecuterQuery(strConn, sql);
//return DataBaseHelper.ExecuterQuery("User ID=downsoft;Password=sys;Data Source=orcl", sql);
}
}
导出Excel的类:
public class DataChangeExcel
{
/// <summary>
/// 数据库转为excel表格
/// </summary>
/// <param name="dataTable">数据库数据</param>
/// <param name="SaveFile">导出的excel文件</param>
public static void DataSetToExcel(DataTable dataTable, string SaveFile)
{
Excel.Application excel;
Excel._Workbook workBook;
Excel._Worksheet workSheet;
object misValue = System.Reflection.Missing.Value;
excel = new Excel.ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (Excel._Worksheet)workBook.ActiveSheet;
int rowIndex = 1;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//取得表格中的数据
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] =
row[col.ColumnName].ToString().Trim();
//设置表格内容居中对齐
workSheet.get_Range(excel.Cells[rowIndex, colIndex],
excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
}
excel.Visible = false;
workBook.SaveAs(SaveFile, Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
dataTable = null;
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.Kill(excel);//调用kill当前excel进程
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
}
关闭进程的类:
public class PublicMethod
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
{
try
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
catch
{ }
}
}
最后在窗体结构中加一个按钮,点击事件
private void button1_Click(object sender, EventArgs e)
{
DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(),@"F:\outputDataBase.xls");
}
如果出现