java 读取Excel并解析获取数据

最新推荐文章于 2024-12-13 11:31:30 发布

原创最新推荐文章于 2024-12-13 11:31:30 发布 · 808 阅读

1 ·

CC 4.0 BY-SA版权

Java 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一种将Excel文件解析成Map的方法，其中key为sheet名称，value为对应sheet的数据内容。通过遍历读取每个sheet，处理每一行和每一列的数据，并存储到List中，最终形成一个Map结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以下方法将Excel文件解析成map，key为Excel的sheet名称，value为Excel中对应sheet数据内容list

public Map<String, Object> readXlsToMap(MultipartFile file) {
       Map<String, Object> map = new HashMap<>();
       InputStream input = null;
       HSSFWorkbook wb = null;
       try {
           input = file.getInputStream();
           // 创建文档
           wb = new HSSFWorkbook(input);
           // 读取sheet(页)
           for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {

               HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
               if (hssfSheet != null) {
                   List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
                   ArrayList<String> rowList = null;
                   if (hssfSheet != null) {
                       totalRows = hssfSheet.getLastRowNum();
                       // 读取Row
                       for (int rowNum = 0; rowNum <= totalRows; rowNum++) {
                           HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                           if (hssfRow != null) {
                               rowList = new ArrayList<String>();
                               totalCells = hssfRow.getLastCellNum();
                               // 读取列，从第一列开始
                               for (short c = 0; c <= totalCells + 1; c++) {
                                   HSSFCell cell = hssfRow.getCell(c);
                                   if (cell == null) {
                                       rowList.add(ExcelUtil.EMPTY);
                                       continue;
                                   }
                                   rowList.add(ExcelUtil.getHValue(cell).trim());
                               }
                               list.add(rowList);
                           }
                       }
                   }
                   map.put(hssfSheet.getSheetName(), list);
               }
           }
       } catch (IOException e) {
           e.printStackTrace();
       } finally {
           try {
               input.close();
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
       return map;
   }

ExcelUtil 工具类

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;

/**
* Excel工具类
*
* @author lp
*
*/
public class ExcelUtil {
   public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
   public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
   public static final String EMPTY = "";
   public static final String POINT = ".";
   public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

   /**
   * 获得path的后缀名
   *
   * @param path
   * @return
   */
   public static String getPostfix(String path) {
       if (path == null || EMPTY.equals(path.trim())) {
           return EMPTY;
       }
       if (path.contains(POINT)) {
           return path.substring(path.lastIndexOf(POINT) + 1, path.length());
       }
       return EMPTY;
   }

   /**
   * 单元格格式
   *
   * @param hssfCell
   * @return
   */
   @SuppressWarnings({ "static-access", "deprecation" })
   public static String getHValue(HSSFCell hssfCell) {
       if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
           return String.valueOf(hssfCell.getBooleanCellValue());
       } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
           String cellValue = "";
           if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
               Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
               cellValue = sdf.format(date);
           } else {
               DecimalFormat df = new DecimalFormat("#.##");
               cellValue = df.format(hssfCell.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 {
           return String.valueOf(hssfCell.getStringCellValue());
       }
   }

   /**
   * 单元格格式
   * @param xssfCell
   * @return
   */
   @SuppressWarnings("deprecation")
   public static String getXValue(XSSFCell xssfCell){
       if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
           return String.valueOf(xssfCell.getBooleanCellValue());
       } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
           String cellValue = "";
               DecimalFormat df = new DecimalFormat("#.##");
               cellValue = df.format(xssfCell.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 {
           return String.valueOf(xssfCell.getStringCellValue());
       }
   }
}