随笔- 171 文章- 0 评论- 30 阅读- 14万

JAVA poi 帮助类

pom.xml 添加引用:

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

Java没有datatable，创建一个数据保存帮助类

import java.util.ArrayList;

public class ExcelDO {
    public ExcelDO() {

    }

    public ExcelDO(String name) {
        this.name = name;
    }

    /*
    * sheet名
    * */
    private String name;

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    /*
    * 二维集合，保存excel中的数据
    * */
    private ArrayList<ArrayList<String>> list;

    public ArrayList<ArrayList<String>> getList() {
        return this.list;
    }

    public void setList(ArrayList<ArrayList<String>> list) {
        this.list = list;
    }
}

View Code

poi帮助类

package me.loveshare.springboot1.Common.Helper;

import me.loveshare.springboot1.Entity.DO.ExcelDO;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.concurrent.atomic.AtomicReference;

public class POIHelper {
    /**
     * 根据excel路径返回集合
     */
    public static ArrayList<ExcelDO> ReadExcel(String filePath) throws IOException {
        ArrayList<ExcelDO> list = new ArrayList<>();

        Workbook workbook = GetWorkbook(filePath);
        // sheet总数
        Integer sheetTotal = workbook.getNumberOfSheets();
        for (Integer num = 0; num < sheetTotal; num++) {
            if (workbook.isSheetHidden(num)) continue;
            Sheet sheet = workbook.getSheetAt(num);

            ExcelDO excelDO = new ExcelDO(sheet.getSheetName());
            ArrayList<ArrayList<String>> itemList = new ArrayList<ArrayList<String>>();

            //  设置最大列，默认为1
            Integer maxColumnNum = 0;
            //  不是有效列集合，连续超过三行不读取后续所有列
            ArrayList<Integer> noValidColumnList = new ArrayList<>();
            //  列：按照列把数据填充到datatable中，防止无限列出现
            for (Integer columnIndex = 0; columnIndex <= maxColumnNum; columnIndex++) {
                noValidColumnList.add(columnIndex);
                //  列中所有数据都是null为true
                Boolean isAllEmpty = true;
                //  行
                for (Integer rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    if (columnIndex == 0)
                        itemList.add(new ArrayList<String>());
                    Row itemRow = sheet.getRow(rowIndex);
                    if (itemRow == null) continue;
                    maxColumnNum = maxColumnNum < itemRow.getLastCellNum() ? itemRow.getLastCellNum() : maxColumnNum;
                    //  把格式转换为utf-8
                    String itemCellValue = StringHelper.FormatUtf8String(GetValue(itemRow, columnIndex));
                    if (!StringHelper.IsNullOrWhiteSpace(itemCellValue)) isAllEmpty = false;
                    itemList.get(rowIndex).add(columnIndex, itemCellValue);
                }

                //  当前列有值
                if (!isAllEmpty)
                    noValidColumnList.clear();
                    //  连续空白列超过三行 或 有空白行且当前行为最后一行
                else if (noValidColumnList.size() > 3 || (noValidColumnList.size() > 0 && columnIndex == maxColumnNum - 1)) {
                    for (Integer i = noValidColumnList.size() - 1; i >= 0; i--)
                        itemList.remove(i);
                    break;
                }
            }

            // 得到一个sheet中有多少个合并单元格
            Integer sheetMergeCount = sheet.getNumMergedRegions();
            for (Integer i = 0; i < sheetMergeCount; i++) {
                // 获取合并后的单元格
                CellRangeAddress range = sheet.getMergedRegion(i);
                String cellValue = itemList.get(range.getFirstRow()).get(range.getFirstColumn());
                for (Integer mRowIndex = range.getFirstRow(); mRowIndex <= range.getLastRow(); mRowIndex++) {
                    for (Integer mColumnIndex = range.getFirstColumn(); mColumnIndex <= range.getLastColumn(); mColumnIndex++) {
                        itemList.get(mRowIndex).set(mColumnIndex, cellValue);
                    }
                }
            }
            excelDO.setList(itemList);
            list.add(excelDO);
        }

        return list;
    }

    /*
     * 把集合中的数据保存为excel文件
     * */
    public static void SaveExcel(ArrayList<ExcelDO> doList, String fileDirectoryPath) {
        doList.forEach(item -> {
            Workbook workbook = new HSSFWorkbook();
            Sheet sheet = workbook.createSheet(item.getName());
            ArrayList<ArrayList<String>> itemList = item.getList();
            if (itemList != null || !itemList.isEmpty()) {
                for (Integer rowNum = 0; rowNum < itemList.size(); rowNum++) {
                    ArrayList<String> rowList = itemList.get(rowNum);
                    Row row = sheet.createRow(rowNum);
                    for (Integer columnNum = 0; columnNum < rowList.size(); columnNum++) {
                        Cell codeCell = row.createCell(columnNum);
                        codeCell.setCellValue(rowList.get(columnNum));
                    }
                }
            }
            String filePath = fileDirectoryPath + item.getName() + ".xls";
            try {
                OutputStream stream = new FileOutputStream(filePath);// 将workbook写到输出流中
                workbook.write(stream);
                stream.flush();
                stream.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        });
    }

    //  根据文件路径，返回文档对象
    public static Workbook GetWorkbook(String filePath) throws IOException {
        String extension = FileHelper.GetExtension(filePath);
        InputStream stream = new FileInputStream(filePath);
        //HSSF提供读写Microsoft Excel XLS格式档案的功能。（97-03）
        //XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
        //HWPF提供读写Microsoft Word DOC格式档案的功能。
        //HSLF提供读写Microsoft PowerPoint格式档案的功能。
        //HDGF提供读Microsoft Visio格式档案的功能。
        //HPBF提供读Microsoft Publisher格式档案的功能。
        //HSMF提供读Microsoft Outlook格式档案的功能。
        switch (extension) {
            case "xls":
                return new HSSFWorkbook(stream);
            case "xlsx":
            case "xlsm":
                return new XSSFWorkbook(stream);
        }
        //抛出自定的业务异常
        throw new Error("excel格式文件错误");
    }

    /*
     * poi特殊日期格式：数字格式化成-yyyy年MM月dd日，格式
     * */
    private static ArrayList<String> PoiDateList = new ArrayList<String>() {
        {
            add("年");
            add("月");
            add("日");
        }
    };

    /// <summary>
    /// 获取XSSFRow的值（全部统一转成字符串）
    /// </summary>
    /// <param name="row"></param>
    /// <param name="index"></param>
    /// <returns></returns>
    public static String GetValue(Row row, int index) {
        Cell rowCell = row.getCell(index);
        return rowCell == null ? "" : GetValueByCellStyle(rowCell, rowCell.getCellType());
    }

    /// <summary>
    /// 根据单元格的类型获取单元格的值
    /// </summary>
    /// <param name="rowCell"></param>
    /// <param name="type"></param>
    /// <returns></returns>
    public static String GetValueByCellStyle(Cell rowCell, int rowCellType) {
        String value = "";
        switch (rowCellType) {
            case Cell.CELL_TYPE_STRING:
                value = rowCell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                //  获取单元格值的格式化信息
                String dataFormat = rowCell.getCellStyle().getDataFormatString();
                //  判断格式化信息中是否存在：年月日
                AtomicReference<Boolean> isDate = new AtomicReference<>(false);
                if (!StringHelper.IsNullOrWhiteSpace(dataFormat))
                    PoiDateList.forEach(x -> isDate.set(isDate.get() || dataFormat.contains(x)));

                if (DateUtil.isCellDateFormatted(rowCell)) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue()));
                } else if (DateUtil.isCellInternalDateFormatted(rowCell)) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue()));
                }
                //有些情况，时间搓？数字格式化显示为时间,不属于上面两种时间格式
                else if (isDate.get()) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(rowCell.getDateCellValue());
                }
                //有些情况，时间搓？数字格式化显示为时间,不属于上面两种时间格式
                else if (dataFormat == null) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue()));
                } else {
                    if (StringHelper.IsNullOrWhiteSpace(dataFormat)) {
                        value = String.valueOf(rowCell.getNumericCellValue());
                    } else {
                        if (rowCell.getCellStyle().getDataFormatString().contains("$")) {
                            value = "$" + rowCell.getNumericCellValue();
                        } else if (rowCell.getCellStyle().getDataFormatString().contains("￥")) {
                            value = "￥" + rowCell.getNumericCellValue();
                        } else if (rowCell.getCellStyle().getDataFormatString().contains("¥")) {
                            value = "¥" + rowCell.getNumericCellValue();
                        } else if (rowCell.getCellStyle().getDataFormatString().contains("€")) {
                            value = "€" + String.valueOf(rowCell.getNumericCellValue());
                        } else {
                            value = String.valueOf(rowCell.getNumericCellValue());
                        }
                    }
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(rowCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                value = ErrorEval.getText(rowCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                //  TODO: 是否存在 嵌套 公式类型
                value = GetValueByCellStyle(rowCell, rowCell.getCachedFormulaResultType());
                String cellvalue = String.valueOf(rowCell.getCellFormula());
                break;
            default:
                System.out.println(rowCell);
                break;
        }
        return value;
    }
}

View Code

StringHelper

import java.io.UnsupportedEncodingException;

public class StringHelper {
    /*
        把特殊字符转换为utf-8格式
    */
    public static String FormatUtf8String(String str) throws UnsupportedEncodingException {
        if (IsNullOrWhiteSpace(str)) return "";
        String newStr = changeCharset(str, "utf-8").trim();
        return newStr;
    }

    /**
     * 字符串编码转换的实现方法
     *
     * @param str        待转换编码的字符串
     * @param newCharset 目标编码
     * @return
     * @throws UnsupportedEncodingException
     */
    public static String changeCharset(String str, String newCharset) throws UnsupportedEncodingException {
        if (IsNullOrWhiteSpace(str)) return "";
        //用默认字符编码解码字符串。
        byte[] bs = str.getBytes();
        //用新的字符编码生成字符串
        return new String(bs, newCharset);
    }

    /*
        判断字符是否为空，为空返回true
    */
    public static boolean IsNullOrWhiteSpace(String str) {
        return str == null || str.isEmpty() ? true : false;
    }
}

View Code

FileHelper

import java.io.File;

public class FileHelper {
    //  返回指定的路径字符串的扩展名,不包含“。”,转小写
    public static String GetExtension(String filePath) {
        File file = new File(filePath);
        String fileName = file.getName();
        return fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length()).toLowerCase();
    }
}