packagecom.jsgwy.xls;importjava.io.BufferedWriter;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStreamWriter;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;public classParseXls

{private staticFileOutputStream out;private staticOutputStreamWriter outWriter;private staticBufferedWriter bufWrite;public static voidmain(String[] args) {try{

out= new FileOutputStream("d:/post.sql");

outWriter= new OutputStreamWriter(out, "UTF-8");

bufWrite= newBufferedWriter(outWriter);

POIFSFileSystem fs= new POIFSFileSystem(new FileInputStream("d:/test.xls"));

HSSFWorkbook wb= newHSSFWorkbook(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 voiddealSheet(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= newStringBuilder();

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("；", "
");

others= others.replaceAll("，", "
");

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();

}

}

}

}