Reading XLS in Java

Asked

Viewed 1,191 times

3

I have a class to read an xls in java, and I need to know how to print a column next to each other as well as in xls itself. So far I’ve only been able to print in a race or single line.

NOTE: I am using jxl library.

Follows the code:

Workbook workbook = Workbook.getWorkbook( new File( "C:\\Users\\lm\\Desktop\\planilha.xls" ));

        Sheet sheet = workbook.getSheet( 0 );

        int rows = sheet.getRows();
        int columns = sheet.getColumns();


        for ( int i = 0; i < columns; i++ )
        {
            for ( int j = 0; j < rows; j++ )
            {
                Cell cell = sheet.getCell( i , j );

                System.out.println( cell.getContents() );
            }

        }

        workbook.close();
java xls

  • That is, you can read all the XLS data and are only having difficulty organizing it on System.out.println?

  • Hi Victor ! That’s right, so that it is structured as if it were the spreadsheet itself. I thought within the looping create a Cell type variable for each column and concatenate, but there are many columns and I would like to use for more than one sheet this scope.

1 answer

4


Let’s divide the problem into two parts:

  1. Transform the spreadsheet into an array of Strings.
  2. Draw an array of Strings using System.out.println.

Follows the resulting code:

public String[][] planilha(String nome)
    try (Workbook workbook = Workbook.getWorkbook(new File(nome))) {
        Sheet sheet = workbook.getSheet(0);

        int rows = sheet.getRows();
        int columns = sheet.getColumns();
        String[][] matriz = new String[rows][columns];

        for (int i = 0; i < columns; i++) {
            for (int j = 0; j < rows; j++) {
                Cell cell = sheet.getCell(i, j);
                matriz[j][i] = cell.getContents();
            }
        }
    }
}

private static String padRight(String s, int tamanho) {
    StringBuilder sb = new StringBuilder(tamanho);
    sb.append(s);
    for (int i = s.length(); i < tamanho; i++) {
        sb.append(' ');
    }
    return sb.toString();
}

public void imprimirMatriz(String[][] matriz) {

    // Primeiro calcula o tamanho de cada coluna.
    int colunas = matriz[0].length;
    int[] tamanhoColunas = new int[colunas];
    for (String[] linha : matriz) {
        for (int i = 0; i < colunas; i++) {
            int t = linha[i].length();
            if (tamanhoColunas[i] < t) tamanhoColunas[i] = t;
        }
    }

    // Imprime a matriz.
    // Usa a função padRight para preencher os espaços corretamente.
    for (String[] linha : matriz) {
        for (int i = 0; i < colunas; i++) {
            System.out.print(padRight(linha[i], tamanhos[i] + 1));
        }
        System.out.println();
    }
}

public void teste() {
    String[][] matriz = planilha("C:\\Users\\lm\\Desktop\\planilha.xls");
    imprimirMatriz(matriz);
}

Browser other questions tagged java xls

You are not signed in. Login or sign up in order to post.