java excel 读取工具_java读取简单excel通用工具类

本文实例为大家分享了java读取简单excel通用工具类的具体代码，供大家参考，具体内容如下

读取excel通用工具类

import java.io.File;

import java.io.FileInputStream;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

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.ss.usermodel.CellType;

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;

/**

* 读取Excel通用工具类

* @author zql

*/

public class ReadExcel {

/**

* 读取Excel

*

* @param filepath 文件路径

* @param filename 文件名，包括扩展名

* @param startrow 开始行号，索引从0开始

* @param startcol 开始列号，索引从0开始

* @param sheetnum 工作簿，索引从0开始

* @return

*/

public List> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {

List> varList = new ArrayList>();

String suffix = filename.substring(filename.lastIndexOf(".") + 1);

if ("xls".equals(suffix)) {

varList = readExcel2003(filepath, filename, startrow, startcol, sheetnum);

} else if ("xlsx".equals(suffix)) {

varList = readExcel2007(filepath, filename, startrow, startcol, sheetnum);

} else {

System.out.println("Only excel files with XLS or XLSX suffixes are allowed to be read!");

return null;

}

return varList;

}

/**

* 读取2003Excel

*

* @param filepath 文件路径

* @param filename 文件名，包括扩展名

* @param startrow 开始行号，索引从0开始

* @param startcol 开始列号，索引从0开始

* @param sheetnum 工作簿，索引从0开始

* @return

*/

public List> readExcel2003(String filepath, String filename, int startrow, int startcol, int sheetnum) {

List> varList = new ArrayList>();

try {

File target = new File(filepath, filename);

FileInputStream fis = new FileInputStream(target);

HSSFWorkbook wb = new HSSFWorkbook(fis);

fis.close();

// sheet 从0开始

HSSFSheet sheet = wb.getSheetAt(sheetnum);

// 取得最后一行的行号

int rowNum = sheet.getLastRowNum() + 1;

HSSFRow rowTitle = sheet.getRow(0);

// 标题行的最后一个单元格位置

int cellTitleNum = rowTitle.getLastCellNum();

String[] title = new String[cellTitleNum];

for (int i = startcol; i < cellTitleNum; i++) {

HSSFCell cell = rowTitle.getCell(Short.parseShort(i + ""));

if (cell != null) {

cell.setCellType(CellType.STRING);

title[i] = cell.getStringCellValue();

} else {

title[i] = "";

}

}

// 行循环开始

for (int i = startrow + 1; i < rowNum; i++) {

Map varpd = new HashMap();

// 行

HSSFRow row = sheet.getRow(i);

// 列循环开始

for (int j = startcol; j < cellTitleNum; j++) {

HSSFCell cell = row.getCell(Short.parseShort(j + ""));

String cellValue = "";

if (cell != null) {

// 把类型先设置为字符串类型

cell.setCellType(CellType.STRING);

cellValue = cell.getStringCellValue();

}

varpd.put(title[j], cellValue);

}

varList.add(varpd);

}

wb.close();

} catch (Exception e) {

System.out.println(e);

}

return varList;

}

/**

* 读取2007Excel

*

* @param filepath 文件路径

* @param filename 文件名，包括扩展名

* @param startrow 开始行号，索引从0开始

* @param startcol 开始列号，索引从0开始

* @param sheetnum 工作簿，索引从0开始

* @return

*/

public List> readExcel2007(String filepath, String filename, int startrow, int startcol, int sheetnum) {

List> varList = new ArrayList>();

try {

File target = new File(filepath, filename);

InputStream ins = new FileInputStream(target);

XSSFWorkbook wb = new XSSFWorkbook(ins);

ins.close();

// 得到Excel工作表对象

XSSFSheet sheet = wb.getSheetAt(sheetnum);

// 取得最后一行的行号

int rowNum = sheet.getLastRowNum() + 1;

XSSFRow rowTitle = sheet.getRow(0);

int cellTitleNum = rowTitle.getLastCellNum();

String[] title = new String[cellTitleNum];

for (int i = startcol; i < cellTitleNum; i++) {

XSSFCell cell = rowTitle.getCell(Short.parseShort(i + ""));

if (cell != null) {

// 把类型先设置为字符串类型

cell.setCellType(CellType.STRING);

title[i] = cell.getStringCellValue();

} else {

title[i] = "";

}

}

// 行循环开始

for (int i = startrow + 1; i < rowNum; i++) {

Map varpd = new HashMap();

// 得到Excel工作表的行

XSSFRow row = sheet.getRow(i);

// 列循环开始

for (int j = startcol; j < cellTitleNum; j++) {

// 得到Excel工作表指定行的单元格

XSSFCell cell = row.getCell(j);

String cellValue = "";

if (cell != null) {

// 把类型先设置为字符串类型

cell.setCellType(CellType.STRING);

cellValue = cell.getStringCellValue();

}

varpd.put(title[j], cellValue);

}

varList.add(varpd);

}

wb.close();

} catch (Exception e) {

System.out.println(e);

}

return varList;

}

}

读取excel通用工具示例测试类

import java.util.List;

import java.util.Map;

/**

* @author zql

*

*/

public class ReadExcelTest {

public static void main(String[] args) throws Exception {

ReadExcel r = new ReadExcel();

List> list = r.readExcel("e:\\excel", "测试表格.xls", 0, 0, 0);

if (list != null) {

for (int i = 0; i < list.size(); i++) {

Map m = list.get(i);

m.forEach((key, value) -> {

System.out.println(key + ":" + value);

});

System.out.println();

}

}

List> lists = r.readExcel("e:\\excel", "测试表格.xlsx", 0, 0, 0);

if (lists != null) {

for (int i = 0; i < lists.size(); i++) {

Map m = lists.get(i);

m.forEach((key, value) -> {

System.out.println(key + ":" + value);

});

System.out.println();

}

}

}

}

普通项目需要引入的包

poi-4.0.1.jar

poi-ooxml-4.0.1.jar

poi-ooxml-schemas-4.0.1.jar

commons-codec-1.11.jar

commons-collections4-4.3.jar

commons-math3-3.6.1.jar

xmlbeans-3.0.2.jar

commons-compress-1.18.jar

curvesapi-1.06.jar

maven项目依赖

org.apache.poi

poi

4.0.1

org.apache.poi

poi-ooxml

4.0.1

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。