POI将excel表中信息读取，并且处理输出到一个新excel表中。

POM文件

        <!-- poi 传入Excel表   2003office 版本-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

bean类 1

package bean;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author JMWANG
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelT {

    double Zcorrect_number;
    String Zcorrect_rate;
    double Zrecall_quantity;
    String Zrecall_rate;

    double Acorrect_number;
    String Acorrect_rate;
    double Arecall_quantity;
    String Arecall_rate;

    double Ccorrect_number;
    String Ccorrect_rate;
    double Crecall_quantity;
    String Crecall_rate;

    double Rcorrect_number;
    String Rcorrect_rate;
    double Rrecall_quantity;
    String Rrecall_rate;
}

bean类2

package bean;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author JMWANG
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class OrderVO {

    /**
     * 困难样本 还是 简单样本
     */
    private String simple_or_difficult;

    /**
     * 样本id
     */
    private String sample_id;

    /**
     * 文书号置信度
     */
    private String instrument_number_confidence;

    /**
     * 文书号是否正确
     */
    private String is_ture_document_number;

    /**
     * 被处罚对象置信度
     */
    private String confidence_of_punished_object;

    /**
     * 被处罚对象是否正确
     */
    private String is_ture_punished_object;

    /**
     * 日期置信度
     */
    private String date;

    /**
     * 日期是否正确
     */
    private String is_ture_date;


}

导入包

import bean.ExcelT;
import bean.OrderVO;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;
import java.math.RoundingMode;
import java.text.NumberFormat;
import java.util.*;

公共静态变量

    private static Logger LOGGER = Logger.getLogger(TestPoi.class);

    public static Map<String, ExcelT> excelTMap = new LinkedHashMap<>();

    {
        excelTMap.put("0.9", null);
        excelTMap.put("0.91", null);
        excelTMap.put("0.92", null);
        excelTMap.put("0.93", null);
        excelTMap.put("0.94", null);
        excelTMap.put("0.95", null);
        excelTMap.put("0.96", null);
        excelTMap.put("0.97", null);
        excelTMap.put("0.98", null);
        excelTMap.put("0.99", null);
    }

    public static Map<String, ExcelT> excelTMapSimple = new LinkedHashMap();

    {
        excelTMapSimple.put("0.9", null);
        excelTMapSimple.put("0.91", null);
        excelTMapSimple.put("0.92", null);
        excelTMapSimple.put("0.93", null);
        excelTMapSimple.put("0.94", null);
        excelTMapSimple.put("0.95", null);
        excelTMapSimple.put("0.96", null);
        excelTMapSimple.put("0.97", null);
        excelTMapSimple.put("0.98", null);
        excelTMapSimple.put("0.99", null);
    }

    public static Map<String, ExcelT> excelTMapDifficult = new LinkedHashMap();

    {
        excelTMapDifficult.put("0.9", null);
        excelTMapDifficult.put("0.91", null);
        excelTMapDifficult.put("0.92", null);
        excelTMapDifficult.put("0.93", null);
        excelTMapDifficult.put("0.94", null);
        excelTMapDifficult.put("0.95", null);
        excelTMapDifficult.put("0.96", null);
        excelTMapDifficult.put("0.97", null);
        excelTMapDifficult.put("0.98", null);
        excelTMapDifficult.put("0.99", null);
    }

    public static List<OrderVO> list_simple = new ArrayList<>();
    public static List<OrderVO> list_difficulty = new ArrayList<>();
    public static List<OrderVO> list_new = new ArrayList<>();
    public static int number_of_samples;
    public static int number_of_simple_samples;
    public static int number_of_difficulty_samples;

从excel中解析样本

    public static void getMeg() {

        try {
            //读取xls文档
            List<OrderVO> list = readXls(new File("C:\\Users\\jmwang.erics\\Desktop\\副本图片识别结果分析 .xls"));
            for (OrderVO orderVO :
                    list) {

                //解析有效样本
                if (orderVO.getSimple_or_difficult().equals("简单样本")) {
                    list_simple.add(orderVO);
                    number_of_simple_samples++;
                } else if (orderVO.getSimple_or_difficult().equals("困难样本")) {
                    list_difficulty.add(orderVO);
                    number_of_difficulty_samples++;
                }

                if (orderVO.getSimple_or_difficult().equals("简单样本") || orderVO.getSimple_or_difficult().equals("困难样本")) {
                    list_new.add(orderVO);
                    number_of_samples++;
                }
            }
            System.out.println(number_of_samples);

            //整体样本
            Iterator<Map.Entry<String, ExcelT>> entries = excelTMap.entrySet().iterator();

            while (entries.hasNext()) {
                Map.Entry<String, ExcelT> entry = entries.next();
                String key = entry.getKey();

                ExcelT theWholeSample = getTheWholeSample(list_new, number_of_samples, Double.parseDouble(key));
                excelTMap.put(key, theWholeSample);

                System.out.println(entry.getKey() + "    " + entry.getValue());
            }

            //简单样本解析
            System.out.println("______________________________________");

            Iterator<Map.Entry<String, ExcelT>> entries2 = excelTMapSimple.entrySet().iterator();
            while (entries2.hasNext()) {
                Map.Entry<String, ExcelT> entry2 = entries2.next();
                String key = entry2.getKey();

                ExcelT theWholeSample = getTheWholeSample(list_simple, number_of_simple_samples, Double.parseDouble(key));
                excelTMapSimple.put(key, theWholeSample);

                System.out.println(entry2.getKey() + "    " + entry2.getValue());
            }


            //复杂样本解析
            System.out.println("______________________________________");

            Iterator<Map.Entry<String, ExcelT>> entries3 = excelTMapDifficult.entrySet().iterator();

            while (entries3.hasNext()) {
                Map.Entry<String, ExcelT> entry3 = entries3.next();
                String key = entry3.getKey();

                ExcelT theWholeSample = getTheWholeSample(list_difficulty, number_of_difficulty_samples, Double.parseDouble(key));
                excelTMapDifficult.put(key, theWholeSample);

                System.out.println(entry3.getKey() + "    " + entry3.getValue());
            }

        } catch (IOException e) {
            e.printStackTrace();

        }
    }

 // 通用样本  解析结果
    public static ExcelT getTheWholeSample(List<OrderVO> list, int number, double key) {
        ExcelT excelTList = new ExcelT();
        int correct_number = 0;
        double correct_rate;
        int recall_quantity = 0;
        double recall_rate;

        for (OrderVO orderVO :
                list) {

            //整体召回数量
            if (Double.parseDouble(orderVO.getConfidence_of_punished_object()) >= key && Double.parseDouble(orderVO.getInstrument_number_confidence()) >= key && Double.parseDouble(orderVO.getDate()) >= key) {
                excelTList.setZrecall_quantity(++recall_quantity);

                //整体正确数量
                if (orderVO.getIs_ture_punished_object().equals("1") && orderVO.getIs_ture_document_number().equals("1") && orderVO.getIs_ture_date().equals("1")) {
                    excelTList.setZcorrect_number(++correct_number);
                }

            }

            //案号召回数量
            if (Double.parseDouble(orderVO.getInstrument_number_confidence()) >= key) {
                excelTList.setArecall_quantity(excelTList.getArecall_quantity() + 1);

                //案号正确数量
                if (orderVO.getIs_ture_document_number().equals("1")) {
                    excelTList.setAcorrect_number(excelTList.getAcorrect_number() + 1);
                }
            }

            //处罚对象召回数量
            if (Double.parseDouble(orderVO.getConfidence_of_punished_object()) >= key) {
                excelTList.setCrecall_quantity(excelTList.getCrecall_quantity() + 1);

                //处罚对象正确数量
                if (orderVO.getIs_ture_punished_object().equals("1")) {
                    excelTList.setCcorrect_number(excelTList.getCcorrect_number() + 1);
                }
            }

            //日期召回数量
            if (Double.parseDouble(orderVO.getDate()) >= key) {
                excelTList.setRrecall_quantity(excelTList.getRrecall_quantity() + 1);

                //日期正确数量
                if (orderVO.getIs_ture_date().equals("1")) {
                    excelTList.setRcorrect_number(excelTList.getRcorrect_number() + 1);
                }
            }
        }

        recall_rate = (recall_quantity + 0.0) / number;
        excelTList.setZrecall_rate(getPercent(recall_rate));

        correct_rate = (correct_number + 0.0) / recall_quantity;
        excelTList.setZcorrect_rate(getPercent(correct_rate));


        excelTList.setAcorrect_rate(getPercent(excelTList.getAcorrect_number() / excelTList.getArecall_quantity()));
        excelTList.setArecall_rate(getPercent(excelTList.getArecall_quantity() / number));

        excelTList.setCcorrect_rate(getPercent(excelTList.getCcorrect_number() / excelTList.getCrecall_quantity()));
        excelTList.setCrecall_rate(getPercent(excelTList.getCrecall_quantity() / number));

        excelTList.setRcorrect_rate(getPercent(excelTList.getRcorrect_number() / excelTList.getRrecall_quantity()));
        excelTList.setRrecall_rate(getPercent(excelTList.getRrecall_quantity() / number));


        return excelTList;
    }


    /**
     * Double 类型转换为 ％字符串
     *
     * @param rate 传入double
     * @return 字符串 百分比
     */
    public static String getPercent(double rate) {

        NumberFormat nf = NumberFormat.getPercentInstance();
        nf.setMinimumFractionDigits(2);//设置保留小数位
        nf.setRoundingMode(RoundingMode.HALF_UP); //设置舍入模式
        String percent = nf.format(rate);

        return percent;
    }

    /**
     * 读取excel解析工具
     *
     */
    public static List<OrderVO> readXls(File file) throws IOException {
        List<OrderVO> list = new ArrayList<OrderVO>();
        try {
            //XSSF和HSSF虽然在不同的包里，但却引用了同一接口Workbook，可以用下面判断
            Workbook wb = null;
            FileInputStream fi = new FileInputStream(file);
//            if (file.getName().toLowerCase().endsWith("xls")) {
//                wb = new HSSFWorkbook(fi);
//            } else if (file.getName().toLowerCase().endsWith("xlsx")) {
//                wb = new XSSFWorkbook(fi);
//            }
            wb = new XSSFWorkbook(fi);

            //获取工作表 0号
            Sheet sheet = wb.getSheetAt(0);

            int rowNum = sheet.getLastRowNum() + 1;
            //i 从1开始表示第一行为标题 不包含在数据中
            for (int i = 2; i < rowNum; i++) {
                OrderVO systemObject = new OrderVO();
                Row row = sheet.getRow(i);
                int cellNum = row.getLastCellNum();
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    String cellValue = null;
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.getStringCellValue();
                    }

                    switch (j) {//通过列数来判断对应插如的字段
                        //数据中不应该保护ID这样的主键记录
                        case 1:
                            systemObject.setSimple_or_difficult(cellValue);
                            break;
                        case 2:
                            systemObject.setSample_id(cellValue);
                            break;
                        case 3:
                            systemObject.setInstrument_number_confidence(cellValue);
                            break;
                        case 4:
                            systemObject.setIs_ture_document_number(cellValue);
                            break;
                        case 5:
                            systemObject.setConfidence_of_punished_object(cellValue);
                            break;
                        case 6:
                            systemObject.setIs_ture_punished_object(cellValue);
                            break;
                        case 7:
                            systemObject.setDate(cellValue);
                            break;
                        case 8:
                            systemObject.setIs_ture_date(cellValue);
                            break;
                    }
                }
                list.add(systemObject);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

输出到本地方法

    public void getWin(Map<String, ExcelT> map, String name, int id) {
        List<ExcelT> list = new ArrayList();

        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream("C:\\Users\\jmwang.erics\\Desktop\\下.xls");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        Iterator<Map.Entry<String, ExcelT>> entries = map.entrySet().iterator();

        while (entries.hasNext()) {
            Map.Entry<String, ExcelT> entry = entries.next();

            ExcelT value = entry.getValue();
            list.add(value);
        }

        downLoadToExcel(outputStream, list, name, id);
    }

    //结果返回的是写入的记录数（以下用的是自己业务场景数据）
    public static int downLoadToExcel(OutputStream outputStream, List<ExcelT> paimaiMoneyVOList, String name, int id) {
//文档对象
        HSSFWorkbook wb = new HSSFWorkbook();
        int rowNum = 0;
        //wb.setSheetName(id,name);
        HSSFSheet sheet = wb.createSheet(name);
        CellRangeAddress cellAddresses = new CellRangeAddress(0, 0, 0, 18);
        sheet.addMergedRegion(cellAddresses);

        // 工作表 格式
        HSSFCellStyle sheetStyle = wb.createCellStyle();
        // 设置列的样式
        for (int i = 0; i <= 18; i++) {
            sheet.setDefaultColumnStyle((short) i, sheetStyle);
        }
        //设置字体
        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);
        //font.setBold(true);

        sheetStyle.setFont(font);
        sheetStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        sheetStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中
        sheetStyle.setLocked(true);
        sheetStyle.setWrapText(true); // 自动换行

        //sheetStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex()); // 左边框的颜色



        Row row0 = sheet.createRow(rowNum++);
        //设置内容
        row0.createCell(0).setCellValue(name);
        if (paimaiMoneyVOList != null && paimaiMoneyVOList.size() > 0) {
            for (ExcelT paimaiMoneyVO : paimaiMoneyVOList) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(1).setCellValue(paimaiMoneyVO.getZcorrect_number());
                row.createCell(2).setCellValue(paimaiMoneyVO.getZcorrect_rate());
                row.createCell(3).setCellValue(paimaiMoneyVO.getZrecall_quantity());
                row.createCell(4).setCellValue(paimaiMoneyVO.getZrecall_rate());
                row.createCell(5).setCellValue(paimaiMoneyVO.getAcorrect_number());
                row.createCell(6).setCellValue(paimaiMoneyVO.getAcorrect_rate());
                row.createCell(7).setCellValue(paimaiMoneyVO.getArecall_quantity());
                row.createCell(8).setCellValue(paimaiMoneyVO.getArecall_rate());
                row.createCell(9).setCellValue(paimaiMoneyVO.getCcorrect_number());
                row.createCell(10).setCellValue(paimaiMoneyVO.getCcorrect_rate());
                row.createCell(11).setCellValue(paimaiMoneyVO.getCrecall_quantity());
                row.createCell(12).setCellValue(paimaiMoneyVO.getCrecall_rate());
                row.createCell(13).setCellValue(paimaiMoneyVO.getRcorrect_number());
                row.createCell(14).setCellValue(paimaiMoneyVO.getRcorrect_rate());
                row.createCell(15).setCellValue(paimaiMoneyVO.getRrecall_quantity());
                row.createCell(16).setCellValue(paimaiMoneyVO.getRrecall_rate());
            }
        }
        try {
            wb.write(outputStream);
            LOGGER.info("表数据写入到excel表成功,一共写入了" + (rowNum - 1) + "条数据");
            outputStream.close();
        } catch (IOException e) {
            LOGGER.error("流关闭异常！", e);
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    LOGGER.error("流关闭异常！", e);
                }
            }
        }
        return rowNum - 1;
    }