ECR excel导出

whycxzp

2023-02-21 8db4d00b41728e5a25cbb7b661d502ecd570e88d

ECR excel导出

 src/main/java/com/whyc/controller/ECRController.java

@@ -11,6 +11,7 @@
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.ParseException;
import java.util.Date;
@@ -61,4 +62,10 @@
        }
        return service.searchEcr(number,subCode,subModel,testTime1,testTime2,pageCurr,pageSize);
    }

    @ApiOperation("导出所有记录")
    @GetMapping("exportExcel")
    public void exportExcel(HttpServletResponse response){
        service.exportExcel(response);
    }
}

 src/main/java/com/whyc/service/ECRService.java

@@ -8,6 +8,7 @@
import com.whyc.pojo.ECR;
import com.whyc.util.CommonUtil;
import com.whyc.util.DateUtil;
import com.whyc.util.ExcelUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
@@ -19,10 +20,12 @@
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
@@ -254,4 +257,31 @@
        List list=mapper.selectList(wrapper);
        return list;
    }

    public void exportExcel(HttpServletResponse response) {
        //查询所有
        List<ECR> ecrList = mapper.selectList(null);
        int size = ecrList.size();
        //ECR编号,申请日期,申请人,变更描述,变更料号,变更型号,变更所属型号,处理方式,创建时间
        String[] title = new String[]{"年份","ECR编号","申请日期","申请人","变更描述","变更料号","变更型号","变更所属型号","处理方式","创建时间"};
        String[][] values = new String[size][]; //size行
        for (int i = 0; i < size; i++) {
            values[i] = new String[10];
            ECR ecr = ecrList.get(i);
            Date proposeDate = ecr.getProposeDate();
            Calendar instance = Calendar.getInstance();
            instance.setTime(proposeDate);
            values[i][0] = String.valueOf(instance.get(Calendar.YEAR));
            values[i][1] = ecr.getNumber();
            values[i][2] = DateUtil.YYYY_MM_DD_UNION.format(proposeDate);
            values[i][3] = ecr.getProposer();
            values[i][4] = ecr.getChangeDescription();
            values[i][5] = ecr.getSubCode();
            values[i][6] = ecr.getSubModel();
            values[i][7] = ecr.getParentModel();
            values[i][8] = ecr.getSolution();
            values[i][9] = DateUtil.YYYY_MM_DD_HH_MM_SS.format(ecr.getCreateTime());
        }
        ExcelUtil.exportExcel("变更记录清单","清单",title,values,null,response);
    }
}

 src/main/java/com/whyc/util/ExcelUtil.java

@@ -1,6 +1,7 @@
package com.whyc.util;

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

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@@ -19,6 +20,8 @@
        }
        //第二步，在webbook中添加一个sheet，即excel的表单
        HSSFSheet sheet = wb.createSheet(sheetName);
        CellRangeAddress rangeAddress = CellRangeAddress.valueOf("A1:J1");
        sheet.setAutoFilter(rangeAddress);
        //设置列宽度
        //判断是否为空
        if (values != null && values.length > 0) {
@@ -28,6 +31,13 @@
        }
        //第三步，在sheet中添加表头第0行，即excel的行
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleCellStyle.setFont(font);
        row.setRowStyle(titleCellStyle);

        //第四步，创建单元格，并设置值表头，设置表头居中，即excel格子单元
        HSSFCellStyle style = wb.createCellStyle();
        //居中格式
@@ -45,7 +55,7 @@
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i].toString());
            cell.setCellStyle(style);
            cell.setCellStyle(titleCellStyle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
@@ -82,13 +92,13 @@
    }

    //构造指定的单元格样式
    public static HSSFCellStyle createCellStyle(HSSFWorkbook wb,String fontType,int fontSize,boolean center,boolean blod){
    public static HSSFCellStyle createCellStyle(HSSFWorkbook wb,String fontType,int fontSize,boolean center,boolean bold){

        HSSFCellStyle cellStyle = wb.createCellStyle();                                                                        //单元格样式
        HSSFFont font = wb.createFont();
        font.setFontName(fontType);
        font.setFontHeightInPoints((short) fontSize);                                                                        //设置字体大小
        font.setBoldweight(blod?HSSFFont.BOLDWEIGHT_BOLD:HSSFFont.BOLDWEIGHT_NORMAL);                                        //粗体显示
        font.setBoldweight(bold?HSSFFont.BOLDWEIGHT_BOLD:HSSFFont.BOLDWEIGHT_NORMAL);                                        //粗体显示
        cellStyle.setFont(font);                                                                                               //标题样式
        cellStyle.setAlignment(center?HSSFCellStyle.ALIGN_CENTER_SELECTION:HSSFCellStyle.ALIGN_LEFT);                         // 居中
        return cellStyle;