Java 类org.apache.poi.xssf.usermodel.XSSFSheet 实例源码

private static void setColumWidth(ExcelFileType type,ExportInfo exportInfo,
        List<Field> availableFields,Sheet sheet) {
    Map<Field, ExportFieldInfo> fieldInfoMap = exportInfo.getFieldInfoMap();
    for(int i = 0 ; i < availableFields.size() ; i++){
        Field field = availableFields.get(i);
        ExportFieldInfo excelFieldInfo = fieldInfoMap.get(field);
        if (excelFieldInfo.getWidth() != null) {
            sheet.setColumnWidth(i, excelFieldInfo.getWidth()*256); 
            continue;
        }
        if(excelFieldInfo.getAutoWidth() != null){
            if(ExcelFileType.XLS == type){
                HSSFSheet hSheet = (HSSFSheet) sheet;
                hSheet.autoSizeColumn(i);
            }else if(ExcelFileType.XLSX == type){
                XSSFSheet xSheet = (XSSFSheet) sheet;
                xSheet.autoSizeColumn(i);
            }
        }
    }
}

@SuppressWarnings("resource")
private List<Map<String,Object>> parseExcel(InputStream stream) throws Exception {
    List<Map<String,Object>> mapList=new ArrayList<Map<String,Object>>();
    XSSFWorkbook wb = new XSSFWorkbook(stream);
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        XSSFSheet sheet = wb.getSheetAt(i);
        if (sheet == null) {
            continue;
        }
        String name = sheet.getSheetName();
        Map<String,Object> map=new HashMap<String,Object>();
        map.put("name",name);
        map.put("data", buildVariables(sheet));
        mapList.add(map);
    }
    return mapList;
}

/**
 * Método que se encarga de leer el libro de excel
 * cuya ruta recibimos en el constructor y devuelve una lista 
 * de ciudadanos
 * @return lista de Usuarios de la base de datos
 */
public List<CitizenDB> readExcelFile(){
    List<CitizenDB> citizens = new ArrayList<CitizenDB>(); 
    // para cada una de las hojas presentes en el documento de excel
    for(int i=0;i < workbook.getNumberOfSheets();i++){
        XSSFSheet sheet = this.workbook.getSheetAt(i);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row;
        int counter = 0;
        //para cada fila de la hoja
        while(rowIterator.hasNext()){
            row = rowIterator.next();
            if (counter > 0) { //omitimos la cabecera (hay que mirar si hay un metodo de la API)
                Iterator<Cell> cellIterator = row.cellIterator();
                int j = 0;
                CitizenDB user = new CitizenDB();
                while (cellIterator.hasNext())  
                    this.insertCitizenField(user, j++, cellIterator.next());    
                user.setPassword(new GenerationPassword().passwordGenerator());
                citizens.add(user);
            }
            counter++;
        }
    }
    return citizens;
}

/**
 * Exports a single sheet to a file
 *
 * @param sheet
 * @throws FactoryConfigurationError
 * @throws XMLStreamException
 * @throws UnsupportedEncodingException
 * @throws FileNotFoundException
 */
private void export(final XSSFSheet sheet, final XMLStreamWriter out)
        throws UnsupportedEncodingException, XMLStreamException, FactoryConfigurationError, FileNotFoundException {
    boolean isFirst = true;
    final Map<String, String> columns = new HashMap<String, String>();
    final String sheetName = sheet.getSheetName();
    System.out.print(sheetName);
    out.writeStartElement("sheet");
    out.writeAttribute("name", sheetName);
    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (isFirst) {
            isFirst = false;
            this.writeFirstRow(row, out, columns);
        } else {
            this.writeRow(row, out, columns);
        }
    }
    out.writeEndElement();
    System.out.println("..");
}

/**
 * Parses an inputstream containin xlsx into an outputStream containing XML
 * 
 * @param inputStream
 *            the source
 * @param outputStream
 *            the result
 * @throws IOException
 * @throws XMLStreamException
 */
public void parse(final InputStream inputStream, final OutputStream outputStream)
        throws IOException, XMLStreamException {
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    XMLStreamWriter out = this.getXMLWriter(outputStream);
    out.writeStartDocument();
    out.writeStartElement("workbook");
    int sheetCount = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
        final XSSFSheet sheet = workbook.getSheetAt(i);
        try {
            this.export(sheet, out);
        } catch (UnsupportedEncodingException | FileNotFoundException | XMLStreamException
                | FactoryConfigurationError e) {
            e.printStackTrace();
        }
    }
    out.writeEndElement();
    out.writeEndDocument();
    out.close();
    workbook.close();
}

private Span getSpan(XSSFSheet sheet,int row ,int column){
    int sheetMergeCount = sheet.getNumMergedRegions(); 
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstColumn = range.getFirstColumn();
        int lastColumn = range.getLastColumn();
        int firstRow = range.getFirstRow();
        if(row == firstRow && column==firstColumn){  
            int lastRow = range.getLastRow();
            int rowSpan=lastRow-firstRow;
            if(rowSpan>0){
                rowSpan++;
            }
            int colSpan=lastColumn-firstColumn;
            if(colSpan>0){
                colSpan++;
            }
            return new Span(rowSpan,colSpan);
        }
    }
    return new Span(0,0);
}

private boolean isMergedRegion(XSSFSheet sheet, int row, int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstColumn = range.getFirstColumn();
        int lastColumn = range.getLastColumn();
        int firstRow = range.getFirstRow();
        int lastRow = range.getLastRow();
        if (row > firstRow && row < lastRow) {
            if (column > firstColumn && column < lastColumn) {
                return true;
            }
        }
    }
    return false;
}

/**
 * 获取Excel2007图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet,
                                                          XSSFWorkbook workbook) {
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
    for (POIXMLDocumentPart dr : sheet.getRelations()) {
        if (dr instanceof XSSFDrawing) {
            XSSFDrawing drawing = (XSSFDrawing) dr;
            List<XSSFShape> shapes = drawing.getShapes();
            for (XSSFShape shape : shapes) {
                XSSFPicture pic = (XSSFPicture) shape;
                XSSFClientAnchor anchor = pic.getPreferredSize();
                CTMarker ctMarker = anchor.getFrom();
                String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
                sheetIndexPicMap.put(picIndex, pic.getPictureData());
            }
        }
    }
    return sheetIndexPicMap;
}

List<ClassifiableText> xlsxToClassifiableTexts(File xlsxFile, int sheetNumber) throws IOException, EmptySheetException {
  if (xlsxFile == null ||
      sheetNumber < 1) {
    throw new IllegalArgumentException();
  }

  try (XSSFWorkbook excelFile = new XSSFWorkbook(new FileInputStream(xlsxFile))) {
    XSSFSheet sheet = excelFile.getSheetAt(sheetNumber - 1);

    // at least two rows
    if (sheet.getLastRowNum() > 0) {
      return getClassifiableTexts(sheet);
    } else {
      throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is empty");
    }
  } catch (IllegalArgumentException e) {
    throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is not found");
  }
}

private List<ClassifiableText> getClassifiableTexts(XSSFSheet sheet) {
  List<Characteristic> characteristics = getCharacteristics(sheet);
  List<ClassifiableText> classifiableTexts = new ArrayList<>();

  // start from second row
  for (int i = 1; i <= sheet.getLastRowNum(); i++) {
    Map<Characteristic, CharacteristicValue> characteristicsValues = getCharacteristicsValues(sheet.getRow(i), characteristics);

    // exclude empty rows
    if (!sheet.getRow(i).getCell(0).getStringCellValue().equals("")) {
      classifiableTexts.add(new ClassifiableText(sheet.getRow(i).getCell(0).getStringCellValue(), characteristicsValues));
    }
  }

  return classifiableTexts;
}

public static void writeXLSXFile() throws IOException {

            // @SuppressWarnings("resource")
            XSSFWorkbook wbObj = new XSSFWorkbook();
            XSSFSheet sheet = wbObj.createSheet(sheetName);
            for (int row = 0; row < tableData.size(); row++) {
                XSSFRow rowObj = sheet.createRow(row);
                rowData = tableData.get(row);
                for (int col = 0; col < rowData.size(); col++) {
                    XSSFCell cell = rowObj.createCell(col);
                    cell.setCellValue(rowData.get(col));
                    logger.info("Writing " + row + " " + col + "  " + rowData.get(col));
                }
            }
            FileOutputStream fileOut = new FileOutputStream(excelFileName);
            wbObj.write(fileOut);
            wbObj.close();
            fileOut.flush();
            fileOut.close();
        }

public static ArrayList readXlsx(String path) throws IOException {
    XSSFWorkbook xwb = new XSSFWorkbook(path);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row;
    String[] cell = new String[sheet.getPhysicalNumberOfRows() + 1];
    ArrayList<String> arrayList = new ArrayList<>();
    for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        cell[i] = "";
        row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
            cell[i] += row.getCell(j).toString();
            cell[i] += " | ";
        }
        arrayList.add(cell[i]);
    }
    return arrayList;
}

public static XSSFRow createWageringHeaderRow(XSSFSheet sheet, int row, int col) {
    XSSFRow headerRow = sheet.createRow(row++);

    col = seedCols(col, headerRow);

    List<String> columns = Arrays.asList("winPayoff", "placePayoff",
            "showPayoff", "totalWpsPool", "doublePayoff", "doublePool", "exactaPayoff",
            "exactaPool", "trifectaPayoff", "trifectaPool", "superfectaPayoff",
            "superfectaPool", "pick3Payoff", "pick3Pool", "pick4Payoff", "pick4Pool",
            "pick5Payoff", "pick5Pool");
    for (String column : columns) {
        CellUtil.createCell(headerRow, col++, column);
    }

    return headerRow;
}

public XSLXSource(Resource resource, XSSFWorkbook workbook) {
    this.resource = resource;
    this.workbook = workbook;
    String fragment = resource.getFragment();
    XSSFSheet sheet;
    if (fragment == null) {
        sheet = workbook.getSheetAt(0);
    } else {
        sheet = workbook.getSheet(fragment);
        if (sheet == null) {
            throw new IllegalArgumentException("Unable to find Sheet named '" + fragment + "'");
        }
    }
    this.sheet = sheet;
    this.rows = this.sheet.iterator();
    if (!rows.hasNext()) {
        throw new IllegalArgumentException(sheet.getSheetName() + " is empty");
    }
    XSSFRow row = nextRow();
    this.firstColumn = row.getFirstCellNum();
    this.headers = readHeaders(row, this.firstColumn);

}

private static String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException {
    StringBuilder sb = new StringBuilder();

    try (InputStream excelStream = new BufferedInputStream(inputStream)) {
        if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files
            POIFSFileSystem excelFS = new POIFSFileSystem(excelStream);
            ExcelExtractor excelExtractor = new ExcelExtractor(excelFS);
            sb.append(excelExtractor.getText());
            excelExtractor.close();
        } else { // New format
            XSSFWorkbook workBook = new XSSFWorkbook(excelStream);
            int numberOfSheets = workBook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workBook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    XSSFRow row = (XSSFRow) rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        XSSFCell cell = (XSSFCell) cellIterator.next();
                        sb.append(cell.toString());
                        sb.append(" ");
                    }
                    sb.append("\n");
                }
                sb.append("\n");
            }
        }
    }

    return sb.toString();
}

/**
 * @param newSheet the sheet to create from the copy.
 * @param sheet the sheet to copy.
 * @param copyStyle true copy the style.
 */
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        XSSFRow srcRow = sheet.getRow(i);
        XSSFRow destRow = newSheet.createRow(i);
        if (srcRow != null) {
            Util.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
   //Util.copyPictures(newSheet,sheet) ;
}

/**
 * 获取Excel2007图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) {
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
    for (POIXMLDocumentPart dr : sheet.getRelations()) {
        if (dr instanceof XSSFDrawing) {
            XSSFDrawing drawing = (XSSFDrawing) dr;
            List<XSSFShape> shapes = drawing.getShapes();
            for (XSSFShape shape : shapes) {
                XSSFPicture pic = (XSSFPicture) shape;
                XSSFClientAnchor anchor = pic.getPreferredSize();
                CTMarker ctMarker = anchor.getFrom();
                String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
                sheetIndexPicMap.put(picIndex, pic.getPictureData());
            }
        }
    }
    return sheetIndexPicMap;
}

/**
 * Read the Excel 2010
 * 
 * @param path
 *            the path of the excel file
 * @return
 * @throws IOException
 */
public static String readXlsx(String path) throws IOException {
    InputStream is = new FileInputStream(path);
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
    StringBuffer sb = new StringBuffer("");
    // Read the Sheet
    for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
        if (xssfSheet == null) {
            continue;
        }
        // Read the Row
        for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            if (xssfRow != null) {
                XSSFCell no = xssfRow.getCell(0);
                XSSFCell name = xssfRow.getCell(1);
                sb.append(no + ":" + name);
                sb.append(";");
            }
        }
    }
    return sb.toString().substring(0, sb.toString().length() - 1);
}

private String createExcel(Context context) throws Exception {

    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;   
    int row = 0;
    XSSFWorkbook wb = new XSSFWorkbook();               
    XSSFSheet sh1 = wb.createSheet( "PDCA Report" );

    row += this.createPdca(wb, sh1, row, context);

       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;

       File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(
            Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "pdca-report.xlsx");
    file = null;
    return oid;
}

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;   
    XSSFWorkbook wb = new XSSFWorkbook();               
    XSSFSheet sh = wb.createSheet();

    BscReportPropertyUtils.loadData();

    int row = this.putDateRange(wb, sh, context);
    this.putCharts(wb, sh, context, row);

       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;

       File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(
            Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "perspectives-dashboard.xlsx");
    file = null;
    return oid;
}

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;   
    XSSFWorkbook wb = new XSSFWorkbook();               
    XSSFSheet sh = wb.createSheet();

    BscReportPropertyUtils.loadData();

    int row = this.putDateRange(wb, sh, context);
    this.putCharts(wb, sh, context, row);

       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;

       File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(
            Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "kpis-dashboard.xlsx");
    file = null;
    return oid;
}

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;   
    XSSFWorkbook wb = new XSSFWorkbook();               
    XSSFSheet sh = wb.createSheet();

    this.putTables(wb, sh, context);

       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;

       File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(
            Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "forecast-analysis.xlsx");
    file = null;
    return oid;
}

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;   
    XSSFWorkbook wb = new XSSFWorkbook();               
    XSSFSheet sh = wb.createSheet();

    BscReportPropertyUtils.loadData();

    int row = this.putDateRange(wb, sh, context);
    this.putCharts(wb, sh, context, row);

       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;

       File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(
            Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "objectives-dashboard.xlsx");
    file = null;
    return oid;
}

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;   
    XSSFWorkbook wb = new XSSFWorkbook();               
    XSSFSheet sh = wb.createSheet();

    this.putTables(wb, sh, context);

       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;

       File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(
            Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "kpis-period-trends.xlsx");
    file = null;
    return oid;
}

public List<String[]> readAllLines(int sheetIndex){
    XSSFSheet sheet = getWorkBook().getSheetAt(sheetIndex);

    List<String[]> rows = CollectionFactory.newArrayList();
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row != null) {
            String[] rowValues = new String[row.getPhysicalNumberOfCells()];
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                rowValues[j] = (row.getCell(j) != null)? getReader().read(row.getCell(j)) : "";
            }
            rows.add(rowValues);
        }
    }
    return rows;
}

public XSSFWorkbook generate(List<Person> persons, List<String> columns) {
    XSSFSheet sheet = (XSSFSheet) createSheet("Document .xlsx");

    int rowIndex = 0;

    addHeadersToSheet(sheet, rowIndex, columns);
    rowIndex++;

    for (Person person : persons) {
        XSSFRow currentRow = sheet.createRow(rowIndex);
        rowIndex++;

        int columnIndex = 0;

        for (String column : columns) {
            addCell(currentRow, columnIndex, person, column);
            columnIndex++;
        }
    }

    finalizeSheet(sheet, columns);

    return (XSSFWorkbook) workbook;
}

/** Return a preview of the spreadsheet */
private List<HtmlCell[]> getSheetPreview(XSSFSheet sheet, List<Integer> columnNumbers) {
    final List<HtmlCell[]> result = new ArrayList<>();
    int rowCount = 0;
    for(Row r: sheet) {
        final HtmlCell[] rowArray = new HtmlCell[columnNumbers.size()];
        int colPos = 0;
        for(int c : columnNumbers) {
            rowArray[colPos] = 
                    new HtmlCell(getTrimmedString(r.getCell(c)), 
                            DisplayUtilities.getTdStyle(r.getCell(c), findFont(r.getCell(c))));
            colPos++;
        }
        result.add(rowArray);
        rowCount++;
        if(rowCount > 1000) break;
    }
    return result;
}

/** How many columns are used in this spreadsheet */
private List<Integer> getLiveColumnList() {
    if(xSSFWorkbook == null) {
        LOG.info("no excel spreadsheet has been uploaded");
        return Collections.EMPTY_LIST;
    }

    int count = 0;
    final XSSFSheet sheet = xSSFWorkbook.getSheetAt(0);
    if(sheet == null) {
        LOG.info("Couldn't get a sheet at index 0");
        return Collections.EMPTY_LIST;
    }
    final Set<Integer> result = new HashSet<>();
    for (Row r : sheet) {
        for(Cell c : r) {
            if(c.getCellType() == Cell.CELL_TYPE_BLANK) continue;
            result.add(c.getColumnIndex());
        }
        if(count > 1000) break;
    }
    final List<Integer> l = new ArrayList<>(result.size());
    l.addAll(result);
    return Collections.unmodifiableList(l);
}

/**
 * Writes the data to a cell and returns the cell.
 *
 * @param sheet the sheet to write on
 * @param icol index of column
 * @param irow index of row
 * @param data data to write in cell (String, Date, Boolean, Double, Float, Integer, Number)
 * @return the given cell at icol;irow
 * @see
 */
public Cell writeToCell(XSSFSheet sheet, int icol, int irow, Object data) {
  Cell cell = getCell(sheet, icol, irow);
  // write data
  if (data instanceof Date)
    cell.setCellValue((Date) data);
  else if (data instanceof Boolean)
    cell.setCellValue((Boolean) data);
  else if (data instanceof String)
    cell.setCellValue((String) data);
  else if ((data instanceof Double))
    cell.setCellValue((Double) data);
  else if ((data instanceof Number))
    cell.setCellValue(((Number) data).doubleValue());
  else if ((data instanceof Integer))
    cell.setCellValue(((Integer) data).doubleValue());
  else if ((data instanceof Float))
    cell.setCellValue(((Float) data).doubleValue());
  return cell;
}

/**
 * writes a data[rows][columns] array to the given wb
 * 
 * @param realFilePath
 * @param data
 * @param rowsFirst true: [rows][cols] false [cols][rows]
 */
public void writeDataArrayToSheet(XSSFSheet sheet, double[][] data, int ic, int ir,
    boolean rowsFirst) {
  // write to wb
  for (int r = 0; r < data.length; r++) {
    // all columns
    for (int c = 0; c < data[r].length; c++) {
      if (data[r][c] != Double.NaN) {
        if (rowsFirst)
          writeToCell(sheet, c + ic, r + ir, data[r][c]);
        else
          writeToCell(sheet, r + ic, c + ir, data[r][c]);
      }
    }
  }
}

/**
 * writes regression data to sheets
 * 
 * @param sheet
 * @param reg
 * @param dp
 * @param quantifier
 */
public void writeRegressionToSheet(XSSFSheet sheet, SimpleRegression reg, double[][] dp) {
  //
  writeToCell(sheet, 0, 1, "c = (I-intercept)/slope");
  writeToCell(sheet, 0, 2, "intercept = ");
  writeToCell(sheet, 1, 2, reg.getIntercept());
  writeToCell(sheet, 0, 2, "slope = ");
  writeToCell(sheet, 1, 2, reg.getSlope());
  writeToCell(sheet, 0, 3, "R^2 = ");
  writeToCell(sheet, 1, 3, reg.getRSquare());
  writeToCell(sheet, 0, 3, "R^2 = ");
  // datapoints
  writeToCell(sheet, 4, 0, "regression");
  writeToCell(sheet, 4, 1, "x");
  writeToCell(sheet, 5, 1, "intensity");
  writeDataArrayToSheet(sheet, dp, 4, 2, true);
}

private void fillSheetAsArray(XSSFSheet sheet, ArraySheetLib arraySheet) {
    //
    Iterator<Row> rowIter = sheet.iterator();

    while (rowIter.hasNext()) {
        Row row = rowIter.next();
        Iterator<Cell> cellIter = row.iterator();
        ArrayList<String> rowCells = new ArrayList<String>(); 
        while (cellIter.hasNext()) {
            Cell cell = cellIter.next();
            String cellValue = getCellValueAsString(cell); 
            rowCells.add(cellValue);
        }
        arraySheet.addRow(rowCells.toArray(new String[rowCells.size()]));  
    }
}

public List<List<String>> readWorksheetWithNumber(String fileName, int worksheetNo)
    //Read a specific worksheet within excel spreadsheet, based on the worksheet number
    {
    try {
        FileInputStream file = new FileInputStream(new File(fileName));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook based on worksheet no
        XSSFSheet sheet = workbook.getSheetAt(worksheetNo);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row input_row = rowIterator.next();
            tableExcelFile.add(readWorksheetRow(input_row));
        }
        file.close();
        setReturnMessage(Constants.OK);
    } catch (Exception e) {
        e.printStackTrace();
        setReturnMessage(e.toString());
    }
    return tableExcelFile;
}

public List<List<String>> readWorksheetWithName(String fileName, String worksheetName)
    //Read a specific worksheet within excel spreadsheet, based on the name of the worksheet
    {
    try {
        FileInputStream file = new FileInputStream(new File(fileName));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook based on worksheet name
        XSSFSheet sheet = workbook.getSheet(worksheetName);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row input_row = rowIterator.next();
            tableExcelFile.add(readWorksheetRow(input_row));
        }
        file.close();
        setReturnMessage(Constants.OK);
    } catch (Exception e) {
        e.printStackTrace();
        setReturnMessage(e.toString());
    }
    return tableExcelFile;
}

/**
 * Parse the list of patients from the workbook.
 *
 * @return A list of {@link Patient} objects.
 */
private List<Patient> readPatients() throws DataProviderException {
    XSSFSheet sheet = readRequiredSheet("patient");
    String sheetName = sheet.getSheetName();
    List<Patient> result = new ArrayList<>();
    Iterator<Row> rows = sheet.rowIterator();
    rows.next(); // skip header row
    while (rows.hasNext()) {
        Row row = rows.next();
        Patient patient = new Patient();
        patient.setId(readLongValue(sheetName, row.getCell(0)));
        patient.setFirstName(readStringValue(sheetName, row.getCell(1)));
        patient.setLastName(readStringValue(sheetName, row.getCell(2)));
        patient.setDateOfBirth(readDateValue(sheetName, row.getCell(3)));
        patient.setLanguage(readStringValue(sheetName, row.getCell(4)));
        patient.setMaritalStatus(readStringValue(sheetName, row.getCell(5)));
        patient.setRace(readStringValue(sheetName, row.getCell(6)));
        patient.setGender(readStringValue(sheetName, row.getCell(7)));
        result.add(patient);
    }
    return result;
}

/**
 * Parse the list of providers in the workbook.
 *
 * @return A list of {@link Provider} objects.
 */
private List<Provider> readProviders() throws DataProviderException {
    XSSFSheet sheet = readRequiredSheet("provider");
    String sheetName = sheet.getSheetName();
    List<Provider> result = new ArrayList<>();
    Iterator<Row> rows = sheet.rowIterator();
    rows.next(); // skip header row
    while (rows.hasNext()) {
        Row row = rows.next();
        Provider provider = new Provider();
        provider.setId(readLongValue(sheetName, row.getCell(0)));
        provider.setFirstName(readStringValue(sheetName, row.getCell(1)));
        provider.setLastName(readStringValue(sheetName, row.getCell(2)));
        result.add(provider);
    }
    return result;
}

/**
 * Parse the list of encounters in the workbook.
 *
 * @return A list of {@link Encounter} objects.
 */
private List<Encounter> readEncounters() throws DataProviderException {
    XSSFSheet sheet = readRequiredSheet("encounter");
    String sheetName = sheet.getSheetName();
    List<Encounter> result = new ArrayList<>();
    Iterator<Row> rows = sheet.rowIterator();
    LOGGER.debug("Encounter iterator: {}", Integer.valueOf(rows.hashCode()));
    rows.next(); // skip header row
    while (rows.hasNext()) {
        Row row = rows.next();
        Encounter encounter = new Encounter();
        encounter.setId(readLongValue(sheetName, row.getCell(0)));
        encounter.setPatientId(readLongValue(sheetName, row.getCell(1)));
        encounter.setProviderId(readLongValue(sheetName, row.getCell(2)));
        encounter.setStart(readDateValue(sheetName, row.getCell(3)));
        encounter.setEnd(readDateValue(sheetName, row.getCell(4)));
        encounter.setType(readStringValue(sheetName, row.getCell(5)));
        encounter.setDischargeDisposition(readStringValue(sheetName, row.getCell(6)));
        result.add(encounter);
    }
    return result;
}

/**
 * Parse the list of CPT codes in the workbook.
 *
 * @return A list of {@link CPT} objects.
 */
private List<CPT> readCpts() throws DataProviderException {
    XSSFSheet sheet = this.workbook.getSheet("eCPT");
    List<CPT> result = new ArrayList<>();
    if (sheet != null) {
        String sheetName = sheet.getSheetName();
        Iterator<Row> rows = sheet.rowIterator();
        rows.next(); // skip header row
        while (rows.hasNext()) {
            Row row = rows.next();
            CPT cpt = new CPT();
            cpt.setId(readStringValue(sheetName, row.getCell(0)));
            cpt.setEncounterId(readLongValue(sheetName, row.getCell(1)));
            cpt.setTimestamp(readDateValue(sheetName, row.getCell(2)));
            cpt.setEntityId(readStringValue(sheetName, row.getCell(3)));
            result.add(cpt);
        }
    }
    return result;
}

/**
 * Parse the list of ICD9 Procedure codes present in the workbook.
 *
 * @return A list of {@link Icd9Procedure} objects.
 */
private List<Icd9Procedure> readIcd9Procedures() throws DataProviderException {
    XSSFSheet sheet = this.workbook.getSheet("eICD9P");
    List<Icd9Procedure> result = new ArrayList<>();
    if (sheet != null) {
        String sheetName = sheet.getSheetName();
        Iterator<Row> rows = sheet.rowIterator();
        rows.next(); // skip header row
        while (rows.hasNext()) {
            Row row = rows.next();
            Icd9Procedure procedure = new Icd9Procedure();
            procedure.setId(readStringValue(sheetName, row.getCell(0)));
            procedure.setEncounterId(readLongValue(sheetName, row.getCell(1)));
            procedure.setTimestamp(readDateValue(sheetName, row
                    .getCell(2)));
            procedure.setEntityId(readStringValue(sheetName, row
                    .getCell(3)));
            result.add(procedure);
        }
    }
    return result;
}

/**
 * Parse the list of medications present in the workbook.
 *
 * @return A list of {@link Medication} objects.
 */
private List<Medication> readMedications() throws DataProviderException {
    XSSFSheet sheet = this.workbook.getSheet("eMEDS");
    List<Medication> result = new ArrayList<>();
    if (sheet != null) {
        String sheetName = sheet.getSheetName();
        Iterator<Row> rows = sheet.rowIterator();
        rows.next(); // skip header row
        while (rows.hasNext()) {
            Row row = rows.next();
            Medication medication = new Medication();
            medication.setId(readStringValue(sheetName, row.getCell(0)));
            medication.setEncounterId(readLongValue(sheetName, row
                    .getCell(1)));
            medication.setTimestamp(readDateValue(sheetName, row
                    .getCell(2)));
            medication.setEntityId(readStringValue(sheetName, row
                    .getCell(3)));
            result.add(medication);
        }
    }
    return result;
}