標籤：資料 excel java
前台jsp
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>資料匯入</title> <style type="text/css"></style></head><body><div align="right"> <form action="<%=request.getContextPath()%>/loanData" method="POST" enctype="multipart/form-data"> <table class="table" id="queryCondition"><tbody class="tbd"><tr><td align="right" style="padding-right: 2px"><input type="file" name="myfiles" id="myfiles" style="display: none;" onchange="document.getElementById('filePath').value=this.value" /><div class="input-group"><input type='text' name='filePath' id='filePath' class='form-control'/> <span class="input-group-btn"><button type="button" class="btn btn-sm btn-info blue" id="btn_check"><i class="icon-edit">請選擇檔案</i></button></span></div></td><td align="left" style="padding-left: 2px"><button type="submit" class="btn btn-sm btn-info" id="upload"><i class="upload-icon icon-cloud-upload bigger-110">匯入</i></button></td></tr></tbody></table></form></div> <script type="text/javascript">$(function() {$("#btn_check").click(function() {$("#myfiles").trigger('click');});$("#filePath").click(function() {$("#myfiles").trigger('click');});});</script></body></html>
java代碼，使用的包為poi3.5，commons-io2.1import java.io.File;import java.io.IOException;import java.io.InputStream;import javax.servlet.http.HttpServletRequest;import org.apache.commons.io.FileUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.multipart.MultipartFile;@Controllerpublic class HdImporController {@RequestMapping("/initLoanData")public String initLoanData(HttpServletRequest request) {return "views/service/import/loanData";}@RequestMapping(value = "/loanData", method = RequestMethod.POST)public String loanData(@RequestParam MultipartFile[] myfiles,HttpServletRequest request) throws IOException {// 如果只是上傳一個檔案，則只需要MultipartFile類型接收檔案即可，而且無需顯式指定@RequestParam註解// 如果想上傳多個檔案，那麼這裡就要用MultipartFile[]類型來接收檔案，並且還要指定@RequestParam註解// 並且上傳多個檔案時，前台表單中的所有<input// type="file"/>的name都應該是myfiles，否則參數裡的myfiles無法擷取到所有上傳的檔案File[] files = new File[myfiles.length];for (MultipartFile myfile : myfiles) {if (myfile.isEmpty()) {System.out.println("檔案未上傳");} else {System.out.println("檔案長度: " + myfile.getSize());System.out.println("檔案類型: " + myfile.getContentType());System.out.println("檔案名稱: " + myfile.getName());System.out.println("檔案原名: " + myfile.getOriginalFilename());System.out.println("========================================");// 如果用的是Tomcat伺服器，則檔案會上傳到\\%TOMCAT_HOME%\\webapps\\YourWebProject\\WEB-INF\\upload\\檔案夾中String realPath = request.getSession().getServletContext().getRealPath("/files/upload/loanData");// 這裡不必處理IO流關閉的問題，因為FileUtils.copyInputStreamToFile()方法內部會自動把用到的IO流關掉，我是看它的源碼才知道的File file = new File(realPath, myfile.getOriginalFilename());FileUtils.copyInputStreamToFile(myfile.getInputStream(), file);if(myfile.getOriginalFilename().toLowerCase().endsWith("xls")){readXls(myfile.getInputStream());}else{readXlsx(file+"");}}}return "views/service/import/loanData";}private void readXlsx(String fileName) throws IOException {//String fileName = "D:\\excel\\xlsx_test.xlsx";XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);// 迴圈工作表Sheetfor (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);if (xssfSheet == null) {continue;}// 迴圈行Rowfor (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {XSSFRow xssfRow = xssfSheet.getRow(rowNum);if (xssfRow == null) {continue;}// 迴圈列Cellfor (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {XSSFCell xssfCell = xssfRow.getCell(cellNum);if (xssfCell == null) {continue;}System.out.print(" " + getValue(xssfCell));}System.out.println();}}} @SuppressWarnings("static-access")private String getValue(XSSFCell xssfCell) {if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {return String.valueOf(xssfCell.getBooleanCellValue());} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {return String.valueOf(xssfCell.getNumericCellValue());} else {return String.valueOf(xssfCell.getStringCellValue());}}private void readXls(InputStream is) throws IOException {HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);// 迴圈工作表Sheetfor (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);if (hssfSheet == null) {continue;}// 迴圈行Rowfor (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {HSSFRow hssfRow = hssfSheet.getRow(rowNum);if (hssfRow == null) {continue;}// 迴圈列Cellfor (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {HSSFCell hssfCell = hssfRow.getCell(cellNum);if (hssfCell == null) {continue;}System.out.print(" " + getValue(hssfCell));}System.out.println();}}}@SuppressWarnings("static-access")private String getValue(HSSFCell hssfCell) {if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {return String.valueOf(hssfCell.getBooleanCellValue());} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {return String.valueOf(hssfCell.getNumericCellValue());} else {return String.valueOf(hssfCell.getStringCellValue());}}}
spingmvc 上傳檔案, poi解析xls,xlsx
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
