Java SpringMVC项目导入excel2003以及2007多版本自动识别对应工具类(util)
来源:互联网 发布:php 判断不为小数 编辑:程序博客网 时间:2024/05/17 12:57
导入excel2003以及2007多版本自动识别对应工具类(util)
想出了一个工具类,希望对大家有用,导入excel生成对应的行的集合(list),集合里是对应列的Map。原生基于apache的poi类
Ps:之间还遇到了时间和电话号码转型问题,不知道为什么excel明明列设置了文本,但导入来的时候还是识别了数字类型,然后改良了代码,识别时间日期和数字类型返回字符串。
1.首先是导入Excel2003以前(包括2003)的版本,扩展名是.xls 的
-
-
-
-
-
-
-
-
- public static List<Map<String,String>> readExcelByXls(MultipartFile templetFile, int startrow, int startcol, int sheetnum) {
- List<Map<String,String>> varList = new ArrayList<Map<String,String>>();
-
- try {
-
- HSSFWorkbook wb = new HSSFWorkbook(templetFile.getInputStream());
- HSSFSheet sheet = wb.getSheetAt(sheetnum);
- int rowNum = sheet.getLastRowNum() + 1;
-
- for (int i = startrow; i < rowNum; i++) {
-
- Map<String,String> varpd = new HashMap<String,String>();
- HSSFRow row = sheet.getRow(i);
- int cellNum = row.getLastCellNum();
-
- for (int j = startcol; j < cellNum; j++) {
-
- HSSFCell cell = row.getCell(Integer.parseInt(j + ""));
- String cellValue = null;
- if (null != cell) {
- switch (cell.getCellType()) {
- case 0:
- if(HSSFDateUtil.isCellDateFormatted(cell)){
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- cellValue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
- } else {
- cell.setCellType(1);
- cellValue = cell.getStringCellValue();
- }
- break;
- case 1:
- cellValue = cell.getStringCellValue();
- break;
- case 2:
-
-
-
- cellValue = String.valueOf(cell.getDateCellValue());
- break;
- case 3:
- cellValue = "";
- break;
- case 4:
- cellValue = String.valueOf(cell.getBooleanCellValue());
- break;
- case 5:
- cellValue = String.valueOf(cell.getErrorCellValue());
- break;
- }
- } else {
- cellValue = "";
- }
-
- varpd.put("var"+j, cellValue);
-
- }
- varList.add(varpd);
- }
-
- } catch (Exception e) {
- System.out.println(e);
- }
-
- return varList;
- }
2.是操作Excel2007的版本,扩展名是.xlsx的
-
-
-
-
-
-
-
-
- public static List<Map<String,String>> readExcelByXlsx(MultipartFile templetFile, int startrow, int startcol, int sheetnum) {
- List<Map<String,String>> varList = new ArrayList<Map<String,String>>();
-
- try {
-
- XSSFWorkbook wb = new XSSFWorkbook(templetFile.getInputStream());
- XSSFSheet sheet = wb.getSheetAt(sheetnum);
- int rowNum = sheet.getLastRowNum() + 1;
-
- for (int i = startrow; i < rowNum; i++) {
-
- Map<String,String> varpd = new HashMap<String,String>();
- XSSFRow row = sheet.getRow(i);
- int cellNum = row.getLastCellNum();
-
- for (int j = startcol; j < cellNum; j++) {
-
- XSSFCell cell = row.getCell(Integer.parseInt(j + ""));
- String cellValue = null;
- if (null != cell) {
- switch (cell.getCellType()) {
- case 0:
- if(HSSFDateUtil.isCellDateFormatted(cell)){
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- cellValue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
- } else {
- cell.setCellType(1);
- cellValue = cell.getStringCellValue();
- }
- break;
- case 1:
- cellValue = cell.getStringCellValue();
- break;
- case 2:
- cellValue = cell.getStringCellValue();
-
-
- break;
- case 3:
- cellValue = "";
- break;
- case 4:
- cellValue = String.valueOf(cell.getBooleanCellValue());
- break;
- case 5:
- cellValue = String.valueOf(cell.getErrorCellValue());
- break;
- }
- } else {
- cellValue = "";
- }
-
- varpd.put("var"+j, cellValue);
-
- }
- varList.add(varpd);
- }
-
- } catch (Exception e) {
- System.out.println(e);
- }
-
- return varList;
- }
3.重点来了,结合前两个代码使用,自动识别excel版本文件
- public static List<Map<String,String>> readExcel(MultipartFile templetFile, int startrow, int startcol, int sheetnum){
- List<Map<String,String>> varList = new ArrayList<Map<String,String>>();
- if(templetFile!=null&&templetFile.getSize()>0){
- String ofn=templetFile.getOriginalFilename();
- String extName = "";
- if (ofn.lastIndexOf(".") >= 0){
- extName = ofn.substring(ofn.lastIndexOf("."));
- }
- if(".xls".equals(extName.toLowerCase())){
- varList=readExcelByXls(templetFile,startrow,startcol,sheetnum);
- }else if(".xlsx".equals(extName.toLowerCase())){
- varList=readExcelByXlsx(templetFile,startrow,startcol,sheetnum);
- }
- }
- return varList;
- }