您的位置 首页 java

java 读取 excel 表格内容

一、添加依赖

<dependency>

<groupId>org.apache. poi </groupId>

<artifactId>poi</artifactId>

<version>3.8</version>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.8</version>

</dependency>

二、工具类

根据文件后缀判断 2003 || 2007 || 2010 格式。

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

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

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

import java. io . FileInputStream ;

import java.io.InputStream;

import java.time.Instant;

import java.time.LocalDateTime;

import java.time.ZoneId;

import java.time.format.DateTimeFormatter;

import java.util.HashMap;

import java.util.Map;

public class ExcelUtils {

private static Workbook wb;

private static Sheet sheet ;

private static Row row;

private static final String EXCEL_XLS = “xls”;

private static final String EXCEL_XLSX = “xlsx”;

/**

* 读取表头

*

* @param inputStream 文件

* @return List<String> 返回list

*/

public static Map<Integer, String> readExcelTitle(InputStream inputStream, String suffix) {

getWorkbook(inputStream, suffix);

sheet = wb.getSheetAt(0);

row = sheet.getRow(0);

// 标题总列数

int colNum = row.getPhysicalNumberOfCells();

Map<Integer, String> map = new HashMap<>();

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

map.put(i, row.getCell(i).getStringCellValue());

}

return map;

}

/**

* 读取excel内容

*

* @param inputStream 文件

* @return Map<行, Map < 下标, Object>>

*/

public static Map<Integer, Map<Integer, String>> readExcelContent(InputStream inputStream, String suffix) {

getWorkbook(inputStream, suffix);

Map<Integer, Map<Integer, String>> 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<Integer, String> cellValue = new HashMap<>();

while (j < colNum) {

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

cellValue.put(j, obj);

j++;

}

content.put(i, cellValue);

}

return content;

}

private static String getCellFormatValue(Cell cell) {

String cellValue = “”;

if (cell != null) {

// 判断当前Cell的 Type

switch (cell.getCellType()) {

// 如果当前Cell的Type为NUMERIC

case Cell.CELL_TYPE_NUMERIC:

case Cell.CELL_TYPE_FORMULA: {

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

if (DateUtil.isCellDateFormatted(cell)) {

DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(“YYYY-MM-dd HH:mm:ss”);

Instant instant = cell.getDateCellValue().toInstant();

ZoneId zoneId = ZoneId.systemDefault();

LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, zoneId);

cellValue = dateTimeFormatter.format(localDateTime);

} else {

// 如果是纯数字

// 取得当前Cell的数值

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

}

break ;

}

// 如果当前Cell的Type为STRING

case Cell.CELL_TYPE_STRING:

// 取得当前的Cell字符串

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

break;

default:

// 默认的Cell值

cellValue = “”;

}

}

return cellValue;

}

private static void getWorkbook(InputStream inputStream, String suffix) {

try {

//2003

if (EXCEL_XLS.equals(suffix)) {

wb = new HSSFWorkbook(inputStream);

//2007/2010

} else if (EXCEL_XLSX.equals(suffix)) {

wb = new XSSFWorkbook(inputStream);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

inputStream. close ();

} catch (Exception e) {

e.printStackTrace();

}

}

}

}

感谢你耐心看完了文章…

要是觉得这篇文章对你有帮助,请转发收藏一下哦,记得关注作者哦,我会不定期在微头条分享Java,Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化、分布式架构,BATJ面试 等资料…

文章来源:智云一二三科技

文章标题:java 读取 excel 表格内容

文章地址:https://www.zhihuclub.com/181299.shtml

关于作者: 智云科技

热门文章

网站地图