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: 该类用于操作excel文件,从文件中读取数据。</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]);
- }
- }
- }
- }
- }