管理后台添加营销员导入功能

mitao

2024-06-06 3d2b51ea4520533de5e78f88dddf5b5c7dce4247

 meiya-core/src/main/java/com/sinata/core/util/ExcelExportUtil.java

@@ -1,8 +1,14 @@
package com.sinata.core.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
@@ -17,6 +23,8 @@
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

/**
 * Excel报表导出工具类
@@ -34,7 +42,76 @@
     */
    public static void easySheet(String fileName, String sheetName, List<List<Object>> dataList, HttpServletResponse response) throws IOException {
        //设置响应头，输出文件
        setResponseHeader(response, fileName);
        setResponseHeader2(response, fileName);

        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = null;

        HSSFCellStyle style = workbook.createCellStyle();
        // 创建字体对象
        Font ztFont = workbook.createFont();
        ztFont.setItalic(true);                     // 设置字体为斜体字   
        ztFont.setColor(Font.COLOR_RED);            // 将字体设置为“红色”   
        ztFont.setFontHeightInPoints((short) 22);    // 将字体大小设置为18px
        ztFont.setFontName("华文行楷");             // 将“华文行楷”字体应用到当前单元格上   
        ztFont.setUnderline(Font.U_DOUBLE);         // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）   
        style.setFont(ztFont);                    // 将字体应用到样式上面   

        // 设置单元格边框样式 
        style.setBorderBottom(CellStyle.BORDER_THICK);
        style.setBorderTop(CellStyle.BORDER_DASHED);
        style.setBorderLeft(CellStyle.BORDER_DOUBLE);
        style.setBorderRight(CellStyle.BORDER_THIN);

        // 设置单元格边框颜色   
        style.setBottomBorderColor(HSSFColor.ORANGE.index);
        style.setTopBorderColor(HSSFColor.ORANGE.index);
        style.setLeftBorderColor(HSSFColor.ORANGE.index);

        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 设置单元格内容垂直对其方式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        sheet = workbook.createSheet(sheetName);//设置表明
        sheet.setDefaultColumnWidth(15);//setColumnWidth设置cell的宽度
        sheet.setDefaultRowHeightInPoints(20);

        // 设置单元格类型为文本类型
        HSSFDataFormat dataFormat = workbook.createDataFormat();
        style.setDataFormat(dataFormat.getFormat("@"));

        //填充报表数据
        for (int y = 0; y < dataList.size(); y++) {
            List<Object> cellList = dataList.get(y);
            Row row = sheet.createRow(y);
            for (int x = 0; x < cellList.size(); x++) {
                row.createCell(x).setCellValue(ToolUtil.toStr(cellList.get(x)));
            }
        }

        OutputStream outStream = response.getOutputStream();
        try {
            workbook.write(outStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        outStream.flush();
        outStream.close();
    }
    /**
     * 简单模板excel导出功能
     *
     * @param fileName  报表文件名
     * @param sheetName 报表表名
     * @param dataList  报表数据（包含行头和列内容）
     * @param response
     * @throws IOException
     */
    public static InputStream easySheet2(String fileName, String sheetName, List<List<Object>> dataList, HttpServletResponse response) throws IOException {
        //设置响应头，输出文件
        setResponseHeader2(response, fileName);

        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = null;
@@ -78,16 +155,37 @@
                row.createCell(x).setCellValue(ToolUtil.toStr(cellList.get(x)));
            }
        }

        OutputStream outStream = response.getOutputStream();
        ByteArrayOutputStream bos = null;
        try {
            workbook.write(outStream);
        } catch (IOException e) {
            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] byteArray = bos.toByteArray();
            return new ByteArrayInputStream(byteArray);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (bos != null) {
                bos.flush();
                bos.close();
        }
        outStream.flush();
        outStream.close();
    }
        /*
        try {
            File file = new File("F:\\DeskTop");
            if (!file.exists()) {
                file.mkdirs();// 创建文件根目录
            }
            String savePath = file.getPath() +"/导入失败数据.xls";
            OutputStream os = new FileOutputStream(savePath);
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }*/
        return null;
    }


    /**
     * 复杂杂模板excel导出（合并单元格、设置表格样式等）
@@ -270,4 +368,20 @@
            e.printStackTrace();
        }
    }
    /**
     * 设置响应头
     *
     * @param response
     */
    public static void setResponseHeader2(HttpServletResponse response, String excelName) {
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + excelName + URLEncoder.encode(".xls", "UTF-8"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}