JAVA读取EXCEL文件号段解析

Bertha 。 2022-06-09 11:57 263阅读 0赞

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

EXCEL 的格式大致如下:

![Center][]

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\]);  
\}  
  
  
\}  
  
  
\}

[Center]: /images/20220609/caf714345da94324b31b8dacdc2aa6b3.png