. Net-Excel import-OleDB (note: the solution for the header to be in the second line/not in the first line),-excel-oledb

. Net-Excel import-OleDB (note: the solution for the header to be in the second line/not in the first line),-excel-oledb

Advantage: Excel is used as a data source for processing, and content is directly read through SQL, which is faster to read.

Disadvantage: The data reading method is not flexible enough to directly read a cell. Only after the entire Sheet page is read (the result is Datatable) in the Datatable, obtain the specified value based on the number of rows and columns.

When the Excel data volume is large. Memory usage is very high. If the memory is insufficient, an exception of memory overflow will be thrown.

Aspx code:

<Asp: FileUpload ID = "FileUpload2" runat = "server" Width = "265px" Style = "float: left; height: 25px;"/>
<Asp: Button ID = "Button2" runat = "server" Text = "Stage 1: recommended responses and Evaluation recommendation tables-Import" CssClass = "pl"
Style = "margin: 10px 10px; width: 250px; float: left" onclick = "Button2_Click"/>

Cs code:

Protected void Button2_Click (object sender, EventArgs e)
{
If (! FileUpload2.PostedFile. FileName. Trim (). ToLower (). EndsWith (". xls "))
{
This. ClientScript. RegisterStartupScript (this. GetType (), "", "alert('can only upload excelfile (.xls). ');", true );
Return;
}
String strFullName = this. FileUpload2.PostedFile. FileName. ToString ();
// Upload the file to the fixed path on the server first
String FileName = FileUpload2.PostedFile. FileName;
String FilePathName = "";
String strFileName = "";
If (FileName! = "")
{
String dateStr = DateTime. Now. ToString ("yyyyMMddhhmmssms ");
String filename = Path. GetFileNameWithoutExtension (FileName );
String oldfilekz = Path. GetExtension (FileName );
FilePathName = Server. MapPath ("~ ") +" File \ "+ filename + dateStr + oldfilekz;
FileUpload2.PostedFile. SaveAs (FilePathName );
StrFileName = Server. MapPath ("~ ") +" File \ "+ filename + dateStr + oldfilekz;
}
// Use Excel as the database to import data
String strCon = "Provider = Microsoft. Jet. OleDb.4.0 ;"
+ "Data source =" + strFileName + ";"
+ "Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1 '";

ArrayList list = new ArrayList ();
Int updatecount = 0;
OleDbConnection con = new OleDbConnection (strCon );
OleDbDataAdapter da = new OleDbDataAdapter ("select * from [Sheet0 $]", con );
DataSet ds = new DataSet ();
Da. Fill (ds, "excel ");

DataTable dt = new DataTable ();
Dt = GetExcelTable (ds. Tables ["excel"]);
// String aa = ds. Tables ["excel"]. Rows [3] [1]. ToString ();
If (dt. Rows. Count> 0)
{
For (int I = 0; I <dt. Rows. Count; I ++)
{
Updatecount + = 1;
StringBuilder SQL = new StringBuilder ();
String TOPICID = dt. Rows [I] ["Serial Number"]. ToString ();
String STATE = (dt. Rows [I] ["accepted"]. ToString () = "yes ")? "5": "5-1 ";
SQL. append ("update bbs_topic set ZN_DEPT = '" + ds. tables ["excel"]. rows [I] ["Reply to department"]. toString () + "', DFR ='" + dt. rows [I] [""]. toString () + "', ZXJY ='" + dt. rows [I] ["is a special suggestion"]. toString () + "', STATE ='" + STATE + "', DFYJ ='" + dt. rows [I] ["replies"]. toString () + "', TA_YX ='" + dt. rows [I] ["recommended for excellent recommendations"]. toString () + "', TJJX ='" + dt. rows [I] ["recommendation Award"]. toString () + "'where TOPICID = '" + TOPICID + "'");
List. Add (SQL );
}
If (list. Count! = 0)
{
If (DbCommon. SqlTranList (list )){
This. ClientScript. RegisterStartupScript (this. GetType (), "", "alert ('information imported successfully! '); ", True );
}
}
}
Else
{
This. ClientScript. RegisterStartupScript (this. GetType (), "", "alert ('the imported file has no records! '); ", True );
Return;
}
}

 

/// <Summary>
/// Remove unnecessary header rows
/// </Summary>
/// <Param name = "table"> </param>
/// <Returns> </returns>

Public DataTable GetExcelTable (DataTable table)
{
Foreach (DataRow row in table. Rows)
{
If (! (Row [0]. ToString (). Equals ("Serial Number "))
{
Row. Delete ();
}
Else
{
Break;
}
}
Table. AcceptChanges ();
For (int I = 0; I <table. Columns. Count; I ++)
{
Table. Columns [I]. ColumnName = table. Rows [0] [I]. ToString ();
}
Table. Rows [0]. Delete ();
Table. AcceptChanges ();
// Table = table. GetChanges (DataRowState. Unchanged );
Return table;
}