使用jxl解析Excel

Question

我使用jxl读取.xls文件，以下是我解析xls文件的代码。它工作得很好，但问题是当我插入日期字段时，它只读取年份的最后两位数字。例如，如果我将12/13/2088和12/13/1988。两者都将读取为12/13/88。这个问题有什么解决办法吗？我是否可以对excel单元格进行格式化，或者更改当前的代码？

ArrayList listDet=new ArrayList();
    HashMap mapDet=null;
    HashMap primeDetails=new HashMap();

    WorkbookSettings ws = null;
    Workbook workbook = null;
    Sheet s = null;
    Cell rowData[] = null;
    int rowCount = '0';
    int columnCount1 = '0';
    int columnCount = '0';
    DateCell dc = null;
    int totalSheet = 0;

    try {
        ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        ws.setCellValidationDisabled(true);

        workbook = Workbook.getWorkbook(fileInputStream, ws);

        totalSheet = workbook.getNumberOfSheets();
        //Getting Default Sheet i.e. 0
        s = workbook.getSheet(0);

        //Total Total No Of Rows in Sheet, will return you no of rows that are occupied with some data
        System.out.println("Total Rows inside Sheet:" + s.getRows());
        rowCount = s.getRows();

        //Total Total No Of Columns in Sheet
        System.out.println("Total Column inside Sheet:" + s.getColumns());
        columnCount = s.getColumns();


        //Reading Individual Row Content
        for (int i = 0; i <rowCount ; i++) {
            //Get Individual Row
            rowData = s.getRow(i);
                mapDet=new HashMap();
                mapDet.put("col1",rowData[0].getContents());
                mapDet.put("col2",rowData[1].getContents());
                mapDet.put("col3",rowData[2].getContents());
                mapDet.put("col4",rowData[3].getContents());
                mapDet.put("col5",rowData[4].getContents());
                mapDet.put("col6",rowData[5].getContents());
                mapDet.put("col7",rowData[6].getContents());
                mapDet.put("col8",rowData[7].getContents());
                mapDet.put("col9",rowData[8].getContents());

                listDet.add(mapDet);



    }
        workbook.close();           
    } catch (IOException e) {
        e.printStackTrace();
    } catch (BiffException e) {
        e.printStackTrace();
    }
    catch(Exception exp)
    {

        exp.printStackTrace();
    }

Answer 1

根据javadocs/2_6_10/docs/jxl/Cell的说法，方法：getContents()是一个“将单元格的内容作为字符串返回的又快又脏的函数”。

由于getContents()不允许完全控制格式，因此您可以定义自己的函数来控制CellType.DATE的格式；例如：

 private String getContents(Cell cell) {
    DateCell dCell=null;
    if(cell.getType() == CellType.DATE)
    {
      dCell = (DateCell)cell;
      // System.out.println("Value of Date Cell is: " + dCell.getDate()); 
      // ==> Value of Date Cell is: Thu Apr 22 02:00:00 CEST 2088
      SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");  
      // System.out.println(sdf.format(dCell.getDate()));
      // ==> 2088-04-22   
      return sdf.format(dCell.getDate());
    }
    // possibly manage other types of cell in here if needed for your goals   
    // read more: http://www.quicklyjava.com/reading-excel-file-in-java-datatypes/#ixzz2fYIkHdZP 
    return cell.getContents();
}

然后，您只需根据调用您自己的私有函数来更改代码：

mapDet.put("col1", getContents(rowData[0]));
mapDet.put("col2", getContents(rowData[1]));
mapDet.put("col3", getContents(rowData[2]));
mapDet.put("col4", getContents(rowData[3]));
mapDet.put("col5", getContents(rowData[4]));
mapDet.put("col6", getContents(rowData[5]));
mapDet.put("col7", getContents(rowData[6]));
mapDet.put("col8", getContents(rowData[7]));
mapDet.put("col9", getContents(rowData[8]));

而不是

mapDet.put("col1",rowData[0].getContents());
...
mapDet.put("col9",rowData[8].getContents());