32806人阅读
c#程序设计（152）
&&& 有时候需要将数据库的数据导出至Excel表格表格，以便进行查看和分析，那么如何导出呢？下面用代码来实现。
&&& 首先，新建一个工程，需要添加引用Microsoft.Office.Interop.Excel.dll，以Oracle数据库为例（只要读出DataTable或DataSet就行了，哪种数据库没关系）。
1、创建一个表格，并插入如下数据。
drop table TABLETESTEXCEL;
create table TABLETESTEXCEL
NUMBER not null,
col_name VARCHAR2(32),
VARCHAR2(4),
col_work VARCHAR2(32),
col_mony FLOAT
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (1, '吴一', 25, '男', '.NET', 5000);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (2, '孙二', 24, '男', 'JAVA', 4999);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (3, '张三', 25, '男', 'PHP', 5001);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (4, '李四', 26, '男', 'DELPHI', 5002);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (5, '王五', 27, '男', 'C++', 5003);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (6, '赵六', 25, '男', 'C', 4008);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (7, '燕七', 25, '男', '数据库', 4007);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (8, '胡八', 25, '男', 'JSP', 5005);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (9, '钱九', 25, '男', 'ASP.NET', 4005);
insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
values (10, '沈十', 25, '男', 'VB', 4000);
2、C#代码实现
数据库操作的类：
public class DataBaseHelper
public static DataTable ExecuterQuery(string connectionString, string commandSql)
DataTable dataTable = new DataTable();
using (OracleConnection oracleConnection =
new OracleConnection(connectionString))
oracleConnection.Open();
using (OracleDataAdapter oracleDataAdapter =
new OracleDataAdapter(commandSql,oracleConnection))
oracleDataAdapter.Fill(dataTable);
oracleConnection.Close();
return dataT
}public class DataBaseDao
public static DataTable GetDataBaseTable()
string sql = & SELECT * FROM tableTestExcel&;
return DataBaseHelper.ExecuterQuery(&User ID=Password=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._Workbook workB
Excel._Worksheet workS
object misValue = System.Reflection.Missing.V
excel = new Excel.ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (Excel._Worksheet)workBook.ActiveS
int rowIndex = 1;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
colIndex++;
excel.Cells[1, colIndex] = col.ColumnN
//取得表格中的数据
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.xlVAlignC
excel.Visible =
workBook.SaveAs(SaveFile, Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
dataTable =
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.Kill(excel);//调用kill当前excel进程
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
private static void releaseObject(object obj)
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
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)
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
写好了如上的类，那么开始调用吧，调用：
DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(),
@&F:\outputFormDataBase.xls&);
这样成功将数据导出，如图。
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1808798次
积分：22751
积分：22751
排名：第301名
原创：396篇
转载：19篇
评论：469条
文章：31篇
阅读：102295
文章：27篇
阅读：48331
(1)(1)(3)(4)(1)(4)(1)(2)(1)(5)(2)(2)(3)(5)(6)(1)(1)(5)(6)(5)(5)(5)(1)(6)(2)(3)(8)(7)(7)(7)(4)(11)(4)(5)(4)(7)(3)(5)(6)(8)(4)(4)(4)(7)(15)(6)(22)(31)(26)(16)(30)(35)(8)(24)(6)(9)(1)