Excel导入导出详解

excel导入

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

1. 页面部分
   页面部分需要注意form的提交类型必须是 enctype=”multipart/form-data”

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

1. 后台部分
   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文件（就是导出列表多个头，头部分提前放好），并拷贝到数据excel文件中。–这步可以不要，看具体操作

    //模板
    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，放入数据，最好写入到导出文件

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

下载文件

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