Java的一个程序，通过在xls文件中使用逗号从地址字符串中解析USA状态

Question

我有一个带有“address.xls”的excel文件，它只包含一个列“a”，行中的值为美国地址。

A
_________________________________________
NO FIXED ADDRESS1, TALLAHASSEE, FL 32301
NO FIXED ADDRESS2, TALLAHASSEE, FL 32303
NO FIXED ADDRESS, SCOTTSDALE, AZ 85258
NO FIXED ADDRESS, AR

邮编：46240-7670,印第安纳波利斯Keystone Xing 8900

我想提取的状态代码只作为一个输出字符串，而不重复重复。

["FL","AZ","AR","IN"]

Answer 1

1. 创建maven项目
2. 在pom.xml中添加以下依赖项

</dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>2.6.0</version>
    </dependency>
</dependencies>

导入org.apache.poi.hssf.usermodel.hssfsheet；导入org.apache.poi.hssf.usermodel.hssfworkbook；导入org.apache.poi.ss.usermodel.cell；导入org.apache.poi.ss.usermodel.row；

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashSet;
import java.util.Set;

/**
 * @author
 * Waqas Ahmed
 */
public class StateParser
{
    public static void main(String args[]) throws IOException
    {
        FileInputStream fis=new FileInputStream(new File("C:\\files\\address.xls"));
        HSSFWorkbook wb=new HSSFWorkbook(fis);
        HSSFSheet sheet=wb.getSheetAt(0);
        int index = 1;
        Set<String> statesSet = new HashSet<>();
        for(Row row: sheet) {
            for(Cell cell: row) {
                String[] arr = cell.getStringCellValue().split(",");
                String state = "";
                if(arr.length <= 2){
                    state = arr[1].substring(0,3);
                }else{
                    state = arr[2].substring(0,3);
                }
                System.out.print("Row# " + index + " =  " + cell.getStringCellValue() + " --------------->> state: " + state);
                statesSet.add("\""+state.trim()+"\"");
                state = "";
                index++;
            }
            System.out.println();
        }
        System.out.println("-------------------------------------------");
        System.out.println(statesSet.toString().trim());
    }


}

在位置“C:\files\address.xls”处添加“address.xls”文件或更改Java文件中的目录路径。

运行程序

输出将打印在控制台中

[“CO”、“NY”、“MS”、“DE”、“HI”、“WV”、“NJ”、“NH”、“TN”、“OH”、“MD”、“SC”、“VA”、“Al”、“WA”、“NV”、“FL”、“In”、“TX”、“IL”、“OR”、“MN”、“Ca”、“MI”、“Ga”、“Az”、“Me”、“Ri”、“NC”、“La”、“MA”、“Wi”、“PA”、“CT”、“Ar”]