工具类

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;/*** Excel工具类** @author xiaoming* @date 2023/11/17 10:40*/
public class ExcelUtils {/*** 自适应宽度(支持中文)** @param sheet 表单* @param size  因为for循环从0开始，size值为 列数-1*/public static void setSizeColumn(Sheet sheet, int size) {for (int columnNum = 0; columnNum <= size; columnNum++) {int columnWidth = sheet.getColumnWidth(columnNum) / 256;for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {Row currentRow;// 当前行未被使用过if (sheet.getRow(rowNum) == null) {currentRow = sheet.createRow(rowNum);} else {currentRow = sheet.getRow(rowNum);}if (currentRow.getCell(columnNum) != null) {Cell currentCell = currentRow.getCell(columnNum);if (currentCell.getCellType() == CellType.STRING) {int length = currentCell.getStringCellValue().getBytes().length;if (columnWidth < length) {columnWidth = length;}}}}sheet.setColumnWidth(columnNum, columnWidth * 256);}}
}

工具类使用方法

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.http.HttpUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONObject;
import com.xxx.utils.ExcelUtils;
import com.xxx.entity.Entity;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.web.bind.annotation.*;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;/*** 文件接口** @author xiaoming* @date 2023/11/17 10:50*/
@RestController
@RequestMapping("/file")
public class FileController {@PostMapping("exportExcel")public void exportExcel(HttpServletResponse response) {// 假设这里有个List是要导出用的List<Entity> list = ...;ExcelWriter writer = ExcelUtil.getWriter(true);writer.addHeaderAlias("field1", "fieldValue1").addHeaderAlias("field2", "fieldValue2").addHeaderAlias("field3", "fieldValue3");if (CollUtil.isNotEmpty(list)) {writer.write(list, true);// 就只有下面两步操作，根据上面addHeaderAlias的个数减一就行，上面是三个，所以填二Sheet sheet = writer.getSheet();ExcelUtils.setSizeColumn(sheet, 2);ServletOutputStream out = nulltry {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("xxx.xlsx", "UTF-8"));out = response.getOutputStream();writer.flush(out, true);} catch (IOException e) {e.printStackTrace();} finally {writer.close();IoUtil.close(out);}}}
}