CODEWITHSUNDEEP
apache-poi
user468587
user468587

Reputation: 5031

apache poi write HH:mm:ss string to Excel Time data type

i used apache poi to rewrite a CSV file to Excel, for any string like HH:mm:ss, i need to convert it to the appropriate Excel data type so that user can apply sum() function on that column. i tried different datatype, but when i open the excel file i cannot sum that column the sum always showed as "0:00:00", even though clicking on that column in excel it showed as 'Time' data type,

here is my code:

CreationHelper creationHelper = workbook.getCreationHelper();
HSSFCellStyle timeStyle = workbook.createCellStyle();          
timeStyle.setDataFormat(creationHelper.createDataFormat().getFormat("h:mm:ss"));
cell.setCellValue(column);
if (isTime(column)) {
  cell.setCellStyle(timeStyle);
}

private boolean isTime(String value) {
        try {
            dtf.parseDateTime(value);
            return true;
        } catch (IllegalArgumentException e) {
            return false;
        }
    }

and here is my excel file enter image description here

Upvotes: 1

Views: 2896

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

If the object column in your code is String, then the content of the cell will always be string (text) cell content after cell.setCellValue(column). This content the function SUM cannot work with. Those functions needs numeric content. In Excel date and time also is numeric content only formatted as date-time. With default settings 1 = 1 day = 01/01/1900 00:00:00. 1 hour = 1/24, 1 minute = 1/24/60, 1 second = 1/24/60/60.

If column is string of format "HH:MM:SS", then DateUtil.convertTime can be used to convert this string into a Excel valuable time.

Complete example which shows what not works and what works:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;

public class ExcelCalculateTimeValues {

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

  Workbook workbook = new HSSFWorkbook();
  //Workbook workbook = new XSSFWorkbook();

  CreationHelper createHelper = workbook.getCreationHelper();
  CellStyle styletime = workbook.createCellStyle();
  styletime.setDataFormat(createHelper.createDataFormat().getFormat("hh:mm:ss"));

  Sheet sheet = workbook.createSheet();
  sheet.createRow(0).createCell(0).setCellValue("Time sting");
  sheet.getRow(0).createCell(1).setCellValue("Time");

  String[][] tableData = new String[][]{
   {"12:34:00", "22:45:00"},
   {"23:45:05", "01:34:40"},
   {"08:01:00", "13:23:00"},
   {"15:41:12", "23:23:22"}
  };

  int r = 1;
  for (String[] rowData : tableData) {
   Row row = sheet.createRow(r++);
   int c = 0;
   for (String cellData : rowData) {
    Cell cell = row.createCell(c);
    if (c == 0 ) {
     cell.setCellValue(cellData); //this sets string cell data
    } else if (c == 1) {
     cell.setCellValue(DateUtil.convertTime(cellData)); //this sets datetime cell data
    }
    cell.setCellStyle(styletime);
    c++;
   }
  }

  sheet.createRow(r).createCell(0).setCellFormula("SUM(A2:A"+r+")"); //cannot work because of string values in A2:A4
  sheet.getRow(r).createCell(1).setCellFormula("SUM(B2:B"+r+")"); //will work

  workbook.setForceFormulaRecalculation(true);

  if (workbook instanceof HSSFWorkbook) {
   workbook.write(new FileOutputStream("ExcelCalculateTimeValues.xls"));
  } else if (workbook instanceof XSSFWorkbook) {
   workbook.write(new FileOutputStream("ExcelCalculateTimeValues.xlsx"));
  }
  workbook.close();

 }

}

Upvotes: 8

Related Questions