public class ExcelRead2003Test {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "用户信息表BigData.xls");
//1.创建工作簿,使用excel能操作的这边都看看操作
Workbook workbook = new HSSFWorkbook(inputStream);
//2.得到表
Sheet sheet = workbook.getSheetAt(0);
//3.得到行
Row row = sheet.getRow(0);
//4.得到列
Cell cell = row.getCell(0);
getValue(cell);
inputStream.close();
public static void getValue(Cell cell){
//匹配类型数据
if (cell != null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case STRING: //字符串
System.out.print("[String类型]");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: //布尔类型
System.out.print("[boolean类型]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空
System.out.print("[BLANK类型]");
break;
case NUMERIC: //数字（日期、普通数字）
System.out.print("[NUMERIC类型]");
if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
System.out.print("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//不是日期格式，防止数字过长
System.out.print("[转换为字符串输出]");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
break;
case ERROR:
System.out.print("[数据类型错误]");
break;
System.out.println(cellValue);
}
2.3.2、XSSF方式导入
public class ExcelRead2007Test {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "用户信息表2007BigData.xlsx");
//1.创建工作簿,使用excel能操作的这边都看看操作
Workbook workbook = new XSSFWorkbook(inputStream);
//2.得到表
Sheet sheet = workbook.getSheetAt(0);
//3.得到行
Row row = sheet.getRow(0);
//4.得到列
Cell cell = row.getCell(0);
getValue(cell);
inputStream.close();
public static void getValue(Cell cell){
//匹配类型数据
if (cell != null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case STRING: //字符串
System.out.print("[String类型]");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: //布尔类型
System.out.print("[boolean类型]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空
System.out.print("[BLANK类型]");
break;
case NUMERIC: //数字（日期、普通数字）
System.out.print("[NUMERIC类型]");
if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
System.out.print("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//不是日期格式，防止数字过长
System.out.print("[转换为字符串输出]");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
break;
case ERROR:
System.out.print("[数据类型错误]");
break;
System.out.println(cellValue);
}
2.3.3、SXSSF方式导入
public class ExcelReadSXSSFTest {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//获取文件流
//1.创建工作簿,使用excel能操作的这边都看看操作
OPCPackage opcPackage = OPCPackage.open(PATH + "用户信息表2007BigData.xlsx");
XSSFReader xssfReader = new XSSFReader(opcPackage);
StylesTable stylesTable = xssfReader.getStylesTable();
ReadOnlySharedStringsTable sharedStringsTable = new ReadOnlySharedStringsTable(opcPackage);
// 创建XMLReader，设置ContentHandler
XMLReader xmlReader = SAXHelper.newXMLReader();
xmlReader.setContentHandler(new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, new SimpleSheetContentsHandler(), false));
// 解析每个Sheet数据
Iterator<InputStream> sheetsData = xssfReader.getSheetsData();
while (sheetsData.hasNext()) {
try (InputStream inputStream = sheetsData.next();) {
xmlReader.parse(new InputSource(inputStream));
* 内容处理器
public static class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
protected List<String> row;
* A row with the (zero based) row number has started
* @param rowNum
@Override
public void startRow(int rowNum) {
row = new ArrayList<>();
* A row with the (zero based) row number has ended
* @param rowNum
@Override
public void endRow(int rowNum) {
if (row.isEmpty()) {
return;
// 处理数据
System.out.println(row.stream().collect(Collectors.joining(" ")));
* A cell, with the given formatted value (may be null),
* and possibly a comment (may be null), was encountered
* @param cellReference
* @param formattedValue
* @param comment
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
row.add(formattedValue);
* A header or footer has been encountered
* @param text
* @param isHeader
* @param tagName
@Override
public void headerFooter(String text, boolean isHeader, String tagName) {
}
下载 APP
