POI|Excel的导出

水深无声 2022-02-24 03:10 426阅读 0赞

【Excel导出工具类】

public class ExportExcel {
        /**
         * excel导出工具类
         * @param sheetName 名称
         * @param title 标题
         * @param wb 对象
         * @return HSSFWorkbook
         */
        public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title,String[][] values, HSSFWorkbook wb){
             // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
            if (wb == null){
                wb = new HSSFWorkbook();
            }
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
    
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            HSSFRow row = sheet.createRow(0);
    
            // 第四步，创建单元格
            HSSFCellStyle style = wb.createCellStyle();
    
            //声明列对象
            HSSFCell cell = null;
    
            //创建标题
            for(int i = 0; i < title.length; i++){
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(style);
            }
    
            //创建内容
            for(int i=0;i<values.length;i++){
                row = sheet.createRow(i + 1);
                for(int j=0;j<values[i].length;j++){
                    //将内容按顺序赋给对应的列对象
                    row.createCell(j).setCellValue(values[i][j]);
                }
            }
            return wb;
        }
    }

Controller控制层

@Controller
    @RequestMapping("/cus")
    public class CustomerController {
    
        @Resource
        private CustomerService customerService;
    
        /**
         * 查询客户的所有信息
         * @param customerVo
         * @return Msg
         */
        @RequestMapping("showInfo")
        @ResponseBody
        public Msg showInfo(@RequestParam(value = "pn", defaultValue = "1")Integer pn, CustomerVo customerVo, HttpSession session){
            PageHelper.startPage(pn, 5);
            PageHelper.orderBy("id asc");
            List<Customer> list = customerService.showInfo(customerVo);
            session.setAttribute("CusList",list);
            PageInfo<Customer> pageInfo = new PageInfo<>(list,5);
            return Msg.success().add("pageInfo", pageInfo);
        }
    
        @RequestMapping("export")
        public String export(HttpSession session){
            // 获取数据
            List<Customer> list = (List<Customer>)session.getAttribute("CusList");
            // 标题
            String[] title = {"id","comname","companyperson","comaddress","comphone","camera","present","remark","addtime"};
    
            // excel文件名
            String fileName = "客户表信息"+System.currentTimeMillis()+".xls";
    
            // sheet名
            String sheetName = "客户表信息";
    
            String[][] content = new String[list.size()][title.length];
            for (int i = 0; i < list.size(); i++) {
                Customer customer = list.get(i);
    
                content[i][0] = customer.getId().toString();
                content[i][1] = customer.getComname();
                content[i][2] = customer.getCompanyperson();
                content[i][3] = customer.getComaddress();
                content[i][4] = customer.getComphone();
                content[i][5] = customer.getCamera();
                content[i][6] = customer.getPresent();
                content[i][7] = customer.getRemark();
                content[i][8] = customer.getAddtime().toString();
            }
    
            // 创建HSSFWorkbook
            HSSFWorkbook workbook = ExportExcel.getHSSFWorkbook(sheetName, title, content, null);
    
            //响应到客户端
            try {
                // 创建存放excel表格的位置
                File file = new File("/Users/zhangqiankun/Downloads/"+fileName);
                FileOutputStream fos = new FileOutputStream(file);
                workbook.write(fos);
                fos.close();
    
            } catch (Exception e) {
                e.printStackTrace();
            }
            //清空session
            session.removeAttribute("CusList");
            return "customer";
        }
    }

JSP页面

$(document).on("click","#exportExcel",function () {
                window.location.href="${pageContext.request.contextPath}/cus/export";
     });

maven导包：

<dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.17</version>
    </dependency>

参考自：[https://www.cnblogs.com/gudongcheng/p/8268909.html][https_www.cnblogs.com_gudongcheng_p_8268909.html]

[https_www.cnblogs.com_gudongcheng_p_8268909.html]: https://www.cnblogs.com/gudongcheng/p/8268909.html