excel导入

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

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

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

后台部分

准备工作，创建临时存放文件夹，分配大小，从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));

把提交上来文件数据放到我们的临时文件中

        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;
    }

从临时文件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();
        }
    }
}
```

Excel导入导出之java

excel导入

excel导出