使用Apache POI在Microsoft Excel XSSF中存储电话号码

I have a phone number stored in Excel as a String, the Excel file is created successfully and the data is free from errors but every phone number has a "number stored as text" error next to it.

我有一个以字符串形式存储在Excel中的电话号码，Excel文件被成功创建，数据没有错误，但是每个电话号码旁边都有一个“以文本形式存储的号码”错误。

I have read online that I should use the special Phone Number format included with excel or a custom 000-000-0000 format. I can set these using the excel program but not from my Java code.

我已经在网上看到，我应该使用excel的特殊电话号码格式，或者是一个定制的，000-000 000的格式。我可以使用excel程序设置它们，但不能使用Java代码。

I have looked around for information on setCellType and DataFormat but I assume CellType must be String and I don't see how I can use DataFormat for anything but dates.

我已经查找了关于setCellType和DataFormat的信息，但是我假设CellType必须是String，我不知道如何使用DataFormat除了日期之外的任何东西。

I have also had a look at DataFormatter but I do not understand how to use it for storing data. It looks like it's only meant to help with reading data. http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html

我也看过DataFormatter，但我不知道如何使用它存储数据。看起来它只是用来帮助阅读数据。http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html

How can I do either of the following?

我如何做以下任何一种?

1) Flag a cell as 'ignore error', to ignore the "number stored as Text" error
2) Make use of the built in Excel cell format "Special > Phone Number"

1)标记一个单元格为“忽略错误”，忽略“作为文本存储的数字”错误2)使用内置的Excel单元格格式“特殊>电话号码”

For 1) it appears there is a flag that persists through saving and closing file, I don't know how to edit or view it with POI. There is a post about it:

对于1)似乎存在一个通过保存和关闭文件而持续存在的标志，我不知道如何使用POI编辑或查看它。有一篇关于它的文章:

Pages 669 and 670 of the excel docs cover FeatFormulaErr2, which is held in FeatRecord shared features, which in theory allows you to store the fact that "number as text" should be ignored for a cell range

excel文档的669页和670页包含了featformaerr2，它保存在FeatRecord共享特性中，在理论上，它允许您存储“作为文本的数字”在计算单元范围内应该被忽略的事实

We've also got two test files, one with the warnings on, and one with them off - 46136-NoWarnings.xls and 46136-WithWarnings.xls . I didn't create them however!

我们还有两个测试文件，一个带有警告，另一个带有警告——46136-NoWarnings。xls和46136 - withwarnings。xls。但是我没有创造它们!

Nick http://mail-archives.apache.org/mod_mbox/poi-user/201003.mbox/%3C27823222.post@talk.nabble.com%3E

尼克http://mail-archives.apache.org/mod_mbox/poi-user/201003.mbox/%3C27823222.post@talk.nabble.com % 3 e

It appears this can be done in VBA with cell.Errors.Item(xlNumberAsText).Ignore = True but there seems to be no equivalent for POI

这似乎可以在VBA中使用cell.Errors.Item(xlNumberAsText)完成。忽略= True，但POI似乎没有对应的值

4 个解决方案

#1

I have figured out how to achieve #2) Make use of the built in Excel cell format "Special > Phone Number"

我已经知道如何实现#2)利用内置的Excel cell格式“特殊>电话号码”

Phone Numbers in Excel are stored as CELL_TYPE_NUMERIC, not CELL_TYPE_STRING

Excel中的电话号码存储为CELL_TYPE_NUMERIC，而不是CELL_TYPE_STRING

try {
    row.createCell(0).setCellValue(Long.parseLong(aNumericOnlyPhoneNumberString));
} catch (NumberFormatException e) {
    row.createCell(0);
}
CellStyle phoneNumberStyle = wb.createCellStyle();
phoneNumberStyle.setDataFormat(wb.createDataFormat().getFormat("(000) 000-0000"));
row.getCell(0).setCellStyle(phoneNumberStyle);

#2

I am not sure if you are doing a datatype conversion before writing to the excel. May be you can use something like this :

我不确定您是否在写excel之前进行了数据类型转换。也许你可以用这样的东西:

if(cellData.getCellType() == 1)
    cell.setCellValue((String)cellData.getData());
else if(cellData.getCellType() == 0)
    cell.setCellValue(((Integer)cellData.getData()).intValue());

#3

-1

Hey Try code given below-

嘿，试试下面给出的代码

@Test
public void writeToExcel() {
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook(); 

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Employee Data");

    //This data needs to be written (Object[])
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    data.put("1", new Object[] {"ID", "NAME", "PHONE"});
    data.put("2", new Object[] {1, "Amit", "9865321425"});
    data.put("3", new Object[] {2, "Lokesh","9562264578"});
    data.put("4", new Object[] {3, "John", "9458262145"});


    //Iterate over data and write to sheet
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset)
    {
        Row row = sheet.createRow(rownum++);
        Object [] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr)
        {
           Cell cell = row.createCell(cellnum++);
           if(obj instanceof String)
                cell.setCellValue((String)obj);
            else if(obj instanceof Integer)
                cell.setCellValue((Integer)obj);
        }
    }
    try
    {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File("D:/writeTest.xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("writeTest.xlsx written successfully on disk.");
    } 
    catch (Exception e) 
    {
        e.printStackTrace();
    }
}

By default cell type for excel is String. If you want to store phone number in numeric format then you have to set cell type to numeric.

默认情况下，excel的单元格类型是String。如果要以数字格式存储电话号码，则必须将手机类型设置为数字。

           if(obj instanceof String)
                cell.setCellValue((String)obj);
            else if(obj instanceof Integer){
               // set cell format to numeric
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue((Integer)obj);
            }

While reading excel file remember one thing that you are reading String type cell data or Numeric type cell data.

在读取excel文件时，请记住您正在读取字符串类型的单元数据或数字类型的单元数据。

To read String type cell data use code as-

要读取字符串类型的单元格数据，请使用代码as-

cell.getStringCellValue();

and to read numeric cell type data--

读取数值单元类型数据—

cell.getNumericCellValue();

#4

-1

try this its work perfectly. this is function for read excel xlsx format and store all data in array list..

把它做得完美无缺。此函数用于读取excel xlsx格式并将所有数据存储在数组列表中。

     public ArrayList Xreadexcel(String file) {        
    boolean f = false;
    ArrayList arraycontainer = new ArrayList();
    try {
        FileInputStream myInput = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowStart = sheet.getFirstRowNum();
        int rowEnd = sheet.getLastRowNum() + 1;
        int count = workbook.getNumberOfSheets();
        if (count > 1) {
            System.out.println("Only one Sheet Allowed");
        } else {
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                int lastColumn = row.getLastCellNum();
                ArrayList arraylist = new ArrayList();
                int cn = 0;
                for (cn = 0; cn < lastColumn + 1; cn++) {
                    Cell cell = row.getCell(cn, Row.RETURN_NULL_AND_BLANK);
                    if ((cell == null) || (cell.equals("")) || (cell.getCellType() == cell.CELL_TYPE_BLANK)) {
                        arraylist.add("");
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        arraylist.add(cell);
                    }
                }
                arraycontainer.add(arraylist);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return arraycontainer;
}

#1