怎么在java项目中导出Excel文件?很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
创新互联科技有限公司专业互联网基础服务商,为您提供成都移动机房托管,高防物理服务器租用,成都IDC机房托管,成都主机托管等互联网服务。
项目结构如下:
修改pom文件,添加依赖;
org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-test test org.apache.poi poi 3.14 org.apache.poi poi-ooxml 3.14 org.apache.poi poi-contrib 3.6 org.apache.poi poi-ooxml-schemas 3.17 org.apache.poi poi-scratchpad 3.17
新建一个实体类,命名为User.java;
package com.twf.springcloud.ExportExcel.po; import java.io.Serializable; public class User implements Serializable{ private static final long serialVersionUID = -9180229310895087286L; private String name; // 姓名 private String sex; // 性别 private Integer age; // 年龄 private String phoneNo; // 手机号 private String address; // 地址 private String hobby; // 爱好 public User(String name, String sex, Integer age, String phoneNo, String address, String hobby) { super(); this.name = name; this.sex = sex; this.age = age; this.phoneNo = phoneNo; this.address = address; this.hobby = hobby; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getPhoneNo() { return phoneNo; } public void setPhoneNo(String phoneNo) { this.phoneNo = phoneNo; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getHobby() { return hobby; } public void setHobby(String hobby) { this.hobby = hobby; } @Override public String toString() { return "User [name=" + name + ", sex=" + sex + ", age=" + age + ", phoneNo=" + phoneNo + ", address=" + address + ", hobby=" + hobby + "]"; } }
新建一个excel样式工具类;
package com.twf.springcloud.ExportExcel.utils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; /** * excle样式工具类 */ public class ExcelFormatUtil { /** * 设置报表头样式 * @param workbook * @return */ public static CellStyle headSytle(SXSSFWorkbook workbook){ // 设置style1的样式,此样式运用在第二行 CellStyle style1 = workbook.createCellStyle();// cell样式 // 设置单元格背景色,设置单元格背景色以下两句必须同时设置 style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式 style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色 // 设置单元格上、下、左、右的边框线 style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); Font font1 = workbook.createFont();// 创建一个字体对象 font1.setBoldweight((short) 10);// 设置字体的宽度 font1.setFontHeightInPoints((short) 10);// 设置字体的高度 font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 style1.setFont(font1);// 设置style1的字体 style1.setWrapText(true);// 设置自动换行 style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向) style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向) return style1; } /** * 设置报表体样式 * @param wb * @return */ public static CellStyle contentStyle(SXSSFWorkbook wb){ // 设置style1的样式,此样式运用在第二行 CellStyle style1 = wb.createCellStyle();// cell样式 // 设置单元格上、下、左、右的边框线 style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setWrapText(true);// 设置自动换行 style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显示居中(左右方向) style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向) return style1; } /** * 设置报表标题样式 * @param workbook * @return */ public static HSSFCellStyle titleSytle(HSSFWorkbook workbook,short color,short fontSize){ // 设置style1的样式,此样式运用在第二行 HSSFCellStyle style1 = workbook.createCellStyle();// cell样式 // 设置单元格背景色,设置单元格背景色以下两句必须同时设置 //style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式 //short fcolor = color; if(color != HSSFColor.WHITE.index){ style1.setFillForegroundColor(color);// 设置填充色 } // 设置单元格上、下、左、右的边框线 style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = workbook.createFont();// 创建一个字体对象 font1.setBoldweight(fontSize);// 设置字体的宽度 font1.setFontHeightInPoints(fontSize);// 设置字体的高度 font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 style1.setFont(font1);// 设置style1的字体 style1.setWrapText(true);// 设置自动换行 style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向) style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向) return style1; } /** *设置表头 * @param sheet */ public static void initTitleEX(SXSSFSheet sheet, CellStyle header,String title[],int titleLength[]) { SXSSFRow row0 = sheet.createRow(0); row0.setHeight((short) 800); for(int j = 0;j新建一个Service接口;
package com.twf.springcloud.ExportExcel.sevice; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.http.ResponseEntity; public interface ExportService { ResponseEntityexportExcel(HttpServletRequest request, HttpServletResponse response); } 新建一个Service接口实现类;
package com.twf.springcloud.ExportExcel.sevice.impl; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Service; import com.twf.springcloud.ExportExcel.controller.BaseFrontController; import com.twf.springcloud.ExportExcel.po.User; import com.twf.springcloud.ExportExcel.sevice.ExportService; import com.twf.springcloud.ExportExcel.utils.ExcelFormatUtil; @Service public class ExportServiceImpl implements ExportService{ Logger logger = LoggerFactory.getLogger(ExportServiceImpl.class); @Override public ResponseEntityexportExcel(HttpServletRequest request, HttpServletResponse response) { try { logger.info(">>>>>>>>>>开始导出excel>>>>>>>>>>"); // 造几条数据 List list = new ArrayList<>(); list.add(new User("唐三藏", "男", 30, "13411111111", "东土大唐", "取西经")); list.add(new User("孙悟空", "男", 29, "13411111112", "菩提院", "打妖怪")); list.add(new User("猪八戒", "男", 28, "13411111113", "高老庄", "偷懒")); list.add(new User("沙悟净", "男", 27, "13411111114", "流沙河", "挑担子")); BaseFrontController baseFrontController = new BaseFrontController(); return baseFrontController.buildResponseEntity(export((List ) list), "用户表.xls"); } catch (Exception e) { e.printStackTrace(); logger.error(">>>>>>>>>>导出excel 异常,原因为:" + e.getMessage()); } return null; } private InputStream export(List list) { logger.info(">>>>>>>>>>>>>>>>>>>>开始进入导出方法>>>>>>>>>>"); ByteArrayOutputStream output = null; InputStream inputStream1 = null; SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 保留1000条数据在内存中 SXSSFSheet sheet = wb.createSheet(); // 设置报表头样式 CellStyle header = ExcelFormatUtil.headSytle(wb);// cell样式 CellStyle content = ExcelFormatUtil.contentStyle(wb);// 报表体样式 // 每一列字段名 String[] strs = new String[] { "姓名", "性别", "年龄", "手机号", "地址","爱好" }; // 字段名所在表格的宽度 int[] ints = new int[] { 5000, 5000, 5000, 5000, 5000, 5000 }; // 设置表头样式 ExcelFormatUtil.initTitleEX(sheet, header, strs, ints); logger.info(">>>>>>>>>>>>>>>>>>>>表头样式设置完成>>>>>>>>>>"); if (list != null && list.size() > 0) { logger.info(">>>>>>>>>>>>>>>>>>>>开始遍历数据组装单元格内容>>>>>>>>>>"); for (int i = 0; i < list.size(); i++) { User user = list.get(i); SXSSFRow row = sheet.createRow(i + 1); int j = 0; SXSSFCell cell = row.createCell(j++); cell.setCellValue(user.getName()); // 姓名 cell.setCellStyle(content); cell = row.createCell(j++); cell.setCellValue(user.getSex()); // 性别 cell.setCellStyle(content); cell = row.createCell(j++); cell.setCellValue(user.getAge()); // 年龄 cell.setCellStyle(content); cell = row.createCell(j++); cell.setCellValue(user.getPhoneNo()); // 手机号 cell.setCellStyle(content); cell = row.createCell(j++); cell.setCellValue(user.getAddress()); // 地址 cell.setCellStyle(content); cell = row.createCell(j++); cell.setCellValue(user.getHobby()); // 爱好 cell.setCellStyle(content); } logger.info(">>>>>>>>>>>>>>>>>>>>结束遍历数据组装单元格内容>>>>>>>>>>"); } try { output = new ByteArrayOutputStream(); wb.write(output); inputStream1 = new ByteArrayInputStream(output.toByteArray()); output.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (output != null) { output.close(); if (inputStream1 != null) inputStream1.close(); } } catch (IOException e) { e.printStackTrace(); } } return inputStream1; } } 新建一个下载文件的通用controller;
package com.twf.springcloud.ExportExcel.controller; import java.io.InputStream; import java.net.URLEncoder; import java.util.HashMap; import java.util.Map; import org.apache.poi.util.IOUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.util.StringUtils; import org.springframework.validation.annotation.Validated; @Validated public class BaseFrontController { /** * slf4j 日志 logger */ protected final Logger logger = LoggerFactory.getLogger(this.getClass()); /** * 下载文件,纯SpringMVC的API来完成 * * @param is 文件输入流 * @param name 文件名称,带后缀名 * * @throws Exception */ public ResponseEntitybuildResponseEntity(InputStream is, String name) throws Exception { logger.info(">>>>>>>>>>>>>>>>>>>>开始下载文件>>>>>>>>>>"); if (this.logger.isDebugEnabled()) this.logger.debug("download: " + name); HttpHeaders header = new HttpHeaders(); String fileSuffix = name.substring(name.lastIndexOf('.') + 1); fileSuffix = fileSuffix.toLowerCase(); Map arguments = new HashMap (); arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); arguments.put("xls", "application/vnd.ms-excel"); String contentType = arguments.get(fileSuffix); header.add("Content-Type", (StringUtils.hasText(contentType) ? contentType : "application/x-download")); if(is!=null && is.available()!=0){ header.add("Content-Length", String.valueOf(is.available())); header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8")); byte[] bs = IOUtils.toByteArray(is); logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-有记录>>>>>>>>>>"); logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>"); return new ResponseEntity<>(bs, header, HttpStatus.OK); }else{ String string="数据为空"; header.add("Content-Length", "0"); header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8")); logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-无记录>>>>>>>>>>"); logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>"); return new ResponseEntity<>(string.getBytes(), header, HttpStatus.OK); } } } 新建一个controller,作为请求的入口;
package com.twf.springcloud.ExportExcel.controller; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import com.twf.springcloud.ExportExcel.sevice.ExportService; @RestController @RequestMapping("/exportExcel/") public class ExportController { @Autowired private ExportService exportService; // 导出excel @RequestMapping("exportExcel") public ResponseEntityexportExcel(HttpServletRequest request, HttpServletResponse response) { return exportService.exportExcel(request,response); } } (9)运行ExportExcelApplication,浏览器访问http://localhost:8080/exportExcel/exportExcel,可以下载excel,打开如下:
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注创新互联行业资讯频道,感谢您对创新互联的支持。
分享标题:怎么在java项目中导出Excel文件
转载来于:http://cdkjz.cn/article/jcejie.html