C # reading Excel files

1. Introduce the corresponding namespace:

View plaincopy to clipboardprint?

1. UsingSystem;
2. UsingSystem. Collections. Generic;
3. UsingSystem. text;
5. UsingSystem. Windows. forms;
6. UsingSystem. Data;
7. UsingSystem. Data. oledb;
8. UsingSystem. Data. sqlclient;

2. Read the Excel file and store the content in Dataset

View plaincopy to clipboardprint?

2. /// <Summary>
4. /// Read the Excel file and store the content in Dataset
6. /// </Summary>
8. /// <Param name = "opnfilename"> Excel file name with path </param>
10. /// <Returns> dataset </returns>
12. Public StaticDataset exceltodataset (StringOpnfilename)
14. {
16. StringStrconn ="Provider = Microsoft. Jet. oledb.4.0; Data Source ="+ Opnfilename +"; Extended properties = \" Excel 8.0; HDR = yes; IMEX = 1 \"";
18. Oledbconnection conn =NewOledbconnection (strconn );
20. StringStrexcel ="";
22. Oledbdataadapter mycommand =Null;
24. Dataset DS =NewDataset ();
26. Strexcel ="Select * from [sheet1 $]";
28. Try
30. {
32. Conn. open ();
34. Mycommand =NewOledbdataadapter (strexcel, strconn );
36. Mycommand. Fill (DS,"Dtsource");
38. ReturnDS;
40. }
42. Catch(Exception ex)
44. {
46. // MessageBox. Show ("Import error:" + ex, "error message ");
48. Console. writeline (Ex );
50. ReturnDS;
52. }
54. Finally
56. {
58. Conn. Close ();
60. Conn. Dispose ();
62. }
64. }

3. Insert the read data to the database.

View plaincopy to clipboardprint?

2. /// <Summary>
4. /// Save all information
6. /// </Summary>
8. Public Static VoidSaveall (Dataset DS)
10. {
12. Foreach(Datarow rowInDS. Tables [0]. Rows)
14. {
16. Console. writeline ("{0} \ t {1} \ t {2 }", Row [0], row [1], row [2]);
20. Userinfo =NewUserinfo ();
22. Userinfo. Username = (String) Row [1];
24. Userinfo. userpwd = (String) Row [2];
28. Add (userinfo );
30. }
32. }
38. Private Static VoidAdd (userinfo)
40. {
42. StringSqlcon ="Server =.; uid = sa; Pwd = 123; database = ACCP";// Connection string
44. Sqlconnection con =NewSqlconnection (sqlcon );// Obtain the connection object
48. Sqlcommand COM =NewSqlcommand ("Insert into userinfo values ('"+ Userinfo. username +"','"+ Userinfo. userpwd +"')", Con );
50. Con. open ();
52. IntRows = com. executenonquery ();
54. If(Con! =Null)
56. {
58. Con. Close ();
60. }
62. }