Java之SSM 实现导出数据（Excel表格,表格中显示图片）
前端实现
- 页面上添加导出按钮
<!-- 表格工具栏 --> <div id="toolbar" class="toolbar-btn-action"> <button id="btn_batch_delete" type="button" class="btn btn-danger btn-sm"> <span class="mdi mdi-close-circle-outline" aria-hidden="true"></span>批量删除 </button> <button id="btn_export" type="button" class="btn btn-primary btn-sm"> <span class="mdi mdi-export" aria-hidden="true"></span>导出 </button> </div> - JS中添加导出按钮点击事件
// 导出 $("#btn_export").click(function() { var params = $("#formSearch").serialize(); window.location.href="../../teacherFile/teacher/exportTeacher.do?"+params; })
后端实现
- Controller层
- 接收查询条件，并根据条件查出所有满足条件的结果
- 需要引用一个导出教师的类（自己写的），来对表格样式进行处理
/** * 导出教师信息 * @param teacher 从前端接收到的查询教师条件 * @return */ @RequestMapping("exportTeacher") public ResponseEntity<Object> exportTeacher(Teacher teacher) { List<Teacher> teachers = teacherService.queryTeachersForExport(teacher); String fileName = "教师信息.xls"; String sheetName = "客户数据"; ByteArrayOutputStream bos = ExprotTeacherUtils.exportTeachers(teachers, sheetName); try { fileName = URLEncoder.encode(fileName, "UTF-8");// 处理文件名乱码 // 创建封装响应头信息的对象 HttpHeaders header = new HttpHeaders(); // 封装响应内容类型(APPLICATION_OCTET_STREAM 响应的内容不限定) header.setContentType(MediaType.APPLICATION_OCTET_STREAM); // 设置下载的文件的名称 header.setContentDispositionFormData("attachment", fileName); return new ResponseEntity<Object>(bos.toByteArray(), header, HttpStatus.CREATED); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } - 导出表格的工具类
package com.tit.utils; import java.awt.image.BufferedImage; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import javax.imageio.ImageIO; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.tit.model.Teacher; public class ExprotTeacherUtils { @SuppressWarnings("deprecation") public static ByteArrayOutputStream exportTeachers(List<Teacher> teachers, String sheetName) { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");// 设置日期格式 // 一组装excel文档 // 1,创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 2,创建样式 HSSFCellStyle baseStyle = ExprotHSSFCellStyle.createBaseStyle(workbook); HSSFCellStyle subTitleStyle = ExprotHSSFCellStyle.createSubTitleStyle(workbook); HSSFCellStyle tableTitleStyle = ExprotHSSFCellStyle.createTableTitleStyle(workbook); HSSFCellStyle titleStyle = ExprotHSSFCellStyle.createTitleStyle(workbook); // 3在工作簿创建sheet HSSFSheet sheet = workbook.createSheet(sheetName); // 4,设置sheet sheet.setDefaultColumnWidth(25); // 5,合并 CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 19); CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, 19); sheet.addMergedRegion(region1); sheet.addMergedRegion(region2); // 6,创建第一行 int index = 0; HSSFRow row1 = sheet.createRow(index); // 6.1在第一行里面创建一个单元格 HSSFCell row1_cell1 = row1.createCell(0); // 6.2设置标题样式 row1_cell1.setCellStyle(titleStyle); // 6.3设置单元格内容 row1_cell1.setCellValue("教师信息列表"); // 7,第二行 index++; HSSFRow row2 = sheet.createRow(index); // 7.1在第一行里面创建一个单元格 HSSFCell row2_cell1 = row2.createCell(0); // 7.2设置标题样式 row2_cell1.setCellStyle(subTitleStyle); // 7.3设置单元格内容 row2_cell1.setCellValue("总条数:" + teachers.size() + " 导出时间:" + new Date().toLocaleString()); // 8第三行 String[] titles = { "教师工号", "所属院系编号", "教师姓名", "性别", "民族", "身份证号", "出生日期", "家庭地址", "手机号码", "学历", "学位", "政治面貌", "毕业院校", "毕业时间", "所学专业", "职称", "一寸照", "主要成就", "备注信息", "入职时间" }; index++; HSSFRow row3 = sheet.createRow(index); for (int i = 0; i < titles.length; i++) { HSSFCell row3_cell = row3.createCell(i); row3_cell.setCellStyle(tableTitleStyle); row3_cell.setCellValue(titles[i]); } HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 9第四行 for (int i = 0; i < teachers.size(); i++) { index++; Teacher customer = teachers.get(i); HSSFRow row = sheet.createRow(index); //设置行高 row.setHeight((short)3000); // 9.1创建列 教师工号 HSSFCell row11 = row.createCell(0); row11.setCellStyle(baseStyle); row11.setCellValue(customer.getTeacherId()); // 9.2创建列 所属院系编号 HSSFCell row22 = row.createCell(1); row22.setCellStyle(baseStyle); row22.setCellValue(customer.getDeptId()); // 9.3创建列 教师姓名 HSSFCell row33 = row.createCell(2); row33.setCellStyle(baseStyle); row33.setCellValue(customer.getName()); // 9.4创建列 性别 HSSFCell row44 = row.createCell(3); row44.setCellStyle(baseStyle); row44.setCellValue(customer.getSex() == 1 ? "男" : "女"); // 9.5创建列 民族 HSSFCell row55 = row.createCell(4); row55.setCellStyle(baseStyle); row55.setCellValue(customer.getNational()); // 9.6创建列身份证号 HSSFCell row66 = row.createCell(5); row66.setCellStyle(baseStyle); row66.setCellValue(customer.getIdCard()); // 9.7创建列 出生日期 HSSFCell row77 = row.createCell(6); row77.setCellStyle(baseStyle); row77.setCellValue(df.format(customer.getBirthday())); // 9.8创建列 家庭地址 HSSFCell row88 = row.createCell(7); row88.setCellStyle(baseStyle); row88.setCellValue(customer.getAddress()); // 9.9创建列 手机号码 HSSFCell row99 = row.createCell(8); row99.setCellStyle(baseStyle); row99.setCellValue(customer.getPhone()); // 9.10创建列 学历 HSSFCell row10 = row.createCell(9); row10.setCellStyle(baseStyle); row10.setCellValue(customer.getEducation()); // 9.11创建列 学位 HSSFCell row111 = row.createCell(10); row111.setCellStyle(baseStyle); row111.setCellValue(customer.getDegree()); // 9.12创建列 政治面貌 HSSFCell row112 = row.createCell(11); row112.setCellStyle(baseStyle); row112.setCellValue(customer.getPartisan()); // 9.13创建列 毕业院校 HSSFCell row113 = row.createCell(12); row113.setCellStyle(baseStyle); row113.setCellValue(customer.getGraduationSchool()); // 9.14创建列 毕业时间 HSSFCell row114 = row.createCell(13); row114.setCellStyle(baseStyle); row114.setCellValue(df.format(customer.getGraduationDate())); // 9.15创建列 所学专业 HSSFCell row115 = row.createCell(14); row115.setCellStyle(baseStyle); row115.setCellValue(customer.getMajor()); // 9.16创建列 职称 HSSFCell row116 = row.createCell(15); row116.setCellStyle(baseStyle); row116.setCellValue(customer.getJobTitle()); // 17 一寸照 // 获取照片 File file = new File(Constast.TEACHER_PATH + customer.getTeacherPhoto()); // 获取文件后缀名 String suffix = customer.getTeacherPhoto().substring(customer.getTeacherPhoto().lastIndexOf(".") + 1); try { BufferedImage bufferedImage = ImageIO.read(file); // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); // 这里要注意formatName要缓存后缀名 ImageIO.write(bufferedImage, suffix, byteArrayOut); byte[] data = byteArrayOut.toByteArray(); // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点） // anchor主要用于设置图片的属性 HSSFClientAnchor anchor = new HSSFClientAnchor(0, // x缩放 0, // y缩放 1023, // 最大1023 255, // 最大255 (short) 16, // 宽度占几格 0开始 i + 3, // 在第几行 (short) 16, // 宽度占几格 0开始 i + 3 // 第几列 ); patriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG)); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 9.18创建列 主要成就 HSSFCell row118 = row.createCell(17); row118.setCellStyle(baseStyle); row118.setCellValue(customer.getAchievements()); // 9.19创建列 备注信息 HSSFCell row119 = row.createCell(18); row119.setCellStyle(baseStyle); row119.setCellValue(customer.getTeacherRemark()); // 9.20创建列 入职时间 HSSFCell row120 = row.createCell(19); row120.setCellStyle(baseStyle); row120.setCellValue(df.format(customer.getWorktime())); } // 到此excel组装完成 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); // 把workbook里面的数据写到outputStream try { workbook.write(outputStream); } catch (IOException e) { e.printStackTrace(); } return outputStream; } } - 表格样式设置
package com.tit.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.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; public class ExprotHSSFCellStyle { /** * 创建基础样式 * 水平和垂直居中 */ public static HSSFCellStyle createBaseStyle(HSSFWorkbook workbook) { HSSFCellStyle style = workbook.createCellStyle(); //设置水平居中 style.setAlignment(HorizontalAlignment.CENTER); //设置垂直居中 style.setVerticalAlignment(VerticalAlignment.CENTER); return style; } /** * 创建数据表格的头的样式 */ public static HSSFCellStyle createTableTitleStyle(HSSFWorkbook workbook) { HSSFCellStyle style = createBaseStyle(workbook); //设置字体 HSSFFont font=workbook.createFont(); font.setBold(true);//是否加粗 font.setItalic(true);//是否斜体 font.setFontHeightInPoints((short)20); //设置字体大小 font.setColor(HSSFColor.HSSFColorPredefined.DARK_YELLOW.getIndex());//设置颜色 font.setFontName("黑体");//设置字体 style.setFont(font); return style; } /** * 创建小标题样式 */ public static HSSFCellStyle createSubTitleStyle(HSSFWorkbook workbook) { HSSFCellStyle style = createBaseStyle(workbook); //设置字体 HSSFFont font=workbook.createFont(); font.setBold(true);//是否加粗 font.setFontHeightInPoints((short)25); //设置字体大小 font.setColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());//设置颜色 font.setFontName("黑体");//设置字体 style.setFont(font); return style; } /** * 创建标题样式 */ public static HSSFCellStyle createTitleStyle(HSSFWorkbook workbook) { HSSFCellStyle style = createBaseStyle(workbook); //设置字体 HSSFFont font=workbook.createFont(); font.setBold(true);//是否加粗 font.setFontHeightInPoints((short)35); //设置字体大小 font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());//设置颜色 font.setFontName("华文行楷");//设置字体 style.setFont(font); return style; } }
1724
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
