Examples of org.apache.poi.xssf.usermodel.XSSFSheet

org.apache.poi.xssf.usermodel.XSSFSheet
High level representation of a SpreadsheetML worksheet.
Sheets are the central structures within a workbook, and are where a user does most of his spreadsheet work. The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can contain text, numbers, dates, and formulas. Cells can also be formatted.

  //private static Map<String, Pool<Plate<LinkedList<Library>, Library>>> process384PlateInputXLSX(XSSFWorkbook wb, User u, RequestManager manager, MisoNamingScheme<Library> libraryNamingScheme) throws Exception {
  private static Map<String, PlatePool> process384PlateInputXLSX(XSSFWorkbook wb, User u, RequestManager manager, MisoNamingScheme<Library> libraryNamingScheme) throws Exception {
    ((RequestManagerAwareNamingScheme) libraryNamingScheme).setRequestManager(manager);


    List<Sample> samples = new ArrayList<Sample>();
    XSSFSheet sheet = wb.getSheetAt(0);
    int rows = sheet.getPhysicalNumberOfRows();


    XSSFRow glrow = sheet.getRow(1);


    //process global headers
    XSSFCell pairedCell = glrow.getCell(0);
    boolean paired = pairedCell.getBooleanCellValue();
    log.info("Got paired: " + paired);


    XSSFCell platformCell = glrow.getCell(1);
    PlatformType pt = null;
    if (getCellValueAsString(platformCell) != null) {
      pt = PlatformType.get(getCellValueAsString(platformCell));
    }
    if (pt == null) {
      throw new InputFormException("Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'");
    }
    else {
      log.info("Got platform type: " + pt.getKey());
    }


    XSSFCell typeCell = glrow.getCell(2);
    LibraryType lt = null;
    if (getCellValueAsString(typeCell) != null) {
      String[] split = getCellValueAsString(typeCell).split("-");
      String plat = split[0];
      String type = split[1];
      if (getCellValueAsString(platformCell).equals(plat)) {
        lt = manager.getLibraryTypeByDescriptionAndPlatform(type, pt);
      }
      else {
        throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell) + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'");
      }
    }
    if (lt == null) {
      throw new InputFormException("Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'");
    }
    else {
      log.info("Got library type: " + lt.getDescription());
    }


    XSSFCell selectionCell = glrow.getCell(3);
    LibrarySelectionType ls = null;
    if (getCellValueAsString(selectionCell) != null) {
      ls = manager.getLibrarySelectionTypeByName(getCellValueAsString(selectionCell));
    }
    if (ls == null) {
      throw new InputFormException("Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'");
    }
    else {
      log.info("Got library selection type: " + ls.getName());
    }


    XSSFCell strategyCell = glrow.getCell(4);
    LibraryStrategyType lst = null;
    if (getCellValueAsString(strategyCell) != null) {
      lst = manager.getLibraryStrategyTypeByName(getCellValueAsString(strategyCell));
    }
    if (lst == null) {
      throw new InputFormException("Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'");
    }
    else {
      log.info("Got library strategy type: " + lst.getName());
    }


    XSSFCell plateBarcodeCell = glrow.getCell(5);
    String plateBarcode = null;
    if (getCellValueAsString(plateBarcodeCell) != null) {
      plateBarcode = getCellValueAsString(plateBarcodeCell);
    }
    if (plateBarcode == null) {
      throw new InputFormException("Cannot resolve plate barcode from: '" + getCellValueAsString(plateBarcodeCell) + "'");
    }
    else {
      log.info("Got plate barcode: " + plateBarcode);
    }


    //process entries
    Simple384WellPlate libraryPlate = null;
    //Map<String, Pool<Plate<LinkedList<Library>, Library>>> pools = new HashMap<String, Pool<Plate<LinkedList<Library>, Library>>>();
    Map<String, PlatePool> pools = new HashMap<String, PlatePool>();
    for (int ri = 4; ri < rows; ri++) {
      XSSFRow row = sheet.getRow(ri);


      // Ax - plate position
      XSSFCell platePosCell = row.getCell(0);
      String platePos = getCellValueAsString(platePosCell);
      if (platePos != null && libraryPlate == null) {

View Full Code Here


  private static List<Sample> processSampleInputXLSX(XSSFWorkbook wb, User u, RequestManager manager, MisoNamingScheme<Library> libraryNamingScheme) throws Exception {
    ((RequestManagerAwareNamingScheme) libraryNamingScheme).setRequestManager(manager);


    List<Sample> samples = new ArrayList<Sample>();
    XSSFSheet sheet = wb.getSheetAt(0);
    int rows = sheet.getPhysicalNumberOfRows();


    XSSFRow glrow = sheet.getRow(1);


    //process global headers
    XSSFCell pairedCell = glrow.getCell(0);
    boolean paired;
    if (getCellValueAsString(pairedCell) != null) {
      paired = pairedCell.getBooleanCellValue();
      log.info("Got paired: " + paired);
    }
    else {
      throw new InputFormException("'Paired' cell is empty. Please specify TRUE or FALSE.");
    }


    XSSFCell platformCell = glrow.getCell(1);
    PlatformType pt = null;
    if (getCellValueAsString(platformCell) != null) {
      pt = PlatformType.get(getCellValueAsString(platformCell));
    }
    if (pt == null) {
      throw new InputFormException("Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'");
    }
    else {
      log.info("Got platform type: " + pt.getKey());
    }


    XSSFCell typeCell = glrow.getCell(2);
    LibraryType lt = null;
    if (getCellValueAsString(typeCell) != null) {
      String[] split = getCellValueAsString(typeCell).split("-");
      String plat = split[0];
      String type = split[1];
      if (getCellValueAsString(platformCell).equals(plat)) {
        lt = manager.getLibraryTypeByDescriptionAndPlatform(type, pt);
      }
      else {
        throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell) + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'");
      }
    }
    if (lt == null) {
      throw new InputFormException("Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'");
    }
    else {
      log.info("Got library type: " + lt.getDescription());
    }


    XSSFCell selectionCell = glrow.getCell(3);
    LibrarySelectionType ls = null;
    if (getCellValueAsString(selectionCell) != null) {
      ls = manager.getLibrarySelectionTypeByName(getCellValueAsString(selectionCell));
    }
    if (ls == null) {
      throw new InputFormException("Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'");
    }
    else {
      log.info("Got library selection type: " + ls.getName());
    }


    XSSFCell strategyCell = glrow.getCell(4);
    LibraryStrategyType lst = null;
    if (getCellValueAsString(strategyCell) != null) {
      lst = manager.getLibraryStrategyTypeByName(getCellValueAsString(strategyCell));
    }
    if (lst == null) {
      throw new InputFormException("Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'");
    }
    else {
      log.info("Got library strategy type: " + lst.getName());
    }


    //process entries
    Plate<LinkedList<Sample>, Sample> samplePlate = null;
    Map<String, Pool<Dilution>> pools = new HashMap<String, Pool<Dilution>>();


    for (int ri = 4; ri < rows; ri++) {
      XSSFRow row = sheet.getRow(ri);


      // Ax - plate position
      XSSFCell platePosCell = row.getCell(0);
      if (getCellValueAsString(platePosCell) != null && samplePlate == null) {
        //plated samples - process as plate

View Full Code Here

    }
  
  private void updateWbFromContent(XSSFWorkbook wb,List<String> title, List<String[]> datas,
    String sheetName) {
  
    XSSFSheet sheet;
    if (null==sheetName | sheetName.length()<1) {//未指定sheet名称时，自动命名创建
      sheet=wb.createSheet();
    }else{
      if (null!=wb.getSheet(sheetName)) sheetName=sheetName+CommUtils.getRandomStr(5);//已存在同名sheet，添加随机字符串
      sheet=wb.createSheet(sheetName);//使用指定的sheetName
    }
    
    //设置列宽
    sheet.setColumnWidth(0, 15*255);    
    sheet.setColumnWidth(1, 25*255);    
    sheet.setColumnWidth(2, 40*255);   
    sheet.setColumnWidth(3, 40*255);    
    sheet.setColumnWidth(4, 10*255);    
    sheet.setColumnWidth(5, 50*255);    
    sheet.setColumnWidth(6, 15*255);    
    sheet.setColumnWidth(7, 20*255);  
    sheet.setColumnWidth(8, 10*255);    
    //创建第一行
    XSSFRow row=sheet.createRow(0);
    //表头样式
        XSSFFont fonttitle=wb.createFont();
        fonttitle.setItalic(true);
        fonttitle.setFontName("微软雅黑");
        fonttitle.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体
        
            CellStyle styletitle = wb.createCellStyle();
            styletitle.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
            styletitle.setFillPattern(CellStyle.SOLID_FOREGROUND); 
            styletitle.setFont(fonttitle);
            styletitle.setBorderLeft(CellStyle.BORDER_THIN);
            styletitle.setBorderRight(CellStyle.BORDER_THIN);
            styletitle.setBorderTop(CellStyle.BORDER_THIN);
            styletitle.setBorderBottom(CellStyle.BORDER_THIN);
            
      //正文样式
            //
            XSSFFont fontRed=wb.createFont();
            fontRed.setColor(HSSFColor.GREEN.index);
            fontRed.setColor(IndexedColors.RED.getIndex());
            fontRed.setFontName("微软雅黑");
            fontRed.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体
            fontRed.setFontHeightInPoints((short) 11);
        CellStyle styleRed = wb.createCellStyle();
        styleRed.setFont(fontRed);
        styleRed.setBorderLeft(CellStyle.BORDER_THIN);
        styleRed.setBorderRight(CellStyle.BORDER_THIN);
        styleRed.setBorderTop(CellStyle.BORDER_THIN);
        styleRed.setBorderBottom(CellStyle.BORDER_THIN);
            
            XSSFFont fontGreen=wb.createFont();
            fontGreen.setColor(IndexedColors.GREEN.getIndex());
            fontGreen.setFontName("微软雅黑");
            fontGreen.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体
            fontGreen.setFontHeightInPoints((short) 11);
        CellStyle styleGreen = wb.createCellStyle();
        styleGreen.setFont(fontGreen);
        styleGreen.setBorderLeft(CellStyle.BORDER_THIN);
        styleGreen.setBorderRight(CellStyle.BORDER_THIN);
        styleGreen.setBorderTop(CellStyle.BORDER_THIN);
        styleGreen.setBorderBottom(CellStyle.BORDER_THIN);
        
            XSSFFont fontBlack=wb.createFont();
            fontBlack.setColor(IndexedColors.BLACK.getIndex());
            fontBlack.setFontName("Arial");
            fontBlack.setFontHeightInPoints((short) 9);
        CellStyle styleBlack = wb.createCellStyle();
        styleBlack.setFont(fontBlack);
        styleBlack.setBorderLeft(CellStyle.BORDER_THIN);
        styleBlack.setBorderRight(CellStyle.BORDER_THIN);
        styleBlack.setBorderTop(CellStyle.BORDER_THIN);
        styleBlack.setBorderBottom(CellStyle.BORDER_THIN);
    //写入标题
    for (int i = 0; i < title.size(); i++) {
      XSSFCell cell=row.createCell(i, XSSFCell.CELL_TYPE_STRING);
      cell.setCellValue(title.get(i));
      cell.setCellStyle(styletitle);
    }
    
    // 写入内容行
    for (int i = 0; i < datas.size(); i++) {
      String[] rowvalue = datas.get(i);
      XSSFRow nextrow = sheet.createRow(sheet.getLastRowNum() + 1);// 创建一行
      for (int j = 0; j < rowvalue.length; j++) {
        XSSFCell cell = nextrow
            .createCell(j, XSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(new XSSFRichTextString(rowvalue[j]));
        if (rowvalue[j].equals("Fail")) {

View Full Code Here

                // Exports elements and attributes mapped with tables
                if (table!=null) {


                    List<XSSFXmlColumnPr> tableColumns = table.getXmlColumnPrs();


                    XSSFSheet sheet = table.getXSSFSheet();


                    int startRow = table.getStartCellReference().getRow();
                    // In mappings created with Microsoft Excel the first row contains the table header and must be skipped
                    startRow +=1;


                    int endRow = table.getEndCellReference().getRow();


                    for(int i = startRow; i<= endRow; i++) {
                        XSSFRow row = sheet.getRow(i);


                        Node tableRootNode = getNodeByXPath(table.getCommonXpath(),doc.getFirstChild(),doc,true);


                        short startColumnIndex = table.getStartCellReference().getCol();
                        for(int j = startColumnIndex; j<= table.getEndCellReference().getCol();j++) {

View Full Code Here

                // Exports elements and attributes mapped with tables
                if (table!=null) {


                    List<XSSFXmlColumnPr> tableColumns = table.getXmlColumnPrs();


                    XSSFSheet sheet = table.getXSSFSheet();


                    int startRow = table.getStartCellReference().getRow();
                    // In mappings created with Microsoft Excel the first row contains the table header and must be skipped
                    startRow +=1;


                    int endRow = table.getEndCellReference().getRow();


                    for(int i = startRow; i<= endRow; i++) {
                        XSSFRow row = sheet.getRow(i);


                        Node tableRootNode = getNodeByXPath(table.getCommonXpath(),doc.getFirstChild(),doc,true);


                        short startColumnIndex = table.getStartCellReference().getCol();
                        for(int j = startColumnIndex; j<= table.getEndCellReference().getCol();j++) {

View Full Code Here

   */
  public String getText() {
    StringBuffer text = new StringBuffer();


    for(int i=0; i<workbook.getNumberOfSheets(); i++) {
      XSSFSheet sheet = workbook.getSheetAt(i);
      if(includeSheetNames) {
        text.append(workbook.getSheetName(i)).append("\n");
      }


      // Header(s), if present
      if(includeHeadersFooters) {
        text.append(
            extractHeaderFooter(sheet.getFirstHeader())
        );
        text.append(
            extractHeaderFooter(sheet.getOddHeader())
        );
        text.append(
            extractHeaderFooter(sheet.getEvenHeader())
        );
      }


      // Rows and cells
      for (Object rawR : sheet) {
        Row row = (Row)rawR;
        for(Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) {
          Cell cell = ri.next();


          // Is it a formula one?
          if(cell.getCellType() == Cell.CELL_TYPE_FORMULA && formulasNotResults) {
            text.append(cell.getCellFormula());
          } else if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
            text.append(cell.getRichStringCellValue().getString());
          } else {
            XSSFCell xc = (XSSFCell)cell;
            text.append(xc.getRawValue());
          }


          // Output the comment, if requested and exists
            Comment comment = cell.getCellComment();
          if(includeCellComments && comment != null) {
              // Replace any newlines with spaces, otherwise it
              //  breaks the output
              String commentText = comment.getString().getString().replace('\n', ' ');
              text.append(" Comment by ").append(comment.getAuthor()).append(": ").append(commentText);
          }


          if(ri.hasNext())
            text.append("\t");
        }
        text.append("\n");
      }


      // Finally footer(s), if present
      if(includeHeadersFooters) {
        text.append(
            extractHeaderFooter(sheet.getFirstFooter())
        );
        text.append(
            extractHeaderFooter(sheet.getOddFooter())
        );
        text.append(
            extractHeaderFooter(sheet.getEvenFooter())
        );
      }
    }


    return text.toString();

View Full Code Here

      else
      {
        _wb=workbook;
            for ( int i = 0; i < _wb.getNumberOfSheets(); i++ )
            {
                XSSFSheet sheet = _wb.getSheetAt( i );
                createAndRegisterSXSSFSheet( sheet );
            }
      }
    }

View Full Code Here

        }
    }


    XSSFSheet getXSSFSheet(SXSSFSheet sheet)
    {
        XSSFSheet result=_sxFromXHash.get(sheet);
        return result;
    }

View Full Code Here

                while (en.hasMoreElements()) 
                {
                    ZipEntry ze = en.nextElement();
                    zos.putNextEntry(new ZipEntry(ze.getName()));
                    InputStream is = zip.getInputStream(ze);
                    XSSFSheet xSheet=getSheetFromZipEntryName(ze.getName());
                    if(xSheet!=null)
                    {
                        SXSSFSheet sxSheet=getSXSSFSheet(xSheet);
                        InputStream xis = sxSheet.getWorksheetXMLInputStream();
                        try

View Full Code Here

     *
     * @param index of the sheet to remove (0-based)
     */
    public void removeSheetAt(int index)
    {
        XSSFSheet xSheet=_wb.getSheetAt(index);
        _wb.removeSheetAt(index);
        deregisterSheetMapping(xSheet);
    }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.poi.xssf.usermodel.XSSFSheet

at.nhmwien.schema_mapping_tool.schemaReaders.XlsxConverter

br.net.woodstock.rockframework.document.spreadsheet.poi.XSSFSpreadsheetDocumentWriter

br.net.woodstock.rockframework.office.spreadsheet.poi.XSSFSpreadsheetDocumentWriter

cn.baiweigang.qtaf.ift.util.ExportReportExcel

com.darkhonor.rage.libs.dataimport.ImportExcel2007

com.darkhonor.rage.libs.dataimport.ImportExcel2007Test

com.github.dandelion.datatables.extras.export.poi.XlsxExport

com.narirelays.ems.utils.ExcelUtility

com.skymobi.qc.admin.action.CpqaBO

org.apache.poi.hssf.record.formula.FormulaShifter

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.