015.Excel文件导出

第一先定义一个类,配置表格的字体行距字体大小

public void reportExport(List<?> listdata, String reportName, HttpServletResponse response) {
    ResourceBundle rb = ResourceBundle.getBundle("constants");
    // 文件名
    String fileName = rb.getString(reportName + ".fileName");
    // 表名
    String sheetName = reportName + ".sheetName";
    // 字段名
    String sheetcolumn = reportName + ".columnName";
    // 取出list长度
    int size = 0;
    if (listdata!=null) size = listdata.size();
    // 读取properties中的内容
    String Name = rb.getString(sheetName);
    // 在Excel工作簿中建一工作表
    SXSSFWorkbook workbook = new SXSSFWorkbook(100);
    // 设置表格样式
    XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
    // 报表边框
    cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    XSSFFont font = (XSSFFont) workbook.createFont();
    // 单元格字体
    font.setFontName("宋体");
    font.setFontHeight(9);
    cellStyle.setFont(font);
    // 新建一名为sheetName的工作表
    SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(Name);
    // 在索引的位置创建行（根据记录数添加）
    SXSSFRow[] row = new SXSSFRow[size + 1];
    row[0] = (SXSSFRow) sheet.createRow(0);
    int g = 0;
    int m = 0;
    int i = 0;
    // 写入表头
    try {
        for (i = 0; i < 1000; i++) {
            CellUtil.createCell(row[0], i, rb.getString(sheetcolumn + "[" + i + "]." + i), cellStyle);
        }
    } catch (MissingResourceException e) {
        m = i;
    }

    // 写入具体数据
    for (int j = 1; j <= size; j++) {
        row[j] = (SXSSFRow) sheet.createRow(j);
        g = 0;
        for (Field data : listdata.get(j - 1).getClass().getDeclaredFields()) {
            data.setAccessible(true);
            // 创建单元格
            SXSSFCell cell = (SXSSFCell) row[j].createCell(g);
            cell.setCellStyle(cellStyle);
            try {
                if (data.get(listdata.get(j - 1)) != null && !data.get(listdata.get(j - 1)).equals("")) {
                    // 填入数据
                    if (data.get(listdata.get(j - 1)) instanceof String) {
                        if(data.get(listdata.get(j - 1)).toString().contains("@%#")){ // 字符串换行处理
                            String str[]=data.get(listdata.get(j - 1)).toString().split("@%#");
                            String resultstr="";
                            for(int k=0;k<str.length;k++){
                                resultstr+=str[k]+"\n";
                            }
                            cellStyle.setWrapText(true);
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(resultstr);
                        }else{
                            cell.setCellValue((String) data.get(listdata.get(j - 1)));
                        }

                    }
                    if (data.get(listdata.get(j - 1)) instanceof Double) {
                        cell.setCellValue((Double) data.get(listdata.get(j - 1)));
                    }
                    if (data.get(listdata.get(j - 1)) instanceof Integer) {
                        cell.setCellValue((Integer) data.get(listdata.get(j - 1)));
                    }
                    if (data.get(listdata.get(j - 1)) instanceof Date) {
                        cell.setCellValue(FasDateUtils.formatDateTime((Date)data.get(listdata.get(j - 1))));
                    }
                }
                g++;
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }
    // 宽度自适应
    for (int k = 0; k < m; k++) {
        sheet.autoSizeColumn(k);
    }
    for (int columnNum = 0; columnNum < size; columnNum++) {

        int columnWidth = sheet.getColumnWidth(columnNum) / 256;
        for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
            Cell a = row[rowNum].getCell(columnNum);
            if (a != null) {
                if (a.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    int length = a.getStringCellValue().getBytes().length;
                    if (columnWidth < length) {
                        columnWidth = length;
                    }
                }
            }
        }
        // 限定最大宽度
        if (columnWidth >= 40) {
            columnWidth = 40;
        }
        sheet.setColumnWidth(columnNum, columnWidth * 256);
    }
    try {
        // 将excel放入输出流推出
        OutputStream os = response.getOutputStream();
        BufferedOutputStream out = new BufferedOutputStream(os);
        // fileDownload插件以此判断下载是否成功,不设置则不执行成功回调函数
        response.setHeader("Set-Cookie", "fileDownload=true; path=/");
        // 设置文件类型
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        // 关闭流
        workbook.write(out);
        out.flush();
        out.close();
        workbook.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

其次在配置文件中定义每一列的对应编号从0开始为第一列，fileName为文件的外部名称

最后Controller的java代码

前端不同的框架访问的方式不同需要的数据类型也不同，根据自己需要返回不同的数据类型。