poi HSSF（excel）工具类

 

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-excelant</artifactId>
    <version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-examples</artifactId>
    <version>3.14</version>
</dependency>

package com.wisdomwater.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PoiUtil {
    //标题大小
    final public static short TITLE_SIZE=20;
    //正文大小
    final public static short CONTENT_SIZE=10;

    /**
     * 设置poi通用样式
     * @param workbook
     * @return
     */
    public static HSSFCellStyle setCommonStyle(HSSFWorkbook workbook, short fontSize){
        HSSFCellStyle style=workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        style.setWrapText(true);//自动换行


        HSSFFont font = workbook.createFont();
//        font.setFontName("华文行楷");//设置字体名称
        font.setFontHeightInPoints(fontSize);//设置字号
//        font.setColor(HSSFColor.RED.index);//设置字体颜色
//        font.setUnderline(FontFormatting.U_SINGLE);//设置下划线
//        font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标
//        font.setStrikeout(true);//设置删除线
        style.setFont(font);

        return style;
    }

    /**
     * 通用标题,从第0行开始写 0-3行标题。第5行正文。已写死后可改。
     * @param workbook
     * @return
     */
    public static int writeTitle(HSSFWorkbook workbook, int col, String title, HSSFSheet sheet){
        HSSFCellStyle style = setCommonStyle(workbook, (short) TITLE_SIZE);

        HSSFRow row1 = sheet.createRow(1);
        HSSFCell cell10=row1.createCell(0);
        cell10.setCellValue(title);
        cell10.setCellStyle(style);
        CellRangeAddress region=new CellRangeAddress(1, 3, 0, col);
        sheet.addMergedRegion(region);
//        setRegionBorder(workbook,sheet,region);
        return 4;
    }

    /**
     * 设置单元格边框
     * @param style
     * @return
     */
    public static HSSFCellStyle setCellBorder(HSSFCellStyle style){
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        return style;
    }

    /**
     * 设置合并单元格边框
     * @param workbook
     * @param sheet
     * @param region
     */
    public static void setRegionBorder(HSSFWorkbook workbook, HSSFSheet sheet, CellRangeAddress region){
        RegionUtil.setBorderBottom(1,region,sheet,workbook);
        RegionUtil.setBorderTop(1,region,sheet,workbook);
        RegionUtil.setBorderLeft(1,region,sheet,workbook);
        RegionUtil.setBorderRight(1,region,sheet,workbook);
    }

    /**
     * 写表格
     * @param workbook
     * @param sheet
     * @param titles
     * @param list idx0:字段名，idx1:打印列名
     * @param next 起始行
     * @param startCol 起始列
     * @return 返回下一行ap
     */
    public static int writeTable(HSSFWorkbook workbook, HSSFSheet sheet, List<List<String>> titles, List<Map<String,String>> list, int next, int startCol){
        int size=titles.size();
        HSSFCellStyle style = setCommonStyle(workbook, (short) CONTENT_SIZE);
        setCellBorder(style);
        //写标题
        HSSFRow row = null;
        if(sheet.getRow(next)==null){
            row=sheet.createRow(next);
        }else{
            row=sheet.getRow(next);
        }
        for(int i=0;i<size;i++){
            String title=titles.get(i).get(1);
            HSSFCell cell = row.createCell(i+startCol);
            cell.setCellStyle(style);
            cell.setCellValue(title);
        }
        next++;

        //写内容
        for(int p=0;p<list.size();p++){
            Map<String,String> map=list.get(p);
            if(sheet.getRow(next)==null){
                row=sheet.createRow(next);
            }else{
                row=sheet.getRow(next);
            }
            for(int i=0;i<size;i++){
                String value=map.get(titles.get(i).get(0));
                HSSFCell cell = row.createCell(i+startCol);
                cell.setCellStyle(style);
                cell.setCellValue(value);
            }
            next++;
        }
        return next;
    }

    /**
     *
     * @param workbook
     * @param sheet
     * @param firstRow 区域起始行
     * @param lastRow 区域结束行
     * @param firstCol 区域起始列
     * @param lastCol 区域结束列
     * @param value 合并单元格的值
     * @param isBorder 是否有边框
     * @return 返回下一行
     */
    public static int mergeCell(HSSFWorkbook workbook, HSSFSheet sheet,
                                int firstRow,int lastRow,int firstCol,int lastCol, String value,boolean isBorder){

        HSSFCellStyle style = setCommonStyle(workbook, (short) CONTENT_SIZE);
        HSSFRow row = null;
        if(sheet.getRow(firstRow)==null){
            row=sheet.createRow(firstRow);
        }else{
            row=sheet.getRow(firstRow);
        }
        HSSFCell cell = row.createCell(firstCol);
        cell.setCellValue(value);
        cell.setCellStyle(style);

        //合并
        CellRangeAddress region=new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);

        if(isBorder){
            setRegionBorder(workbook,sheet,region);
        }

        return lastRow+1;
    }

    /**
     * //写入一个单元格
     * @param workbook
     * @param sheet
     * @param r 行
     * @param c 列
     * @param value 值
     * @param isBorder 是否有边
     */
    void setCell(HSSFWorkbook workbook, HSSFSheet sheet,int r,int c,String value,boolean isBorder){
        HSSFRow row = null;
        if(sheet.getRow(r)==null){
            row=sheet.createRow(r);
        }else{
            row=sheet.getRow(r);
        }
        HSSFCell cell = row.createCell(c);

        HSSFCellStyle style = setCommonStyle(workbook, CONTENT_SIZE);

        if(isBorder){
            setCellBorder(style);
        }
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }

    /**
     * 统一转换为Map<String,String>类型
     * @param obj
     * @param <T>
     * @return
     * @throws IllegalAccessException
     */
    public static <T> Map<String,String> convertMap(T obj) {
        HashMap<String,String> map=new HashMap<>();
        Class<?> clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();

        for(Field field:fields){
            field.setAccessible(true);
            String key= field.getName();
            try {
                String v = field.get(obj).toString();
                map.put(key, v);
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        return map;
    }

}