Java Excel XSSFSheetXMLHandler DataFormatter 笔记

Excel中日期格式处理

---

public void processSheet(Styles styles, SharedStrings strings,
        SheetContentsHandler sheetHandler, InputStream sheetInputStream)
        throws IOException, SAXException {
    DataFormatter formatter = new CustomDataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler,
                formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}

private class CustomDataFormatter extends DataFormatter {

    @Override
    public String formatRawCellContents(double value, int formatIndex, String formatString,
            boolean use1904Windowing) {

        // Is it a date?
        if (DateUtil.isADateFormat(formatIndex, formatString)) {
            if (DateUtil.isValidExcelDate(value)) {
                Date d = DateUtil.getJavaDate(value, use1904Windowing);
                try {
                    return new SimpleDateFormat("yyyyMMdd").format(d);
                } catch (Exception e) {
                    logger.log(Level.SEVERE, "Bad date value in Excel: " + d, e);
                }
            }
        }
        return new DecimalFormat("##0.#####").format(value);
    }
}

相关文档：http://tool.oschina.net/uploads/apidocs/apache-POI/org/apache/poi/ss/usermodel/DataFormatter.html

 

 

读取Excel日期显示为数字，转换成日期格式 。

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;


public class ExcelDateFormater{
	
	   //将excel里面的数字时间转化为准确的时间格式！

	    private static final int SECONDS_PER_MINUTE = 60;
	    private static final int MINUTES_PER_HOUR = 60;
	    private static final int HOURS_PER_DAY = 24;
	    private static final int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);
	    private static final long DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;
	    
	    private static SimpleDateFormat sdFormat=new SimpleDateFormat("yyyy-MM-dd");
	    public static void main(String args[]){
//	    	double date=43529.00;
//	    	int wholeDays = (int)Math.floor(date);
//	        int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
//	        Calendar calendar = new GregorianCalendar(); // using default time-zone
//	        setCalendar(calendar, wholeDays, millisecondsInDay, false);
//	        System.out.println(sdFormat.format(calendar.getTime())); 
	    	
	    	getDateByDays("99");
	    	
	    }
	    
	    
	    public static String getDateByDays(String days){
	    	if(null == days || "".equals(days) ){
	    		return "1900-01-01";
	    	}
	    	double date = 0;
			try {
				date = Double.parseDouble(days);
			} catch (NumberFormatException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
				System.out.println("传入参数有误:" + days);
				return days;
			}
	    	int wholeDays = (int)Math.floor(date);
	        int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
	        Calendar calendar = new GregorianCalendar(); // using default time-zone
	        setCalendar(calendar, wholeDays, millisecondsInDay, false);
	        //System.out.println(sdFormat.format(calendar.getTime())); 
	        return sdFormat.format(calendar.getTime());
	    }
	    
	    public static void setCalendar(Calendar calendar, int wholeDays,
	            int millisecondsInDay, boolean use1904windowing) {
	        int startYear = 1900;
	        int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't
	        if (use1904windowing) {
	            startYear = 1904;
	            dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
	        }
	        else if (wholeDays < 61) {
	            // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
	            // If Excel date == 2/29/1900, will become 3/1/1900 in Java representation
	            dayAdjust = 0;
	        }
	        calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);
	        calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
	    }
}