Java读取Excel的方法，解析xls、xlsx两种格式（java读取excel表格）

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.Date;

import java.util.HashMap;

import java.util.Map;

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 org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

* 读取Excel

*

* @author zengwendong

*/

public class ReadExcelUtils {

private Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class);

private Workbook wb;

private Sheet sheet;

private Row row;

public ReadExcelUtils(String filepath) {

if(filepath==null){

return;

}

String ext = filepath.substring(filepath.lastIndexOf("."));

try {

InputStream is = new FileInputStream(filepath);

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

wb = new HSSFWorkbook(is);

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

wb = new XSSFWorkbook(is);

}else{

wb=null;

}

} catch (FileNotFoundException e) {

logger.error("FileNotFoundException", e);

} catch (IOException e) {

logger.error("IOException", e);

}

}

/**

* 读取Excel表格表头的内容

*

* @param InputStream

* @return String 表头内容的数组

* @author zengwendong

*/

public String[] readExcelTitle() throws Exception{

if(wb==null){

throw new Exception("Workbook对象为空！");

}

sheet = wb.getSheetAt(0);

row = sheet.getRow(0);

// 标题总列数

int colNum = row.getPhysicalNumberOfCells();

System.out.println("colNum:" + colNum);

String[] title = new String[colNum];

for (int i = 0; i < colNum; i++) {

// title[i] = getStringCellValue(row.getCell((short) i));

title[i] = row.getCell(i).getCellFormula();

}

return title;

}

/**

* 读取Excel数据内容

*

* @param InputStream

* @return Map 包含单元格数据内容的Map对象

* @author zengwendong

*/

public Map> readExcelContent() throws Exception{

if(wb==null){

throw new Exception("Workbook对象为空！");

}

Map> content = new HashMap>();

sheet = wb.getSheetAt(0);

// 得到总行数

int rowNum = sheet.getLastRowNum();

row = sheet.getRow(0);

int colNum = row.getPhysicalNumberOfCells();

// 正文内容应该从第二行开始,第一行为表头的标题

for (int i = 1; i <= rowNum; i++) {

row = sheet.getRow(i);

int j = 0;

Map cellValue = new HashMap();

while (j < colNum) {

Object obj = getCellFormatValue(row.getCell(j));

cellValue.put(j, obj);

j++;

}

content.put(i, cellValue);

}

return content;

}

/**

*

* 根据Cell类型设置数据

*

* @param cell

* @return

* @author zengwendong

*/

private Object getCellFormatValue(Cell cell) {

Object cellvalue = "";

if (cell != null) {

// 判断当前Cell的Type

switch (cell.getCellType()) {

case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC

case Cell.CELL_TYPE_FORMULA: {

// 判断当前的cell是否为Date

if (DateUtil.isCellDateFormatted(cell)) {

// 如果是Date类型则，转化为Data格式

// data格式是带时分秒的：2013-7-10 0:00:00

// cellvalue = cell.getDateCellValue().toLocaleString();

// data格式是不带带时分秒的：2013-7-10

Date date = cell.getDateCellValue();

cellvalue = date;

} else {// 如果是纯数字

// 取得当前Cell的数值

cellvalue = String.valueOf(cell.getNumericCellValue());

}

break;

}

case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING

// 取得当前的Cell字符串

cellvalue = cell.getRichStringCellValue().getString();

break;

default:// 默认的Cell值

cellvalue = "";

}

} else {

cellvalue = "";

}

return cellvalue;

}

public static void main(String[] args) {

try {

String filepath = "F:test.xls";

ReadExcelUtils excelReader = new ReadExcelUtils(filepath);

// 对读取Excel表格标题测试

// String[] title = excelReader.readExcelTitle();

// System.out.println("获得Excel表格的标题:");

// for (String s : title) {

// System.out.print(s + " ");

// }

// 对读取Excel表格内容测试

Map> map = excelReader.readExcelContent();

System.out.println("获得Excel表格的内容:");

for (int i = 1; i <= map.size(); i++) {

System.out.println(map.get(i));

}

} catch (FileNotFoundException e) {

System.out.println("未找到指定路径的文件!");

e.printStackTrace();

}catch (Exception e) {

e.printStackTrace();

}

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。