欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

java读取简单excel通用工具类

程序员文章站 2022-07-08 18:13:45
本文实例为大家分享了java读取简单excel通用工具类的具体代码,供大家参考,具体内容如下读取excel通用工具类import java.io.file;import java.io.fileinp...

本文实例为大家分享了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>

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

相关标签: java 读取 excel