POI导出Excel工具类

package com.jesse.commons;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.lang.reflect.Field;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Set;

/**
 * @author wpx
 * @date: 2019/10/21
 */
public class ExportExcelUtil {

    /**
     * 固定列宽
     */
    private static final Integer WIDTH = 5120;

    /**
     * 导出excel
     * @param title  sheet标题
     * @param rowNames 第一行
     * @param dataList 数据列表
     * @param <T> 封装数据
     * @return
     * @throws Exception
     */
    public static <T> XSSFWorkbook export(String title, LinkedHashMap<String, String> rowNames, List<T> dataList) throws Exception{
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(title);
        //产生标题行
        XSSFRow rowm = sheet.createRow(0);
        XSSFCell cellTitle = rowm.createCell(0);

        //sheet样式定义【】
        XSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowNames.size() - 1)));
        cellTitle.setCellStyle(columnTopStyle);
        cellTitle.setCellValue(title);

        // 定义所需列数
        int columnNum = rowNames.size();
        XSSFRow rowRowName = sheet.createRow(2);

        // 将列头设置到sheet的单元格中
        Set<String> keySet = rowNames.keySet();
        Iterator<String> iterator = keySet.iterator();
        for (int n = 0; n < columnNum; n++) {
            XSSFCell cell = rowRowName.createCell(n);
            sheet.setColumnWidth(n, WIDTH);
            XSSFRichTextString text = new XSSFRichTextString(rowNames.get(iterator.next()));
            cell.setCellValue(text);
            cell.setCellStyle(columnTopStyle);
        }

        // 将查询到的数据设置到sheet对应的单元格中
        XSSFCellStyle style = getStyle(workbook);
        for (int i = 0; i < dataList.size(); i++) {
            T t = dataList.get(i);
            XSSFRow row = sheet.createRow(i + 3);
            Class<?> aClass = t.getClass();
            int cellCount = 0;
            for (String s : keySet) {
                XSSFCell cell = row.createCell(cellCount);
                Field field = aClass.getDeclaredField(s);
                field.setAccessible(true);
                cell.setCellValue(field.get(t) == null ? "" : field.get(t).toString());
                cell.setCellStyle(style);
                cellCount++;
            }
        }
        return workbook;
    }

    /**
     * 列头单元格样式
     * @param workbook
     * @return
     */
    public static XSSFCellStyle getColumnTopStyle(XSSFWorkbook workbook) {
        // 设置字体
        XSSFFont font = workbook.createFont();

        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBold(true);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式
        XSSFCellStyle style = workbook.createCellStyle();
        // 在样式中应用设置的字体
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;

    }

    /**
     * 其余列样式
     * @param workbook
     * @return
     */
    public static XSSFCellStyle getStyle(XSSFWorkbook workbook) {
        // 设置字体
        XSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 10);
        // 字体加粗
        font.setBold(true);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        XSSFCellStyle style = workbook.createCellStyle();
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(true);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }



}

实体类:

package com.jesse.dao.entity;


/**
 * @author wpx
 * @date: 2019/10/21
 */
public class ResubmitData {
    public ResubmitData(String enName, String name, String mobile, String classCode, String courseType, String resubmitPerson, String classTeacherName, String appealData, String reviewDate) {
        this.enName = enName;
        this.name = name;
        this.mobile = mobile;
        this.classCode = classCode;
        this.courseType = courseType;
        this.resubmitPerson = resubmitPerson;
        this.classTeacherName = classTeacherName;
        this.appealData = appealData;
        this.reviewDate = reviewDate;
    }

    /**
     * 学员英文名
     */
     private String enName;
    /**
     * 学员中文名
     */
     private String name;
    /**
     * 学员手机号
     */
     private String mobile;
    /**
     * 班级编号
     */
     private String classCode;
    /**
     * 课程类型
     */
     private String courseType;
    /**
     * 续报关系人
     */
     private String resubmitPerson;
    /**
     * 班级班主任
     */
     private String classTeacherName;
    /**
     * 是否为申诉数据
     */
     private String appealData;
    /**
     * 审核时间
     */
     private String reviewDate;

    public String getEnName() {
        return enName;
    }

    public void setEnName(String enName) {
        this.enName = enName;
    }

    public String getName() {
        return name;
    }

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

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getClassCode() {
        return classCode;
    }

    public void setClassCode(String classCode) {
        this.classCode = classCode;
    }

    public String getCourseType() {
        return courseType;
    }

    public void setCourseType(String courseType) {
        this.courseType = courseType;
    }

    public String getResubmitPerson() {
        return resubmitPerson;
    }

    public void setResubmitPerson(String resubmitPerson) {
        this.resubmitPerson = resubmitPerson;
    }

    public String getClassTeacherName() {
        return classTeacherName;
    }

    public void setClassTeacherName(String classTeacherName) {
        this.classTeacherName = classTeacherName;
    }

    public String getAppealData() {
        return appealData;
    }

    public void setAppealData(String appealData) {
        this.appealData = appealData;
    }

    public String getReviewDate() {
        return reviewDate;
    }

    public void setReviewDate(String reviewDate) {
        this.reviewDate = reviewDate;
    }

    @Override
    public String toString() {
        return super.toString();
    }
}

测试类:

public class PoiTest {
@Test
public void test3() {
    String title = "续报数据列表";
    //首行
    LinkedHashMap<String, String> rowNames = new LinkedHashMap<>();
    rowNames.put("enName", "学员英文名");
    rowNames.put("name", "学员中文名");
    rowNames.put("mobile", "学员手机号");
    rowNames.put("classCode", "班级编号");
    rowNames.put("courseType", "课程类型");
    rowNames.put("resubmitPerson", "续报关系人");
    rowNames.put("classTeacherName", "班级班主任");
    rowNames.put("appealData", "是否为申诉数据");
    rowNames.put("reviewDate", "审核时间");
    List<ResubmitData> resubmitDatas = new ArrayList<>();
    for (int i = 0 ; i < 5; i++) {
        String value = i + "";
        ResubmitData resubmitData = new ResubmitData("123456789258656255165456456", value, value, value, value, value, value, value, null);
        resubmitDatas.add(resubmitData);
    }

    try {
        FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\wangp\\Desktop\\test.xlsx");
        XSSFWorkbook export = ExportExcelUtil.export(title, rowNames, resubmitDatas);
        export.write(fileOutputStream);
    } catch (Exception e) {
        e.printStackTrace();
    }

}

}

结果为:

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值