Excel读取

package com.calm.erp.electronicContract;


import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class Test {


public static void main(String[] args) {
try {
readXml("D:/work/201703/ERP待装机商户明细.xlsx");
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

public static void readXml(String fileName) throws ParseException{  
        boolean isE2007 = false;    //判断是否是excel2007格式 
        String commercialSql = "INSERT INTO erp_work_commercial(commercial_id,commercial_name,work_id,is_start_install,is_end_install) VALUES ";
        String agreementSql = "INSERT INTO erp_service_agreement (work_id,agreement_no,response_person,phone,contract_type,idcard_no,card_type,parta_name,brand_address,contract_time,tel,email)\n";
        String sql = "UPDATE erp_commercial_relation c SET c.install_status = 2 AND c.real_inst_time = ";
        if(fileName.endsWith("xlsx"))  
            isE2007 = true;  
        
        File file = new File("D:/work/201702/1.txt");
        FileWriter fw = null;
        BufferedWriter writer = null;
        try {  
            InputStream input = new FileInputStream(fileName);  //建立输入流  
            fw = new FileWriter(file);
            writer = new BufferedWriter(fw);
            Workbook wb  = null;  
            //根据文件格式(2003或者2007)来初始化  
            if(isE2007)  
                wb = new XSSFWorkbook(input);  
            else  
                wb = new HSSFWorkbook(input);  
            
            Sheet sheet = wb.getSheetAt(0);     //获得第一个表单  
            Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器  
            while (rows.hasNext()) {  
                Row row = rows.next();  //获得行数据  
//                System.out.println("Row #" + row.getRowNum());  //获得行号从0开始  
                Iterator<Cell> cells = row.cellIterator();    //获得第一行的迭代器 
                String commercialId = "";
                String commercialName = "";
                String workId = "";
                String agNo = "";
                String responsePerson = "";
                String phone = "";
                String idcardNo = "";
                String partaName = "";
                String brandAddress = "";
                String contractTime = "";
                String tel = "";//固定电话
                String email = "";
                String installTime = "";
                while (cells.hasNext()) {  
                    Cell cell = cells.next();
//                    System.out.print("Cell #" + cell.getColumnIndex());
                    cell.setCellType(Cell.CELL_TYPE_STRING);
//                    System.out.print( cell.getStringCellValue());
                    switch (cell.getColumnIndex()) {
case 4:
commercialId = cell.getStringCellValue();
break;
case 5:
commercialName = cell.getStringCellValue();
break;
case 6:
System.out.println(cell.getStringCellValue());
installTime = cell.getStringCellValue();
break;
case 8:
// contractTime = cell.getStringCellValue();
System.out.println(cell.getStringCellValue());
// contractTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(cell.getStringCellValue()));
// contractTime = cell.getStringCellValue();
break;
case 9:
workId = cell.getStringCellValue();
break;
case 10:
agNo = cell.getStringCellValue();
break;
case 13:
responsePerson = cell.getStringCellValue();
break;
case 14:
phone = cell.getStringCellValue();
break;
case 16:
idcardNo = cell.getStringCellValue();
break;
case 17:
tel = cell.getStringCellValue();
break;
case 18:
email = cell.getStringCellValue();
break;
case 19:
brandAddress = cell.getStringCellValue();
break;
case 12:
partaName = cell.getStringCellValue();
break;
default:
break;
}
                }
                
                String sql1 = commercialSql + "(\"" + commercialId + "\",\"" + commercialName + "\",\"" + workId + "\",1,1);";
                String sql2 = agreementSql + "VALUES (\"" + workId + "\",\"" + agNo + "\",\"" + responsePerson + "\",\"" + phone + "\",1,\"" + idcardNo + "\",1,\"" + partaName + "\",\"" + brandAddress + "\",\"" + contractTime + "\",\"" + tel + "\",\"" + email +"\");";
                String sql3 = sql + "\"" +installTime + "\" WHERE c.commercial_id = \"" + commercialId + "\";";
                writer.write(sql3);
                writer.newLine();//换行
                writer.flush();
            }  
        } catch (IOException ex) {  
            ex.printStackTrace();  
        } finally {
        try {
writer.close();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
        }
    }  
}