您的位置:

excel数据提取程序java(从excel提取数据)

本文目录一览:

如何使用java从excel表提取内容

以下是使用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 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

for optional dependencies.

TIFFImageWriter not loaded. tiff files will not be processed

See

for optional dependencies.

J2KImageReader not loaded. JPEG2000 files will not be processed.

See

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 王传大 22 Java软件开发

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

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

java怎么获取excel中的数据

使用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;icountrows;icount++){

row = sheet.getRow(icount);

int line=row.getPhysicalNumberOfCells();

for(int j=0;jline;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如何读取整个excel文件的内容

本例使用java来读取excel的内容并展出出结果,代码如下:

复制代码 代码如下:

import java.io.BufferedInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Date;

import java.util.List;

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

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

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

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

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

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelOperate {

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

File file = new File("ExcelDemo.xls");

String[][] result = getData(file, 1);

int rowLength = result.length;

for(int i=0;irowLength;i++) {

for(int j=0;jresult[i].length;j++) {

System.out.print(result[i][j]+"\t\t");

}

System.out.println();

}

}

/**

* 读取Excel的内容,第一维数组存储的是一行中格列的值,二维数组存储的是多少个行

* @param file 读取数据的源Excel

* @param ignoreRows 读取数据忽略的行数,比喻行头不需要读入 忽略的行数为1

* @return 读出的Excel中数据的内容

* @throws FileNotFoundException

* @throws IOException

*/

public static String[][] getData(File file, int ignoreRows)

throws FileNotFoundException, IOException {

ListString[] result = new ArrayListString[]();

int rowSize = 0;

BufferedInputStream in = new BufferedInputStream(new FileInputStream(

file));

// 打开HSSFWorkbook

POIFSFileSystem fs = new POIFSFileSystem(in);

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFCell cell = null;

for (int sheetIndex = 0; sheetIndex wb.getNumberOfSheets(); sheetIndex++) {

HSSFSheet st = wb.getSheetAt(sheetIndex);

// 第一行为标题,不取

for (int rowIndex = ignoreRows; rowIndex = st.getLastRowNum(); rowIndex++) {

HSSFRow row = st.getRow(rowIndex);

if (row == null) {

continue;

}

int tempRowSize = row.getLastCellNum() + 1;

if (tempRowSize rowSize) {

rowSize = tempRowSize;

}

String[] values = new String[rowSize];

Arrays.fill(values, "");

boolean hasValue = false;

for (short columnIndex = 0; columnIndex = row.getLastCellNum(); columnIndex++) {

String value = "";

cell = row.getCell(columnIndex);

if (cell != null) {

// 注意:一定要设成这个,否则可能会出现乱码

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING:

value = cell.getStringCellValue();

break;

case HSSFCell.CELL_TYPE_NUMERIC:

if (HSSFDateUtil.isCellDateFormatted(cell)) {

Date date = cell.getDateCellValue();

if (date != null) {

value = new SimpleDateFormat("yyyy-MM-dd")

.format(date);

} else {

value = "";

}

} else {

value = new DecimalFormat("0").format(cell

.getNumericCellValue());

}

break;

case HSSFCell.CELL_TYPE_FORMULA:

// 导入时如果为公式生成的数据则无值

if (!cell.getStringCellValue().equals("")) {

value = cell.getStringCellValue();

} else {

value = cell.getNumericCellValue() + "";

}

break;

case HSSFCell.CELL_TYPE_BLANK:

break;

case HSSFCell.CELL_TYPE_ERROR:

value = "";

break;

case HSSFCell.CELL_TYPE_BOOLEAN:

value = (cell.getBooleanCellValue() == true ? "Y"

: "N");

break;

default:

value = "";

}

}

if (columnIndex == 0 value.trim().equals("")) {

break;

}

values[columnIndex] = rightTrim(value);

hasValue = true;

}

if (hasValue) {

result.add(values);

}

}

}

in.close();

String[][] returnArray = new String[result.size()][rowSize];

for (int i = 0; i returnArray.length; i++) {

returnArray[i] = (String[]) result.get(i);

}

return returnArray;

}

/**

* 去掉字符串右边的空格

* @param str 要处理的字符串

* @return 处理后的字符串

*/

public static String rightTrim(String str) {

if (str == null) {

return "";

}

int length = str.length();

for (int i = length - 1; i = 0; i--) {

if (str.charAt(i) != 0x20) {

break;

}

length--;

}

return str.substring(0, length);

}

}

java如何读取整个excel文件的内容?

在java程序添加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");

        }

    }

}