package com.b2bjy.crm.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class POIExcelUtil
{
/** *//** 总行数 */
private int totalRows = 0;
/** *//** 总列数 */
private int totalCells = 0;
/** *//** 构造方法 */
public POIExcelUtil()
{}
/** *//**
*
* - Description:[根据文件名读取excel文件]
* - Created by [Huyvanpull] [Jan 20, 2010]
* - Midified by [modifier] [modified time]
*
*
* @param fileName
* @return
* @throws Exception
*/
public ArrayList read(String fileName)
{
ArrayList dataLst = new ArrayList();
/** *//** 检查文件名是否为空或者是否是Excel格式的文件 */
if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$"))
{
return dataLst;
}
boolean isExcel2003 = true;
/** *//** 对文件的合法性进行验证 */
if (fileName.matches("^.+\\.(?i)(xlsx)$"))
{
isExcel2003 = false;
}
/** *//** 检查文件是否存在 */
File file = new File(fileName);
if (file == null || !file.exists())
{
return dataLst;
}
try
{
/** *//** 调用本类提供的根据流读取的方法 */
dataLst = read(new FileInputStream(file), isExcel2003);
}
catch (Exception ex)
{
ex.printStackTrace();
}
/** *//** 返回最后读取的结果 */
return dataLst;
}
/** *//**
*
* - Description:[根据流读取Excel文件]
* - Created by [Huyvanpull] [Jan 20, 2010]
* - Midified by [modifier] [modified time]
*
*
* @param inputStream
* @param isExcel2003
* @return
*/
public ArrayList read(InputStream inputStream,
boolean isExcel2003)
{
ArrayList dataLst = null;
try
{
/** *//** 根据版本选择创建Workbook的方式 */
Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream)
: new XSSFWorkbook(inputStream);
dataLst = read(wb);
}
catch (IOException e)
{
e.printStackTrace();
}
return dataLst;
}
/** *//**
*
* - Description:[得到总行数]
* - Created by [Huyvanpull] [Jan 20, 2010]
* - Midified by [modifier] [modified time]
*
*
* @return
*/
public int getTotalRows()
{
return totalRows;
}
/** *//**
*
* - Description:[得到总列数]
* - Created by [Huyvanpull] [Jan 20, 2010]
* - Midified by [modifier] [modified time]
*
*
* @return
*/
public int getTotalCells()
{
return totalCells;
}
/** *//**
*
* - Description:[读取数据]
* - Created by [Huyvanpull] [Jan 20, 2010]
* - Midified by [modifier] [modified time]
*
*
* @param wb
* @return
*/
private ArrayList read(Workbook wb)
{
ArrayList rowLst = new ArrayList();
/** *//** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
this.totalRows = sheet.getPhysicalNumberOfRows();
if (this.totalRows >= 1 && sheet.getRow(0) != null)
{
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** *//** 循环Excel的行 */
for (int r = 0; r < this.totalRows; r++)
{
Row row = sheet.getRow(r);
if (row == null)
{
continue;
}
/** *//** 循环Excel的列 */
for (short c = 0; c < 1; c++)
{
Cell cell = row.getCell(c);
String cellValue = "";
if (cell == null)
{
rowLst.add(cellValue);
continue;
}
/** *//** 处理数字型的,自动去零 */
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType())
{
cellValue = getRightStr(cell.getNumericCellValue() + "");
}
/** *//** 处理字符串型 */
else if (Cell.CELL_TYPE_STRING == cell.getCellType())
{
cellValue = cell.getStringCellValue();
}
/** *//** 处理布尔型 */
else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType())
{
cellValue = cell.getBooleanCellValue() + "";
}
/** *//** 其它的,非以上几种数据类型 */
else
{
cellValue = cell.toString() + "";
}
rowLst.add(cellValue);
}
}
return rowLst;
}
/** *//**
*
* - Description:[正确地处理整数后自动加零的情况]
* - Created by [Huyvanpull] [Jan 20, 2010]
* - Midified by [modifier] [modified time]
*
*
* @param sNum
* @return
*/
private String getRightStr(String sNum)
{
DecimalFormat decimalFormat = new DecimalFormat("#.000000");
String resultStr = decimalFormat.format(new Double(sNum));
if (resultStr.matches("^[-+]?\\d+\\.[0]+$"))
{
resultStr = resultStr.substring(0, resultStr.indexOf("."));
}
return resultStr;
}
/** *//**
*
* - Description:[测试main方法]
* - Created by [Huyvanpull] [Jan 20, 2010]
* - Midified by [modifier] [modified time]
*
*
* @param args
* @throws Exception
*/
public String getPhones(){
ArrayList dataLst = new POIExcelUtil().read("D:\\b.xls");
StringBuffer rowData = new StringBuffer();
for (int i = 0; i < dataLst.size()-1; i++) {
rowData.append(dataLst.get(i)).append(",");
}
rowData.append(dataLst.get(dataLst.size()-1));
if (rowData.length() > 0)
{
System.out.println(rowData);
}
return rowData.toString();
}
public static void main(String[] args) throws Exception
{
System.out.println(new POIExcelUtil().getPhones());
}
}
