springboot生成excel文件
springboot 生成excel文件使用poi库,引用如下:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
代码编写如下,主要部分都加了注释了
@Controller
@RequestMapping("/excel")
public class ExcelDownloadController {
private Gson mGson = new Gson();
@PostMapping(value = "/makeStatisticTableExcel")
private void makeStatisticTableExcel(@RequestParam(name="sock_name") String data) throws IOException {
Map<String, Object> result = new HashMap<>();
TableToExcelEntity entity = mGson.fromJson(data, TableToExcelEntity.class);
//声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("统计表");
sheet.setDefaultColumnWidth(20);
//声明表头
HSSFRow headrow = sheet.createRow(0);
HSSFCell tempCell = headrow.createCell(0);
HSSFRichTextString tempText = new HSSFRichTextString("");
tempCell.setCellValue(tempText);
for (int i = 0; i < entity.getHeader().size(); i++) {
String title = entity.getHeader().get(i);
//创建一个单元格
HSSFCell cell = headrow.createCell(i + 1);
//创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(title);
//将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
}
for (int i = 0; i < entity.getTablelines().size(); i++) {
List<String> tableLine = entity.getTablelines().get(i);
if (tableLine == null || tableLine.isEmpty()) {
continue;
}
HSSFRow row = sheet.createRow(i + 1);
for (int j = 0; j < tableLine.size(); j++) {
String s = tableLine.get(j);
HSSFCell cell = row.createCell(j);
HSSFRichTextString text = new HSSFRichTextString(s);
cell.setCellValue(text);
}
}
//获取response
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
//八进制输出流
response.setContentType("application/octet-stream");
//这后面可以设置导出Excel的名称,此例中名为statistic.xls
response.setHeader("Content-Disposition", "attachment;filename=statistic.xlsx");
//刷新缓冲
response.flushBuffer();
//workbook将Excel写入到response的输出流中,供页面下载
workbook.write(response.getOutputStream());
}
注意:要想浏览器自动弹出下载弹窗使用下面俩种方式
1.请求方法使用GET
2.如果需要带大量数据得使用POST方式,需要前端使用form方式请求,前端代码如下:
var url = "http://192.168.100.14:8766/excel/makeStatisticTableExcel"
var data = ""
var form = document.createElement("form")
form.setAttribute("style",'display:none')
form.setAttribute('target','')
form.setAttribute('method','post')
form.setAttribute('action',url)
var input = document.createElement("input")
input.setAttribute('type','hidden')
input.setAttribute('name','sock_name')
input.setAttribute('value',data)
form.appendChild(input)
document.getElementsByTagName('body')[0].appendChild(form)
form.submit()
后端接口接受数据的时候需要注意,使用@RequestParam,name需要和input标签指定的name一致。