java 读取 excel 表格内容

一、添加依赖

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.8</version> </dependency>12345678910二、工具类根据文件后缀判断 2003 || 2007 || 2010 格式。

import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;import java.time.Instant;import java.time.LocalDateTime;import java.time.ZoneId;import java.time.format.DateTimeFormatter;import java.util.HashMap;import java.util.Map;

public class ExcelUtils {

private static Workbook wb; private static Sheet sheet; private static Row row;

private static final String EXCEL_XLS = "xls";private static final String EXCEL_XLSX = "xlsx";

/** * 读取表头 * * @param inputStream inputStream * @param suffix file suffix * @return map <index,value> */public static Map<Integer, String> readExcelTitle(InputStream inputStream, String suffix) { getWorkbook(inputStream, suffix); sheet = wb.getSheetAt(0); row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); Map<Integer, String> map = new HashMap<>(); for (int i = 0; i < colNum; i++) { map.put(i, row.getCell(i).getStringCellValue()); } return map;}

/** * 读取excel内容 * * @param inputStream 文件 * @return Map<行, Map < 下标, Object>> */public static Map<Integer, Map<Integer, String>> readExcelContent(InputStream inputStream, String suffix) { getWorkbook(inputStream, suffix); Map<Integer, Map<Integer, String>> content = new HashMap<>(); sheet = wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { row = sheet.getRow(i); int j = 0; Map<Integer, String> cellValue = new HashMap<>(); while (j < colNum) { String obj = getCellFormatValue(row.getCell(j)); cellValue.put(j, obj); j++; } content.put(i, cellValue); } return content;}

private static String getCellFormatValue(Cell cell) { String cellValue = ""; if (cell != null) { // 判断当前Cell的Type switch (cell.getCellType()) { // 如果当前Cell的Type为NUMERIC case Cell.CELL_TYPE_NUMERIC: case Cell.CELL_TYPE_FORMULA: { // 判断当前的cell是否为Date if (DateUtil.isCellDateFormatted(cell)) { DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm:ss"); Instant instant = cell.getDateCellValue().toInstant(); ZoneId zoneId = ZoneId.systemDefault(); LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, zoneId); cellValue = dateTimeFormatter.format(localDateTime); } else { // 如果是纯数字 // 取得当前Cell的数值 cellValue = String.valueOf(cell.getNumericCellValue()); } break; } // 如果当前Cell的Type为STRING case Cell.CELL_TYPE_STRING: // 取得当前的Cell字符串 cellValue = cell.getRichStringCellValue().getString(); break; default: // 默认的Cell值 cellValue = ""; } } return cellValue;}

private static void getWorkbook(InputStream inputStream, String suffix) {

try { //2003 if (EXCEL_XLS.equals(suffix)) { wb = new HSSFWorkbook(inputStream); //2007/2010 } else if (EXCEL_XLSX.equals(suffix)) { wb = new XSSFWorkbook(inputStream); } } catch (Exception e) { e.printStackTrace(); } finally { try { inputStream.close(); } catch (Exception e) { e.printStackTrace(); } } }

}--------------------- 作者：Break all 来源： 原文：https://blog.csdn.net/weixin_40467684/article/details/91883896 版权声明：本文为博主原创文章，转载请附上博文链接！

转载于:https://www.cnblogs.com/douyinlianmeng/p/11203873.html

相关资源：java 读取excel表格 内容、图片、图片自动上传、图片上传后回调上传地址，并赋值到图片表格位置