Excel导入导出之java

太过爱你忘了你带给我的痛 2022-10-01 10:47 180阅读 0赞

# excel导入 #

> 目的:上传一个excel文件，读取到里面的数据并做相关操作

*  页面部分 需注意form的提交类型必须为 enctype = "multipart/form-data"

<input type="file" name="fileUploader" class="form-control" placeholder="" accept="application/msexcel">
    复制代码

*  后台部分
    
    1.  准备工作， 创建临时存放文件夹，分配大小，从request中得到FileItem
        
            File userFolder = FileUtil.getFile(tempFilesFolder + userLoginId + "/");
            if (!userFolder.exists()) {
                userFolder.mkdirs();
            }
            FileItemFactory fit=new DiskFileItemFactory(10240, userFolder);
            ServletFileUpload dfu = new ServletFileUpload(fit);
            fileItems = UtilGenerics.checkList(dfu.parseRequest(request));
            复制代码
    2.  把提交上来文件数据放到我们的临时文件中
        
            private boolean storeAcctgFile() throws IOException {
                    FileItem fi = null;
                    FileItem pricatFi = null;
                    byte[] pricatBytes = {};
                    // store the file
                    for (int i = 0; i < fileItems.size(); i++) {
                        fi = fileItems.get(i);
                        String fieldName = fi.getFieldName();
                        if (fieldName.equals("fileUploader")) {
                            pricatFi = fi;
                            pricatBytes = pricatFi.get();
                            Path path = Paths.get(fi.getName());
                            acctgFile = new File(tempFilesFolder + userLoginId + "/" + path.getFileName().toString());
                            FileOutputStream fos = new FileOutputStream(acctgFile);
                            fos.write(pricatBytes);
                            fos.flush();
                            fos.close();
                            session.setAttribute(AcctgParseExcelHtmlThread.ACCTG_FILE, acctgFile.getAbsolutePath());
                        }
                    }
                    return true;
                }
            复制代码
    3.  从临时文件excel中一行行读出数据写入到数据库
        
            **//整合方法**
            private void parseAcctgExcel() {
            XSSFWorkbook workbook = null;
            try {
                // 2. store the pricat excel file
                storeAcctgFile();
            
                // 3. read the pricat excel file
                FileInputStream is = new FileInputStream(acctgFile);
            
            
                try {
                    workbook = new XSSFWorkbook(is);
            
                } catch(IOException e) {
            
                    deletePricatFile = true;
                    return;
                } catch(POIXMLException e) {
            
                    deletePricatFile = true;
                    return;
                }
                XSSFSheet sheet = workbook.getSheetAt(0);
                containsDataRows(sheet);
                // parse row by row and store the contents into xml file(s)
                parseRowByRow(sheet);
                deletePricatFile = true;
            } catch (IOException e) {
                getReport().println(e);
                Debug.logError(e, module);
            } finally {
                if (UtilValidate.isNotEmpty(fileItems)) {
                    // remove tmp files
                    FileItem fi = null;
                    for (int i = 0; i < fileItems.size(); i++) {
                        fi = fileItems.get(i);
                        fi.delete();
                    }
                }
                if (deletePricatFile && acctgFile != null && acctgFile.exists() && acctgFile.isFile()) {
                    acctgFile.delete();
                }
                if (workbook != null) {
                    try {
                        workbook.close();
                    } catch (IOException e) {
                        Debug.logError(e, module);
                    }
                }
            }
            复制代码
    
    \}
    
    **//处理数据** private void parseRowByRow(XSSFSheet sheet) \{ int rows = sheet.getPhysicalNumberOfRows(); List<Object\[\]> colNames = ColNamesList.get(acctgFileVersion); int colNumber = colNames.size();
    
        for (int i = headerRowNo + 1; i < rows; i++) {
                  getReport().print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE);
                  XSSFRow row = sheet.getRow(i);
                  if (UtilValidate.isEmpty(row)) {
                      continue;
                  }
                  List<Object> cellContents = getCellContents(row, colNames, colNumber);
                  try {
                      String externalId = (String) getCellContent(cellContents, "商家订单号");
                      String salesMoney = (String) getCellContent(cellContents, "收入金额");
                      String tradeNo = (String) getCellContent(cellContents, "付款凭证号");
                      //当必填项都为空的时候，我就认为该条数据为空，跳出循环
                      if(UtilValidate.isEmpty(externalId)&&UtilValidate.isEmpty(salesMoney)&&UtilValidate.isNotEmpty(tradeNo)){
                          break;
                      }else{
        
                          if (parseCellContentsAndStore(row, cellContents)) {
                              getReport().println(" ... " + UtilProperties.getMessage(resource, "ok", getLocale()), InterfaceReport.FORMAT_OK);
                          } else {
                              getReport().println(" ... " + UtilProperties.getMessage(resource, "skipped", getLocale()), InterfaceReport.FORMAT_NOTE);
                          }
                      }
                  } catch (GenericTransactionException e) {
                      getReport().println(e);
                  }
              }
          }
          private Object getCellContent(List<Object> cellContents, String colName) {
              if (UtilValidate.isNotEmpty(headerColNames) && headerColNames.contains(colName)) {
                  return cellContents.get(headerColNames.indexOf(colName));
              }
              return null;
          }
        复制代码

# excel导出 #

> 目的：看到一个列表，生成一个具体模板的excel表格放在某个位置，向下载的时候可以下载

1. 提前放好模板excel文件（就是做个有表头的表格）,需要时复制模板
    ```java
         //模板
        String path;
        File templateFile ;
            try {
                // 导出模板的地址
                path = PricatPath +"Template_v10.xlsx";
                templateFile = new File(path);
            } catch (MalformedURLException e) {
                templateFile = null;
            }
        //数据excel
        orderExportFile = FileUtil.getFile(tempFilesFolder+"/"+exportType+"/" + sequenceNum + ".xlsx");
        //移动数据
        copyFile(templateFile, exportFile)
        public static boolean copyFile(File srcFile, File destFile) {
            FileInputStream fi = null;
            FileOutputStream fo = null;
            FileChannel in = null;
            FileChannel out = null;
            try {
                fi = new FileInputStream(srcFile);
                fo = new FileOutputStream(destFile);
                in = fi.getChannel();//得到对应的文件通道
                out = fo.getChannel();//得到对应的文件通道
                in.transferTo(0, in.size(), out);//连接两个通道，并且从in通道读取，然后写入out通道
                return true; 
            } catch (IOException e) {
                e.printStackTrace();
                return false;
            } finally {
                try {
                    fi.close();
                    in.close();
                    fo.close();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    
    ```
    2. 查询得到数据， XSSFWorkbook，放入数据，最好写入到导出文件
    ```java
        public void exportExcel(Map<String, String[]> parameters) {
        XSSFWorkbook workbook = null;
        try {
            // 1. read the pricat excel file
            FileInputStream is = new FileInputStream(exportFile);
    
            // 2. use POI to load this bytes
    
            try {
                workbook = new XSSFWorkbook(is);
            } catch (IOException e) {
                return;
            } catch (POIXMLException e) {
                return;
            }
    
            isNumOfSheetsOK(workbook);
    
            XSSFSheet sheet = workbook.getSheetAt(0);
            // 这里会给AbstractReportThread 中的 版本赋值，后面获取列名的时候会用到这个值
            if (!isVersionSupported(sheet)) {
                return;
            }
            // 准备数据 根据穿进去的map参数查询得到一个list数据，放在公用变量中
            prepareData(parameters);
            // 4. fill data in the 1st sheet
            fillSheet(sheet);
    
            // 5. write the workbook to file
            writeWorkbookToFile(workbook, exportFile);
    
            // 6. clean up the log files and exported Excel files
            cleanupLogAndExportedExcel(parameters.get("exportType")[0]);
        } catch (IOException e) {
            report.println(e);
            Debug.logError(e, module);
        } finally {
    
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    Debug.logError(e, module);
                }
            }
        }
    }
    
    //fillSheet---调用 fillDataRowByRow(sheet, data, headerRowNo, exportFileVersion);
    //fillRowDataCellByCell  formatRowData这里根据不同的version获取具体列
       int i = headerRowNo + 1;
        int j = 0;
        boolean useAlterStyle = false;
        for (类型 rowData : data) {
            XSSFRow row = sheet.createRow(i);
            List<Object> cellContents = formatRowData(rowData.getAllFields(), exportFileVersion);
           fillRowDataCellByCell(row, cellContents, useAlterStyle ? cellStyle : null, lastCellStyle))
            i++;
            j++;
            if (j/10*10 == j) {
                useAlterStyle = useAlterStyle ? false : true;
            }
        }
    
    //copy
    public void writeWorkbookToFile(XSSFWorkbook workbook, File exportFile) {
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(exportFile);
            workbook.write(fos);
            fos.flush();
            fos.close();
        } catch (FileNotFoundException e) {
            Debug.logError(e, module);
        } catch (IOException e) {
            Debug.logError(e, module);
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    Debug.logError(e, module);
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    Debug.logError(e, module);
                }
            }
        }
    }
    ```
    
    # 下载文件
    
    ```java
        public static final String ReconciliationTempName = "文件名.xlsx";
    
        Path path = Paths.get(URL地址);
        byte[] bytes=Files.readAllBytes(path);
        //application/octet-stream 二进制流，下载时不知道类型
        streamContentToBrowser(response, bytes,"application/octet-stream", URLEncoder.encode(ReconciliationTempName,"UTF-8"));
    
    public static void streamContentToBrowser(HttpServletResponse response, byte[] bytes, String contentType, String fileName) throws IOException {
        // tell the browser not the cache
        setResponseBrowserProxyNoCache(response);
    
        // set the response info
        response.setContentLength(bytes.length);
        if (contentType != null) {
            response.setContentType(contentType);
        }
        if (fileName != null) {
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        }
    
        // create the streams
        OutputStream out = response.getOutputStream();
        InputStream in = new ByteArrayInputStream(bytes);
    
        // stream the content
        try {
            streamContent(out, in, bytes.length);
        } catch (IOException e) {
            in.close();
            out.close(); // should we close the ServletOutputStream on error??
            throw e;
        }
    
        // close the input stream
        in.close();
    
        // close the servlet output stream
        out.flush();
        out.close();
    }
    
    public static void setResponseBrowserProxyNoCache(HttpServletResponse response) {
        long nowMillis = System.currentTimeMillis();
        response.setDateHeader("Expires", nowMillis);
        response.setDateHeader("Last-Modified", nowMillis); // always modified
        response.setHeader("Cache-Control", "no-store, no-cache, must-revalidate"); // HTTP/1.1
        response.addHeader("Cache-Control", "post-check=0, pre-check=0, false");
        response.setHeader("Pragma", "no-cache"); // HTTP/1.0
    }
    复制代码

public static void streamContent(OutputStream out, InputStream in, int length) throws IOException \{ int bufferSize = 512; // same as the default buffer size; change as needed

// make sure we have something to write to
        if (out == null) {
            throw new IOException("Attempt to write to null output stream");
        }
    
        // make sure we have something to read from
        if (in == null) {
            throw new IOException("Attempt to read from null input stream");
        }
    
        // make sure we have some content
        if (length == 0) {
            throw new IOException("Attempt to write 0 bytes of content to output stream");
        }
    
        // initialize the buffered streams
        BufferedOutputStream bos = new BufferedOutputStream(out, bufferSize);
        BufferedInputStream bis = new BufferedInputStream(in, bufferSize);
    
        byte[] buffer = new byte[length];
        int read = 0;
        try {
            while ((read = bis.read(buffer, 0, buffer.length)) != -1) {
                bos.write(buffer, 0, read);
            }
        } catch (IOException e) {
            Debug.logError(e, "Problem reading/writing buffers", module);
            bis.close();
            bos.close();
            throw e;
        } finally {
            if (bis != null) {
                bis.close();
            }
            if (bos != null) {
                bos.flush();
                bos.close();
            }
        }
    }
    ```
    复制代码

转载于:https://juejin.im/post/5c6e3afc6fb9a049e2329973