Datatable and Excel file (CSV) Conversion

In a project, you need to import data from an Excel file and display it on the datagridview. You also need to right-click the datagridview to save the data as an Excel file, so you have written these two tools. This article provides two methods for converting between EXCEL and able.

1. import data from an Excel or CSV file to a able:
 

1. Public static datatable csvtoable able (string file)
2. {
3. String strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + file + "; extended properties = 'excel 8.0; '"; // Excel File
4. If (file. endswith (". CSV "))
5. Strconn = "provider = Microsoft. jet. oledb.4.0; Data Source = "+ path +"; extended properties = 'text; HDR = yes; FMt = delimited; '"; // CSV file: HDR = yes -- first line is Header
6. Oledbconnection oleconn = new oledbconnection (strconn );
7. Oleconn. open ();
8. Datatable sheets = oleconn. getoledbschematable (oledbschemaguid. Tables, null );
9. If (sheets = NULL | sheets. Rows. Count <1)
10. {
11. Return NULL;
12. }
13. String filename = sheets. Rows [0] ["table_name"]. tostring (); // sheets. Rows [0] -- first sheet of Excel
14. If (file. endswith (". CSV "))
15. Filename = file. substring (file. lastindexof ("/"));
16. String olestr = "select * from [" + filename + "]";
17. If (file. endswith (". CSV "))
18. Olestr = "select * from [" + filename + "]";
19. Oledbcommand olecomm = new oledbcommand (olestr, oleconn );
20. Olecomm. Connection = oleconn;
21. Oledbdataadapter oleda = new oledbdataadapter ();
22. Oleda. selectcommand = olecomm;
23. Dataset DS = new dataset ();
24. Oleda. Fill (DS );
25. Oleconn. Close ();
26. Return Ds. Tables [0];
27. }

2. datatable to the CSV file:
 

1. Public static void datatabletocsv (datatable table, string file)
2. {
3. String title = "";
4.  
5. Filestream FS = new filestream (file, filemode. Create );
6. Streamwriter Sw = new streamwriter (New bufferedstream (FS), system. Text. encoding. Default );
7.  
8. For (INT I = 0; I <Table. Columns. Count; I ++)
9. {
10. Title + = table. Columns [I]. columnname + ",";
11. }
12. Title = title. substring (0, title. Length-1) + "\ n ";
13. Sw. Write (title );
14.  
15. Foreach (datarow row in table. Rows)
16. {
17. String line = "";
18. For (INT I = 0; I <Table. Columns. Count; I ++)
19. {
20. Line + = row [I]. tostring () + ",";
21. }
22. Line = line. substring (0, line. Length-1) + "\ n ";
23.  
24. Sw. Write (line );
25. }
26.  
27. Sw. Close ();
28. FS. Close ();
29. }
30. }
31.  
32.  

This article is from the "little he Beibei's technical space" blog, please be sure to keep this http://babyhe.blog.51cto.com/1104064/407065