C # five methods of exporting access data to an Excel File Format

1. First declare that these methods are also collected by myself, and then used, the program deficiencies, Please Master advice. These methods have not closed the Excel process.
2. There are many examples of using SQL statements to import and export data on the Internet.

Method 1: When you call the com component to export access data to Excel, you can directly call the access export function. This method is extremely fast.
Using Access;
Access. ApplicationClass oAccess = new Access. ApplicationClass ();
OAccess. Visible = false;
Try
{// ACCESS9:
OAccess. OpenCurrentDatabase ("d: \ wcf. mdb", false ,"");
// Export to excel
OAccess. DoCmd. TransferSpreadsheet (Access. AcDataTransferType. acExport, Acce ss. AcSpreadSheetType. Sheet, "worksheet name", "d: \ wcf.xls", true, null, null );
// Import txt
// OAccess. DoCmd. TransferText (Access. AcTextTransferType. acExportDelim, "", "Enterprise", "d: \ wcf.txt", true, "", 0 );
OAccess. closecuritydatabase ();
OAccess. DoCmd. Quit (Access. AcQuitOption. acQuitSaveNone );
System. Runtime. InteropServices. Marshal. ReleaseComObject (oAccess );
OAccess = null;
MessageBox. Show ("imported successfully ");
}
Catch (Exception ex)
{
MessageBox. Show (ex. ToString ());
}
Finally
{
GC. Collect ();
}

Method 2: This method is fast, but the exported format is not standard Excel format. The default worksheet name is the same as the file name.
String FileName = "d: \ abc.xls ";
System. Data. DataTable dt = new System. Data. DataTable ();
FileStream objFileStream;
StreamWriter objStreamWriter;
String strLine = "";
ObjFileStream = new FileStream (FileName, FileMode. OpenOrCreate, FileAccess. Write );
ObjStreamWriter = new StreamWriter (objFileStream, System. Text. Encoding. Unicode );
For (int I = 0; I <dt. Columns. Count; I)
{
StrLine = strLine dt. Columns [I]. ColumnName. ToString () Convert. ToChar (9 );
}
ObjStreamWriter. WriteLine (strLine );
StrLine = "";
For (int I = 0; I <dt. Rows. Count; I)
{
StrLine = strLine (I 1) Convert. ToChar (9 );
For (int j = 1; j <dt. Columns. Count; j)
{
StrLine = strLine dt. Rows [I] [j]. ToString () Convert. ToChar (9 );
}
ObjStreamWriter. WriteLine (strLine );
StrLine = "";
}
ObjStreamWriter. Close ();
ObjFileStream. Close ();

Method 3: Using Ado.net this method is slower than the above two, and the larger the data volume, the more obvious
Int Id = 0;
String Name = "test ";
String FileName = "d: \ abc.xls ";
System. Data. DataTable dt = new System. Data. DataTable ();
Long totalCount = dt. Rows. Count;
Long rowRead = 0;
Float percent = 0;
OleDbParameter [] parm = new OleDbParameter [dt. Columns. Count];
String connString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" FileName "; Extended Properties = Excel 8.0 ;";
OleDbConnection objConn = new OleDbConnection (connString );
OleDbCommand objCmd = new OleDbCommand ();
ObjCmd. Connection = objConn;
ObjConn. Open ();
// Create a table structure
ObjCmd. CommandText = @ "create table Sheet1 (serial number Integer, name varchar )";
ObjCmd. ExecuteNonQuery ();
// Create the INSERT Command
ObjCmd. CommandText = "insert into Sheet1 (" Id "," Name ")";
Parm [0] = new OleDbParameter ("@ Id", OleDbType. Integer );
ObjCmd. Parameters. Add (parm [0]);
Parm [1] = new OleDbParameter ("@ Company", OleDbType. VarChar );
ObjCmd. Parameters. Add (parm [1]);
// Traverse DataTable to insert data into the new Excel File
For (int I = 0; I <dt. Rows. Count; I)
{
Parm [0]. Value = I 1;
For (int j = 1; j <parm. Length; j)
{
Parm [j]. Value = dt. Rows [I] [j];
}
ObjCmd. ExecuteNonQuery ();
RowRead;
Percent = (float) (100 * rowRead)/totalCount;
// This. FM. CaptionText. Text = "exporting data. exported [" percent. ToString ("0.00") "%]...";
If (I = dt. Rows. Count-1)
// This. FM. CaptionText. Text = "Please wait ......";
System. Windows. Forms. Application. DoEvents ();
}
ObjConn. Close ();
// This. FM. CaptionText. Text = "";

Method 4: This method calls the com component, which is slower than the preceding three methods.
Using Excel;
System. Data. DataTable dt = new System. Data. DataTable ();
String FileName = "d: \ abc.xls ";
Long totalCount = dt. Rows. Count;
Long rowRead = 0;
Float percent = 0;
Excel. Application xlApp = null;
XlApp = new Excel. Application ();
Excel. Workbooks workbooks = xlApp. Workbooks;
Excel. Workbook workbook = workbooks. Add (Excel. XlWBATemplate. xlWBATWorksheet );
Excel. Worksheet worksheet = (Excel. Worksheet) workbook. Worksheets [1];
// Get sheet1
Excel. Range range;
// Write Fields
For (int I = 0; I <dt. Columns. Count; I)
{
Worksheet. Cells [1, I 1] = dt. Columns [I]. ColumnName;
Range = (Excel. Range) worksheet. Cells [1, I 1];
}
For (int r = 0; r <dt. Rows. Count; r)
{
Worksheet. Cells [r 2, 1] = r 1;
For (int I = 0; I <dt. Columns. Count; I)
{
// Worksheet. Cells [r 2, I 1] = dt. Rows [r] [I];
If (I 1! = Dt. Columns. Count)
Worksheet. Cells [r 2, I 2] = dt. Rows [r] [I 1];
}
RowRead;
Percent = (float) (100 * rowRead)/totalCount;
// This. FM. CaptionText. Text = "exporting data. exported [" percent. ToString ("0.00") "%]...";
System. Windows. Forms. Application. DoEvents ();
}
Range = worksheet. get_Range (worksheet. Cells [2, 1], worksheet. Cells [dt. Rows. Count 2, dt. Columns. Count]);
Workbook. Saved = true;
Workbook. SaveCopyAs (FileName );
// This. FM. CaptionText. Text = "";

Method 5: Using the clipboard, some people say this method is very fast, but when I use it, this method is the slowest, please give pointers.
System. Data. DataTable dt = new System. Data. DataTable ();
String filePath = @ "d: abc.xls ";
Object oMissing = System. Reflection. Missing. Value;
Excel. ApplicationClass xlApp = new Excel. ApplicationClass ();
Try
{
XlApp. Visible = false;
XlApp. DisplayAlerts = false;
Excel. Workbooks oBooks = xlApp. Workbooks;
Excel. _ Workbook xlWorkbook = null;
XlWorkbook = oBooks. Open (filePath, oMissing,
OMissing, oMissing );
Excel. Worksheet xlWorksheet;
// Add a new Sheet.
XlWorksheet = (Excel. Worksheet) xlWorkbook. Worksheets. Add (oMissing, oMissing, 1, oMissing );
// Use TableName as the newly added Sheet name.
XlWorksheet. Name = "Enterprise directory ";
// Retrieve all values in this able and store them in stringBuffer.
String stringBuffer = "";
For (int j = 0; j <dt. Rows. Count; j)
{
For (int k = 0; k <dt. Columns. Count; k)
{
StringBuffer = dt. Rows [j] [k]. ToString ();
If (k <dt. Columns. Count-1)
StringBuffer = "";
}
StringBuffer = "";
}
// Use the system clipboard
System. Windows. Forms. Clipboard. SetDataObject ("");
// Place the stringBuffer into the clipboard.
System. Windows. Forms. Clipboard. SetDataObject (stringBuffer );
// Select the first cell on the sheet page
(Excel. Range) xlWorksheet. Cells [1, 1]). Select ();
// Paste!
XlWorksheet. Paste (oMissing, oMissing );
// Clear the system clipboard.
System. Windows. Forms. Clipboard. SetDataObject ("");
// Save and close the workbook.
XlWorkbook. Close (Excel. XlSaveAction. xlSaveChanges, oMissing, oMissing );
System. Runtime. InteropServices. Marshal. ReleaseComObject (xlWorkbook );
XlWorkbook = null;