1,引入所用的包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.5-FINAL</version>
</dependency>
2,创建列名List,此处将显示到单元格每列名称,根据自己的业务需求更改列名
List<String> columnList = 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 <br/> * <p>提现管理导出参数</p> * =============================== * 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, List<String> columnList, List<?> list, String title, Integer titlePostion) throws IllegalAccessException, IOException { response.reset(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss"); String dateStr = sdf.format(new Date()); Map<String,Object> map=new HashMap<String,Object>(); // 指定下载的文件名 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 接收集合参数
List<UserBalanceCashExcelDTO> list = 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"); List<UserBalanceCashExcelDTO> list = balanceCashApplyService.exportApplyList(param); String title = "提现申请导出" + "(" + df.format(new Date()) + ")"; List<String> 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 <br/> * <p>导出excel 实体类，与 excel列名对应</p> * =============================== * 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<Map<String,String>> list = null; List<ExcelEntity> entitys = new ArrayList<ExcelEntity>(); String cellData = null; //这里设置要读取的原始数据 String filePath = "D:\\潍坊二院数据.xls"; //此处的数据是没有意义的，只是为了读取原始数据的列数（5列），设置失误会导致缺失数据 String columns[] = {"id","name","flag","flagName","content"}; wb = readExcel(filePath); if(wb != null){ //用来存放表中数据 list = new ArrayList<Map<String,String>>(); //获取第三个sheet sheet = wb.getSheetAt(2); //获取最大行数 int rownum = sheet.getPhysicalNumberOfRows(); //获取第一行 row = sheet.getRow(0); //获取最大列数 // int colnum = row.getPhysicalNumberOfCells(); int colnum = 5;//暂定数据，最大五行，其他空白行无意义 ExcelEntity entity = new ExcelEntity(); for (int i = 1; i<rownum; i++) { Map<String,String> map = new LinkedHashMap<String,String>(); row = sheet.getRow(i); if(row !=null){ for (int j=0;j<colnum;j++){ cellData = (String) getCellFormatValue(row.getCell(j)); map.put(columns[j], cellData); } }else{ break; } list.add(map); //entitys.add(entity); } } //遍历解析出来的list for (Map<String,String> map : list) { String flag = null; String content; JSONArray jsonArry = null; String id = null; ExcelEntity entity = new ExcelEntity(); for (Entry<String,String> entry : map.entrySet()) { // System.out.print(entry.getKey()+":"+entry.getValue()+","); //检查类型 if(entry.getKey().equals("flag")) { flag = entry.getValue(); entity.setFlag(entry.getValue()); } //检查的内容 if(entry.getKey().equals("content")) { content = entry.getValue(); content = "[{'content':'"+content+"','pat_in_hos_id':'"+id+"','study_bodypart':'test','time':'2019-03-01 12:05:00'}]"; if(!EmptyUtil.isEmpty(content)) { jsonArry =JSONArray.fromObject(content); } entity.setContent(content); } //检查患者的Id if(entry.getKey().equals("id")) { id = entry.getValue(); entity.setId(id); } //检查患者的名称 if(entry.getKey().equals("name")) { entity.setName(entry.getValue()); } } entity.setResult("测试结果"); entitys.add(entity); } //读取方法完成,已封装为list实体!!!!!! System.out.println(entitys.size()); }