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());
}