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<map<string,string>> readexcel(string filepath, string filename, int startrow, int startcol, int sheetnum) {
 list<map<string, string>> varlist = new arraylist<map<string, string>>();
 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<map<string,string>> readexcel2003(string filepath, string filename, int startrow, int startcol, int sheetnum) {
 list<map<string, string>> varlist = new arraylist<map<string, string>>();
 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<string, string> varpd = new hashmap<string, string>();
 // 行
 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<map<string,string>> readexcel2007(string filepath, string filename, int startrow, int startcol, int sheetnum) {
 list<map<string, string>> varlist = new arraylist<map<string, string>>();
 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<string, string> varpd = new hashmap<string, string>();
 // 得到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<map<string, string>> list = r.readexcel("e:\\excel", "测试表格.xls", 0, 0, 0);
 if (list != null) {
 for (int i = 0; i < list.size(); i++) {
 map<string, string> m = list.get(i);
 m.foreach((key, value) -> {
  system.out.println(key + ":" + value);
 });
 system.out.println();
 }
 }

 list<map<string, string>> lists = r.readexcel("e:\\excel", "测试表格.xlsx", 0, 0, 0);
 if (lists != null) {
 for (int i = 0; i < lists.size(); i++) {
 map<string, string> 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项目依赖

<!-- poi -->
<dependency>
 <groupid>org.apache.poi</groupid>
 <artifactid>poi</artifactid>
 <version>4.0.1</version>
</dependency>
<dependency>
 <groupid>org.apache.poi</groupid>
 <artifactid>poi-ooxml</artifactid>
 <version>4.0.1</version>
</dependency>

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