poi0010,excel文件读取

客官°小女子只卖身不卖艺 2022-04-22 06:44 260阅读 0赞

官网：[http://poi.apache.org/components/spreadsheet/index.html][http_poi.apache.org_components_spreadsheet_index.html]

poi解析excel文件有三种方式：

1、eventmodel方式，基于事件驱动,SAX的方式解析excel（.xlsx是基于OOXML的），CPU和内存消耗非常低，但是只能读不能写（博客：[https://blog.csdn.net/wrongyao/article/details/83663065][https_blog.csdn.net_wrongyao_article_details_83663065]）

2、usermodel，就是我们一般使用的方式，这种方式可以读可以写，但是CPU和内存消耗非常大

3、SXSSF，POI3.8以后开始支持，这种方式只能写excel（博客：[https://www.cnblogs.com/tootwo2/p/6683143.html][https_www.cnblogs.com_tootwo2_p_6683143.html]）

这边介绍的是第二种，usermodel（用户模式），介绍代码之前，先来看看excel文件的组成，最大的一层是一个excel文件，然后是多个sheet页，一个sheet页包含n多行，一行又包含多个单元格，单元格有样式。下面简单介绍poi中对应的类，详细方法参见api

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>excel</td> 
   <td>sheet</td> 
   <td>Row</td> 
   <td>cell</td> 
   <td>样式</td> 
  </tr> 
  <tr> 
   <td>接口</td> 
   <td>Workbook</td> 
   <td>Sheet</td> 
   <td>Row</td> 
   <td>Cell</td> 
   <td>CellStyle</td> 
  </tr> 
  <tr> 
   <td>2003</td> 
   <td>XSSFWorkbook</td> 
   <td>XSSFSheet</td> 
   <td>XSSFRow</td> 
   <td>XSSFCell</td> 
   <td>XSSFCellStyle</td> 
  </tr> 
  <tr> 
   <td>2007</td> 
   <td>HSSFWorkbook</td> 
   <td>HSSFSheet</td> 
   <td>HSSFRow</td> 
   <td>HSSFCell</td> 
   <td>HSSFCellStyle</td> 
  </tr> 
 </tbody> 
</table>

文件读取的简单例子

1、创建maven工程，加入依赖

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>

2、创建excel文件的读写方法，注意这种方法仅支持小文件的读取，大文件读取容易造成内存溢出，大文件读取见贴二

package com.honor.poi;
    
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.*;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    
    /**
     * Created by rongyaowen
     * on 2018/10/21.
     * 普通的excel读写类，支持excel文件(xlsx,xls)的读写
     */
    public class CommonExcel {
        private final static String XLS = "xls";
        private final static String XLSX = "xlsx";
    
        /**
         * excel读方法
         *
         * @param path 文件路径
         * @return
         */
        public static List<Map<String, Object>> read(String path) {
            List<Map<String, Object>> mapList = new ArrayList<>();
            if (null == path || path.equals("")) {
                throw new RuntimeException("文件路径不能为空！");
            }
            String suffix = path.substring((path.indexOf(".") + 1));
            Workbook workbook = null;
            FileInputStream fis = null;
            try {
                fis = new FileInputStream(path);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            if (null != suffix && suffix.equals(XLS)) {
                try {
                    workbook = new HSSFWorkbook(fis);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } else if (null != suffix && suffix.equals(XLSX)) {
                try {
                    workbook = new XSSFWorkbook(fis);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } else {
                throw new RuntimeException("文件格式错误");
            }
            if (null == path || path.equals("")) {
                throw new RuntimeException("文件路径不能为空！");
            }
            // 遍历sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Row header = sheet.getRow(0);
                // 遍历行
                for (int j = 1; j < sheet.getLastRowNum(); j++) {
                    Row content = sheet.getRow(j);
                    Map<String, Object> map = new HashMap<>();
                    for (int k = 0; k < content.getLastCellNum(); k++) {
                        map.put(header.getCell(k).toString(), getValue(content.getCell(k)));
                        System.out.print(header.getCell(k).toString() + " : " + getValue(content.getCell(k)).toString() + " ");
                    }
                    System.out.println();
                    mapList.add(map);
                }
            }
    
            return mapList;
        }
    
        /**
         * 根据单元格的类型返回真实值
         *
         * @param cell
         * @return
         */
        private static Object getValue(Cell cell) {
            Object obj = null;
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    obj = cell.getRichStringCellValue().getString();
                    break;
                case NUMERIC:
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        obj = sdf.format(cell.getDateCellValue());
                    } else {
                        obj = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    obj = cell.getBooleanCellValue();
                    break;
                case BLANK:
                    obj = "";
                    break;
                default:
                    obj = cell.toString();
                    break;
            }
            return obj;
        }
    
        /**
         * 写入单元格
         *
         * @param mapList 单元格数据
         * @param path    文件路径
         */
        public static void write(List<Map<String, Object>> mapList, String path) {
            if (null == path || path.equals("")) {
                throw new RuntimeException("文件路径不能为空！");
            }
            String suffix = path.substring((path.indexOf(".") + 1));
            Workbook workbook = null;
            if (null != suffix && suffix.equals(XLS)) {
                workbook = new HSSFWorkbook();
            } else if (null != suffix && suffix.equals(XLSX)) {
                workbook = new XSSFWorkbook();
            } else {
                throw new RuntimeException("文件格式错误");
            }
            Sheet sheet = workbook.createSheet();
            Row header = sheet.createRow(0);
            if (mapList.isEmpty()) {
                throw new RuntimeException("写入值为空！");
            }
            Map<String, Object> headerMap = mapList.get(0);
            int i = 0;
            for (String str : headerMap.keySet()) {
                Cell cell = header.createCell(i);
                cell.setCellValue(str);
                i++;
            }
            i = 1;
            for (Map<String, Object> map : mapList) {
                Row content = sheet.createRow(i);
                for (int j = 0; j < header.getLastCellNum(); j++) {
                    Cell cell = content.createCell(j);
                    cell.setCellValue(map.get(header.getCell(j).getStringCellValue()).toString());
                }
                i++;
            }
            try {
                FileOutputStream fileOutputStream = new FileOutputStream(path);
                workbook.write(fileOutputStream);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

[http_poi.apache.org_components_spreadsheet_index.html]: http://poi.apache.org/components/spreadsheet/index.html
[https_blog.csdn.net_wrongyao_article_details_83663065]: https://blog.csdn.net/wrongyao/article/details/83663065
[https_www.cnblogs.com_tootwo2_p_6683143.html]: https://www.cnblogs.com/tootwo2/p/6683143.html