Java第四十天，业务实战（一），读取Excel文件

一、需要用到的工具类

1.字段名转换类

驼峰文件名和下划线文件名相互转换的工具类

package com.git.easyloan.netloan.entity.util;

public class FormatConversion {

    public static String humpToUnderline(String name) {
        StringBuilder result = new StringBuilder();
        if (name != null && name.length() > 0) {
            // 将第一个字符处理成大写
            result.append(name.substring(0, 1).toUpperCase());
            // 循环处理其余字符
            for (int i = 1; i < name.length(); i++) {
                String s = name.substring(i, i + 1);
                // 在大写字母前添加下划线
                if (s.equals(s.toUpperCase()) && !Character.isDigit(s.charAt(0))) {
                    result.append("_");
                }
                // 其他字符直接转成大写
                result.append(s.toUpperCase());
            }
        }
        return result.toString();
    }

    public static String underlineToHump(String name) {
        StringBuilder result = new StringBuilder();
        // 快速检查
        if (name == null || name.isEmpty()) {
            // 没必要转换
            return "";
        } else if (!name.contains("_")) {
            // 不含下划线，仅将首字母小写
            return name.substring(0, 1).toLowerCase() + name.substring(1);
        }
        // 用下划线将原始字符串分割
        String camels[] = name.split("_");
        for (String camel : camels) {
            // 跳过原始字符串中开头、结尾的下换线或双重下划线
            if (camel.isEmpty()) {
                continue;
            }
            // 处理真正的驼峰片段
            if (result.length() == 0) {
                // 第一个驼峰片段，全部字母都小写
                result.append(camel.toLowerCase());
            } else {
                // 其他的驼峰片段，首字母大写
                result.append(camel.substring(0, 1).toUpperCase());
                result.append(camel.substring(1).toLowerCase());
            }
        }
        return result.toString();
    }
}

2.读取Excel文件类

package com.git.easyloan.netloan.entity.util;

import javacommon.coreframe.util.PageData;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.io.File;
import java.io.FileInputStream;

public class ReadExcel {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private final String XLS = "xls";
    private final String XLSX = "xlsx";
    // private PageData pd;
    // private ArrayList<PageData> pdResult;
    private ArrayList<String> attrs = new ArrayList<>();

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     * @param inputStream 读取文件的输入流
     * @param fileType 文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    public Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 解析Excel数据
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private ArrayList<PageData> parseExcel(Workbook workbook) {

        ArrayList<PageData> pdResult = new ArrayList<PageData>();
        PageData pd = new PageData();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                logger.error("解析Excel失败，在第一行没有读取到任何数据！");
            }

            for(int index = 0;index < firstRow.getLastCellNum();index++){
                if(convertCellValueToString(firstRow.getCell(index)) == null || convertCellValueToString(firstRow.getCell(index)).equals("")){
                    logger.error("第" + String.valueOf(index + 1) + "列标题栏为空，程序退出");
                    return null;
                }else{
                    attrs.add(FormatConversion.underlineToHump(convertCellValueToString(firstRow.getCell(index))));
                }
            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);

                if (null == row) {
                    continue;
                }

                pd = convertRowToData(row);
                if (null == pd) {
                    logger.error("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                    continue;
                }
                pdResult.add(pd);
            }
        }
        return pdResult;
    }

    /**
     * 将单元格内容转换为字符串
     * @param cell
     * @return
     */
    private String convertCellValueToString(Cell cell) {
        if(cell==null){
            return null;
        }
        String returnValue = null;
        /**
         * CELL_TYPE_NUMERIC     	数值 	0
         * CELL_TYPE_STRING     	字符串型 	1
         * CELL_TYPE_FORMULA     	公式型 	2
         * CELL_TYPE_BLANK     	    空值 	3
         * CELL_TYPE_BOOLEAN     	布尔型 	4
         * CELL_TYPE_ERROR     	    错误 	5
         */
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     *
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private PageData convertRowToData(Row row) {

        PageData pd = new PageData();
        Cell cell;
        int cellNum = 0;

        for (int i = 0; i < row.getLastCellNum(); i++) {
            cell = row.getCell(cellNum);
            if(convertCellValueToString(cell) == null || convertCellValueToString(cell).equals("")){
                pd.put(attrs.get(cellNum++), "?");
                logger.error("第" + String.valueOf(i + 1) + "列数据为空，程序已自动填充为 ? ");
            }else{
                pd.put(attrs.get(cellNum++), convertCellValueToString(cell));
            }
        }
        return pd;
    }

    public ArrayList<PageData> ReadExcel(String fileUrl){

        Workbook workbook = null;
        FileInputStream inputStream = null;
        ArrayList<PageData> pdResult = new ArrayList<>();

        try {
            // 获取Excel后缀名
            String fileType = fileUrl.substring(fileUrl.lastIndexOf(".") + 1, fileUrl.length());
            // 获取Excel文件
            File excelFile = new File(fileUrl);
            if (!excelFile.exists()) {
                logger.error("指定的Excel文件不存在！");
                return null;
            }

            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);
            // 读取excel中的数据
            pdResult = parseExcel(workbook);
            return pdResult;
        } catch (Exception e) {
            logger.error("解析Excel失败，文件名：" + fileUrl + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                logger.error("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }
}

3.写Excel文件类

package com.git.easyloan.netloan.entity.util;

import javacommon.coreframe.util.PageData;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.util.*;

public class WriteExcel {

    private ArrayList<String> CELL_HEADS = new ArrayList<>(); //标题列表

    // 初始化标题
    private void initCellHeads(PageData pd){
        Set set = pd.keySet();
        Iterator it = set.iterator();
        while(it.hasNext()){
            CELL_HEADS.add(it.next().toString());
        }
    }

    // 根据列表中第一行数据的 key 列表写入标题
    public boolean initTitle(PageData pd){
        if(pd == null){
            return false;
        }else{
            initCellHeads(pd);
            return true;
        }
    }

    /**
     * 生成sheet表，并写入第一行数据（列头）
     */
    private Sheet buildDataSheet(Workbook workbook) {
        Sheet sheet = workbook.createSheet();
        // 设置列头宽度
        for (int i=0; i< CELL_HEADS.size(); i++) {
            sheet.setColumnWidth(i, 4000);
        }
        // 设置默认行高
        sheet.setDefaultRowHeight((short) 400);
        // 构建头单元格样式
        CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
        // 写入第一行各列的数据
        Row head = sheet.createRow(0);
        for (int i = 0; i < CELL_HEADS.size(); i++) {
            Cell cell = head.createCell(i);
            cell.setCellValue(CELL_HEADS.get(i));
            cell.setCellStyle(cellStyle);
        }
        return sheet;
    }

    /**
     * 设置第一行列头的样式
     * @param workbook 工作簿对象
     * @return 单元格样式对象
     */
    private CellStyle buildHeadCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //对齐方式设置
        style.setAlignment(HorizontalAlignment.CENTER);
        //边框颜色和宽度设置
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
        //设置背景颜色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //粗体字设置
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    /**
     * 生成Excel并写入数据信息
     */
    public Workbook exportData(ArrayList<PageData> pdList){

        // 生成xlsx的Excel
        Workbook workbook = new SXSSFWorkbook();

        // 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
        //Workbook workbook = new HSSFWorkbook();

        // 写入标题
        if(!initTitle(pdList.get(0))){
            return null;
        }

        // 生成Sheet表，写入第一行的列头
        Sheet sheet = buildDataSheet(workbook);

        // 构建每行的数据内容
        // 第 0 行为标题，以后的都为内容
        int rowNum = 1;
        for (Iterator<PageData> it = pdList.iterator(); it.hasNext(); ) {

            PageData pd = it.next();
            if (pd == null) {
                continue;
            }
            //输出行数据
            Row row = sheet.createRow(rowNum++);
            convertDataToRow(pd, row);
        }
        return workbook;
    }

    /**
     * 将数据转换成行
     * @return
     */
    private void convertDataToRow(PageData pd, Row row){
        Cell cell;
        int rows = pd.size();
        for (int i = 0; i < rows; i++) {
            cell = row.createCell(i);
            cell.setCellValue(null == pd.getString(CELL_HEADS.get(i)) ? "?" : pd.getString(CELL_HEADS.get(i)));
        }
    }
}

二、读取写入Excel文件总控制类

想要读取或写入Excel文件只需要创建该类的对象进行操作即可；它会自动调用其他用到的工具类。该类仅有两个方法，一个读取，一个写入；参数也非常明了，故不再做多余的笔记

package com.git.easyloan.netloan.entity.util;

import javacommon.coreframe.util.PageData;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelUtil {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private WriteExcel write;

    public ArrayList<PageData> ReadExcelFileToPageDataList(String resourceFileUrl){
        return new ReadExcel().ReadExcel(resourceFileUrl);
    }

    public void WriteExcelFileFromPageDataList(ArrayList<PageData> pds, String targetFileUrl){
        write = new WriteExcel();
        Workbook workbook = write.exportData(pds);
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(targetFileUrl);
            workbook.write(fileOut);
            fileOut.flush();
        } catch (Exception e) {
            logger.error("输出Excel时发生错误，错误原因：" + e.getMessage());
        } finally {
            try {
                if (null != fileOut) {
                    fileOut.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (IOException e) {
                logger.error("关闭输出流时发生错误，错误原因：" + e.getMessage());
            }
        }
    }
}

三、在线、离线 jar 包

1.离线包下载地址

https://download.csdn.net/download/ITlanyue/12671478

2.在线包（使用Maven）配置文件声明

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.16-beta1</version>
</dependency>

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