java解析excel文件详解,java解析Excel文件

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.List;

import com.supermap.dataservice.datapojo.entity.user.UserExcel;

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;

public class UserExcelUtils {

public static List putUserExcel() {

Workbook wb =null;

Sheet sheet = null;

Row row = null;

List list = null;

String cellData = null;

String filePath = "C:\\Users\\69008\\Desktop\\user.xlsx";

wb = readExcel(filePath);

if(wb != null){

//用来存放表中数据

list = new ArrayList();

//获取第一个sheet

sheet = wb.getSheetAt(0);

//获取最大行数

int rownum = sheet.getPhysicalNumberOfRows();

//获取第一行

row = sheet.getRow(0);

boolean b = fristCellIsRight(row);

if(!b){

return null;

}

//获取最大列数

int colnum = row.getPhysicalNumberOfCells();

for (int i = 1; i

UserExcel userExcel = new UserExcel();

row = sheet.getRow(i);

if(row !=null){

userExcel.setProvince((String) getCellFormatValue(row.getCell(1)));

userExcel.setCity((String) getCellFormatValue(row.getCell(2)));

userExcel.setArea((String) getCellFormatValue(row.getCell(3)));

userExcel.setAdministrativeCode((String) getCellFormatValue(row.getCell(4)));

userExcel.setUsername((String) getCellFormatValue(row.getCell(5)));

userExcel.setMobile(((String) getCellFormatValue(row.getCell(6))).trim());

userExcel.setAddress((String) getCellFormatValue(row.getCell(7)));

userExcel.setType((String) getCellFormatValue(row.getCell(8)));

userExcel.setPlanting((String) getCellFormatValue(row.getCell(9)));

userExcel.setExperttype((String) getCellFormatValue(row.getCell(10)));

}else{

break;

}

list.add(userExcel);

}

}

//遍历解析出来的list

for (UserExcel userExcel : list) {

System.out.println(userExcel);

System.out.println();

}

return list;

}

//读取excel

public static Workbook readExcel(String filePath){

Workbook wb = null;

if(filePath==null){

return null;

}

String extString = filePath.substring(filePath.lastIndexOf("."));

InputStream is = null;

try {

is = new FileInputStream(filePath);

if(".xls".equals(extString)){

return wb = new HSSFWorkbook(is);

}else if(".xlsx".equals(extString)){

return wb = new XSSFWorkbook(is);

}else{

return wb = null;

}

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

return wb;

}

public static Object getCellFormatValue(Cell cell){

Object cellValue = null;

if(cell!=null){

//判断cell类型

switch(cell.getCellType()){

case Cell.CELL_TYPE_NUMERIC:{

if (DateUtil.isCellDateFormatted(cell)) {

cellValue = String.valueOf(cell.getDateCellValue());

} else {

cell.setCellType(Cell.CELL_TYPE_STRING);

String temp = cell.getStringCellValue();

// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串

if (temp.indexOf(".") > -1) {

cellValue = String.valueOf(new Double(temp)).trim();

} else {

cellValue = temp.trim();

}

}

break;

}

case Cell.CELL_TYPE_FORMULA:{

//判断cell是否为日期格式

if(DateUtil.isCellDateFormatted(cell)){

//转换为日期格式YYYY-mm-dd

cellValue = cell.getDateCellValue();

}else{

//数字

DecimalFormat format = new DecimalFormat("#");

cellValue = String.valueOf(cell.getNumericCellValue()).trim();

}

break;

}

case Cell.CELL_TYPE_STRING:{

cellValue = cell.getRichStringCellValue().getString().trim();

break;

}

default:

cellValue = "";

}

}else{

cellValue = "";

}

return cellValue;

}

public static boolean fristCellIsRight(Row row){

for (int i = 0; i < row.getRowNum(); i++) {

Cell cell = row.getCell(i);

String trim = cell.getStringCellValue().trim();

switch(i)

{

case 0 :

if(!trim.equals("No.")){

return false;

}

break;

case 1 :

if(!trim.equals("省")){

return false;

}

break;

case 2 :

if(!trim.equals("地/州/市")){

return false;

}

break;

case 3 :

if(!trim.equals("区县")){

return false;

}

break;

case 4 :

if(!trim.equals("行政编码")){

return false;

}

break;

case 5 :

if(!trim.equals("姓名")){

return false;

}

break;

case 6 :

if(!trim.equals("手机号")){

return false;

}

break;

case 7 :

if(!trim.equals("地址")){

return false;

}

break;

case 8 :

if(!trim.equals("类型")){

return false;

}

break;

case 9 :

if(!trim.equals("种植情况")){

return false;

}

break;

case 10 :

if(!trim.equals("专家类型")){

return false;

}

break;

}

}

return true;

}

}