C # batch upload data in Excel

The following figure shows how to upload data to the database in batches in Excel.

I. UI Layer

1. Front-end code:

<A> Upload File (Excel): <asp: fileupload id = "fileupload1" runat = "server" width = "237px"/> </a>
<A>
<Asp: button id = "btnup" runat = "server" text = "Import Database" width = "75px"
Onclick = "btnup_click"/> </a>

2. Background code

Protected void btnup_click (Object sender, eventargs E)
{
// BLL creation instantiation
Scoremanagerbll. importscoremanager importsocre= new importscoremanager ();
String path; // defines the PATH variable.

// Hasfile is used to check whether a specified file exists in fileupload.
If (this. fileupload1.postedfile = NULL | this. fileupload1.postedfile. filename = "")
{
Response. Write ("<SCRIPT> alert ('select an Excel file') </SCRIPT> ");
Return;
}
// System. Io. Path. getextension get the file extension
String fileext = system. Io. Path. getextension (fileupload1.filename). tostring (). tolower ();
If (fileext = ". xls" | fileext = ". XLSX ")
{
Int Len = This. fileupload1.filename. tostring (). Trim (). length;
// Obtain the uploaded virtual path ".." as the previous directory
Path = "../tempplace/" + this. fileupload1.filename. tostring (). Trim ();
Path = server. mappath (PATH );
This. fileupload1.saveas (PATH );
// Call The bll Layer Method
Importsocre. imaportoptional (PATH );
}
Else
{
Response. Write ("<SCRIPT> alert ('only Excel files' can be selected) </SCRIPT> ");
Return; // If the selected file is not an Excel file
}
// Delete this Excel file after uploading
If (system. Io. file. exists (PATH ))
{
System. Io. file. Delete (PATH );
}
Else
{
Response. Write ("this Excel does not exist! ");
}

}

Ii. BLL layer (this layer encapsulates specific practices and can be called multiple times)

1. BLL external open operations:

Public String imaportoptional (string strpath)
{
// Create an instance of the Dal Layer
Optionalcoursescoredao optionalsdao = new optionalcoursescoredao ();
Datatable dt = new datatable ();
// Call a method in a specific operation class and return a datatable
Dt = isbll. writeexceltodatabase (strpath );
// Call the DLL Layer Method
Optionalsdao. exceltodatabase (DT );
Return NULL;
}
2. Specific operations (encapsulated in a small class)

Public datatable writeexceltodatabase (string strpath)
{
Datatable dt = new datatable ();
Oledbconnection objconn = new system. data. oledb. oledbconnection ("provider = Microsoft. ace. oledb.12.0; Data Source = "+ strpath +"; extended properties = 'excel 12.0 XML; HDR = yes '");
Objconn. open ();

// Obtain data in sheet1
Try
{
Datatable schematable = objconn. getoledbschematable (system. Data. oledb. oledbschemaguid. Tables, null );
String sheetname = string. empty;
// Only make the next loop one time, and obtain only the table in sheet1
For (Int J = 0; j <1; j ++)
{
Sheetname = schematable. Rows [J] [2]. tostring (). Trim (); // obtain the table name in Excel. The default value is sheet1.
Dt = exceltodatatable (strpath, sheetname, true );

}
}
Catch (sqlexception ex)
{
Throw ex;
}
Finally
{
Objconn. Close (); // close the connection
Objconn. Dispose (); // release the connection
}
Return DT;

}

One method that this class must call is:

Public static datatable exceltodatatable (string servermappathexcel, string strsheetname, bool istitleordataoffirstrow)
{

String HDR = string. Empty; // if the first line is data rather than the title, enter: "HDR = no ;"
If (istitleordataoffirstrow)
{
HDR = "yes"; // The first line is the title.
}
Else
{
HDR = "no"; // The first line is data.
}
// Source Definition
String strconn = "provider = Microsoft. Ace. oledb.12.0; Data Source =" + servermappathexcel + "; extended properties = 'excel 12.0 XML; HDR = Yes '";
// SQL statement
// String strexcel = string. Format ("select * from [{0} $]", strsheetname); this is a method
String strexcel = "select * from [" + strsheetname + "]";
// Define the stored data table
Dataset DS = new dataset ();
// Connect to the data source
Using (oledbconnection conn = new oledbconnection (strconn ))
{
Try
{Conn. open ();
// Adapt to the data source
Oledbdataadapter adapter = new oledbdataadapter (strexcel, strconn );
Adapter. Fill (DS, strsheetname );
}
Catch (system. Data. sqlclient. sqlexception ex)
{
Throw ex;
}
Finally
{
Conn. Close ();
Conn. Dispose ();
}
}
Return Ds. Tables [strsheetname];
}

Iii. Dal:

Public bool exceltodatabase (datatable DT)
{

// Call the batch import method in sqlhelp. If yes, return 0. If no, return 1.

If (sqlhelper. batchinsertdata ("graduationproject", DT, 0, 1) = 0)
{
Return true;
}
Else
{
Return false;
}

}

Iv. Methods in sqlhelp:

''' <Summary>
''' Batch insert data
''' </Summary>
''' <Param name = "stablename"> data table name </param>
''' <Param name = "DT"> data to be inserted </param>
''' <Param name = "isoursfieldoffset"> offset of the source data column </param>
''' <Param name = "sdestinationfieldoffset"> column offset in the database </param>
''' <Returns> </returns>
''' <Remarks> created on: 2011-8-5 13:50:05 by sxy </remarks>
Public overloads function batchinsertdata (byval stablename as string, byval dT as datatable, byval isoursfieldoffset as integer, byval sdestinationfieldoffset as integer) as integer
Me. initializeconnection () 'initialize the connection
'Batch import data to sqlserver and create instances
Dim sqlbulk as system. Data. sqlclient. sqlbulkcopy = new system. Data. sqlclient. sqlbulkcopy (m_objconnection)
'Target database table name
Sqlbulk. destinationtablename = stablename
'Map the dataset field index to the database Field Index
For I = 0 to DT. Columns. Count-1
Sqlbulk. columnmappings. Add (isoursfieldoffset + I, sdestinationfieldoffset + I)
Next

'Import
Sqlbulk. writetoserver (DT)

Sqlbulk. Close ()
Me. finalizeconnection () 'Close the connection (related to the way the connection object is maintained)

Return 0

End Function

This method is used to import data into excel in batches. One drawback is that only data in sheet1 can be read, but the reusability of the method can be considered.

 

 

Method 2: To avoid this disadvantage, you can combine the BLL layer two methods:

Public datatable writeexceltodatabase (string strpath)
{
Datatable dt = new datatable ();
Oledbconnection objconn = new system. data. oledb. oledbconnection ("provider = Microsoft. ace. oledb.12.0; Data Source = "+ strpath +"; extended properties = 'excel 12.0 XML; HDR = yes '");
Objconn. open ();

// Obtain data in sheet1
Try
{
Datatable schematable = objconn. getoledbschematable (system. Data. oledb. oledbschemaguid. Tables, null );
String sheetname = string. empty;
// Write the data of each sheet table to the database in sequence.

For (Int J = 0; j <1; j ++)
{
Sheetname = schematable. Rows [J] [2]. tostring (). Trim (); // obtain the table name in Excel. The default value is sheet1.
Dt = exceltodatatable (strpath, sheetname, true );

If (exceldatatable. Columns. Count> 1)
{

Edao. exceltodatabase (exceldatatable );
}

 

}
}
Catch (sqlexception ex)
{
Throw ex;
}
Finally
{
Objconn. Close (); // close the connection
Objconn. Dispose (); // release the connection
}
Return DT;

}

One method that this class must call is:

Public static datatable exceltodatatable (string servermappathexcel, string strsheetname, bool istitleordataoffirstrow)
{

String HDR = string. Empty; // if the first line is data rather than the title, enter: "HDR = no ;"
If (istitleordataoffirstrow)
{
HDR = "yes"; // The first line is the title.
}
Else
{
HDR = "no"; // The first line is data.
}
// Source Definition
String strconn = "provider = Microsoft. Ace. oledb.12.0; Data Source =" + servermappathexcel + "; extended properties = 'excel 12.0 XML; HDR = Yes '";
// SQL statement
// String strexcel = string. Format ("select * from [{0} $]", strsheetname); this is a method
String strexcel = "select * from [" + strsheetname + "]";
// Define the stored data table
Dataset DS = new dataset ();
// Connect to the data source
Using (oledbconnection conn = new oledbconnection (strconn ))
{
Try
{Conn. open ();
// Adapt to the data source
Oledbdataadapter adapter = new oledbdataadapter (strexcel, strconn );
Adapter. Fill (DS, strsheetname );
}
Catch (system. Data. sqlclient. sqlexception ex)
{
Throw ex;
}
Finally
{
Conn. Close ();
Conn. Dispose ();
}
}
Return Ds. Tables [strsheetname];
}

This is the batch import of Excel files I have studied. It is a little urgent to write (the notes are not good enough). If you do not understand it, leave a message. Let's discuss it together .......