Java與Excel的互動!-

沉江劍發表於2018-10-29
JavaExcel

-1. Excel (POI)

1.1 匯入poi報表需要的jar包

<poi.version>3.11</poi.version>

<!--
<!-- Excel解析工具類  
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>${poi.version}</version>
</dependency>
-->
<!-- excel2003使用的包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>
        <!-- excel2007+使用的包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.11</version>
        </dependency>

1.2.訪問

$("#exportXlsBtn").click(function(){
    // 下載效果  不能是ajax非同步
   <!-- $.ajax({
        type: "GET",
        url: "/report/exportXls"
    });
    -->
 location.href = "/excelInpost/downLoad?filename=運單模板表.xlsx";        
});

1.3 用法(頁面)


<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>運單批量匯入</title>
        <!-- 匯入jquery核心類庫 -->
        <script type="text/javascript" src="../../js/jquery-1.8.3.js"></script>
        <!-- 匯入easyui類庫 -->
        <link rel="stylesheet" type="text/css" href="../../js/easyui/themes/default/easyui.css">
        <link rel="stylesheet" type="text/css" href="../../js/easyui/themes/icon.css">
        <link rel="stylesheet" type="text/css" href="../../js/easyui/ext/portal.css">
        <link rel="stylesheet" type="text/css" href="../../css/default.css">
        <script type="text/javascript" src="../../js/easyui/jquery.easyui.min.js"></script>
        <script type="text/javascript" src="../../js/easyui/ext/jquery.portal.js"></script>
        <script type="text/javascript" src="../../js/easyui/ext/jquery.cookie.js"></script>
        <script src="../../js/easyui/locale/easyui-lang-zh_CN.js" type="text/javascript"></script>
        <script type="text/javascript" src="../../js/jquery.ocupload-1.1.2.js"></script>
        <script type="text/javascript">
            $(function(){
                $("#grid").datagrid({
                    url : `/wayBill/pageQuery`,
                    method:"GET",
                    toolbar : [
                        {
                            id : `btn-download`,
                            text : `模板下載`,
                            iconCls : `icon-save`,
                            handler : function(){
                                $.messager.confirm("提示","您確定下載模板嗎?",function () {
                                    location.href = "/excelInpost/downLoad?filename=運單模板表.xlsx";
                                })
                            }
                        },{
                            id : `btn-upload`,
                            text : `批量匯入`,
                            iconCls : `icon-redo`
                        },{
                            id : `btn-refresh`,
                            text : `重新整理`,
                            iconCls : `icon-reload`,
                            handler : function(){
                                $("#grid").datagrid(`reload`);                        
                            }
                        }        
                    ],
                    columns : [[
                        {
                            field : `id`,
                            title : `編號`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `goodsType`,
                            title : `產品`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `sendProNum`,
                            title : `快遞產品型別`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `sendName`,
                            title : `發件人姓名`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `sendMobile`,
                            title : `發件人電話`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `sendAddress`,
                            title : `發件人地址`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `recName`,
                            title : `收件人姓名`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `recMobile`,
                            title : `收件人電話`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `recCompany`,
                            title : `收件人公司`,
                            width : 120 ,
                            align : `center`
                        },{
                            field : `recAddress`,
                            title : `收件人地址`,
                            width : 120 ,
                            align : `center`
                        }
                    ]],
                    pageList: [10,20,30],
                    pagination : true,
                    striped : true,
                    singleSelect: true,
                    rownumbers : true,
                    fit : true // 佔滿容器
                });
                
                /*// 一鍵上傳
                $("#btn-upload").upload({
                     name: `upload`,  // <input name="file" />
                     action: `/excelInpost/excelUp`,  // 提交請求action路徑
                     enctype: `multipart/form-data`, // 編碼格式
                     autoSubmit: true, // 選中檔案提交表單
                     onComplete: function(response) {
                            if(response=="success"){
                                $.messager.alert("提示資訊","資料匯入成功！","info");
                                $("#grid").datagrid("reload");
                            }else{
                                $.messager.alert("錯誤提示",response,"error");
                            }
                     }// 請求完成時 呼叫函式
                });*/
                $("#btn-upload").upload({
                    action:"/excelInpost/excelUp",
                    name:"myFile",
                    onComplete:function () {
                        $.messager.alert("提示資訊","資料匯入成功！","info");
                        $("#grid").datagrid("reload");
                    },
                    onSelect:function () {
                        // 如果檔名字不符合要求，就阻止提交，
                        // 1 關閉預設的自動提交
                        this.autoSubmit = false;
                        // 2 獲取檔名
                        var filename = this.filename();
                        // 3 正規表示式校驗
                        // . 1個換行符意外的任意字元
                        // * 0-n個任意字元
                        // . 標識.
                        // (a|b)  只能出現a或者b
                        var regex = /^.*.(xls|xlsx)$/;
                        // 校驗
                        if (regex.test(filename)) {
                            // 手動提交
                            this.submit();
                        }else{
                            // 不符合規則，不提交，給出提醒
                            $.messager.alert("提示","請選擇xls或者xlsx結尾的檔案")
                        }
                    }
                })
            });
        </script>

    </head>

    <body class="easyui-layout">
        <div region="center">
            <table id="grid"></table>
        </div>
    </body>
</html>

1.4 用法(後臺) —>自定義模板下載

  /**
     * 模板下載
     * @param filename 檔名
     * @return
     * @throws Exception
     */
    @GetMapping("/downLoad")
    public ResponseEntity<String> downLoadExcel(String filename) throws Exception {

        System.out.println(filename+"-------------------------------------------------------");
        /**
         * 查詢資料之後，下面只需要將內容寫進xls中，然後下載
         */

        //1 建立工作簿
        Workbook wb = new XSSFWorkbook();
        //2 建立工作表
        Sheet sheet = wb.createSheet();

        /**
         * 定義公共變數
         */
        int rowNo=0,cellNo=0;//行號  和  列號
        Row nRow = null;// 行物件通用變數
        Cell nCell = null;// 單元格物件通用變數

        /****************大標題列印****************/
        //3 建立行
        nRow = sheet.createRow(rowNo);
        // 設定行高
        nRow.setHeightInPoints(36);
        //4 建立單元格
        nCell = nRow.createCell(cellNo);
        //5 設定內容
        nCell.setCellValue("運單資料");
        //6 設定內容格式
        // 合併單元格  //引數1：起始行 引數2：終止行 引數3：起始列 引數4：終止列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) 9));
        // 樣式
        CellStyle bigTitleCellStyle = bigTitleStyle(wb);
        nCell.setCellStyle(bigTitleCellStyle);

        rowNo++;
        nRow = sheet.createRow(rowNo);

        //小標題
        String[] titles={"編號","產品","快遞產品型別","發件人姓名","發件人電話","發件人地址","收件人姓名","收件人電話","收件人公司","收件人地址"};

        for (int i = 0;i<titles.length;i++){
            String title = titles[i];
            //4 建立單元格
            nCell = nRow.createCell(cellNo++);// 先建立單元格，然後在新增
            //5 設定內容
            nCell.setCellValue(title);
            //6 設定內容格式
            nCell.setCellStyle(titleStyle(wb));

            //設定列寬 -->自動
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i,sheet.getColumnWidth(i)*17/10);


        }

        //下載
        DownloadUtil downloadUtil = new DownloadUtil();
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        wb.write(byteArrayOutputStream);
        downloadUtil.download(byteArrayOutputStream,response,filename);

        return new ResponseEntity<>(HttpStatus.OK);

    }

1.5 上傳匯入

/**
     * 匯入
     * @return
     */
    @RequestMapping("/excelUp")
    public ResponseEntity<Void> batchImport(@RequestParam("myFile") MultipartFile file){
        try {

            // 1.1 獲取檔名
            String originalFilename = file.getOriginalFilename();// 檔名
            String name = file.getName();//此名字對應<input> 文字框的name屬性的值
            // 1.2 設定儲存的path
            String path = "d:\"+ UUID.randomUUID().toString()+originalFilename;
            // 1.3 建立File--path
            File myFile = new File(path);
            // 1.4 複製
            file.transferTo(myFile);


            // 解析xls，將資料存入資料庫
            // 2 建立Workbook
            // 2.1 建立輸入流
            FileInputStream is = new FileInputStream(myFile);
//            InputStream is = file.getInputStream();

            // 2.2 建立Workbook
            Workbook wb = new XSSFWorkbook(is);
            // 3 獲取Sheet
            Sheet sheet = wb.getSheetAt(0);

            // 定義List集合存放資料，最後一次性儲存
            List<WayBill> list = new ArrayList<>();

            // 4 獲取row
            for(Row row:sheet){
                // 5 獲取單元格資料
                // 跳過第一行
                if(row.getRowNum()==0 || row.getRowNum()==1){
                    continue;
                }

                // 如果第一列為空，整條資料都不讀取
                if(row.getCell(0)==null || (int) row.getCell(0).getNumericCellValue()==0){
                    continue;
                }


                WayBill wayBill = new WayBill();
                //編號
                int value0 = (int) row.getCell(0).getNumericCellValue();
                wayBill.setId(value0);

                //產品
                wayBill.setGoodsType(row.getCell(1).getStringCellValue());

                //快遞產品型別
                wayBill.setSendProNum(row.getCell(2).getStringCellValue());

                //發件人姓名
                wayBill.setSendName(row.getCell(3).getStringCellValue());

                //發件人電話
                String value4 =""+ (int) row.getCell(4).getNumericCellValue();
                wayBill.setSendMobile(value4);

                //發件人地址
                wayBill.setSendAddress(row.getCell(5).getStringCellValue());

                //收件人姓名
                wayBill.setRecName(row.getCell(6).getStringCellValue());

                //收件人電話
                wayBill.setRecMobile(""+ (int) row.getCell(7).getNumericCellValue());

                //收件人公司
                wayBill.setRecCompany(row.getCell(8).getStringCellValue());

                //收件人地址
                wayBill.setRecAddress(row.getCell(9).getStringCellValue());


                list.add(wayBill);
            }


            // 6 儲存至資料庫
            wayBillService.saveWayBillAll(list);


        }catch (Exception e){
            e.printStackTrace();
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
        return new ResponseEntity<>(HttpStatus.OK);
    }



    public CellStyle bigTitleStyle(Workbook wb){
        // 建立格式
        CellStyle cellStyle = wb.createCellStyle();
        // 水平對齊方式
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        // 設定字型
        Font font = wb.createFont();
        // 是數值的1/20 大小
        font.setFontHeight((short) 480);

        font.setBold(true);
        font.setColor(Font.COLOR_RED);

        cellStyle.setFont(font);
        return cellStyle;
    }

    public CellStyle titleStyle(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);

        Font font = wb.createFont();
        font.setFontHeight((short)300);
        cellStyle.setFont(font);
        return cellStyle;
    }


相關文章