登录
一个解析Excel2007的POI工具类
2025年5月19日 00:00
通过apache-poi解析读取excel2007表格中的文字和图片，数字按照字符形式读取，表格中的图片和文字都按照行和列顺序读取到二维数组中相应的位置上。
package com.util;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ooxml.POIXMLDocumentPart;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.*;import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;import java.io.ByteArrayInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;public class POIUtil { /** * 读入excel2007文件 * * @param file * @throws IOException */ public static List<String[]> readExcel(String fileName, byte[] bytes, int sheetNum) throws IOException { // 获取excel文件的io流 InputStream is = new ByteArrayInputStream(bytes); // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象 Workbook workbook = new XSSFWorkbook(is);; // 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回 List<String[]> list = new ArrayList<String[]>(); if (workbook != null) { // for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { // 获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(sheetNum); // if (sheet == null) { // continue; // } // 获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); // 获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); // 循环除了第一行的所有行 for (int rowNum = firstRowNum + 0; rowNum <= lastRowNum; rowNum++) { // 获得当前行 Row row = sheet.getRow(rowNum); if (row == null || row.getPhysicalNumberOfCells()==0) { continue; } // 获得当前行的开始列 int firstCellNum = row.getFirstCellNum(); // 获得当前行的列数 int lastCellNum = row.getPhysicalNumberOfCells(); String[] cells = new String[row.getPhysicalNumberOfCells()]; // 循环当前行 for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) { Cell cell = row.getCell(cellNum); cells[cellNum] = getCellValue(cell); } list.add(cells); } // } workbook.close(); } return list; } private static String getCellValue(Cell cell) { String cellValue = ""; if (cell == null) { return cellValue; } // 把数字当成String来读，避免出现1读成1.0的情况 if (cell.getCellType() == CellType.NUMERIC) { cell.setCellType(CellType.STRING); } // 判断数据的类型 switch (cell.getCellType()) { case NUMERIC: // 数字 cellValue = String.valueOf(cell.getNumericCellValue()); break; case STRING: // 字符串 cellValue = String.valueOf(cell.getStringCellValue()); break; case BOOLEAN: // Boolean cellValue = String.valueOf(cell.getBooleanCellValue()); break; case FORMULA: // 公式 cellValue = String.valueOf(cell.getCellFormula()); break; case BLANK: // 空值 cellValue = ""; break; case ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } public static Map<String, byte[]> getExcelPictures(String fileName, byte[] bytes, int sheetNum) throws IOException { Map<String, byte[]> map = new HashMap<String, byte[]>(); // 获取excel文件的io流 InputStream is = new ByteArrayInputStream(bytes); // 获得Workbook工作薄对象 Workbook workbook = new XSSFWorkbook(is);; XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(sheetNum); List<POIXMLDocumentPart> list = sheet.getRelations(); for (POIXMLDocumentPart part : list) { if (part instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) part; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { XSSFPicture picture = (XSSFPicture) shape; XSSFClientAnchor anchor = picture.getPreferredSize(); CTMarker marker = anchor.getFrom(); String key = marker.getRow() + "-" + marker.getCol(); byte[] data = picture.getPictureData().getData(); map.put(key, data); } } } return map; }}