Excel导出

数据的导出在实际项目中也会用到，不妨先学习了解它的用法，有备无患。它利用了Apache官网的jar包，来创建一个Workbook，然后利用workbook的write方法方法，将excel下载。
由于我喜欢用反射，所以在导出中需要注意：反射后获取的值，需要判断它的类型；单元格的样式需要设置，否则导出来的excel不美观。根据官网的文档学习这个很简单。贴上测试代码。

public class ExcelUtil {
    public static <T> Workbook createExcel(List<T> list,String title) throws Exception{
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        //合并单元格，让标题单独一行
        //最后一个参数8，根据每列的标题的个数来确定
        //在方法签名中修改createExcel(List<T> list,String title,String[] headers),下面修改为sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8);
        //创建标题头的字体和居中对齐
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(title);
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font titlefont = workbook.createFont();
        titlefont.setBold(true);
        titleStyle.setFont(titlefont);
        titleCell.setCellStyle(titleStyle);

        //创建内容单元
        //反射获取T中的成员变量名
        Class<T> clazz = (Class<T>) list.get(0).getClass();
        Field[] fields = clazz.getDeclaredFields();
        for(int i=0;i<list.size();i++){
            Row cellRow = sheet.createRow(i+1);
            T t = list.get(i);
            for(int j=0;j<fields.length;j++){
                Cell cell =cellRow.createCell(j);
                getMethod(fields[j], clazz, t, cell);
            }
        }
        return workbook;
    }

    public static <T> void getMethod(Field field,Class clazz,T t,Cell cell) throws Exception{
        String fieldName = field.getName();
        String getter = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
        Method method  = clazz.getDeclaredMethod(getter);
        Object value = method.invoke(t);
        if(value!=null&&value.hashCode()!=0){
            if(field.getType()==Date.class){
                cell.setCellValue((Date)value);
            }else if(field.getType()==Double.class||field.getType()==double.class){
                cell.setCellValue(Double.parseDouble(value.toString()));
            }else if(field.getType()==Boolean.class||field.getType()==boolean.class){
                cell.setCellValue(Boolean.parseBoolean(value.toString()));
            }else if(field.getType()==Integer.class||field.getType()==int.class){
                cell.setCellValue(Integer.valueOf(value.toString()));
            }else{
                cell.setCellValue(value.toString());
            }
        }
    }

}

//这里我利用的springmvc框架做的后台处理
@Controller
@RequestMapping(value="/file")
public class DowUpFile {

    @Resource
    private RoomDetailService roomDetailService;

    @RequestMapping(value="/download.do")
    public void downLoad(String fileName,HttpServletResponse response) throws Exception{
        if(fileName==null||fileName.hashCode()==0){
            fileName = "测试导出";
        }
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        //设置下载文件编码，解决乱码问题
        response.setHeader("Content-Disposition",
        "attachment;filename="+URLEncoder.encode(exportName+".xls", "utf-8"));
        OutputStream out = response.getOutputStream();
        Workbook workbook = ExcelUtil.createExcel(roomDetailService.queryAll(), "客房信息");
        workbook.write(out);
        out.flush();
        out.close();
    }

}

当在po中，设置成员变量的为String类型，而存储的是数字。导出之后，显示的左上角仍有尖号，可以转成数字的提示。所以在代码中可以进一步修改。

Object value = method.invoke(t);
        if(value!=null&&value.hashCode()!=0){
            if(field.getType()==Date.class){
                cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
                cell.setCellValue((Date)value);
            }else if(field.getType()==Double.class||field.getType()==double.class){
                cell.setCellValue(Double.parseDouble(value.toString()));
            }else if(field.getType()==Boolean.class||field.getType()==boolean.class){
                cell.setCellValue(Boolean.parseBoolean(value.toString()));
            }else if(field.getType()==Integer.class||field.getType()==int.class){
                cell.setCellValue(Integer.valueOf(value.toString()));
                //用try catch方法可以解决该问题。
                try{
                    Integer ifInteger = Integer.valueOf(value.toString());
                    cell.setCellValue(ifInteger);
                }catch(Exception e){
                    cell.setCellValue(value.toString());
                }
            }
        }

问题记录:

//用这种方式解决乱码，火狐浏览器显示的是乱码，opera和谷歌浏览器正常。
response.setHeader("Content-Disposition",
        "attachment;filename="+URLEncoder.encode(exportName+".xls", "utf-8"));
//用这种方式，三个浏览器下载后的名字全为(.xls)不能显示中文，如果exportName为英文就能正常显示
response.setHeader("Content-Disposition",
        "attachment;filename="+exportName+".xls");