Excel導(dǎo)入導(dǎo)出詳解

2019-11-06 06:35:10

字體：大中小

供稿：網(wǎng)友

Excel導(dǎo)入

目的:上傳一個(gè)excel文件，讀取到里面的數(shù)據(jù)并做相關(guān)操作

頁面部分頁面部分需要注意form的提交類型必須是 enctype=”multipart/form-data” <input type="file" name="fileUploader" class="form-control" placeholder="" accept="application/msexcel">后臺部分 1.準(zhǔn)備工作，創(chuàng)建臨時(shí)存放文件夾，分配大小，把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.把提交上來的文件數(shù)據(jù)放到我們的臨時(shí)文件中

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.從臨時(shí)文件excel中一行行讀出數(shù)據(jù)寫入到數(shù)據(jù)庫中

**//整合方法**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); } } } } **//處理數(shù)據(jù)** 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, "付款憑證號"); //當(dāng)必填項(xiàng)都為空的時(shí)候，我就認(rèn)為該條數(shù)據(jù)為空，跳出循環(huán) 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導(dǎo)出

目的：看到一個(gè)列表，生成一個(gè)具體模板的excel表格放在某個(gè)位置，想下載的時(shí)候可以下載

1.提前放好模板 excel文件（就是導(dǎo)出列表多個(gè)頭，頭部分提前放好），并拷貝到數(shù)據(jù)excel文件中。–這步可以不要，看具體操作

//模板 String path; File templateFile ; try { // 導(dǎo)出模板的地址 path = PricatPath +"Template_v10.xlsx"; templateFile = new File(path); } catch (MalformedURLException e) { templateFile = null; } //數(shù)據(jù)excel orderExportFile = FileUtil.getFile(tempFilesFolder+"/"+exportType+"/" + sequenceNum + ".xlsx"); //移動數(shù)據(jù) 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();//得到對應(yīng)的文件通道 out = fo.getChannel();//得到對應(yīng)的文件通道 in.transferTo(0, in.size(), out);//連接兩個(gè)通道，并且從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.查詢得到數(shù)據(jù)，XSSFWorkbook，放入數(shù)據(jù)，最好寫入到導(dǎo)出文件

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 中的版本賦值，后面獲取列名的時(shí)候會用到這個(gè)值 if (!isVersionSupported(sheet)) { return; } // 準(zhǔn)備數(shù)據(jù) 根據(jù)穿進(jìn)去的map參數(shù)查詢得到一個(gè)list數(shù)據(jù)，放在公用變量中 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---調(diào)用 fillDataRowByRow(sheet, data, headerRowNo, exportFileVersion); //fillRowDataCellByCell formatRowData這里根據(jù)不同的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 二進(jìn)制流，下載時(shí)不知道類型 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手機(jī)號碼驗(yàn)證

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