知识点: 前端表格数据, 调用后台接口, 导出 excel 文件数据, 使用到 Apache POI 接口
POI 提供 API 给 Java 程序对 Microsoft Office 格式档案读和写的功能.
(1) 在 pom.xml 中引入 POI 和文件读写相关的包
- <dependency>
- <groupId>commons-io</groupId>
- <artifactId>commons-io</artifactId>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- </dependency>
(2)Controller 层, 表格导出接口
- /**
- * 导出数据
- */
- @RequestMapping("/Export")
- public void export(HttpServletRequest request,HttpServletResponse response){
- Export<Equipment> ee = new Export<Equipment>();//equipment 实体类
- List<Equipment> equiplist = equipmentService.getEquipmentList(); //equiplist 是查询的数据集合
- String [] headers = {"e_id","e_code","e_location","e_ip","e_attend_code","cruser","crtime","upuser","uptime"};// 表格头数据设置
- String fileName = "设备列表数据"; //excel 表格名称
- response.setContentType("application/x-msdownload");
- ServletOutputStream outputStream = null; //outputStream 文件输出流
- try
- {
- fileName = fileName + ".xls";// 定义文件格式
- if (request.getHeader("USER-AGENT").toLowerCase().contains("firefox"))// 火狐
- {
- response.setHeader("Content-Disposition",
- "attachment;fileName=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
- }
- else
- {
- response.setHeader("Content-Disposition",
- "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
- }
- outputStream = response.getOutputStream();
- try {
- ee.Export(headers, fileName, equiplist, outputStream);// 表格导出工具类
- } catch (InvocationTargetException e) {
- e.printStackTrace();
- }
- }
- catch (UnsupportedEncodingException e)
- {
- throw new BusinessException("导出失败!");
- }
- catch (IOException e)
- {
- throw new BusinessException("导出失败!");
- }
- catch (NoSuchMethodException e)
- {
- LOGGER.error(e.getMessage());
- }
- catch (IllegalAccessException e)
- {
- LOGGER.error(e.getMessage());
- } finally
- {
- IOUtils.closeQuietly(outputStream);//
- }
- }
(3)Export.java 表格导出工具类
- package com.agesun.attendance.common;
- import java.io.OutputStream;
- import java.lang.reflect.Field;
- import java.lang.reflect.InvocationTargetException;
- import java.lang.reflect.Method;
- import java.util.Iterator;
- import java.util.List;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFFont;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- /**
- * < 一句话功能简述 > < 功能详细描述 >
- *
- * @author nishuai
- * @version [版本号, 2018 年 06 月 14 日]
- * @see [相关类 / 方法]
- * @since [产品 / 模块版本]
- */
- public class Export<T>
- {
- public void Export(String[] headers, String fileName, List<T> dataset, OutputStream outputStream)
- throws NoSuchMethodException, InvocationTargetException, IllegalAccessException
- {
- // 声明一个工作薄
- HSSFWorkbook wb = new HSSFWorkbook();
- // 声明一个单子并命名
- HSSFSheet sheet = wb.createSheet(fileName);
- /****************** 头部样式 ***********************/
- HSSFFont font = wb.createFont();
- font.setFontName("微软雅黑");
- font.setFontHeightInPoints((short)14);// 设置字体大小
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- // 给单子名称一个长度
- sheet.setDefaultColumnWidth((short)16);
- // 生成一个样式
- HSSFCellStyle style = wb.createCellStyle();
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
- style.setFont(font);
- /********************* 数据单元格样式 **************************/
- HSSFFont rowFont = wb.createFont();
- rowFont.setFontName("宋体");
- rowFont.setFontHeightInPoints((short)14);// 设置字体大小
- // 给单子名称一个长度
- sheet.setDefaultColumnWidth((short)16);
- // 生成一个样式
- HSSFCellStyle rowStyle = wb.createCellStyle();
- rowStyle.setFont(rowFont);
- // 样式字体居中
- rowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- // 创建第一行 (也可以称为表头)
- HSSFRow row = sheet.createRow(0);
- row.setHeight((short)500);
- // 给表头第一行一次创建单元格
- for (short i = 0; i <headers.length; i++)
- {
- HSSFCell cell = row.createCell(i);
- cell.setCellValue(headers[i]);
- cell.setCellStyle(style);
- }
- // 遍历集合数据, 产生数据行
- Iterator<T> it = dataset.iterator();
- int index = 0;
- while (it.hasNext())
- {
- index++;
- row = sheet.createRow(index);
- row.setHeight((short)500);
- T t = it.next();
- // 利用反射, 根据 javabean 属性的先后顺序, 动态调用 getXxx() 方法得到属性值
- Field[] fields = t.getClass().getDeclaredFields();
- for (short i = 0; i < headers.length; i++)
- {
- Field field = fields[i];
- String fieldName = field.getName();
- String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
- Class tCls = t.getClass();
- Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
- Object value = getMethod.invoke(t, new Object[] {});
- // 判断值的类型后进行强制类型转换
- String textValue = null;
- // 其它数据类型都当作字符串简单处理
- if (value != null && !"".equals(value))
- {
- textValue = value.toString();
- }
- if (textValue != null)
- {
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(rowStyle);
- cell.setCellValue(textValue);
- }
- }
- }
来源: http://www.bubuko.com/infodetail-2644502.html