Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt.

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:com.servoy.extensions.plugins.excelxport.ImportSelectSheetPanel.java

License:Open Source License

public void actionPerformed(ActionEvent event) {
    String command = event.getActionCommand();
    if (command.equals("UseHeaderRows")) //$NON-NLS-1$
    {//from w ww. ja  va  2s  . com
        if (tableModel != null)
            tableModel.setUseHeaderRow(headerRows.isSelected());
    } else if (event.getSource() == sheetSelect) {
        if (sheetSelect.getSelectedItem() != null) {
            HSSFWorkbook wb = (HSSFWorkbook) state.getProperty("workbook"); //$NON-NLS-1$
            HSSFSheet sheet = wb.getSheetAt(wb.getSheetIndex(sheetSelect.getSelectedItem().toString()));
            tableModel = new SheetTableModel(sheet);
            tableModel.setUseHeaderRow(headerRows.isSelected());
            table.setModel(tableModel);
        }
    }
    table.sizeColumnsToFit(JTable.AUTO_RESIZE_ALL_COLUMNS);
    revalidate();
    repaint();
}

From source file:com.servoy.extensions.plugins.excelxport.ImportSelectSheetPanel.java

License:Open Source License

public Runnable needsToRunFirst(final boolean forward) {
    return new Runnable() {
        public void run() {
            try {
                parent.blockGUI(Messages.getString("servoy.plugin.import.status.organizingData")); //$NON-NLS-1$
                if (forward) {
                    HSSFWorkbook wb = (HSSFWorkbook) state.getProperty("workbook"); //$NON-NLS-1$
                    SortedComboModel dcm = new SortedComboModel(StringComparator.INSTANCE);
                    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                        dcm.add(wb.getSheetName(i));
                    }/*from w  w  w .j  a v  a 2s  .c  o  m*/
                    sheetSelect.setModel(dcm);

                    HSSFSheet sheet = wb.getSheetAt(0);//take first default
                    tableModel = new SheetTableModel(sheet);
                    tableModel.setUseHeaderRow(headerRows.isSelected());
                    table.setModel(tableModel);
                }
            } finally {
                parent.releaseGUI();
            }
        }
    };
}

From source file:com.sigueme.frontend.controller.ExportController.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());

    for (Row row : sheet) {
        for (Cell cell : row) {
            cell.setCellValue(cell.getStringCellValue().toUpperCase());
            cell.setCellStyle(style);// w  ww . ja va2s. c  o  m

        }
    }
}

From source file:com.siva.javamultithreading.MultiThreadExecutor.java

public static void main(String[] args) throws ExecutionException, IOException {

    //Populate the data
    List<DomainObject> list = new ArrayList<>();
    DomainObject object = null;//  w w w  .  j a  v a 2 s  .  com
    for (int i = 0; i < 230000; i++) {
        object = new DomainObject();
        object.setId("ID" + i);
        object.setName("NAME" + i);
        object.setComment("COMMENT" + i);
        list.add(object);
    }

    int maxNoOfRows = 40000;
    int noOfThreads = 1;
    int remaining = 0;

    if (list.size() > 40000) {
        noOfThreads = list.size() / maxNoOfRows;
        remaining = list.size() % maxNoOfRows;
        if (remaining > 0) {
            noOfThreads++;
        }
    }

    List<List<DomainObject>> dos = ListUtils.partition(list, maxNoOfRows);

    ExecutorService threadPool = Executors.newFixedThreadPool(noOfThreads);
    CompletionService<HSSFWorkbook> pool = new ExecutorCompletionService<>(threadPool);

    // Excel creation through multiple threads
    long startTime = System.currentTimeMillis();

    for (List<DomainObject> listObj : dos) {
        pool.submit(new ExcelChunkSheetWriter(listObj));
    }

    HSSFWorkbook hSSFWorkbook = null;
    HSSFWorkbook book = new HSSFWorkbook();
    HSSFSheet sheet = book.createSheet("Report");

    try {
        for (int i = 0; i < 5; i++) {
            hSSFWorkbook = pool.take().get();
            System.out.println(
                    "sheet row count : sheet.PhysicalNumberOfRows() = " + sheet.getPhysicalNumberOfRows());
            int currentCount = sheet.getPhysicalNumberOfRows();
            int incomingCount = hSSFWorkbook.getSheetAt(0).getPhysicalNumberOfRows();
            if ((currentCount + incomingCount) > 60000) {
                sheet = book.createSheet("Report" + i);
            }
            ExcelUtil.copySheets(book, sheet, hSSFWorkbook.getSheetAt(0));
        }
    } catch (InterruptedException ex) {
        Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex);
    } catch (ExecutionException ex) {
        Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex);
    }

    try {
        writeFile(book, new FileOutputStream("Report.xls"));
    } catch (Exception e) {
        e.printStackTrace();
    }

    //System.out.println("No of Threads : " + noOfThreads + " Size : " + list.size() + " remaining : " + remaining);
    long endTime = System.currentTimeMillis();
    System.out.println("Time taken: " + (endTime - startTime) + " ms");
    threadPool.shutdown();

    //startProcess();
}

From source file:com.siva.javamultithreading.MultiThreadExecutor.java

/**
 * This is sample.//from  w  ww  . ja v a  2 s.  c  om
 */
private static void startProcess() {

    ExecutorService threadPool = Executors.newFixedThreadPool(4);
    CompletionService<HSSFWorkbook> pool = new ExecutorCompletionService<>(threadPool);
    // Excel creation through multiple threads
    long startTime = System.currentTimeMillis();
    pool.submit(new ExcelChunkSheetWriter(0, 1000));
    pool.submit(new ExcelChunkSheetWriter(1001, 20000));
    pool.submit(new ExcelChunkSheetWriter(2, 3000));
    pool.submit(new ExcelChunkSheetWriter(3, 40000));
    pool.submit(new ExcelChunkSheetWriter(4, 50000));

    HSSFWorkbook hSSFWorkbook = null;
    HSSFWorkbook book = new HSSFWorkbook();
    HSSFSheet sheet = book.createSheet("Report");

    try {
        for (int i = 0; i < 5; i++) {
            hSSFWorkbook = pool.take().get();
            System.out.println(
                    "sheet row count : sheet.PhysicalNumberOfRows() = " + sheet.getPhysicalNumberOfRows());
            int currentCount = sheet.getPhysicalNumberOfRows();
            int incomingCount = hSSFWorkbook.getSheetAt(0).getPhysicalNumberOfRows();
            if ((currentCount + incomingCount) > 60000) {
                sheet = book.createSheet("Report" + i);
            }
            ExcelUtil.copySheets(book, sheet, hSSFWorkbook.getSheetAt(0));
        }
    } catch (InterruptedException ex) {
        Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex);
    } catch (ExecutionException ex) {
        Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex);
    }

    try {
        writeFile(book, new FileOutputStream("Report.xls"));
    } catch (Exception e) {
        e.printStackTrace();
    }

    /*
     FileOutputStream fos = new FileOutputStream("all.zip");
     ZipOutputStream zos = new ZipOutputStream(fos);
     for (int i = 0; i < 5; i++) {
     try {
     hSSFWorkbook = pool.take().get();                
     ZipEntry ze = new ZipEntry("Excel" + i + ".xls");
     zos.putNextEntry(ze);
     hSSFWorkbook.write(zos);
     zos.closeEntry();
     } catch (InterruptedException ex) {
     Logger.getLogger(MultiThreadExecutor.class.getName()).log(Level.SEVERE, null, ex);
     }           
     }
     zos.close();
     */
    long endTime = System.currentTimeMillis();
    System.out.println("Time taken: " + (endTime - startTime) + " ms");
    threadPool.shutdown();
}

From source file:com.smanempat.controller.ControllerClassification.java

private void showXLS(JTextField txtFileDirectory, JTable tablePreview)
        throws FileNotFoundException, IOException {
    DefaultTableModel tableModel = new DefaultTableModel();
    File fileName = new File(txtFileDirectory.getText());
    FileInputStream inputStream = new FileInputStream(fileName);
    HSSFWorkbook workBook = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = workBook.getSheetAt(0);

    int rowValue = sheet.getLastRowNum() + 1;
    int colValue = sheet.getRow(0).getLastCellNum();
    String[][] data = new String[rowValue][colValue];
    String[] colName = new String[colValue];
    for (int i = 0; i < rowValue; i++) {
        HSSFRow row = sheet.getRow(i);/*from  w w w  .  ja  v  a 2 s. com*/
        for (int j = 0; j < colValue; j++) {
            HSSFCell cell = row.getCell(j);
            int type = cell.getCellType();
            Object returnCellValue = null;
            if (type == 0) {
                returnCellValue = cell.getNumericCellValue();
            } else if (type == 1) {
                returnCellValue = cell.getStringCellValue();
            }

            data[i][j] = returnCellValue.toString();
        }
    }

    for (int i = 0; i < colValue; i++) {
        colName[i] = data[0][i];
    }

    tableModel = new DefaultTableModel(data, colName);
    tablePreview.setModel(tableModel);
    tableModel.removeRow(0);
}

From source file:com.tecnosur.util.Excel.java

public void ExportarMatriculados(CControlmatricula datos, String aula) {
    try { // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet = wb.createSheet("matriculados");

        // quito las lineas del libro para darle un mejor acabado
        sheet.setDisplayGridlines(false);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));

        // creo una nueva fila
        Row trow = sheet.createRow((short) 1);

        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "ALUMNOS MATRICULADOS");

        Row trow2 = sheet.createRow((short) 3);
        createTituloCell(wb, trow2, 0, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER, "AULA: " + aula);

        // Creo la cabecera de mi listado en Excel
        Row row = sheet.createRow((short) 5);

        // Creo las celdas de mi fila, se puede poner un diseo a la celda_codigo
        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "CDIGO", true, true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "ALUMNO", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "TIPO PAGO", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "CRONO. PAGO", true, true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "N CUOT.", true, true);
        createCell(wb, row, 5, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "MON. MATR.", true, true);
        createCell(wb, row, 6, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "MON. CUOT.", true, true);

        // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //la celda_codigo se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.setColumnWidth(0, 60 * 40);
        ssheet.setColumnWidth(1, 255 * 40);
        ssheet.setColumnWidth(2, 90 * 40);
        ssheet.autoSizeColumn(3);//from   w ww.j av a  2s. c  o  m
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);

        HSSFFont cellFont = wb.createFont();

        cellFont.setFontHeightInPoints((short) 8);
        cellFont.setFontName(HSSFFont.FONT_ARIAL);

        CellStyle cellStyle = wb.createCellStyle();

        cellStyle.setFont(cellFont);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor((short) 8);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor((short) 8);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor((short) 8);

        for (int i = 0; i < datos.size(); i++) {
            row = sheet.createRow((short) i + 6);
            Cell celda_codigo = row.createCell(0);

            celda_codigo.setCellStyle(cellStyle);
            celda_codigo.setCellValue(datos.get(i).getIdalumno());

            Cell celda_alumno = row.createCell(1);
            celda_alumno.setCellStyle(cellStyle);
            celda_alumno.setCellValue(datos.get(i).getNom_completo());

            Cell celda_tipopago = row.createCell(2);
            celda_tipopago.setCellStyle(cellStyle);
            celda_tipopago.setCellValue(datos.get(i).getTipopagante());

            Cell celda_crono = row.createCell(3);
            celda_crono.setCellStyle(cellStyle);
            celda_crono.setCellValue(datos.get(i).getDesc_cronopagtable());

            Cell celda_ncuota = row.createCell(4);
            celda_ncuota.setCellStyle(cellStyle);
            celda_ncuota.setCellValue(datos.get(i).getNum_cuota());

            Cell celda_matricula = row.createCell(5);
            celda_matricula.setCellStyle(cellStyle);
            celda_matricula.setCellValue(datos.get(i).getMonmatricula());

            Cell celda_moncuota = row.createCell(6);
            celda_moncuota.setCellStyle(cellStyle);
            celda_moncuota.setCellValue(datos.get(i).getMoncuota());
        }

        String strRuta = "TYSAC_Matriculados.xls";
        FileOutputStream fileOut = new FileOutputStream(strRuta);
        wb.write(fileOut);

        fileOut.close();
        Runtime.getRuntime().exec("cmd /c start " + strRuta);

    } catch (IOException e) {
        System.out.println("Error de escritura");
        e.printStackTrace();
    }
}

From source file:com.templatemailer.handler.ExcelHandler.java

public static MailerData readFile(String fileName) throws Exception {

    FileInputStream file = new FileInputStream(new File(fileName));
    System.out.println("Read File");
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    System.out.println("Parsed");
    HSSFSheet worksheet = workbook.getSheetAt(0);
    int numberOfEmails = 0, numberOfAttributes = 0;

    for (int i = 0;; i++) {

        HSSFRow row1 = worksheet.getRow(i);
        if (row1 == null)
            break;
        if (row1.getCell(0) == null)
            break;
        String s = row1.getCell(0).getStringCellValue();
        System.out.println("Reading s:" + s);
        if (s != null && s != "") {
            numberOfEmails++;//from   w  ww  . j a va 2s  .  c o m
        } else {
            break;
        }

    }

    HSSFRow row1 = worksheet.getRow(0);
    for (int i = 0;; i++) {
        HSSFCell cell = row1.getCell(i);
        if (cell == null)
            break;
        if (cell.getStringCellValue() == null | cell.getStringCellValue() == "") {
            break;

        }

        numberOfAttributes++;
    }
    MailerData mailerData = new MailerData(numberOfAttributes, numberOfEmails);
    for (int i = 0; i < numberOfAttributes; i++) {
        HSSFCell cell = row1.getCell(i);
        if (cell == null)
            break;
        if (cell.getStringCellValue() == null | cell.getStringCellValue() == "") {
            break;

        }
        mailerData.attributes[i] = cell.getStringCellValue().trim();

    }

    for (int i = 0; i < numberOfEmails; i++) {
        HSSFRow row = worksheet.getRow(i);
        if (i != 0)
            mailerData.listOfAddresses[i - 1] = row.getCell(0).getStringCellValue();
        for (int j = 0; j < numberOfAttributes; j++) {
            HSSFCell cell = row.getCell(j);
            mailerData.attributeMatrix[i][j] = cell.getStringCellValue().trim();

        }
    }

    file.close();
    return mailerData;

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param fileName - Name of the excel file.
*//*from   w  w w  .j  ava2  s  .c  om*/
private List readExcelFile() {

    List cellDataList = new ArrayList();
    try {

        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet hssfSheet = workBook.getSheetAt(0);
        Iterator rowIterator = hssfSheet.rowIterator();

        while (rowIterator.hasNext()) {
            HSSFRow hssfRow = (HSSFRow) rowIterator.next();
            Iterator iterator = hssfRow.cellIterator();
            List cellTempList = new ArrayList();
            while (iterator.hasNext()) {
                HSSFCell hssfCell = (HSSFCell) iterator.next();
                cellTempList.add(hssfCell);
            }
            cellDataList.add(cellTempList);
        }
    } catch (Exception e) {
        WmLog.printMessage("Can not read XLs file=" + this.fileName);
        e.printStackTrace();
    }

    //printToConsole(cellDataList);

    return cellDataList;
}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to modify data from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* @param colIndex - Index of col 0,1,2 etc.
* @param value - value to be modified//from   ww  w  .j  a  va  2s . co m
*/
public void modifyExcelCol(int sheetIndex, int rowIndex, int colIndex, String value) {
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        sheet.setForceFormulaRecalculation(true);
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        cell.setCellValue(value);
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workBook.write(fileOut);
        fileOut.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}