基于注解的方式导出Excel

最新推荐文章于 2024-08-01 18:45:00 发布

YU_XIN_JAVA

最新推荐文章于 2024-08-01 18:45:00 发布

阅读量536

点赞数 1

分类专栏： java 文章标签： excel

本文链接：https://blog.csdn.net/JAVA_YU_XIN/article/details/108853472

版权

java 专栏收录该内容

39 篇文章 0 订阅

订阅专栏

利用Java反射机制实现。

1.自定义注解：

package com.quanyu.base.annotation;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Excel{
    public String colName();   //列名
    public int order();   //顺序
    public String type(); //类型 number,data
    public String dateFormat();//时间格式化 'yyyy-MM-dd'
    public String numberFormat();//数字格式化 '0.00''0.##'
}

2.在需要导出的字段的get方法上使用：因为get方法可以获取该字段的数据

package com.quanyu.mini.controller.util;

import com.quanyu.base.annotation.Excel;

public class CaseInfo {
    /**
     * 编号
     */
    protected String no;
    @Excel(colName = "座位号", order = 0,type = "String",dateFormat = "",numberFormat = "")
    public String getNo() {
        return no;
    }
}

3.编写导出Excel工具类：

package com.quanyu.mini.controller.util;

import com.quanyu.base.annotation.Excel;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel处理工具类,使用注解
 *
 */
public class POIExportUtils<T>{
    private POIExportUtils(){}
    private static class ExcelUtilHolder {
        private static final POIExportUtils INSTANCE = new POIExportUtils();
    }

    public static final POIExportUtils getInstance() {
        return POIExportUtils.ExcelUtilHolder.INSTANCE;
    }
    public void ResponseInit(HttpServletRequest request, HttpServletResponse response, String fileName){
        response.reset();
        String enableFileName = null;
        String userAgent = request.getHeader("USER-AGENT");
        //设置content-disposition响应头控制浏览器以下载的形式打开文件
        try {
            if(StringUtils.contains(userAgent, "MSIE")||StringUtils.contains(userAgent, "Trident") || StringUtils.contains(userAgent,"Edge")){//IE 浏览器
                enableFileName = URLEncoder.encode(fileName,"UTF8");
            }else{//火狐，google等其他浏览器
                enableFileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            }
            response.setHeader("Content-Disposition", "attachment; filename=\"" + enableFileName + "\"");//\" 解决Firefox下载英文+中文组合的文件名的问题
        } catch (UnsupportedEncodingException e) {
        }
        //让服务器告诉浏览器它发送的数据属于excel文件类型
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Prama", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
    }

    public void POIOutPutStream( HttpServletResponse response, SXSSFWorkbook wb) throws IOException {
        BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
          try {
               wb.write(out);
               out.flush();
               out.close();
           } catch (FileNotFoundException e) {
               e.printStackTrace();
           } catch (IOException e) {
               e.printStackTrace();
           }finally {
               if (out != null) {
                   out.close();
                   response.flushBuffer();
               }
           }
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public void export(Class<T> objClass, List<T> dataList,HttpServletRequest request, HttpServletResponse response, String fileName) throws Exception{
        ResponseInit(request,response,fileName);
        Class excelClass = objClass;
        Method[] methods = excelClass.getMethods();
        List<Method> methodListGet = new ArrayList<>();
        List<Method> methodListSet = new ArrayList<>();
        for (Method method : methods) {
            String name = method.getName().substring(0,3);
            if("get".equals(name)){
                methodListGet.add(method);
            }
            if("set".equals(name)){
                methodListSet.add(method);
            }
        }
        Map<Integer, String> mapCol = new TreeMap<>();
        Map<Integer, String> mapMethod = new TreeMap<>();
        Map<Integer, Excel> mapExcel = new TreeMap<>();
        for (Method method : methodListGet) {
            Excel excel = method.getAnnotation(Excel.class);
            if (excel != null) {
                mapCol.put(excel.order(), excel.colName());
                mapMethod.put(excel.order(), method.getName());
                mapExcel.put(excel.order(),excel);
            }
        }
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);

        POIBuildBody(POIBuildHead(wb,"sheet1",mapCol),excelClass,mapMethod,dataList,wb,mapExcel);

        POIOutPutStream(response,wb);
    }

    public Sheet POIBuildHead(SXSSFWorkbook wb, String sheetName, Map<Integer, String> mapCol){
        Sheet sheet01 = wb.createSheet(sheetName);
        Row row = sheet01.createRow(0);
        XSSFCellStyle cellStyle0 = (XSSFCellStyle) wb.createCellStyle();
        Font font0 = createFonts(wb, "宋体", false, (short) 200);
        //设置边框
        cellStyle0.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle0.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle0.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle0.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        cellStyle0.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
        cellStyle0.setWrapText(true);
        font0.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        cellStyle0.setFont(font0);

        Cell cell;
        int i = 0;
        for (Map.Entry<Integer, String> entry : mapCol.entrySet()) {
            cell = row.createCell(i++);
            cell.setCellStyle(cellStyle0);
            cell.setCellValue(entry.getValue());
            //设置自动列宽
            sheet01.autoSizeColumn(i);
            sheet01.setColumnWidth(i,sheet01.getColumnWidth(i)*17/10);
        }
        return sheet01;
    }

    public void POIBuildBody(Sheet sheet01, Class<T> excelClass, Map<Integer, String> mapMethod, List<T> dataList, SXSSFWorkbook wb, Map<Integer, Excel> mapExcel) throws Exception{
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        //设置边框
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setWrapText(true);
        Font font = createFonts(wb, "宋体", false, (short) 200);
        cellStyle.setFont(font);
        Row row = null;
        Cell cell = null;
        Map<Integer, NumberFormat> mapNumberFormat = new HashMap<>();
        Map<Integer, DateFormat> mapDateFormat = new HashMap<>();
        NumberFormat numberFormat=null;
        DateFormat dateFormat =null;
        for (Map.Entry<Integer, Excel> entry : mapExcel.entrySet()) {
            if(entry.getValue()!=null && "number".equals(entry.getValue().type())){
                numberFormat = new DecimalFormat(entry.getValue().numberFormat());
                mapNumberFormat.put(entry.getKey(),numberFormat);
            }
            if(entry.getValue()!=null && "data".equals(entry.getValue().type())){
                dateFormat = new SimpleDateFormat(entry.getValue().numberFormat());
                mapDateFormat.put(entry.getKey(),dateFormat);
            }
        }
        if(dataList != null && dataList.size() > 0){
            for(int i = 0;i<dataList.size();i++){
                row= sheet01.createRow(i+1);
             	int colI = 0;
                for (Map.Entry<Integer, String> entry : mapMethod.entrySet()) {
                    Object obj = excelClass.getDeclaredMethod(entry.getValue()).invoke(dataList.get(i));
                    if("number".equals(mapExcel.get(entry.getKey()).type())){
                        NumberFormat format=  mapNumberFormat.get(entry.getKey());
                        String objString = obj==null?"":format.format(obj);
                        createCell(row, colI, objString, cellStyle);
                    }else if("date".equals(mapExcel.get(entry.getKey()).type())){
                        DateFormat dateFormat1=  mapDateFormat.get(entry.getKey());
                        String objString = obj==null?"":dateFormat1.format(obj);
                        createCell(row, colI, objString, cellStyle);
                    }else {
                        createCell(row, colI, (obj==null?"":obj.toString()+""), cellStyle);
                    }
                    colI++;
                }
            }
        }
    }
    // 设置字体格式
    public Font createFonts(Workbook wb, String fontName, boolean isItalic, short hight) {
        Font font = wb.createFont();
        font.setFontName(fontName);
        font.setItalic(isItalic);
        font.setFontHeight(hight);
        return font;
    }
    // 设置内容
    public void createCell(Row row, int column, String value, XSSFCellStyle cellStyle) {
        Cell cell = row.createCell(column);
        cell.setCellValue(("null").equals(value)?"":value);
        cell.setCellStyle(cellStyle);
    }
}

4.测试：

@RequestMapping("exportXlsx")
public void exportXlsx(HttpServletRequest request, HttpServletResponse response){
		String fileName = "工程项目Excel.xlsx";
		try {
			List<CaseInfo> list1 = new ArrayList<>();
			list1.add(....);

POIExportUtils.getInstance().export(CaseInfo.class,list1,request,response,fileName);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}