javaweb/spring/springboot实现Excel导出

例如：

• 思路 通过自定义注解,标注实体类上并设置列表头，反射获取里面值通过apache.poi 这个jar生成Excel表
  提示：默认Dao取出来数据List 这种类型数据
  啥也不说直接开搞

提示：环境 springboot 和maven

例如：

1. 使用Maven导入jar包

		
            org.apache.poi
            poi
            5.2.2
        

2. 自定义注解

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelHead {
    public String value() default "默认设置列头";
}

4. 标注实体类后效果
   

5. 封装工具类

@Component
public class ExcelUtil {
    
    public void createExcel(String savePath, String listName, List list, int columnWidth, int rowHeight, Class target) throws Exception {
        FileOutputStream outputStream = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = workbook.createSheet(listName);
        //设置列头
        setHead(target, sheet, columnWidth, rowHeight);

        int i = 1;
        //对于list里数据进行遍历
        for (T t : list) {
            //设置第一行
            HSSFRow row = sheet.createRow(i++);
            //设置行高度
            row.setHeight((short) (rowHeight * 10));
            Class aClass = t.getClass();
            Field[] declaredFields = aClass.getDeclaredFields();
            int j = 0;
            for (Field field : declaredFields) {
                field.setAccessible(true);
                //对于没有标注该注解直接结束本次循环
                if (field.getAnnotation(ExcelHead.class) == null){
                    continue;
                }
                Object o = field.get(t);
                HSSFCell cell = row.createCell(j++);
                cell.setCellValue(o.toString());
            }
        }
        try {
            outputStream = new FileOutputStream(savePath);
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(outputStream);
        }
    }

    
    public void setHead(Class target, HSSFSheet sheet, int ColumnWidth, int rowHeight) {
        //建立第0行
        HSSFRow row = sheet.createRow(0);
        //通过反射拿到属性
        Field[] fields = target.getDeclaredFields();
        int i = 0;
        for (Field field : fields) {
            //突破private
            field.setAccessible(true);
            //有些属性没有标注注解那么结束本次循环，没必要继续往下进行操作
            if (field.getAnnotation(ExcelHead.class) == null) {
                continue;
            }
            ExcelHead annotation = field.getAnnotation(ExcelHead.class);
            //获取注解里值
            String value = annotation.value();
            //建立0行第i个小单元格
            HSSFCell cell = row.createCell(i++);
            //设置列宽
            sheet.setColumnWidth(i, ColumnWidth * 256);
            //设置行高
            row.setHeight((short) (rowHeight * 10));
            //往一个单元格放值（也就是注解里值）
            cell.setCellValue(value);
        }
    }

    public void close(FileOutputStream fileOutputStream) {
        if (fileOutputStream != null) {
            try {
                fileOutputStream.flush();
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    public void createExcel(String savePath, String listName, List list, Class target) throws Exception {
        this.createExcel(savePath, listName, list, 20, 30, target);
    }


}

6. 最后完成业务层

@Service
public class CsvServiceImpl implements CsvService{
    //通过spring注入工具类
    @Autowired
    ExcelUtil excelUtil;
    @Override
    public void getCSVFile(HttpServletResponse response, HttpSession session) throws Exception{
        //通过session取dao里获取数据
        List list = (List)session.getAttribute("userLog");
        //输出路径
        String savePath = "./src/main/resources/static/用户日志表.xls";
        try {
            excelUtil.createExcel(savePath,"用户日志表",list,LoginLogEntity.class);
        }catch (Exception e){
           e.printStackTrace();
        }
        //以上完成excel文档（里面已经存入dao里数据）在项目里建立，下面就是取Excel文档了
        
        
        FileInputStream inputStream = null;
        ServletOutputStream stream = null;
        try {
            //因为中文问题，对中文名解码ios
            String filename = "日志表.xls";
            String filename1 = new String(filename.getBytes("utf-8"),"ISO-8859-1");
            //告诉浏览器下载
            response.addHeader("content-type","application/x-msdownload");
            response.setContentType("application/octet-stream");
            response.addHeader("Content-Disposition","attachment;filename="+filename1);
            File file = new File("./src/main/resources/static/用户日志表.xls");
            if (!file.exists()){
                throw new RuntimeException("文件不存在");
            }
            inputStream = new FileInputStream(file);
            stream = response.getOutputStream();
            byte[] bytes = new byte[200];
            int len = 0;
            while ((len = inputStream.read(bytes)) != -1){
                stream.write(bytes,0,len);
            }

        }finally {
            if (stream != null){
                stream.flush();
                stream.close();
            }
            if (inputStream != null){
                inputStream.close();
            }
        }
    }
}