excel导入与导出

Excel生成

导入依赖

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.15</version>
        </dependency>
       


 <!--easypoi-->
        <dependency>
          <groupId>cn.afterturn</groupId>
          <artifactId>easypoi-base</artifactId>
          <version>3.2.0</version>
        </dependency>
        <dependency>
          <groupId>cn.afterturn</groupId>
          <artifactId>easypoi-web</artifactId>
          <version>3.2.0</version>
        </dependency>
        <dependency>
          <groupId>cn.afterturn</groupId>
          <artifactId>easypoi-annotation</artifactId>
          <version>3.2.0</version>
        </dependency>

控制层代码：

@PostMapping("/tcontrolEntmeasuresExcel")
    private void TControlEntmeasuresExcel(HttpServletResponse response, HttpServletRequest request){
        String[] fieldData = new String[] {"序号", "企业名称", "行业", "设施名称", "年份"};
//查询数据库得到想要导出数据的list集合
        List<TControlEntmeasures> findT_control_entmeasuresByallexcel = tcontrolEntmeasuresService.findT_control_entmeasuresByallexcel();
        controlReserveplanService.comCompanyExcelXLSX(response, request,fieldData);
        
    }
//可以根据request进行条件查询

业务层controlReserveplanService.comCompanyExcelXLSX(response, request,fieldData代码

public void comCompanyExcelXLSX(HttpServletResponse response,HttpServletRequest, List<TControlEntmeasures> list,
            String startTime, String endTime, String[] fieldData) {
        int cowspan = 0;
        if(fieldData != null) {
            cowspan = fieldData.length;
        }
        try {
            String fileName = "污染源管控措施";
            response.setContentType("application/vnd.ms-excel");
            // 传递中文参数编码
            String codedFileName =  java.net.URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("content-disposition",  ";filename="+codedFileName+".xlsx");
            
            /**
             * 创建工作表
             */
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("0");
            //设置列宽
            sheet.setColumnWidth(0, 256*10);
            sheet.setColumnWidth(1, 256*20);
            sheet.setColumnWidth(2, 256*40);
            sheet.setColumnWidth(3, 256*20);
            sheet.setColumnWidth(4, 256*25);
            sheet.setColumnWidth(5, 256*25);
            sheet.setColumnWidth(6, 256*25);
            sheet.setColumnWidth(7, 256*25);
            sheet.setColumnWidth(8, 256*25);
            sheet.setColumnWidth(9, 256*15);
            sheet.setColumnWidth(10, 256*15);
            sheet.setColumnWidth(11, 256*20);
            sheet.setColumnWidth(12, 256*15);
            sheet.setColumnWidth(13, 256*15);
            sheet.setColumnWidth(14, 256*15);
            sheet.setColumnWidth(15, 256*15);
            /**
             * 标题样式 样式
             */
            CellStyle titleCellStyle = wb.createCellStyle();
            titleCellStyle.setBorderLeft(BorderStyle.NONE);
            titleCellStyle.setBorderRight(BorderStyle.NONE);
            titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
            titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
            
            XSSFFont titleFont = wb.createFont();
            titleFont.setFontHeight(18);
            titleFont.setBold(true);
            titleCellStyle.setFont(titleFont);
            /**
             * 主标题 在这里插入主标题
             */
            Row titleRow;
            Cell titleCell;
            sheet.addMergedRegion(new CellRangeAddress((short) 0, (short) 0, (short) 0, (short) cowspan));//合并单元格；四个参数分别为起始行、终止行、起始列、终止列
            titleRow = sheet.createRow(0);//从0开始，第一行
            for (int j = 0; j < 3; j++) {
                titleCell = titleRow.createCell(j);
                titleCell.setCellType(CellType.STRING);
                titleCell.setCellStyle(titleCellStyle);
                titleCell.setCellValue("污染源管控措施");
            }
            /**
             * 时间范围
             */
            CellStyle timeCellStyle = wb.createCellStyle();
            timeCellStyle.setBorderLeft(BorderStyle.NONE);
            timeCellStyle.setBorderRight(BorderStyle.NONE);
            timeCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居中
            timeCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
            
            XSSFFont timeFont = wb.createFont();
            timeFont.setFontHeight(12);
            timeCellStyle.setFont(timeFont);
            
            
            
            /**
             * 列 标题 在这里插入标题
             */
            CellStyle textCellStyle = wb.createCellStyle();
            textCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
            textCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
            XSSFFont textFont = wb.createFont();
            textFont.setFontHeight(12);
            textFont.setBold(true);
            textCellStyle.setFont(textFont);
            //列标题赋值处理
            Row rowLabel;
            Cell cellLabel;
            rowLabel = sheet.createRow(1);//第二行，插入标题
            for (int j = 0; j < fieldData.length; j++) {
                cellLabel = rowLabel.createCell(j);
                cellLabel.setCellType(CellType.STRING);
                cellLabel.setCellStyle(textCellStyle);
                cellLabel.setCellValue(fieldData[j]);
            }
            /**
             * 单元格 样式
             */
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
            /**
             * --列 数据 在这里插入数据
             */
            int i=2;
            int count = 2;//第三行；插入数据
            Row rowCheck;
            for (TControlEntmeasures temp : list) {
                rowCheck = sheet.createRow(count);
                rowCheck.setRowStyle(cellStyle);
                rowCheck.createCell(0).setCellValue(i-1);
                rowCheck.createCell(1).setCellValue(temp.getEntName());//企业名称
                rowCheck.createCell(2).setCellValue(temp.getRedalert());//红色预警等级
                rowCheck.createCell(3).setCellValue(temp.getRedwarningscheme());//红色措施
               ...
　　　　　　　　　...
　　　　　　　　　...

                rowCheck.createCell(15).setCellValue(temp.getYear());//年份
                for(int n=0; n<15; n++) {
                    rowCheck.getCell(n).setCellStyle(cellStyle);//给单元格加上样式
                }              
                count++;//循环一次行数+1
                i++;//循环一次序号+1
            }   
　　　　　　　　　　　

/**
* 合并单元格

startRow = endRow + 1;
endRow = endRow + device.size();
if(endRow > startRow) {
//合并单元格 -指定 4 个参数，起始行，结束行，起始列，结束列。然后这个区域将被合并
CellRangeAddress region = new CellRangeAddress(startRow, endRow, 0, 0);
sheet.addMergedRegion(region);

CellRangeAddress region1 = new CellRangeAddress(startRow, endRow, 1, 1);
sheet.addMergedRegion(region1);

CellRangeAddress region2 = new CellRangeAddress(startRow, endRow, 2, 2);
sheet.addMergedRegion(region2);

CellRangeAddress region3 = new CellRangeAddress(startRow, endRow, 3, 3);
sheet.addMergedRegion(region3);
}

*/



            /**
             * 页脚
             */
            wb.setSheetName(0, "污染源管控措施");
            /**
             * 进行导出
             */
            OutputStream out =  response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
                
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

Excel导入数据库

控制层代码：

/**
     * 根据文件路径导入excel数据
     * @param response
     */
    @PostMapping("/tcontrolEntmeasuresExcelImport")
    private void TControlEntmeasuresExcelImport(HttpServletResponse response){//传入对应需要导入文档的路径和创建的实体类
        List<TControlEntmeasuresExcel> tcontrolEntmeasuresExcellmport = ExcelUtiles.importExcel("F:\程序快捷\---文档\--模板.xlsx", 0, 1, TControlEntmeasuresExcel.class);
        for(int i=0;i<tcontrolEntmeasuresExcellmport.size();i++) {
            TControlEntmeasuresExcel tControlEntmeasuresExcel = tcontrolEntmeasuresExcellmport.get(i);
            String entId = tControlEntmeasuresExcel.getEntId();//获取企业名称进行查询企业id插入
            String deviceId = tControlEntmeasuresExcel.getDeviceId();//获取设施名称查询设施id插入
            String redStart = tControlEntmeasuresExcel.getRedStart();//获取是否启动红色预警
          
            ...
　　　　　　　...
　　　　　　　...
　　　　　　　

　　　　　　　TControlEntmeasures controlEntmeasures =new TControlEntmeasures();
　　　　　　　controlEntmeasures.setYear(Integer.valueOf(tControlEntmeasuresExcel.getYear()));//年份
　　　　　　　controlEntmeasures.setEntId(tControlEntmeasuresExcel.getEntId());//企业id
　　　　　　　controlEntmeasures.setDeviceId(tControlEntmeasuresExcel.getDeviceId());//设施id

　　　　...
　　　　　...
　　　　　　...

            int insertT_control_entmeasuress = tcontrolEntmeasuresService.insertT_control_entmeasuress(controlEntmeasures);//将数据插入
            System.out.println(tControlEntmeasuresExcel.toString());
        }

    }

ExcelUtiles.importExcel代码：

/**
     * 功能描述：根据文件路径来导入Excel
     * @param filePath   文件路径
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        // 判断文件是否存在
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);//设置表标题行数
        params.setHeadRows(headerRows);//设置表头行数
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
//            throw new RuntimeException("模板不能为空");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

实体类代码：

public class TControlEntmeasuresExcel {
    @Excel(name = "序号",orderNum = "0")
    private String number;
    @Excel(name = "年份",orderNum = "1")
    private String year;
    @Excel(name = "企业名称",orderNum = "2")
    private String entId;
    @Excel(name = "设施名称",orderNum = "3")
    private String deviceId;

getter、setter和tostring方法

多sheet导入

控制层代码：

@RequestMapping("/importAirPollutionElectricityData")
    public void importAirPollutionElectricityData(MultipartFile file) {
        
        String s = excelService.importSheets(file);
}

service层：

/**
     * 多sheet导入
     * @param file
     * @return
     * @throws IOException
     */
    @Override
    public String importSheets(MultipartFile file){
        try {
            // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
            Workbook workBook = ExcelUtiles.getWorkBook(file);
            StringBuilder sb=new StringBuilder();
            ImportParams params = new ImportParams();
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                 // 第几个sheet页
                params.setStartSheetIndex(numSheet);
                // 验证数据
                params.setNeedVerfiy(true);
                if(numSheet==0){
                    // 表头在第几行
                    params.setTitleRows(1);
                    // 距离表头中间有几行不要的数据
                    params.setStartRows(0);
                    ExcelImportResult<ControlEntElectricity> result =ExcelImportUtil.importExcelMore(file.getInputStream(),
                            ControlEntElectricity.class, params);
                    // 校验是否合格
                    if(result.isVerfiyFail()){
                        // 不合格的数据
                        List<ControlEntElectricity> errorList = result.getList();
                        // 拼凑错误信息,自定义
                        for(int i=0;i<errorList.size();i++){
                            ExcelUtiles.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "。。信息不合法");
                        }
                    }
                    // 合格的数据
                    List<ControlEntElectricity> successList = result.getList();
                    // 业务逻辑
                    System.out.println(successList.toString());
                }else if(numSheet==1){
                    // 表头在第几行
                    params.setTitleRows(2);
                    // 距离表头中间有几行不要的数据
                    params.setStartRows(0);
                    ExcelImportResult<ControlEntElectricityDay> result =ExcelImportUtil.importExcelMore(file.getInputStream(),
                            ControlEntElectricityDay.class, params);
                    // 校验是否合格
                    if(result.isVerfiyFail()){
                        // 不合格的数据
                        List<ControlEntElectricityDay> errorList = result.getList();
                        // 拼凑错误信息,自定义
                        for(int i=0;i<errorList.size();i++){
                            ExcelUtiles.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "。。信息不合法");
                        }
                    }
                    // 校验合格的数据
                    List<ControlEntElectricityDay> successList = result.getList();
                    // 业务逻辑
                    System.out.println(successList.toString());
                }
 
            }
        } catch (Exception e) {
//            log.error("导入失败:{}",e);
            return "导入失败！请检查导入文档的格式是否正确";
        }
        return "导入成功！";
    }

 ExcelUtiles.getWorkBook()和 ExcelUtiles.getWrongInfo()方法代码：

 /**
     * 得到Workbook对象
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException{
        //这样写  excel 能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is =file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }


 /**
     * 得到错误信息
     * @param sb
     * @param list
     * @param i
     * @param obj
     * @param name  用哪个属性名去表明不和规定的数据
     * @param msg
     * @throws Exception
     */
    public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throws Exception{
        Class clazz=obj.getClass();
        Object str=null;
        //得到属性名数组
        Field[] fields = clazz.getDeclaredFields();
        for(Field f : fields){
            if(f.getName().equals(name)){
                //用来得到属性的get和set方法
                PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
                //得到get方法
                Method getMethod=pd.getReadMethod();
                str = getMethod.invoke(obj);
            }
        }
        if(i==0) {
            sb.append(msg + str + ";");
        }
        else if(i==(list.size()-1)) {
            sb.append(str + "</br>");
        }
        else {
            sb.append(str + ";");
        }
    }

附上excel导入导出可能会用到的工具类代码 ExcelUtiles.java：

package com.mapuni.datacenter.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.mapuni.datacenter.entity.ControlEntElectricity;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException; 
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;

/**
 * @description: easypoiUtils
 * 导入导出工具类
 */
public class ExcelUtiles {
    
    /**
     * 测试
     * @param args
     */
    public static void main(String[] args) {
        List<ControlEntElectricity> list = ExcelUtiles.importExcel("D:\5..27.xlsx", 0, 1, ControlEntElectricity.class);
        System.out.println(list.toString());
        String s = "D:\5..27.xlsx";
        System.out.println(s.substring(s.lastIndexOf(".")));
    }

    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
     * @param list           导出的实体类
     * @param title          表头名称
     * @param sheetName      sheet表名
     * @param pojoClass      映射的实体类
     * @param isCreateHeader 是否创建表头
     * @param fileName
     * @param response
     * @return
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
            boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头
     * @param list      导出的实体类
     * @param title     表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param fileName
     * @param response
     * @return
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
            HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    /**
     * 功能描述：Map 集合导出
     * @param list     实体集合
     * @param fileName 导出的文件名称
     * @param response
     * @return
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    /**
     * 功能描述：默认导出方法
     * @param list         导出的实体集合
     * @param fileName     导出的文件名
     * @param pojoClass    pojo实体
     * @param exportParams ExportParams封装实体
     * @param response
     * @return
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
            ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    /**
     * 功能描述：Excel导出
     * @param fileName 文件名称
     * @param response
     * @param workbook Excel对象
     * @return
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // throw new NormalException(e.getMessage());
        }
    }

    /**
     * 功能描述：默认导出方法
     * @param list     导出的实体集合
     * @param fileName 导出的文件名
     * @param response
     * @return
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 功能描述：根据文件路径来导入Excel
     * @param filePath   文件路径
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        // 判断文件是否存在
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
//            throw new RuntimeException("模板不能为空");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

    /**
     * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
     * @param file       上传的文件
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
            Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            // throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }
     /**
     * 得到Workbook对象
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException{
        //这样写  excel 能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is =file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }
    
    /**
     * 得到错误信息
     * @param sb
     * @param list
     * @param i
     * @param obj
     * @param name  用哪个属性名去表明不和规定的数据
     * @param msg
     * @throws Exception
     */
    public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throws Exception{
        Class clazz=obj.getClass();
        Object str=null;
        //得到属性名数组
        Field[] fields = clazz.getDeclaredFields();
        for(Field f : fields){
            if(f.getName().equals(name)){
                //用来得到属性的get和set方法
                PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
                //得到get方法
                Method getMethod=pd.getReadMethod();
                str = getMethod.invoke(obj);
            }
        }
        if(i==0) {
            sb.append(msg + str + ";");
        }
        else if(i==(list.size()-1)) {
            sb.append(str + "</br>");
        }
        else {
            sb.append(str + ";");
        }
    }

    
}