AI专业者开发社区 1 号成员
  •  0 回帖  •  4 浏览  •  4 个月前
 

Spring Boot利用poi导出Excel

至于 poi 的用法就不多说了，网上多得很，但是发现 spring boot 结合 poi 的就不多了，而且大多也有各种各样的问题。

public class ExcelData implements Serializable {

</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> final <span style="color: rgba(0, 0, 255, 1)">long</span> serialVersionUID = <span style="color: rgba(128, 0, 128, 1)">4444017239100620999L</span><span style="color: rgba(0, 0, 0, 1)">;

</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 表头</span>
<span style="color: rgba(0, 0, 255, 1)">private</span> List&lt;String&gt;<span style="color: rgba(0, 0, 0, 1)"> titles;

</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 数据</span>
<span style="color: rgba(0, 0, 255, 1)">private</span> List&lt;List&lt;Object&gt;&gt;<span style="color: rgba(0, 0, 0, 1)"> rows;

</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 页签名称</span>
<span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String name;

</span><span style="color: rgba(0, 0, 255, 1)">public</span> List&lt;String&gt;<span style="color: rgba(0, 0, 0, 1)"> getTitles() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> titles;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setTitles(List&lt;String&gt;<span style="color: rgba(0, 0, 0, 1)"> titles) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.titles =<span style="color: rgba(0, 0, 0, 1)"> titles;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> List&lt;List&lt;Object&gt;&gt;<span style="color: rgba(0, 0, 0, 1)"> getRows() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> rows;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setRows(List&lt;List&lt;Object&gt;&gt;<span style="color: rgba(0, 0, 0, 1)"> rows) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.rows =<span style="color: rgba(0, 0, 0, 1)"> rows;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getName() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> name;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setName(String name) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.name =<span style="color: rgba(0, 0, 0, 1)"> name;
}



}

public class ExportExcelUtils {
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
        exportExcel(data, response.getOutputStream());
    }

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span> exportExcel(ExcelData data, OutputStream <span style="color: rgba(0, 0, 255, 1)">out</span><span style="color: rgba(0, 0, 0, 1)">) throws Exception {

    XSSFWorkbook wb </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> XSSFWorkbook();
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        String sheetName </span>=<span style="color: rgba(0, 0, 0, 1)"> data.getName();
        </span><span style="color: rgba(0, 0, 255, 1)">if</span> (<span style="color: rgba(0, 0, 255, 1)">null</span> ==<span style="color: rgba(0, 0, 0, 1)"> sheetName) {
            sheetName </span>= <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Sheet1</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
        }
        XSSFSheet sheet </span>=<span style="color: rgba(0, 0, 0, 1)"> wb.createSheet(sheetName);
        writeExcel(wb, sheet, data);

        wb.write(</span><span style="color: rgba(0, 0, 255, 1)">out</span><span style="color: rgba(0, 0, 0, 1)">);
    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        wb.close();
    }
}

</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {

    </span><span style="color: rgba(0, 0, 255, 1)">int</span> rowIndex = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;

    rowIndex </span>=<span style="color: rgba(0, 0, 0, 1)"> writeTitlesToExcel(wb, sheet, data.getTitles());
    writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
    autoSizeColumns(sheet, data.getTitles().size() </span>+ <span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">);

}

</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">int</span> writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List&lt;String&gt;<span style="color: rgba(0, 0, 0, 1)"> titles) {
    </span><span style="color: rgba(0, 0, 255, 1)">int</span> rowIndex = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
    </span><span style="color: rgba(0, 0, 255, 1)">int</span> colIndex = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;

    Font titleFont </span>=<span style="color: rgba(0, 0, 0, 1)"> wb.createFont();
    titleFont.setFontName(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">simsun</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
    titleFont.setBold(</span><span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">);
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> titleFont.setFontHeightInPoints((short) 14);</span>



        titleFont.setColor(IndexedColors.BLACK.index);


    XSSFCellStyle titleStyle </span>=<span style="color: rgba(0, 0, 0, 1)"> wb.createCellStyle();
    titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    titleStyle.setFillForegroundColor(</span><span style="color: rgba(0, 0, 255, 1)">new</span> XSSFColor(<span style="color: rgba(0, 0, 255, 1)">new</span> Color(<span style="color: rgba(128, 0, 128, 1)">182</span>, <span style="color: rgba(128, 0, 128, 1)">184</span>, <span style="color: rgba(128, 0, 128, 1)">192</span><span style="color: rgba(0, 0, 0, 1)">)));
    titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    titleStyle.setFont(titleFont);
    setBorder(titleStyle, BorderStyle.THIN, </span><span style="color: rgba(0, 0, 255, 1)">new</span> XSSFColor(<span style="color: rgba(0, 0, 255, 1)">new</span> Color(<span style="color: rgba(128, 0, 128, 1)">0</span>, <span style="color: rgba(128, 0, 128, 1)">0</span>, <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">)));

    Row titleRow </span>=<span style="color: rgba(0, 0, 0, 1)"> sheet.createRow(rowIndex);
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> titleRow.setHeightInPoints(25);</span>
    colIndex = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;

    </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String field : titles) {
        Cell cell </span>=<span style="color: rgba(0, 0, 0, 1)"> titleRow.createCell(colIndex);
        cell.setCellValue(field);
        cell.setCellStyle(titleStyle);
        colIndex</span>++<span style="color: rgba(0, 0, 0, 1)">;
    }

    rowIndex</span>++<span style="color: rgba(0, 0, 0, 1)">;
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> rowIndex;
}

</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">int</span> writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List&lt;List&lt;Object&gt;&gt; rows, <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> rowIndex) {
    </span><span style="color: rgba(0, 0, 255, 1)">int</span> colIndex = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;

    Font dataFont </span>=<span style="color: rgba(0, 0, 0, 1)"> wb.createFont();
    dataFont.setFontName(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">simsun</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> dataFont.setFontHeightInPoints((short) 14);</span>



        dataFont.setColor(IndexedColors.BLACK.index);


    XSSFCellStyle dataStyle </span>=<span style="color: rgba(0, 0, 0, 1)"> wb.createCellStyle();
    dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    dataStyle.setFont(dataFont);
    setBorder(dataStyle, BorderStyle.THIN, </span><span style="color: rgba(0, 0, 255, 1)">new</span> XSSFColor(<span style="color: rgba(0, 0, 255, 1)">new</span> Color(<span style="color: rgba(128, 0, 128, 1)">0</span>, <span style="color: rgba(128, 0, 128, 1)">0</span>, <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">)));

    </span><span style="color: rgba(0, 0, 255, 1)">for</span> (List&lt;Object&gt;<span style="color: rgba(0, 0, 0, 1)"> rowData : rows) {
        Row dataRow </span>=<span style="color: rgba(0, 0, 0, 1)"> sheet.createRow(rowIndex);
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> dataRow.setHeightInPoints(25);</span>
        colIndex = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;

        </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (Object cellData : rowData) {
            Cell cell </span>=<span style="color: rgba(0, 0, 0, 1)"> dataRow.createCell(colIndex);
            </span><span style="color: rgba(0, 0, 255, 1)">if</span> (cellData != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">) {
                cell.setCellValue(cellData.toString());
            } </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)"> {
                cell.setCellValue(</span><span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">);
            }

            cell.setCellStyle(dataStyle);
            colIndex</span>++<span style="color: rgba(0, 0, 0, 1)">;
        }
        rowIndex</span>++<span style="color: rgba(0, 0, 0, 1)">;
    }
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> rowIndex;
}

</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span> autoSizeColumns(Sheet sheet, <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> columnNumber) {

    </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> i = <span style="color: rgba(128, 0, 128, 1)">0</span>; i &lt; columnNumber; i++<span style="color: rgba(0, 0, 0, 1)">) {
        </span><span style="color: rgba(0, 0, 255, 1)">int</span> orgWidth =<span style="color: rgba(0, 0, 0, 1)"> sheet.getColumnWidth(i);
        sheet.autoSizeColumn(i, </span><span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">int</span> newWidth = (<span style="color: rgba(0, 0, 255, 1)">int</span>) (sheet.getColumnWidth(i) + <span style="color: rgba(128, 0, 128, 1)">100</span><span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">if</span> (newWidth &gt;<span style="color: rgba(0, 0, 0, 1)"> orgWidth) {
            sheet.setColumnWidth(i, newWidth);
        } </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)"> {
            sheet.setColumnWidth(i, orgWidth);
        }
    }
}

</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
    style.setBorderTop(border);
    style.setBorderLeft(border);
    style.setBorderRight(border);
    style.setBorderBottom(border);
    style.setBorderColor(BorderSide.TOP, color);
    style.setBorderColor(BorderSide.LEFT, color);
    style.setBorderColor(BorderSide.RIGHT, color);
    style.setBorderColor(BorderSide.BOTTOM, color);
}<br>}</span></pre>



@RestController
public class ExcelController {
    @RequestMapping(value = "/excel", method = RequestMethod.GET)
    public void excel(HttpServletResponse response) throws Exception {
        ExcelData data = new ExcelData();
        data.setName("hello");
        List<String> titles = new ArrayList();
        titles.add("a1");
        titles.add("a2");
        titles.add("a3");
        data.setTitles(titles);

    List</span>&lt;List&lt;Object&gt;&gt; rows = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ArrayList();
    List</span>&lt;Object&gt; row = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ArrayList();
    row.add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">11111111111</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
    row.add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">22222222222</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
    row.add(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">3333333333</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
    rows.add(row);

    data.setRows(rows);


    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">生成本地</span>
    <span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">File f = new File("c:/test.xlsx");
    FileOutputStream out = new FileOutputStream(f);
    ExportExcelUtils.exportExcel(data, out);
    out.close();</span><span style="color: rgba(0, 128, 0, 1)">*/</span><span style="color: rgba(0, 0, 0, 1)">
    ExportExcelUtils.exportExcel(response,</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">hello.xlsx</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,data);
}



}

功能比较简单，没有做太多扩展，想自己扩展的可以查看 poi 的官方文档自行扩展，简单的很。

 

如果用 POST 提交，前端不能用 Ajax，只能用 form 表单提交，参数接受 Controller 那一块用 @ModelAttribute,@RequestBody 是专门用于接受 Json 数据的

 

项目地址：https://github.com/xiaopotian1990/SpringBootExcel