Java 获取字符串值，当我使用日期类型 (apache poi) 从 excel 读取时

Get String value, when I read from excel with Date type (apache poi)

提问by Sergii Lisnychyi

I have an xlsx filethat I am reading with - Apache POI Library.

我有一个正在阅读的xlsx 文件- Apache POI 库。

For example, In some row, I have such cells values:

例如，在某些行中，我有这样的单元格值：

01-Sep-13 | 15136.00| Matt|......

13 年 9 月 1 日 | 15136.00| 马特|......

---

---

My goal is:Read all cells in the rows as String values. But as I see, I can't read 01-Sep-13as string, it only represents like Date type () with cell.getDateCellValue();

我的目标是：将行中的所有单元格读取为字符串值。但正如我所见，我无法将01-Sep-13读取为字符串，它仅表示像 Date 类型 () 和 cell.getDateCellValue();

---

---

1) Could I read somehow 01-Sep-13 as string: "01-Sep-13"; 2) Could I convert Date value to string in case I have different date patterns(ddMMyyyy) and (dd-MMM-yy);

1）我可以以某种方式将 01-Sep-13 读为字符串：“01-Sep-13”；2）如果我有不同的日期模式（ddMMyyyy）和（dd-MMM-yy），我可以将日期值转换为字符串吗？

Code:

代码：

When I iterate over rows and cells, Apache POI analyzes each cell type:

当我遍历行和单元格时，Apache POI 会分析每种单元格类型：

String normalizeCellType(Cell cell) {

    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
           return cell.getRichStringCellValue().getString());            
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {

                Date date = cell.getDateCellValue(); ????????????
                System.out.println(date);

            } else {

                ....
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
           return ....
        case Cell.CELL_TYPE_FORMULA:
           return ....
        default:
            System.out.println();
    }
}

采纳答案by rgettman

You have 2 options:

您有 2 个选择：

• Use Java's SimpleDateFormatclassto format the returned Dateas a Stringin the format of your choice. You can have a SimpleDateFormatinstance for each different format you need.
• Use Apache POI's DataFormatterclassto format the the Celldirectly.

• 使用Java的SimpleDateFormat类以格式返回Date为String您选择的格式。您可以SimpleDateFormat为您需要的每种不同格式创建一个实例。
• 使用Apache POI 的DataFormatter类Cell直接格式化。

回答by Keerthivasan

You can get the return value for cell#getDateCellValue() and use SimpleDateFormat to covert it to a String instance

您可以获取 cell#getDateCellValue() 的返回值并使用 SimpleDateFormat 将其转换为 String 实例

 // Create an instance of SimpleDateFormat used for formatting 
    // the string representation of date (month/day/year)
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");

    // Get the date today using cell.getDateCellValue() 
    Date today = cell.getDateCellValue()       
    // Using DateFormat format method we can create a string 
    // representation of a date with the defined format.
    String reportDate = df.format(today);

You can get the value of Date in String format - 31/10/2013. Hope this helps

您可以以字符串格式获取日期的值 - 31/10/2013。希望这可以帮助

回答by Mukesh S

Here are the answers for you questions:

以下是您问题的答案：

1) Could I read somehow 01-Sep-13 as string: "01-Sep-13";

1）我可以以某种方式将 01-Sep-13 读为字符串：“01-Sep-13”；

Yes you can. You need to set the Cell type to String before reading it. Something like this:

是的你可以。在读取之前，您需要将 Cell 类型设置为 String。像这样的东西：

.
.
.
int fcell = row.getFirstCellNum();// first cell number of excel
int lcell = row.getLastCellNum(); //last cell number of excel
while (rows.hasNext()) {
row = (XSSFRow) rows.next();//increment the row iterator
for (int i = fcell; i < lcell; i++) {
    row.getCell(i).setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
    .
    .
    ..//processing
    .
    .
    }
}

2.)Could I convert Date value to string in case I have different date patterns (ddMMyyyy) and (dd-MMM-yy);

2.）如果我有不同的日期模式（ddMMyyyy）和（dd-MMM-yy），我可以将日期值转换为字符串吗？

Since the first answer is already giving you the String value. Still to convert a String value to date you can use SimpleDateFormat as @rgettman and @Keerthi suggested

由于第一个答案已经为您提供了 String 值。仍然要将字符串值转换为日期，您可以使用 SimpleDateFormat 作为@rgettman 和@Keerthi 建议

Hope this will help...:)

希望这会有所帮助...:)

回答by Aki

DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
java.util.Date d =  row1.getCell(i).getDateCellValue();
String buy_date = df.format(d);
System.out.println("date is :- "+ buy_date);

output will be

输出将是

date is :- 2014/08/01

Now it will give date in Stringformat which you can store in your database.

现在它将String以您可以存储在数据库中的格式给出日期。

回答by Gagravarr

Apache POI has the functionality to do this for you, you just need to call it! The class you need to use is DataFormatter. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back. If you pass it a "date" cell (numeric cell with date formatting rules), it'll format it as a date and give you the string back

Apache POI 具有为您执行此操作的功能，您只需要调用它即可！您需要使用的类是DataFormatter。您将这个单元格传递给它，它会尽最大努力返回一个字符串，其中包含 Excel 将为您显示的该单元格内容。如果你传递给它一个字符串单元格，你会得到字符串。如果您将一个应用了格式规则的数字单元格传递给它，它将根据它们格式化数字并将字符串返回给您。如果您向它传递一个“日期”单元格（具有日期格式规则的数字单元格），它会将其格式化为日期并将字符串返回给您

You can see it in action with this round-trip bit of code:

您可以使用此往返代码来查看它的运行情况：

 DataFormat df = workBook.createDataFormat();
 CellStyle dateStyle = wb.createCellStye();
 setDataFormat.setDataFormat(df.getFormat("dd-mmm-yy"));

 Cell cell = row.createCell(0);
 cell.setCellStyle(dateStyle);

 // Set it to be a date
 Calendar c = Calendar.getInstance();
 c.set(2013,9-1,1); // Don't forget months are 0 based on Calendar
 cell.setCellValue( c.getTime() );

 // Get it formatted as a string
 DataFormatter formatter = new DataFormatter();
 String cellAsStr = formatter.formatCellValue(cell);

 // cellAsStr will now be "01-Sep-13"
 // based on the format rules applied to the cell

回答by jayeshecs

My goal is: Read all cells in the rows as String values. But as I see, I can't read 01-Sep-13 as string, it only represents like Date type () with cell.getDateCellValue();

我的目标是：将行中的所有单元格读取为字符串值。但正如我所见，我无法将 01-Sep-13 读取为字符串，它仅表示像 Date 类型 () 和 cell.getDateCellValue();

You can use XSSFExcelExtractor or ExcelExtractor to get correctly formatted cell value.

您可以使用 XSSFExcelExtractor 或 ExcelExtractor 来获取格式正确的单元格值。

Note: - 1) XSSFExcelExtractor is for XLSX file 2) ExcelExtractor is for XLS file

注意：- 1) XSSFExcelExtractor 用于 XLSX 文件 2) ExcelExtractor 用于 XLS 文件

回答by Udaya

It is so easy to read string values in excel sheet using java. this is the my way..

使用java读取excel表中的字符串值非常容易。这是我的方式..

Workbook wb=Workbook.getWorkbook(file);
        Sheet s= wb.getSheet(1);
        int row=s.getRows();
        int col=s.getColumns();
        for(int i=1;i<row;i++){
            for(int j=0;j<col;j++){
                Cell c=s.getCell(j,i);
                String MyString=c.getContents().toString();
                System.out.println(MyString);
               }
          }