workbook需要引入的包_JAVA导入(读取)Excel中的数据（支持xls与xlsx文件）

一、导入jar包

poi-3.7.jar

poi-scratchpad-3.7.jar

poi-examples-3.7.jar

poi-ooxml-3.7.jar

poi-ooxml-schemas-3.7.jar

二、编写工具类ExcelTool.java

package com.test;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.xssf.usermodel.XSSFCell;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.util.Date;

public class ExcelTool {

public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";

public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

public static final String EMPTY = "";

public static final String POINT = ".";

public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

/**

* 获得path的后缀名

* @param path

* @return

*/

public static String getPostfix(String path){

if(path==null || EMPTY.equals(path.trim())){

return EMPTY;

}

if(path.contains(POINT)){

return path.substring(path.lastIndexOf(POINT)+1,path.length());

}

return EMPTY;

}

/**

* 单元格格式

* @param hssfCell

* @return

*/

@SuppressWarnings({ "static-access", "deprecation" })

public static String getHValue(HSSFCell hssfCell){

if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {

return String.valueOf(hssfCell.getBooleanCellValue());

} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {

String cellValue = "";

if(HSSFDateUtil.isCellDateFormatted(hssfCell)){

Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());

cellValue = sdf.format(date);

}else{

DecimalFormat df = new DecimalFormat("#.##");

cellValue = df.format(hssfCell.getNumericCellValue());

String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());

if(strArr.equals("00")){

cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));

}

}

return cellValue;

} else {

return String.valueOf(hssfCell.getStringCellValue());

}

}

/**

* 单元格格式

* @param xssfCell

* @return

*/

public static String getXValue(XSSFCell xssfCell){

if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

return String.valueOf(xssfCell.getBooleanCellValue());

} else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

String cellValue = "";

if(XSSFDateUtil.isCellDateFormatted(xssfCell)){

Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());

cellValue = sdf.format(date);

}else{

DecimalFormat df = new DecimalFormat("#.##");

cellValue = df.format(xssfCell.getNumericCellValue());

String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());

if(strArr.equals("00")){

cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));

}

}

return cellValue;

} else {

return String.valueOf(xssfCell.getStringCellValue());

}

}

/**

* 自定义xssf日期工具类

* @author lp

*

*/

static class XSSFDateUtil extends DateUtil {

protected static int absoluteDay(Calendar cal, boolean use1904windowing) {

return DateUtil.absoluteDay(cal, use1904windowing);

}

}

}

三、编写调用类ExcelUtils.java

package com.test;

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.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

import java.util.ArrayList;

import java.util.List;

public class ExcelUtils {

public static int totalRows; //sheet中总行数

public static int totalCells; //每一行总单元格数

/**

* read the Excel .xlsx,.xls

* @param file jsp中的上传文件

* @return

* @throws IOException

*/

public static List> readExcel(File file) throws IOException {

if(file==null){

return null;

}else{

String postfix = ExcelTool.getPostfix(file.getName());

if(!ExcelTool.EMPTY.equals(postfix)){

if(ExcelTool.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){

return readXls(file);

}else if(ExcelTool.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){

return readXlsx(file);

}else{

return null;

}

}

}

return null;

}

/**

* read the Excel 2010 .xlsx

* @param file

* @return

* @throws IOException

*/

@SuppressWarnings("deprecation")

public static List> readXlsx(File file){

List> list = new ArrayList>();

// IO流读取文件

InputStream input = null;

XSSFWorkbook wb = null;

ArrayList rowList = null;

try {

input = new FileInputStream(file);

// 创建文档

wb = new XSSFWorkbook(input);

//读取sheet(页)

for(int numSheet=0;numSheet

XSSFSheet xssfSheet = wb.getSheetAt(numSheet);

if(xssfSheet == null){

continue;

}

totalRows = xssfSheet.getLastRowNum();

//读取Row,从第二行开始

for(int rowNum = 0;rowNum <= totalRows;rowNum++){

XSSFRow xssfRow = xssfSheet.getRow(rowNum);

if(xssfRow!=null){

rowList = new ArrayList();

totalCells = xssfRow.getLastCellNum();

//读取列，从第一列开始

for(int c=0;c<=totalCells+1;c++){

XSSFCell cell = xssfRow.getCell(c);

if(cell==null){

rowList.add(ExcelTool.EMPTY);

continue;

}

rowList.add(ExcelTool.getXValue(cell).trim());

}

list.add(rowList);

}

}

}

return list;

} catch (IOException e) {

e.printStackTrace();

} finally{

try {

input.close();

} catch (IOException e) {

e.printStackTrace();

}

}

return null;

}

/**

* read the Excel 2003-2007 .xls

* @param file

* @return

* @throws IOException

*/

public static List> readXls(File file){

List> list = new ArrayList>();

// IO流读取文件

InputStream input = null;

HSSFWorkbook wb = null;

ArrayList rowList = null;

try {

input = new FileInputStream(file);

// 创建文档

wb = new HSSFWorkbook(input);

//读取sheet(页)

for(int numSheet=0;numSheet

HSSFSheet hssfSheet = wb.getSheetAt(numSheet);

if(hssfSheet == null){

continue;

}

totalRows = hssfSheet.getLastRowNum();

//读取Row,从第二行开始

for(int rowNum = 0;rowNum <= totalRows;rowNum++){

HSSFRow hssfRow = hssfSheet.getRow(rowNum);

if(hssfRow!=null){

rowList = new ArrayList();

totalCells = hssfRow.getLastCellNum();

//读取列，从第一列开始

for(short c=0;c<=totalCells+1;c++){

HSSFCell cell = hssfRow.getCell(c);

if(cell==null){

rowList.add(ExcelTool.EMPTY);

continue;

}

rowList.add(ExcelTool.getHValue(cell).trim());

}

list.add(rowList);

}

}

}

return list;

} catch (IOException e) {

e.printStackTrace();

} finally{

try {

input.close();

} catch (IOException e) {

e.printStackTrace();

}

}

return null;

}

}

四、调用方法

1、本地调用方式

public static void main(String[] args) throws Exception {

File file=new File("E:\\excel文档测试\\电话号码.xlsx");

List> list = ExcelUtils.readExcel(file);

System.out.println(list.size());

}

2、JavaWeb调用方式(只需把上面的方法中File类型改成MultipartFile类型,对应的方法进行修改即可)

@RequestMapping(value = "o_import.do",method = RequestMethod.POST)

public String importXls(

@RequestParam(value = "Filedata", required = false) MultipartFile file){

ExcelUtil excelUtil = new ExcelUtil();

try {

//list为excel数据集合

List> list = ExcelUtils.readExcel(file);

} catch (Exception e) {

e.printStackTrace();

}

}