Import Excel Data to database

Read the data in Excel into the dataset of the Target Structure Based on the configuration file, and process the merged cells.

1. class design: the Excel file must be read into the dataset according to the configuration.

Using system;
Using system. Data;
Using system. collections;
Using system. Data. oledb;

Namespace hkh. Common
{
/// <Summary>
/// Import data from Excel to dataset, which is a base class with virtual functions
/// </Summary>
/// <Remarks> Create by liwt on 2006-09-15
/// </Remarks>
Public class clsimportexcel
{
# Region variable

Protected string m_mappingfile; // ing configuration file path
Protected string m_excelsheetname; // name of the table to be imported in Excel
Protected string m_sqltablename; // The Name Of The SQL table to be imported. It can also be of another type, such as Oracle
Protected arraylist [] m_columnmapping; // column ing configuration list, which includes three parts: 0 -- SQL column name, 1 -- excel column Index
// 2 -- if the current Excel behavior is empty, whether to assign a value to the value of the previous row
Private bool isloadmapping;

# Endregion

# Region Constructor

/// <Summary>
/// No parameter structure
/// </Summary>
Public clsimportexcel ()
{
M_mappingfile = "";
M_excelsheetname = "";
Isloadmapping = false;
M_columnmapping = new arraylist [3];

M_columnmapping [0] = new arraylist ();
M_columnmapping [1] = new arraylist ();
M_columnmapping [2] = new arraylist ();
}

/// <Summary>
/// Overload the constructor
/// </Summary>
/// <Param name = "mappingfilepath"> ing configuration file path </param>
/// <Param name = "excelsheetname"> name of the table to be imported in Excel </param>
Public clsimportexcel (string mappingfilepath, string excelsheetname)
{
M_mappingfile = mappingfilepath;
M_excelsheetname = excelsheetname;
Isloadmapping = false;
M_columnmapping = new arraylist [3];

M_columnmapping [0] = new arraylist ();
M_columnmapping [1] = new arraylist ();
M_columnmapping [2] = new arraylist ();
}

# Endregion

# Region attributes

/// <Summary>
/// Read or set the ing configuration file path
/// </Summary>
Public String mappingfilepath
{
Get
{
Return m_mappingfile;
}
Set
{
M_mappingfile = value;
Isloadmapping = false;
}
}

/// <Summary>
/// Read or set the name of the table to be imported in Excel
/// </Summary>
Public String excelsheetname
{
Get
{
Return m_excelsheetname;
}
Set
{
M_excelsheetname = value;
Isloadmapping = false;
}
}

# Endregion

# Region Public Method

/// <Summary>
/// Import data
/// </Summary>
/// <Param name = "excelfilepath"> path of the Excel file to be imported </param>
/// <Param name = "dstarget"> Target dataset </param>
/// <Returns> true -- succeeded, false -- failed
/// </Returns>
Public bool import (string excelfilepath, ref dataset dstarget)
{
Try
{
If (! Isloadmapping)
{
If (! Loadmapping ())
{
Return false;
}
}

// Use OLE to read Excel Data
Oledbconnection oleconn = new oledbconnection ("provider = Microsoft. Jet. oledb.4.0; extended properties = Excel 8.0; Data Source =" + excelfilepath + ";");
Oledbdataadapter oleda = new oledbdataadapter ("select * from [" + m_excelsheetname + "$]", oleconn );

Dataset dsexcel = new dataset ();
Oleda. Fill (dsexcel, m_excelsheetname );

Oleda. Dispose ();
Oleconn. Dispose ();

// Create a data row cache for filling empty cells
Datarow temprow = dsexcel. Tables [m_excelsheetname]. Rows [0];

For (INT I = 0; I <dsexcel. Tables [m_excelsheetname]. Rows. Count; I ++)
{
Datarow excelrow = dsexcel. Tables [m_excelsheetname]. Rows [I];

// Call the pre-import data processing function and determine the next step based on the returned value.
If (! Importingbefore (ref excelrow ))
{
Continue;
}

Datarow sqlnewrow = dstarget. Tables [0]. newrow ();

For (Int J = 0; j <m_columnmapping [0]. Count; j ++)
{
String sqlcolname = m_columnmapping [0] [J]. tostring ();
Int excelcolindex = (INT) m_columnmapping [1] [J];
Bool inherit = convert. toboolean (m_columnmapping [2] [J]);

// If the current row is empty
If (convert. isdbnull (excelrow [excelcolindex])
{
// If it is allowed to be filled with temporary values
If (inherit)
{
Sqlnewrow [sqlcolname] = temprow [excelcolindex];
}
}
Else
{
// Fill in the data and update the cache row data
Sqlnewrow [sqlcolname] = excelrow [excelcolindex];
Temprow [excelcolindex] = excelrow [excelcolindex];
}

}

// Call the imported data processing method and decide the next step based on the returned value.
If (importingafter (ref sqlnewrow ))
{
Dstarget. Tables [0]. Rows. Add (sqlnewrow );
}
}

Return true;
}
Catch (exception ex)
{
Throw ex;
}
}

# Endregion

# Region protected virtual functions, which must be rewritten by subclass

/// <Summary>
/// Process the Excel row data before import
/// </Summary>
/// <Param name = "drexcelrow"> the current Excel row being read </param>
/// <Returns> true -- continue processing, false -- skip the current row
/// </Returns>
Protected virtual bool importingbefore (ref datarow drexcelrow)
{
Return true;
}

/// <Summary>
/// Process the current row after data is transferred to storage
/// </Summary>
/// <Param name = "drsqlrow"> current SQL row that has been converted to data </param>
/// <Returns> true -- continue processing, false -- skip the current row
/// </Returns>
Protected virtual bool importingafter (ref datarow drsqlrow)
{
Return true;
}

# Endregion

# Region private Method

/// <Summary>
/// Load the configuration file to obtain the ing between tables and columns
/// </Summary>
/// <Returns> </returns>
Private bool loadmapping ()
{
Try
{
// Clear outdated configurations
M_columnmapping [0]. Clear ();
M_columnmapping [1]. Clear ();
M_columnmapping [2]. Clear ();

If (null = m_mappingfile | "" = m_mappingfile)
{
Throw new exception ("configuration file not found ");
}

// Read the configuration file
Dataset dsmaping = new dataset ();
Dsmaping. readxml (m_mappingfile );

If (dsmaping. Tables. Count = 0)
{
Throw new exception ("failed to read the configuration file ");
}

// Read Table ing
Datarow [] tablemap = dsmaping. Tables ["tablemapping"]. Select ("excelsheet = '" + m_excelsheetname + "'");

If (tablemap. length! = 1)
{
Throw new exception ("this sheet does not exist or is configured multiple times ");
}

// Read Column ing
Datarow [] colmap = dsmaping. Tables ["columnmapping"]. Select ("tablemapping_id =" + tablemap [0] ["tablemapping_id"]. tostring ());

If (colmap. Length <= 0)
{
Throw new exception ("column ing Not Configured for this table ");
}

For (INT I = 0; I <colmap. length; I ++)
{
M_columnmapping [0]. Add (colmap [I] ["sqlcol"]);
M_columnmapping [1]. Add (execcolumnindex (colmap [I] ["excelcol"]. tostring ()));
M_columnmapping [2]. Add (colmap [I] ["inherit"]);
}

// Set to loaded configuration
Isloadmapping = true;

Return true;
}
Catch
{
Return false;
}
}

/// <Summary>
/// Calculate the index corresponding to the column title in Excel (a = 0)
/// </Summary>
/// <Param name = "strcolumntitle"> </param>
/// <Returns> </returns>
Private int execcolumnindex (string strcolumntitle)
{
If (null = strcolumntitle | "" = strcolumntitle. Trim ())
Return-1;

String temp = strcolumntitle. Trim (). toupper ();

If (2 = temp. length)
{
// Return temp [0]-65 + 26 + temp [1]-65;
Return temp [0]-104 + temp [1];
}
Else
{
Return temp [0]-65;
}
}

# Endregion
}
}

2. configuration file XSD

3. configuration file example

Excelsheet ---- name of the workbook in the Excel file to import the database

Sqltable --- Name of the database table to be imported

Column headers in EXCELCOL--EXCEL tables

Column name in SQLCOL--SQL Database

Inherit --- whether to continue the preceding cell values when tables are merged in Excel. Here, the cells are used for splitting. This field refers to the merged rows. True indicates splitting. That is, all cells are filled with the merged values, if this parameter is set to false, the first row is filled. Other rows are filled with null values.

<Importconfiguration>
<Tablemapping excelsheet = "sheet1" sqltable = "cnki_illegalipinfo">
<Columnmapping excelcol = "A" sqlcol = "unitname" inherit = "false"/>
<Columnmapping excelcol = "B" sqlcol = "startip" inherit = "false"/>
<Columnmapping excelcol = "C" sqlcol = "endip" inherit = "false"/>
</Tablemapping>
</Importconfiguration>