C # How to export data to excel

 

 

1. First, declare that these methods are also collected by myself, and then used. If there are deficiencies in the program, please give pointers.
2. There are many examples of using SQL statements to import and export data on the Internet.

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 = xlapp. workbooks;
Excel. Workbook workbook = workbooks. Add (Excel. xlwbatemplate. xlwbatworksheet );
Excel. 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 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;

These methods did not close the Excel process. There is a lot of such information, and I don't want to write it here. I hope this will bring convenience to some people.

 

Http://community.csdn.net/Expert/topic/4570/4570895.xml? Temp = 1.687258e-02

Appendix:

Http://topic.csdn.net/u/20091108/01/2893410f-001d-4b35-8252-cd3f941b7be7.html

Http://topic.csdn.net/u/20091104/14/f7c72172-2bd8-4a1a-b5d1-c0d80878bf27.html

 

Notes: Efficiency

1. The most efficient direct use of command lines

2. Data sources

3. Using COM components is the slowest, but the import efficiency in batches may be high.

 

 

// ======================================

Other Summary

 

 

This is for Excel operations. What do you want? · If you want to export the instance, others will answer the question.
1 open Excel dim myexcel as new excel. Application () myexcel. Visible = true

2. Add new workbook myexcel. workbooks. Add ()

3. Set the second worksheet as the active worksheet myexcel. worksheets (2). acivate ()

4. Open the specified Excel file myexcel. workbooks. Open ("C:/my.xls ")

5. Show the Excel window myexcel. Visible = true

6. Change the title bar of Excel. myexcel. Caption = "Welcome! "

7. Assign the value of myexcel to the cells in Excel. cells (1, 4 ). value = 100 this statement makes the fourth column of the first row of the current Excel worksheet, that is, the D1 cell is equal to 100, you can also write: myexcel. range ("d1 "). value = 100

8. Set the width of the specified column (unit: number of characters). myexcel. activesheet. colums (1). columnwidth = 20. Set the width of the 1st column of the current worksheet to 20.

9 set the height of the specified row (unit: lbs) myexcel. activesheet. Rows (1). rowheight = 1/0. 035 1 lb = 0.035 cm set the height of 1st rows to 1 cm

10 insert a page break myexcel. activesheet. Rows (20). pagebreak = 1 insert a page break before 20th rows

11 Delete the Page Break myexcel. activesheet. Columns (20). pagebreak = 0 Delete the page break before the 20th Column

12. Specify the border line width myexcel. activesheet. range ("B3: D3 "). borders (1 ). weight = 3 where the borders parameter specifies the cell border position: 1: Left 2: Right 3: Top 4: bottom 5: Oblique/6: Oblique/

13. Specify the border line type myexcel. activesheet. range ("B1: D3 "). borders (2 ). linestyle = 1 This statement sets the right border of cell B1: D3 of the current worksheet to the solid line linestyle parameter: 1: solid line 2: dotted line 3: dotted line 4: double solid line

14 set the footer myexcel. activesheet. pagesetup. centerfooter = "Page & P" NOTE: When setting the footer, ensure that a printer is installed on the computer; otherwise, an error occurs!

15 set the header myexcel. activesheet. pagesetup. centerfooter = "Page & P"

16 set the maximum disconnection distance from the header to 2 cm myexcel. activesheet. pagesetup. headermargin = 2/0. 035

17 set the footer end distance to 2 cm myexcel. activesheet. pagesetup. footermargin = 2/0. 035

18 set the top edge margin to 2 cm myexcel. activesheet. pagesetup. topmargin = 2/0. 035

19 set the bottom edge margin to 2 cm myexcel. activesheet. pagesetup. bottommargin = 2/0. 035

20 set the left margin to 2 cm myexcel. activesheet. pagesetup. leftmargin = 2/0. 035

21. Set the right margin to 2 cm myexcel. activesheet. pagesetup. rightmargin = 2/0. 035.

22 set the horizontal center of the page. myexcel. activesheet. pagesetup. centerhorizontally = true

23 set vertical center of page myexcel. activesheet. pagesetup. centervertically = true

24 set the page size (1, narrow rows 8.5*11; 39, wide rows 14*11) myexcel. activesheet. pagesetup. papersize = 1

25 print the cell gridline myexcel. activesheet. pagesetup. printgridlines = true

26 copy the entire worksheet myexcel. activesheet. usedrange. Copy

27 copy myexcel. activesheet. Range ("A1: B5"). Copy

28 paste myexcel. worksheets ("sheet2"). Range ("A1"). pastespecial

29 insert a row of myexcel. activesheet. Rows (2). Insert in front of row 2nd

30 Insert a column of myexcel. activesheet. columns (2). Insert in front of Column 2nd

31 merge C4: D4 Cell myexcel. activesheet. Range ("C4: D4"). Merge ()

32 automatically adjust 2nd column width myexcel. activesheet. columns (2). autofit

33 set the font myexcel. activesheet. cells (). Font. Name = ""

34 set the font size myexcel. activesheet. cells (). Font. size = 25

35. Set the font to myexcel. activesheet. cells (). Font. italic = true.

36 set the font to bold myexcel. activesheet. cells (). Font. Bold = true

37 clear the cell content myexcel. activesheet. cells (). clearcontents

38 print preview worksheet myexcel. activesheet. printpreview

39 print the worksheet myexcel. activesheet. Printout

40 Save the worksheet as myexcel. activeworkbook. saveas ("C:/book2.xls ")

41 discard the storage disk myexcel. activeworkbook. Saved = false

42 close the workbook myexcel. workbooks. Close

43 exit Excel myexcel. Quit

Http://wenwen.soso.com/z/q183619223.htm