package zhangchuang.shuai.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.xssf.usermodel.XSSFWorkbook;
public class XLS {
public static int[][] AnalysisXLS(String filePath) {
Workbook wb =null;
Sheet sheet = null;
Row row = null;
List<Map<String,String>> list = null;
String cellData = null;
int rownum = 0;
int colnum = 0;
wb = readExcel(filePath);
int [][] nums = null;
if(wb != null){
list = new ArrayList<Map<String,String>>();
sheet = wb.getSheetAt(0);
rownum = sheet.getPhysicalNumberOfRows();
System.out.println("rownum="+rownum);
row = sheet.getRow(0);
colnum = row.getPhysicalNumberOfCells();
System.out.println("colnum="+colnum);
nums=new int[rownum][colnum];
for (int i = 0; i<rownum; i++) {
row = sheet.getRow(i);
if(row !=null){
for (int j=0;j<colnum;j++){
cellData = (String) getCellFormatValue(row.getCell(j));
nums[i][j]=Integer.parseInt(cellData);
}
}else{
break;
}
}
}
for(int i=0;i<rownum;i++) {
for(int j=1;j<colnum-1;j++)
for(int k=j+1;k<colnum;k++) {
if(nums[i][k]<nums[i][j]) {
int temp=nums[i][k];
nums[i][k]=nums[i][j];
nums[i][j]=temp;
}
}
}
return nums;
}
public static Workbook readExcel(String filePath){
Workbook wb = null;
if(filePath==null){
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if(".xls".equals(extString)){
return wb = new HSSFWorkbook(is);
}else if(".xlsx".equals(extString)){
return wb = new XSSFWorkbook(is);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
public static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:{
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA:{
if(DateUtil.isCellDateFormatted(cell)){
cellValue = cell.getDateCellValue();
}else{
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING:{
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
}else{
cellValue = "";
}
return cellValue;
}
}
package zhangchuang.shuai.main;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import zhangchuang.shuai.util.XLS;
public class testMain {
public static void main(String[] args) {
int [][] a=XLS.AnalysisXLS("D:\\zc\\11xuan5.xls");
List<String> list=new ArrayList<String>();
for(int i=0;i<a.length;i++) {
String temp="";
for(int j=1;j<6;j++) {
temp+=a[i][j];
}
temp+="_"+a[i][0];
list.add(temp);
}
Map<String,String> map=new HashMap<String,String>();
for (String str:list) {
String str2=str.split("_")[0];
String str3=str.split("_")[1]+"_";
if(map.get(str2)==null)
map.put(str2, str3+"1");
else
map.put(str2,map.get(str2)+"_"+str3+(Integer.parseInt(map.get(str2).split("_")[map.get(str2).split("_").length-1])+1));
}
Set<Entry<String, String>> set= map.entrySet();
List<String> sortList=new ArrayList<String>();
for(Entry<String, String> entry:set ) {
sortList.add(entry.getKey()+"_"+entry.getValue());
}
sortList.sort(new Comparator<String>() {
public int compare(String s1, String s2) {
if(Integer.parseInt(s1.split("_")[s1.split("_").length-1])<Integer.parseInt(s2.split("_")[s2.split("_").length-1]))
return 1;
return -1;
}
});
List<String> sortList2=new ArrayList<String>();
for (int i = 0; i < sortList.size(); i++) {
System.out.println(sortList.get(i));
sortList2.add(sortList.get(i));
}
}
}
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>zhangchuang</groupId>
<artifactId>shuai</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>shuai</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
</dependencies>
</project>