excel导入

poi导入excel文件

导入时，根据xls和xlsx导入不同的类，excel文件的读取分解：结构为sheet，包含着行，行里有列，在列中读取数据，提取出来放在map中，最后再循环遍历，很容易理解。

package com.corp.project;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;


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;


import aos.framework.core.utils.AOSCons.IS;


public class Poi {

public static void main(String[] args) {
String cellData=null;
List<Map<String,String>> list = null;
String path="D:\\test.xlsx";
String columns[]={"name","age","score"};
Workbook workbook = readExcel(path);
if(workbook!=null){
list = new ArrayList<Map<String,String>>();
//获取sheet
Sheet sheet = workbook.getSheetAt(0);
//获取行数 
int Rows = sheet.getPhysicalNumberOfRows();
Row row1 = sheet.getRow(0);//第一行
int colnum = row1.getPhysicalNumberOfCells();//最大列数

for (int i = 0; i < Rows; i++) {
LinkedHashMap<String, String> lsh = new LinkedHashMap<String,String>();
Row row = sheet.getRow(i);//从第一行开始

if(row!=null){
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
lsh.put(columns[j], cellData);
}
}else{
                    break;
                }
list.add(lsh);
}
}
for (Map<String,String> map : list) {
            for (Entry<String,String> entry : map.entrySet()) {
                System.out.print(entry.getKey()+":"+entry.getValue()+",");
            }
            System.out.println();
        }
}

@SuppressWarnings("resource")
public static Workbook readExcel(String Path){
Workbook workbook=null;
if(Path==null){
return null;
}

String extString = Path.substring(Path.lastIndexOf("."));
InputStream is=null;
try {
is = new FileInputStream(Path);
if(".xls".equals(extString)){
return workbook=new HSSFWorkbook(is);

}else if (".xlsx".equals(extString)) {
return workbook=new XSSFWorkbook(is);

}else{
return workbook=null;
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return workbook;
}



public static Object getCellFormatValue(Cell cell){//判断格式类型  number date String
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC:{
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            }
            case Cell.CELL_TYPE_FORMULA:{
                //判断cell是否为日期格式
                if(DateUtil.isCellDateFormatted(cell)){
                    //转换为日期格式YYYY-mm-dd
                    cellValue = cell.getDateCellValue();
                }else{
                    //数字
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            case Cell.CELL_TYPE_STRING:{
                cellValue = cell.getRichStringCellValue().getString();
                break;
            }
            default:
                cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }
}