How to align output from an excel file?-CodePudding

    ''''Workbook wb = new Workbook("workbook.xlsx");
    WorksheetCollection collection = wb.getWorksheets();
    
    
    for (int worksheetIndex = 0; worksheetIndex < collection.getCount(); worksheetIndex  ) {
        
        Worksheet worksheet = collection.get(worksheetIndex);
        System.out.println("People on Island: "   worksheet.getName());
        System.out.println();
        
        int rows = worksheet.getCells().getMaxDataRow();
        int cols = worksheet.getCells().getMaxDataColumn();
        Style st = wb.createStyle();
        
        for (int i = 0; i <= rows; i  ) {
            
        for (int j = 0; j <= cols; j  )  {
            
            System.out.print(worksheet.getCells().get(i, j).getValue()   "   ");
            
            
        }
            
        System.out.println();
        System.out.println();
        }
        
    }
}''''

OUTPUT CURRENTLY:

People on Island: Sheet1

First Name Last name Gender Country Age Date

Dulce Bleoop Female Zimbabwe 43 2035-03-01T00:00:00

Sheeba Loadlee Female Sketchawan 24 3040-04-03T00:00:00

Jello Bamop Male Antarctica 32 2053-04-06T00:00:00

Barbara Slack Female Scandalousia 70 3203-03-12T00:00:00

CodePudding user response：

You can do something like this:

//Code for reading data, not relevant for the solution
    String my_string= """
            People on Island: Sheet1

            FirstName LastName Gender Country Age Date

            Dulce Bleoop Female Zimbabwe 43 2035-03-01T00:00:00

            Sheeba Loadlee Female Sketchawan 24 3040-04-03T00:00:00

            Jello Bamop Male Antarctica 32 2053-04-06T00:00:00

            Barbara Slack Female Scandalousia 70 3203-03-12T00:00:00""";

    String[] split_string = my_string.split("\n\n");

    String[][] finalString = new String[split_string.length][];

    for (int i = 0; i < split_string.length; i  ) {
        finalString[i] = split_string[i].split(" ");
    }
//Relevant code below
    int spaceBetween = 25; //this variable decides the distance between each column
    for (int i = 0; i < finalString.length; i  ) {
        for (int j = 0; j < finalString[i].length; j  )  {
            System.out.print(finalString[i][j]);
            for (int k = 0; k < spaceBetween-finalString[i][j].length(); k  )
            System.out.print(" ");
        }
        System.out.println("\n");
    }

The output is:

People                   on                       Island:                  Sheet1                   

FirstName                LastName                 Gender                   Country                  Age                      Date                     

Dulce                    Bleoop                   Female                   Zimbabwe                 43                       2035-03-01T00:00:00      

Sheeba                   Loadlee                  Female                   Sketchawan               24                       3040-04-03T00:00:00      

Jello                    Bamop                    Male                     Antarctica               32                       2053-04-06T00:00:00      

Barbara                  Slack                    Female                   Scandalousia             70                       3203-03-12T00:00:00

The Space between each column is decided by the variable spaceBetween.

In conclusion, you should be able to change the line System.out.print(worksheet.getCells().get(i, j).getValue() " "); to

val = worksheet.getCells().get(i, j).getValue();
System.out.print(val);
for (int k = 0; k < spaceBetween-val.lenght(); k  )
    System.out.print(" ");

where you choose a value for spaceBetween somewhere in the code.

CodePudding user response：

If what you want is to align the output lines printed in console, you will have to loop over the fields two times. The first for getting whe length of each column and the second to print them correctly.

You can use a method that given a String pads it with whitespaces to the end:

public static String alignLeft(String str, int width) {
    if (str.length() >= width) {
        return str;
    }
    return alignLeft(str   " ", width);
}

Then, given the values of each cell as a String in a bidimensional array cells, doing the following:

int[] columnsWidths = new int[cells[0].length];
for (int i = 0; i < cells.length; i  ) {
    for (int j = 0; j < cells[i].length; j  ) {
        if (columnsWidths[j] < cells[i][j].length()) {
            columnsWidths[j] = cells[i][j].length();
        }
    }
}

for (int i = 0; i < cells.length; i  ) {
    for (int j = 0; j < cells[i].length; j  ) {
        System.out.print(alignLeft(cells[i][j], columnsWidths[j])   " ");
    }
    System.out.println();
}

CodePudding user response：

Assuming you are using aspose, since you included the tag, you could use the export to array method:

....

int rows = worksheet.getCells().getMaxDataRow();
int cols = worksheet.getCells().getMaxDataColumn();

Object dataTable[][] = worksheet.getCells().exportArray(0, 0, rows, cols);

for (final Object[] row : dataTable) {
    System.out.format("ssssss%n", row);
}

For left-aligned output change the print statement to

 System.out.format("%-15s%-15s%-15s%-15s%-15s%-15s%n", row);

Of course 15 is just a random value I choosed, because I thought the max length of a cell value has 15 chars. change as you see fit. You could also for each column, check length of all strings in that column and take max string length in that column as column width for that column. Which could end for example in something like:

System.out.format("%-10s%-10s%-8s%-15s%-5s%-25s%n", row);