Java tutorial
/* * blanco Framework * Copyright (C) 2004-2009 IGA Tosiki * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. */ /******************************************************************************* * Copyright (c) 2009 IGA Tosiki, NTT DATA BUSINESS BRAINS Corp. * All rights reserved. This program and the accompanying materials * are made available under the terms of the Eclipse Public License v1.0 * which accompanies this distribution, and is available at * http://www.eclipse.org/legal/epl-v10.html * * Contributors: * IGA Tosiki (NTT DATA BUSINESS BRAINS Corp.) - initial API and implementation *******************************************************************************/ package blanco.commons.calc.parser; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import javax.xml.transform.Transformer; import javax.xml.transform.TransformerConfigurationException; import javax.xml.transform.TransformerFactory; import javax.xml.transform.TransformerFactoryConfigurationError; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.xml.sax.ContentHandler; import org.xml.sax.DTDHandler; import org.xml.sax.EntityResolver; import org.xml.sax.ErrorHandler; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.SAXNotRecognizedException; import org.xml.sax.SAXNotSupportedException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.AttributesImpl; import blanco.commons.parser.ContentHandlerStream; /** * Calc?????SAX2?? <br> * ??????SAX2?????? * * @author IGA Tosiki */ public abstract class AbstractBlancoCalcParser implements XMLReader { /** * ???? */ private ContentHandler contentHandler = null; private ContentHandlerStream chainedContentHandler = null; public static final String URI_PROPERTY_NAME_WORKBOOK = "http://blanco/commons/calc/parser/workbook"; public static final String URI_PROPERTY_NAME_SHEET = "http://blanco/commons/calc/parser/sheet"; private String _propertyNameWorkbook = "workbook"; private String _propertyNameSheet = "sheet"; /** * ????? * * @param arg0 * ? * @see org.xml.sax.XMLReader#getFeature(java.lang.String) */ public final boolean getFeature(final String arg0) throws SAXNotRecognizedException, SAXNotSupportedException { return false; } /** * ???? * * @param name * ??? * @param value * ?? * @see org.xml.sax.XMLReader#setFeature(java.lang.String, boolean) */ public final void setFeature(final String name, boolean value) throws SAXNotRecognizedException, SAXNotSupportedException { } /** * ???? */ public final Object getProperty(final String name) throws SAXNotRecognizedException, SAXNotSupportedException { if (name.equals(URI_PROPERTY_NAME_WORKBOOK)) { return _propertyNameWorkbook; } else if (name.equals(URI_PROPERTY_NAME_SHEET)) { return _propertyNameSheet; } else { throw new SAXNotRecognizedException("???????????." + name); } } /** * ??? */ public final void setProperty(final String name, final Object value) throws SAXNotRecognizedException, SAXNotSupportedException { if (name.equals(URI_PROPERTY_NAME_WORKBOOK)) { _propertyNameWorkbook = (String) value; } else if (name.equals(URI_PROPERTY_NAME_SHEET)) { _propertyNameSheet = (String) value; } else { throw new SAXNotRecognizedException("???????????." + name); } } /** * ???? * * @param arg0 * ?? * @see org.xml.sax.XMLReader#setEntityResolver(org.xml.sax.EntityResolver) */ public final void setEntityResolver(final EntityResolver arg0) { } /** * ????? * * @return ?? * @see org.xml.sax.XMLReader#getEntityResolver() */ public final EntityResolver getEntityResolver() { return null; } /** * DTD???? * * @param arg0 * DTD?? * @see org.xml.sax.XMLReader#setDTDHandler(org.xml.sax.DTDHandler) */ public final void setDTDHandler(final DTDHandler arg0) { } /** * DTD?????? * * @return DTD?? * @see org.xml.sax.XMLReader#getDTDHandler() */ public final DTDHandler getDTDHandler() { return null; } /** * ???? */ public final void setContentHandler(ContentHandler arg0) { contentHandler = arg0; if (chainedContentHandler != null) { chainedContentHandler.setContentHandler(arg0); } } /** * ????? */ public final ContentHandler getContentHandler() { if (chainedContentHandler == null) { return contentHandler; } else { return chainedContentHandler; } } public final void chainContentHandlerStream(ContentHandlerStream arg0) { chainedContentHandler = arg0; } /** * ???? * * @param arg0 * ? * @see org.xml.sax.XMLReader#setErrorHandler(org.xml.sax.ErrorHandler) */ public final void setErrorHandler(ErrorHandler arg0) { } /** * ????? * * @return ? * @see org.xml.sax.XMLReader#getErrorHandler() */ public final ErrorHandler getErrorHandler() { return null; } /** * ??? * * @param inputSource * ??? * @see org.xml.sax.XMLReader#parse(org.xml.sax.InputSource) */ public final void parse(final InputSource inputSource) throws IOException, SAXException { System.out.println("AbstractBlancoCalcParser#:parse"); Workbook workbook = null; InputStream inStream = null; try { if (inputSource.getByteStream() != null) { // OK?????????? } else if (inputSource.getSystemId() != null && inputSource.getSystemId().length() > 0) { inStream = new FileInputStream(inputSource.getSystemId()); inputSource.setByteStream(inStream); } else { throw new IOException("??InputSource???????."); } workbook = WorkbookFactory.create(inputSource.getByteStream()); // ???????? parseWorkbook(workbook); } catch (IOException e) { e.printStackTrace(); throw new IOException("???????.: " + e.toString()); } catch (InvalidFormatException e) { e.printStackTrace(); throw new IOException("???????.: " + e.toString()); } finally { if (workbook != null) { workbook.close(); } // InputSource?????? // ???? ????????? if (inStream != null) { inStream.close(); } } } /** * ????? * * ?? inputSource???? * * @param arg0 * ? */ public final void parse(final String arg0) throws IOException, SAXException { InputSource inputSource = new InputSource(arg0); inputSource.setByteStream(new FileInputStream(arg0)); parse(inputSource); } /** * ??? * * @param workbook * * @throws SAXException * SAX???? */ private void parseWorkbook(final Workbook workbook) throws SAXException { getContentHandler().startDocument(); getContentHandler().startElement("", (String) getProperty(URI_PROPERTY_NAME_WORKBOOK), (String) getProperty(URI_PROPERTY_NAME_WORKBOOK), new AttributesImpl()); for (int indexSheet = 0; indexSheet < workbook.getNumberOfSheets(); indexSheet++) { Sheet sheet = workbook.getSheetAt(indexSheet); parseSheet(sheet); } getContentHandler().endElement("", (String) getProperty(URI_PROPERTY_NAME_WORKBOOK), (String) getProperty(URI_PROPERTY_NAME_WORKBOOK)); getContentHandler().endDocument(); } /** * ??? * * @param sheet * * @throws SAXException * SAX???? */ private final void parseSheet(final Sheet sheet) throws SAXException { // ????? AttributesImpl attrImpl = new AttributesImpl(); attrImpl.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); getContentHandler().startElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET), (String) getProperty(URI_PROPERTY_NAME_SHEET), attrImpl); startSheet(sheet.getSheetName()); //getLastRowNum()??0???? +1? int maxRows = sheet.getLastRowNum() + 1; for (int row = 0; row < maxRows; row++) { startRow(row + 1); Row line = sheet.getRow(row); if (line != null) { for (int column = 0; column < line.getLastCellNum(); column++) { startColumn(column + 1); Cell cell = line.getCell(column); // ?trim()?????????????? String value = getCellValue(cell); fireCell(column + 1, row + 1, value); endColumn(column + 1); } } endRow(row + 1); } endSheet(sheet); // ????? getContentHandler().endElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET), (String) getProperty(URI_PROPERTY_NAME_SHEET)); } public static String getCellValue(Cell cell) { // 2016.01.20 j.amano // ?jxl to poi ????? //------------------------ //??:\-1,000 //jxl:($1,000)?$????????? //poi:-1000 //------------------------ //??:2016/1/20 //jxl:0020, 1 20, 2016 //poi:2016/01/20 00:00:00 //------------------------ //??:#REF!??? //jxl:#REF! //poi:#REF! //------------------------ //??:1,000 //jxl:" "1,000 //poi:-1000 //------------------------ if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_NUMERIC: // ?? if (DateUtil.isCellDateFormatted(cell)) { // ???? Date dt = cell.getDateCellValue(); // ???? DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); String sDate = df.format(dt); return sDate; } // ???.0 DecimalFormat format = new DecimalFormat("0.#"); return format.format(cell.getNumericCellValue()); case Cell.CELL_TYPE_FORMULA: Workbook wb = cell.getSheet().getWorkbook(); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator(); return getCellValue(evaluator.evaluateInCell(cell)); case Cell.CELL_TYPE_ERROR: byte errorCode = cell.getErrorCellValue(); FormulaError error = FormulaError.forInt(errorCode); String errorText = error.getString(); return errorText; default: return ""; } } return ""; } /** * ??????? * * @param sheetName * ?? * @throws SAXException * SAX???? */ protected abstract void startSheet(String sheetName) throws SAXException; /** * ??????? * * @param sheet * * @throws SAXException * SAX???? */ protected abstract void endSheet(final Sheet sheet) throws SAXException; protected abstract void startRow(int row) throws SAXException; protected abstract void endRow(int row) throws SAXException; protected abstract void startColumn(int column) throws SAXException; protected abstract void endColumn(int column) throws SAXException; /** * ??????? * * @param column * @param row * @param cellValue * @throws SAXException */ protected abstract void fireCell(int column, int row, String cellValue) throws SAXException; /** * Transformer???? * * @return ? * @throws TransformerFactoryConfigurationError * ?????? * @throws TransformerConfigurationException * ????? */ public static final Transformer getTransformer() throws TransformerFactoryConfigurationError, TransformerConfigurationException { TransformerFactory tf = TransformerFactory.newInstance(); Transformer transformer = tf.newTransformer(); transformer.setOutputProperty("encoding", "UTF-8"); transformer.setOutputProperty("standalone", "yes"); transformer.setOutputProperty("indent", "yes"); return transformer; } }