Java tutorial
/******************************************************************************* * Copyright (c) 2011,2013 IBM Corporation. * * All rights reserved. This program and the accompanying materials * are made available under the terms of the Eclipse Public License v1.0 * and Eclipse Distribution License v. 1.0 which accompanies this distribution. * * The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html * and the Eclipse Distribution License is available at * http://www.eclipse.org/org/documents/edl-v10.php. * * Contributors: * * Masaki Wakao * Yoshio Horiuchi * Kohji Ohsawa *******************************************************************************/ package org.eclipse.lyo.samples.excel.adapter.dao.internal; import java.io.FileInputStream; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import java.util.Set; import org.apache.commons.lang.time.FastDateFormat; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Name; 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.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.AreaReference; import org.apache.poi.ss.util.CellReference; import org.eclipse.lyo.samples.excel.adapter.MapperEntry; import org.eclipse.lyo.samples.excel.adapter.MapperTable; import org.eclipse.lyo.samples.excel.adapter.dao.ExcelDao; import org.eclipse.lyo.samples.excel.common.ConfigSingleton; import com.hp.hpl.jena.rdf.model.Literal; import com.hp.hpl.jena.rdf.model.Model; import com.hp.hpl.jena.rdf.model.ModelFactory; import com.hp.hpl.jena.rdf.model.Property; import com.hp.hpl.jena.rdf.model.Resource; import com.hp.hpl.jena.vocabulary.RDF; public class ExcelDaoImpl implements ExcelDao { //TODO private static final String DEFAULT_SHEET_NAME = "defects"; private final static String DEFAULT_OUTPUT_DATE_FORMAT = "yyyy-MM-dd'T'HH:mm:ss.SSSZZ"; private String relationshipUri = null; private MapperTable mapperTable = null; public void setRelationshipUri(String relationshipUri) { this.relationshipUri = relationshipUri; } public void setMapperTable(MapperTable mapperTable) { this.mapperTable = mapperTable; } public Model parseFile(String fileName) { if (relationshipUri == null) { return null; } FileInputStream in = null; Workbook wb = null; try { in = new FileInputStream(fileName); wb = WorkbookFactory.create(in); } catch (Exception e) { e.printStackTrace(); } finally { try { in.close(); } catch (Exception e) { e.printStackTrace(); } } Model model = ModelFactory.createDefaultModel(); model.setNsPrefixes(ConfigSingleton.getInstance().getNsPrefixes()); HashMap<Sheet, Object[]> sheetResourceMap = new HashMap<Sheet, Object[]>(); // Loop for Resources defined in Mapper file for (String en : mapperTable.getNameList()) { MapperEntry e = mapperTable.getEntry(en); String type = e.getType(); String line = e.getLine(); String uri = e.getUri(); // parse line definition in Mapper file String[] ls = line.split(","); if (ls.length < 3) { System.err.println("line must has at least sheet, start row, and end row information"); continue; } String ssheet = ls[0].trim(); String sstart = ls[1].trim(); String send = ls[2].trim(); Sheet sheet = null; try { sheet = wb.getSheetAt(Integer.parseInt(ssheet)); } catch (NumberFormatException ex) { sheet = wb.getSheet(ssheet); } if (sheet == null) { System.err.println("target sheet is not found"); continue; } int start = Integer.parseInt(sstart); int end = sheet.getLastRowNum(); if (!send.equals("*")) { end = Integer.parseInt(send); } String cond_cellstring = null; boolean exist = true; if (ls.length > 3) { String scond = ls[3].trim(); if (scond.startsWith("exist")) { cond_cellstring = scond.substring(6, scond.length() - 1).trim(); } else if (scond.startsWith("notexist")) { exist = false; cond_cellstring = scond.substring(9, scond.length() - 1).trim(); } } // map to find referenced resource later Object[] resourceMap = sheetResourceMap.get(sheet); if (resourceMap == null) { resourceMap = new Object[sheet.getLastRowNum() + 1]; Arrays.fill(resourceMap, null); sheetResourceMap.put(sheet, resourceMap); } // Loop of excel table rows to find the resource for (int j = start; j <= end; j++) { if (sheet.getRow(j) == null) { continue; } if (cond_cellstring != null) { Cell cell = getCell(sheet, cond_cellstring, j); String value = getCellValue(cell); if (value == null && exist || value != null && !exist) { continue; } } // generate URI for this resource String[] uris = uri.split(","); String format = uris[0].trim(); String uriString = format; if (uris.length == 3) { Cell cell = getCell(sheet, uris[1].trim(), j); String value1 = getCellValue(cell); cell = getCell(sheet, uris[2].trim(), j); String value2 = getCellValue(cell); uriString = String.format(format, value1, value2); } else if (uris.length == 2) { Cell cell = getCell(sheet, uris[1].trim(), j); String value = getCellValue(cell); uriString = String.format(format, value); } // create a Resource in RDF model with URI and resource type defined in Mapper file Resource resource = null; try { resource = model.createResource(relationshipUri + URLEncoder.encode(uriString, "UTF-8")); type = getNameUri(type.trim(), model); resource.addProperty(RDF.type, model.createResource(type)); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } if (resource == null) { continue; } // Keep resource map for current row which will be used to generate reference URI later Map<String, Resource> curResMap = (Map<String, Resource>) resourceMap[j]; if (curResMap == null) { curResMap = new HashMap<String, Resource>(); resourceMap[j] = curResMap; } curResMap.put(en, resource); // Loop for Properties for this resource defined in Mapper file for (String propName : e.getPropertyNameList()) { MapperEntry.Property prop = e.getProperty(propName); if (prop == null) { continue; } String propType = prop.getType(); if (propType == null) { continue; } if (propType.equalsIgnoreCase("resource")) { // assume that prop contains "reference" information in Mapper file String reference = prop.getReference(); if (reference != null) { processReference(model, resource, propName, reference, resourceMap, j); } } else { // assume that prop contains "column" information in Mapper file String[] tokens = prop.getColumn().trim().split(","); String fmt = null; String column = tokens[0]; if (tokens.length > 1) { fmt = tokens[0]; column = tokens[1]; } Cell cell = getCell(sheet, column, j); if (cell != null) { String value = getCellValue(cell); if (value != null) { if (fmt != null) { value = String.format(fmt, value); } String qpname = propName.trim(); qpname = getNameUri(qpname, model); Property property = model.createProperty(qpname); Literal literal = model.createLiteral(value); resource.addLiteral(property, literal); } } } } } } return model; } private Cell getCell(Sheet sheet, String cellRowString, int defaultRowIndex) { Cell cell = getNamedCell(sheet, cellRowString, defaultRowIndex); if (cell != null) { return cell; } int[] index = cellRowStringToIndex(cellRowString); int rowIndex = (index.length > 1) ? index[1] : defaultRowIndex; Row row = sheet.getRow(rowIndex); if (row != null) { return row.getCell(index[0]); } return null; } private Cell getNamedCell(Sheet sheet, String cellRowString, int defaultRowIndex) { Name name = sheet.getWorkbook().getName(cellRowString); if (name != null) { AreaReference areaRef = new AreaReference(name.getRefersToFormula()); CellReference firstCell = areaRef.getFirstCell(); CellReference lastCell = areaRef.getLastCell(); int rowIndex = defaultRowIndex; if (rowIndex < firstCell.getRow() || lastCell.getRow() < rowIndex) { rowIndex = firstCell.getRow(); } Row row = sheet.getRow(rowIndex); if (row != null) { return row.getCell(firstCell.getCol()); } } return null; } private int[] cellRowStringToIndex(String cellRowString) { int index = -1; for (int i = 0; i < cellRowString.length(); i++) { char c = cellRowString.charAt(i); if (Character.isDigit(c)) { index = i; break; } } if (index <= 0) { // No digit, or digit only. // The string only has a cell index in this case. return new int[] { cellStringToIndex(cellRowString) }; } // The string has both cell and row indices. String cellString = cellRowString.substring(0, index); String rowString = cellRowString.substring(index); return new int[] { cellStringToIndex(cellString), Integer.parseInt(rowString) }; } private int cellStringToIndex(String cellString) { try { return Integer.parseInt(cellString); } catch (NumberFormatException ex) { return CellReference.convertColStringToIndex(cellString); } } private void processReference(Model model, Resource resource, String propName, String referenceDef, Object[] resourceMaps, int currentRow) { referenceDef = referenceDef.trim(); int suffixIndex; // sameLine suffixIndex = referenceDef.toLowerCase().indexOf("[sameline]"); if (suffixIndex > 0) { String targetResourceName = referenceDef.substring(0, suffixIndex); Map<String, Resource> resMap = (Map<String, Resource>) resourceMaps[currentRow]; addReferenceProperty(model, resource, resMap, propName, targetResourceName); } // mostRecent suffixIndex = referenceDef.toLowerCase().indexOf("[mostrecent]"); if (suffixIndex > 0) { String targetResourceName = referenceDef.substring(0, suffixIndex); for (int i = currentRow; i >= 0; i--) { // mostrecent includes sameline Map<String, Resource> resMap = (Map<String, Resource>) resourceMaps[i]; if (addReferenceProperty(model, resource, resMap, propName, targetResourceName)) break; } } } private boolean addReferenceProperty(Model model, Resource resource, Map<String, Resource> resourceMap, String propName, String target) { if (resourceMap != null) { Resource targetResource = resourceMap.get(target); if (targetResource != null) { String qpname = propName.trim(); qpname = getNameUri(qpname, model); Property property = model.createProperty(qpname); resource.addProperty(property, targetResource); String backLinkUri = ConfigSingleton.getInstance().getBacklinks().get(qpname); if (backLinkUri != null) { Property backLinkProperty = model.createProperty(backLinkUri); targetResource.addProperty(backLinkProperty, resource); } return true; } } return false; } private String getCellValue(Cell cell) { if (cell != null) { String value = null; int type = cell.getCellType(); if (type == Cell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } else if (type == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); value = FastDateFormat.getInstance(DEFAULT_OUTPUT_DATE_FORMAT).format(date); } else { double d = cell.getNumericCellValue(); if (d == Math.floor(d)) { // need to consider when d is negative value = "" + (int) d; } else { value = "" + cell.getNumericCellValue(); } } } else if (type == Cell.CELL_TYPE_FORMULA) { // get calculated value if the cell type is formula Workbook wb = cell.getSheet().getWorkbook(); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator(); // get recursively if the value is still formula value = getCellValue(evaluator.evaluateInCell(cell)); } return value; } return null; } private String getNameUri(String name, Model model) { Map<String, String> prefixMapping = model.getNsPrefixMap(); Set<String> keys = prefixMapping.keySet(); Iterator<String> ite = keys.iterator(); String prefix = null; while (ite.hasNext()) { String key = ite.next(); if (name.startsWith(key + ":")) { prefix = key; } } if (prefix != null) { String uri = model.getNsPrefixURI(prefix); return name.replaceFirst(prefix + ":", uri); } if (name.startsWith("dcterms:")) { return name.replaceFirst("dcterms:", "http://purl.org/dc/terms/"); } if (name.startsWith("dc:")) { return name.replaceFirst("dc:", "http://purl.org/dc/terms/"); } return name; } @Override public int getNewId(String fileName) { HSSFWorkbook workBook = new HSSFWorkbook(); try { FileInputStream in = new FileInputStream(fileName); workBook = (HSSFWorkbook) WorkbookFactory.create(in); in.close(); } catch (IOException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } HSSFSheet sheet = workBook.getSheet(DEFAULT_SHEET_NAME); return sheet.getLastRowNum() + 1; } }