自定义数据下载到Excel的功能需求

概述 

1.今天给大家介绍的是如何将数据导出到excel中

实现效果

如果是大家想要的效果请继续往下看！

 

代码

 /**
     * 将数据导出到excel中
     */
   @GetMapping(value = "/testExcel")
   public void  testSendNews(HttpServletResponse response){
       String[] headers = new String[]{"序号","名字","密码"};
       String versionName = "测试模板";
       //需要导出的数据转为Excel格式数据
       List<ExcelVo> excelVos = new ArrayList<>();
       ExcelVo vo = new ExcelVo(1,"斗罗大陆","唐家三少");
       ExcelVo vo1 = new ExcelVo(1,"斗破苍穹","天蚕土豆");
       ExcelVo vo2 = new ExcelVo(1,"斗罗大陆","唐家三少");
       excelVos.add(vo);
       excelVos.add(vo1);
       excelVos.add(vo2);
       //excel工具
       ExcelPOIUtil excelPOIUtil = new ExcelPOIUtil();
       //为了解决中文名称乱码问题
       String fileName = null;
       try {
           fileName = URLEncoder.encode(versionName,"utf-8");
           response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
           response.setHeader("Content-Disposition", "attachment;fileName=" + fileName+".xlsx");
           excelPOIUtil.exportDataToExcel(excelVos,headers,versionName,response.getOutputStream());
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

接下来我将其中的类一一贴出来。

ExcelVo

import lombok.Data;

/**
 * @Author: tanghh18
 * @Date: 2019/9/22 11:52
 */
@Data
public class ExcelVo {
    private int id;
    private String name;
    private String password;

    public ExcelVo(){

    }
    public ExcelVo(int id,String name,String password){
        this.id = id;
        this.name = name;
        this.password = password;
    }
}

ExcelPOIUtil类

package com.bos.common;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;


/**
 * @Author: tanghh18
 * @Date: 2019/4/26 14:18
 * @Version 1.0
 * @Description
 */
public class ExcelPOIUtil {
    private static final Logger logger = Logger.getLogger(ExcelUtil.class);

    /**
     * @Title: createWorkbook
     * @Description: 判断excel文件后缀名，生成不同的workbook
     * @param @param is
     * @param @param excelFileName
     * @param @return
     * @param @throws IOException
     * @return Workbook
     * @throws
     */
    public Workbook createWorkbook(InputStream is,String excelFileName) throws IOException{
        if (excelFileName.endsWith(".xls")) {
            return new HSSFWorkbook(is);
        }else if (excelFileName.endsWith(".xlsx")) {
            return new XSSFWorkbook(is);
        }
        return null;
    }

    /**
     * @Title: getSheet
     * @Description: 根据sheet索引号获取对应的sheet
     * @param @param workbook
     * @param @param sheetIndex
     * @param @return
     * @return Sheet
     * @throws
     */
    public Sheet getSheet(Workbook workbook,int sheetIndex){
        return workbook.getSheetAt(0);
    }

    /**
     * @Title: importDataFromExcel
     * @Description: 将sheet中的数据保存到list中，
     * 1、调用此方法时，vo的属性个数必须和excel文件每行数据的列数相同且一一对应，vo的所有属性都为String
     * 2、在action调用此方法时，需声明
     *     private File excelFile;上传的文件
     *     private String excelFileName;原始文件的文件名
     * 3、页面的file控件name需对应File的文件名
     * @param @param vo javaBean
     * @param @param is 输入流
     * @param @param excelFileName
     * @param @return
     * @return List<Object>
     * @throws
     */
    public List<Object> importDataFromExcel(Object vo,InputStream is,String excelFileName) throws NoSuchMethodException, IOException, InvocationTargetException, IllegalAccessException, InstantiationException {
        List<Object> list = new ArrayList<Object>();
        try {
            //创建工作簿
            Workbook workbook = this.createWorkbook(is, excelFileName);
            //创建工作表sheet
            Sheet sheet = this.getSheet(workbook, 0);
            //获取sheet中数据的行数
            int rows = sheet.getPhysicalNumberOfRows();
            //获取表头单元格个数
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            //利用反射，给JavaBean的属性进行赋值
            Field[] fields = vo.getClass().getDeclaredFields();
            for (int i = 1; i < rows; i++) {//第一行为标题栏，从第二行开始取数据
                Row row = sheet.getRow(i);
                int index = 0;
                while (index < cells) {
                    Cell cell = row.getCell(index);
                    if (null == cell) {
                        cell = row.createCell(index);
                    }
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String value = null == cell.getStringCellValue()?"":cell.getStringCellValue();

                    Field field = fields[index];
                    String fieldName = field.getName();
                    String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
                    Method setMethod = vo.getClass().getMethod(methodName, new Class[]{String.class});
                    setMethod.invoke(vo, new Object[]{value});
                    index++;
                }
                if (isHasValues(vo)) {//判断对象属性是否有值
                    list.add(vo);
                    vo = vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新创建一个vo对象
                }
            }
        } finally{
            try {
                is.close();//关闭流
            } catch (Exception e2) {
                logger.error(e2);
            }
        }
        return list;
    }

    /**
     * @Title: isHasValues
     * @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true
     * @param @param object
     * @param @return
     * @return boolean
     * @throws
     */
    public boolean isHasValues(Object object){
        Field[] fields = object.getClass().getDeclaredFields();
        boolean flag = false;
        for (int i = 0; i < fields.length; i++) {
            String fieldName = fields[i].getName();
            String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
            Method getMethod;
            try {
                getMethod = object.getClass().getMethod(methodName);
                Object obj = getMethod.invoke(object);
                if (null != obj && "".equals(obj)) {
                    flag = true;
                    break;
                }
            } catch (Exception e) {
                logger.error(e);
            }

        }
        return flag;

    }

    public <T> void exportDataToExcel(List<T> list,String[] headers,String title,OutputStream os){
        XSSFWorkbook workbook = new XSSFWorkbook();
        //生成一个表格
        XSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽15个字节
        sheet.setDefaultColumnWidth(15);
        //生成一个样式
        XSSFCellStyle style = this.getCellStyle(workbook);
        //生成一个字体
        XSSFFont font = this.getFont(workbook);
        //把字体应用到当前样式
        style.setFont(font);

        //生成表格标题
        XSSFRow row = sheet.createRow(0);
        row.setHeight((short)300);
        XSSFCell cell = null;

        for (int i = 0; i < headers.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //将数据放入sheet中
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i+1);
            T t = list.get(i);
            //利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值
            Field[] fields = t.getClass().getDeclaredFields();
            try {
                for (int j = 0; j < fields.length; j++) {
                    cell = row.createCell(j);
                    Field field = fields[j];
                    String fieldName = field.getName();
                    String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
                    Method getMethod = t.getClass().getMethod(methodName,new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});

                    if(null == value)
                        value ="";
                    cell.setCellValue(value.toString());

                }
            } catch (Exception e) {
                logger.error(e);
            }
        }

        try {
            workbook.write(os);
        } catch (Exception e) {
            logger.error(e);
        }finally{
            try {
                os.flush();
                os.close();
            } catch (IOException e) {
                logger.error(e);
            }
        }

    }

    /**
     * @Title: getCellStyle
     * @Description: 获取单元格格式
     * @param @param workbook
     * @param @return
     * @return HSSFCellStyle
     * @throws
     */
    public XSSFCellStyle getCellStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = workbook.createCellStyle();
        return style;
    }

    /**
     * @Title: getFont
     * @Description: 生成字体样式
     * @param @param workbook
     * @param @return
     * @return HSSFFont
     * @throws
     */
    public XSSFFont getFont(XSSFWorkbook workbook){
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)12);
        return font;
    }

    public boolean isIE(HttpServletRequest request){
        return request.getHeader("USER-AGENT").toLowerCase().indexOf("msie")>0?true:false;
    }
}