java解析大数据量xlsx格式excel

Myth丶恋晨 2021-07-27 00:02 560阅读 0赞

百度了好久关于解析excel的内容都找不到自己想要的东西,所以希望跟我有一样需求的人,能够因为这篇文章少走弯路.  
　　  
　　excel有两种格式,一种xls格式(97),一种xlsx格式(07). 提到excel API可能首先想到的是POI,使用POI能够读写所有的excel,但是POI针对于每种格式的excel分了好几种模式,UserModel EventModel UserEventModel  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4NjAzMTI3_size_16_color_FFFFFF_t_70][]

本人使用usermodel解析excel,10M大小的文件,就会OOM,因为usermodel模式是一次性加载所有的内容到内存,每个Cell就是一个对象,导致内存被撑爆.对于内存要求比较高或者说是文件比较大(10M也算大?)的场景,应该使用EventModel或者UserEventModel的API(我没研究,官网给的东西比较难看).这两种API比较复杂.  
　　  
　　因为本人的需求是解析xlsx格式的excel,针对于读取/解析excel的需求,可不必依赖POI , Streaming-Reader是对于POI的再次封装,并且简单易懂,虽然只能针对于xlsx格式的excel读取/解析(xls跟xlsx底层本质是不一样的),但是针对于这种需求,可以完美的解决掉爆内存的问题.  
　　  
　　解决内存问题的关键就是使用流式处理,读取一批数据解析完后就释放,再进行下一批,Streaming-Reader就是使用这种方式.

<dependency>
        <groupId>com.monitorjbl</groupId>
        <artifactId>xlsx-streamer</artifactId>
        <version>2.0.0</version>
    </dependency>

下面是示例代码

import com.monitorjbl.xlsx.StreamingReader;
    import org.apache.poi.ss.usermodel.*;
    import java.io.File;
    import java.io.FileInputStream;
    
    public class Test {
        public static void main(String[] args) throws Exception {
        
            FileInputStream in = new FileInputStream(new File("src/190917_MEAC Aug Database - v1.xlsx"));
            Workbook open = StreamingReader.builder()
                    .rowCacheSize(100)//一次读取多少行(默认是10行)
                    .bufferSize(1024)//使用的缓冲大小(默认1024)
                    .open(in);
            for (Sheet sheet : open) {
                for (Row row : sheet) {
                    for (Cell cell : row) {
    
                    }
                }
            }
        }
    }

基本逻辑就是这样子,是不是很简单? 可以根据sheet的名字取出特定sheet,但是无法指定具体的rownumber来获取行,因为这是基于流的方式读取excel内容,流的单位是.rowCacheSize(100)指定的行数,我们不可能在当前100行的流中,去指定第101行的Row.至于其他的API 可以自行翻看源码,源码中的内容通俗易懂.

下面贴出来的是我在解析中的一个实例.

public class DataImport {
    
        public static void main(String[] args) throws FileNotFoundException, SQLException {
            DataImportJdbc dataImportJdbc = new DataImportJdbc();
            HashMap<String, String> excDBMapping = dataImportJdbc.readMapping("smme");
            Map<String, Integer> fieldsIndexMapping = new HashMap<String, Integer>();
            FileInputStream in = new FileInputStream(new File("src/190917_MEAC Aug Database - v1.xlsx"));
            Workbook open = StreamingReader.builder()
                    .rowCacheSize(100)
                    .bufferSize(1024)
                    .open(in);
            HashMap<String, Integer> monthMapping = SMMEFields.monthMapping;
            HashMap<String, Integer> realMonthMapping = new HashMap<>();
            StreamingSheet sheet = (StreamingSheet) open.getSheet("Raw");
            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    for (Cell cell : row) {
                        String stringCellValue = cell.getStringCellValue().toLowerCase();
                        if (excDBMapping.containsKey(stringCellValue)) {
                            fieldsIndexMapping.put(excDBMapping.get(stringCellValue), cell.getColumnIndex());
                        } else if (monthMapping.containsKey(stringCellValue)) {
                            if("year".equals(stringCellValue)) {
                                realMonthMapping.put(stringCellValue, cell.getColumnIndex());
                            }else{
                                String month = MonthMatch.SMMEMonthMatch(stringCellValue);
                                realMonthMapping.put(month, cell.getColumnIndex());
                            }
                        }
                    }
                    System.out.println(realMonthMapping);
                } else {
                    Integer countryIndex = fieldsIndexMapping.get("country");
                    String country = row.getCell(countryIndex).getStringCellValue();
                    if (SMMEFields.countryList.contains(country)) {
                        System.out.println(country);
                        continue;
                    }
                    HashMap<String, String> stringStringHashMap = new HashMap<String, String>();
                    for (String key : fieldsIndexMapping.keySet()) {
                        Cell cell = row.getCell(fieldsIndexMapping.get(key));
                        String stringCellValue = cell.getStringCellValue();
                        stringStringHashMap.put(key, stringCellValue);
                    }
                    for (String key : realMonthMapping.keySet()) {
                        if (!"year".equals(key) && realMonthMapping.get(key) != null) {
                            Integer integer = realMonthMapping.get(key);
                            Cell cell = row.getCell(integer);
                            String volume = cell.getStringCellValue();
                            if (volume.trim().isEmpty() || volume.length() == 0 || volume.equals("- 0")) {
                                volume = "0";
                            } else if (volume.contains(",")) {
                                volume = volume.replace(",", "");
                            }
                            String year = row.getCell(realMonthMapping.get("year")).getStringCellValue();
                            String yearMonth = year + key;
                            String time = DateUtils.getStringTime();
                            OneData oneData = new OneData();
                            oneData.setMap(stringStringHashMap);
                            oneData.setTime(time);
                            oneData.setYearmonth(yearMonth);
                            oneData.setVolume(volume);
                            String id = DigestUtils.md5DigestAsHex(oneData.toString().getBytes());
                            oneData.setId(id);
                            dataImportJdbc.upsertData(oneData);
                        }
                    }
                }
            }
        }
    }

不用揣想上面的代码中我要做什么,只需要看基本的逻辑,以及API的使用就好.

如果使用中出现异常

Exception in thread "main" java.lang.UnsupportedOperationException
    	at com.monitorjbl.xlsx.impl.StreamingSheet.getFirstRowNum(StreamingSheet.java:118)
    	at com.parsh.Test.main(Test.java:21)

那是因为源码方法中没有逻辑内容,直接通过Throw来抛出了这个异常.看到这个异常就是说明,有这个方法,但是功能没实现,请不要使用这个方法的意思.

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4NjAzMTI3_size_16_color_FFFFFF_t_70]: /images/20210726/10ae99eb33914b338cc090a9484afd21.png