POI 生成、导出Excel（包含多个sheet）带图片

1、导入依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

　　　　 <dependency>
   　　　　　　<groupId>org.projectlombok</groupId>
   　　　　　　<artifactId>lombok</artifactId>
   　　　　　　<optional>true</optional>
　　　　 </dependency>

2、封装实体类

excelEntity——对应一个excel文件

package com.example.mytest.demo.entity;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.List;

/**
 * @author jieke
 * @date 2018/8/9 10:58
 * @Description:
 */
@Getter
@Setter
@ToString
public class ExcelEntity {
    private List<SheetEntity> sheetEntityList;
    private String excelName;
}

SheetEntity——对应excel中的一个sheet

package com.example.mytest.demo.entity;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.io.ByteArrayOutputStream;
import java.util.List;

/**
 * @author jieke
 * @date 2018/8/9 10:46
 * @Description:
 */
@Getter
@Setter
@ToString
public class SheetEntity {
    private String sheetName;
    private List list;//每一行数据实体类list
    private ByteArrayOutputStream byteArrayOut;//图片
}

Student——对应表格的一行数据

package com.example.mytest.demo.repository.entity;

import com.example.mytest.demo.annotation.Column;
import lombok.Data;

import javax.persistence.*;

/**
 * @author jieke
 * @date 2018/8/2 11:09
 * @Description:
 */
@Data
@Entity
public class Student {

    @Column("学生id")
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column("学校id")
    private Integer schoolId;

    @Column("学生姓名")
    private String name;

    @Column("学生年龄")
    private Integer age;

    @Column("学生性别")
    private Integer sex;

    @Column("班级")
    private Integer studentClass;


}

自定义注解：@Column

package com.example.mytest.demo.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author jieke
 * @date 2018/8/9 9:55
 * @Description:
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {
    String value() default "";
}

3、POIServiceImpl

package com.example.mytest.demo.service.impl;

import com.example.mytest.demo.entity.ExcelEntity;
import com.example.mytest.demo.entity.SheetEntity;
import com.example.mytest.demo.repository.entity.Student;
import com.example.mytest.demo.service.POIService;
import com.example.mytest.demo.util.PoiUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author jieke
 * @date 2018/8/8 17:50
 * @Description:
 */
@Service
public class POIServiceImpl implements POIService{
    @Override
    public void downLoad(HttpServletResponse response) {

//////////////////////////////////////////////////////////////////////////////
　　　　　　　　          ////////创建测试数据/////////

        // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        try {
            BufferedImage bufferImg = ImageIO.read(new File("C:/Users/jieke/Desktop/1.jpg"));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
        } catch (IOException e) {
            e.printStackTrace();
        }


        Student student = new Student();
        student.setId(1L);
        student.setSchoolId(1);
        student.setName("name");
        student.setAge(1);
        student.setSex(1);
        student.setStudentClass(1);


        List<Student> list = new ArrayList<>();
        list.add(student);
        list.add(student);

        SheetEntity sheet1 = new SheetEntity();
        sheet1.setList(list);
        sheet1.setSheetName("sheet1");
        sheet1.setByteArrayOut(byteArrayOut);

        SheetEntity sheet2 = new SheetEntity();
        sheet2.setList(list);
        sheet2.setSheetName("sheet2");

        List<SheetEntity> sheetEntityList = new ArrayList<>();

        sheetEntityList.add(sheet1);
        sheetEntityList.add(sheet2);

        ExcelEntity excelEntity = new ExcelEntity();
        excelEntity.setExcelName("测试");
        excelEntity.setSheetEntityList(sheetEntityList);

///////////////////////////////////////////////////////////////////////////

        // 第一步，创建一个workbook，对应一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();

        for (SheetEntity sheetEntity:excelEntity.getSheetEntityList()) {
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = workbook.createSheet(sheetEntity.getSheetName());

            //第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);

            //第四步，创建单元格，并设置值表头 设置表头居中
            PoiUtil.createCell(workbook, row, sheetEntity.getList().get(0).getClass());

            // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
            PoiUtil.setCellValue(sheetEntity.getList(),row,sheet);

            //第六步，插入图片
            PoiUtil.insertPicture(sheet,workbook,sheetEntity);
        }
        // 第七步，下载excel
        PoiUtil.downLoadExcel(response,excelEntity.getExcelName(),workbook);
    }
}

4、util类
PoiUtil

package com.example.mytest.demo.util;

import com.example.mytest.demo.annotation.Column;
import com.example.mytest.demo.entity.SheetEntity;
import com.example.mytest.demo.repository.entity.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author jieke
 * @date 2018/8/9 10:11
 * @Description:PoiUtil
 */
public class PoiUtil {

    //创建单元格，并设置值表头 设置表头居中
    public static void createCell(HSSFWorkbook workbook,HSSFRow row, Class<?> c) {

        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        HSSFCell cell;
        Field[] fields = c.getDeclaredFields();
        for (int i=0 ; i < fields.length ; i++) {
            cell = row.createCell((short) i);
            cell.setCellValue(fields[i].getAnnotation(Column.class).value());
            cell.setCellStyle(style);
        }
    }

    // 通过反射写入实体数据
    public static void setCellValue(List list, HSSFRow row, HSSFSheet sheet) {
        for (int i = 0; i < list.size(); i++)
        {
            row = sheet.createRow(i + 1);
            Object o = list.get(i);
            Field[] declaredFields = Student.class.getDeclaredFields();
            Map<String, Object> map = BeanUtil.ObjectToMap(o);
            for(int j = 0; j < declaredFields.length ; j++){
                if (declaredFields[j].getType() == String.class){
                    row.createCell(j).setCellValue((map.get(declaredFields[j].getName())).toString());
                }else if(declaredFields[j].getType() == Integer.class){
                    row.createCell(j).setCellValue(Integer.parseInt(map.get(declaredFields[j].getName()).toString()));
                }else if(declaredFields[j].getType() == Long.class){
                    row.createCell(j).setCellValue(Long.parseLong(map.get(declaredFields[j].getName()).toString()));
                }else if(declaredFields[j].getType() == Date.class){
                    try {
                        row.createCell(j).setCellValue(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(declaredFields[j].getName()).toString()));
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }else{
                    row.createCell(j).setCellValue(map.get(declaredFields[j].getName()).toString());
                }
            }
        }
    }

    //下载生成的excel到本地
    public static void downLoadExcel(HttpServletResponse response, String excelName, HSSFWorkbook workbook) {
        try (OutputStream out = response.getOutputStream()) {
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(String.valueOf(URLEncoder.encode(excelName + ".xls", "UTF-8"))));
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //excel中插入图片
    public static void insertPicture(HSSFSheet sheet, HSSFWorkbook workbook, SheetEntity sheetEntity) {
        if(sheetEntity.getByteArrayOut()!=null){
            // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            // anchor主要用于设置图片的属性
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, sheetEntity.getList().size()+1, (short) 5, 7+10);
            // 插入图片
            patriarch.createPicture(anchor, workbook.addPicture(sheetEntity.getByteArrayOut().toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        }
    }
}

BeanUtil

package com.example.mytest.demo.util;

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

/**
 * @author jieke
 * @date 2018/8/8 18:47
 * @Description:ObjectToMap
 */
public class BeanUtil {

    public static Map<String,Object> ObjectToMap(Object object){
        if(object==null){
            return null;
        }
        Map<String,Object> map = new HashMap<>();
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field:fields) {
            field.setAccessible(true);
            try {
                map.put(field.getName(), field.get(object));
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return map;
    }
}

posted on 2018-08-10 15:20 明天过后！阅读(550) 评论(0) 收藏举报

刷新页面返回顶部

明天过后

公告