C # How to export database data to an Excel worksheet

Sometimes we need to export the database data to an Excel table for viewing and analysis. How can we export it? The following code is used for implementation.
First, create a new project and add reference Microsoft. Office. Interop. Excel. dll. Take the Oracle database as an example (you only need to read the able or DataSet, which database does not matter ).

1. Create a table and insert the following data.

Drop table TABLETESTEXCEL;
Create table TABLETESTEXCEL
(
Col_id NUMBER not null,
Col_name VARCHAR2 (32 ),
Col_age NUMBER,
Col_sex VARCHAR2 (4 ),
Col_work VARCHAR2 (32 ),
Col_mony FLOAT
);
Data:

Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (1, 'wu yi', 25, 'male', '. net', 5000 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (2, 'Sun 2', 24, 'male', 'java', 4999 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (3, 'zhang san', 25, 'male', 'php', 5001 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (4, 'Lee 4', 26, 'mal', 'delphi, 5002 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (5, 'wang 5', 27, 'male', 'c ++ ', 5003 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (6, 'zhao liu', 25, 'male', 'C', 4008 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (7, 'swallow 7', 25, 'male', 'database', 4007 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (8, 'hu Ba', 25, 'male', 'jsps, 5005 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (9, 'money 9', 25, 'male', 'asp. net', 4005 );
 
Insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
Values (10, 'shen 10', 25, 'mal', 'vb ', 4000 );
Commit;
2. C # code implementation

Database Operation class:

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 ()
{
String SQL = "SELECT * FROM tableTestExcel ";
 
Return DataBaseHelper. ExecuterQuery ("User ID = downsoft; Password = sys; Data Source = orcl", SQL );
}
}
Excel export class:

Public class DataChangeExcel
{
/// <Summary>
/// Convert the database to an excel table
/// </Summary>
/// <Param name = "dataTable"> database data </param>
/// <Param name = "SaveFile"> exported excel file </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;
 
// Obtain the title
Foreach (DataColumn col in dataTable. Columns)
{
ColIndex ++;
 
Excel. Cells [1, colIndex] = col. ColumnName;
}
 
// Obtain the data in the table
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 ();
 
// Set the center and alignment of the table content
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, Excel. XlSaveAsAccessMode. xlExclusive,

MisValue, misValue );
 
DataTable = null;
 
WorkBook. Close (true, misValue, misValue );
 
Excel. Quit ();
 
PublicMethod. Kill (excel); // call kill current excel Process
 
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 ();
}
}
}
To disable a process:

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. getprocpolicyid (k );

P. Kill ();
}
Catch
{}
}
}
After writing the above class, start calling. call:

DataChangeExcel. DataSetToExcel (DataBaseDao. GetDataBaseTable (),
@ "F: \ outputFormDataBase.xls ");
In this way, the data is exported successfully ,.

From Poplar