2020年9月7日 19:56:31go评论80阅读模式

英文:

Apache POI, align text to left and other text to right in same row

问题

我正在使用Apache POI来创建Excel导出文件（.xlsx，带有XSSF）。
我在单元格之间遇到了一个边框问题。

我需要将几个单元格合并成一行，并且在该行中，我需要一个文本朝左对齐，另一个文本朝右对齐，就像这样：

但是不带中间的那个边框。

为了实现图像中的效果，我使用了两个合并区域，在一个区域中，我将文本对齐到左侧，在另一个区域中，我将文本对齐到右侧。我不确定是否有更好/更方便的方法来实现这一点，如果您知道，请在答案中写出，但是对于我当前的方法，问题出在那个边框上，我可以去掉它吗？我尝试将第一个合并区域的右边框设置为NONE，并且将第二个合并区域的左边框也设置为NONE，但是没有起作用。

我应该如何处理这个问题？

英文:

I'm using Apache POI to create excel export file (.xlsx with XSSF).
I'm having one issue with borders between cells.

I need to have few cells merged into one row and in that row I need to have one text aligned to the left, and another text aligned to the right, something like:

but without that border in between.

To get what you see in the image I used two merged areas, in one I aligned text to the left and in the other one I aligned text to the right, I'm not sure if there is a better/more convenient way to do this or not, if you know it please write it in the answer, but for my approach right now the issue is with that border, can I remove it? I tried setting right border for first merged area to NONE and setting left border for the second merged area to NONE as well, but it doesn't work.

How should I handle this?

答案1

得分: 3

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class Test {

    public static void main(String[] args) throws Exception {
        try (Workbook wb = new XSSFWorkbook(); OutputStream fos = new FileOutputStream("test.xlsx")) {
            Sheet sheet = wb.createSheet();

            Font font = wb.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 11);

            CellRangeAddress leftCellRangeAddress = new CellRangeAddress(
                0,
                1,
                CellReference.convertColStringToIndex("A"),
                CellReference.convertColStringToIndex("E")
            );
            sheet.addMergedRegion(leftCellRangeAddress);
            CellRangeAddress rightCellRangeAddress = new CellRangeAddress(
                0,
                1,
                CellReference.convertColStringToIndex("F"),
                CellReference.convertColStringToIndex("H")
            );
            sheet.addMergedRegion(rightCellRangeAddress);
            Row row = sheet.createRow(0);

            Cell leftCell = row.createCell(CellReference.convertColStringToIndex("A"));
            leftCell.setCellValue("LEFT");
            leftCell.getCellStyle().setFont(font);
            CellUtil.setVerticalAlignment(leftCell, VerticalAlignment.CENTER);
            CellUtil.setAlignment(leftCell, HorizontalAlignment.LEFT);
            RegionUtil.setBorderRight(BorderStyle.THIN, leftCellRangeAddress, sheet);
            RegionUtil.setRightBorderColor(IndexedColors.WHITE.getIndex(), leftCellRangeAddress, sheet);

            Cell rightCell = row.createCell(CellReference.convertColStringToIndex("F"));
            rightCell.setCellValue("RIGHT");
            rightCell.getCellStyle().setFont(font);
            CellUtil.setVerticalAlignment(rightCell, VerticalAlignment.CENTER);
            CellUtil.setAlignment(rightCell, HorizontalAlignment.RIGHT);
            RegionUtil.setBorderLeft(BorderStyle.THIN, rightCellRangeAddress, sheet);
            RegionUtil.setLeftBorderColor(IndexedColors.WHITE.getIndex(), rightCellRangeAddress, sheet);

            wb.write(fos);
        }
    }
}

若您想在底部添加灰色边框，可以添加以下代码：

CellRangeAddress firstRowRegion = new CellRangeAddress(
    0,
    1,
    CellReference.convertColStringToIndex("A"),
    CellReference.convertColStringToIndex("H")
);
RegionUtil.setBorderBottom(BorderStyle.THICK, firstRowRegion, sheet);
RegionUtil.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex(), firstRowRegion, sheet);

这样您将得到以下效果：

Apache POI，将文本左对齐并将其他文本右对齐在同一行。


<details>
<summary>英文:</summary>
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.OutputStream;
public class Test {
public static void main(String[] args) throws Exception {
try(Workbook wb = new XSSFWorkbook(); OutputStream fos = new FileOutputStream(&quot;test.xlsx&quot;)){
Sheet sheet = wb.createSheet();
Font font = wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short)11);
CellRangeAddress leftCellRangeAddress = new CellRangeAddress(
0,
1,
CellReference.convertColStringToIndex(&quot;A&quot;),
CellReference.convertColStringToIndex(&quot;E&quot;)
);
sheet.addMergedRegion(leftCellRangeAddress);
CellRangeAddress rightCellRangeAddress = new CellRangeAddress(
0,
1,
CellReference.convertColStringToIndex(&quot;F&quot;),
CellReference.convertColStringToIndex(&quot;H&quot;)
);
sheet.addMergedRegion(rightCellRangeAddress);
Row row = sheet.createRow(0);
Cell leftCell = row.createCell(CellReference.convertColStringToIndex(&quot;A&quot;));
leftCell.setCellValue(&quot;LEFT&quot;);
leftCell.getCellStyle().setFont(font);
CellUtil.setVerticalAlignment(leftCell, VerticalAlignment.CENTER);
CellUtil.setAlignment(leftCell, HorizontalAlignment.LEFT);
RegionUtil.setBorderRight(BorderStyle.THIN, leftCellRangeAddress, sheet);
RegionUtil.setRightBorderColor(IndexedColors.WHITE.getIndex(), leftCellRangeAddress, sheet);
Cell rightCell = row.createCell(CellReference.convertColStringToIndex(&quot;F&quot;));
rightCell.setCellValue(&quot;RIGHT&quot;);
rightCell.getCellStyle().setFont(font);
CellUtil.setVerticalAlignment(rightCell, VerticalAlignment.CENTER);
CellUtil.setAlignment(rightCell, HorizontalAlignment.RIGHT);
RegionUtil.setBorderLeft(BorderStyle.THIN, rightCellRangeAddress, sheet);
RegionUtil.setLeftBorderColor(IndexedColors.WHITE.getIndex(), rightCellRangeAddress, sheet);
wb.write(fos);
}
}
}
[![enter image description here][1]][1]
If you want the grey border on the bottom you can add
&lt;pre&gt;&lt;code&gt;CellRangeAddress firstRowRegion = new CellRangeAddress(
0,
1,
CellReference.convertColStringToIndex(&quot;A&quot;),
CellReference.convertColStringToIndex(&quot;H&quot;)
);
RegionUtil.setBorderBottom(BorderStyle.THICK, firstRowRegion, sheet);
RegionUtil.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex(), firstRowRegion, sheet);
&lt;/code&gt;&lt;/pre&gt;
and you&#39;ll get
[![enter image description here][2]][2]
[1]: https://i.stack.imgur.com/9NvSM.png
[2]: https://i.stack.imgur.com/nR3jk.png
</details>
# 答案2
**得分**: 1
你的截图显示的是网格线，而不是边框线。这是电子表格中的一个区别。网格线只在应用程序窗口中显示，以便更好地看到单元格。它们不会被打印出来。
如果你不想看到网格线，你可以选择将整个工作表切换为不显示网格线，但我不建议这样做；或者你可以设置白色的边框线，这样会覆盖掉一些网格线。
由于你标记了`apache-poi-4`，我将展示一个完整的示例，使用`CellUtil`和`PropertyTemplate`的高级方法来生成你所需要的内容。
代码：
```java
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
class CreateExcelLeftRight {
public static void main(String[] args) throws Exception {
try (Workbook workbook = new XSSFWorkbook();
FileOutputStream fileout = new FileOutputStream("Excel.xlsx")) {
//创建大号字体
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 24);
Sheet sheet = workbook.createSheet();
//合并A1:E2
sheet.addMergedRegion(new CellRangeAddress(
0, //第一行（从0开始）
1, //最后一行（从0开始）
0, //第一列（从0开始）
4  //最后一列（从0开始）
));
//合并F1:H2
sheet.addMergedRegion(new CellRangeAddress(
0, //第一行（从0开始）
1, //最后一行（从0开始）
5, //第一列（从0开始）
7  //最后一列（从0开始）
));
//创建第一行
Row row = sheet.createRow(0);
//创建单元格A1
Cell cell = row.createCell(0);
cell.setCellValue("LEFT");
CellUtil.setFont(cell, font);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
//创建单元格F1
cell = row.createCell(5);
cell.setCellValue("RIGHT");
CellUtil.setFont(cell, font);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setAlignment(cell, HorizontalAlignment.RIGHT);
PropertyTemplate propertyTemplate = new PropertyTemplate();
//在A1:H2上绘制所有内部边框为白色
propertyTemplate.drawBorders(new CellRangeAddress(0, 1, 0, 7),
BorderStyle.THIN, IndexedColors.WHITE.getIndex(), BorderExtent.INSIDE);
//在A2:H2上绘制所有底部边框为粗灰色
propertyTemplate.drawBorders(new CellRangeAddress(1, 1, 0, 7),
BorderStyle.THICK, IndexedColors.GREY_40_PERCENT.getIndex(), BorderExtent.BOTTOM);
propertyTemplate.applyBorders(sheet);
sheet.setActiveCell(new CellAddress(3, 0));
workbook.write(fileout);
}
}
}

结果：

（结果图片在此处显示，但由于文本限制，我无法在这里显示图片。你可以在你的浏览器中查看图片链接。）

英文:

What your screenshot shows is a gridline and not a border line. That's a difference in spreadsheets. The gridlines are shown in application window only to see the cells better. They will not be printed.

If you dont want to see the gridlines you either could switching to not showing gridlines for the whole sheet, what i do not recommend, or you could set white border lines which will overpaint some of the gridlines then.

Since you have tagged apache-poi-4 I will show a complete example which uses the advanced methods of CellUtil and PropertyTemplate to produce what you seems to want.

Code:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
class CreateExcelLeftRight {
public static void main(String[] args) throws Exception {
try (Workbook workbook = new XSSFWorkbook(); 
FileOutputStream fileout = new FileOutputStream(&quot;Excel.xlsx&quot;) ) {
//create font with bigger size
Font font = workbook.createFont();
font.setFontHeightInPoints((short)24);
Sheet sheet = workbook.createSheet(); 
//merge A1:E2
sheet.addMergedRegion(new CellRangeAddress(
0, //first row (0-based)
1, //last row  (0-based)
0, //first column (0-based)
4  //last column  (0-based)
));
//merge F1:H2
sheet.addMergedRegion(new CellRangeAddress(
0, //first row (0-based)
1, //last row  (0-based)
5, //first column (0-based)
7  //last column  (0-based)
));
//create row 1
Row row = sheet.createRow(0);
//create cell A1
Cell cell = row.createCell(0);
cell.setCellValue(&quot;LEFT&quot;);
CellUtil.setFont(cell, font);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
//create cell F1
cell = row.createCell(5);
cell.setCellValue(&quot;RIGHT&quot;);
CellUtil.setFont(cell, font);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setAlignment(cell, HorizontalAlignment.RIGHT);
PropertyTemplate propertyTemplate = new PropertyTemplate();
//paint all inside borders white on A1:H2
propertyTemplate.drawBorders(new CellRangeAddress(0, 1, 0, 7), 
BorderStyle.THIN, IndexedColors.WHITE.getIndex(), BorderExtent.INSIDE);
//paint all bottom borders thick gray on A2:H2
propertyTemplate.drawBorders(new CellRangeAddress(1, 1, 0, 7), 
BorderStyle.THICK, IndexedColors.GREY_40_PERCENT.getIndex(), BorderExtent.BOTTOM);
propertyTemplate.applyBorders(sheet);
sheet.setActiveCell(new CellAddress(3, 0));
workbook.write(fileout);
}
}
}

Result:

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

Apache POI，将文本左对齐并将其他文本右对齐在同一行。

问题

答案1

无法找到正确的java.util

如何在Processing或Java中获取PFont字符串的高度和宽度？

Java在DatagramSocket上设置不同的IP地址

内部服务器错误的Post请求Spring Boot [Java]

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论