在网上看了很多资料都是讨论如何利用POI读EXCEL的,但是没看到几个用POI向EXCEL写入日期数据的贴子,好不容易使通了,但是碰到了两个新问题,希望各位大侠指点:我向EXCEL写日期数据思路是先用JAVA函数把日期转换成相对1900-1-1 0:00的数字,然后利用POI自定义EXCEL单元格格式为YYYY-MM-DD HH:MM型,我测试了一下程序,这个方法基本上是行的通的,但是其中有两点问题:1.JAVA日期转换函数的参考日期是1900-1-1 0:00而EXCEL的参考日期是1900-1-0 0:00,因此算出来的数值参了一,这是好处理的,但是EXCEL跟JAVA日期转换函数间的偏差却不只这一处,不知道EXCEL不知道还在哪里少算了一天的时间,大家告诉我为什么会出现这种好吗?我就怕是处理闰年的依据不一样,这样可能会导致后面的日期时间里再出现偏差。
2.JAVA日期转换函数对12点的识别有问题,会识别成0点,如:1994-01-01 12:00会识别成Sat Jan 01 00:00:00 CST 1994大家有碰到这种情况吗?我的代码是这样的:
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.io.*,java.lang.*,org.apache.poi.hssf.usermodel.*,org.apache.poi.hssf.usermodel.HSSFCell,org.apache.poi.hssf.usermodel.HSSFCellStyle,org.apache.poi.hssf.usermodel.HSSFDataFormat,org.apache.poi.hssf.usermodel.HSSFFont,org.apache.poi.hssf.usermodel.HSSFRow,org.apache.poi.hssf.usermodel.HSSFSheet,org.apache.poi.hssf.usermodel.HSSFWorkbook,org.apache.poi.hssf.usermodel.HSSFRow,org.apache.poi.hssf.util.HSSFColor" %>
<%@ page import="zs_bx.DataQuery"%>
<%@ page import="java.sql.*"%>
<%@ page import ="java.util.Date"%>
<%@ page import ="java.util.Locale"%>
<%@ page import ="java.text.*"%>
<%
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle dateCellStyle=wb.createCellStyle();
short df=wb.createDataFormat().getFormat("yyyy-mm-dd hh:mm");
dateCellStyle.setDataFormat(df);DateFormat format;
SimpleDateFormat datetemp = new SimpleDateFormat("yyyy-MM-dd hh:mm");
SimpleDateFormat datetemp_test=new SimpleDateFormat("yyyy-MM-dd");
Date temp1 = datetemp.parse("1900-01-01 00:00");
Date temp2=datetemp.parse("1994-01-01 12:00");
double numOfDateTime=0;
numOfDateTime=temp2.getTime()-temp1.getTime();
numOfDateTime=numOfDateTime/24/60/60/1000+1;HSSFRow row;
HSSFCell cell;
row = sheet.createRow(0); cell = row.createCell((short)0);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(numOfDateTime);
cell.setCellStyle(dateCellStyle);....
....
2.JAVA日期转换函数对12点的识别有问题,会识别成0点,如:1994-01-01 12:00会识别成Sat Jan 01 00:00:00 CST 1994大家有碰到这种情况吗?我的代码是这样的:
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.io.*,java.lang.*,org.apache.poi.hssf.usermodel.*,org.apache.poi.hssf.usermodel.HSSFCell,org.apache.poi.hssf.usermodel.HSSFCellStyle,org.apache.poi.hssf.usermodel.HSSFDataFormat,org.apache.poi.hssf.usermodel.HSSFFont,org.apache.poi.hssf.usermodel.HSSFRow,org.apache.poi.hssf.usermodel.HSSFSheet,org.apache.poi.hssf.usermodel.HSSFWorkbook,org.apache.poi.hssf.usermodel.HSSFRow,org.apache.poi.hssf.util.HSSFColor" %>
<%@ page import="zs_bx.DataQuery"%>
<%@ page import="java.sql.*"%>
<%@ page import ="java.util.Date"%>
<%@ page import ="java.util.Locale"%>
<%@ page import ="java.text.*"%>
<%
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle dateCellStyle=wb.createCellStyle();
short df=wb.createDataFormat().getFormat("yyyy-mm-dd hh:mm");
dateCellStyle.setDataFormat(df);DateFormat format;
SimpleDateFormat datetemp = new SimpleDateFormat("yyyy-MM-dd hh:mm");
SimpleDateFormat datetemp_test=new SimpleDateFormat("yyyy-MM-dd");
Date temp1 = datetemp.parse("1900-01-01 00:00");
Date temp2=datetemp.parse("1994-01-01 12:00");
double numOfDateTime=0;
numOfDateTime=temp2.getTime()-temp1.getTime();
numOfDateTime=numOfDateTime/24/60/60/1000+1;HSSFRow row;
HSSFCell cell;
row = sheet.createRow(0); cell = row.createCell((short)0);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(numOfDateTime);
cell.setCellStyle(dateCellStyle);....
....
2.由于EXCEL误以为1900年是闰年(真正的判断法则是:能被4或400整除,但不能被100整除的年份为闰年),所以在这个问题上EXCEL又把天数算多了一天,因此,用JAVA算出来的时间要转化成EXCEL的话要再加多一天,因此是加2对于闰年的判断我不知道EXCEL是不是只错了1900年,反正从1900到2007EXCEL就错了一回,其它的闰年都算对了,希望这个发现能对各位大侠有帮助吧^_^
计算出来添进去,把格式设成日期型就OK了
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle dateCellStyle=wb.createCellStyle();
short df=wb.createDataFormat().getFormat("yyyy-mm-dd hh:mm");
dateCellStyle.setDataFormat(df); DateFormat format;
SimpleDateFormat datetemp = new SimpleDateFormat("yyyy-MM-dd HH:mm");
SimpleDateFormat datetemp_test = new SimpleDateFormat("yyyy-MM-dd");
Date temp1 = datetemp.parse("1900-01-01 00:00");
Date temp2 = datetemp.parse("1994-01-01 12:00");
//double numOfDateTime=0;
//numOfDateTime=temp2.getTime()-temp1.getTime();
//numOfDateTime=numOfDateTime/24/60/60/1000+1; HSSFRow row;
HSSFCell cell;
row = sheet.createRow(0); cell = row.createCell((short)0);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cell.setCellValue(numOfDateTime);
cell.setCellValue(temp2);
cell.setCellStyle(dateCellStyle);
public class Test {
public static void main(String[] args) {
HSSFWorkbook excel = createExcel();
OutputStream out = null;
try {
out = new FileOutputStream("D:/test.xls");
excel.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(out != null)
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
private static HSSFWorkbook createExcel() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle dateCellStyle=wb.createCellStyle();
short df=wb.createDataFormat().getFormat("yyyy-mm-dd hh:mm");
dateCellStyle.setDataFormat(df);
SimpleDateFormat datetemp = new SimpleDateFormat("yyyy-MM-dd HH:mm");
Date temp2 = null;
try {
temp2 = datetemp.parse("1994-01-01 12:00");
} catch (ParseException e) {
e.printStackTrace();
} HSSFRow row;
HSSFCell cell;
row = sheet.createRow(0); cell = row.createCell((short)0);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(temp2);
cell.setCellStyle(dateCellStyle);
sheet.setColumnWidth((short)0, (short)5000);
return wb;
}
}
NumberCell nc = (NumberCell) cell;
XFRecord xfr = (XFRecord) nc.getCellFormat();
final int INDEX_OF_DATE = 58;
if(xfr.formatIndex == INDEX_OF_DATE) {
Date date = HSSFDateUtil.getJavaDate(nc.getValue());
content = dateformat.format(date);
}
}