JAVA读取EXCEL文件号段解析

EXCEL文件  有地区的号段  读取excel文件并解析它.

EXCEL 的格式大致如下:

1330+551 = 1330551,目的是为了得到1330551.

代码如下:

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.DateUtil;




public class AnalyseMobile {


static String PATH = "E://area.xls";//E://area.xls
static String[][] startWithMobile = new String[8][10];
static String[][][] endWithMobile = new String[8][17][11];


public static void getAllByExcel(String filepath) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
// 同时支持Excel 2003、2007
File excelFile = new File(filepath); // 创建文件对象
FileInputStream is = new FileInputStream(excelFile); // 文件流
Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel
// 2003/2007/2010
// 都是可以处理的
int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
// 遍历每个Sheet


for (int s = 0; s < sheetCount; s++) {
// System.out.println("当前sheet页码:" + s);
Sheet sheet = workbook.getSheetAt(s);
int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
// 遍历每一行
for (int r = 0; r < rowCount; r++) {
Row row = sheet.getRow(r);
int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数
// 遍历每一列
for (int c = 0; c < cellCount; c++) {
Cell cell = row.getCell(c);
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case Cell.CELL_TYPE_STRING: // 文本
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: // 数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); // 日期型
} else {
cellValue = String.valueOf((int) cell
.getNumericCellValue()); // 数字
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔型
cellValue = String.valueOf(cell
.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: // 空白
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: // 错误
cellValue = "错误";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = "错误";
break;
default:
cellValue = "错误";
}
if (r == 1 && c > 2 && c < 13) {
startWithMobile[s][c - 3] = cellValue;
}
if (r > 1 && r < 19 && c > 2 && c < 14) {
/*
* System.out.print("第" + r + "行,第" + c + "列" +
* cellValue + "\t");
*/
endWithMobile[s][r - 2][c - 3] = cellValue;
}
}


}


}
} catch (Exception e) {
e.printStackTrace();
}


}


public static List<String> getAreaNumber() {


getAllByExcel(PATH);
List<String> finalist = new ArrayList<String>();
List<String> list = new ArrayList<String>();
for (int a = 0; a < endWithMobile.length; a++) {
for (int b = 0; b < endWithMobile[a].length - 1; b++) {
for (int c = 0; c < endWithMobile[a][b].length - 1; c++) {
String[] endPart = endWithMobile[a][b][c].split("、");
for (int i = 0; i < endPart.length; i++) {


String[] endPartsp = endPart[i].split("-");
if (endPartsp.length == 1) {
String com = startWithMobile[a][c] + endPartsp[0];
/*if(b==0){
list.add(com+"--->"+"合肥");
}*/
switch (b) {
case 0:
list.add(com+"_"+"合肥");
break;
case 1:
list.add(com+"_"+"池州");
break;
case 2:
list.add(com+"_"+"毫州");
break;
case 3:
list.add(com+"_"+"阜阳");
break;
case 4:
list.add(com+"_"+"宿州");
break;
case 5:
list.add(com+"_"+"安庆");
break;
case 6:
list.add(com+"_"+"马鞍山");
break;
case 7:
list.add(com+"_"+"淮南");
break;
case 8:
list.add(com+"_"+"芜湖");
break;
case 9:
list.add(com+"_"+"蚌埠");
break;
case 10:
list.add(com+"_"+"滁州");
break;
case 11:
list.add(com+"_"+"巢湖");
break;
case 12:
list.add(com+"_"+"六安");
break;
case 13:
list.add(com+"_"+"宣城");
break;
case 14:
list.add(com+"_"+"铜陵");
break;
case 15:
list.add(com+"_"+"淮北");
break;
case 16:
list.add(com+"_"+"黄山");
break;
default:
break;
}


} else {
int diffnum = Integer.parseInt(endPartsp[1])
- Integer.parseInt(endPartsp[0]);


for (int j = 0; j < diffnum + 1; j++) {
int num = Integer.parseInt(endPartsp[0]) + j;
String numStr = "";
if(num<10){
numStr = "00"+num;
}else if(num<100&&num>=10){
numStr = "0"+num;
} else{
numStr = ""+num;
}
String com = startWithMobile[a][c] + numStr;

//list.add(com+"--->"+b+c);
switch (b) {
case 0:
list.add(com+"_"+"合肥");
break;
case 1:
list.add(com+"_"+"池州");
break;
case 2:
list.add(com+"_"+"毫州");
break;
case 3:
list.add(com+"_"+"阜阳");
break;
case 4:
list.add(com+"_"+"宿州");
break;
case 5:
list.add(com+"_"+"安庆");
break;
case 6:
list.add(com+"_"+"马鞍山");
break;
case 7:
list.add(com+"_"+"淮南");
break;
case 8:
list.add(com+"_"+"芜湖");
break;
case 9:
list.add(com+"_"+"蚌埠");
break;
case 10:
list.add(com+"_"+"滁州");
break;
case 11:
list.add(com+"_"+"巢湖");
break;
case 12:
list.add(com+"_"+"六安");
break;
case 13:
list.add(com+"_"+"宣城");
break;
case 14:
list.add(com+"_"+"铜陵");
break;
case 15:
list.add(com+"_"+"淮北");
break;
case 16:
list.add(com+"_"+"黄山");
break;
default:
break;
}

}
}
}


}
}
}
/*
* System.out.println(startWithMobile[1].length 10 );
* System.out.println(endWithMobile.length 8 );
* System.out.println(endWithMobile[1].length-1 16 );
* System.out.println(endWithMobile[1][2].length-1 10);
*/
/*
* String [] endPart = endWithMobile[0][0][3].split("、"); for (int i =
* 0; i < endPart.length; i++) {
* 
* String [] endPartsp = endPart[i].split("-"); if(endPartsp.length==1){
* list.add(startWithMobile[0][3]+endPartsp[0]); }else{ int diffnum =
* Integer.parseInt(endPartsp[1]) - Integer.parseInt(endPartsp[0]);
* 
* for (int j = 0; j < diffnum+1; j++) { int num =
* Integer.parseInt(endPartsp[0])+j;
* list.add(startWithMobile[0][3]+num); } } }
*/
for (int i = 0; i < list.size(); i++) {
if (list.get(i).length() > 9) {
finalist.add(list.get(i));
}
}
for (int i = 0; i < finalist.size(); i++) {
finalist.get(i);
}


return finalist;
}


public static void main(String[] args) {


List<String> list = getAreaNumber();
for (int i = 0; i < list.size(); i++) {
String citytoal[] = list.get(i).split("_");
System.out.println("号段:"+citytoal[0]+"城市:"+citytoal[1]);
}


}


}