C # Operate Excel files (read and write Excel files)

I have been asking questions about reading and importing Excel files on the forum. In my spare time, I will summarize what I know about Excel operations. Now I want to share my experience with you and hope to help you.
In addition, we also need to pay attention to some simple issues 1. the Excel file can only store 65535 rows of data. If your data is larger than 65535 rows, you need to cut and store the Excel file. 2. garbled characters.

1. Loading Excel (reading Excel content) the returned value is a dataset

// Load the Excel public static dataset loaddatafromexcel (string filepath) {try {string strconn; strconn = "provider = Microsoft. jet. oledb.4.0; Data Source = "+ filepath +"; extended properties = 'excel 8.0; HDR = false; IMEX = 1' "; oledbconnection oleconn = new oledbconnection (strconn); oleconn. open (); string SQL = "select * from [sheet1 $]"; // but change the sheet name, such as sheet2, oledbdataadapter oledaexcel = new oledbdataadapte R (SQL, oleconn); dataset oledsexcle = new dataset (); oledaexcel. fill (oledsexcle, "sheet1"); oleconn. close (); Return oledsexcle;} catch (exception ERR) {MessageBox. show ("Data Binding to excel failed! Cause of failure: "+ err. message," prompt message ", messageboxbuttons. OK, messageboxicon. Information); return NULL ;}}

2. Write Excel content, number of partitions: exceltable is a table to be imported into Excel

Public static bool savedatatabletoexcel (system. data. datatable exceltable, string filepath) {Microsoft. office. interOP. excel. application APP = new Microsoft. office. interOP. excel. applicationclass (); try {app. visible = false; workbook wbook = app. workbooks. add (true); worksheet wsheet = wbook. worksheets [1] As worksheet; If (exceltable. rows. count> 0) {int ROW = 0; ROW = exceltable. rows. count; int col = Exceltable. columns. count; For (INT I = 0; I <row; I ++) {for (Int J = 0; j <Col; j ++) {string STR = exceltable. rows [I] [J]. tostring (); wsheet. cells [I + 2, J + 1] = STR ;}} int size = exceltable. columns. count; For (INT I = 0; I <size; I ++) {wsheet. cells [1, 1 + I] = exceltable. columns [I]. columnname;} // you can specify whether to enable the pop-up dialog box for saving and overwriting. displayalerts = false; app. alertbeforeoverwriting = false; // Save the job Book wbook. save (); // Save the Excel file app. save (filepath); app. saveworkspace (filepath); app. quit (); APP = NULL; return true;} catch (exception ERR) {MessageBox. show ("Excel export error! Error cause: "+ err. message," prompt message ", messageboxbuttons. OK, messageboxicon. Information); Return false ;}finally {}}

Reprinted friends must indicate the source. Thank you! Http://blog.csdn.net/gisfarmer/

C # Operate Excel files (read and write Excel files)