一、引入依赖
<!--poi--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.0</version></dependency>
二、操作execl表格，生成自己想要的文件
package com.example.testways.utils;import lombok.extern.slf4j.Slf4j;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.util.CellRangeAddress;import org.ponent;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.FileOutputStream;import java.io.OutputStream;@Slf4j@Componentpublic class ExeclUtil {public static HSSFWorkbook downLoadCollectionList(HttpServletRequest request, HttpServletResponse response) throws Exception {//创建一个HSSFWorkbook，对应一个Excel文件HSSFWorkbook wb = new HSSFWorkbook();//创建单元格，并设置值表头 设置表头居中HSSFCellStyle style = wb.createCellStyle();//文本居中对齐style.setAlignment(HorizontalAlignment.CENTER);//文本垂直居中style.setVerticalAlignment(VerticalAlignment.CENTER);//在workbook中添加一个sheet,对应Excel文件中的sheetHSSFSheet sheet = wb.createSheet("sheet1");// 标题String[] title = {"行人","非机动车","机动车","电动自行车","合计"};//声明列对象HSSFCell cell = null;//合并单元格 第一行 起始行,结束行,起始列,结束列//在sheet中添加表头第0行,HSSFRow row = sheet.createRow(0);//合并第一行 从第一行开始第一行结束，第0列开始 第某列结束megerSheet(0, 0, 0, 3,sheet);megerSheet(0, 0, 4, 7,sheet);megerSheet(0, 0, 8, 10,sheet);megerSheet(0, 0, 11, 13,sheet);megerSheet(0, 0, 14, 16,sheet);int index = 0;for (int i = 0; i < title.length; i++) {//在第一行第一个单元格cell = row.createCell(index);//sheet.setColumnWidth(0, 4000);//sheet.setDefaultRowHeight((short) 1000);// 设置样式为居中cell.setCellStyle(style);//第一行合并内容cell.setCellValue(title[i]);//确保为合并的单元格的首列赋值if(index >= 8){index += 3;}else{index += 4;}}String[] title1 = {"违法数","通行量","守法率","违法总数","通行量","守法率","违法数","通行量","守法率","头盔佩戴数","通行量","头盔佩戴率","违法数","通行量","守法率"};//在sheet中添加表头第1行HSSFRow row1 = sheet.createRow(1);//合并第一行 从第一行开始第一行结束，第0列开始 第某列结束megerSheet(1, 1, 0, 1,sheet);megerSheet(1, 1, 4, 5,sheet);int index1 = 0;for (int i = 0; i < title1.length; i++) {//在第2行第一个单元格cell = row1.createCell(index1);sheet.setColumnWidth(index1, 3000);// 设置样式为居中cell.setCellStyle(style);//第一行合并内容cell.setCellValue(title1[i]);//确保为合并的单元格的首列赋值if(i == 0 || i == 2){index1 = i + 2;}else{if(i == 3){index1 += 2;}else{index1 += 1;}}}//在sheet中添加表头第1行HSSFRow row2 = sheet.createRow(2);HSSFRow row3 = sheet.createRow(3);HSSFRow row4 = sheet.createRow(4);HSSFRow row5 = sheet.createRow(5);HSSFRow row6 = sheet.createRow(6);String[] title2 = {"闯红灯","闯红灯"};String[] title3 = {"不走斑马线","逆行"};String[] title4 = {"不走人行道","走机动车道"};String[] title5 = {"小计","越线停车"};String[] title6 = {"小计","小计"};//合并第一行 从第一行开始第一行结束，第0列开始 第某列结束megerSheet(5, 6, 0, 0,sheet);megerSheet(5, 6, 1, 1,sheet);megerSheet(2, 6, 2, 2,sheet);megerSheet(2, 6, 3, 3,sheet);megerSheet(2, 6, 6, 6,sheet);megerSheet(2, 6, 7, 7,sheet);megerSheet(2, 6, 8, 8,sheet);megerSheet(2, 6, 9, 9,sheet);megerSheet(2, 6, 10, 10,sheet);megerSheet(2, 6, 11, 11,sheet);megerSheet(2, 6, 12, 12,sheet);megerSheet(2, 6, 13, 13,sheet);megerSheet(2, 6, 14, 14,sheet);megerSheet(2, 6, 15, 15,sheet);megerSheet(2, 6, 16, 16,sheet);createCellAndGetValue(title2,cell,row2,sheet,style);createCellAndGetValue(title3,cell,row3,sheet,style);createCellAndGetValue(title4,cell,row4,sheet,style);createCellAndGetValue(title5,cell,row5,sheet,style);createCellAndGetValue(title6,cell,row6,sheet,style);long time = System.currentTimeMillis();//输出到本地//String fileName ="D:\\User\\违法"+time+".xls";//FileOutputStream fileOutputStream = new FileOutputStream(fileName);//wb.write(fileOutputStream);String fileName =time+"路口交通秩序测评结果.xls";response.setContentType("application/octet-stream");response.reset();response.setCharacterEncoding("UTF-8");response.setContentType("application/octet-stream");// 转码防止乱码response.addHeader("Content-Disposition","attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));OutputStream out = response.getOutputStream();wb.write(out);return wb;}/*** 创建单元格并赋值* @param title* @param cell* @param row* @param sheet* @param style*/public static void createCellAndGetValue(String[] title, HSSFCell cell, HSSFRow row, HSSFSheet sheet, HSSFCellStyle style){int index = 0;for (int i = 0; i < title.length; i++) {//在第n行第一个单元格cell = row.createCell(index);sheet.setColumnWidth(index, 4000);// 设置样式为居中cell.setCellStyle(style);//第一行合并内容cell.setCellValue(title[i]);index += 4;}}/*** 合并单元格* @param firstRow* @param lastRow* @param firstCol* @param lastCol* @param sheet*/public static void megerSheet(int firstRow, int lastRow, int firstCol, int lastCol,HSSFSheet sheet){CellRangeAddress callAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);sheet.addMergedRegion(callAddress);}}
三、测试
package com.example.testways.controller;import com.example.testways.utils.ExeclUtil;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;@RestController@RequestMapping("/operatrExecl")public class OperateExeclController {@GetMapping("/downLoadCollectionList")public void downLoadCollectionList(HttpServletRequest request, HttpServletResponse response) throws Exception {ExeclUtil.downLoadCollectionList(request,response);}}
四、启动项目之后，访问相应的接口，可以看到正在下载execl表格；
本测试代码生成的excel表格效果如图所示：
