cell.getCellStyle().getDataFormat();根据这个值进行时间、日期格式的判断;
POI读取出来的结果也是有些变化的;需要在实际项目中进行确认;
时间格式的遍历:
/** * 处理数据类型 * * @param attributes */ public void setNextDataType(Attributes attributes) { nextDataType = CellDataType.NUMBER; formatIndex = -1; formatString = null; String cellType = attributes.getValue("t"); String cellStyleStr = attributes.getValue("s"); String columData = attributes.getValue("r"); if ("b".equals(cellType)) { nextDataType = CellDataType.BOOL; } else if ("e".equals(cellType)) { nextDataType = CellDataType.ERROR; } else if ("inlineStr".equals(cellType)) { nextDataType = CellDataType.INLINESTR; } else if ("s".equals(cellType)) { nextDataType = CellDataType.SSTINDEX; } else if ("str".equals(cellType)) { nextDataType = CellDataType.FORMULA; } if (cellStyleStr != null) { int styleIndex = Integer.parseInt(cellStyleStr); XSSFCellStyle style = this.stylesTable.getStyleAt(styleIndex); formatIndex = style.getDataFormat(); formatString = style.getDataFormatString(); short format=this.formatIndex; if (format == 14 || format == 31 || format == 57 || format == 58 || (176 <= format && format <= 178) || (182 <= format && format <= 196) || (210 <= format && format <= 213) || (208 == format)) { // 日期 this.formatString = "yyyy-MM-dd"; nextDataType = CellDataType.NUMBER; } else if (format == 20 || format == 32 || format == 183 || (200 <= format && format <= 209)) { // 时间 this.formatString = "HH:mm"; nextDataType = CellDataType.NUMBER; } if (this.formatString == null) { nextDataType = CellDataType.NULL; formatString = BuiltinFormats.getBuiltinFormat(formatIndex); } } }
package com.beyondsoft.util.base; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import java.util.List; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.xml.sax.Attributes; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler; import org.xml.sax.helpers.XMLReaderFactory; import com.beyondsoft.util.base.XLSXCovertCSVReader2.xssfDataType; /** * 抽象Excel2007读取器,excel2007的底层数据结构是xml文件,采用SAX的事件驱动的方法解析 * xml,需要继承DefaultHandler,在遇到文件内容时,事件会触发,这种做法可以大大降低 * 内存的耗费,特别使用于大数据量的文件。
* 作者:史红星 * */ public class Excel2007Reader extends DefaultHandler { //共享字符串表 private ReadOnlySharedStringsTable sharedStringsTable; //上一次的内容 private String lastContents; private boolean nextIsString; // Set when V start element is seen private boolean vIsOpen; private int sheetIndex = -1; private List<String> rowlist = new ArrayList<String>(); //当前行 private int curRow = 0; //当前列 private int curCol = 0; private int preCol = 0; //上一列列索引 //日期标志 private