java使用模板导出Excel表格

java使用模板导出Excel表格

---

---

模板示例

---

一、引入依赖

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

二、使用步骤

1.添加工具类

package com.test.common.utils;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.test.aliyunApi.HuaweiyunOss;
import com.test.skycloud.consts.DeleteFlag;
import com.test.skycloud.modules.foodTraceability.entity.FoodTraceability;
import com.test.skycloud.modules.foodTraceability.service.IFoodTraceabilityService;
import com.test.skycloud.modules.foodTraceability.vo.FoodTraceabilityQueryVo;
import com.test.skycloud.modules.foodTraceability.vo.HSSFWorkVo;
import com.test.skycloud.modules.manufacturerInformation.entity.ManufacturerInformation;
import com.test.skycloud.modules.manufacturerInformation.mapper.ManufacturerInformationMapper;
import com.test.skycloud.modules.system.entity.Category;
import com.test.skycloud.modules.system.service.ICategoryService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class ExcelUtil {

    @Autowired
    private static HuaweiyunOss huaweiyunOss;
    @Autowired
    private static IFoodTraceabilityService foodTraceabilityService;
    @Autowired
    private static ICategoryService categoryService;
    @Autowired
    private static ManufacturerInformationMapper manufacturerInformationMapper;


    public static void addSheet(HSSFWorkbook wb, HSSFSheet sheet, List<HSSFWorkVo> hssfWorkVoList
    ) throws IOException {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        sheet.setColumnWidth(6, 6000);
        sheet.setColumnWidth(7, 6000);
        sheet.setColumnWidth(8, 6000);
        sheet.setColumnWidth(8, 6000);
        int i = 0;
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        for (HSSFWorkVo data : hssfWorkVoList) {//基本信息\第一模块 行，列
            //第一模块
            i++;
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            //heightInPoints 设置的值永远是height属性值的30倍
            row.setHeightInPoints(30);
            row.createCell(0).setCellValue(i);
            row.createCell(1).setCellValue(data.getManufacturerName());
            row.createCell(2).setCellValue(data.getManufacturerContacts());
            row.createCell(3).setCellValue(data.getManufacturerPhone());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            if (null != data.getEntryTime()) {
                row.createCell(4).setCellValue(sdf.format(data.getEntryTime()));
            }
            row.createCell(5).setCellValue(data.getFoodName());
            row.createCell(6).setCellValue(data.getUserName());
            row.createCell(7).setCellValue(data.getCategoryDishesName());
            row.createCell(8).setCellValue(data.getDescription());
        }
    }

    /**
     * 导出--返回下载URL
     * @param wb
     * @param fileName
     * @return
     * @throws Exception
     */
    public static Response<?> export(HSSFWorkbook wb, String fileName) throws Exception {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wb.write(bos);
        byte[] bytes = bos.toByteArray();
        InputStream is = new ByteArrayInputStream(bytes);
        boolean b = huaweiyunOss.uploadFile(fileName, HuaweiyunOss.FileType.EXPORT_FOOD_TRACEABILITY, is);
        bos.close();
        is.close();
        if (b) {
            String downloadUrl1 = huaweiyunOss.getDownloadUrl(fileName, HuaweiyunOss.FileType.EXPORT_FOOD_TRACEABILITY);
            return Response.success(downloadUrl1);
        }
        return Response.success();
    }

    /**
     * 直接导出
     * @param response
     * @param wb
     * @param fileName
     * @throws IOException
     */
    private static void export(HttpServletResponse response, HSSFWorkbook wb, String fileName) throws IOException {
        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
        if (StringUtils.isNotBlank(fileName) && StringUtils.isNotBlank(fileName)) {
            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
        }
        wb.write(output);
        output.close();
    }

    /**
     * 填入数据 -- 导出数据
     *
     * @param wb
     */
    public static void insert(HSSFWorkbook wb, FoodTraceabilityQueryVo queryVo) throws IOException {
        //获取列表全部数据
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar dateNow = Calendar.getInstance();
        dateNow.add(Calendar.DAY_OF_MONTH, -1);
        Date time = dateNow.getTime();
        String format = sdf.format(time);
        HSSFSheet sheet = wb.cloneSheet(0);
        //sheet名称
        wb.setSheetName(1, format);
        //获取每一个列表对应的详情数据

        QueryWrapper<FoodTraceability> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(FoodTraceability::getDeleteFlag, DeleteFlag.NORMAL.getStatus());
        if(StringUtils.isNotEmpty(queryVo.getSchoolId())){
            queryWrapper.lambda().eq(FoodTraceability::getSchoolId, queryVo.getSchoolId());
        }
        queryWrapper.lambda().orderByDesc(FoodTraceability::getCreateTime);
        List<FoodTraceability> foodTraceabilityList = foodTraceabilityService.list(queryWrapper);

        QueryWrapper<Category> queryW = new QueryWrapper<>();
        queryW.lambda().eq(Category::getDeleteFlag, DeleteFlag.NORMAL.getStatus());
        Map<String, String> categoryMap = categoryService.list(queryW).parallelStream().collect(Collectors.toMap(Category::getId, Category::getName));

        Set<String> manufactorIds = foodTraceabilityList.parallelStream().map(FoodTraceability::getManufactorId).collect(Collectors.toSet());
        Map<String, ManufacturerInformation> manufactorNames = manufactorIds.isEmpty() ? Collections.emptyMap() : manufacturerInformationMapper.selectBatchIds(manufactorIds).parallelStream().collect(Collectors.toMap(ManufacturerInformation::getId, d -> d));

        List<HSSFWorkVo> list = new ArrayList<>();
        foodTraceabilityList.stream().forEach(d -> {
            HSSFWorkVo hssfWorkVo = new HSSFWorkVo();
            if (StringUtils.isNotEmpty(d.getAutograph())) {
                hssfWorkVo.setUserName(d.getUserName());//录入人员
            }
            hssfWorkVo.setCategoryDishesName(categoryMap.get(d.getCategoryDishes()));
            hssfWorkVo.setDescription(d.getDescription());
            hssfWorkVo.setEntryTime(d.getEntryTime());
            hssfWorkVo.setFoodName(d.getFoodName());
            //获取厂家信息
            ManufacturerInformation manufacturerInformation = manufactorNames.get(d.getManufactorId());
            if (null != manufacturerInformation) {
                hssfWorkVo.setManufacturerName(manufacturerInformation.getManufacturerName());
                hssfWorkVo.setManufacturerContacts(manufacturerInformation.getManufacturerContacts());
                hssfWorkVo.setManufacturerPhone(manufacturerInformation.getManufacturerPhone());
            }
            hssfWorkVo.setPhysicalPhotos(d.getPhysicalPhotos());
            hssfWorkVo.setQualityInspectionReport(d.getQualityInspectionReport());
            list.add(hssfWorkVo);
        });
        ExcelUtil.addSheet(wb, sheet, list);//把数据加入sheet中
    }


}

2.导出Excel

@ApiOperation(value = "导出")
    @PostMapping("/export")
    public Response<?> export(@RequestBody FoodTraceabilityQueryVo query) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar dateNow = Calendar.getInstance();
        dateNow.add(Calendar.DAY_OF_MONTH, -1);
        Date time = dateNow.getTime();
        String format = sdf.format(time);
        String fileName = format + ".xls";
        //下载模板
        byte[] bytes = huaweiyunOss.downloadFile("test.xls", HuaweiyunOss.FileType.EXPORT_FOOD_TRACEABILITY);
        if (null == bytes) {
            return Response.error();
        }
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
        POIFSFileSystem ps = new POIFSFileSystem(byteArrayInputStream);
        HSSFWorkbook wb = new HSSFWorkbook(ps);
        //查询数据并且插入
        ExcelUtil.insert(wb,query);
        wb.removeSheetAt(0);
        return ExcelUtil.export(wb, fileName);
    }

---