如何对齐 Excel 文件的输出？

Question

    ''''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();
        }
        
    }
}''''

当前输出：

岛上人员：Sheet1

名字 姓氏 性别 国家/地区 年龄 日期

Dulce Bleoop 女 津巴布韦 43 2035-03-01T00:00:00

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

Jello Bamop 男 南极洲 32 2053-04-06T00:00:00

芭芭拉·斯莱克 女性 Scandalousia 70 3203-03-12T00:00:00

Answer 1

你可以这样做：

//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");
    }

输出是：

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    

每列之间的空间由变量 spaceBetween 决定。

总之，你应该能够改变线路
System.out.print(worksheet.getCells().get(i, j).getValue() + " ");
到

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

您在代码中某处选择 spaceBetween 值的位置。

Answer 2

如果您想要对齐控制台中打印的输出行，则必须在字段上循环两次。第一个用于获取每列的长度，第二个用于正确打印它们。

您可以使用给定 String 的方法，用空格将其填充到末尾：

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

然后，将每个单元格的值作为二维数组 cells 中的 String 给出，执行以下操作：

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();
}

Answer 3

假设您使用的是 aspose，由于您包含了标签，您可以使用导出到数组方法：

....

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("%15s%15s%15s%15s%15s%15s%n", row);
}

对于左对齐输出，将 print 语句更改为

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

当然 15 只是我选择的随机值，因为我认为单元格值的最大长度为 15 个字符。根据您的需要进行更改。您还可以对于每一列，检查该列中所有字符串的长度，并将该列中的最大字符串长度作为该列的列宽。例如，这可能会以如下形式结束：

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