POI解析Excel(.xls)和Excel2007(.xlsx)

最新推荐文章于 2024-08-25 15:24:33 发布

mhady

最新推荐文章于 2024-08-25 15:24:33 发布

阅读量1.5k

点赞数

本文链接：https://blog.csdn.net/mhady/article/details/50473787

版权

Java Web 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

在java项目中解析Microsoft的office系列软件，都会用到Apache的poi包，从office2007开始office系列的文件后缀名发生了改变，所以原来低版本的poi并不支持office2007以后的文件解析。好像是从poi3.5以后才支持了2007版本的解析。如果项目中的poi版本太旧可以在http://poi.apache.org/download.html下载新版本的poi。

1.解析.xls文件

public List<TestUpload> readXls(String filePath) throws IOException {
		
		GetSys GetSys=new GetSys();
		InputStream is = new FileInputStream(filePath);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		TestUpload testupload = null;
		List<TestUpload> list = new ArrayList<TestUpload>();

		for (int numSheet = 0; numSheet <hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}

			for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);

				if (hssfRow != null) {
					testupload= new TestUpload();
					
					//HSSFCell id = hssfRow.getCell((short) 0);
					HSSFCell  name= hssfRow.getCell((short)0);

					//testupload.setUserId(GetSys.getUserId().toString());
					//testupload.setId(id.getNumericCellValue());
					testupload.setName(getValue_Xls(name));

					list.add(testupload);
				}
			}
		}
		return list;
	}

上面函数中，有getValue_Xls(),作用是格式化excel文件每个单元格的数据类型，将单元格中的不同数据类型都转换为String，代码如下：

private String getValue_Xls(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);
		}
	}

当然还要导入各种包：

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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;

2.解析.xlsx文件

xlsx的解析跟xls的解析非常相近，只是将导入的包从HSSF***换成XSSF***。

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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;

public List<TestUpload> readXlsx(String filePath) throws IOException {
		
		GetSys GetSys=new GetSys();
		InputStream is = new FileInputStream(filePath);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		TestUpload testupload = null;
		List<TestUpload> list = new ArrayList<TestUpload>();

		for (int numSheet = 0; numSheet <xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}

			for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);

				if (xssfRow != null) {
					testupload= new TestUpload();
					
					//HSSFCell id = hssfRow.getCell((short) 0);
					XSSFCell  name= xssfRow.getCell((short)0);

					//testupload.setUserId(GetSys.getUserId().toString());
					//testupload.setId(id.getNumericCellValue());
					testupload.setName(getValue_Xlsx(name));

					list.add(testupload);
				}
			}
		}
		return list;
	}

private String getValue_Xlsx(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());
		}
	}