本文共 9688 字,大约阅读时间需要 32 分钟。
org.apache.poi poi 4.1.2
/** * 导出 Excel 表格 * @return */ @GetMapping("/export") public ResponseEntityexportData() { // 1.这一步就是查询你要导出的数据 List employeeList = employeeService.getEmployees(); // 2.创建一个 POIUtils 工具类进行导出操作 return POIUtils.employee2Excel(employeeList); }
import org.apache.poi.hpsf.DocumentSummaryInformation;import org.apache.poi.hpsf.SummaryInformation;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.IndexedColors;import org.javaboy.vhr.bean.*;import org.springframework.http.HttpHeaders;import org.springframework.http.HttpStatus;import org.springframework.http.MediaType;import org.springframework.http.ResponseEntity;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.util.List;/** * Excel导入导出工具类 */public class POIUtils { /** * 导出数据 Excel * @param list 要导出的数据 * @return */ public static ResponseEntityemployee2Excel(List list) { //1. 创建一个 Excel 文档 HSSFWorkbook workbook = new HSSFWorkbook(); //2. 创建文档摘要 workbook.createInformationProperties(); //3. 获取并配置文档信息 DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation(); //文档类别 docInfo.setCategory("员工信息"); //文档管理员 docInfo.setManager("javaboy"); //设置公司信息 docInfo.setCompany("www.javaboy.org"); //4. 获取文档摘要信息 SummaryInformation summInfo = workbook.getSummaryInformation(); //文档标题 summInfo.setTitle("员工信息表"); //文档作者 summInfo.setAuthor("javaboy"); // 文档备注 summInfo.setComments("本文档由 javaboy 提供"); //5. 创建样式 //创建标题行的样式 HSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); HSSFSheet sheet = workbook.createSheet("员工信息表"); //设置列的宽度 sheet.setColumnWidth(0, 5 * 256); sheet.setColumnWidth(1, 12 * 256); sheet.setColumnWidth(2, 10 * 256); sheet.setColumnWidth(3, 5 * 256); sheet.setColumnWidth(4, 12 * 256); sheet.setColumnWidth(5, 20 * 256); sheet.setColumnWidth(6, 10 * 256); sheet.setColumnWidth(7, 10 * 256); sheet.setColumnWidth(8, 16 * 256); sheet.setColumnWidth(9, 12 * 256); sheet.setColumnWidth(10, 15 * 256); sheet.setColumnWidth(11, 20 * 256); sheet.setColumnWidth(12, 16 * 256); sheet.setColumnWidth(13, 14 * 256); sheet.setColumnWidth(14, 14 * 256); sheet.setColumnWidth(15, 12 * 256); sheet.setColumnWidth(16, 8 * 256); sheet.setColumnWidth(17, 20 * 256); sheet.setColumnWidth(18, 20 * 256); sheet.setColumnWidth(19, 15 * 256); sheet.setColumnWidth(20, 8 * 256); sheet.setColumnWidth(21, 25 * 256); sheet.setColumnWidth(22, 14 * 256); sheet.setColumnWidth(23, 15 * 256); sheet.setColumnWidth(24, 15 * 256); //6. 创建标题行 HSSFRow r0 = sheet.createRow(0); HSSFCell c0 = r0.createCell(0); c0.setCellValue("编号"); c0.setCellStyle(headerStyle); HSSFCell c1 = r0.createCell(1); c1.setCellStyle(headerStyle); c1.setCellValue("姓名"); HSSFCell c2 = r0.createCell(2); c2.setCellStyle(headerStyle); c2.setCellValue("工号"); HSSFCell c3 = r0.createCell(3); c3.setCellStyle(headerStyle); c3.setCellValue("性别"); HSSFCell c4 = r0.createCell(4); c4.setCellStyle(headerStyle); c4.setCellValue("出生日期"); HSSFCell c5 = r0.createCell(5); c5.setCellStyle(headerStyle); c5.setCellValue("身份证号码"); HSSFCell c6 = r0.createCell(6); c6.setCellStyle(headerStyle); c6.setCellValue("婚姻状况"); HSSFCell c7 = r0.createCell(7); c7.setCellStyle(headerStyle); c7.setCellValue("民族"); HSSFCell c8 = r0.createCell(8); c8.setCellStyle(headerStyle); c8.setCellValue("籍贯"); HSSFCell c9 = r0.createCell(9); c9.setCellStyle(headerStyle); c9.setCellValue("政治面貌"); HSSFCell c10 = r0.createCell(10); c10.setCellStyle(headerStyle); c10.setCellValue("电话号码"); HSSFCell c11 = r0.createCell(11); c11.setCellStyle(headerStyle); c11.setCellValue("联系地址"); HSSFCell c12 = r0.createCell(12); c12.setCellStyle(headerStyle); c12.setCellValue("所属部门"); HSSFCell c13 = r0.createCell(13); c13.setCellStyle(headerStyle); c13.setCellValue("职称"); HSSFCell c14 = r0.createCell(14); c14.setCellStyle(headerStyle); c14.setCellValue("职位"); HSSFCell c15 = r0.createCell(15); c15.setCellStyle(headerStyle); c15.setCellValue("聘用形式"); HSSFCell c16 = r0.createCell(16); c16.setCellStyle(headerStyle); c16.setCellValue("最高学历"); HSSFCell c17 = r0.createCell(17); c17.setCellStyle(headerStyle); c17.setCellValue("专业"); HSSFCell c18 = r0.createCell(18); c18.setCellStyle(headerStyle); c18.setCellValue("毕业院校"); HSSFCell c19 = r0.createCell(19); c19.setCellStyle(headerStyle); c19.setCellValue("入职日期"); HSSFCell c20 = r0.createCell(20); c20.setCellStyle(headerStyle); c20.setCellValue("在职状态"); HSSFCell c21 = r0.createCell(21); c21.setCellStyle(headerStyle); c21.setCellValue("邮箱"); HSSFCell c22 = r0.createCell(22); c22.setCellStyle(headerStyle); c22.setCellValue("合同期限(年)"); HSSFCell c23 = r0.createCell(23); c23.setCellStyle(headerStyle); c23.setCellValue("合同起始日期"); HSSFCell c24 = r0.createCell(24); c24.setCellStyle(headerStyle); c24.setCellValue("合同终止日期"); //7.循环你的集合进行存储数据 for (int i = 0; i < list.size(); i++) { Employee emp = list.get(i); HSSFRow row = sheet.createRow(i + 1); row.createCell(0).setCellValue(emp.getId()); row.createCell(1).setCellValue(emp.getName()); row.createCell(2).setCellValue(emp.getWorkID()); row.createCell(3).setCellValue(emp.getGender()); // 时间类型参数设置 HSSFCell cell4 = row.createCell(4); cell4.setCellStyle(dateCellStyle); cell4.setCellValue(emp.getBirthday()); row.createCell(5).setCellValue(emp.getIdCard()); row.createCell(6).setCellValue(emp.getWedlock()); row.createCell(7).setCellValue(emp.getNation().getName()); row.createCell(8).setCellValue(emp.getNativePlace()); row.createCell(9).setCellValue(emp.getPoliticsstatus().getName()); row.createCell(10).setCellValue(emp.getPhone()); row.createCell(11).setCellValue(emp.getAddress()); row.createCell(12).setCellValue(emp.getDepartment().getName()); row.createCell(13).setCellValue(emp.getJobLevel().getName()); row.createCell(14).setCellValue(emp.getPosition().getName()); row.createCell(15).setCellValue(emp.getEngageForm()); row.createCell(16).setCellValue(emp.getTiptopDegree()); row.createCell(17).setCellValue(emp.getSpecialty()); row.createCell(18).setCellValue(emp.getSchool()); // 时间类型参数设置 HSSFCell cell19 = row.createCell(19); cell19.setCellStyle(dateCellStyle); cell19.setCellValue(emp.getBeginDate()); row.createCell(20).setCellValue(emp.getWorkState()); row.createCell(21).setCellValue(emp.getEmail()); row.createCell(22).setCellValue(emp.getContractTerm()); // 时间类型参数设置 HSSFCell cell23 = row.createCell(23); cell23.setCellStyle(dateCellStyle); cell23.setCellValue(emp.getBeginContract()); // 时间类型参数设置 HSSFCell cell24 = row.createCell(24); cell24.setCellStyle(dateCellStyle); cell24.setCellValue(emp.getEndContract()); // 时间类型参数设置 HSSFCell cell25 = row.createCell(25); cell25.setCellStyle(dateCellStyle); cell25.setCellValue(emp.getConversionTime()); } ByteArrayOutputStream baos = new ByteArrayOutputStream(); HttpHeaders headers = new HttpHeaders(); try { headers.setContentDispositionFormData("attachment", new String("员工表.xls".getBytes("UTF-8"), "ISO-8859-1")); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); workbook.write(baos); } catch (IOException e) { e.printStackTrace(); } return new ResponseEntity (baos.toByteArray(), headers, HttpStatus.CREATED); }}
导出数据
exportData() { // 去请求后端接口进行导出数据 window.open("/employee/basic/export", "_parent");}
文中所用技术或许与你所用的技术不一样,但是稍稍修改也是也可实现Excel导出功能的,这个其实没有什么技术点,代码又不需要死记硬背,要用的时候拿过来用即可,如果各位看官有什么问题,评论区留言或者私信我,我都会回复你的。
转载地址:http://psqwi.baihongyu.com/