POI读取 Excel 系统管理员 2022-02-18 11:47 276阅读 0赞 可解决 整形变浮点型、日期读取变成整型或其他未知类型、 数字变成科学计数法等问题。 依赖: <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> 工具类: import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.commons.io.FilenameUtils; import org.apache.commons.io.IOUtils; import org.apache.commons.lang3.time.DateFormatUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.util.NumberToTextConverter; 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; public class ExcelReadUtil { public static void main(String[] args) { String path = "C:\\Users\\Administrator\\Documents\\tmp_template\\模板.xlsx"; try { List<List<String>> result = new ExcelReadUtil().read(path); System.out.println("总行数:"+result.size()); for (int i = 0; i < result.size(); i++) { List<String> model = result.get(i); System.out.println(model.toString()); } } catch (Exception e) { e.printStackTrace(); } } public static List<List<String>> read(String path) throws Exception{ File file=new File(path); String ext=FilenameUtils.getExtension(file.getName()); if("xls".equalsIgnoreCase(ext)){ return readXls(path); }else if("xlsx".equalsIgnoreCase(ext)){ return readXlsx(path); } return null; } public static List<List<String>> read(String pathDir,String fileName) throws Exception{ File file=new File(pathDir,fileName); return read(file.getAbsolutePath()); } /** * * @Title: readXls * @Description: 处理xls文件 * @param @param path * @param @return * @param @throws Exception 设定文件 * @return List<List<String>> 返回类型 * @throws * */ private static List<List<String>> readXls(String path) throws Exception { InputStream is = new FileInputStream(path); // HSSFWorkbook 标识整个excel HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<List<String>> result = new ArrayList<List<String>>(); int size = hssfWorkbook.getNumberOfSheets(); // 循环每一页,并处理当前循环页 // for (int numSheet = 0; numSheet < size; numSheet++) { //遍历所有的sheet页 for (int numSheet = 0; numSheet < 1; numSheet++) { //numSheet<1,即 只读取第一个sheet页。 // HSSFSheet 标识某一页 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // 处理当前页,循环读取每一行 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { //第1行是标题,要跳过,从第2行开始读,即下标是1。 // HSSFRow表示行 HSSFRow hssfRow = hssfSheet.getRow(rowNum); if(hssfRow==null){ break; } int minColIx = hssfRow.getFirstCellNum(); int maxColIx = hssfRow.getLastCellNum(); List<String> rowList = new ArrayList<String>(); // 遍历改行,获取处理每个cell元素 for (int colIx = minColIx; colIx < maxColIx; colIx++) { // HSSFCell 表示单元格 HSSFCell cell = hssfRow.getCell(colIx); if (cell == null) { rowList.add(""); }else{ rowList.add(getStringVal(cell)); } } result.add(rowList); } } return result; } private static List<List<String>> readXlsx(String path) throws Exception { InputStream is = new FileInputStream(path); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); List<List<String>> result = new ArrayList<List<String>>(); // 循环每一页,并处理当前循环页 if(xssfWorkbook.iterator().hasNext()){ //只读取第一个sheet页 // for (XSSFSheet xssfSheet : xssfWorkbook) { //遍历所有的sheet页 XSSFSheet xssfSheet=xssfWorkbook.iterator().next(); //只读取第一个sheet 页 // if (xssfSheet == null) { // continue; // } // 处理当前页,循环读取每一行 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { //第1行是标题,要跳过,从第2行开始读,即下标是1。 XSSFRow xssfRow = xssfSheet.getRow(rowNum); if(xssfRow==null){ break; } int minColIx = xssfRow.getFirstCellNum(); int maxColIx = xssfRow.getLastCellNum(); List<String> rowList = new ArrayList<String>(); for (int colIx = minColIx; colIx < maxColIx; colIx++) { XSSFCell cell = xssfRow.getCell(colIx); if (cell == null) { rowList.add(""); }else{ //rowList.add(cell.toString()); rowList.add(getStringVal(cell)); } } result.add(rowList); } } return result; } /** * 改造poi默认的toString()方法如下 */ private static String getStringVal(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? "true" : "false"; case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_NUMERIC: String cellvalue = ""; short format = cell.getCellStyle().getDataFormat(); if(format == 14 || format == 31 || format == 57 || format == 58){ //excel中的时间格式 Date date = cell.getDateCellValue(); cellvalue = DateFormatUtils.format(date, "yyyy-MM-dd"); } // 判断当前的cell是否为 Date else if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); cellvalue= formater.format(date); } else { // 如果是纯数字 // 取得当前Cell的数值 cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue()); } return cellvalue; case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); default: return ""; } } }
还没有评论,来说两句吧...