Java tutorial
/** * Copyright 2008-2015 Qualogy Solutions B.V. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.qualogy.qafe.service; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStreamReader; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.poi.poifs.filesystem.OfficeXmlFileException; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; 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.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import au.com.bytecode.opencsv.CSVReader; import com.qualogy.qafe.core.datastore.DataToLogStringBuilder; import com.qualogy.qafe.service.domain.DocumentExportInput; import com.qualogy.qafe.service.domain.DocumentExportOutput; import com.qualogy.qafe.service.domain.DocumentOutput; import com.qualogy.qafe.service.domain.DocumentParameter; import com.qualogy.qafe.util.UUIDHelper; public class DocumentServiceImpl implements DocumentService { private static final Logger LOG = Logger.getLogger(DocumentServiceImpl.class.getName()); public static String DEFAULT_FIELD_NAME = "FIELD"; public static int EMPTY_NUMCOLUMNS_TOLERANCE = 10; public DocumentOutput processExcelUpload(DocumentParameter parameter) { DocumentOutput out = null; try { out = handleExcel2003(parameter); } catch (OfficeXmlFileException e) { out = handleExcel2007(parameter); } catch (IOException e) { if (canHandleCSV(e)) { out = handleCSV(parameter); } else { LOG.log(Level.FINE, e.getMessage(), e); throw new RuntimeException(e); } } // store the sequence of the columns, // this information will lose when calling through the webservice out.setColumnSequence(getSequence(out.getData())); return out; } private boolean canHandleCSV(Exception e) { if (e != null) { if (e.getMessage().contains("Invalid header signature;")) { return true; } else if (e.getMessage().contains("Unable to read entire header;")) { // When plain text files are smaller than 32 bytes, // an exception will be thrown in the HeaderBlockReader class (calling from the constructor of // POIFSFileSystem): // Exception: "Unable to read entire header; <number> bytes read; expected 32 bytes" return true; } } return false; } private DocumentOutput handleCSV(DocumentParameter parameter) { DocumentOutput out = null; String uuid = UUIDHelper.generateUUID(); try { CSVReader reader = null; if (parameter.getDelimiter() != null) { char separator = parameter.getDelimiter().length() > 0 ? parameter.getDelimiter().charAt(0) : ','; reader = new CSVReader(new InputStreamReader(new ByteArrayInputStream(parameter.getData())), separator); } else { reader = new CSVReader(new InputStreamReader(new ByteArrayInputStream(parameter.getData()))); } List<String[]> sheetData = reader.readAll(); out = handleCSVData(sheetData, parameter.isFirstFieldHeader()); } catch (Exception e) { LOG.log(Level.FINE, e.getMessage(), e); out = new DocumentOutput(); } out.setUuid(uuid); return out; } // private DocumentOutput handleCSV(DocumentParameter parameter) { // DocumentOutput out = new DocumentOutput(); // String uuid = null; // CSVReader reader; // // Apparently a CSV file // if (parameter.getDelimiter() != null) { // char separator = parameter.getDelimiter().length() > 0 ? parameter.getDelimiter().charAt(0) : ','; // reader = new CSVReader(new InputStreamReader(new ByteArrayInputStream(parameter.getData())), // separator); // } else { // reader = new CSVReader(new InputStreamReader(new ByteArrayInputStream(parameter.getData()))); // } // try { // List<String[]> entries = reader.readAll(); // List<Map<String, String>> data = new ArrayList<Map<String, String>>(); // // List<String> keys = new ArrayList<String>(); // if (parameter.isFirstFieldHeader()) { // if (entries.iterator().hasNext()) { // String[] row = entries.iterator().next(); // for (int i = 0; i < row.length; i++) { // String cell = row[i]; // if (cell != null && cell.trim().length() > 0) { // keys.add(cell); // } // } // } // } else { // we define fields for the data: // if (entries.iterator().hasNext()) { // String[] row = entries.iterator().next(); // int index = 0; // for (int i = 0; i < row.length; i++) { // String cell = row[i]; // if (cell != null && cell.trim().length() > 0) { // keys.add(cell.trim()); // } // } // } // } // // boolean readFirstRow = false; // Iterator<String[]> rit = entries.iterator(); // while (rit.hasNext()) { // String[] row = rit.next(); // if (parameter.isFirstFieldHeader() && !readFirstRow) { // readFirstRow = true; // // } else { // int index = 0; // Map<String, String> dataMap = new TreeMap<String, String>(); // boolean rowWithData = false; // int i = 0; // for (String key : keys) { // String cell = row[i]; // // if (cell != null && cell.trim().length() > 0) { // rowWithData = true; // // } // dataMap.put(keys.get(index), cell != null ? cell.trim() : cell); // index++; // i++; // } // // if (rowWithData) { // data.add(dataMap); // } // } // } // uuid = UUIDHelper.generateUUID(); // printData(data); // out.setData(data); // out.setUuid(uuid); // // } catch (IOException e1) { // logger.debug(e1.getMessage(), e1); // } // out.setUuid(uuid); // return out; // // } private DocumentOutput handleExcel2007(DocumentParameter parameter) { DocumentOutput out = null; String uuid = UUIDHelper.generateUUID(); try { XSSFWorkbook workBook = new XSSFWorkbook(new ByteArrayInputStream(parameter.getData())); XSSFSheet sheet = workBook.getSheetAt(0); out = handleExcelData(sheet, parameter.isFirstFieldHeader()); } catch (Exception e) { LOG.log(Level.FINE, e.getMessage(), e); out = new DocumentOutput(); } out.setUuid(uuid); return out; } // private DocumentOutput handleExcel2007(DocumentParameter parameter) { // DocumentOutput out = new DocumentOutput(); // String uuid = null; // XSSFWorkbook workBook; // try { // workBook = new XSSFWorkbook(new ByteArrayInputStream(parameter.getData())); // uuid = UUIDHelper.generateUUID(); // XSSFSheet sheet = workBook.getSheetAt(0); // List<Map<String, String>> data = new ArrayList<Map<String, String>>(); // // List<String> keys = new ArrayList<String>(); // if (parameter.isFirstFieldHeader()) { // if (sheet.rowIterator().hasNext()) { // Row row = sheet.rowIterator().next(); // for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { // Cell cell = cit.next(); // if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { // keys.add(getCellValue(cell)); // } // } // } // } else { // we define fields for the data: // if (sheet.rowIterator().hasNext()) { // Row row = sheet.rowIterator().next(); // int index = 0; // for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { // Cell cell = cit.next(); // if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { // keys.add("FIELD" + index); // index++; // } // } // } // } // // boolean readFirstRow = false; // for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { // Row row = rit.next(); // if (parameter.isFirstFieldHeader() && !readFirstRow) { // readFirstRow = true; // // } else { // int index = 0; // Map<String, String> dataMap = new TreeMap<String, String>(); // boolean rowWithData = false; // for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { // Cell cell = cit.next(); // if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { // String value = getCellValue(cell); // if (value != null || value.length() > 0) { // rowWithData = true; // } // dataMap.put(keys.get(index), value); // index++; // } // } // if (rowWithData) { // data.add(dataMap); // } // } // } // printData(data); // out.setData(data); // out.setUuid(uuid); // // } catch (IOException e1) { // // TODO Auto-generated catch block // logger.debug(e1.getMessage(), e1); // } // out.setUuid(uuid); // return out; // } private DocumentOutput handleExcel2003(DocumentParameter parameter) throws IOException { DocumentOutput out = null; String uuid = UUIDHelper.generateUUID(); POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(parameter.getData())); Workbook workbook = WorkbookFactory.create(fs); Sheet sheet = workbook.getSheetAt(0); out = handleExcelData(sheet, parameter.isFirstFieldHeader()); out.setUuid(uuid); return out; } // private DocumentOutput handleExcel2003(DocumentParameter parameter) throws IOException { // DocumentOutput out = new DocumentOutput(); // String uuid = null; // POIFSFileSystem fs; // // fs = new POIFSFileSystem(new ByteArrayInputStream(parameter.getData())); // uuid = UUIDHelper.generateUUID(); // Workbook wb = WorkbookFactory.create(fs); // Sheet sheet = wb.getSheetAt(0); // List<Map<String, String>> data = new ArrayList<Map<String, String>>(); // // List<String> keys = new ArrayList<String>(); // if (parameter.isFirstFieldHeader()) { // if (sheet.rowIterator().hasNext()) { // Row row = sheet.rowIterator().next(); // for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { // Cell cell = cit.next(); // if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { // keys.add(getCellValue(cell)); // } // } // } // } else { // we define fields for the data: // if (sheet.rowIterator().hasNext()) { // Row row = sheet.rowIterator().next(); // int index = 0; // for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { // Cell cell = cit.next(); // if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { // keys.add("FIELD" + index); // index++; // } // } // } // } // // boolean readFirstRow = false; // for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { // Row row = rit.next(); // if (parameter.isFirstFieldHeader() && !readFirstRow) { // readFirstRow = true; // // } else { // int index = 0; // Map<String, String> dataMap = new TreeMap<String, String>(); // boolean rowWithData = false; // for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { // Cell cell = cit.next(); // if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { // String value = getCellValue(cell); // if (value != null && value.length() > 0) { // rowWithData = true; // } // dataMap.put(keys.get(index), value); // index++; // } // } // if (rowWithData) { // data.add(dataMap); // } // } // } // printData(data); // out.setData(data); // out.setUuid(uuid); // return out; // // } // CHECKSTYLE.OFF: CyclomaticComplexity private DocumentOutput handleExcelData(Sheet sheetData, boolean hasRowHeader) { DocumentOutput docOutput = new DocumentOutput(); // Determine the column names List<String> columnNameList = new ArrayList<String>(); if (sheetData.rowIterator().hasNext()) { Row row = sheetData.rowIterator().next(); int emptyColCountChain = 0; String colName = null; for (Iterator<Cell> itr = row.cellIterator(); itr.hasNext();) { Cell cell = itr.next(); boolean cellHasData = (cell.getCellType() != Cell.CELL_TYPE_BLANK); if (hasRowHeader && cellHasData) { colName = getCellValue(cell); } else { colName = DEFAULT_FIELD_NAME + cell.getColumnIndex(); } columnNameList.add(colName); if (cellHasData) { emptyColCountChain = 0; } else { emptyColCountChain++; } if (emptyColCountChain > EMPTY_NUMCOLUMNS_TOLERANCE) { break; } } } // Get the data from sheet List<Map<String, String>> data = new ArrayList<Map<String, String>>(); boolean[] columnsHaveData = new boolean[columnNameList.size()]; for (Iterator<Row> itr = sheetData.rowIterator(); itr.hasNext();) { Row row = itr.next(); if (hasRowHeader && (row.getRowNum() == 0)) { continue; } Map<String, String> rowData = new LinkedHashMap<String, String>(); boolean rowHasData = false; for (Iterator<Cell> itr2 = row.cellIterator(); itr2.hasNext();) { Cell cell = itr2.next(); if (cell.getColumnIndex() < columnNameList.size()) { String colName = columnNameList.get(cell.getColumnIndex()); String cellValue = null; if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { cellValue = getCellValue(cell); } boolean cellHasData = ((cellValue != null) && (cellValue.length() > 0)); columnsHaveData[cell.getColumnIndex()] = columnsHaveData[cell.getColumnIndex()] || cellHasData; rowHasData = rowHasData || cellHasData; rowData.put(colName, cellValue); } else { break; } } if (rowHasData) { data.add(rowData); } } removeEmptyColumns(columnNameList, data, columnsHaveData); printData(data); docOutput.setData(data); return docOutput; } private DocumentOutput handleCSVData(List<String[]> sheetData, boolean hasRowHeader) { DocumentOutput docOutput = new DocumentOutput(); // Determine the column names List<String> columnNameList = new ArrayList<String>(); if (sheetData.iterator().hasNext()) { String[] row = sheetData.iterator().next(); int emptyColCountChain = 0; String colName = null; for (int i = 0; i < row.length; i++) { String cell = row[i]; boolean cellHasData = (cell != null) && (cell.trim().length() > 0); if (hasRowHeader && cellHasData) { colName = cell; } else { colName = DEFAULT_FIELD_NAME + i; } columnNameList.add(colName); if (cellHasData) { emptyColCountChain = 0; } else { emptyColCountChain++; } if (emptyColCountChain > EMPTY_NUMCOLUMNS_TOLERANCE) { break; } } } // Get the data List<Map<String, String>> data = new ArrayList<Map<String, String>>(); boolean[] columnsHaveData = new boolean[columnNameList.size()]; int rowIndex = 0; for (Iterator<String[]> itr = sheetData.iterator(); itr.hasNext();) { String[] row = itr.next(); if (!hasRowHeader || (rowIndex != 0)) { Map<String, String> rowData = new LinkedHashMap<String, String>(); boolean rowHasData = false; for (int i = 0; i < row.length; i++) { if (i < columnNameList.size()) { String colName = columnNameList.get(i); String cell = row[i]; boolean cellHasData = (cell != null) && (cell.trim().length() > 0); columnsHaveData[i] = columnsHaveData[i] || cellHasData; rowHasData = rowHasData || cellHasData; rowData.put(colName, cell); } else { break; } } if (rowHasData) { data.add(rowData); } } rowIndex++; } removeEmptyColumns(columnNameList, data, columnsHaveData); printData(data); docOutput.setData(data); return docOutput; } // CHECKSTYLE.OFF: CyclomaticComplexity private void removeEmptyColumns(List<String> columnNameList, List<Map<String, String>> data, boolean[] columnsHaveData) { for (int i = 0; i < columnsHaveData.length; i++) { if (!columnsHaveData[i]) { String colName = columnNameList.get(i); for (int j = 0; j < data.size(); j++) { Map<String, String> rowData = data.get(j); rowData.remove(colName); } } } } private void printData(List<Map<String, String>> data) { StringBuilder builder = new StringBuilder(); Map<String, Object> elements = new HashMap<String, Object>(); elements.put("excel_import", data); builder.append("Structure of data:\n"); DataToLogStringBuilder.build(elements, builder); LOG.info(builder.toString()); } String getCellValue(Cell cell) { String value = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: case Cell.CELL_TYPE_STRING: value = cell.toString(); break; // case Cell.CELL_TYPE_NUMERIC: // value = cell.getNumericCellValue() + ""; // break; // case Cell.CELL_TYPE_STRING: // value = cell.getStringCellValue(); // break; case Cell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case Cell.CELL_TYPE_BLANK: ; break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_ERROR: value = cell.getErrorCellValue() + ""; break; } return value != null ? value.trim() : value; } public DocumentExportOutput export(DocumentExportInput input) { DocumentExportOutput de = new DocumentExportOutput(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd-HHmmss"); de.setFileName("export-file-" + sdf.format(new Date()) + "." + getFileNameExtension(input.getExportCode())); de.setMimeType(getMimeType(input.getExportCode())); ByteArrayOutputStream bout = new ByteArrayOutputStream(); if (EXPORT_CSV.equals(input.getExportCode())) { DocumentExporter.exportCVS(input, bout); } else if (EXPORT_EXCEL.equals(input.getExportCode())) { DocumentExporter.exportExcel(input, bout); } else if (EXPORT_PDF.equals(input.getExportCode())) { DocumentExporter.exportPDF(input, bout); } else if (EXPORT_RTF.equals(input.getExportCode())) { DocumentExporter.exportRTF(input, bout); } else if (EXPORT_XML.equals(input.getExportCode())) { DocumentExporter.exportXML(input, bout); } de.setBytes(bout.toByteArray()); return de; } private String getFileNameExtension(String exportCode) { String extension = "txt"; if (EXPORT_CSV.equals(exportCode)) { extension = "txt"; } else if (EXPORT_EXCEL.equals(exportCode)) { extension = "xls"; } else if (EXPORT_PDF.equals(exportCode)) { extension = "pdf"; } else if (EXPORT_RTF.equals(exportCode)) { extension = "rtf"; } else if (EXPORT_XML.equals(exportCode)) { extension = "xml"; } return extension; } private String getMimeType(String exportCode) { String mimeType = "application/octet-stream"; if (EXPORT_CSV.equals(exportCode)) { } else if (EXPORT_EXCEL.equals(exportCode)) { } else if (EXPORT_PDF.equals(exportCode)) { } else if (EXPORT_RTF.equals(exportCode)) { } else if (EXPORT_XML.equals(exportCode)) { } return mimeType; } private List<String> getSequence(Object data) { List<String> sequence = null; if (data instanceof Map) { Map map = (Map) data; sequence = new ArrayList<String>(); Iterator<String> itr = map.keySet().iterator(); while (itr.hasNext()) { String field = itr.next(); sequence.add(field); } } else if (data instanceof List) { List list = (List) data; if (!list.isEmpty()) { Object entry = list.get(0); sequence = getSequence(entry); } } return sequence; } }