Sourceforge.net



ExcelParseToDB This application parses excel file which contains many columns (eg 20 to 30 columns ) into database, from a pointed input directory.Jar files used in this project.SL#JAR FILE NAMESOURCECOMMENTS1ibatis 2.3.4.726Used as data maping from excel to database2log4j 1.2.8For logging3javax.mail 1.4To send email4ojdbc14 10.2.0.5To connect to oracle database5jxl 2.6 parsing excelFor more understanding of Ibatis setup , please check JXL , please refer setup for the projectTable name: payslip_FILEIn this table it contains the name of the files to be processedIf the processed field records the status as E then it’s a error and the file was not processed If the processed field records the status as C then the parsing was successfulIf the processed field records the status as N then the file is pending to be processedCOLUMN_NAMEDATA_TYPECOMMENTSCONSTRAINTSIDNUMBER?PRIMARY KEYDATE_CREATEDTIMESTAMP(6)??FILE_NAMEVARCHAR2?NAME OF THE FILELAST_UPDATEDTIMESTAMP(6)??LOCATION_IDNUMBER?SOME FORIGN KEYS YOU CAN IGNOREPERIOD_IDNUMBER?SOME FORIGN KEYS YOU CAN IGNOREPROCESSEDVARCHAR2?THIS FIELD CONTAINS VALUES N, C and EDB_FILE_NAMEVARCHAR2?ACTUAL FILE NAME ON THE DISK BY DEFAULT IT WILL BE SAME AS THE FILE_NAMECOMMENTSVARCHAR2?IF THE PROCESSING FAILS , THIS FIELD RECORDS THE JAVA EXCEPTIONUPLOADED_USERVARCHAR2?USER WHO UPLOADED THE FILE????Table name: payslipThis table contains content of the file into databaseCOLUMN_NAMEDATA_TYPECOMMENTSACTUALL COLUMN ON THE EXCEL FILEIDNUMBERPRIMARY KEYN/AUSER_INITIALVARCHAR2?InitialBASE_SALARYVARCHAR2?Base SalaryBASE_SALARY_ADJVARCHAR2?Base Salary AdjBASE_SALARY_ADJ_DESIGN_BONUSVARCHAR2?Base Salary Adj - Design bonusTH13_MONTH_SALARYVARCHAR2?13th month salary SALES_COMMISSIONVARCHAR2?Sales CommissionFAE_COMMISSIONVARCHAR2?FAE CommissionSERVICE_YEAR_AWARDVARCHAR2?Service Year AwardRETENTION_BONUSVARCHAR2?Retention bonusPTO_CASH_OUTVARCHAR2?PTO cash outKEBPVARCHAR2?KEBPDESIGN_BONUSVARCHAR2?Design bonusOTHER_BONUS_ESPP_RELATEDVARCHAR2?Other Bonus (ESPP Related)REFERRAL_BONUSVARCHAR2?Referral bonusHIRE_BONUSVARCHAR2?Hire bonusAUTHOR_INCENTIVE_PATENT_AWARDVARCHAR2?Author incentive/Patent awardRELO_EXP_GROSS_UPVARCHAR2?Relo. Exp. - gross upPPSPPSPVARCHAR2?PPS/PPSPOTHER_BONUS_WITHOUT_ESPPVARCHAR2?Other Bonus (without ESPP)TRAVEL_ALLOW_HIGH_TEMP_ALLOWVARCHAR2?Travel& Meal Allowance & High Temp AllowanceCAR_ALLOWANCEVARCHAR2?Car AllowanceGROSS_PAYMENTVARCHAR2?Gross PaymentIIT_DEDUCT_ADD_HOUSING_FUNDVARCHAR2?IIT Deductible Additional Housing Fund 10%HOLD_INCOME_TAX_OF_SALVARCHAR2?Withhold Income Tax of SalaryHOLD_INCOME_TAX_OF_STOCK_OPTVARCHAR2?Withhold Income Tax of Stock OptionHOLD_INCOME_TAX_OF_CASH_DIVIDVARCHAR2?Withhold Income Tax of Cash Dividend HOLD_TAX_OF_13TH_MONTH_SALARYVARCHAR2?Withhold Income Tax of 13th Month SalaryETRADE_STOCK_TAX_RETUREVARCHAR2?E-trade Stock Tax RetureSINGLE_CHILD_FEEVARCHAR2?Single Child FeeNONTAXABLE_LIVING_ALLOWANCEVARCHAR2?Non-taxable Living AllowanceEXPATRIATES_SALARY_PAID_IN_USVARCHAR2?Expatriates Salary paid in USESPPVARCHAR2?ESPPOTHER_RECEIVABLE_VARCHAR2?Other Receivable WITHHOLD_PAYROLL_PAYMENTVARCHAR2?Withhold payroll paymentNET_PAYTIMESTAMP(6)?Net PayPTO_BALANCETIMESTAMP(6)?PTO BalanceSample excel file as a input to the processingInitial Base Salary Base Salary Adj Base Salary Adj - Design bonus 13th month salary Sales CommissionFAE CommissionService Year AwardRetention bonusPTO cash outKEBPDesign bonusOther Bonus (ESPP Related)Referral bonusHire bonusAuthor incentive/Patent awardRelo. Exp. - gross upPPS/PPSPOther Bonus (without ESPP)Travel& Meal Allowance & High Temp AllowanceCar AllowanceGross PaymentIIT Deductible Additional Housing Fund 10%Withhold Income Tax of SalaryWithhold Income Tax of Stock OptionWithhold Income Tax of Cash Dividend Withhold Income Tax of 13th Month SalaryE-trade Stock Tax RetureSingle Child FeeNon-taxable Living AllowanceExpatriates Salary paid in USESPPOther Receivable Withhold payroll paymentNet PayPTO Balanceemp1 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0 daysemp2 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0 daysJava files in the projectClass name payslipfilepackage com.yourcompany.excelprocess;public class payslipfile {int ID;int VERSION;String FILE_NAME;int LOCATION_ID;int PERIOD_ID;String PROCESSED;String DB_FILE_NAME;String COMMENTS;String UPLOADED_USER; public int getID() {return ID;}public void setID(int id) {ID = id;}public int getVERSION() {return VERSION;}public void setVERSION(int version) {VERSION = version;}public String getFILE_NAME() {return FILE_NAME;}public void setFILE_NAME(String file_name) {FILE_NAME = file_name;}public int getLOCATION_ID() {return LOCATION_ID;}public void setLOCATION_ID(int location_id) {LOCATION_ID = location_id;}public int getPERIOD_ID() {return PERIOD_ID;}public void setPERIOD_ID(int period_id) {PERIOD_ID = period_id;}public String getPROCESSED() {return PROCESSED;}public void setPROCESSED(String processed) {PROCESSED = processed;}public String getDB_FILE_NAME() {return DB_FILE_NAME;}public void setDB_FILE_NAME(String db_file_name) {DB_FILE_NAME = db_file_name;}public String getCOMMENTS() {return COMMENTS;}public void setCOMMENTS(String comments) {COMMENTS = comments;}public String getUPLOADED_USER() {return UPLOADED_USER;}public void setUPLOADED_USER(String uploaded_user) {UPLOADED_USER = uploaded_user;}This class file is the value object file for the details_file table in the database as mentioned in the section Java class : ReadExcelThis file is from process the file , i have added main method which calls the read methodpackage com.yourcompany.excelprocess;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.sql.*;import java.util.List;import java.util.HashMap;import java.util.Iterator;import java.util.Properties;import org.apache.log4j.Category;import jxl.Cell;import jxl.CellType;import jxl.Sheet;import jxl.Workbook;import jxl.WorkbookSettings;import jxl.read.biff.BiffException;import com.ibatis.sqlmap.client.SqlMapClient;import com.yourcompany.excelprocess.utils.*;public class ReadExcel {private static java.util.ArrayList listPendAccount;private String inputFile;public static Properties excelColumns;private static Category logger = Category.getInstance(ReadExcel.class.getName());private static Util util;private static SqlMapClient sqlMapper; public void setInputFile(String inputFile) { this.inputFile = inputFile; } public void read(int fileid) throws IOException,Exception { File inputWorkbook = new File(inputFile); Workbook w; HashMap excelRow; try { WorkbookSettings settings = new WorkbookSettings(); settings.setEncoding("Cp1252"); w = Workbook.getWorkbook(inputWorkbook,settings); // Get the first sheet Sheet sheet = w.getSheet(0); // Loop over first 10 column and lines for (int i = 0; i < sheet.getRows(); i++) { excelRow = new HashMap(); for (int j = 0; j < sheet.getColumns(); j++) { Cell cell = sheet.getCell(j, i); //CellType type = cell.getType(); Integer invar = new Integer(j+1); System.out.println("column name " + excelColumns.getProperty(invar.toString()) + " = " + cell.getContents()); excelRow.put(excelColumns.getProperty(invar.toString()), cell.getContents()); } try { excelRow.put("PAYSLIP_ID_FILE", fileid); sqlMapper.insert("insertPayslip", excelRow); } catch(Exception e){ throw e; } } } catch (BiffException e) { throw e; } } /** * @param args */public static void main(String[] args) throws IOException {// TODO Auto-generated method stubReadExcel test = new ReadExcel();String input_dir_path=null;String output_dir_path=null;excelColumns = new Properties();try { logger.debug(" processing started - reading properties files ");excelColumns = load("c:/excelcolumns.props"); // you can get the path to props file from the args[]util = new Util();sqlMapper = util.getSqlMapClient();input_dir_path=excelColumns.getProperty("input_directory"); //"C:/INPUT_DIR";output_dir_path=excelColumns.getProperty("output_directory"); //"C:/OUTPUT_DIR";List list = sqlMapper.queryForList("SelectpendingFiles", null);Iterator itr = list.iterator(); while(itr.hasNext()){ com.yourcompany.excelprocess.Payslipfile file = (com.yourcompany.excelprocess.Payslipfile) itr.next(); System.out.println(input_dir_path + "/" + file.DB_FILE_NAME); logger.debug(input_dir_path + "/" + file.DB_FILE_NAME); file.setCOMMENTS(" FILE PROCESSED SUCCESSFULLY "); file.setPROCESSED("C"); // todo: SendHTMLEmail.sendMail(); try { test.setInputFile(input_dir_path + "/" + file.DB_FILE_NAME ); test.read(file.getID()); // copy the successfully processed excel file to output directory moveFileToOutput(file.DB_FILE_NAME); }catch(Exception e) { // record the error into the DATABASE // TODO Send email to the user who uploaded the file logger.debug("Cannot process the file : REASON :" + e.toString() ); file.setCOMMENTS("Cannot process the file : REASON :" + e.toString() ); file.setPROCESSED("E"); } sqlMapper.update("Update_Success_Imports", file); logger.debug("Update_Success_Imports"); file = null; } }catch(Exception e){e.printStackTrace();}}// reads properties filepublic static Properties load(String path) throws IOException {File fis1= new File(path);Properties props = new Properties();FileInputStream fis = new FileInputStream(fis1);props.load(fis);fis.close();return props;}// moves file from input directory to output directorypublic static void moveFileToOutput(String file_name) throws IOException,Exception {boolean flag_file_move=false;File afile =new File(excelColumns.getProperty("input_directory") + "/" + file_name);try {flag_file_move = afile.renameTo(new File(excelColumns.getProperty("output_directory") + "/" + file_name));}catch(Exception e){ System.out.println("File is failed to move!" + e); throw e;}} }Java class name : Util.javapackage com.yourcompany.excelprocess.utils;import java.io.Reader;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.Statement;import com.mon.resources.Resources;import com.ibatis.sqlmap.client.SqlMapClient;import com.ibatis.sqlmap.client.SqlMapClientBuilder;import java.io.*;import oracle.jdbc.*;import oracle.jdbc.pool.*;public class Util { Statement st; File file = null; FileReader freader = null; public SqlMapClient getSqlMapClient() throws Exception{ Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml"); SqlMapClient sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader); reader.close(); return sqlMapper; }}IBATIS Setup for the projectFile name SqlMapConfig.xml<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE sqlMapConfig PUBLIC "-//ibatis./DTD SQL Map Config 2.0//EN" ""><sqlMapConfig> <!-- Configure a built-in transaction manager. If you're using an app server, you probably want to use its transaction manager and a managed datasource --> <transactionManager type="JDBC" commitRequired="false"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/> <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@dbtnsNames:1521:sid"/> <property name="JDBC.Username" value="nsv"/> <property name="JDBC.Password" value="nsv"/> </dataSource> </transactionManager> <!-- List the SQL Map XML files. They can be loaded from the classpath, as they are here (com.domain.data...) --> <sqlMap resource="excel_parse.xml"/></sqlMapConfig>Filename : excel_parse<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//ibatis./DTD SQL Map 2.0//EN" ""> <sqlMap namespace="chinaOnlinePayslip"> <!-- Select with no parameters using the result map for territoryId class. --> <resultMap id="select-file-list" class="com.yourcompany.excelprocess.Payslipfile"><result property ="ID" column ="ID" /><result property ="VERSION" column ="VERSION" /> <result property ="FILE_NAME" column ="FILE_NAME" /><result property ="LOCATION_ID" column ="LOCATION_ID" /><result property ="PERIOD_ID" column ="PERIOD_ID" /><result property ="PROCESSED" column ="PROCESSED" /><result property ="DB_FILE_NAME" column ="DB_FILE_NAME" /><result property ="COMMENTS" column ="COMMENTS" /><result property ="UPLOADED_USER" column ="UPLOADED_USER" /></resultMap> <select id="SelectpendingFiles" resultMap="select-file-list">SELECT ID, VERSION, FILE_NAME, LOCATION_ID, PERIOD_ID, PROCESSED, DB_FILE_NAME,COMMENTS,UPLOADED_USER FROM PAYSLIP_FILE where processed='N' </select> <update id="Update_Success_Imports" parameterClass="com.yourcompany.excelprocess.Payslipfile" > update PAYSLIP_FILE set processed=#PROCESSED# , COMMENTS=#COMMENTS# WHERE Id=#ID# </update><insert id="insertPayslip" parameterClass="java.util.HashMap">insert into payslip (USER_INITIAL,BASE_SALARY,BASE_SALARY_ADJ,BASE_SALARY_ADJ_DESIGN_BONUS,TH13_MONTH_SALARY,SALES_COMMISSION,FAE_COMMISSION,SERVICE_YEAR_AWARD,RETENTION_BONUS,PTO_CASH_OUT,KEBP,DESIGN_BONUS,OTHER_BONUS_ESPP_RELATED,REFERRAL_BONUS,HIRE_BONUS,AUTHOR_INCENTIVE_PATENT_AWARD,RELO_EXP_GROSS_UP,PPSPPSP,OTHER_BONUS_WITHOUT_ESPP,TRAVEL_ALLOW_HIGH_TEMP_ALLOW,CAR_ALLOWANCE,GROSS_PAYMENT,IIT_DEDUCT_ADD_HOUSING_FUND,HOLD_INCOME_TAX_OF_SAL,HOLD_INCOME_TAX_OF_STOCK_OPT,HOLD_INCOME_TAX_OF_CASH_DIVID,HOLD_TAX_OF_13TH_MONTH_SALARY,ETRADE_STOCK_TAX_RETURE,SINGLE_CHILD_FEE,NONTAXABLE_LIVING_ALLOWANCE,EXPATRIATES_SALARY_PAID_IN_US,ESPP,OTHER_RECEIVABLE_,WITHHOLD_PAYROLL_PAYMENT,NET_PAY,PTO_BALANCE,id,PAYSLIP_ID_FILE)values(#USER_INITIAL#,#BASE_SALARY#,#BASE_SALARY_ADJ#,#BASE_SALARY_ADJ_DESIGN_BONUS#,#TH13_MONTH_SALARY#,#SALES_COMMISSION#,#FAE_COMMISSION#,#SERVICE_YEAR_AWARD#,#RETENTION_BONUS#,#PTO_CASH_OUT#,#KEBP#,#DESIGN_BONUS#,#OTHER_BONUS_ESPP_RELATED#,#REFERRAL_BONUS#,#HIRE_BONUS#,#AUTHOR_INCENTIVE_PATENT_AWARD#,#RELO_EXP_GROSS_UP#,#PPSPPSP#,#OTHER_BONUS_WITHOUT_ESPP#,#TRAVEL_ALLOW_HIGH_TEMP_ALLOW#,#CAR_ALLOWANCE#,#GROSS_PAYMENT#,#IIT_DEDUCT_ADD_HOUSING_FUND#,#HOLD_INCOME_TAX_OF_SAL#,#HOLD_INCOME_TAX_OF_STOCK_OPT#,#HOLD_INCOME_TAX_OF_CASH_DIVID#,#HOLD_TAX_OF_13TH_MONTH_SALARY#,#ETRADE_STOCK_TAX_RETURE#,#SINGLE_CHILD_FEE#,#NONTAXABLE_LIVING_ALLOWANCE#,#EXPATRIATES_SALARY_PAID_IN_US#,#ESPP#,#OTHER_RECEIVABLE_#,#WITHHOLD_PAYROLL_PAYMENT#,#NET_PAY#,#PTO_BALANCE#,PAYSLIP_ID.nextval,#PAYSLIP_ID_FILE#)</insert></sqlMap>Pom.xml<?xml version="1.0" encoding="UTF-8"?> <project> <modelVersion>4.0.0</modelVersion> <groupId>ExcelParseToDB</groupId> <artifactId>ExcelParseToDB</artifactId> <version>0.0.1</version><dependencies> <dependency> <groupId>com.ibatis</groupId> <artifactId>ibatis</artifactId> <version>2.3.4.726</version></dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.8</version></dependency><dependency> <groupId>javax.mail</groupId> <artifactId>mail</artifactId> <version>1.4</version></dependency> <dependency><groupId>com.oracle.jdbc</groupId><artifactId>ojdbc14</artifactId><version>10.2.0.5</version> </dependency> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6</version> </dependency> </dependencies></project> ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download