技术:apache.poi
maven依赖如下:
org.apache.poi
poi
3.17
org.apache.poi
poi-ooxml
3.17
源代码:
public static List getExcel() throwsFileNotFoundException, IOException {
File excelFile= new File("E:\\officeproject\\qysc\\test.xls");
HSSFWorkbook wb= new HSSFWorkbook(newFileInputStream(excelFile));
HSSFSheet sheet= wb.getSheetAt(0);
List> listAll = new ArrayList>();for(Row row : sheet) {
List list = new ArrayList();for(Cell cell : row) {switch(cell.getCellType()) {case Cell.CELL_TYPE_STRING://字符串
String categoryName =cell.getRichStringCellValue().getString();
list.add(categoryName);
System.out.print(" ");break;case Cell.CELL_TYPE_NUMERIC://数值与日期
String axis =Double.toString(cell.getNumericCellValue());
list.add(axis);
System.out.print(" ");break;default:
}
}
listAll.add(list);
System.out.println();
}
System.out.println(listAll);
System.out.println(listAll.size());returnlistAll;
}
遇到的问题:
Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
解决:关于excel不同版本读取有对应的工具类,上述例子是对于后缀为 .xls 的excel表格。要是表格不多,就打开excel换一下后缀名就ok了
使用excel打开表格,文件- 另存为:
后缀选择:
保存即可
补充2:关于手机的,在导入时,获取了cell后,axis=Double.toString(cell.getNumericCellValue());这样来读取value时。例如手机号是:13111112222,读取到的可能就是1.3111112222E10,解决措施:
参考:https://blog.csdn.net/maxu12345/article/details/47977811
DecimalFormat df = new DecimalFormat("#");
String axis= df.format(cell.getNumericCellValue());
补充3:关于百分数:2.5%
String axis =Double.toString(cell.getNumericCellValue());
NumberFormat nf=NumberFormat.getPercentInstance();
nf.setMaximumFractionDigits(1);//这个1的意识是保存结果到小数点后几位,但是特别声明:这个结果已经先*100了。String telNum= df.format(cell.getNumericCellValue());
关于不同excel版本文件读取,请参考:https://blog.csdn.net/it_wangxiangpan/article/details/42778167 (未测试,概不负责)