首先导入poi的jar包:
java代码:
`public class ImportToSql {
public static void main(String[] args) throws Exception {
System.out.println("-------------");
// readXml("d:/test2.xls");
String result = readXml("C:\\Users\\czp\\Desktop\\topic\\hebing1.xlsx");
write(result,"");
System.out.println("写入完毕");
}
public static String readXml(String fileName){
String resultStr="name,alternateName,catagrory,subCatagrory,description,END"+"\r\n";
boolean isE2007 = false; //判断是否是excel2007格式
if(fileName.endsWith("xlsx"))
isE2007 = true;
try {
InputStream input = new FileInputStream(fileName); //建立输入流
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开始
if(row.getRowNum()>0){
resultStr+="END"+ "\r\n";
}
Iterator<Cell> cells = row.cellIterator(); //获得第一行的迭代器
while (cells.hasNext()) {
Cell cell = cells.next();
System.out.println("Cell #" + cell.getColumnIndex());
switch (cell.getCellType()) { //根据cell中的类型来输出数据
case HSSFCell.CELL_TYPE_NUMERIC:
resultStr+=""+",";
System.out.println(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
String str="";
str=cell.getStringCellValue();
str=str.replaceAll(",",","); //把英文的逗号换成中文的
str=str.replaceAll("\"","\"\"");//把双引号换为两个双引号,输出时默认为一个 "\"","\"\""
str=str.replaceAll(System.getProperty("line.separator"),"##");//把换行符换为##
str=str.replaceAll("\r|\n|\t", "");
resultStr+=str+",";
System.out.println(str);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
resultStr+=""+",";
System.out.println("unsuported sell type");
break;
}
}
}
} catch (IOException ex) {
ex.printStackTrace();
}
return resultStr+="END"+ "\r\n";
}
private static void write(String result, String toFile) throws Exception {
OutputStream out = null;
try {
File f=new File("C:\\Users\\czp\\Desktop\\topic\\1.csv");
if(f.exists()){
f.delete();
}
out = new FileOutputStream(f);
}
catch (Exception e) {
e.printStackTrace();
}
out.write(result.toString().getBytes("gbk"));
out.flush();
out.close();
}
}`