Purpose
Using this toolkit, You can import MS Excel table data to a relational database table with the corresponding structure. You can use the extended interface to operate the data of each column or row in an Excel table, remove bad data, repair or replace data of a specific unit, this allows you to freely import Excel data into tables with foreign key associations or references. This Toolkit can generate simple XML data files while importing data. Class Structure
Instructions for use
Extension interface com. uncnet. dbexcel. flopper to implement the corresponding method
Method: String conversionop (
Connection connection,
String Arg,
Int index,
Document Doc,
Element table,
Element row)
Throws exception;
Description: converts data of Excel columns and operates database tables.
Parameter: Connection connection, database connection;
String Arg: Data of Excel column units;
Int index, Excel table column number, starting from 1;
Document Doc, the document element of unc xml;
Element table, unc xml element;
Element row, a unc xml element.
Return: data converted from Excel XML to unc xml.
Sample Code:
/**
* @ See COM. uncnet. dbexcel. flopper # conversionop (connection, String, Int, document, element, element)
*/
Public String conversionop (
Connection connection,
String Arg,
Int index,
Document Doc,
Element table,
Element row ){
String text = NULL;
If (ARG = NULL)
Arg = "";
Try {
If (connection = NULL)
Return "no connection ";
Stmt = connection. createstatement ();
If (col_num ++ % columns = 0)
Ps_in = new string [columns + 1]; Switch (INDEX ){
Case 1:
{
TEXT = ARG;
Ps_in [0] = ARG;
Break;
}
Case 8:
{
TEXT = ARG;
Ps_in [8] = findfordict (null, ARG, stmt, 8 );
Break;
}
Case 10:
{
TEXT = ARG;
If (! Arg. Equals (""))
Ps_in [10] = Arg. substring (1 );
Else
Ps_in [10] = ARG;
Break;
}
Default:
{
TEXT = ARG;
Ps_in [Index] = ARG;
}
}
If (col_num % columns = 0)
Istocmt = true;
Else
Istocmt = false;
If (istocmt ){
Pstmt = connection. preparestatement (sqlscript );
Connection. setautocommit (false );
Row_num ++;
Boolean istoexe = true;
Try {
Integer. parseint (ps_in [0]);
Integer. parseint (ps_in [1]);
} Catch (numberformatexception NFE ){
Istoexe = false;
}
Stringtokenizer ster = NULL;
Date = NULL;
Int year, month, day;
For (INT I = 0; I <columns + 1; I ++ ){
If (I = 12 | I = 13 ){
Ster = new stringtokenizer (ps_in [I], "-");
Year = integer. parseint (ster. nexttoken ());
Month = integer. parseint (ster. nexttoken ());
Day = integer. parseint (ster. nexttoken ());
Date = new date (year, month, day );
Pstmt. setdate (I + 1, date );
} Else
Pstmt. setstring (I + 1, ps_in [I]);
System. Err. Print (ps_in [I] + "");
}
System. Err. println ();
If (istoexe ){
Pstmt.exe cute ();
Connection. Commit ();
} Else
System. Err. Print ("encountering null .");
Connection. setautocommit (true );
If (pstmt! = NULL)
Pstmt. Close ();
}
} Catch (exception e ){
E. printstacktrace (system. Err );
} Finally {
Try {
If (stmt! = NULL)
Stmt. Close ();
If (istocmt & pstmt! = NULL)
Pstmt. Close ();
} Catch (exception e ){
System. Err. println (E );
}
}
If (text = NULL ){
Table. removechild (ROW );
} Return text;
} Method: void setparameter (string [] para );
Description: settings.ProgramParameters
Parameter: String [] para Method: Connection getconnection ();
Description: used to establish a database connection.
Back: Database Connection Method: void closeconnection (connection conn );
Description: closes the database connection.
Parameter: Connection Conn, database connection Method: void beginconvert ();
Description: operation before conversion. Method: void endofconvert ();
Description: operation after conversion. Method: void afterconnected (connection conn );
Description: operations performed after the database is connected. Method: void beforeclosed (connection conn );
Description: operation before closing the database connection. Interface flopper extension class
Class: COM. uncnet. dbexcel. flopper. dbflopper
Description: Implements database connection and shutdown and parameter settings. Class: COM. uncnet. dbexcel. flopper. dictflopper
Description: dictionary class, which enables you to find the corresponding data of a dictionary table from a file. program execution
main program entry: COM. uncnet. dbexcel. textcontroller's main method
call parameters: Excel XML file path, output unc xml file path, flopper extension class namespace, and other extension parameters...
parameter example:
F: \ work4 \ 9-23 \ expert. xml f: \ work4 \ 9-23 \ _ expert2.xml COM. uncnet. dbexcel. flopper. ypzbexpertfp oracle. JDBC. driver. oracledriver JDBC: oracle: thin: @ 127.0.0.1: 1521: Oracle Scott tiger F: \ work4 \ 9-23 \ data \ diploma.txt F: \ work4 \ 9-23 \ data \ hospital.txt |
