简单的读取Excel数据demo

一个简单的读取Excel内容并打印的demo，具体的excel数据可以存储在数据库中以进行后续复杂处理，这里只做打印

1.使用Idea构建maven项目

构建Springboot + maven的demo项目，选中lombok组件
在生成的项目中引入以下依赖：

<!--fastjson jar dependency-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.31</version>
</dependency>

<!-- import poi to deal with excel -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${apache.poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${apache.poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>${apache.poi.version}</version>
</dependency>

其中apache poi支持处理xlsx格式的文件，版本：

<apache.poi.version>4.0.0</apache.poi.version>

2.解析xlsx文件的代码（只提供初级的单元格数据读取)

package com.richardyao.analysisexcel;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 *
 * @author richard_xsyao
 * @date 2018/10/13 15:03
 * Description:
 */
@Slf4j
@Component
public class ExcelUtil {

    /**
     * Analysis file content and return the list result for store
     * @param inputStream
     * @return
     */
    public List<Map<String, String>> readFileContentToEntity(InputStream inputStream) {
        List<Map<String, String>> result = new ArrayList<>();
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(inputStream);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
        if(wb != null) {
            // Get all exist sheets and deal it with loop
            int sheetsNum = wb.getNumberOfSheets();
            if(sheetsNum > 0) {
                for(int i=0; i<sheetsNum; i++) {
                    Sheet sheet = wb.getSheetAt(i);
                    if(sheet.getPhysicalNumberOfRows() > 0) {
                        result.addAll(dealWithExcelSheet(sheet));
                    }
                }
            }
        }
        return result;
    }

    /**
     * Analysis one sheet content and return List data
     * @param sheet
     * @return
     */
    private List<Map<String, String>> dealWithExcelSheet(Sheet sheet) {
        List<Map<String, String>> result = new ArrayList<>();
        // Loop every row
        for(int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            Map<String, String> record = new HashMap<>(16);
            Row row = sheet.getRow(i);
            // Loop one row's every Cell
            if(row != null && row.getPhysicalNumberOfCells() > 0) {
                // The first cell is sequence which not useful
                for(int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                    Cell cell = row.getCell(j);
                    if(cell != null) {
                        // Read every Cell data as String type
                        cell.setCellType(CellType.STRING);
                        String value = cell.getStringCellValue();
                        // Every cell must contain value
                        if(!StringUtils.isEmpty(value)) {
                            try {
                                // Do not deal with space characters
                                value = value.trim();
                                String key = String.format("Row %d Column %d", i, j);
                                record.put(key, value);
                            } catch(NumberFormatException e) {
                                log.error(e.getMessage(), e);
                            }
                        }
                    }
                }
                result.add(record);
            }
        }
        return result;
    }
}

3.读取excel文件并使用以上util工具

package com.richardyao.analysisexcel;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 *
 * @author richard_xsyao
 * @date 2018/10/13 15:21
 * Description:
 */
@Slf4j
@Component
public class MethodToDealExcel implements CommandLineRunner {

    @Autowired
    private ExcelUtil excelUtil;

    @Override
    public void run(String... args) throws Exception {
        String path = "E:\\data-all\\Afghanistan.xlsx";
        File file = new File(path);
        if(file.exists()) {
            try {
                InputStream fileInput = new FileInputStream(file);
                List<Map<String, String>> result = excelUtil.readFileContentToEntity(fileInput);
                System.out.println(JSON.toJSONString(result));
            } catch (FileNotFoundException e) {
                log.error(e.getMessage(), e);
            }
        }
    }
}