基于poi的excel导出功能

public class ExcelUtils{

/**

*

* @param sheetName 名称

* @param title 标题

* @param values excle内容

* @param wb HSSFWorkbook对象

* @return

*/

public static HSSFWorkbook getExcelObject(String sheetName, String []title, List<Map<String, Object>> values, HSSFWorkbook wb, Class<?> T){

//1.创建一个HSSFWorkbook对象（Excel对象）

if(Objects.isNull(wb)){

wb = new HSSFWorkbook();

}

//2.在HSSFWordbook对象中创建一个sheet（excle的表单名字）

HSSFSheet sheet = wb.createSheet(sheetName);

//3.在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制

HSSFRow row = sheet.createRow(0);

 

// 第四步，创建单元格，并设置值表头 设置表头居中

HSSFCellStyle style = wb.createCellStyle();

// 创建一个居中格式

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

//创建一个字体样式

HSSFFont font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示

style.setFont(font);

 

//声明列对象

HSSFCell cell = null;

//创建标题

for (int i = 0;i < title.length; i++) {

cell = row.createCell(i);

cell.setCellValue(title[i]);

cell.setCellStyle(style);

}

//创建内容

//获取的是该类自己声明的成员变量的信息

Field[] declaredFields = T.getDeclaredFields();

for (int i = 0;i< values.size();i++){

row =sheet.createRow(i+1);

int count =0;

for (int j = 0 ;j<declaredFields.length;j++){

String name = declaredFields[j].getName();

Object value =values.get(i).get(name);

if(Objects.isNull(value)){

count--;

}else{

count++;

row.createCell(count).setCellValue(value.toString());

}

}

}

return wb;

}

 

/**

*发送响应流方法

* @param response 设置响应头

* @param fileName

* @throws IOException

*/

public static void setResponseHeader(HttpServletResponse response, String fileName) throws IOException {

fileName = new String(fileName.getBytes(),"ISO8859-1");

response.setContentType("application/octet-stream;charset=ISO8859-1");

response.setHeader("Content-Disposition", "attachment;filename="+ fileName);

response.addHeader("Pargam", "no-cache");

response.addHeader("Cache-Control", "no-cache");

}

 

/**

* 输出excel

* @param response

* @param wb

* @throws IOException

*/

public static void printExcel(HttpServletResponse response,HSSFWorkbook wb) throws IOException {

OutputStream os = response.getOutputStream();

wb.write(os);

os.flush();

os.close();

}

}

 

public class Transfer {
    /**
     * 将对象装换为map
     * @param bean
     * @return
     */
    public static <T> Map<String, Object> beanToMap(T bean) {
        Map<String, Object> map = Maps.newHashMap();
        if (bean != null) {
            BeanMap beanMap = BeanMap.create(bean);
            for (Object key : beanMap.keySet()) {
                map.put(key+"", beanMap.get(key));
            }
        }
        return map;
    }

    /**
     * 将map装换为javabean对象
     * @param map
     * @param bean
     * @return
     */
    public static <T> T mapToBean(Map<String, Object> map,T bean) {
        BeanMap beanMap = BeanMap.create(bean);
        beanMap.putAll(map);
        return bean;
    }

    /**
     * 将List<T>转换为List<Map<String, Object>>
     * @param objList
     * @return
     *
     */
    public static <T> List<Map<String, Object>> objectsToMaps(List<T> objList) {
        List<Map<String, Object>> list = Lists.newArrayList();
        if (objList != null && objList.size() > 0) {
            Map<String, Object> map = null;
            T bean = null;
            for (int i = 0,size = objList.size(); i < size; i++) {
                bean = objList.get(i);
                map = beanToMap(bean);
                list.add(map);
            }
        }
        return list;
    }

    /**
     * 将List<Map<String,Object>>转换为List<T>
     * @param maps
     * @param clazz
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> List<T> mapsToObjects(List<Map<String, Object>> maps,Class<T> clazz) throws InstantiationException, IllegalAccessException {
        List<T> list = Lists.newArrayList();
        if (maps != null && maps.size() > 0) {
            Map<String, Object> map = null;
            T bean = null;
            for (int i = 0,size = maps.size(); i < size; i++) {
                map = maps.get(i);
                bean = clazz.newInstance();
                mapToBean(map, bean);
                list.add(bean);
            }
        }
        return list;
    }
}

 

controller

 

//调用service获取数据

List<Course> list =thisService.list;

//设置excel标题

String [] title ={"ID","学科","学分"};

//excel文件名

String fileName = "课程表"+System.currentTimeMillis()+".xls";

//sheet名

String sheetName = "课程信息表";

//将List<T>转化为List<Map<String,objcet>>

List<Map<String,Object>> content =Transfer.objectsToMaps(list);

try {

//创建HSSFWorkbook

HSSFWorkbook wb = ExcelUtils.getExcelObject(sheetName,title,content,null,Course.class);

ExcelUtils.setResponseHeader(response,fileName);

ExcelUtils.printExcel(response,wb);

} catch (IOException e) {

e.printStackTrace();

}