POI读写excel

前段时间做项目用到POI解析excel并且往excel中写数据，一直没空下时间来写自己的实现，现在把解析和读取的方法共享下：

读取excel时：POI提供两种API一种解析xls格式的一种解析xlsx格式的我分别来说：

package com.test.ajing.util;
/**
 * 读取excel demo
 */
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;


import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.hssf.record.DVRecord;
import org.apache.poi.hssf.record.aggregates.DataValidityTable;
import org.apache.poi.hssf.record.formula.Ptg;
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.formula.Formula;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class PoiUtil {


public static void main(String[] args)throws Exception {
// readExcelXlsx("f:\\mergion.xlsx",0);
readExcelXsl("f:\\ceshi.xls",0);
}


/******************read xlsx excel start*******************************/
/**
* 读取excel .xlsx扩展名的
* @param fileName 文件的绝对路径
* @param sheetIndex sheet索引
*/
public static void readExcelXlsx(String fileName,int sheetIndex){
try {
 
//创建workbook
XSSFWorkbook workbook=new XSSFWorkbook(fileName);
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
//获取表格中实际的最大行数
int lastRowNum = sheet.getLastRowNum();
//获取表格中实际的最大列数
int maxCols= getXlsxMaxColum(sheet);
System.out.println("numer==="+sheet.getNumMergedRegions());
for(int i=0;i<lastRowNum;i++){
for(int j=0;j<maxCols;j++){
XSSFCell cell = sheet.getRow(i).getCell(j);
if (cell==null) {
// System.out.println("单元格为空===");
}else if(xlsxMergedType(cell, getXlsxMergedRange(sheet))==1){
// System.out.println("合并单元格=第一个"+cell.getReference()+";x="+cell.getRowIndex());
// System.out.println("y="+cell.getReference()+";x="+cell.getRowIndex()
// +";type="+cell.getCellType()/*+";data="+cell.getDateCellValue()+
// ";formula="+cell.getCellFormula()+";type="+cell.getCellType()*/
// );
}else if (xlsxMergedType(cell, getXlsxMergedRange(sheet))==2) {
// System.out.println("其它合并单元格不必记录");
}else{

if (cell.getCellType()==XSSFCell.CELL_TYPE_BLANK) {//此代表不为空的单元格但是有颜色
// System.out.println("y==="+cell.getReference()+";x="+cell.getRowIndex());
}else if (cell.getCellType()==XSSFCell.CELL_TYPE_FORMULA) {//读取规则单元格
// System.out.println("CELL_TYPE_FORMULA=="+cell.getCellFormula());
}else if (cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC) {//读取数字单元格
// System.out.println("x="+cell.getRowIndex()+";y="+cell.getReference());
// System.out.println("x="+cell.getRowIndex()+";y="+cell.getReference());
// System.out.println("CELL_TYPE_NUMERIC=="+cell.getNumericCellValue());
// System.out.println("CELL_TYPE_NUMERIC=="+cell.get);

}else if (cell.getCellType()==XSSFCell.CELL_TYPE_STRING) {
// System.out.println("CELL_TYPE_STRING");
}else {
System.out.println("y==="+cell.getReference()+";x="+cell.getRowIndex());
System.out.println("cellType== "+cell.getCellType());
}
}
}
}
 
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

/**
* 单独解析下拉列表框和取值范围的单元格
* @param sheet
*/
public static void parseDropDownList(XSSFSheet sheet){
List<XSSFDataValidation> dataValidations = sheet.getDataValidations();

for(XSSFDataValidation data:dataValidations){
//如果数据类型是ANY类型则是限制范围的例如[1,20]此时取规则是Formula1和Formula2都取
if (data.getValidationConstraint().getValidationType()==ValidationType.INTEGER) {
// System.out.println("取值范围1="+data.getValidationConstraint().getFormula1());
// System.out.println("取值范围2="+data.getValidationConstraint().getFormula2());
}else if (data.getValidationConstraint().getValidationType()==ValidationType.LIST) {
//是下拉列表框一次全取出来执行Formula1即可
// System.out.println("下拉列表框的值="+data.getValidationConstraint().getFormula1());
}
}

}

/**
* 取得sheet中最大列数，
* @param sheet
* @return
*/
public static int getXlsxMaxColum(XSSFSheet sheet){
int max=0;
//得到最大行数循环行数
int maxRow = sheet.getLastRowNum();
for(int i=0;i<maxRow;i++){
if (max<sheet.getRow(i).getLastCellNum()) {
max=sheet.getRow(i).getLastCellNum();
}
}
return max;
}
/**
* 获取合并单元格集合
* @param sheet
* @return
*/
public static List<CellRangeAddress> getXlsxMergedRange(XSSFSheet sheet){
List<CellRangeAddress> addresses = new ArrayList<CellRangeAddress>();
int sum=sheet.getNumMergedRegions();
if (sum>0) {
for(int i=0;i<sum;i++){
addresses.add(sheet.getMergedRegion(i));
}
}
return addresses;
}

/**
* 判断此单元格类型 如果是0为普通单元格，为1为合并单元格第一个，如果为2是其它合并单元格不必记录
* @param cell 要判断的单元格
* @param mergedList 合并单元格集合
*/
public static int xlsxMergedType(XSSFCell cell,List<CellRangeAddress> mergedList){
int cellRow=cell.getRowIndex();
int cellCol=cell.getColumnIndex();
int type=0;
for(CellRangeAddress address : mergedList){
if (cellRow>=address.getFirstRow()&&cellRow<=address.getLastRow()
&&cellCol>=address.getFirstColumn()&&cellCol<=address.getLastColumn()) {
if (cellRow==address.getFirstRow()&&cellCol==address.getFirstColumn()) {
type=1;
}else{
type= 2;
}
}else{
continue;
}
}
return type;
}
/******************read xlsx excel end*******************************/




/*****
* 
* 以下代码为读取xls格式的excel
* 
* 
*/
/******************read xls excel start*******************************/
public static void readExcelXsl(String fileName,int sheetIndex){

//得到workbook
HSSFWorkbook workbook;
try {
workbook = new HSSFWorkbook(new FileInputStream(fileName));
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
processInternalSheet(sheet);
// getXlsType(null,sheet);
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
for(;firstRow<lastRow;firstRow++){
HSSFRow hssfRow = sheet.getRow(firstRow);
if (hssfRow!=null) {
for(int i=0;i<getXlsMaxColum(sheet);i++){
HSSFCell cell = hssfRow.getCell(i);
if (cell==null) {
// System.out.println("此单元格为空");
}else if (getXlsType(cell,sheet)==0) {
if (cell.getCellType()==Cell.CELL_TYPE_NUMERIC) {

}else if (cell.getCellType()==Cell.CELL_TYPE_FORMULA) {

}else if (cell.getCellType()==Cell.CELL_TYPE_STRING) {

}
}else if (getXlsType(cell,sheet)==1) {
// System.out.println("合并单元格存取值：row="+cell.getRowIndex()+",colum="+cell.getColumnIndex());
}
}
}

}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

}


public static int getRowColun(HSSFCell cell,HSSFSheet sheet){


int sumMerged = sheet.getNumMergedRegions();
int resultType=0;
if (cell==null||sheet==null) {
return resultType;
}
for(int i=0;i<sumMerged;i++){
CellRangeAddress address = sheet.getMergedRegion(i);
if (address.getFirstRow()==cell.getRowIndex()&&address.getFirstColumn()==cell.getColumnIndex()) {
// resultType=(address.getLastRow()-address.getFirstRow())==0?0：;
}
}
return resultType;
}

/**
* 判断单元格是否为合并单元格中的一个 返回值：如果为0说明是未合并单元格
* 如果是1说明是应该存取的第一个单元格，如果是2就是其它的合并单元格不需要存储
* @param cell
* @param sheet
* @return
*/
public static int getXlsType(HSSFCell cell,HSSFSheet sheet){

int sumMerged = sheet.getNumMergedRegions();
int resultType=0;
if (cell==null||sheet==null) {
return resultType;
}
for(int i=0;i<sumMerged;i++){
CellRangeAddress address = sheet.getMergedRegion(i);
if (address.getFirstRow()==cell.getRowIndex()&&address.getFirstColumn()==cell.getColumnIndex()) {
resultType=1;
}else if (address.getFirstRow()<cell.getRowIndex()&&address.getFirstColumn()<cell.getColumnIndex()
&&address.getLastRow()>=cell.getRowIndex()&&address.getLastColumn()>=cell.getColumnIndex()) {
resultType=2;
}
// System.out.println("fristrow=="+address.getFirstRow()+";fristcolum="+address.getFirstColumn()+";lastrow="
// +address.getLastRow()+";lastcolum="+address.getLastColumn());
}
return resultType;
}

/**
* 获得最大列
* @param sheet
* @return
*/
public static int getXlsMaxColum(HSSFSheet sheet){

int maxColum=0;
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
for(;firstRow<lastRow;firstRow++){
HSSFRow hssfRow = sheet.getRow(firstRow);
if (hssfRow!=null) {
if (maxColum<hssfRow.getLastCellNum()) {
maxColum=hssfRow.getLastCellNum();
}else{
continue;
}
}else{
continue;
}

}
// System.out.println("maxColum==="+maxColum);
return maxColum;

}

/**
* 读取下拉列表框和数据范围的单元格单独处理
* @param sheet
* @throws UnsupportedEncodingException 
*/
public static void processInternalSheet(HSSFSheet sheet) throws UnsupportedEncodingException{
try {

//得到解析下拉列表框的和限制范围规则的的InternalSheet
InternalSheet internalSheet = getFieldObject(sheet,"_sheet");
DataValidityTable tables = internalSheet.getOrCreateDataValidityTable();
//得到记录集合（是规则类型）单元格的规则是相同的为一个
List<DVRecord> dvRecords  = getFieldObject(tables,"_validationList");
// System.out.println("dvRecords=="+dvRecords.size());
for(DVRecord record:dvRecords){
CellRangeAddressList addresses = record.getCellRangeAddress();
CellRangeAddress[] CellRangeAddress=addresses.getCellRangeAddresses();
for(int i=0;i<CellRangeAddress.length;i++){
// System.out.println("record.getDataType()==="+record.getDataType());
CellRangeAddress address = CellRangeAddress[i];
// System.out.println("cell firstrow=="+address.getFirstRow()+";firstcolum="+address.getFirstColumn()
// +";lastrow="+address.getLastRow()+";lastcolums="+address.getLastColumn()
// );

}
if (record.getDataType()==ValidationType.INTEGER) {
Formula formula1 = (Formula)getFieldObject(record,"_formula1");
Formula formula2 = (Formula)getFieldObject(record,"_formula2");
// System.out.println("toStringFormula1=="+toStringFormula(formula1));
// System.out.println("toStringFormula2=="+toStringFormula(formula2));
}else if (record.getDataType()==ValidationType.LIST) {
Formula formula1 = (Formula)getFieldObject(record,"_formula1");

System.out.println("toStringFormula=="+toStringFormula(formula1));
String[] t = toStringFormula(formula1).split("");
for(String str:t){
System.out.println("length=="+str);
}
}
}
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch blockl
e.printStackTrace();
}
}
/**
* 得到 某个类上的某个属性
* @param obj 传入的对象
* @param fieldName  对象身上的字段名
* @return
* @throws SecurityException
* @throws NoSuchFieldException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
@SuppressWarnings("unchecked")
public static <T> T  getFieldObject(Object obj,String fieldName)throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException{
Field field = obj.getClass().getDeclaredField(fieldName);
field.setAccessible(true);
return (T)field.get(obj);
}

/**
* 格式化规则
* @param f
* @return
* @throws UnsupportedEncodingException 
*/
public static String toStringFormula(Formula f) throws UnsupportedEncodingException {
if (f == null) {
     return null;
   }else{
    StringBuffer xy = new StringBuffer();
    Ptg[] ptgs = f.getTokens();
    for (int i = 0; i < ptgs.length; i++){
//     System.out.println("ptgs[i]=="+
//     new String(ptgs[i].toFormulaString().getBytes("ISO-8859-1"),"GB2312"));
    xy.append(ptgs[i].toFormulaString());
    }
//     System.out.println(xy.toString().split(""));
    return xy.toString();
   }
 }
/******************read xlsx excel end*******************************/
}