java+poi+单元格行高_如何使用合并的单元格值大于单元格宽度的Apache-POI增加excel行的高度？...

小编典典

让您了解如何计算所需的行高的挑战。

我们可以使用Sheet.getColumnWidth(int)获得以字符宽度表示的列宽度。但这并不是真的准确，因为它仅适用于数字字形：1、2、3、4、5、6、7、8、9、0。在真型字体中，有一些字形(。，|，l，…)需要的宽度要少得多。因此，与文本中使用的宽度较小的字形相比，此结果将更加不准确。

我们可能会以一定的系数来校正“字符列宽度”。我用5/4。但这在很大程度上取决于所使用的语言。

然后，我们可以计算所需的行数，然后通过获取用于一行的默认行高并将其乘以所需的行数来获得所需的行高。

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.util.CellRangeAddress;

public class CreateExcelCellWrapText {

public static void main(String[] args) throws Exception {

XSSFWorkbook workbook = new XSSFWorkbook();

CellStyle cellstyle = workbook.createCellStyle();

cellstyle.setWrapText(true);

Sheet sheet = workbook.createSheet();

//__________________________not merged = height is auto sized

Row row = sheet.createRow(0);

Cell cell = row.createCell(2);

cell.setCellValue("String cell content\nhaving line wrap.");

cell.setCellStyle(cellstyle);

//__________________________merged = height is not auto sized

row = sheet.createRow(2);

cell = row.createCell(2);

cell.setCellValue("String cell content\nhaving line wrap.");

cell.setCellStyle(cellstyle);

CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 2, 2, 3);

sheet.addMergedRegion(cellRangeAddress);

//__________________________merged with calculated height

row = sheet.createRow(4);

String text = "String cell content\nhaving line wrap.\nIt has new line marks and then a long text without new line marks.\nFollowed by short text part.\n\n\nGreetings";

cell = row.createCell(2);

cell.setCellValue(text);

cell.setCellStyle(cellstyle);

cellRangeAddress = new CellRangeAddress(4, 4, 2, 3);

sheet.addMergedRegion(cellRangeAddress);

//get the column width in character widths for the merged columns

//this is not really accurate since it only is for number glyphs: 1,2,3,4,5,6,7,8,9,0

//in true type fonts there are glyps (., |, l, ...) which need much less width

int colwidthinchars = (sheet.getColumnWidth(2) + sheet.getColumnWidth(3)) / 256;

System.out.println(colwidthinchars);

//correct the colwidthinchars by a factor 5/4 (highly dependent on the language used)

colwidthinchars = Math.round(colwidthinchars * 5f/4f);

System.out.println(colwidthinchars);

//calculate the needed rows dependent on the text and the column width in character widths

String[] chars = text.split("");

int neededrows = 1;

int counter = 0;

for (int i = 0; i < chars.length; i++) {

counter++;

if (counter == colwidthinchars) {

System.out.println("new line because of charcter count");

neededrows++;

counter = 0;

} else if ("\n".equals(chars[i])) {

System.out.println("new line because of new line mark");

neededrows++;

counter = 0;

}

}

System.out.println(neededrows);

//get default row height

float defaultrowheight = sheet.getDefaultRowHeightInPoints();

System.out.println(defaultrowheight);

row.setHeightInPoints(neededrows * defaultrowheight);

workbook.write(new FileOutputStream("CreateExcelCellWrapText.xlsx"));

workbook.close();

}

}

这是有效的，因为使用了默认字体和字体大小。当使用不同的字体和不同的字体大小时，挑战将达到无穷大.

请参阅如何使用Java获取具有定义宽度的多行富文本字段(任何字体，任何字体大小)的所需高度？例如，以中的格式显示格式的文本JTextPane以获得所需的高度。

2020-07-28