POI互換読出しExcel
14037 ワード
package ycl.learn.excel;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
public class ExcelResult {
public static final String HEADER_KEY = "header";
private static final String DEFUAL_KEY = "defalut";
private ArrayList<ExcelErrorEnum> errors = new ArrayList<ExcelErrorEnum>();
private Map<String, Object> contentMap = new HashMap<String,Object>();
public enum ExcelErrorEnum{
FILE_FORMAT,WORKBOOK_NOT_EXIST,SHEET_NOT_DATA,HEADER_LENGTH_NOT_MATCH,HEADER_NOT_MATCH,CELL_NOT_EXIST;
}
/**
* The error exist.
*
* @return
*/
public boolean errorsExist() {
if ( errors.size() > 0 ) {
return true;
} else {
return false;
}
}
/**
* add errorCode
*
* @param errorCode
*/
public void addError( ExcelErrorEnum errorCode ) {
errors.add(errorCode);
}
/**
* add all sub errorCodes
*
* @param excelError
*/
public void addErrors(ExcelResult excelError){
errors.addAll(excelError.errors);
}
public ArrayList<ExcelErrorEnum> getErrors(){
return errors;
}
public void setContent(Object obj){
contentMap.put(DEFUAL_KEY, obj);
}
public Object getContent(){
return contentMap.get(DEFUAL_KEY);
}
public void setContent(String key,Object obj){
contentMap.put(key, obj);
}
public Object getContent(String key){
return contentMap.get(key);
}
public Map<String,Object> getContentMap(){
return contentMap;
}
}
this is the definetion of ExcelResult, contain the error and result.
package ycl.learn.excel;
public class ExcelHeader {
public interface ColumnHeader{
public String getName();
}
public enum UserHeader implements ColumnHeader{
ACTION("ACTION"),FIRST_NAME("FIRST NAME"),LAST_NAME("LAST NAME"),E_MAIL("E-MAIL"),PHONE("PHONE");
private String name=null;
private UserHeader(String name){
this.name = name;
}
public String getName(){
return name;
}
}
public enum PhoneHeader implements ColumnHeader{
//ACTION("ACTION"),
NAME("NAME"),PHONE_NUMBER("PHONE NUMBER");
private String name=null;
private PhoneHeader(String name){
this.name = name;
}
public String getName(){
return name;
}
}
}
this is the ExcelHeader definetion, You can choose some of columns you wanted to export.
package ycl.learn.excel;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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 ycl.learn.excel.ExcelResult.ExcelErrorEnum;
import ycl.learn.excel.ExcelHeader.ColumnHeader;
public class ExcelReader {
public static ExcelResult readExcelFile(InputStream inputStream,
String fileName, ColumnHeader[] chs) {
ExcelResult result = new ExcelResult();
String ext = fileName.substring(fileName.lastIndexOf(".") + 1);
boolean version2007 = false;
if ("xlsx".equals(ext.toLowerCase())) {
version2007 = true;
}
Workbook workbook = null;
try {
if (version2007)
workbook = new XSSFWorkbook(inputStream);
else
workbook = new HSSFWorkbook(inputStream);
} catch (Exception e) {
result.addError(ExcelErrorEnum.FILE_FORMAT);
return result;
}
result = parseWorkBook(workbook, chs);
return result;
}
private static ExcelResult parseWorkBook(Workbook workbook, ColumnHeader[] chs) {
ExcelResult reulst = new ExcelResult();
if (workbook == null) {
reulst.addError(ExcelErrorEnum.WORKBOOK_NOT_EXIST);
}
for (int num = 0; num < workbook.getNumberOfSheets(); num++) {
Sheet sheet = workbook.getSheetAt(num);
String sheetName = sheet.getSheetName();
if (sheet != null) {
ExcelResult sheetResult = parseSheet(sheet, chs);
if(sheetResult.getErrors().contains(ExcelErrorEnum.SHEET_NOT_DATA)){
continue;
}
reulst.addErrors(sheetResult);
reulst.setContent(sheetName, sheetResult);
}
}
return reulst;
}
@SuppressWarnings("unchecked")
private static ExcelResult parseSheet(Sheet sheet, ColumnHeader[] chs) {
ExcelResult result = new ExcelResult();
Map<String, Integer> allMap = new HashMap<String, Integer>();
Map<String, Integer> headerMap = new HashMap<String, Integer>();
List<Map<String, String>> dataListMap = new ArrayList<Map<String, String>>();
int rows = sheet.getLastRowNum();
if (rows < 1) {
result.addError(ExcelErrorEnum.SHEET_NOT_DATA);
return result;
}
Row headerRow = sheet.getRow(0);
if (headerRow == null
|| headerRow.getLastCellNum() < chs.length) {
result.addError(ExcelErrorEnum.HEADER_LENGTH_NOT_MATCH);
return result;
}
for (int i = 0; i < headerRow.getLastCellNum(); i++) {
Cell headerCell = headerRow.getCell(i);
String header = getStringCellValue(headerCell);
allMap.put(header.toUpperCase(), i);
}
for (ColumnHeader ch : chs) {
Integer index = allMap.get(ch.getName().toUpperCase());
if (index == null) {
result.addError(ExcelErrorEnum.HEADER_NOT_MATCH);
return result;
}else{
headerMap.put(ch.getName().toUpperCase(), index);
}
}
for (int r = 1; r <= rows; r++) {
Row row = sheet.getRow(r);
ExcelResult rowReult = parseRow(headerMap, row, chs);
result.addErrors(rowReult);
Map<String, String> rowValues = (Map<String, String>) rowReult
.getContent();
dataListMap.add(rowValues);
}
result.setContent(ExcelResult.HEADER_KEY, headerMap);
result.setContent(dataListMap);
return result;
}
private static ExcelResult parseRow(Map<String, Integer> headerMap,
Row row, ColumnHeader[] chs) {
ExcelResult result = new ExcelResult();
Map<String, String> rowValues = new HashMap<String, String>();
for (ColumnHeader ch : chs) {
Integer index = headerMap.get(ch.getName());
Cell cell = row.getCell(index);
if (cell != null) {
rowValues.put(ch.getName(), getStringCellValue(cell));
}else{
result.addError(ExcelErrorEnum.CELL_NOT_EXIST);
return result;
}
}
result.setContent(rowValues);
return result;
}
/**
* be able to read as string value from a non-string type cell.
* Every cell value transform to string format.
*
* @param cell
* @return
*/
private static String getStringCellValue(Cell cell) {
if (cell == null) {
return "";
} else {
Object ret = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA://formula
switch (cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_STRING:
ret = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
ret = new Double(cell.getNumericCellValue());
break;
default:
ret = cell.getStringCellValue();
}
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
ret = cell.getDateCellValue();
} else {
ret = new Double(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
ret = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK:// blank
ret = "";
break;
case Cell.CELL_TYPE_ERROR:
ret = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
ret = new Boolean(cell.getBooleanCellValue());
break;
default:
try {
ret = cell.getStringCellValue();
} catch (Exception e) {
ret = "";
}
}
return ret.toString().trim();
}
}
}
this is the read process. not expanded, but you can ,just read the first line as header, via you designed Columnheader to read xls. and return as the definetion as ExcelResult.
package ycl.learn.excel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import ycl.learn.excel.ExcelHeader.ColumnHeader;
import ycl.learn.excel.ExcelHeader.UserHeader;
public class TestExcelReader {
private static final String UPLOAD_FILE = "C:\\Documents and Settings\\e557400\\workspace\\YclPlmTest\\UserHeader.xls";
private static final String UPLOAD_PHONE_FILE = "C:\\Documents and Settings\\e557400\\workspace\\YclPlmTest\\PhoneHeader.xls";
private static final String UPLOAD_PHONE_FILE_2007 = "C:\\Documents and Settings\\e557400\\workspace\\YclPlmTest\\PhoneHeader2007.xlsx";
private static final String FILE_NAME="UserHeader.xls";
private static final String FILE_NAME_PHONE="PhoneHeader.xls";
private static final String FILE_NAME_PHONE_2007="PhoneHeader2007.xlsx";
public static void main(String args[]) throws FileNotFoundException{
//testUserHeader();
testPhoneHeader();
}
public static void testUserHeader() throws FileNotFoundException{
InputStream is = new FileInputStream(UPLOAD_FILE);
ExcelReader reader = new ExcelReader();
ColumnHeader[] ch = ExcelHeader.UserHeader.values();
ExcelResult result = reader.readExcelFile(is,FILE_NAME,ch);
if(result.errorsExist()){
System.out.println(result.getErrors());
return;
}
for(Map.Entry<String, Object> entry :result.getContentMap().entrySet()){
String sheetName = entry.getKey();
System.out.println("sheet:"+sheetName);
ExcelResult sheetResult = (ExcelResult) entry.getValue();
Map<String, Integer> headerMap = (Map<String, Integer>) sheetResult.getContent(ExcelResult.HEADER_KEY);
for(Map.Entry<String, Integer> headerentry :headerMap.entrySet()){
System.out.print(headerentry.getKey()+"||");
}
System.out.println();
List<Map<String, String>> dataListMap = (List<Map<String, String>>) sheetResult.getContent();
for(Map<String, String> dataMap:dataListMap){
for(Map.Entry<String, String> dataerentry :dataMap.entrySet()){
System.out.print(dataerentry.getValue()+"||");
}
System.out.println();
}
}
}
/**
* you successed that just modify the header, you can match any other xls.
* you successed that just get some columns in xls, not all
* you successed that get 1997-2007 's xls reader.
* @throws FileNotFoundException
*/
public static void testPhoneHeader() throws FileNotFoundException{
InputStream is = new FileInputStream(UPLOAD_PHONE_FILE_2007);
ExcelReader reader = new ExcelReader();
ColumnHeader[] ch = ExcelHeader.PhoneHeader.values();
ExcelResult result = reader.readExcelFile(is,FILE_NAME_PHONE_2007,ch);
if(result.errorsExist()){
System.out.println(result.getErrors());
return;
}
for(Map.Entry<String, Object> entry :result.getContentMap().entrySet()){
String sheetName = entry.getKey();
System.out.println("sheet:"+sheetName);
ExcelResult sheetResult = (ExcelResult) entry.getValue();
Map<String, Integer> headerMap = (Map<String, Integer>) sheetResult.getContent(ExcelResult.HEADER_KEY);
for(Map.Entry<String, Integer> headerentry :headerMap.entrySet()){
System.out.print(headerentry.getKey()+"||");
}
System.out.println();
List<Map<String, String>> dataListMap = (List<Map<String, String>>) sheetResult.getContent();
for(Map<String, String> dataMap:dataListMap){
for(Map.Entry<String, String> dataerentry :dataMap.entrySet()){
System.out.print(dataerentry.getValue()+"||");
}
System.out.println();
}
}
}
}
this is the testCase. just modified the xls file and column definetion, can read with different format xls.