juvenfan

USE `database`;

/*Table structure for table `post` */

DROP TABLE IF EXISTS `post`;

CREATE TABLE `post` (
  `no` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列号',
  `attach` varchar(16) DEFAULT NULL COMMENT '隶属关系',
  `areaNo` varchar(8) DEFAULT NULL COMMENT '地区编码',
  `area` varchar(16) DEFAULT NULL COMMENT '地区',
  `unitNo` varchar(8) DEFAULT NULL COMMENT '部门编码',
  `unit` varchar(64) DEFAULT NULL COMMENT '部门',
  `positionNo` varchar(8) DEFAULT NULL COMMENT '职位代码',
  `position` varchar(64) DEFAULT NULL COMMENT '职位',
  `positionDesc` varchar(1024) DEFAULT NULL COMMENT '职位简介',
  `positionType` varchar(2) DEFAULT NULL COMMENT '职位类别',
  `ratio` int(11) DEFAULT NULL COMMENT '开考比例',
  `number` int(11) DEFAULT NULL COMMENT '人数',
  `education` varchar(16) DEFAULT NULL COMMENT '学历',
  `major` varchar(256) DEFAULT NULL COMMENT '专业',
  `others` varchar(1024) DEFAULT NULL COMMENT '其他',
  `city` varchar(16) DEFAULT NULL COMMENT '城市/垂直部门',
  PRIMARY KEY (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

package com.jsgwy.xls;

import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ParseXls
{
    private static FileOutputStream out;
    private static OutputStreamWriter outWriter;
    private static BufferedWriter bufWrite;
    
    public static void main(String[] args) {
        try {
            out = new FileOutputStream("d:/post.sql");
            outWriter = new OutputStreamWriter(out, "UTF-8");
            bufWrite = new BufferedWriter(outWriter);
            
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/test.xls"));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            for (int i = 0; i < 17; i++) {
                HSSFSheet sheet = wb.getSheetAt(i);
                dealSheet(sheet);
            }
            bufWrite.close();  
            outWriter.close();  
            out.close(); 
            wb.close();
            fs.close();
            System.out.println("done!");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    private static void dealSheet(HSSFSheet sheet) {
        String sheetName  = sheet.getSheetName();
        int    rowCounter = sheet.getLastRowNum();
        for (int i = 3; i < rowCounter; i++) {
            HSSFRow row = sheet.getRow(i);
            int cellCounter = row.getLastCellNum();
            StringBuilder sb = new StringBuilder();
            String tablePrefix  = "INSERT INTO `post`("
                    + "`attach`,`areaNo`,`area`,`unitNo`,`unit`,"
                    + "`positionNo`,`position`,`positionDesc`,`positionType`,"
                    + "`ratio`,`number`,`education`,`major`,`others`,`city`) VALUES ('";
            String attach       = row.getCell(0).getStringCellValue().trim();
            String areaNo       = row.getCell(1).getStringCellValue().trim();
            String area         = row.getCell(2).getStringCellValue().trim();
            String unitNo       = row.getCell(3).getStringCellValue().trim();
            String unit         = row.getCell(4).getStringCellValue().trim();
            String positionNo   = row.getCell(5).getStringCellValue().trim();
            String position     = row.getCell(6).getStringCellValue().trim();
            String positionDesc = row.getCell(7).getStringCellValue().trim();
            String positionType = row.getCell(8).getStringCellValue().trim();
            int    ratio        = (int)(row.getCell(9).getNumericCellValue());
            int    number       = (int)(row.getCell(10).getNumericCellValue());
            String education    = row.getCell(11).getStringCellValue().trim();
            String major        = row.getCell(12).getStringCellValue().trim();
            String others       = "";
            if (cellCounter > 13) {
                others = row.getCell(13).getStringCellValue().trim();
            }
            others = others.replaceAll("；", "<br/>");
            others = others.replaceAll("，", "<br/>");
            String city = sheetName;
            sb.append(tablePrefix).append(attach)
              .append("','").append(areaNo)
              .append("','").append(area)
              .append("','").append(unitNo)
              .append("','").append(unit)
              .append("','").append(positionNo)
              .append("','").append(position)
              .append("','").append(positionDesc)
              .append("','").append(positionType)
              .append("',").append(ratio)
              .append(",").append(number)
              .append(",'").append(education)
              .append("','").append(major)
              .append("','").append(others)
              .append("','").append(city)
              .append("');");
            try {
                bufWrite.write(sb.toString() + "\r\n");  
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}