java poi怎么获取excel单元格的内容

package edusjtuerplabpoi;

import javaioInputStream&ch=wwxqychain" target="_blank" class="link-baike">FileInputStream;

import javaioFileNotFoundException;

import javaioIOException;

import javaioInputStream;

import javatextSimpleDateFormat;

import javautilDate;

import javautilHashMap;

import javautilMap;

import orgapachepoihssfusermodelHSSFCell;

import orgapachepoihssfusermodelHSSFDateUtil;

import orgapachepoihssfusermodelHSSFRow;

import orgapachepoihssfusermodelHSSFSheet;

import orgapachepoihssfusermodelHSSFWorkbook;

import orgapachepoipoifsfilesystemPOIFSFileSystem;

/

   *** 作Excel表格的功能类

 /

public class ExcelReader {

    private POIFSFileSystem fs;

    private HSSFWorkbook wb;

    private HSSFSheet sheet;

    private HSSFRow row;

    /

      读取Excel表格表头的内容

      @param InputStream

      @return String 表头内容的数组

     /

    public String[] readExcelTitle(InputStream is) {

        try {

            fs = new POIFSFileSystem(is);

            wb = new HSSFWorkbook(fs);

        } catch (IOException e) {

            eprintStackTrace();

        }

        sheet = wbgetSheetAt(0);

        row = sheetgetRow(0);

        // 标题总列数

        int colNum = rowgetPhysicalNumberOfCells();

        Systemoutprintln("colNum:" + colNum);

        String[] title = new String[colNum];

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

            //title[i] = getStringCellValue(rowgetCell((short) i));

            title[i] = getCellFormatValue(rowgetCell((short) i));

        }

        return title;

    }

    /

      读取Excel数据内容

      @param InputStream

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

     /

    public Map<Integer, String> readExcelContent(InputStream is) {

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

        String str = "";

        try {

            fs = new POIFSFileSystem(is);

            wb = new HSSFWorkbook(fs);

        } catch (IOException e) {

            eprintStackTrace();

        }

        sheet = wbgetSheetAt(0);

        // 得到总行数

        int rowNum = sheetgetLastRowNum();

        row = sheetgetRow(0);

        int colNum = rowgetPhysicalNumberOfCells();

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

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

            row = sheetgetRow(i);

            int j = 0;

            while (j < colNum) {

                // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据

                // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean

                // str += getStringCellValue(rowgetCell((short) j))trim() +

                // "-";

                str += getCellFormatValue(rowgetCell((short) j))trim() + "    ";

                j++;

            }

            contentput(i, str);

            str = "";

        }

        return content;

    }

    /

      获取单元格数据内容为字符串类型的数据

      @param cell Excel单元格

      @return String 单元格数据内容

     /

    private String getStringCellValue(HSSFCell cell) {

        String strCell = "";

        switch (cellgetCellType()) {

        case HSSFCellCELL_TYPE_STRING:

            strCell = cellgetStringCellValue();

            break;

        case HSSFCellCELL_TYPE_NUMERIC:

            strCell = StringvalueOf(cellgetNumericCellValue());

            break;

        case HSSFCellCELL_TYPE_BOOLEAN:

            strCell = StringvalueOf(cellgetBooleanCellValue());

            break;

        case HSSFCellCELL_TYPE_BLANK:

            strCell = "";

            break;

        default:

            strCell = "";

            break;

        }

        if (strCellequals("") || strCell == null) {

            return "";

        }

        if (cell == null) {

            return "";

        }

        return strCell;

    }

    /

      获取单元格数据内容为日期类型的数据

      @param cell

                 Excel单元格

      @return String 单元格数据内容

     /

    private String getDateCellValue(HSSFCell cell) {

        String result = "";

        try {

            int cellType = cellgetCellType();

            if (cellType == HSSFCellCELL_TYPE_NUMERIC) {

                Date date = cellgetDateCellValue();

                result = (dategetYear() + 1900) + "-" + (dategetMonth() + 1)

                        + "-" + dategetDate();

            } else if (cellType == HSSFCellCELL_TYPE_STRING) {

                String date = getStringCellValue(cell);

                result = datereplaceAll("[年月]", "-")replace("日", "")trim();

            } else if (cellType == HSSFCellCELL_TYPE_BLANK) {

                result = "";

            }

        } catch (Exception e) {

            Systemoutprintln("日期格式不正确!");

            eprintStackTrace();

        }

        return result;

    }

    /

      根据HSSFCell类型设置数据

      @param cell

      @return

     /

    private String getCellFormatValue(HSSFCell cell) {

        String cellvalue = "";

        if (cell != null) {

            // 判断当前Cell的Type

            switch (cellgetCellType()) {

            // 如果当前Cell的Type为NUMERIC

            case HSSFCellCELL_TYPE_NUMERIC:

            case HSSFCellCELL_TYPE_FORMULA: {

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

                if (HSSFDateUtilisCellDateFormatted(cell)) {

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

                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00

                    //cellvalue = cellgetDateCellValue()toLocaleString();

                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12

                    Date date = cellgetDateCellValue();

                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                    cellvalue = sdfformat(date);

                }

                // 如果是纯数字

                else {

                    // 取得当前Cell的数值

                    cellvalue = StringvalueOf(cellgetNumericCellValue());

                }

                break;

            }

            // 如果当前Cell的Type为STRIN

            case HSSFCellCELL_TYPE_STRING:

                // 取得当前的Cell字符串

                cellvalue = cellgetRichStringCellValue()getString();

                break;

            // 默认的Cell值

            default:

                cellvalue = " ";

            }

        } else {

            cellvalue = "";

        }

        return cellvalue;

    }

    public static void main(String[] args) {

        try {

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

            InputStream is = new FileInputStream("d:\\test2xls");

            ExcelReader excelReader = new ExcelReader();

            String[] title = excelReaderreadExcelTitle(is);

            Systemoutprintln("获得Excel表格的标题:");

            for (String s : title) {

                Systemoutprint(s + " ");

            }

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

            InputStream is2 = new FileInputStream("d:\\test2xls");

            Map<Integer, String> map = excelReaderreadExcelContent(is2);

            Systemoutprintln("获得Excel表格的内容:");

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

                Systemoutprintln(mapget(i));

            }

        } catch (FileNotFoundException e) {

            Systemoutprintln("未找到指定路径的文件!");

            eprintStackTrace();

        }

    }

}

package comtest;

/

需要的jar包：

poi-302-FINAL-20080204jar

poi-contrib-302-FINAL-20080204jar

poi-scratchpad-302-FINAL-20080204jar

poi-35-beta6-20090622jar

geronimo-stax-api_10_spec-10jar

ooxml-schemas-10jar

openxml4j-bin-betajar

poi-ooxml-35-beta6-20090622jar

xmlbeans-230jar

dom4j-161jar

/

import javaioByteArrayInputStream;

import javaioFileInputStream;

import javaioFileOutputStream;

import javaioIOException;

import javaioInputStream;

import javaioStringWriter;

import orgapachepdfboxpdmodelPDDocument;

import orgapachepdfboxutilPDFTextStripper;

import orgapachepoiPOIOLE2TextExtractor;

import orgapachepoiPOITextExtractor;

import orgapachepoiPOIXMLDocument;

import orgapachepoiPOIXMLTextExtractor;

import orgapachepoiextractorExtractorFactory;

import orgapachepoihssfusermodelHSSFCell;

import orgapachepoihssfusermodelHSSFRow;

import orgapachepoihssfusermodelHSSFSheet;

import orgapachepoihssfusermodelHSSFWorkbook;

import orgapachepoihwpfextractorWordExtractor;

import orgapachepoiopenxml4jexceptionsOpenXML4JException;

import orgapachepoiopenxml4jopcOPCPackage;

import orgapachepoipoifsfilesystemDirectoryEntry;

import orgapachepoipoifsfilesystemDocumentEntry;

import orgapachepoipoifsfilesystemPOIFSFileSystem;

import orgapachepoixslfextractorXSLFPowerPointExtractor;

import orgapachepoixssfusermodelXSSFCell;

import orgapachepoixssfusermodelXSSFRow;

import orgapachepoixssfusermodelXSSFSheet;

import orgapachepoixssfusermodelXSSFWorkbook;

import orgapachepoixwpfextractorXWPFWordExtractor;

import orgapachexmlbeansXmlException;

public class WordAndExcelExtractor {

public static void main(String[] args) {

try {

// 读取word

String wordFile = "D:/1doc";

//String wordText2007 = WordAndExcelExtractorextractTextFromDOC2007(wordFile);

//Systemoutprintln("wordText2007=======" + wordText2007);

InputStream isword = new FileInputStream(wordFile);

WordExtractor wordExtractor = new WordExtractor(isword);

Systemoutprintln("word========" + wordExtractorgetText());

// 读取 Excel

InputStream is = new FileInputStream("D:/测试xls");

String excelText = WordAndExcelExtractorextractTextFromXLS(is);

Systemoutprintln("text2003==========" + excelText);

String excelFile = "D:/test2xlsx";

String excelText2007 = WordAndExcelExtractor

extractTextFromXLS2007(excelFile);

Systemoutprintln("excelText2007==========" + excelText2007);

// 读取 PPT

PowerPointExtractor ppe = new PowerPointExtractor("D:/testppt");

Systemoutprintln("ppt2003===============" + ppegetText());

// Systemoutprintln("###############################");

// Systemoutprintln(ppegetText(true, true, true, true));

//

// InputStream is = new FileInputStream("D:/testppt");

// PowerPointExtractor ppt2003 = new PowerPointExtractor(is);

// Systemoutprintln(ppt2003getText());

Systemoutprintln("");

XSLFPowerPointExtractor ppt = new XSLFPowerPointExtractor(

POIXMLDocumentopenPackage("D:/test2pptx"));

Systemoutprintln("ppt2007============================="

+ pptgetText());

/ 读取PDF /

InputStream in = new FileInputStream("D:/testpdf");

PDDocument pdfDocument = PDDocumentload(in);

if (pdfDocumentisEncrypted()) {

// 仅仅尝试使用默认密码打开加密的PDF

pdfDocumentdecrypt("");

}

PDFTextStripper stripper = null;

// 创建一个writer用来作来存储文件正文

StringWriter writer = new StringWriter();

if (stripper == null) {

stripper = new PDFTextStripper();

} else {

stripperresetEngine();

}

stripperwriteText(pdfDocument, writer);

String contents = writergetBuffer()toString();

Systemoutprintln("pdfd===" + contents);

/ 向Word中写入数据 /

byte[] a = contentsgetBytes();

ByteArrayInputStream bs = new ByteArrayInputStream(a);

POIFSFileSystem fs = new POIFSFileSystem();

// /////////////////////////////////

DirectoryEntry directory = fsgetRoot();

DocumentEntry de = directorycreateDocument("WordDocument", bs);

// 以上两句代码不能省略，否则输出的是乱码

FileOutputStream fos = new FileOutputStream("D:\\dddoc");

fswriteFilesystem(fos);

bsclose();

fosflush();

fosclose();

Systemoutprintln("写入成功");

} catch (Exception e) {

eprintStackTrace();

}

}

/

@Method: extractTextFromXLS

@Description: 从excel 2003档中提取纯文本

@param

@return String

@throws

/

@SuppressWarnings("deprecation")

private static String extractTextFromXLS(InputStream is) throws IOException {

StringBuffer content = new StringBuffer();

HSSFWorkbook workbook = new HSSFWorkbook(is); // 创建对Excel工作簿文件的引用

for (int numSheets = 0; numSheets < workbookgetNumberOfSheets(); numSheets++) {

if (null != workbookgetSheetAt(numSheets)) {

HSSFSheet aSheet = workbookgetSheetAt(numSheets); // 获得一个sheet

contentappend(aSheetgetSheetName());

contentappend("\r\n-----------------------\r\n");

for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet

getLastRowNum(); rowNumOfSheet++) {

if (null != aSheetgetRow(rowNumOfSheet)) {

HSSFRow aRow = aSheetgetRow(rowNumOfSheet); // 获得一行

for (short cellNumOfRow = 0; cellNumOfRow <= aRow

getLastCellNum(); cellNumOfRow++) {

if (null != aRowgetCell(cellNumOfRow)) {

HSSFCell aCell = aRowgetCell(cellNumOfRow); // 获得列值

if (aCellgetCellType() == HSSFCellCELL_TYPE_NUMERIC) {

contentappend(aCellgetNumericCellValue());

} else if (aCellgetCellType() == HSSFCellCELL_TYPE_BOOLEAN) {

contentappend(aCellgetBooleanCellValue());

} else {

contentappend(aCellgetStringCellValue());

}

contentappend("\t");

}

}

contentappend("\r\n");

}

}

}

}

return contenttoString();

}

/

@Method: extractTextFromXLS2007

@Description: 从excel 2007文档中提取纯文本

@param

@return String

@throws

/

private static String extractTextFromXLS2007(String fileName)

throws Exception {

StringBuffer content = new StringBuffer();

// 构造 XSSFWorkbook 对象，strPath 传入文件路径

XSSFWorkbook xwb = new XSSFWorkbook(fileName);

// 循环工作表Sheet

for (int numSheet = 0; numSheet < xwbgetNumberOfSheets(); numSheet++) {

XSSFSheet xSheet = xwbgetSheetAt(numSheet);

if (xSheet == null) {

continue;

}

// 循环行Row

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

XSSFRow xRow = xSheetgetRow(rowNum);

if (xRow == null) {

continue;

}

// 循环列Cell

for (int cellNum = 0; cellNum <= xRowgetLastCellNum(); cellNum++) {

XSSFCell xCell = xRowgetCell(cellNum);

if (xCell == null) {

continue;

}

if (xCellgetCellType() == XSSFCellCELL_TYPE_BOOLEAN) {

contentappend(xCellgetBooleanCellValue());

} else if (xCellgetCellType() == XSSFCellCELL_TYPE_NUMERIC) {

contentappend(xCellgetNumericCellValue());

} else {

contentappend(xCellgetStringCellValue());

}

}

}

}

return contenttoString();

}

}

这是POI jar包的下载地址，我下载的是39版本的

>

public static void main(String[] args) {

Workbook wb = null;

try {

//如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook

wb = new HSSFWorkbook(new FileInputStream("G:\excel\testxls"));

} catch (Exception e) {

eprintStackTrace();

}

//获取第一个画布

Sheet sheet = wbgetSheetAt(0);

CellReference cellReference = new CellReference("A4");

boolean flag = false;

for (int i = cellReferencegetRow(); i <= sheetgetLastRowNum();) {

Row r = sheetgetRow(i);

if(r == null){

//如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动

sheetshiftRows(i+1, sheetgetLastRowNum(),-1);

continue;

}

flag = false;

for(Cell c : r){

if(cgetCellType() != CellCELL_TYPE_BLANK){

flag = true;

break;

}

}

if(flag){

i++;

continue;

}else{//如果是空白行（即可能没有数据，但是有一定格式）

if(i == sheetgetLastRowNum()){

//如果到了最后一行，直接将那一行remove掉

sheetremoveRow(r);

}else{

//如果还没到最后一行，则数据往上移一行

sheetshiftRows(i+1, sheetgetLastRowNum(),-1);

}

}

}

Systemoutprintln("有效行数为:"+(sheetgetLastRowNum()+1));

}

直接上源码：

package edusjtuerplabpoi;

import javaioFileInputStream;

import javaioFileNotFoundException;

import javaioIOException;

import javaioInputStream;

import javatextSimpleDateFormat;

import javautilDate;

import javautilHashMap;

import javautilMap;

import orgapachepoihssfusermodelHSSFCell;

import orgapachepoihssfusermodelHSSFDateUtil;

import orgapachepoihssfusermodelHSSFRow;

import orgapachepoihssfusermodelHSSFSheet;

import orgapachepoihssfusermodelHSSFWorkbook;

import orgapachepoipoifsfilesystemPOIFSFileSystem;

/

*** 作Excel表格的功能类

/

public class ExcelReader {

private POIFSFileSystem fs;

private HSSFWorkbook wb;

private HSSFSheet sheet;

private HSSFRow row;

/

读取Excel表格表头的内容

@param InputStream

@return String 表头内容的数组

/

public String[] readExcelTitle(InputStream is) {

try {

fs = new POIFSFileSystem(is);

wb = new HSSFWorkbook(fs);

} catch (IOException e) {

eprintStackTrace();

}

sheet = wbgetSheetAt(0);

row = sheetgetRow(0);

// 标题总列数

int colNum = rowgetPhysicalNumberOfCells();

Systemoutprintln("colNum:" + colNum);

String[] title = new String[colNum];

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

//title[i] = getStringCellValue(rowgetCell((short) i));

title[i] = getCellFormatValue(rowgetCell((short) i));

}

return title;

}

/

读取Excel数据内容

@param InputStream

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

/

public Map<Integer, String> readExcelContent(InputStream is) {

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

String str = "";

try {

fs = new POIFSFileSystem(is);

wb = new HSSFWorkbook(fs);

} catch (IOException e) {

eprintStackTrace();

}

sheet = wbgetSheetAt(0);

// 得到总行数

int rowNum = sheetgetLastRowNum();

row = sheetgetRow(0);

int colNum = rowgetPhysicalNumberOfCells();

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

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

row = sheetgetRow(i);

int j = 0;

while (j < colNum) {

// 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据

// 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean

// str += getStringCellValue(rowgetCell((short) j))trim() +

// "-";

str += getCellFormatValue(rowgetCell((short) j))trim() + " ";

j++;

}

contentput(i, str);

str = "";

}

return content;

}

/

获取单元格数据内容为字符串类型的数据

@param cell Excel单元格

@return String 单元格数据内容

/

private String getStringCellValue(HSSFCell cell) {

String strCell = "";

switch (cellgetCellType()) {

case HSSFCellCELL_TYPE_STRING:

strCell = cellgetStringCellValue();

break;

case HSSFCellCELL_TYPE_NUMERIC:

strCell = StringvalueOf(cellgetNumericCellValue());

break;

case HSSFCellCELL_TYPE_BOOLEAN:

strCell = StringvalueOf(cellgetBooleanCellValue());

break;

case HSSFCellCELL_TYPE_BLANK:

strCell = "";

break;

default:

strCell = "";

break;

}

if (strCellequals("") || strCell == null) {

return "";

}

if (cell == null) {

return "";

}

return strCell;

}

/

获取单元格数据内容为日期类型的数据

@param cell

Excel单元格

@return String 单元格数据内容

/

private String getDateCellValue(HSSFCell cell) {

String result = "";

try {

int cellType = cellgetCellType();

if (cellType == HSSFCellCELL_TYPE_NUMERIC) {

Date date = cellgetDateCellValue();

result = (dategetYear() + 1900) + "-" + (dategetMonth() + 1)

+ "-" + dategetDate();

} else if (cellType == HSSFCellCELL_TYPE_STRING) {

String date = getStringCellValue(cell);

result = datereplaceAll("[年月]", "-")replace("日", "")trim();

} else if (cellType == HSSFCellCELL_TYPE_BLANK) {

result = "";

}

} catch (Exception e) {

Systemoutprintln("日期格式不正确!");

eprintStackTrace();

}

return result;

}

/

根据HSSFCell类型设置数据

@param cell

@return

/

private String getCellFormatValue(HSSFCell cell) {

String cellvalue = "";

if (cell != null) {

// 判断当前Cell的Type

switch (cellgetCellType()) {

// 如果当前Cell的Type为NUMERIC

case HSSFCellCELL_TYPE_NUMERIC:

case HSSFCellCELL_TYPE_FORMULA: {

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

if (HSSFDateUtilisCellDateFormatted(cell)) {

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

//方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00

//cellvalue = cellgetDateCellValue()toLocaleString();

//方法2：这样子的data格式是不带带时分秒的：2011-10-12

Date date = cellgetDateCellValue();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

cellvalue = sdfformat(date);

}

// 如果是纯数字

else {

// 取得当前Cell的数值

cellvalue = StringvalueOf(cellgetNumericCellValue());

}

break;

}

// 如果当前Cell的Type为STRIN

case HSSFCellCELL_TYPE_STRING:

// 取得当前的Cell字符串

cellvalue = cellgetRichStringCellValue()getString();

break;

// 默认的Cell值

default:

cellvalue = " ";

}

} else {

cellvalue = "";

}

return cellvalue;

}

public static void main(String[] args) {

try {

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

InputStream is = new FileInputStream("d:\\test2xls");

ExcelReader excelReader = new ExcelReader();

String[] title = excelReaderreadExcelTitle(is);

Systemoutprintln("获得Excel表格的标题:");

for (String s : title) {

Systemoutprint(s + " ");

}

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

InputStream is2 = new FileInputStream("d:\\test2xls");

Map<Integer, String> map = excelReaderreadExcelContent(is2);

Systemoutprintln("获得Excel表格的内容:");

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

Systemoutprintln(mapget(i));

}

} catch (FileNotFoundException e) {

Systemoutprintln("未找到指定路径的文件!");

eprintStackTrace();

}

}

}

以上就是关于java poi怎么获取excel单元格的内容全部的内容，包括:java poi怎么获取excel单元格的内容、谁能给我一个详细的Java通过Apache POI导出Excel方法，最好能给完整代码、利用POI读取Excel有效行数(含有内容)等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！