importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.text.DecimalFormat;importorg.apache.commons.logging.Log;importorg.apache.commons.logging.LogFactory;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.junit.Test;public classTestExcel {//记录类的输出信息
static Log log = LogFactory.getLog(TestExcel.class);//获取Excel文档的路径
public static String filePath = TestExcel.class.getResource("/test.xls").getPath();
@Testpublic static voidmain(String[] args) {try{//创建对Excel工作簿文件的引用
HSSFWorkbook wookbook = new HSSFWorkbook(newFileInputStream(
filePath));//在Excel文档中,第一张工作表的缺省索引是0//其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
HSSFSheet sheet = wookbook.getSheet("Sheet1");//获取到Excel文件中的所有行数
int rows =sheet.getPhysicalNumberOfRows();//遍历行,索引从0 开始,第0行可用作表头,不获取。
for (int i = 1; i < rows; i++) {//读取左上端单元格
HSSFRow row =sheet.getRow(i);//行不为空
if (row != null) {//获取到Excel文件中的所有的列//int cells = row.getPhysicalNumberOfCells();
String value = "";//遍历列//防止当使用POI处理excel的时候cell.getNumbericCellValue()//当长度大一点的时候会变成科学计数法形式。
DecimalFormat df = new DecimalFormat("0");//***下方3为cells的值,更换为固定列数,解决空值问题***
for (int j = 0; j < 5; j++) {//获取到列的值
HSSFCell cell =row.getCell(j);if (cell != null) {switch(cell.getCellType()) {caseHSSFCell.CELL_TYPE_FORMULA:break;caseHSSFCell.CELL_TYPE_NUMERIC:
value+=df.format(cell.getNumericCellValue())+ ",";break;caseHSSFCell.CELL_TYPE_STRING:
value+= cell.getStringCellValue() + ",";break;default:
value+= "#"+",";break;
}
}else{
value+= "#" + ",";
}
}//将数据插入到mysql数据库中
String[] val = value.split(",");
System.out.println(val[0]);
System.out.println(val[1]);
System.out.println(val[2]);
System.out.println(val[3]);
System.out.println(val[4]);
System.out.println(" ");//*******在此处保存数据到数据库******
}
}
}catch(FileNotFoundException e) {
e.printStackTrace();
}catch(IOException e) {
e.printStackTrace();
}
}
}