1 packagecom.cn.util;2
3 importjava.io.FileNotFoundException;4 importjava.io.IOException;5 importjava.io.InputStream;6 importjava.util.ArrayList;7 importjava.util.List;8
9 importorg.apache.log4j.Logger;10 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;11 importorg.apache.poi.ss.usermodel.Cell;12 importorg.apache.poi.ss.usermodel.Row;13 importorg.apache.poi.ss.usermodel.Sheet;14 importorg.apache.poi.ss.usermodel.Workbook;15 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;16 importorg.springframework.web.multipart.MultipartFile;17 /**
18 * excel读写工具类19 *@authorsun.kai20 * 2016年8月21日21 */
22 public classPOIUtil {23 private static Logger logger = Logger.getLogger(POIUtil.class);24 private final static String xls = "xls";25 private final static String xlsx = "xlsx";26
27 /**
28 * 读入excel文件,解析后返回29 *@paramfile30 *@throwsIOException31 */
32 public static List readExcel(MultipartFile file) throwsIOException{33 //检查文件
34 checkFile(file);35 //获得Workbook工作薄对象
36 Workbook workbook =getWorkBook(file);37 //创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
38 List list = new ArrayList();39 if(workbook != null){40 for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){41 //获得当前sheet工作表
42 Sheet sheet =workbook.getSheetAt(sheetNum);43 if(sheet == null){44 continue;45 }46 //获得当前sheet的开始行
47 int firstRowNum =sheet.getFirstRowNum();48 //获得当前sheet的结束行
49 int lastRowNum =sheet.getLastRowNum();50 //循环除了第一行的所有行
51 for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){52 //获得当前行
53 Row row =sheet.getRow(rowNum);54 if(row == null){55 continue;56 }57 //获得当前行的开始列
58 int firstCellNum =row.getFirstCellNum();59 //获得当前行的列数
60 int lastCellNum =row.getPhysicalNumberOfCells();61 String[] cells = newString[row.getPhysicalNumberOfCells()];62 //循环当前行
63 for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){64 Cell cell =row.getCell(cellNum);65 cells[cellNum] =getCellValue(cell);66 }67 list.add(cells);68 }69 }70 workbook.close();71 }72 returnlist;73 }74 public static void checkFile(MultipartFile file) throwsIOException{75 //判断文件是否存在
76 if(null ==file){77 logger.error("文件不存在!");78 throw new FileNotFoundException("文件不存在!");79 }80 //获得文件名
81 String fileName =file.getOriginalFilename();82 //判断文件是否是excel文件
83 if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){84 logger.error(fileName + "不是excel文件");85 throw new IOException(fileName + "不是excel文件");86 }87 }88 public staticWorkbook getWorkBook(MultipartFile file) {89 //获得文件名
90 String fileName =file.getOriginalFilename();91 //创建Workbook工作薄对象,表示整个excel
92 Workbook workbook = null;93 try{94 //获取excel文件的io流
95 InputStream is =file.getInputStream();96 //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
97 if(fileName.endsWith(xls)){98 //2003
99 workbook = newHSSFWorkbook(is);100 }else if(fileName.endsWith(xlsx)){101 //2007
102 workbook = newXSSFWorkbook(is);103 }104 } catch(IOException e) {105 logger.info(e.getMessage());106 }107 returnworkbook;108 }109 public staticString getCellValue(Cell cell){110 String cellValue = "";111 if(cell == null){112 returncellValue;113 }114 //把数字当成String来读,避免出现1读成1.0的情况
115 if(cell.getCellType() ==Cell.CELL_TYPE_NUMERIC){116 cell.setCellType(Cell.CELL_TYPE_STRING);117 }118 //判断数据的类型
119 switch(cell.getCellType()){120 case Cell.CELL_TYPE_NUMERIC: //数字
121 cellValue =String.valueOf(cell.getNumericCellValue());122 break;123 case Cell.CELL_TYPE_STRING: //字符串
124 cellValue =String.valueOf(cell.getStringCellValue());125 break;126 case Cell.CELL_TYPE_BOOLEAN: //Boolean
127 cellValue =String.valueOf(cell.getBooleanCellValue());128 break;129 case Cell.CELL_TYPE_FORMULA: //公式
130 cellValue =String.valueOf(cell.getCellFormula());131 break;132 case Cell.CELL_TYPE_BLANK: //空值
133 cellValue = "";134 break;135 case Cell.CELL_TYPE_ERROR: //故障
136 cellValue = "非法字符";137 break;138 default:139 cellValue = "未知类型";140 break;141 }142 returncellValue;143 }144 }