项目中使用POI中的Java解析、生成Excel方法

 在介绍POI方法前还可以了解下一种占内存比较小的方法easyExcel 

链接如右边 https://blog.csdn.net/jianggujin/article/details/80200400

接下来我们主要介绍下 项目中使用POI中的Java解析、生成Excel方法

将系统中的数据导入到Excel中

//list可以通过查询表获取
//httpResponse 为 HttpServletResponse httpResponse
//Excel Util
        Integer[] columnWidths = {30, 50, 30, 30, 30, 30, 35, 35, 35};
        String[] columnNames = {"区域", "市场", "日期", "当日检测批次", "检测批次阈值", "检测批次达标情况", "非农残比例", "非农残比例阈值", "非农残检测达标情况"};
        String[] methodNames = {"getAdname", "getMarketname", "getCheckdate", "getPici", "getYzpici", "getPicidb", "getBili", "getYzbili", "getBilidb"};
        Map<String, Object> map1 = new HashMap<>();
        map1.put("title", "市场自检情况统计表");
        map1.put("columnWidths", columnWidths);
        map1.put("columnNames", columnNames);
        map1.put("methodNames", methodNames);
        ExcelUtil.export(httpResponse, list1, map1);

 将Excel中的数据导入到系统当中 

List list = ExcelUtil.read(wb);

创建ExcelUtil方法

package com.example.demo.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.ResourceUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {
    public static String deepBlue = "#0A57A7";

    /**
     * 构造方法
     */

    public ExcelUtil() {

    }
    /**
     * read方法 获取Excel中的信息存储到list中
     *
     * */
    public static List<List<String>> read(Workbook wb) {
        int totalRows = 0;
        int totalCells = 0;

        List<List<String>> dataLst = new ArrayList<List<String>>();

        /* 得到第一个shell */
        Sheet sheet = wb.getSheetAt(0);

        /* 得到Excel的行数 */
        totalRows = sheet.getPhysicalNumberOfRows();

        /* 得到Excel的列数 */
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        /* 循环Excel的行 */
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            List<String> rowLst = new ArrayList<String>();
            /* 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                rowLst.add(getCellValue(cell));
            }
            /* 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }
    /**
     * getCellValue 用于设置单元格传入的文字格式
     *
     * */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 以下是判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case STRING:
                cellValue = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是date类型则 ，获取该cell的date值
                    cellValue = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
                } else { // 纯数字
                    // 返回数值类型的值
                    Object inputValue = null;// 单元格值
                    Long longVal = Math.round(cell.getNumericCellValue());
                    Double doubleVal = cell.getNumericCellValue();
                    if (Double.parseDouble(longVal + ".0") == doubleVal) { // 判断是否含有小数位.0
                        inputValue = longVal;
                    } else {
                        inputValue = doubleVal;
                    }
                    DecimalFormat df = new DecimalFormat("#.####"); // 格式化为四位小数，按自己需求选择；
                    cellValue = String.valueOf(df.format(inputValue)); // 返回String类型
                }
                break;
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case BLANK:
                cellValue = "";
                break;
            default:
                cellValue = cell.toString();
                break;
        }
        return cellValue;
    }
    /**
     *  export 将系统中的信息存储到Excel中
     * */
    public static <T> void export(HttpServletResponse resp, List<T> list, Map<String, Object> options) {
        HSSFWorkbook wb = new HSSFWorkbook();

        String title = (String) options.get("title");
        Integer[] columnWidths = (Integer[]) options.get("columnWidths");
        String[] columnNames = (String[]) options.get("columnNames");
        String[] methodNames = (String[]) options.get("methodNames");

        HSSFSheet sheet = wb.createSheet(title);

        for (int i = 0; i < columnWidths.length; i++) {
            sheet.setColumnWidth(i, columnWidths[i] * 160);
        }

        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);

        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;

        for (int i = 0; i < columnNames.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(columnNames[i]);
        }
        SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Method method = null;

        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            T t = list.get(i);

            for (int j = 0; j < methodNames.length; j++) {
                String methodName = methodNames[j];
                try {
                    method = t.getClass().getMethod(methodName);
                    Object val = method.invoke(t);
                    cell = row.createCell(j);
                    if (val instanceof Date) {
                        setCellValue(cell, f.format(val), style2);
                    } else if (val != null) {
                        setCellValue(cell, val.toString(), style2);
                    } else {
                        setCellValue(cell, "-", style2);
                    }
                } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {