C # export to excel

Method 1: to call the COM component and 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, access. acspreadsheettype. acspreadsheettypeexcel9, "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 at a slower speed 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]; // obtain sheet1
excel. range;

// write a field
for (INT I = 0; I {< br> worksheet. cells [1, I + 1] = DT. columns [I]. columnname;
range = (Excel. range) worksheet. cells [1, I + 1];
}< br> for (INT r = 0; r {< br> worksheet. cells [R + 2, 1] = R + 1;
for (INT I = 0; I {< br> // 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];
}< br> rowread ++;
percent = (float) (100 * rowread)/totalcount;
// This. FM. captiontext. TEXT = "exporting data, exported [" + percent. tostring ("0.00") + "%]... ";
system. windows. forms. application. doevents ();
}< br> 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 advice.
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 + = "\ t ";
}
Stringbuffer + = "\ n ";
}
// 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;

none of these methods disable the Excel process, there are a lot of such materials and I will not write them here. I hope these materials will bring convenience to some people.