通过Controller导出数据库表到Excel

2019独角兽企业重金招聘Python工程师标准>>>

初步步骤: 源代码来自> @abcijkxyz以此感谢长期以来的帮助~~~ abcijkxyz

此代码目前为止不太完善，原因如下: 1.有些数据是不需要显示的，但是我所做的是显示所有 2.时间格式目前为止有问题，还需要改进 3.此为单表内容显示，部分内容需要连接多表查询再显示 4.目前就是这些，还有其他的没想到，欢迎补充~~

@RequestMapping(value = "exportAll",method = {RequestMethod.GET})
    public void exportAll(Map query, HttpServletResponse response) {
//        Pagination page = getPagination(b);
//        Pagination page = new Pagination();
        String[] title = new String[]{"id","type","createtime","buyer", "proname",
                "status","price","creator","appid","seller","orderno","total_count",
                "total_money","proid","protype","orderimage","agentname","parentid",
                "supplier","deliver","goodstype","refereesid","ticketgiftnum","pickorder"};
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("订单表");
        sheet.setDefaultColumnWidth(15);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell = null;
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        try {
//            orderService.queryPageList(page);
//            orderChargeService.reportTotal(page);
//            List<CostObject> list = page.getDatas();
            List<Order> list = orderService.queryListByMap(query);
            if (list != null && list.size() > 0) {
                 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                for (int i = 0, len = list.size(); i < len; i++) {
                    Order o = list.get(i);
                    row = sheet.createRow(i + 1);
                    //setCellValue不能为空，进行判断
                    row.createCell(0).setCellValue(o.getId());
                    if (o.getType()==null){
                        row.createCell(1).setCellValue(0);
                    }else {
                        row.createCell(1).setCellValue(o.getType());
                    }
                    if (o.getCreatetime()==null){
                        row.createCell(2).setCellValue(0);
                    }else {
                        row.createCell(2).setCellValue(o.getCreatetime().toString());
                    }
//                    row.createCell(2).setCellValue(o.getCreatetime());
                    if (o.getBuyer()==null){
                        row.createCell(3).setCellValue((long)0);
                    }else {
                        row.createCell(3).setCellValue(o.getBuyer());
                    }
                    if (o.getProname()==null){
                        row.createCell(4).setCellValue("(null)");
                    }else {
                        row.createCell(4).setCellValue(o.getProname());
                    }
                    if (o.getStatus()==null){
                        row.createCell(5).setCellValue(0);
                    }else {
                        row.createCell(5).setCellValue(o.getStatus());
                    }
                    if (o.getPrice()==null){
                        row.createCell(6).setCellValue("(null)");
                    }else {
                        row.createCell(6).setCellValue(o.getPrice());
                    }
                    if (o.getCreator()==null){
                        row.createCell(7).setCellValue((long)0);
                    }else {
                        row.createCell(7).setCellValue(o.getCreator());
                    }
                    if (o.getAppid()==null){
                        row.createCell(8).setCellValue("(null)");
                    }else {
                        row.createCell(8).setCellValue(o.getAppid());
                    }
                    if (o.getSeller()==null){
                        row.createCell(9).setCellValue((long)0);
                    }else {
                        row.createCell(9).setCellValue(o.getSeller());
                    }
                    if (o.getOrderno()==null){
                        row.createCell(10).setCellValue("(null)");
                    }else {
                        row.createCell(10).setCellValue(o.getOrderno());
                    }
                    if (o.getTotal_count()==null){
                        row.createCell(11).setCellValue((long)0);
                    }else {
                        row.createCell(11).setCellValue(o.getTotal_count());
                    }
                    if (o.getTotal_money()==null){
                        row.createCell(12).setCellValue("(null)");
                    }else {
                        row.createCell(12).setCellValue(o.getTotal_money());
                    }
                    if (o.getProid()==null){
                        row.createCell(13).setCellValue((long)0);
                    }else {
                        row.createCell(13).setCellValue(o.getProid());
                    }
                    if (o.getProtype()==null){
                        row.createCell(14).setCellValue(0);
                    }else {
                        row.createCell(14).setCellValue(o.getProtype());
                    }
                    if (o.getOrderimage()==null){
                        row.createCell(15).setCellValue("(null)");
                    }else {
                        row.createCell(15).setCellValue(o.getOrderimage());
                    }
                    if (o.getAgentname()==null){
                        row.createCell(16).setCellValue("(null)");
                    }else {
                        row.createCell(16).setCellValue(o.getAgentname());
                    }
                    if (o.getParentid()==null){
                        row.createCell(17).setCellValue((long)0);
                    }else {
                        row.createCell(17).setCellValue(o.getParentid());
                    }
                    if (o.getSupplier()==null){
                        row.createCell(18).setCellValue((long)0);
                    }else {
                        row.createCell(18).setCellValue(o.getSupplier());
                    }
                    if (o.getDeliver()==null){
                        row.createCell(19).setCellValue((long)0);
                    }else {
                        row.createCell(19).setCellValue(o.getDeliver());
                    }
                    if (o.getGoodstype()==null){
                        row.createCell(20).setCellValue(0);
                    }else {
                        row.createCell(20).setCellValue(o.getGoodstype());
                    }
                    if (o.getRefereesid()==null){
                        row.createCell(21).setCellValue((long)0);
                    }else {
                        row.createCell(21).setCellValue(o.getRefereesid());
                    }
                    if (o.getTicketgiftnum()==null){
                        row.createCell(22).setCellValue(0);
                    }else {
                        row.createCell(22).setCellValue(o.getTicketgiftnum());
                    }
                        row.createCell(23).setCellValue("(null)");
                }
            }
            OutputStream out = response.getOutputStream();
            response.setHeader("content-disposition", "attachment; filename=exportAll.xls");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            wb.write(out);
            out.close();
        } catch (Exception e) {
            log.error("导出失败,{}", e);
        }
    }

转载于:https://my.oschina.net/LYQ19941105/blog/748553