Set the date and time from the character string with POI

environment

java

Apache Poi

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 base.PNG

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 time.PNG

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 base2.PNG

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 date.PNG

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)

Recommended Posts

Set the date and time from the character string with POI
Check the actual date and time at parse with Java's SimpleDateFormat
[Java] How to set the Date time to 00:00:00
Deletes after the specified character from the character string
Date and time
[Java] Get and display the date 10 days later using the Time API added from Java 8.
Parse the date and time string formatted by the C asctime function in Java
[JSR-310 Date and Time API] Precautions for format definition when converting from Japanese calendar character string to date type
[Ruby] I want to make an array from a character string with the split method. And vice versa.
[Rails] Precautions when comparing date and time with DateTime
[Java] Cut out a part of the character string with Matcher and regular expression
Form and process file and String data at the same time with Spring Boot + Java
Find the address class and address type from the IP address with Java
Handle Java 8 date and time API with Thymeleaf with Spring Boot
What is the LocalDateTime class? [Java beginner] -Date and time class-
Sample code to parse date and time with Java SimpleDateFormat
Correct the character code in Java and read from the URL
Change only one character from the String type string to uppercase
[Java] Use ResolverStyle.LENIENT to handle the date and time nicely
[Ruby] Difference between symbol variables and character string variables. About the difference between [: a] and ['a'].
Get the current date and time by specifying the time zone in Thymeleaf
The design concept of Java's Date and Time API is interesting
Read the file under the classpath as a character string with spring
The relationship between strict Java date checking and daylight savings time
Divide the character string starting from the decimal point (using index.Of, substring)
[Convenient to remember !!!] How to convert from LocalDate type to character string and from character string to LocalDate type
[Java] How to convert from String to Path type and get the path
[MySQL] [java] Receive date and time
[Swift] Do not implement the format from date / time, number, currency, data size, list, person's name, number with unit to String by yourself.
Find the address class and address type from the IP address with Java [No. 2 decoction]
Invoke the character string passed as an argument as a method with send
Draw a bar graph and a line graph at the same time with MPAndroidChart
How to compare only the time with Rails (from what time to what time, something like)
I translated the grammar of R and Java [Updated from time to time]
I want to find out what character the character string appears from the left
SetCookie from the client side with OkHttp3
Explanation of Ruby Time and Date objects
I will absolutely convert the time string!
[Java] Get the date with the LocalDateTime class
I tried to get the distance from the address string to the nearest station with ruby
Shows how many years and months the difference from a particular date is
[Deep Learning from scratch] in Java 1. For the time being, differentiation and partial differentiation