java读取xlsx文件，java 读取excel poi

java读取xlsx文件？二、Java读取Excel数据,首先，创建一个xls文件（如：jxltest.xls），然后在文件中添加一些数据，Excel文件创建完成后，便可以开始写代码读取了。三、进行一个小小的扩展，读取一个目录下的所有Excel文件，那么，java读取xlsx文件？一起来了解一下吧。

java解析xlsx文件

大家都知道Excel2003或之前的版本保存的表格默认格式一般是“xls”的后缀名，那么“xlsx”的后缀又是什么文件呢;那是什么程序生成出来的呢?其实xlsx文件也是Excel生成出来文件后缀，只不过是Excel2007或以上版本默认的文件后缀。xlsx文件在Excel2003和之前的版本中是无法打开的，只有Excel2007和以上版本才可以打开使用。

有些朋友电脑上使用的是Excel2003，但是经常会接触到一些“xlsx文件”却无法宏慎庆打开，这个问题该如何解决呢，xlsx文件怎么打开呢?其实Word联盟对这种问题有N种解决方法，下面一起来看看吧!

一、使用在线打开xlsx文件

网易文档查看

它不仅仅可以打开xlsx文件，还可以打开：

Office系列：蔽握doc, docx, ppt, pptx, xls, xlsx

Adobe系列：pdf

代码系列：java, jsp, js, c, cpp, h, hpp, py, cs, sh, css

以上这些后缀名文件都可以在“网易文档查看”中打开，大家赶紧去试试吧!

二、安装Office兼容包即可

这是微软推出的一款兼容，安装Office兼容包以后你就可以通过使用Excel2003来打开和编辑xlsx文件了。

java读写excel文件 无依赖

添加spire.xls.jar为依赖，使用下面的代码

import com.spire.xls.*;

public class ReadExcel {

public static void main(String[] args) {

//创建Workbook对象敏凳

Workbook wb = new Workbook();

//加载一个Excel文档

衡升wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\test.xlsx");

//获取第一个工作表

Worksheet sheet = wb.getWorksheets().get(0);

//遍历工作表的每一行

for (int i = 1; i < sheet.getLastRow() + 1; i++) {

//遍历工作的每一列

for (int j = 1; j < sheet.getLastColumn() + 1; j++) {

桥拦旅 //输出指定单元格的数据

System.out.print(sheet.get(i,j).getText());

System.out.print("\t");

}

System.out.print("\n");

}

}

}

java读取xlsx格式的excel

package com.jqgj.test;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.io.FilenameUtils;

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

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

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

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

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

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;

public class ImportNameTest {

/**

* Excel 2003

*/

private final static String XLS = "xls";

/**

* Excel 2007

*/

private final static String XLSX = "xlsx";

/**

* 分隔符

*/

private final static String SEPARATOR = "竖返渗|";

/**

* 由Excel文世烂件的Sheet导出至List

*

* @param file

* @param sheetNum

* @return

*/

public static List exportListFromExcel(File file, int sheetNum)

throws IOException {

return exportListFromExcel(new FileInputStream(file),

FilenameUtils.getExtension(file.getName()), sheetNum);

}

/**

* 由Excel流的Sheet导出至List

*

* @param is

* @param extensionName

* @param sheetNum

* @return

* @throws IOException

*/

public static List exportListFromExcel(InputStream is,

String extensionName, int sheetNum) throws IOException {

Workbook workbook = null;

if (extensionName.toLowerCase().equals(XLS)) {

workbook = new HSSFWorkbook(is);

} else if (extensionName.toLowerCase().equals(XLSX)) {

workbook = new XSSFWorkbook(is);

}

return exportListFromExcel(workbook, sheetNum);

}

/**

* 由指定的Sheet导出至List

*

* @param workbook

* @param sheetNum

* @return

* @throws IOException

*/

private static List exportListFromExcel(Workbook workbook,

int sheetNum) {

Sheet sheet = workbook.getSheetAt(sheetNum);

/余脊/ 解析公式结果

FormulaEvaluator evaluator = workbook.getCreationHelper()

.createFormulaEvaluator();

List list = new ArrayList();

int minRowIx = sheet.getFirstRowNum();

int maxRowIx = sheet.getLastRowNum();

for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {

Row row = sheet.getRow(rowIx);

StringBuilder sb = new StringBuilder();

short minColIx = row.getFirstCellNum();

short maxColIx = row.getLastCellNum();

for (short colIx = minColIx; colIx <= maxColIx; colIx++) {

Cell cell = row.getCell(new Integer(colIx));

CellValue cellValue = evaluator.evaluate(cell);

if (cellValue == null) {

continue;

}

// 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了

// 其余数据类型，根据文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html

switch (cellValue.getCellType()) {

case Cell.CELL_TYPE_BOOLEAN:

sb.append(SEPARATOR + cellValue.getBooleanValue());

break;

case Cell.CELL_TYPE_NUMERIC:

// 这里的日期类型会被转换为数字类型，需要判别后区分处理

if (DateUtil.isCellDateFormatted(cell)) {

sb.append(SEPARATOR + cell.getDateCellValue());

} else {

//把手机号码转换为字符串

DecimalFormat df = new DecimalFormat("#");

sb.append(SEPARATOR + df.format(cellValue.getNumberValue()));

}

break;

case Cell.CELL_TYPE_STRING:

sb.append(SEPARATOR + cellValue.getStringValue());

break;

case Cell.CELL_TYPE_FORMULA:

break;

case Cell.CELL_TYPE_BLANK:

break;

case Cell.CELL_TYPE_ERROR:

break;

default:

break;

}

}

list.add(sb.toString());

}

return list;

}

/**

* @param args

*/

public static void main(String[] args) {

String path = "f:\\telName.xlsx";

try {

List listS= exportListFromExcel(new File(path),0);

for(int i=0;i

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

}

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

Java读取xlsfile文件

使用java poi

package webservice;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

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

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

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

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

public class ExcelController {

@SuppressWarnings("deprecation")

public void excel() throws FileNotFoundException, IOException{

String filename = "d:\\excel.xls";

HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filename));

//按名引用excel工作表

// HSSFSheet sheet = workbook.getSheet("JSP");

//用式获取excel工作表宴陵采用工作表索引值

HSSFSheet sheet = workbook.getSheetAt(0);

HSSFRow row ;

HSSFCell cell1;

int rows=sheet.getLastRowNum();

for(int icount=0;icount

row = sheet.getRow(icount);

int line=row.getPhysicalNumberOfCells();

for(int j=0;j

cell1= row.getCell(j);

System.out.println(cell1+"--"+icount+"---"+j);

}

}

//打印读取值

// System.out.println(cell.getStringCellValue());

//新建输流

FileOutputStream fout = new FileOutputStream(filename); //PS：冲祥高filename 另存路径处理直接写入模版文件

//存盘

workbook.write(fout);

fout.flush();

//结束关闭

fout.close();

}

public HSSFCell getCell(HSSFRow row, int index) {

// 取发期单元格

HSSFCell cell = row.getCell(index);

// 单元格存

if (cell == null) {

// 创建单元格

cell = row.createCell(index);

}

//散尺 返单元格

return cell;

}

public static void main(String[] args) {

ExcelController ec = new ExcelController();

try {

ec.excel();

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

java最常用的五个包

以下是使用java从excel表提取内容的程序 -

import java.io.File;import java.io.FileInputStream;import org.apache.tika.metadata.Metadata;import org.apache.tika.parser.ParseContext;import org.apache.tika.parser.microsoft.ooxml.OOXMLParser;import org.apache.tika.sax.BodyContentHandler;public class ExtractContentFromExcel {

public static void main(String args[]) throws Exception {

// detecting the file type

BodyContentHandler handler = new BodyContentHandler();

Metadata metadata = new Metadata();

FileInputStream inputstream = new FileInputStream(new File("excelExample.xlsx"));

ParseContext pcontext = new ParseContext();

// OOXml parser

OOXMLParser msofficeparser = new OOXMLParser();

msofficeparser.parse(inputstream, handler, metadata, pcontext);

System.out.println("Contents of the document:" + handler.toString());

System.out.println("Metadata of the document:");

String[] metadataNames = metadata.names();

for (String name : metadataNames) {

System.out.println(name + ": " + metadata.get(name));

}

}}Java

原ODF文件：excelExample.xlsx 的内容如下羡大 -

执行上面示例代码敏卖，得到以下结果 -

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/F:/worksp/javaexamples/libs/tika_libs/tika-app-1.16.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/F:/worksp/javaexamples/libs/tika_libs/tika-server-1.16.jar!/org/slf4j/桥派逗impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

九月 27, 2017 5:15:47 上午 org.apache.tika.config.InitializableProblemHandler$3 handleInitializableProblem

警告: JBIG2ImageReader not loaded. jbig2 files will be ignored

See http://pdfbox.apache.org/2.0/dependencies.html#jai-image-io

for optional dependencies.

TIFFImageWriter not loaded. tiff files will not be processed

See http://pdfbox.apache.org/2.0/dependencies.html#jai-image-io

for optional dependencies.

J2KImageReader not loaded. JPEG2000 files will not be processed.

See http://pdfbox.apache.org/2.0/dependencies.html#jai-image-io

for optional dependencies.

九月 27, 2017 5:15:47 上午 org.apache.tika.config.InitializableProblemHandler$3 handleInitializableProblem

警告: org.xerial's sqlite-jdbc is not loaded.

Please provide the jar on your classpath to parse sqlite files.

See tika-parsers/pom.xml for the correct version.

Contents of the document:Sheet1

编号姓名年龄工作

1001王传大22Java开发

1002李小双29项目经理

1020张在传28人事经理

Sheet2

Sheet3

Metadata of the document:

date: 2017-09-27T09:15:38Z

meta:creation-date: 2017-09-27T09:13:50Z

extended-properties:Application: Microsoft Excel

Creation-Date: 2017-09-27T09:13:50Z

dcterms:created: 2017-09-27T09:13:50Z

Last-Modified: 2017-09-27T09:15:38Z

dcterms:modified: 2017-09-27T09:15:38Z

Last-Save-Date: 2017-09-27T09:15:38Z

protected: false

meta:save-date: 2017-09-27T09:15:38Z

Application-Name: Microsoft Excel

modified: 2017-09-27T09:15:38Z

Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

custom:KSOProductBuildVer: 2052-10.1.0.6489Shell

易百教程移动端：请扫描本页面底部(右侧)二维码并关注微信公众号，回复："教程" 选择相关教程阅读或直接访问：http://m.yiibai.com 。

以上就是java读取xlsx文件的全部内容，通过java的poi读取xlsx文件:将excel文件使用文件流读取 把excel的文件流转化成excel的工作薄 获取工作薄的sheet（工作表），迭代获取 最后再次迭代每一个sheet，获取每一行的每一个单元格。