Set the date and time from the character string with POI

environment

java

• java7

Apache Poi

• 3.14

Set the string as date and time in the cell

When setting the character string as date and time, [org.apache.poi.ss.usermodel.DateUtil](http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil. Use html)

Set the time in the cell

The original contents of the Excel file look like this

User-defined (h: mm) is set in cell C3 Code to set 6:45 in cell C3

Sample.java

import java.io.FileOutputStream;
import java.io.OutputStream;
import org.apache.poi.ss.usermodel.Cell;
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.apache.poi.ss.usermodel.WorkbookFactory;

    public static void main(String[] args) { 
        try(Workbook book = WorkbookFactory.create(Sample.class.getResourceAsStream("sample.xlsx"));
                OutputStream out = new FileOutputStream("sample.xlsx");) {
            Sheet sheet = book.getSheetAt(0);
            Row row = sheet.getRow(2);
            Cell cell = row.getCell(2);
            //Convert string to serial number
            double time = DateUtil.convertTime("06:45");
            //Set the time
            cell.setCellValue(time);
            
            book.write(out);
        }catch(Exception err) {
            //Do something
        }
    }

After execution

The important thing is the process of converting to serial value using DateUtil.convertTime. The format of the character string that can be used as an argument is "HH: MM" or "HH: MM: SS" Any other format will result in an IllegalArgumentException

The method used when entering a value in a cell uses setCellValue (double)

Set the date in the cell

The original contents of the Excel file look like this

User-defined (yyyy "year" m "month" d "day") is set in cell C2 Code to set May 15, 2016 in cell C2

Sample.java

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
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.apache.poi.ss.usermodel.WorkbookFactory;

    public static void main(String[] args) { 
        try(Workbook book = WorkbookFactory.create(Sample.class.getResourceAsStream("sample.xlsx"));
                OutputStream out = new FileOutputStream("sample.xlsx");) {
            Sheet sheet = book.getSheetAt(0);
            Row row = sheet.getRow(1);
            Cell cell = row.getCell(2);
            //Convert the value you want to set to Date type
            Date date = DateUtil.parseYYYYMMDDDate("2016/05/15");
            //Set date
            cell.setCellValue(date);
            
            book.write(out);
        }catch(Exception ex) {
            //Do something
        }

    }

After execution

The important thing is the process of converting to java.uti.Date using DateUtil.parseYYYYMMDDDate The only character string format that can be used as an argument is "YYYY / MM / DD" Any other format will result in an IllegalArgumentException

The method used when putting a value in a cell uses setCellValue (java.util.Date)