根据POI生成(导出)Excel表格入门
1.添加导出按钮
例如:<a id="exportXlsBtn" icon="icon-print" href="#" class="easyui-linkbutton" plain="true">导出Excel按钮</a>
2.添加导出事件 例如:
$("#exportXlsBtn").click(function(){
$("#searchForm").attr("action","../../report_exportXls.action");
$("#searchForm").submit();
});
3.编写ExportAction,添加exportXls方法
* POI 生成 Excel 步骤写 Excel 过程一样，新建 Excel 文档 -- 新建 Sheet -- 新建 Row --新建 Cell 单元格 -- 写单元格数据
* POI 生成 HSSF （xls）和 XSSF （xlsx）
package cn.itcast.bos.web.action.report; import java.io.IOException; import java.util.List; import javax.servlet.ServletOutputStream; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.struts2.ServletActionContext; import org.apache.struts2.convention.annotation.Action; import org.apache.struts2.convention.annotation.Namespace; import org.apache.struts2.convention.annotation.ParentPackage; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Controller; import cn.itcast.bos.domain.take_delivery.WayBill; import cn.itcast.bos.service.take_delivery.WayBillService; import cn.itcast.bos.utils.FileUtils; import cn.itcast.bos.web.action.common.BaseAction; @Controller @Namespace("/") @ParentPackage("json-default") @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("运单数据"); // 设置表头 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);//目前只有第一行,+1就是第二行 dataRow.createCell(0).setCellValue(wayBill.getNum()); 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");//设置文件类型MIME类型 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; } }
4.编写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())) { // 运单号查询,(name-value) QueryBuilder tempQuery = new TermQueryBuilder("wayBillNum", wayBill.getWayBillNum()); query.must(tempQuery); } 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 wildcardQuery = new WildcardQueryBuilder( "recAddress", "*" + wayBill.getRecAddress() + "*"); query.must(wildcardQuery); } if (StringUtils.isNoneBlank(wayBill.getSendProNum())) { // 速运类型 等值查询 QueryBuilder termQuery = new TermQueryBuilder("sendProNum", wayBill.getSendProNum()); query.must(termQuery); } 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等价于QueryBuilder加Pageable,他返回Iterable.queryBuild返回Page对象 SearchQuery searchQuery = new NativeSearchQuery(query); // ElasticSearch 允许搜索分页查询，最大数据条数10000 Pageable pageable = new PageRequest(0, 10000); searchQuery.setPageable(pageable); // 分页效果 // 有条件查询 、查询索引库 return wayBillIndexRepository.search(searchQuery).getContent(); }
