java excel数据提取_java从Excel2007中提取数据

Author: kagula

Last Update Date: 2016-8-2

Environment:

maven  eclipse mars.2   jdk1.8

Introduce:

How to extract the specified column data by title name  from the excel 2003/2007 file demo.

the excel file first row is header for distinguish column, after that is body will be extract.

Content:

Part1: Maven

org.apache.poi

poi

3.14

org.apache.poi

poi-ooxml

3.14

Part2: Source

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

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;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public abstract class ExcelHelper {

private static Logger logger = LoggerFactory.getLogger(ExcelHelper.class);

private final static String XLS = "xls";

private final static String XLSX = "xlsx";

/*

* file: a full path in the storage.

* sheetNum: sheet index in the file, base is 0.

* listKyes: list key you want to extract from the excel file, must be in left to right order.

*/

public static List> fromExcel2List(File file,

int sheetNum,

List listKeys)

throws IOException {

return fromExcel2List(new FileInputStream(file),

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

}

public static List> fromExcel2List(InputStream is,

String extensionName, int sheetNum, List listKeys) 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 fromExcel2List(workbook, sheetNum,listKeys);

}

private static List> fromExcel2List(Workbook workbook,

int sheetNum, List listKeys) {

Sheet sheet = workbook.getSheetAt(sheetNum);

// 解析公式结果

FormulaEvaluator evaluator = workbook.getCreationHelper()

.createFormulaEvaluator();

List> list = new ArrayList>();

int minRowIx = sheet.getFirstRowNum();

int maxRowIx = sheet.getLastRowNum();

if( maxRowIx - minRowIx <= 1 )

{

logger.warn("Rows is not enough!");

return null;

}

//Extract index in excel corresponding keys.

Row rowHeader = sheet.getRow(minRowIx);

Map mapSI = new HashMap();

short minColIx = rowHeader.getFirstCellNum();

short maxColIx = rowHeader.getLastCellNum();

for(int i=minColIx; i<=maxColIx; i++)

{

Cell cell = rowHeader.getCell(new Integer(i));

CellValue cellValue = evaluator.evaluate(cell);

if (cellValue == null) {

continue;

}

if(cellValue.getCellType()!=Cell.CELL_TYPE_STRING)

continue;

String t = cellValue.getStringValue().trim();

mapSI.put(t, i);

}

//User must assure key order.

List listKeyInIndex = new ArrayList();

for(int i=0; i

{

String key = listKeys.get(i);

if(mapSI.containsKey(key) == false)

{

logger.warn("Can not find key in the excel file!");

return null;

}

listKeyInIndex.add(mapSI.get(key));

}

//Extract content

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

Row row = sheet.getRow(rowIx);

Map mapRow = new HashMap();

List listRow = new ArrayList();

minColIx = row.getFirstCellNum();

maxColIx = row.getLastCellNum();

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

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

CellValue cellValue = evaluator.evaluate(cell);

if (cellValue == null) {

continue;

}

// Here, only exist Boolean、Numeric and String three types except Error type.

// Other types, According official site can be ignore http://poi.apache.org/spreadsheet/eval.html

switch (cellValue.getCellType()) {

case Cell.CELL_TYPE_BOOLEAN:

mapRow.put(colIx, cellValue.getBooleanValue()+"");

break;

case Cell.CELL_TYPE_NUMERIC:

//data type will be convert to numeric type, so should be distinguish.

if (DateUtil.isCellDateFormatted(cell)) {

mapRow.put(colIx, cell.getDateCellValue()+"");

} else {

mapRow.put(colIx, cellValue.getNumberValue()+"");

}

break;

case Cell.CELL_TYPE_STRING:

mapRow.put(colIx, cellValue.getStringValue());

break;

case Cell.CELL_TYPE_FORMULA:

break;

case Cell.CELL_TYPE_BLANK:

break;

case Cell.CELL_TYPE_ERROR:

break;

default:

break;

}//end case

}//end for

for(int i=0;i

{

String strColVal = "";

if(mapRow.containsKey(listKeyInIndex.get(i)))

{

strColVal = mapRow.get(listKeyInIndex.get(i));

}

listRow.add(strColVal);

}

list.add(listRow);

}//end for

return list;

}

}

Last Part: Test

@Test

public void TestParseExcelFile() throws IOException

{

String path = "C:\\Users\\kagula\\20160705.xls";

List> list = null;

List parameters = new ArrayList();

parameters.add("地市");

parameters.add("加油站");

parameters.add("加油站(描述)");

list = ExcelHelper.fromExcel2List(new File(path), 0, parameters);

for(int i=0;i

{

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

}//end for

}//end function

Reference

[1]《Java操作Excel文件导入》