package cn.com.css.common.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
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;
public class ExcelParser {
private XSSFWorkbook xssfWorkbook;
private XSSFSheet xssfSheet;
private XSSFRow xssfRow;
private int columnNumbers;
private int totalRows;
/**
* @param args
* @throws Exception
*/
private String getCellFormatValue(XSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 推断当前Cell的Type
switch (cell.getCellType()) {
// 假设当前Cell的Type为NUMERIC
case XSSFCell.CELL_TYPE_NUMERIC: {
BigDecimal big = new BigDecimal(cell.getNumericCellValue());
cellvalue = big.toString();
break;
}
case XSSFCell.CELL_TYPE_FORMULA: {
// 推断当前的cell是否为Date
/*
* if (XSSFDateUtil.isCellDateFormatted(cell)) { //
* 假设是Date类型则，转化为Data格式
*
* //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00 //cellvalue =
* cell.getDateCellValue().toLocaleString();
*
* //方法2：这样子的data格式是不带带时分秒的：2011-10-12 Date date =
* cell.getDateCellValue(); SimpleDateFormat sdf = new
* SimpleDateFormat("yyyy-MM-dd"); cellvalue = sdf.format(date);
*
* } // 假设是纯数字 else { // 取得当前Cell的数值 cellvalue =
* String.valueOf(cell.getNumericCellValue()); }
*/
BigDecimal bigula = new BigDecimal(cell
.getCachedFormulaResultType());
cellvalue = bigula.toString();
break;
}
// 假设当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
/**
* \brief 通过这种方法获得Excel中的列名和一些excel的初始化信息，比方行数和列数
*
* @param is
* @return
* @attention
* @author 涂作权
* @date 2014-5-27
* @note begin modify by null
*/
public String[] getTitle(InputStream is) {
try {
xssfWorkbook = new XSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
}
xssfSheet = xssfWorkbook.getSheetAt(0);
System.out.println("xssfSheet.getSheetName() = " + xssfSheet.getSheetName());
// 获得行数
totalRows = xssfSheet.getLastRowNum();
System.out.println("totalRows = " + totalRows);
xssfRow = xssfSheet.getRow(0);// first line:title
// 获得列数
columnNumbers = xssfRow.getPhysicalNumberOfCells();
System.out.println("columnNumbers = " + columnNumbers);
// 列名的集合
String[] title = new String[columnNumbers];
for (int i = 0; i < columnNumbers; i++) {
title[i] = getCellFormatValue(xssfRow.getCell(i));
}
// 返回的是列名的数组
return title;
}
public Map<Integer, String> readExcelContent(InputStream is) {
getTitle(is);
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
// 正文内容应该从第二行開始,第一行为表头的标题
for (int i = 1; i <= totalRows; i++) {
xssfRow = xssfSheet.getRow(i);
int j = 0;
while (j <= columnNumbers) {
String cell = getCellFormatValue(xssfRow.getCell(j)).trim();
if (cell == "") {
j++;
continue;// cell="null";
}
str += cell + "\t";
j++;
}
str.trim();
content.put(i, str);
str = "";
}
return content;
}
public XSSFWorkbook getXssfWorkbook() {
return xssfWorkbook;
}
public void setXssfWorkbook(XSSFWorkbook xssfWorkbook) {
this.xssfWorkbook = xssfWorkbook;
}
public XSSFSheet getXssfSheet() {
return xssfSheet;
}
public void setXssfSheet(XSSFSheet xssfSheet) {
this.xssfSheet = xssfSheet;
}
public XSSFRow getXssfRow() {
return xssfRow;
}
public void setXssfRow(XSSFRow xssfRow) {
this.xssfRow = xssfRow;
}
public int getColumnNumbers() {
return columnNumbers;
}
public void setColumnNumbers(int columnNumbers) {
this.columnNumbers = columnNumbers;
}
public int getTotalRows() {
return totalRows;
}
public void setTotalRows(int totalRows) {
this.totalRows = totalRows;
}
public static void main(String[] args) throws Exception {
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
ExcelParser ep = new ExcelParser();
InputStream is = new FileInputStream(new File("D:\\产品管理模板.xlsx"));
//String[] titles = ep.getTitle(is);
Map<Integer, String> map = ep.readExcelContent(is);
for (int i = 1; i <= map.size(); i++) {
String row = map.get(i);
String rowArr[] = row.split(" ");
// 假设每行里面有大于3列信息
String cell0 = rowArr[0]; // 产品名
String cell1 = rowArr[1]; // 国家编码
System.out.println(cell0 + "\t" + cell1);
//System.out.println(map.size());
}
is.close();
is = null;
}
}
