spingmvc 上传文件, poi解析xls,xlsx

浏览数：59 / 时间：2015年06月09日

前台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.1

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

@Controller
public 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);

		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}

			// 循环行Row
			for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow == null) {
					continue;
				}

				// 循环列Cell
				for (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);

		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}

			// 循环行Row
			for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}

				// 循环列Cell
				for (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