找到
1
篇与
typecho,php,blog
相关的结果
-
Java使用SXSSFWorkbook导出Excel工具类 列 1col2 （列2）col3 前引 在项目中经常有导出Excel的需求，为了方便就封装了一个导出Excel到前端的工具类，使用下来还比较好用 使用JDK版本：1.8 spring boot版本：2.6.15 前端请求：axios Java代码 Excel所需Maven依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency>完整代码如下： public class excelUtils { public static CellStyle headStyle(Workbook workbook) { // 创建单元格样式 CellStyle headerStyle = workbook.createCellStyle(); // 设置字体样式 Font headerFont = workbook.createFont(); headerFont.setBold(true); // 加粗 headerFont.setFontHeightInPoints((short) 12); // 字体大小 headerStyle.setFont(headerFont); // 可选：设置其他样式属性 headerStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中 headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中 return headerStyle; } public static CellStyle tableStyle(Workbook workbook) { CellStyle tableStyle = workbook.createCellStyle(); tableStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中 tableStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中 return tableStyle; } /** * @author Mango * * 获取对象的字段值 * @param object 对象 * @param key 字段名 * @return 字段值 */ public static String getFieldString(Object object, String key) { if(object instanceof Map){ return String.valueOf(((Map<?, ?>) object).get(key)); } Field field = null; String value = ""; try { field = object.getClass().getDeclaredField(key); field.setAccessible(true); String orgStr = String.valueOf(field.get(object)); if (orgStr != null) { value = orgStr; // 处理数组类型 if (orgStr.startsWith("[") && orgStr.endsWith("]")) { value = orgStr .replace("[", "") .replace("]", "") .replace("\"", ""); } } } catch (NoSuchFieldException e) { throw new RuntimeException(e); } catch (IllegalAccessException e) { throw new RuntimeException(e); } return value; } /** * @author Mango * 导出Excel * @param response HttpServletResponse * @param srcList 数据源 * @param keys 数据源对应字段列表 * @param titles 表头 * @param excelName 文件名 */ public static void exportExcel(HttpServletResponse response, List<?> srcList, List<String> keys, List<String> titles, String excelName) { // 创建workbook // 使用 SXSSFWorkbook 比 XSSFWorkbook 内存占用少 SXSSFWorkbook workbook = new SXSSFWorkbook(); // 根据workbook创建sheet Sheet sheet = workbook.createSheet(excelName); //设置表头 Row titleRoe = sheet.createRow(0); // 设置表头样式 CellStyle headStyle = headStyle(workbook); // 向单元格中添加表头数据 for (int i = 0; i < titles.size(); i++) { Cell cell = titleRoe.createCell(i); cell.setCellValue(titles.get(i)); cell.setCellStyle(headStyle); } // 定义数据样式 CellStyle tableStyle = tableStyle(workbook); // 处理表格数据 for (int i = 0; i < srcList.size(); i++) { // 获取一组数据 Object object = srcList.get(i); // 生成行 Row rowData = sheet.createRow(i + 1); // 设置行高 rowData.setHeight((short) 400); // 向每一行中的单元格添加数据 // keys 为对象字段列表 for (int j = 0; j < keys.size(); j++) { String key = keys.get(j); Cell cell = rowData.createCell(j); cell.setCellStyle(tableStyle); String value = getFieldString(object, key); cell.setCellValue(value); } // 每100行就写一次磁盘，减少内存消耗 if (i % 100 == 0) { try { ((SXSSFSheet) sheet).flushRows(100); // 将缓存中的数据写入磁盘 } catch (IOException e) { throw new RuntimeException(e); } } } // 获取当前时间作为文件名 LocalDateTime localDateTime = LocalDateTime.now(); String fileName = excelName + localDateTime.format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx"; // 设置响应头 try { response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); } catch (UnsupportedEncodingException e) { // 发生异常不处理中文文件名 response.setHeader("Content-Disposition", "attachment;filename=" + fileName); throw new RuntimeException(e); } response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Accept-Ranges", "bytes"); // 返回数据 返回进度条 try (ByteArrayOutputStream fileOutputStream = new ByteArrayOutputStream(); ServletOutputStream out = response.getOutputStream()) { // 这里假设 workbook 是生成的 Excel 文件对象 workbook.write(fileOutputStream); // 获取文件的总大小，并设置响应头 byte[] fileBytes = fileOutputStream.toByteArray(); response.setHeader("Content-Length", String.valueOf(fileBytes.length)); // 分块传输文件内容 int chunkSize = 256; // 每次写入数据字节 int totalSize = fileBytes.length; int currentPosition = 0; while (currentPosition < totalSize) { int remaining = totalSize - currentPosition; int chunk = Math.min(chunkSize, remaining); // 写入一块数据 out.write(fileBytes, currentPosition, chunk); out.flush(); // 刷新输出流，确保数据传送给客户端 // 更新当前下载位置 currentPosition += chunk; } out.close(); workbook.close(); // 关闭工作簿 } catch (IOException e) { // 处理异常 response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map<String, String> map = new HashMap<>(); map.put("message", "下载文件失败: " + e.getMessage()); try { response.getWriter().println(JSON.toJSONString(map)); } catch (IOException ee) { ee.printStackTrace(); } e.printStackTrace(); } } }前端请求示例 请求接口 export const outExcel = async ( data: any, downloadProgress: Function, ) => { return await request({ url: "/test/outExcel", method: "post", data, responseType: "blob", onDownloadProgress: (event: any) => { downloadProgress(event); }, }); }注： 这里的代码是已经封装过的统一封装的axios responseType： “blob” 指定文件为二进制 onDownloadProgress利用回调函数返回下载进度 下载示例 const exportExcel = async () => { isProcessing.value = true; let response = null; try { response = await props.downloadMethod(props.searchForm, (event: any) => { progress.value = (event.loaded / event.total) * 100; if (progress.value > 0) { isProcessing.value = false; } }); } catch (e: any) { isProcessing.value = false; //出错时结束处理状态 isError.value = true; //下载失败 } if (response == null) return; // 没有返回数据 if (!response.status) { isError.value = true; //接口返回错误设置为下载失败 } const fileName = "教师参与情况" + formatDate(new Date(), "yy-MM-dd hh:mm:ss") + ".xlsx"; const blob = new Blob([response.data], { type: "application/vnd.ms-excel" }); // 创建一个链接来下载 Blob 对象 const downloadLink = document.createElement("a"); downloadLink.href = URL.createObjectURL(blob); downloadLink.download = decodeURI(fileName); downloadLink.style.display = "none"; document.body.appendChild(downloadLink); // 触发点击事件以下载文件 downloadLink.click(); // 清理创建的链接对象 document.body.removeChild(downloadLink); };将props.downloadMethod替换为下载接口 props.searchFormt替换为筛选数据
