java实现导出数据到excel表格

java实现导出数据到excel表格 java实现导出数据到excel表格

1. 依赖

        
        
        
            org.apache.poi
            poi-ooxml
            3.11
        

2. 代码
   excelUtil编写

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.List;


public class ExcelUtil {

    
    public static  void uploadExcel(HttpServletResponse response, String fileName, List columnList, List dataList, List headers){
        //声明输出流
        OutputStream os = null;
        //设置响应头
        setResponseHeader(response,fileName);
        try {
            //获取输出流
            os = response.getOutputStream();
            HSSFWorkbook wb = new HSSFWorkbook();
            //获取该工作区的第一个sheet
            Sheet sheet1 = wb.createSheet(fileName);
            int excelRow = 0;
            //创建标题行
            Row titleRow = sheet1.createRow(excelRow++);
            for(int i = 0;i0){
                for (T dto : dataList) {
                    Row dataRow = sheet1.createRow(excelRow++);
                    Field[] fields = dto.getClass().getDeclaredFields();
                    for (Field field : fields) {
                        int m = 0;
                        for (String header : headers) {
                            if (field.getName().equals(header)) {
                                Cell cell = dataRow.createCell(m);
                                //获取get方法
                                cell.setCellValue(invokeGet(dto, header)==null?null:invokeGet(dto, header).toString());
                                break;
                            }
                            m++;
                        }
                    }
                }
            }
            //将整理好的excel数据写入流中
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        finally {
            try {
                // 关闭输出流
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    
    private static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            invokeSet(response,"contentType","application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName+".xls");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    
    @SuppressWarnings("unchecked")
    public static Method getGetMethod(Class objectClass, String fieldName) {
        StringBuffer sb = new StringBuffer();
        sb.append("get");
        sb.append(fieldName.substring(0, 1).toUpperCase());
        sb.append(fieldName.substring(1));
        try {
            return objectClass.getMethod(sb.toString());
        } catch (Exception e) {
        }
        return null;
    }

    
    public static Object invokeGet(Object o, String fieldName) {
        Method method = getGetMethod(o.getClass(), fieldName);
        try {
            return method.invoke(o, new Object[0]);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    
    @SuppressWarnings("unchecked")
    public static Method getSetMethod(Class objectClass, String fieldName) {
        try {
            Class[] parameterTypes = new Class[1];
            Field field = objectClass.getDeclaredField(fieldName);
            parameterTypes[0] = field.getType();
            StringBuffer sb = new StringBuffer();
            sb.append("set");
            sb.append(fieldName.substring(0, 1).toUpperCase());
            sb.append(fieldName.substring(1));
            Method method = objectClass.getMethod(sb.toString(), parameterTypes);
            return method;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    
    public static void invokeSet(Object o, String fieldName, Object value) {
        Method method = getSetMethod(o.getClass(), fieldName);
        try {
            method.invoke(o, new Object[] { value });
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

测试使用

@ApiOperation(value = "导出站店盘点数据接口")
    @GetMapping("/exportStation")
    public ResponseModelDto exportAllStationRecord(MallQueryInventoryStationReqDto reqDto, HttpServletResponse response){
        List list = mallStationInventoryService.selectAllRecord(reqDto);
        List title = Arrays.asList("小店名称","盘点状态(false未盘点,true已盘点)","复盘次数","异常情况(false有异常,true无异常)","盘点人员","任务时间","最后提交时间");
        List headers = Arrays.asList("stationName","inventory","replayNum","status","stationmasterName","inventoryTime","submitTime");
        String fileName = "盘点记录"+reqDto.getStartTime();
        ExcelUtil.uploadExcel(response,fileName,title,list,headers);
        return ResponseModels.ok();
    }

3. 简介
   本文主要通过java反射来进行数据的获取以及读写