C # Excel (Import and Export)

View plaincopy to clipboardprint?

2. ClassExampleoflegalsoft
4. {
6. /// <Summary>
8. /// Read the Excel document
10. /// </Summary>
12. /// <Param name = "path"> file name </param>
14. /// <Returns> Returns a dataset </returns>
16. PublicDataset exceltods (StringPath)
18. {
20. StringStrconn ="Provider = Microsoft. Jet. oledb.4.0 ;"+"Data Source ="+ Path +";"+"Extended properties = Excel 8.0 ;";
22. Oledbconnection conn =NewOledbconnection (strconn );
24. Conn. open ();
26. StringStrexcel ="";
28. Oledbdataadapter mycommand =Null;
30. Dataset DS =Null;
32. Strexcel ="Select * from [sheet1 $]";
34. Mycommand =NewOledbdataadapter (strexcel, strconn );
36. DS =NewDataset ();
38. Mycommand. Fill (DS,"Table1");
40. ReturnDS;
42. }
44. /// <Summary>
46. /// Write the Excel document
48. /// </Summary>
50. /// <Param name = "path"> file name </param>
52. Public BoolSavefp2toexcel (StringPath)
54. {
56. Try
58. {
60. StringStrconn ="Provider = Microsoft. Jet. oledb.4.0 ;"+"Data Source ="+ Path +";"+"Extended properties = Excel 8.0 ;";
62. Oledbconnection conn =NewOledbconnection (strconn );
64. Conn. open ();
66. System. Data. oledb. oledbcommand cmd =NewOledbcommand ();
68. Cmd. Connection = conn;
70. // Cmd. commandtext = "Update [sheet1 $] Set Name =" 2005-01-01 "where employee ID =" date "";
72. // Cmd. executenonquery ();
74. For(IntI = 0; I <fp2.sheets [0]. rowcount-1; I ++)
76. {
78. If(Fp2.sheets [0]. cells [I, 0]. Text! ="")
80. {
82. Cmd. commandtext ="Insert into [sheet1 $] (employee ID, name, department, position, date, time) values ("+ Fp2.sheets [0]. cells [I, 0]. Text +","+
84. Fp2.sheets [0]. cells [I, 1]. Text +","+ Fp2.sheets [0]. cells [I, 2]. Text +","+ Fp2.sheets [0]. cells [I, 3]. Text +
86. ","+ Fp2.sheets [0]. cells [I, 4]. Text +","+ Fp2.sheets [0]. cells [I, 5]. Text +")";
88. Cmd. executenonquery ();
90. }
92. }
94. Conn. Close ();
96. Return True;
98. }
100. Catch(System. Data. oledb. oledbexception ex)
102. {
104. System. Diagnostics. Debug. writeline ("An error occurred while writing data to excel :"+ Ex. Message );
106. }
108. Return False;
110. }