读去excel文件内容写入数据库

思想

读取excel 文件中的内容 ,拼接字符串，将数据插入oracle数据库临时表中,再对临时表中的数据进行操作

package com.cici.testExcel;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

 
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
/**
 * @param sourcefile
 * @return
 */
public class ReadExcel {

    public static void main(String[] args) { 
     
        /* System.out.println("Please insert the pageNO :");
            int pageNo = SystemIn.readInt();

            System.out.println("Please insert the lineMount :");
            int lineMount = SystemIn.readInt();

            System.out.println("Please insert the listMount :");
            int listMount = SystemIn.readInt();*/

            //List list = readExcel("E:\\F\\UtilJars\\TABLE NAMES.xlsx","AR_STATISTIC_EMPTYPE", 4, 7, 4);
            List list = readExcel("E:\\F\\UtilJars\\TABLE NAMES.xlsx","TEST", 5, 52, 2);
            for (int i = 0; i < list.size(); ++i)
              System.out.println(list.get(i));
    }
    /**
     * ReadData from Excel txt form
     * 
     * @param sourcefile the source file path
     * @param sheetPageNo the sheet no of target content 
     * @param lineNo the line no of target content 
     * @param listNo the list no of target content
     * @return
     */
    
    public static List<StringBuffer> readExcel(String sourcefile,String tableName,int pageNo,int lineMount,int listMount ) {
         XSSFWorkbook xwb = null;
            try {
              xwb = new XSSFWorkbook(new FileInputStream(sourcefile));
            } catch (IOException e) {
              e.printStackTrace();
            }

            List lists = new ArrayList();

            for (int i = 0; i < lineMount; ++i) {
              XSSFSheet xSheet = xwb.getSheetAt(pageNo);
              XSSFRow row = xSheet.getRow(i);
              StringBuffer s1 = new StringBuffer("INSERT INTO "+tableName+"  VALUES(  '");
              for (int j = 0; j < listMount; ++j)
              {
                row.getCell(j).setCellType(1);
                String msgj = row.getCell(j).getStringCellValue();

                if ((msgj != null)  && (j != listMount - 1))
                  s1 = s1.append(msgj.trim() + "','");
                else
                  s1 = s1.append(msgj.trim() + "');");
              }

              lists.add(s1);
            }

            return lists;
    }
 

}

 

拼接如下字符串(insert 语句)

INSERT INTO TEST VALUES(  'CPNY_ID','EMPID');
INSERT INTO TEST VALUES(  'PERSON_ID','CHINESE_PINYIN');
INSERT INTO TEST VALUES(  'EMPID','ENGLISH_NAME');
INSERT INTO TEST VALUES(  'LOCAL_NAME','KOREAN_NAME');
INSERT INTO TEST VALUES(  'CHINESE_PINYIN','POSITION_NO');
INSERT INTO TEST VALUES(  'ENGLISH_NAME','DUTY_NO');
INSERT INTO TEST VALUES(  'KOREAN_NAME','POST_NO');
INSERT INTO TEST VALUES(  'STEP_CHANGE_DATE','POST_GRADE_NO');
INSERT INTO TEST VALUES(  'EMP_OFFICE','PAY_STEP');
INSERT INTO TEST VALUES(  'OLD_PERSON_ID','STATUS_CODE');

登录数据库

比较两个字段的差集1 (字段A有的数据 但字段B没有的数据)

SELECT A FROM TEST MINUS
SELECT B FROM TEST;

 

显示结果

1    AGENT_POST_GRADE_ID
2    DUTY_ID
3    OLD_POST_GRADE_ID
4    POSITION_ID
5    POST_GRADE_ID
6    POST_ID
7   

比较两个字段的差集2 (字段B有的数据 但字段A没有的数据)

SELECT B FROM TEST MINUS
SELECT A FROM TEST ;

 

显示结果

1    ACTIVITY
2    AGENT_POST_GRADE_NO
3    CREATED_BY
4    CREATE_DATE
5    DUTY_NO
6    JOIN_BLOC_DATE
7    OLD_POST_GRADE_NO
8    ORDERNO
9    POSITION_NO
10    POST_GRADE_NO
11    POST_NO
12    SETTLEMENT_DATE
13    STAT_NO
14    UPDATED_BY
15    UPDATE_DATE