PJ,
I am trying to use the copyRows as you suggested but I am hitting some
exceptions regarding formulas even though my data has no formulas. I took a
unit test of mine in which I was testing the bug I reported in
https://bz.apache.org/bugzilla/show_bug.cgi?id=69583 and refitted it with
an HSSFWorkbook along with my code which creates a new Workbook from each
Sheet of an existing Workbook. The unit code test is included as an
attachment. The stacktrace I get is:
[main] WARN org.apache.poi.POIDocument - DocumentSummaryInformation
property set came back as null
[main] WARN org.apache.poi.POIDocument - SummaryInformation property set
came back as null
java.lang.IllegalStateException: Cannot get a FORMULA value from a STRING
formula cell
at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:648)
at org.apache.poi.hssf.usermodel.HSSFCell.getCellFormula(HSSFCell.java:640)
at
org.apache.poi.hssf.usermodel.helpers.HSSFRowColShifter.updateRowFormulas(HSSFRowColShifter.java:74)
at
org.apache.poi.hssf.usermodel.helpers.HSSFRowShifter.updateRowFormulas(HSSFRowShifter.java:71)
at org.apache.poi.hssf.usermodel.HSSFRow.copyRowFrom(HSSFRow.java:853)
at
org.apache.nifi.excel.TestHSSFRowCopyRowFrom.test(TestHSSFRowCopyRowFrom.java:74)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
On Mon, May 12, 2025 at 6:32 PM Dan S <[email protected]> wrote:
> PJ,
> Thank you! That is very helpful and aligns very well with the XSSFSheet
> copyRows method in regards to use of CellCopyPolicy and CellCopyContext.
>
> On Mon, May 12, 2025 at 5:49 PM PJ Fanning <[email protected]> wrote:
>
>> There is copyRowFrom in HSSFRow.
>>
>>
>> https://poi.apache.org/apidocs/dev/org/apache/poi/hssf/usermodel/HSSFRow.html#copyRowFrom-org.apache.poi.ss.usermodel.Row-org.apache.poi.ss.usermodel.CellCopyPolicy-org.apache.poi.ss.usermodel.CellCopyContext-
>>
>> On Mon, 12 May 2025 at 21:54, Dan S <[email protected]> wrote:
>> >
>> > XSSFSheet has a copyRows method which allows for copying the contents of
>> > one XSSFSheet to another one. I do not see an equivalent method for an
>> > HSSFSheet. I see from this post
>> > <
>> https://lists.apache.org/[email protected]:dfr=2020-1-1|dto=2025-5-12:HSSFSheet%20copy
>> <https://lists.apache.org/[email protected]:dfr=2020-1-1%7Cdto=2025-5-12:HSSFSheet%20copy>
>> >
>> > a solution which allows copying for HSSFSheet and XSSFSheet. I just
>> want to
>> > make sure if that is a recommended way for HSSF and if not has there
>> been
>> > any change to POI that provides methods for this?
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellCopyContext;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.jupiter.api.Test;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.Iterator;
public class TestHSSFRowCopyRowFrom {
@Test
void test() throws IOException {
CellCopyPolicy cellCopyPolicy = new CellCopyPolicy.Builder()
.cellFormula(false) // NOTE: setting to false allows for copying the evaluated formula value.
.cellStyle(CellCopyPolicy.DEFAULT_COPY_CELL_STYLE_POLICY)
.cellValue(CellCopyPolicy.DEFAULT_COPY_CELL_VALUE_POLICY)
.condenseRows(CellCopyPolicy.DEFAULT_CONDENSE_ROWS_POLICY)
.copyHyperlink(CellCopyPolicy.DEFAULT_COPY_HYPERLINK_POLICY)
.mergeHyperlink(CellCopyPolicy.DEFAULT_MERGE_HYPERLINK_POLICY)
.mergedRegions(CellCopyPolicy.DEFAULT_COPY_MERGED_REGIONS_POLICY)
.rowHeight(CellCopyPolicy.DEFAULT_COPY_ROW_HEIGHT_POLICY)
.build();
final LocalDateTime localDateTime = LocalDateTime.of(2023, 1, 1, 0, 0, 0);
final LocalDateTime nonValidExcelDate = LocalDateTime.of(1899, 12, 31, 0, 0, 0);
final Object[][] data = {
{"transaction_id", "transaction_date", "transaction_time"},
{75, localDateTime, nonValidExcelDate.plusHours(9).plusMinutes(53).plusSeconds(44).toLocalTime()},
{78, localDateTime, nonValidExcelDate.plusHours(9).plusMinutes(55).plusSeconds(16).toLocalTime()}
};
final ByteArrayOutputStream workbookOutputStream = new ByteArrayOutputStream();
try (Workbook workbook = new HSSFWorkbook()) {
final Sheet sheet = workbook.createSheet("SomeSheetName");
populateSheet(sheet, data);
setCellStyles(sheet, workbook);
workbook.write(workbookOutputStream);
}
try {
final HSSFWorkbook originalWorkbook = new HSSFWorkbook(new ByteArrayInputStream(workbookOutputStream.toByteArray()));
final Iterator<Sheet> originalSheetsIterator = originalWorkbook.sheetIterator();
final CellCopyContext cellCopyContext = new CellCopyContext();
int index = 0;
while (originalSheetsIterator.hasNext()) {
final HSSFSheet originalSheet = (HSSFSheet) originalSheetsIterator.next();
final String originalSheetName = originalSheet.getSheetName();
final Iterator<Row> originalRowsIterator = originalSheet.rowIterator();
try (HSSFWorkbook newWorkbook = new HSSFWorkbook()) {
final HSSFSheet newSheet = newWorkbook.createSheet(originalSheetName);
while (originalRowsIterator.hasNext()) {
HSSFRow originalRow = (HSSFRow) originalRowsIterator.next();
HSSFRow newRow = newSheet.createRow(originalRow.getRowNum());
newRow.copyRowFrom(originalRow, cellCopyPolicy, cellCopyContext);
}
try (final OutputStream out = Files.newOutputStream(Paths.get(String.format("target/sheet-%s.xls", index)))) {
newWorkbook.write(out);
}
}
index++;
}
} catch (final IOException e) {
throw new RuntimeException("Failed to split XLS file", e);
}
}
private static void populateSheet(Sheet sheet, Object[][] data) {
int rowCount = 0;
for (Object[] dataRow : data) {
Row row = sheet.createRow(rowCount++);
int columnCount = 0;
for (Object field : dataRow) {
Cell cell = row.createCell(columnCount++);
switch (field) {
case String string -> cell.setCellValue(string);
case Integer integer -> cell.setCellValue(integer.doubleValue());
case Long l -> cell.setCellValue(l.doubleValue());
case LocalDateTime localDateTime -> cell.setCellValue(localDateTime);
case LocalTime localTime -> cell.setCellValue(DateUtil.convertTime(DateTimeFormatter.ISO_LOCAL_TIME.format(localTime)));
default -> { }
}
}
}
}
void setCellStyles(Sheet sheet, Workbook workbook) {
CreationHelper creationHelper = workbook.getCreationHelper();
CellStyle dayMonthYearCellStyle = workbook.createCellStyle();
dayMonthYearCellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("dd/mm/yyyy"));
CellStyle hourMinuteSecond = workbook.createCellStyle();
hourMinuteSecond.setDataFormat((short) 21); // 21 represents format h:mm:ss
for (int rowNum = sheet.getFirstRowNum() + 1; rowNum < sheet.getLastRowNum() + 1; rowNum++) {
Row row = sheet.getRow(rowNum);
row.getCell(1).setCellStyle(dayMonthYearCellStyle);
row.getCell(2).setCellStyle(hourMinuteSecond);
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
