Excel导入导出详解

2019-11-06 06:35:10

字体：大中小

来源：转载

供稿：网友

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文件（就是导出列表多个头，头部分提前放好），并拷贝到数据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(); } } }

上一篇：lx010手机号码验证

下一篇：linux下安装dubbo管理中心