Asp.net Excel Import & Export

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 above code writes data in Excel into dataset. After this step is implemented, You can process the obtained dataset as needed. One of the issues to be aware of 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;
}
Through 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 );

# Region sets numbers in text format to avoid the loss of 0 when long numbers are converted to scientific notation.
String strstyle = "<style> TD {MSO-number-format: '\\@' ;}</style> ";

Ostringwriter. writeline (strstyle );
# Endregion

Response. Write (ostringwriter. tostring ());
Response. End ();
}

When using this method, you must note that page turning is allowed in the data source of the DataGrid, and the allowed sorting is set to "false". At the same time, you need to 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.

3. Notes When importing Excel Data in. net

When importing data from Excel to a database, the most common method is to use provider = Microsoft. jet. oledb.4.0; Data Source = D: "demo.xls; extended properties = 'excel 8.0; HDR = yes; IMEX = 1 '. However, when a column contains both numbers and texts, some data cannot be read, sometimes text data cannot be read, or numeric data cannot be read. Why? In the past, oledb intelligently determined the column type of Excel Based on the data of the first eight rows. When the number of rows of text data> the number of rows of numeric data, the column type is set to text, otherwise it is a number. So what should I do if I want to read all the data? The secret to this question is that IMEX = 1 reads data in import mode.