操作ｅｘｃｅｌ文件，从文件中读取数据。

最新推荐文章于 2024-07-23 19:00:57 发布

comeonliming

最新推荐文章于 2024-07-23 19:00:57 发布

阅读量169

点赞数

分类专栏： java 文章标签： Excel 单元测试 J# Apache log4j

java 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

java 代码

import org.apache.log4j.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.util.*;
import java.io.*;
/**
*
* <p>Title: POIBean.java</p>
*
* <p>Description: 该类用于操作ｅｘｃｅｌ文件，从文件中读取数据。</p>
* @version 1.0
*/
public class POIUtil
{
//定义日志记录器
private static final Logger log = Logger.getLogger(POIUtil.class);
private static POIUtil poiUtil = null;
//单例
POIUtil()
{
}
public static POIUtil getInstance()
{
if (poiUtil == null)
{
poiUtil = new POIUtil();
}
return poiUtil;
}
/*******读取xls表格中的数据********
* @param filePath : 文件完整路径名
* @param worksheet: 读取的工作表，0为第一张工作表
* @param start : 开始读的行数
* @param length : 读取的列数长度，从第一列开始
* @param link : 连接各个单元格的值
* @param maxRows : 读取的最多行数
* @throws IOException
*/
public ArrayList readData(String filePath, int worksheet, int start,
int length, String link, final int maxRows) throws IOException
{
try
{
File file = new File(filePath);
if (!file.exists())
{
log.error("read file failed : " + filePath + " is not exist !");
return null;
}
}
catch (Exception e)
{
log.error(e.toString());
return null;
}
ArrayList arrayList = new ArrayList();
FileInputStream fis = null;
try
{
fis = new FileInputStream(filePath);
POIFSFileSystem fs = new POIFSFileSystem(fis);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(worksheet);
//不存在worksheet
if (sheet == null)
{
log.error("can not get the specified sheet at " + worksheet);
return null;
}
//所有的行数
int times = 0;
for (int i = start; i <= sheet.getLastRowNum(); i++)
{
//用于单元格内容为空时，确定其坐标
String division = "";
//整行数据都不为空时，存储这一行的数据
StringBuffer rowValue = new StringBuffer("");
times++;
//超过读的行数
if (times > maxRows)
{
break;
}
HSSFRow row = sheet.getRow(i);
//行值不为空
if (row != null)
{
//所有的列数
for (int j = 0; j < length; j++)
{
HSSFCell cell = row.getCell((short) j);
if (cell == null)
{
division = "@" + (i + 1) + ":" + (j + 1);
rowValue.append(division);
rowValue.append(link);
log.debug("cell " + (i + 1) + ":" + (j + 1) +
"is null");
continue;
}
switch (cell.getCellType())
{
//单元格内容是数字
case HSSFCell.CELL_TYPE_NUMERIC:
rowValue.append(new Double(cell.
getNumericCellValue()).intValue());
rowValue.append("@" + (i + 1) + ":" + (j + 1));
rowValue.append(link);
break;
//单元格内容是字符串
case HSSFCell.CELL_TYPE_STRING:
rowValue.append(cell.getStringCellValue());
rowValue.append("@" + (i + 1) + ":" + (j + 1));
rowValue.append(link);
break;
default:
division = "@" + (i + 1) + ":" + (j + 1);
rowValue.append(division);
rowValue.append(link);
}
}
arrayList.add(rowValue.toString());
}
//行值为空,登记位置
else
{
log.debug("row " + (i + 1) + "is null");
}
}
}
catch (IOException e)
{
log.error(e.toString());
throw new IOException("IO Exception was found");
}
finally
{
// 关闭流
if (fis != null)
{
fis.close();
}
}
return arrayList;
}
/**
* 对Excel文件进行判断
* @param File file Excel文件
* @param sheetNames Sheet的名字
* @return boolean 是否是真文件 true 真　　false 加
*/
public boolean isFile(File file, String[] sheetNames) throws Exception
{
//记录日志
log.debug("enter getCounts()");
//参数判断
if (file == null || !file.exists() || sheetNames == null || sheetNames.length == 0)
{
log.error("param is not valid");
throw new IllegalArgumentException("param is not valid");
}
//定义流变量
POIFSFileSystem fs = null;
FileInputStream fis = null;
HSSFWorkbook hssfWorkBook = null;
boolean isValid = false;
//默认的sheet个数为0
int counts = 0;
try
{
//读入文件
fis = new FileInputStream(file);
fs = new POIFSFileSystem(fis);
hssfWorkBook = new HSSFWorkbook(fs);
counts = hssfWorkBook.getNumberOfSheets();
//对Sheet的个数进行判断
if (counts != sheetNames.length)
{
return isValid;
}
String tempSheetName = null;
//再判断sheet名
for (int i = 0; i < counts; i++)
{
tempSheetName = hssfWorkBook.getSheetName(i);
if (!tempSheetName.equals(sheetNames[i].trim()))
{
return isValid;
}
}
isValid = true;
}
catch(IOException ioe)
{
log.error(ioe.toString());
return isValid;
}
catch(Exception e)
{
log.error(e.toString());
throw new Exception();
}
finally
{
try
{
fis.close();
}
catch(Exception e)
{
log.error(e.toString());
return isValid;
}
}
return isValid;
}
//测试
public static void main(String[] args)
{
POIUtil poiUtil = new POIUtil();
ArrayList list = null;
try
{
list=poiUtil.readData("D:\\task模板.xls",0,0,2,"@@@",3);
}
catch(IOException ioe)
{
}
if (list == null)
{
System.out.println("======================");
}
else
{
String temp = null;
String[] str = new String[2];
String[] tempStr = new String[2];
for (int i = 0; i < list.size(); i++)
{
temp = (String)list.get(i);
System.out.println("========row" + temp);
str = temp.split("@@@");
for (int j = 0; j < str.length; j++)
{
tempStr = str[j].split("@");
System.out.println(tempStr.length);
System.out.println(tempStr[0]);
System.out.println(tempStr[1]);
}
}
}
}
}

comeonliming

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
操作ｅｘｃｅｌ文件，从文件中读取数据。

java 代码 import org.apache.log4j.*; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import java.util.*; ...
复制链接

扫一扫

专栏目录