POI包解析Excel文件

最新推荐文章于 2023-12-16 17:39:25 发布

anialy

最新推荐文章于 2023-12-16 17:39:25 发布

阅读量2.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： Java Java Web

本文链接：https://blog.youkuaiyun.com/anialy/article/details/8735517

Java 同时被 2 个专栏收录

57 篇文章

订阅专栏

Java Web

27 篇文章

订阅专栏

简述：

用Apache的POI包解析Excel文件

网页上传Excel文件，之后导入数据到

步骤：

1. 导入POI的Maven依赖项

	    <!-- Excel 操作包 -->
<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-excelant</artifactId>
			<version>3.8</version>
 </dependency>

2. 上传的Jsp文件

<%@ page contentType="text/html;charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:set var="ctx" value="${pageContext.request.contextPath}" />

<html>
<head>
<meta charset="utf-8">
<title>Excel上传</title>
<script type="text/javascript">


$(document).ready(function() {
});


function ajaxFileUploadHomeImage() {
	$.ajaxFileUpload({
		url :  GetContextPath() + "/service/user?action=importUsers" ,  
 		data: {
 			contextPath: GetContextPath(),
	    }, 
		secureuri : false,
		fileElementId : 'fileToUpload',
		dataType : 'json', 
        success: function (data, status)  //服务器成功响应处理函数
        {
            if(typeof(data.error) != 'undefined')
            {
                if(data.error != '')
                {
                    alert(data.error);
                }else
                {
                	alert(data.msg);
                }
            }
        },
        error: function (data, status, e)//服务器响应失败处理函数
        {
            alert(status);
        }
	});
	return false;
}


</script>
</head>
<body>
<h3 class="left">Excel批量上传用户</h3>
  
    <br>
    
	<!-- 上传Excel文件 -->
	<form name="form" method="POST" enctype="multipart/form-data">
		<table>
			<tbody>
				<tr>
					<td><input id="fileToUpload"  type="file" size="45" 
						name="fileToUpload"  class="input"></td>
				</tr>
			</tbody>
			<tfoot>
				<tr>
					<td><button class="button" id="buttonUpload"
							onclick="return ajaxFileUploadHomeImage();">上传Excel</button></td>
				</tr>
			</tfoot>
		</table>
	</form>
</body>
</html>

3. 处理上传的service

	public void importUsers(HttpServletRequest request,
			HttpServletResponse response) {
		ExcelDataArray excelDataArray = null;
		//判断提交过来的表单是否为文件上传菜单
		boolean isMultipart= ServletFileUpload.isMultipartContent(request);
		if(isMultipart){
			//构造一个文件上传处理对象
			FileItemFactory factory = new DiskFileItemFactory();
			ServletFileUpload upload = new ServletFileUpload(factory);
			Iterator items;
			try {
				items = upload.parseRequest(request).iterator();			
				while(items.hasNext()){
					FileItem item = (FileItem) items.next();
					if ( !item.isFormField() ){
						try {
							final int startRow = 4; //Excel数据开始的行数
							excelDataArray = POIExcelUtilProcess
									.importExcelFileToDataArray(item.getInputStream(), startRow);
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
				}
			} catch (FileUploadException e) {
				e.printStackTrace();
			}
			mobileUserService.saveMobileUserDataArray(excelDataArray);
			String responseText = "{error: '',msg: '保存成功'}";
	        
	        //返回数据
	        responseOutWithText(response, responseText);
		}
	}

4.处理之后的数据类

public class ExcelDataArray {
    private String[][] dataArray;
    private int rowNum; //行数
    private int lineNum; //列数
	public ExcelDataArray(int rowNum, int lineNum) {
		this.rowNum = rowNum;
		this.lineNum = lineNum;
		dataArray = new String[rowNum][lineNum];
	}
	public String[][] getDataArray() {
		return dataArray;
	}
	public void setDataArray(String[][] dataArray) {
		this.dataArray = dataArray;
	}
	public int getRowNum() {
		return rowNum;
	}
	public void setRowNum(int rowNum) {
		this.rowNum = rowNum;
	}
	public int getLineNum() {
		return lineNum;
	}
	public void setLineNum(int lineNum) {
		this.lineNum = lineNum;
	}
	
	/**
	 * 设置rowIndex行，lineIndex列的数据
	 * @param rowIndex
	 * @param lineIndex
	 * @param value
	 * @return
	 */
	public String setColumn(int rowIndex, int lineIndex, String value){
		dataArray[rowIndex][lineIndex] = value;
		return dataArray[rowIndex][lineIndex];
	}
	
	
	/**
	 * 获取rowIndex行，lineIndex列的数据
	 * @param rowIndex
	 * @param lineIndex
	 * @return
	 */
	public String getColumnColumn(int rowIndex, int lineIndex){
		return dataArray[rowIndex][lineIndex];
	}
	
}

5. 处理的工具类

public class POIExcelUtilProcess {
	
	private static Logger logger=Logger.getLogger(POIExcelUtilProcess.class);
	
    /**
     * 批量导入Excel表
     * @param is
     * @param startRow
     * @return 返回Excel表的数据矩阵Array[行][列]
     */
	public static ExcelDataArray importExcelFileToDataArray(InputStream is, int startRow){
		try {
			XSSFWorkbook workBook = new XSSFWorkbook(is);
			XSSFSheet  sheet = workBook.getSheetAt(0);
			// 获取Sheet表中所包含的最后一行行号
			int sheetRows = sheet.getLastRowNum();
			// 获取Sheet表中所包含的最后一列的列号
			int sheetLines = sheet.getRow(startRow).getLastCellNum();
			logger.debug("读取Excel表格"
					+ "总行数： " + sheetRows + "\n"
					+ "总列数： " + sheetLines + "\n"
					+ "开始行号： " + startRow + "\n");
			//实际数据字段行数
			int rowCnt = sheet.getLastRowNum() - startRow + 1;
			//实际数据字段列数
			int lineCnt = sheet.getRow(startRow).getLastCellNum();
			//初始化结果数据数组
			ExcelDataArray dataArray = new ExcelDataArray(rowCnt, lineCnt);
			
			//过滤第一行标题
			for(int rowIndex=0; rowIndex<sheetRows-startRow+1; rowIndex++){
				logger.debug("读取第" + (rowIndex) + "行数据");
				XSSFRow row = sheet.getRow(rowIndex+startRow);
				for(int lineIndex=0; lineIndex<sheetLines; lineIndex++){
					logger.debug("读取第" + (lineIndex) + "列数据");
					XSSFCell cell = row.getCell(lineIndex);
					cell.setCellType(XSSFCell.CELL_TYPE_STRING);//默认都先设置为String
					//设置值
					dataArray.setColumn(rowIndex, lineIndex
							, cell.getStringCellValue());
				}
			}
			return dataArray;
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}