java完美解析excel

最新推荐文章于 2025-06-18 18:29:57 发布

原创最新推荐文章于 2025-06-18 18:29:57 发布 · 置顶 · 563 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #excel

本文介绍了一种使用Java代码解析Excel表格的方法，适用于2003及2007版本。通过Apache POI库，实现了对不同格式单元格内容的读取，包括数字、日期、布尔值等，并将数据存储为ArrayList结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

java 代码完美解析excel表格，2007与2003版。2003及以上版本均可使用2007版
package com.elensdata;

import org.apache.poi.hssf.usermodel.*;
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.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
public class ExcelUtil {
//默认单元格内容为数字时格式
private static DecimalFormat df = new DecimalFormat(“0”);
// 默认单元格格式化日期字符串
private static SimpleDateFormat sdf = new SimpleDateFormat( “yyyy-MM-dd HH:mm:ss”);
// 格式化数字
private static DecimalFormat nf = new DecimalFormat(“0.00”);
public static ArrayList<ArrayList> readExcel(File file){
if(file == null){
return null;
}
if(file.getName().endsWith(“xlsx”)){
//处理ecxel2007
return readExcel2007(file);
}else{
//处理ecxel2003
return readExcel2003(file);
}
}
/*
* @return 将返回结果存储在ArrayList内，存储结构与二位数组类似
* lists.get(0).get(0)表示过去Excel中0行0列单元格
*/
public static ArrayList<ArrayList> readExcel2003(File file){
try{
ArrayList<ArrayList> rowList = new ArrayList<ArrayList>();
ArrayList colList;
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
Object value;
for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
row = sheet.getRow(i);
colList = new ArrayList();
if(row == null){
//当读取行为空时
if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行
rowList.add(colList);
}
continue;
}else{
rowCount++;
}
for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){
cell = row.getCell(j);
if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
//当该单元格为空
if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格
colList.add("");
}
continue;
}
switch(cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
// System.out.println(i + “行” + j + " 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if (“General”.equals(cell.getCellStyle()
.getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
// System.out.println(i + “行” + j
// + " 列 is Number type ; DateFormt:"
// + value.toString());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
// System.out.println(i + “行” + j + " 列 is Boolean type");
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
// System.out.println(i + “行” + j + " 列 is Blank type");
value = “”;
break;
default:
// System.out.println(i + “行” + j + " 列 is default type");
value = cell.toString();
}// end switch
colList.add(value);
}//end for j
rowList.add(colList);
}//end for i

        return rowList;
    }catch(Exception e){
        return null;
    }
}

public static ArrayList<ArrayList<Object>> readExcel2007(File file){
    try{
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
        ArrayList<Object> colList;
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row;
        XSSFCell cell;
        Object value;
        for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
            row = sheet.getRow(i);
            colList = new ArrayList<Object>();
            if(row == null){
                //当读取行为空时
                if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行
                    rowList.add(colList);
                }
                continue;
            }else{
                rowCount++;
            }
            for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){
                cell = row.getCell(j);
                if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
                    //当该单元格为空
                    if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格
                        colList.add("");
                    }
                    continue;
                }
                switch(cell.getCellType()){
                    case XSSFCell.CELL_TYPE_STRING:

// System.out.println(i + “行” + j + " 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if (“General”.equals(cell.getCellStyle()
.getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
// System.out.println(i + “行” + j
// + " 列 is Number type ; DateFormt:"
// + value.toString());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
// System.out.println(i + “行” + j + " 列 is Boolean type");
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
// System.out.println(i + “行” + j + " 列 is Blank type");
value = “”;
break;
default:
// System.out.println(i + “行” + j + " 列 is default type");
value = cell.toString();
}// end switch
colList.add(value);
}//end for j
rowList.add(colList);
}//end for i

        return rowList;
    }catch(Exception e){
        System.out.println("exception");
        return null;
    }
}


public static DecimalFormat getDf() {
    return df;
}
public static void setDf(DecimalFormat df) {
    ExcelUtil.df = df;
}
public static SimpleDateFormat getSdf() {
    return sdf;
}
public static void setSdf(SimpleDateFormat sdf) {
    ExcelUtil.sdf = sdf;
}
public static DecimalFormat getNf() {
    return nf;
}
public static void setNf(DecimalFormat nf) {
    ExcelUtil.nf = nf;
}