package test;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.NotImplementedException;
import jxl.Cell;
import jxl.CellType;
import jxl.FormulaCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class Test1 {
public static void main(String[] args) {
/*
* JXL读取excel将单元格中存在错误的单元格进行过滤
*/
//程序中对公式单元格的类型判断并输出内容,当单元格的公式存在错误的场合使用POI获取workbook
//就会发生异常,以下注释的代码可以检查存在的错误并给以处理,但这种处理不能修改公式,
//而是将公式去掉只保留单元格内容,这样在判断公式单元格的时候输出类型就没差别了。
/*
try {
WritableWorkbook workbook = Workbook.createWorkbook(new File("E:/test1.xls"),
Workbook.getWorkbook(new File("E:/test.xls")));
WritableSheet[] sheets =workbook.getSheets();
for(int ix=0; ix<sheets.length; ix++){
for(int iy=0; iy<sheets[ix].getRows();iy++){
for(int iz=0; iz<sheets[ix].getColumns();iz++){
WritableCell wcell = sheets[ix].getWritableCell(iz,iy);
if(wcell.getType() == CellType.NUMBER_FORMULA || wcell.getType() == CellType.STRING_FORMULA || wcell.getType() == CellType.BOOLEAN_FORMULA
|| wcell.getType() == CellType.DATE_FORMULA || wcell.getType() == CellType.FORMULA_ERROR){
Label nCell = new Label(iz, iy, wcell.getContents(), wcell.getCellFormat());
sheets[ix].addCell(nCell);
}
}
}
}
workbook.write();
workbook.close();
} catch (Exception e) {
System.out.println("JXL获取workbook报错!" + e.toString());
}
*/
FileInputStream fileIn = null;
HSSFWorkbook hssfwb = null;
try {// POI获取workbook
fileIn = new FileInputStream("E:/test.xls");
hssfwb = new HSSFWorkbook(fileIn, true);
} catch (Exception e) {
System.out.println("POI获取workbook报错!" + e.toString());
} finally {
if (fileIn != null) {
try {
fileIn.close();
} catch (IOException e) {
System.out.println(e.toString());
}
}
}
try {
// JXL获取workbook
Workbook wb = Workbook.getWorkbook(new File("E:/test.xls"));
// 取得sheet数
int sheetNum = wb.getNumberOfSheets();
for(int i=0; i<sheetNum; i++){
System.out.println("第 "+ (i+1)+ " 个SHEET!");
printCellFormatValue(wb.getSheet(i),hssfwb.getSheetAt(i));
}
} catch (Exception e) {
System.out.println("JXL获取workbook报错!" + e.toString());
}
}
/**
* 通过jxl、poi输出excel单元格内容
*/
public static void printCellFormatValue(Sheet sheet,HSSFSheet sheetPOI){
for(int i=0; i<sheet.getRows(); i++){
Cell[] cells = sheet.getRow(i);
for(int j=0; j<cells.length; j++){
// 判断单元格是否为公式类型
if (cells[j] instanceof FormulaCell) {
// 在前面处理过公式,故hcell肯定不为null
HSSFCell hcell = sheetPOI.getRow(i).getCell(j);
// POI计算公式,得出结果
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheetPOI.getWorkbook());
try{
evaluator.setCurrentRow(sheetPOI.getRow(i));
int eva = evaluator.evaluateFormulaCell(hcell);
switch (eva) {
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
System.out.println("第" + (i+1) + "行,第" + (j+1) + "列单元格值为:"+ hcell.getBooleanCellValue() + ",类型为:CELL_TYPE_BOOLEAN");
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
NumberCell numCell = (NumberCell) cells[j];
System.out.println("第" + (i+1) + "行,第" + (j+1) + "列单元格值为:"+ numCell.getNumberFormat().format(hcell.getNumericCellValue()) + ",类型为:CELL_TYPE_NUMERIC");
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
System.out.println("第" + (i+1) + "行,第" + (j+1) + "列单元格值为:"+ hcell.getStringCellValue() + ",类型为:CELL_TYPE_STRING");
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
String value = String.valueOf(hcell.getNumericCellValue());
System.out.println("第" + (i+1) + "行,第" + (j+1) + "列单元格值为:"+ value + ",类型为:CELL_TYPE_FORMULA");
//如果获取的数据值为非法值,则转换为获取字符串
if(value.equals("NaN")){
value = hcell.getRichStringCellValue().toString();
System.out.println("第" + (i+1) + "行,第" + (j+1) + "列单元格数据值非法值,值为:"+ value + ",类型为:CELL_TYPE_FORMULA");
}
break;
}
}catch(NotImplementedException ee){
System.out.println("第" + (i+1) + "行,第" + (j+1) + "列单元格数据值非法值,类型为:CELL_TYPE_FORMULA");
}
}else{
System.out.println("第" + (i+1) + "行,第" + (j+1) + "列单元格值:"+ cells[j].getContents());
}
}
}
}
}