Java读取Excel练习日记(POI)

2016-10-30  邓智群 

学习是一个终身的过程,不论权贵,长相,地位。

又到一年年末时,各位屌丝是不是都有蠢蠢欲动的想法了,所以更应该努力一搏,但是学习的过程日记不要忘记分享给别人哦,分享是一种美德。。

最近本刁在看之前写过的自动化代码,顺便巩固一下基础知识,以下是java读取excel的一段,分享给大家


public class Excel {  
private Workbook workbook;
private Sheet sheet;
private int firstrow=-1;
private int lastrow=-1;
private int firstcell=-1;
private int lastcell=-1;
//初始化变量
private void init(){
if(workbook==null){
throw new RuntimeException("");
}
if(sheet == null){
sheet = workbook.getSheetAt(0);
}
if(firstrow<0){
firstrow = sheet.getFirstRowNum(); 
}
if(lastrow<0){
lastrow = sheet.getLastRowNum()+1;
}
if(firstcell<0 ){
firstcell=0;
}
if(lastcell<0){
Row row = sheet.getRow(firstrow);
lastcell = getMaxCellIndex();
row=null;
}
}

//获取excel数据
public List parseData(){
init();
Row row = null;
Cell cell = null;
int nullCell = 0;
String []aryCell=null;
List listrow = new ArrayList();
for(int i=firstrow;i<lastrow;i++){
row = sheet.getRow(i);
aryCell = new String[lastcell-firstcell];
if(row==null){


}else{
for(int j=firstcell;j<lastcell;j++){
cell = row.getCell(j);
if(cell == null){
continue;
}
aryCell[j] = getCellValue(cell);
if(aryCell[j]==null){
nullCell++;
}
cell = null;
}
}
listrow.add(aryCell);
aryCell= null;
row = null;
}
return listrow;
}

//处理单元格格式
@SuppressWarnings("deprecation")
public static String getCellValue(Cell cell){
if(cell.getCellType()==cell.CELL_TYPE_STRING){
return cell.getStringCellValue().trim();
}
else if(cell.getCellType()==cell.CELL_TYPE_BLANK){
return "";
}
else if(DateUtil.isCellDateFormatted(cell)){
return Date2String(cell.getDateCellValue());
}
else{
if(Double.isNaN(cell.getNumericCellValue())) {
return cell.getStringCellValue();
                }
                else {
                NumberFormat fmt = DecimalFormat.getInstance();
                fmt.setGroupingUsed(false);
                String value = new BigDecimal(cell.getNumericCellValue()*100).toString();
            if (new BigDecimal(cell.getNumericCellValue()*100).toString().indexOf(".") != -1) {
String pattern = "#0.000000000";
((DecimalFormat) fmt).applyPattern(pattern);
}
            else if (new BigDecimal(cell.getNumericCellValue()).toString().indexOf(".") != -1){
String pattern = "#0.00";
((DecimalFormat) fmt).applyPattern(pattern);
            }
                
            return fmt.format(cell.getNumericCellValue());                
                }
}

}


//获取所有行中最大列索引号
public int getMaxCellIndex(){
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
int maxcellindex =0;
for(int i=firstRow;i<lastRow;i++){
Row row = sheet.getRow(i);
if(row==null){
if(row.getLastCellNum()>maxcellindex){
maxcellindex=row.getLastCellNum();
}
}
}
return maxcellindex;
}
//日期转化为字符串
public static String Date2String(java.util.Date date) {
       SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
       return simpleDateFormat.format(date);
     }
 
//get&&set
public int getFirstRow(){
return firstrow;
}
 
public void setFirstRow(int firstrow){
this.firstrow = firstrow;
}
 
public int getFirstCell(){
return firstcell;
}
 
public void setFirstCell(int firstcell){
this.firstcell = firstcell;
}
 
public int getLastRow(){
return lastrow;
}
 
public void setLastRow(int lastrow){
this.lastrow = lastrow;
}
 
public int getLastCell(){
return lastcell;
}
 
public void setLastCell(int lastcell){
this.lastcell = lastcell;
}
 
public void setSheet(int atsheet){
this.sheet=this.workbook.getSheetAt(atsheet);
}
 
public void setWorkBook(File filename){
try{
this.workbook = new XSSFWorkbook(new FileInputStream(filename));
}catch (IOException ex){
throw new RuntimeException(ex);
}
}
}  

361°/3612 人阅读/0 条评论 发表评论

登录 后发表评论