记录一下Excel 测试

1， maven 内容（由于整合 老系统，版本 较低 ）,最新的Excel 参考：GitHub - alibaba/easyexcel: 快速、简单避免OOM的java处理Excel工具

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.10-FINAL</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.10-FINAL</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

2.工具类

package com.study.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 生成ExcelUtils 工具
 */

@SuppressWarnings("all")
public class ExcelUtils {



    /**
     *  Excell  生成
     * @param dataLisst
     * @param sheetTitle
     * @return
     * @throws PlatException
     *
     */
    public static HSSFWorkbook getExcelHSSFWorkbook(Map<String, Object> headMap , List<Map<String, Object>> dataList , String sheetTitle) throws IOException {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet();
        hssfWorkbook.setSheetName(0, sheetTitle);

//         合并单元格 ，自定义抽取方法
//        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 2));


        // 设置 列头
        HSSFRow headRow = sheet.createRow(0);

        // 标题样式
        CellStyle headCellStyle = getHeadCellStyle(hssfWorkbook);

        Set<String> keySet = headMap.keySet();
        int index = 0;
        for (String key : keySet) {
            HSSFCell hssfCell = headRow.createCell(index);
            hssfCell.setCellStyle(headCellStyle);
            hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            hssfCell.setCellValue((String) headMap.get(key));
            index++;
        }

        // 数据单元格样式
        HSSFCellStyle dataCellStyle = getDataCellStyle(hssfWorkbook);

        //  填充 数据
        for (int i = 1; i <= dataList.size(); i++) {
            index = 0;
            HSSFRow row = sheet.createRow(i);
            Map<String, Object> resultMap = (Map<String, Object>) dataList.get(i - 1);
            for (Iterator keys = headMap.keySet().iterator(); keys.hasNext(); ) {
                HSSFCell cell = row.createCell(index);
                cell.setCellValue((String) resultMap.get(keys.next()));
                cell.setCellStyle(dataCellStyle);
                index++;
            }
        }



        return hssfWorkbook;
    }


    /**
     *  获取 标题单元格样式
     * @param workbook
     * @return
     */
    public static HSSFCellStyle getHeadCellStyle(HSSFWorkbook workbook){
        //  标题单元格样式
        HSSFCellStyle headCellStyle = (HSSFCellStyle)workbook.createCellStyle();
        headCellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
        headCellStyle.setWrapText(true); // 换行
        headCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
        headCellStyle.setFillPattern( CellStyle.SOLID_FOREGROUND);
        headCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        // 字体
        HSSFFont headFont = (HSSFFont) workbook.createFont();  // 使用实现类自己的字体，POI接口中可用性不好
        headFont.setFontName("仿宋");
        headFont.setFontHeightInPoints((short) 11); // 设置字体高度
        headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体
        headCellStyle.setFont(headFont);
        return headCellStyle ;
    }


    /**
     *     获取 数据单元格样式
     */
    public static HSSFCellStyle getDataCellStyle(Workbook workbook){
        //  数据单元格样式
        HSSFCellStyle dataCellStyle = (HSSFCellStyle) workbook.createCellStyle();
        dataCellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
        dataCellStyle.setWrapText(true); // 换行
        dataCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中



        // 字体
        Font dataFont = workbook.createFont();
        dataFont.setFontName("仿宋");
        dataFont.setFontHeightInPoints((short) 11);
        dataCellStyle.setFont(dataFont);

        return dataCellStyle ;
    }


}

3，测试类

package com.study.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.jupiter.api.Test;

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


@SuppressWarnings("all")
public class HSSFWorkbookTest {

    String path = "E:\\work\\workSpace\\2021-11-01\\easyexcel";


    /**
     * 07 版Excel
     *
     * @throws IOException
     */
    @Test
    public void test07() throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet firstSheet = workbook.createSheet("firstSheet");


        String[] ColumnNamme = {"COLUMN1", "COLUMN2", "COLUMN3", "COLUMN4", "COLUMN5", "COLUMN6", "COLUMN7", "COLUMN8",
                "COLUMN9", "COLUMN10", "COLUMN11", "COLUMN12", "COLUMN13", "COLUMN14", "COLUMN15", "COLUMN16", "COLUMN17",
                "COLUMN18"};

        String[] ColumnValue1 = {"序号  No.", "钢级  Steel Grade", "炉号 Heat No.", "批号  Batch No.",
                "钢板厚度(mm) Plate  Thickness", "试样编号 Specimen No.", "试样尺寸", "试样尺寸",
                "试样尺寸", "试样尺寸", "试样尺寸", "力学性能", "力学性能", "力学性能", "力学性能", "力学性能", "力学性能", "力学性能"};

        String[] ColumnValue2 = {"序号  No.", "钢级  Steel Grade", "炉号 Heat No.", "批号  Batch No.",
                "钢板厚度(mm) Plate  Thickness", "试样编号 Specimen No.", "厚", "宽",
                "面积", "标距长度", "标距长度", "Fe  （KN）", "Fm  （KN）", "Re", "Rm", "A", "冲击试验KV2", "冲击试验KV2"};
        String[] ColumnValue3 = {"序号 No.", "钢级  Steel Grade", "炉号 Heat No.", "批号  Batch No.",
                "钢板厚度(mm) Plate  Thickness", "试样编号 Specimen No.", "mm", "mm",
                "mm*mm", "mm", "mm", "Fe  （KN）", "Fm  （KN）", "Mpa", "Mpa", "%", "试验名称", "试验值"};
        String[] ColumnValue4 = {"序号 No.", "钢级  Steel Grade", "炉号 Heat No.", "批号  Batch No.",
                "钢板厚度(mm) Plate  Thickness", "试样编号 Specimen No.", "d0或a", "d1或b",
                "S0", "L0", "L1", "Fe  （KN）", "Fm  （KN）", "Mpa", "Mpa", "%", "试验名称", "试验值"};


        List<String[]> headList = new ArrayList<>();
        headList.add(ColumnValue1);
        headList.add(ColumnValue2);
        headList.add(ColumnValue3);
        headList.add(ColumnValue4);

        //   设置 列宽自适应
        for (int i = 0; i < ColumnValue1.length; i++) {
            firstSheet.autoSizeColumn(i);
            firstSheet.setColumnWidth(i, firstSheet.getColumnWidth(i) * 17 / 10);
        }

        // 列样式
        HSSFCellStyle headCellStyle = ExcelUtils.getHeadCellStyle(workbook);

        //  列头数组
        int ruwNum = 0;
        for (String[] columnName : headList) {
            HSSFRow row = firstSheet.createRow(ruwNum++);
            for (int i = 0; i < columnName.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(headCellStyle);
                cell.setCellValue(columnName[i]);
            }
        }


        //  设置多级列头

        firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 0, 0));
        firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 1, 1));
        firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 2, 2));
        firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 3, 3));
        firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 4, 4));
        firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 5, 5));
        // 试样尺寸 力学性能
        firstSheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 10));
        firstSheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 17));
        // mm  标距长度 mm
        firstSheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 7));
        firstSheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 10));
        firstSheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 10));
        //  Fe Fm
        firstSheet.addMergedRegion(new CellRangeAddress(1, 3, 11, 11));
        firstSheet.addMergedRegion(new CellRangeAddress(1, 3, 12, 12));
        // Mpa  %
        firstSheet.addMergedRegion(new CellRangeAddress(2, 3, 13, 14));
        firstSheet.addMergedRegion(new CellRangeAddress(2, 3, 15, 15));
        // 冲击试验
        firstSheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 17));
        firstSheet.addMergedRegion(new CellRangeAddress(2, 3, 16, 16));
        firstSheet.addMergedRegion(new CellRangeAddress(2, 3, 17, 17));

        //  数据

        List<Map<String, Object>> dataList = new ArrayList<>();
        Map<String, Object> dataMap = new HashMap<>();

        dataMap.put("COLUMN1", "981");
        dataMap.put("COLUMN2", "AB/DB36");
        dataMap.put("COLUMN3", "12F202");
        dataMap.put("COLUMN4", "H2135232");
        dataMap.put("COLUMN5", 12.0d);
        dataMap.put("COLUMN6", "F6984");
        dataMap.put("COLUMN7", 12.18);
        dataMap.put("COLUMN8", 25.26);
        dataMap.put("COLUMN9", 307.7);
        dataMap.put("COLUMN10", 98);
        dataMap.put("COLUMN11", 125.0);
        dataMap.put("COLUMN12", 131);
        dataMap.put("COLUMN13", 131);
        dataMap.put("COLUMN14", 170);
        dataMap.put("COLUMN15", 425);
        dataMap.put("COLUMN16", 27.5);
        dataMap.put("COLUMN17", "纵向-20Ckv");
        dataMap.put("COLUMN18", 150);

        for (int i = 0; i < 10; i++) {
            dataList.add(dataMap);
        }
        HSSFCellStyle dataCellStyle = ExcelUtils.getDataCellStyle(workbook);
        // 数据填充
        for (Map<String, Object> map : dataList) {
            HSSFRow row = firstSheet.createRow(ruwNum++);
            int cellIndex = 0;
            for (String key : ColumnNamme) {
                HSSFCell cell = row.createCell(cellIndex++);
                cell.setCellStyle(dataCellStyle);
                Class<?> aClass = map.get(key).getClass();
                if (aClass.equals(Double.class)) {
                    cell.setCellValue((Double) map.get(key));
                } else if (aClass.equals(String.class)) {
                    cell.setCellValue((String) map.get(key));
                } else if (aClass.equals(Integer.class)) {
                    cell.setCellValue((Integer) map.get(key));
                }

            }
        }


        // 生成Excel
        FileOutputStream outputStream = new FileOutputStream(path + "Excel-07.xls");
        workbook.write(outputStream);
        outputStream.close();
        System.out.println("excel 文件已经生成 ");
    }


}