Java 使用Apache POI读取和写入Excel表格
1,引入所用的包
org.apache.poi poi-ooxml 3.5-FINAL
2,创建列名List,此处将显示到单元格每列名称,根据自己的业务需求更改列名
ListcolumnList = new ArrayList(); columnList.add("申请人账号"); columnList.add("申请人"); columnList.add("提现金额"); columnList.add("开户行"); columnList.add("持卡人"); columnList.add("卡号"); columnList.add("银行名称"); columnList.add("申请时间");
3,创建将要导出的参数(实体类),此处必须和创建的列名List一一对应,否则会错行显示
package com.sanmi.active.fission.management.balance.dto; import lombok.Data; import java.math.BigDecimal; import java.sql.Timestamp; /** * @author:Ziggo Xu
*提现管理导出参数
* =============================== * Date:2018/12/12 * Time:16:55 * ================================ */ @Data public class UserBalanceCashExcelDTO { /** * 申请人账户 */ private String account; /** * 申请人 */ private String nickName; /** * 提现金额 */ private BigDecimal bcCashMoney;/** * 开户行名称 */ private String ubiOpenBank; /** * 持卡人姓名 */ private String ubiRealName; /** * 银行卡卡号 */ private String ubiCardNo; /** * 银行卡名称 */ private String ubiBankName; /** * 提现时间 */ private Timestamp bcCreateTime; }
4,创建导出Excel的工具类
package com.sanmi.active.fission.base.util; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.lang.reflect.Field; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.*; public class Tool { /** * 导出excel操作 * @param response * @param columnList 列名 * @param list 内容 * @param title * @param titlePostion 标题位置 * @throws IllegalAccessException * @throws IOException */ public static void export(HttpServletResponse response, ListcolumnList, List<?> list, String title, Integer titlePostion) throws IllegalAccessException, IOException { response.reset(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss"); String dateStr = sdf.format(new Date()); Map map=new HashMap (); // 指定下载的文件名 response.setHeader("Content-Disposition", "attachment;filename=" +dateStr+".xlsx"); // response.setContentType("application/vnd.ms-excel;charset=UTF-8"); XSSFWorkbook workBook = new XSSFWorkbook(); // 在workbook中添加一个sheet,对应Excel文件中的sheet XSSFSheet sheet = workBook.createSheet(); //列号 int colNum = 0; //行号 int rowNum = 0; XSSFRow rowtitle = sheet.createRow(rowNum++); rowtitle.createCell(titlePostion).setCellValue(title); XSSFRow rowheader = sheet.createRow(rowNum++); for(String string:columnList){ rowheader.createCell(colNum++).setCellValue(string); } Iterator iterator = list.iterator(); while (iterator.hasNext()){ Object object = iterator.next(); XSSFRow row = sheet.createRow(rowNum++); colNum=0; for (Field field : object.getClass().getDeclaredFields()){ field.setAccessible(true); if (field.getType().isInstance(Timestamp.class)){ row.createCell(colNum++).setCellValue(field.get(object).toString() .substring(0,field.get(object).toString().indexOf("."))); }else { if(field.get(object) == null){ row.createCell(colNum++).setCellValue(""); }else { row.createCell(colNum++).setCellValue(field.get(object).toString()); } } } } workBook.write(response.getOutputStream()); } }
5,业务逻辑处理,使用第三部创建的UserBalanceCashExcelDTO 接收集合参数
Listlist = balanceCashApplyService.exportApplyList(param);
6,设置文档的标题
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String title = "提现申请导出" + "(" + df.format(new Date()) + ")";
7,调用工具类,导出文档
Tool.export(response, columnList, list, title, 1);
参考代码如下
/** * 导出提现信息 * * @param param * @throws Exception * @throws IllegalAccessException */ @RequestMapping("/exportApplyList") public void exportApplyList(UserBalanceCashParam param) throws Exception { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Listlist = balanceCashApplyService.exportApplyList(param); String title = "提现申请导出" + "(" + df.format(new Date()) + ")"; List columnList = new ArrayList(); columnList.add("申请人账号"); columnList.add("申请人"); columnList.add("提现金额"); columnList.add("开户行"); columnList.add("持卡人"); columnList.add("卡号"); columnList.add("银行名称"); columnList.add("申请时间"); //导出 Tool.export(response, columnList, list, title, 1); }
导出效果图示
8,补充读取excel逻辑实现,新建存储实体
package com.sanmi.active.fission.management.balance.dto; import lombok.Data; import java.sql.Timestamp; /** * @author:Ziggo Xu
*导出excel 实体类,与 excel列名对应
* =============================== * Date:2019/04/12 * Time:16:55 * ================================ */ @Data public class ExcelEntity{ //患者id private String id; //患者名称 private String name; //患者检查类型标识 CT MR US 病理 private String flag; //患者报告内容 private String content; //分析结果 private String result; }
9,补充读取excel逻辑实现,读取方法
public static void begin(HttpServletResponse response) throws Exception { Workbook wb =null; Sheet sheet = null; Row row = null; List