系统要求按照条件查询并导出Exel报表 前端页面如下:
Action代码
import cn.bos.domain.take_delivery.WayBill;
import cn.bos.service.take_delivery.WayBillService;
import cn.bos.utils.FileUtils;
import cn.bos.web.action.common.BaseAction;
@Controller
@ParentPackage("json-default")
@Namespace("/")
@Scope("prototype")
public class ReportAction extends BaseAction<WayBill> {
@Autowired
private WayBillService wayBillService;
@Action(value="report_exportXls")
public String exportXls() throws IOException {
List<WayBill> wayBills = wayBillService.findWayBills(model);
//生成Excel文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet("运单数据");
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short)15);
//表头
HSSFRow headRow = sheet.createRow(0);
headRow.createCell(0).setCellValue("运单号");
headRow.createCell(1).setCellValue("寄件人");
headRow.createCell(2).setCellValue("寄件人电话");
headRow.createCell(3).setCellValue("寄件人地址");
headRow.createCell(4).setCellValue("收件人");
headRow.createCell(5).setCellValue("收件人电话");
headRow.createCell(6).setCellValue("收件人地址");
//表格数据
for(WayBill wayBill : wayBills) {
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum()+1);
dataRow.createCell(0).setCellValue(wayBill.getWayBillNum());
dataRow.createCell(1).setCellValue(wayBill.getSendName());
dataRow.createCell(2).setCellValue(wayBill.getSendMobile());
dataRow.createCell(3).setCellValue(wayBill.getSendAddress());
dataRow.createCell(4).setCellValue(wayBill.getRecName());
dataRow.createCell(5).setCellValue(wayBill.getRecMobile());
dataRow.createCell(6).setCellValue(wayBill.getRecAddress());
}
//下载导出
//设置头消息
ServletActionContext.getResponse().setContentType(" application/vnd.ms-excel");
String filename = "运单数据.xls";
String agent = ServletActionContext.getRequest().getHeader("user-agent");//获得游览器
filename = FileUtils.encodeDownloadFilename(filename, agent); //使用工具类解决文件乱码的问题
ServletActionContext.getResponse().setHeader("Content-Disposition", "attachment;filename="+filename);
ServletOutputStream outputStream = ServletActionContext.getResponse().getOutputStream();
hssfWorkbook.write(outputStream);
//关闭
hssfWorkbook.close();
return NONE;
}
}
service代码
@Override
public List<WayBill> findWayBills(WayBill wayBill) {
//判断WayBill 中条件是否存在
if(StringUtils.isBlank(wayBill.getWayBillNum() )
&& StringUtils.isBlank(wayBill.getSendAddress())
&& StringUtils.isBlank(wayBill.getRecAddress())
&& StringUtils.isBlank(wayBill.getSendProNum())
&& (wayBill.getSignStatus() == null || wayBill.getSignStatus() == 0)){
//条件不存在
return wayBillRepository.findAll();
}else {
//条件存在
//查询条件
// must 条件必须成立 and
// must not 条件必须不成立 not
//should 条件可以成立 or
BoolQueryBuilder query = new BoolQueryBuilder();//布尔查询 多条件组合查询
//向组合查询对象添加条件
if(StringUtils.isNoneBlank(wayBill.getWayBillNum())) {
//运单号查询 等值查询
QueryBuilder termQuery = new TermQueryBuilder("wayBillNum",
wayBill.getWayBillNum());
query.must(termQuery);
}
if(StringUtils.isNoneBlank(wayBill.getSendAddress())) {
//发货地 模糊查询
// 情况一: 输入"北" 是查询词条一部分, 使用模糊匹配词条查询
QueryBuilder wildcardQuery = new WildcardQueryBuilder("sendAddress",
"*"+wayBill.getSendAddress()+"*");
//情况二:输入“北京市海淀区”是多个词条组成 进行分词后 每个词条分词查询
QueryBuilder queryStringQueryBuilder = new QueryStringQueryBuilder(wayBill.getSendAddress())
.field("sendAddress").defaultOperator(Operator.AND);
//两种情况区or关系
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
boolQueryBuilder.should(wildcardQuery);
boolQueryBuilder.should(queryStringQueryBuilder);
query.must(boolQueryBuilder);
}
if(StringUtils.isNoneBlank(wayBill.getRecAddress())) {
//收货地 模糊查询
//情况一 查询词条的一部分 使用模糊匹配查询
QueryBuilder wildcardQueryBuilder = new WildcardQueryBuilder("recAddress",
wayBill.getRecAddress());
//情况二 查询"北京市海淀区"包含多个词条 先分词再进行分词后每个词条模糊查询
QueryBuilder queryStringQueryBuilder = new QueryStringQueryBuilder(wayBill.getRecAddress())
.field("recAddress").analyzer("ik").defaultOperator(Operator.AND);
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
boolQueryBuilder.should(wildcardQueryBuilder);
boolQueryBuilder.should(queryStringQueryBuilder);
query.must(boolQueryBuilder);
}
if(StringUtils.isNoneBlank(wayBill.getSendProNum())) {
//快递产品类型 等值查询
QueryBuilder termQuery = new TermQueryBuilder("sendProNum",
wayBill.getSendProNum());
query.must(termQuery);
}
if(wayBill.getSignStatus() != null && wayBill.getSignStatus() != 0) {
//运单状态等值查询
QueryBuilder termQuery = new TermQueryBuilder("signStatus",
wayBill.getSignStatus());
query.must(termQuery);
}
SearchQuery searchQuery = new NativeSearchQuery(query);
Pageable pageable = new PageRequest(0, Integer.MAX_VALUE);
searchQuery.setPageable(pageable); //分页效果
//有条件查询 查询索引库
return wayBillIndextRepository.search(searchQuery).getContent();
}
}
Pageable pageable = new PageRequest(0, Integer.MAX_VALUE);会导致报错,原因:elasticsearch的最大查询为10000,传递 Integer.MAX_VALUE会超出其范围。把 Integer.MAX_VALUE改为10000即可
很简单的实现,在以后的需求中待完善