Java导出excel_ouyg-CSDN博客

本文链接：https://blog.csdn.net/zh555888/article/details/124817619

Java导出excel

	@ApiOperation(value = "导出冲压需求量计划")
    @PostMapping(value = "Export.do")
    public R exportPressNeed(HttpServletResponse response, @RequestBody PressNeedExportEntry entry) throws Exception {
        // 查询到出数据
        HashMap<String, Object> map = new HashMap<>();
        map.put("CAR_SERIES_CODE", entry.getCarSerialNo());
        map.put("PART_No", entry.getPartNo());
        map.put("PANEL_NAME", entry.getPartName());
        map.put("PLAN_TYPE", entry.getPlanType());
        map.put("beginTime", entry.getBeginTime());
        map.put("endTime", entry.getEndTime());
        map.put("YEAR_MONTY", entry.getYearMonth().replace("-", ""));
        // 查询需要导出的数据
        R pressNeed = biz.getPressNeed(map, 1, 200);
        ListResult result = (ListResult) pressNeed.getData().get("data");
        List rows = result.getRows();
        List<Map<String, Object>> list = new ArrayList<>();
        this.dealExplotList(rows, list);
        // 构建导出excel表头（第一行）
        String[] excelHeader = {"车型", "计划类型", "冲压零件号", "冲压件名称", "状态", "生产线", "年月", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"};
        // 导出excel每一列的值
        String[] excelHeaderKey = {"CAR_SERIES_CODE", "PLAN_TYPE", "PART_NO", "PANEL_NAME", "IS_ENABLE", "PRODUCT_LINE", "YEAR_MONTY", "DAY01", "DAY02", "DAY03", "DAY04", "DAY05", "DAY06", "DAY07", "DAY08", "DAY09", "DAY10", "DAY11", "DAY12", "DAY13", "DAY14", "DAY15", "DAY16", "DAY17", "DAY18", "DAY19", "DAY20", "DAY21", "DAY22", "DAY23", "DAY24", "DAY25", "DAY26", "DAY27", "DAY28", "DAY29", "DAY30", "DAY31"};
        ExportPOIEUtils.exportExcel(response, excelHeader, excelHeaderKey, list, "B/S_冲压件需求量管理", "表1");
        return R.ok();
    }
	// 将查询到的数据进行处理，放入map中，方便后续导出
	private void dealExplotList(List rows, List<Map<String, Object>> list) {
        for (int i = 0; i < rows.size(); i++) {
            Map map1 = (Map) rows.get(0);
            HashMap<String, Object> hashMap = new HashMap<>();
            hashMap.put("CAR_SERIES_CODE", map1.get("CAR_SERIES_CODE"));
            hashMap.put("PLAN_TYPE", map1.get("PLAN_TYPE"));
            hashMap.put("PART_NO", map1.get("PART_NO"));
            hashMap.put("PANEL_NAME", map1.get("PANEL_NAME"));
            hashMap.put("IS_ENABLE", map1.get("IS_ENABLE"));
            hashMap.put("PRODUCT_LINE", map1.get("PRODUCT_LINE"));
            hashMap.put("YEAR_MONTY", map1.get("YEAR_MONTY"));
            hashMap.put("DAY01", map1.get("DAY01"));
            hashMap.put("DAY02", map1.get("DAY02"));
            hashMap.put("DAY03", map1.get("DAY03"));
            hashMap.put("DAY04", map1.get("DAY04"));
            hashMap.put("DAY05", map1.get("DAY05"));
            hashMap.put("DAY06", map1.get("DAY06"));
            hashMap.put("DAY07", map1.get("DAY07"));
            hashMap.put("DAY08", map1.get("DAY08"));
            hashMap.put("DAY09", map1.get("DAY09"));
            hashMap.put("DAY10", map1.get("DAY10"));
            hashMap.put("DAY11", map1.get("DAY11"));
            hashMap.put("DAY12", map1.get("DAY12"));
            hashMap.put("DAY13", map1.get("DAY13"));
            hashMap.put("DAY14", map1.get("DAY14"));
            hashMap.put("DAY15", map1.get("DAY15"));
            hashMap.put("DAY16", map1.get("DAY16"));
            hashMap.put("DAY17", map1.get("DAY17"));
            hashMap.put("DAY18", map1.get("DAY18"));
            hashMap.put("DAY19", map1.get("DAY19"));
            hashMap.put("DAY20", map1.get("DAY20"));
            hashMap.put("DAY21", map1.get("DAY21"));
            hashMap.put("DAY22", map1.get("DAY22"));
            hashMap.put("DAY23", map1.get("DAY23"));
            hashMap.put("DAY24", map1.get("DAY24"));
            hashMap.put("DAY25", map1.get("DAY25"));
            hashMap.put("DAY26", map1.get("DAY26"));
            hashMap.put("DAY27", map1.get("DAY27"));
            hashMap.put("DAY28", map1.get("DAY28"));
            hashMap.put("DAY29", map1.get("DAY29"));
            hashMap.put("DAY30", map1.get("DAY30"));
            hashMap.put("DAY31", map1.get("DAY31"));
            list.add(hashMap);
        }
    }

package com.szlanyou.cloud.whepss.common.excel;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ouyg
 * @version 1.0
 * @date 2022/5/12 17:36
 */
public class ExportPOIEUtils {
    /**
     * Excel 导出
     * @param response  HttpServletResponse
     * @param header 表头
     * @param keys map的key值
     * @param content 内容数据
     * @param title 表格名字
     * @param sheetName sheet名
     */
    public static void exportExcel(HttpServletResponse response, String[] header,String[] keys, List<Map<String, Object>> content,String title,String sheetName) throws Exception{
        title = title + ".xlsx";
        Workbook wb = new SXSSFWorkbook(1000);
        Sheet sheet = wb.createSheet(sheetName);
        Row row = sheet.createRow( 0);
        // 行高
        row.setHeight((short) 700);
        // 列宽
        for (int i = 0; i < header.length; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }
        for (int i = 0; i < header.length; i++) {
            org.apache.poi.ss.usermodel.Cell cell = row.createCell(i);
            cell.setCellValue(header[i]);
            cell.setCellStyle(HeaderStyle(wb));
        }
        for (int i = 0; i < content.size(); i++) {
            Map<String, Object> map = content.get(i);
            row = sheet.createRow((int) i + 1);
            row.setHeight((short) 500);
            for (int j = 0; j < keys.length; j++){
                org.apache.poi.ss.usermodel.Cell cell = row.createCell(j);
                cell.setCellValue(map.get(keys[j]) == null ? "" : map.get(keys[j]).toString());
                cell.setCellStyle(contentStyle(wb));
            }
        }
//        if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
//            title = new String(title.getBytes("UTF-8"), "ISO8859-1"); // firefox浏览器
//        } else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
//            title = URLEncoder.encode(title, "UTF-8");// IE浏览器
//        } else if (request.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) {
//            title = new String(title.getBytes("UTF-8"), "ISO8859-1");// 谷歌
//        }
        title = new String(title.getBytes("UTF-8"), "ISO8859-1");
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Content-Disposition", "attachment; filename=" + title);
        wb.write(response.getOutputStream());
        response.getOutputStream().close();
    }


    /**
     * Excel 导入
     * @param file 文件
     * @param keys 数据顺序
     */
    public static  List<Map<String, Object>>  importExcel(MultipartFile file,String[] keys) throws Exception{
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith("xls")) {
            POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
            wb = new HSSFWorkbook(pois);
        } else if (fileName.endsWith("xlsx")) {
            wb = new XSSFWorkbook(file.getInputStream());
        }
        Sheet sheet = wb.getSheetAt(0);
        int rowCount = sheet.getPhysicalNumberOfRows();
        if (sheet.getRow( 1).getPhysicalNumberOfCells() != keys.length){
            throw new RuntimeException("导入的Excel和模板的列不匹配");
        }
        List<Map<String,Object>> result = new ArrayList<>();
        for (int i = 0; i < rowCount - 1; i++) {
            Row row = sheet.getRow(i + 1);
            Map<String,Object> tmp = new HashMap<>();
            for (int j = 0;j < keys.length; j++){
                Cell cell = row.getCell(j);
                // 把类型转行Spring
//                cell.setCellType(CellType.STRING);
                tmp.put(keys[j], cell.getStringCellValue());
            }
            result.add(tmp);
        }
        return result;
    }

    /**
     * 表头样式
     */
    private static CellStyle HeaderStyle(Workbook wb){
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setBold(true);
        font.setFontHeightInPoints((short) 11);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 内容样式
     */
    private static CellStyle contentStyle(Workbook wb){
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 公共样式
     */
    private static CellStyle commonStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);// 自动换行
        return style;
    }

}