Java tutorial
/******************************************************************************* * Copyright (c) 2003, 2007 s IT Solutions AT Spardat GmbH . * 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: * s IT Solutions AT Spardat GmbH - initial API and implementation *******************************************************************************/ /* * @(#) $Id: GridPOIAdapter.java 5436 2010-04-14 14:34:18Z gub $ * * * * * */ package at.spardat.xma.mdl.grid; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import at.spardat.enterprise.exc.SysException; /** * This class is used to read spreadsheets from excel files or POI classes and convert them to a {@link IGridWM}. * * @author s2877 */ public class GridPOIAdapter { /** * Reads a spreadsheet from an execl file using POI and transfers the data into the <code>IGridWMServer</code>. * Only the first sheet in the file is copied. * * @param grid the XMA model where to copy the data * @param in an InputStream containing the excel file * @throws SysException if the file could not be read by poi * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges * @throws SysException if the file could not be read and parsed by poi. */ public static List poi2xma(IGridWM grid, InputStream in) { try { POIFSFileSystem fs = new POIFSFileSystem(in); HSSFWorkbook book = new HSSFWorkbook(fs); List errors = poi2xma(grid, book, 0); calcAlignements(grid, book.getSheetAt(0)); return errors; } catch (IOException exc) { // problem dedected by POI and reported in an IOException throw new SysException(exc, ((GridWM) grid).getMessage("poiReadError", exc.getMessage())) .setCode(GridWM.CODE_poiReadError); } catch (Exception exc) { // problem not dedected by POI -> need original exception type throw new SysException(exc, ((GridWM) grid).getMessage("poiReadError", exc.toString())) .setCode(GridWM.CODE_poiReadError); } } /** * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>. * Only the first sheet in the workbook is copied. * @param grid the XMA model where to copy the data * @param book the POI represntation of the data * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges */ public static List poi2xma(IGridWM grid, HSSFWorkbook book) { List errors = poi2xma(grid, book, 0); calcAlignements(grid, book.getSheetAt(0)); return errors; } /** * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>. * Only the sheet on the given sheetIndex is copied. * @param igrid the XMA model where to copy the data * @param book the POI represntation of the data * @param sheetIndex the index of the sheet to copy * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges */ public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) { GridWM grid = (GridWM) igrid; try { List errorList = new ArrayList(); grid.setSheetName(book.getSheetName(sheetIndex)); grid.colors.clear(); grid.initBuildInColors(); short ic = GridWM.HSSF_FIRST_COLOR_INDEX; HSSFPalette palette = book.getCustomPalette(); for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) { grid.colors.add(ic, new GridColor(color.getTriplet())); } grid.fonts.clear(); int numFonts = book.getNumberOfFonts(); if (numFonts > 4) { // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt() numFonts += 1; } for (short i = 0; i < numFonts; i++) { HSSFFont font = book.getFontAt(i); byte fontstyle = GridFont.FONT_NORML; if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD) fontstyle |= GridFont.FONT_BOLD; if (font.getItalic()) fontstyle |= GridFont.FONT_ITALIC; grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor())); } grid.styles.clear(); for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) { HSSFCellStyle style = book.getCellStyleAt(i); grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor())); } grid.namedRanges.clear(); for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) { HSSFName name = book.getNameAt(i); String rangeName = name.getNameName(); String rangeRef = null; try { // ranges not defined but referenced by formulas have a name but no reference in HSSF rangeRef = name.getReference(); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName)) .setCode(GridWM.CODE_inconsistentRange)); } if (rangeRef != null) { try { GridRange range = grid.getJeksDelegate().toRange(rangeRef); range.setKey(rangeName); grid.namedRanges.put(rangeName, range); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef)) .setCode(GridWM.CODE_unsupportedReference)); } } } grid.rows.clear(); grid.cols.clear(); grid.cells.clear(); grid.delegate = new GridJeksDelegate(grid); HSSFSheet sheet = book.getSheetAt(sheetIndex); int firstColNum = Integer.MAX_VALUE; int lastColNum = Integer.MIN_VALUE; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row == null) continue; if (row.getFirstCellNum() >= 0) firstColNum = Math.min(firstColNum, row.getFirstCellNum()); lastColNum = Math.max(lastColNum, row.getLastCellNum()); if (lastColNum > 255) lastColNum = 255; for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { HSSFCell hssfcell = row.getCell(j); if (hssfcell == null) continue; GridCell gridcell = grid.getOrCreateCell(i, j); switch (hssfcell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: gridcell.setValue(hssfcell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: // TODO: recherche error text byte errorCode = hssfcell.getErrorCellValue(); // gridcell.setValue(errorCode); gridcell.setValue("#ERROR"); errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord", grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode))) .setCode(GridWM.CODE_errorRecord)); break; case HSSFCell.CELL_TYPE_FORMULA: String formula = null; try { formula = hssfcell.getCellFormula(); gridcell.setFormula(formula); } catch (SysException e) { if (formula != null) gridcell.setValue("=" + formula); //set it as text without interpretation errorList.add(e); } break; case HSSFCell.CELL_TYPE_NUMERIC: if (isDateCell(book, hssfcell)) { gridcell.setValue(hssfcell.getDateCellValue()); } else { gridcell.setValue(hssfcell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: gridcell.setValue(hssfcell.getStringCellValue()); break; default: throw new SysException("unknown cell type " + hssfcell.getCellType()); } gridcell.setEditable(!hssfcell.getCellStyle().getLocked()); gridcell.setStyle(hssfcell.getCellStyle().getIndex()); } } final int scalefactor = 256 / 7; // empirically testet // int width = sheet.getDefaultColumnWidth(); // returns nonsense // width = width/scalefactor; // grid.setDefaultColumnWidth(width); for (short i = (short) firstColNum; i <= lastColNum; i++) { int width = sheet.getColumnWidth(i); width = width / scalefactor; grid.getOrCreateColumn(i).setWidth(width); } if (firstColNum == Integer.MAX_VALUE) firstColNum = 0; if (lastColNum == Integer.MIN_VALUE) lastColNum = 0; grid.setMaxRange( new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum)); grid.setVisibleRange(grid.getMaxRange()); return errorList; } finally { grid.handle(grid.new GridReloadEvent()); } } // checks if the given cell is formated as date private static boolean isDateCell(HSSFWorkbook book, HSSFCell cell) { if (HSSFDateUtil.isCellDateFormatted(cell)) { // knows only excel buildin date formats return true; } else { short format = cell.getCellStyle().getDataFormat(); String formatString = book.createDataFormat().getFormat(format); if (formatString.indexOf("d") >= 0 && formatString.indexOf("m") >= 0 && formatString.indexOf("yy") >= 0) { return true; // day, month and year are contained -> must be a date } else { return false; } } } /** * Calculates the alignement of each column of the given grid. * In Excel alignements are defined per cell, in SWT alignement are defined per column. * So the alignment for SWT is calculated by choosing the most used alignement of the visible * cells of each column. * * @param igrid the XMA model where to set the alignemnets * @param sheet the POI representation from where to read the alignements */ static public void calcAlignements(IGridWM igrid, HSSFSheet sheet) { GridWM grid = (GridWM) igrid; GridRange range = grid.getVisibleRange(); for (int col = range.getFirstColumn(), lastCol = range.getLastColumn(); col <= lastCol; col++) { GridColumn gridColumn = grid.getOrCreateColumn(col); if (gridColumn.isHidden()) continue; int left = 0, right = 0, center = 0; for (int i = range.getFirstRow(), lastRow = range.getLastRow(); i <= lastRow; i++) { GridRow gridRow = grid.getRow(i); if (gridRow != null && gridRow.isHidden()) continue; HSSFRow hrow = sheet.getRow(i); if (hrow == null) continue; HSSFCell hcell = hrow.getCell((short) col); if (hcell == null) continue; HSSFCellStyle hstyle = hcell.getCellStyle(); if (hstyle == null) continue; switch (hstyle.getAlignment()) { case HSSFCellStyle.ALIGN_CENTER: case HSSFCellStyle.ALIGN_CENTER_SELECTION: center++; break; case HSSFCellStyle.ALIGN_LEFT: case HSSFCellStyle.ALIGN_FILL: case HSSFCellStyle.ALIGN_JUSTIFY: left++; break; case HSSFCellStyle.ALIGN_RIGHT: right++; break; case HSSFCellStyle.ALIGN_GENERAL: switch (hcell.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: center++; break; case HSSFCell.CELL_TYPE_NUMERIC: case 42: // CELL_TYPE_DATE: right++; break; case HSSFCell.CELL_TYPE_STRING: left++; break; case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: case HSSFCell.CELL_TYPE_FORMULA: default: break; } break; default: break; } } if (left >= right && left >= center) { gridColumn.setAlignement(GridColumn.ALIGN_LEFT); } else if (right > left && right >= center) { gridColumn.setAlignement(GridColumn.ALIGN_RIGHT); } else if (center > left && center > right) { gridColumn.setAlignement(GridColumn.ALIGN_CENTER); } } } // moved to GridWM // // see http://sc.openoffice.org/excelfileformat.pdf chapter 5.74 // private static void initBuildInColors(GridWM grid) { // grid.colors.add(0,new GridColor((short)0,(short)0,(short)0)); // grid.colors.add(1,new GridColor((short)255,(short)255,(short)255)); // grid.colors.add(2,new GridColor((short)255,(short)0,(short)0)); // grid.colors.add(3,new GridColor((short)0,(short)255,(short)0)); // grid.colors.add(4,new GridColor((short)0,(short)0,(short)255)); // grid.colors.add(5,new GridColor((short)255,(short)255,(short)0)); // grid.colors.add(6,new GridColor((short)255,(short)0,(short)255)); // grid.colors.add(7,new GridColor((short)0,(short)255,(short)255)); // } // system colors not used, null does the magic in SWT // // see http://sc.openoffice.org/excelfileformat.pdf chapter 5.74 // private static void initSystemColors(GridWMServer grid) { // grid.systemColors.put(new Short((short)0x40),new GridColor((short)0,(short)0,(short)0)); // grid.systemColors.put(new Short((short)0x41),new GridColor((short)255,(short)255,(short)255)); // grid.systemColors.put(new Short((short)0x43),new GridColor((short)128,(short)128,(short)128)); // grid.systemColors.put(new Short((short)0x4D),new GridColor((short)0,(short)0,(short)0)); // grid.systemColors.put(new Short((short)0x4E),new GridColor((short)255,(short)255,(short)255)); // grid.systemColors.put(new Short((short)0x4F),new GridColor((short)0,(short)0,(short)0)); // grid.systemColors.put(new Short((short)0x50),new GridColor((short)255,(short)255,(short)0)); // grid.systemColors.put(new Short((short)0x51),new GridColor((short)0,(short)0,(short)0)); // grid.systemColors.put(new Short((short)0x7fff),new GridColor((short)0,(short)0,(short)0)); // } }