将excel导入到list<list<Obj>>中
工具类
package io.renren.utils; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; /** * description: Excel 数据导入 * @version v1.0 **/ public class ExcelImport { private ExcelImport INSTANSE = new ExcelImport(); /** * excel 2003 suffix */ private static final String EXCEL_XLS_SUFFIX = ".xls"; /** * excel 2007 或以上 suffix */ private static final String EXCEL_XLSX_SUFFIX = ".xlsx"; /** * 分隔符 "." */ public static final String POINT = "."; /** * description: 读取excel数据 * * @param file * @return List<List < Object>> * @version v1.0 */ public static List<List<Object>> importFile(File file) throws Exception { if (file == null) { return null; } if (file.getName().endsWith(EXCEL_XLS_SUFFIX)) { return readXls(new FileInputStream(file)); } if (file.getName().endsWith(EXCEL_XLSX_SUFFIX)) { return readXlsx(new FileInputStream(file)); } throw new RuntimeException("文件不对,必须是excel文件,后缀名以:" + EXCEL_XLS_SUFFIX + " 或者 " + EXCEL_XLSX_SUFFIX); } /** * description: 导入excel --- 支持web * * @return List<List < Object>> * @throws Exception * @version v1.0 */ public static List<List<Object>> importFile(MultipartFile multipartFile) throws Exception { if (multipartFile == null) { return null; } if (multipartFile.getOriginalFilename().endsWith(EXCEL_XLS_SUFFIX)) { return readXls(multipartFile.getInputStream()); } if (multipartFile.getOriginalFilename().endsWith(EXCEL_XLSX_SUFFIX)) { return readXlsx(multipartFile.getInputStream()); } throw new RuntimeException("文件不对,必须是excel文件,后缀名以:" + EXCEL_XLS_SUFFIX + " 或者 " + EXCEL_XLSX_SUFFIX); } /** * description: 读取03版excel * * @return List<List < Object>> * @version v1.0 */ private static List<List<Object>> readXls(InputStream inputStream) throws Exception { List<List<Object>> list = new ArrayList<>(); // 读取excel HSSFWorkbook workbook = new HSSFWorkbook(inputStream); // 获取sheet 页数量 int sheets = workbook.getNumberOfSheets(); for (int num = 0; num < sheets; num++) { HSSFSheet sheet = workbook.getSheetAt(num); if (null == sheet) { continue; } // sheet 页的总行数 int rows = sheet.getLastRowNum(); // startRow 开始读取的行数 --- 第二行开始读 for (int startRow = 1; startRow <= rows; startRow++) { HSSFRow row = sheet.getRow(startRow); List<Object> rowList = new ArrayList<>(); if (null != row) { // row 行中的 单元格总个数 short cells = row.getLastCellNum(); for (int x = 0; x <= cells; x++) { HSSFCell cell = row.getCell(x); if (null == cell) { rowList.add(""); } else { rowList.add(getXlsValue(cell)); } } list.add(rowList); } } } return list; } /** * description: 获取 03 版 excel数据 * * @param cell * @return String * @version v1.0 */ private static String getXlsValue(HSSFCell cell) { if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { String cellValue = ""; if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date); } else { DecimalFormat df = new DecimalFormat("#.##"); cellValue = df.format(cell.getNumericCellValue()); String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length()); if (strArr.equals("00")) { cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT)); } } return cellValue; } else { // 其他类型的值,统一设置为 string // http://blog.csdn.net/ysughw/article/details/9288307 cell.setCellType(Cell.CELL_TYPE_STRING); return String.valueOf(cell.getStringCellValue()); } } /** * description: 读取07或以上版本的 excel * * @return List<List < Object>> * @throws Exception * @version v1.0 */ private static List<List<Object>> readXlsx(InputStream inputStream) throws Exception { List<List<Object>> list = new ArrayList<>(); // 读取excel ,封装到 XSSFWorkbook 对象 XSSFWorkbook workbook = new XSSFWorkbook(inputStream); int sheets = workbook.getNumberOfSheets(); for (int num = 0; num < sheets; num++) { XSSFSheet sheet = workbook.getSheetAt(num); if (null == sheet) { continue; } // 获取sheet页的总行数 int rows = sheet.getLastRowNum(); for (int startRow = 1; startRow <= rows; startRow++) { // startRow 开始读取的行数, 从第二行开始读取 XSSFRow row = sheet.getRow(startRow); List<Object> rowList = new ArrayList<>(); if (null != row) { // 获取行总单元格个数 short cells = row.getLastCellNum(); for (int x = 0; x < cells; x++) { XSSFCell cell = row.getCell(x); if (cell == null) { rowList.add(""); } else { rowList.add(getXlsxValue(cell)); } } list.add(rowList); } } } return list; } /** * description: 获取07或以上版本 excel 数据 * * @param cell * @return Object * @version v1.0 */ private static Object getXlsxValue(XSSFCell cell) { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { String cellValue = ""; if (DateUtil.isCellDateFormatted(cell)) { Date date = DateUtil.getJavaDate(cell.getNumericCellValue()); cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date); } else { DecimalFormat df = new DecimalFormat("#.##"); cellValue = df.format(cell.getNumericCellValue()); String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length()); if (strArr.equals("00")) { cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT)); } } return cellValue; } else { // 其他类型的值,统一设置为 string // http://blog.csdn.net/ysughw/article/details/9288307 //cell.setCellType(Cell.CELL_TYPE_STRING); return String.valueOf(cell.getStringCellValue()); } } }
测试
@PostMapping("/getDataListExcel") @ApiOperation("解析excel") public String getDataListExcel(@RequestBody MultipartFile file) { try { List<List<Object>> lists = ExcelImport.importFile(file); System.out.println(lists); } catch (Exception e) { e.printStackTrace(); } return "success"; }
结果