Go to Asp.net Excel Import/Export

Turn: http://blog.csdn.net/smartyaya/archive/2006/09/29/1305361.aspx

 

1. Import Excel data to the database:

// This method exports data from Excel to dataset. The filepath is the absolute path of the Excel file, and the sheetname is the Excel table;
Public dataset exceldatasource (string filepath, string sheetname)
...{
String strconn;
Strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Oledbdataadapter oada = new oledbdataadapter ("select * from [" + sheetname + "$]", strconn );
Dataset DS = new dataset ();
Oada. Fill (DS );
Return Ds;
} The code above implements writing data in Excel into dataset. After this step is implemented, You can process the obtained dataset as needed. One of the issues to be noted is, you must specify the sheetname, that is, which worksheet in Excel needs to be imported. Otherwise, an error occurs.

2. How to obtain the worksheet (sheetname) from Excel ):

// Obtain all sheetnames in Excel.
Public arraylist excelsheetname (string filepath)
...{
Arraylist Al = new arraylist ();
String strconn;
Strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Conn. open ();
Datatable sheetnames = conn. getoledbschematable
(System. Data. oledb. oledbschemaguid. Tables, new object []... {null, "table "});
Conn. Close ();
Foreach (datarow DR in sheetnames. Rows)
...{
Al. Add (Dr [2]);
}
Return al;
} In these two steps, you can basically import the Excel file into the database. You can obtain the details in practice.

3. Import the data in the database to excel. Here I will describe two methods in my practice:

Method 1: Start with writing the most basic file and write the data read from the database to the Excel file;

// This method imports data into an Excel file. The datatable dt is the data you need to write to the Excel file;
Public void exportexcel (datatable DT, streamwriter W)
...{
Try
...{
For (INT I = 0; I <DT. Columns. Count; I ++)
...{
W. Write (Dt. Columns [I]);
W. Write ('');
}
W. Write ("");

Object [] values = new object [DT. Columns. Count];
Foreach (datarow DR in DT. Rows)
...{
Values = dr. itemarray;
For (INT I = 0; I <DT. Columns. Count; I ++)
...{
W. Write (Values [I]);
W. Write ('');
}
W. Write ("");
}
W. Flush ();
W. Close ();
}
Catch
...{
W. Close ();
}
} Streamwriter W is a self-created stream. When creating this data stream, you need to specify the file path to which the data needs to be written. to download the data, you can simply use response. redirect ("specify the file path to be downloaded ");

Method 2: import data from the DataGrid to excel:

// Filename is the Excel name, And toexcelgrid is the data source, which is the DataGrid data source;
Private void exportexcelfromdatagrid (string filename, system. Web. UI. webcontrols. DataGrid toexcelgrid)
...{
Response. Clear ();
Response. Buffer = true;
Response. charset = "UTF-8 ";
Response. appendheader ("content-disposition", "attachment; filename =" + server. urlencode (filename ));
Response. contentencoding = system. Text. encoding. getencoding ("UTF-8"); // set the output stream to simplified Chinese
Response. contenttype = "application/MS-excel"; // set the output file type to an Excel file.
This. enableviewstate = false;
System. Globalization. cultureinfo mycitrad = new system. Globalization. cultureinfo ("ZH-CN", true );
System. Io. stringwriter ostringwriter = new system. Io. stringwriter (mycitrad );
System. Web. UI. htmltextwriter ohtmltextwriter = new system. Web. UI. htmltextwriter (ostringwriter );
Toexcelgrid. rendercontrol (ohtmltextwriter );
Response. Write (ostringwriter. tostring ());
Response. End ();
} When using this method, you must note that page turning is allowed in the DataGrid data source and sorting is allowed to be set to "false". At the same time, you must set some special rows to be invisible, for example, to edit a row, if this is not the case, use this method to import the data to excel. An exception occurs: "You need to put XXX into a form with runat = server ", I forgot the meaning of XXX. You can try it in practice to know the error.