Excel格式报表生成—POI

系统要求按照条件查询并导出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即可

很简单的实现，在以后的需求中待完善

转载于:https://my.oschina.net/wuzhentao/blog/1501675