C # import an excel file and rewrite it

Recently, a project has a page that needs to be imported into an excel file and then updated the data in the database. I never remember how to import or export an excel file, in fact, there is nothing to remember (normally, recording macros are used in excel). The import will soon be completed, so there is no problem with the test, however, a problem occurs after real data is obtained.

Real data such:

 

The number of articles imported into an excel file on the internet is as large as that in the case that the OLEDB query statement is select * from [Sheet1 $], but a real data table won't write a name for you, in addition, the number of workbooks in excel is not fixed, and the import in the program is of course problematic.

Later, I thought that I could retrieve the excel schema information when importing an excel file. However, the excel schema information must contain the number of workbooks and the name of the workbook, the workbook creation date.

 

The final result is as follows:

 

Name of the excel Workbook:

 

The complete code after the change is as follows:

Code

# Region batch modify MSC ID
Void btnBatchMscIdClick (object sender, RoutedEventArgs e)
{
Try
{

Var ofd = new OpenFileDialog ()
{
Filter = "Microsoft Office Excel Workbook (*. xls) | *. xls ",
Multiselect = false
};
If (ofd. ShowDialog () = DialogResult. Cancel) return;

Var ds = new DataSet ("msgIds ");

Var strConn = string. Format ("Provider = Microsoft. Jet. OLEDB.4.0; Data Source = {0}; Extended Properties = Excel 8.0;", ofd. FileName );
Using (var oledbConn = new OleDbConnection (strConn ))
{
OledbConn. Open ();

Var sheetName = oledbConn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, // retrieves excel schema information
New [] {null, "Table "});
Var sheet = new string [sheetName. Rows. Count];
For (int I = 0, j = sheetName. Rows. Count; I <j; I ++)
{
Sheet [I] = sheetName. Rows [I] ["TABLE_NAME"]. ToString ();
}
// You can use a for statement to query each workbook.
Var adapter = new OleDbDataAdapter (string. Format ("select * from [{0}]", sheet [0]), oledbConn );
Adapter. Fill (ds );
}

Int success = 0, lose = 0;
Using (var client = new DataCmClient ("DataTransfer_http "))
{
Foreach (DataTable dt in ds. Tables)
{
Foreach (DataRow dr in dt. Rows)
{
Var msc_name = dr [0]. ToString ();
Var msg_addr = dr [1]. ToString ();
Var isSuccess = client. UpdateMscId (msc_name, msg_addr );
If (isSuccess)
{
Success ++;
}
Else
{
Lose ++;
}
}
}
}
MessageBox. Show (string. Format ("{0} data records updated successfully, {1} data records failed", success, lose ),
"System prompt", MessageBoxButtons. OK, MessageBoxIcon. Question );
}
Catch
{
MessageBox. Show ("an exception occurred during data import. Please operate again ",
"System prompt", MessageBoxButtons. OK, MessageBoxIcon. Warning );
}

}
# Endregion

 

 

By the way, let's explain the GetOleDbSchemaTable method.

Note:SqlClient. SqlConnectionThe object is notGetOleDbSchemaTableEquivalent method.

Use the ole db. NET data providerOleDbConnectionObjectGetOleDbSchemaTableMethod to display the architecture information.GetOleDbSchemaTableReturnsDataTable.
GetOleDbSchemaTableThe first parameter is the architecture parameter, which isOleDbSchemaGuidSpecifies the type of schema information to be returned (such as tables, columns, and primary keys ). The second parameter is an array of restricted objects.DataTableThe Rows returned in the schema are filtered (for example, you can specify the table name, type, owner, and/or schema restrictions ).

 
OleDbSchemaGuid Member
OleDbSchemaGuid parameter specifiedGetOleDbSchemaTableThe type of the schema table to be returned.
OleDbSchemaGuid members include:

•	Column
•	Foreign key
•	Index
•	Primary Key
•	Table
•	View

 

 

Restrictions

The limit is an array of filtered value objects. Each element corresponds to the result.DataTableOneDataColumn.OleDbSchemaGuidParameters determine the corresponding limits. For exampleOleDbSchemaGuidThe limit array is as follows:

{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}

When passing the limit array value, use the Visual C #. NETNullKeyword. For example, if you want to retrieve the structure of a table, useOleDbSchemaGuid. Tables. However, if a table is specified, aliases, synonyms, views, and other related objects are returned. Therefore, if you want to filter out all objects except the TABLE, use TABLE restrictions on TABLE_TYPE. You can use TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAMENullBecause you do not filter these objects:schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] {null, null, null, "TABLE"});

 

Returned data table

EachOleDbSchemaGuidThe object of the type and restriction rule correspondsGetOleDbSchemaTableMethod returnDataTable. Each limit column correspondsDataTableIs followed by a column based onOleDbSchemaGuidOther Architecture Information of the field.
For example, if you use the following codeDataTableEach row of is a database table:
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] {null, null, null, "TABLE"});

 

DataTableEach column returned in is a restricted column (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE) followed by other schema columns TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, and DATE_MODIFIED.

To obtain a list of column names (that is, field descriptors, such as TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME), you can use the column location sequence. Note:ColumnsThe element subscript of the array starts from 0:
for (int i = 0; i < schemaTable.Columns.Count; i++) {
Console.WriteLine(schemaTable.Columns[i].ToString());
}

To obtain the values of each column (that is, the actual table name, such as Categories, MERS MERs, and Employees), you can useItemArray. Note:ItemArrayThe element subscript of the array starts from 0:
for (int i = 0; i < schemaTable.Rows.Count; i++) {
Console.WriteLine(schemaTable.Rows[i].ItemArray[2].ToString());
}

 

I hope this will help you.