Working with Excel files often involves managing date and time data, which requires careful formatting to ensure accuracy during import and export operations. This article demonstrates how to manipulate temporal data in Excel using Java and the Apache POI library.

Date Representation in Excel

Excel stores dates as serial numbers, where each integer represents a day since a reference date. When reading or writing temporal values, it's esssential to apply appropriate formatting to maintain consistency between Excel's internal representation and Java's Date objects.

Reading Excel Files with Temporal Data

To extract date values from Excel sheets, Apache POI provides methods to access cell content directly as Date instances:

// Load workbook from file
Workbook workbook = WorkbookFactory.create(new FileInputStream("input_data.xlsx"));
Sheet worksheet = workbook.getSheetAt(0);

// Extract date from specific cell
Row dataRow = worksheet.getRow(0);
Cell dateCell = dataRow.getCell(0);
Date extractedDate = dateCell.getDateCellValue();

Writing Formatted Dates to Excel

When creating Excel files, applying custom date formats ensures that temporal data appears correctly in the output:

// Initialize new workbook
Workbook outputWorkbook = new XSSFWorkbook();
Sheet outputSheet = outputWorkbook.createSheet("Data");

// Define date formatting style
CellStyle dateFormatStyle = outputWorkbook.createCellStyle();
CreationHelper helper = outputWorkbook.getCreationHelper();
dateFormatStyle.setDataFormat(helper.createDataFormat().getFormat("MM/dd/yyyy hh:mm"));

// Insert formatted date into cell
Row outputRow = outputSheet.createRow(0);
Cell outputCell = outputRow.createCell(0);
outputCell.setCellValue(extractedDate);
outputCell.setCellStyle(dateFormatStyle);

// Save workbook to file
FileOutputStream outputStream = new FileOutputStream("result.xlsx");
outputWorkbook.write(outputStream);
outputStream.close();

Data Processing Workflow

The typical workflow for handling temporal data includes loading the Excel file, extracting date values, processing them as needed, and then writing the results back to a new Excel file with proper formatting applied.

This approach enables seamless conversion between Excel's date storage mechanism and Java's temporal data types, ensuring accurate representation of time-based information throughout the import/export cycle.