高财政才是真的高

最新推荐文章于 2024-05-27 21:20:14 发布

对知识充满着渴望

最新推荐文章于 2024-05-27 21:20:14 发布

阅读量133

点赞数

本文链接：https://blog.csdn.net/qq_23850065/article/details/81275174

版权

package com.huawei.response.improve.a.little;

import java.util.List;

/**
* 解析excel表格的各级列头，建立映射关系
* @author gao
*
*/
public class ColumnParser {
   //属于几级列名，从1开始
   public int level ;
   public String columnName ;
   //直系资子列的列表
   public List<ColumnParser> childs;
   //直系子列的数目
   public int childNum ;
   //最后一级别的子类数目，对于每级列表的都相同
   public int totalNum;
   //它的y坐标
   public int Y;

   public ColumnParser(int level, String columnName, int childNum, int totalNum) {
       super();
       this.level = level;
       this.columnName = columnName;
       this.childNum = childNum;
       this.totalNum = totalNum;
   }
   public String getColumnName() {
       return columnName;
   }
   public void setColumnName(String columnName) {
       this.columnName = columnName;
   }
   public List<ColumnParser> getChilds() {
       return childs;
   }
   public void setChilds(List<ColumnParser> childs) {
       this.childs = childs;
   }
   public int getChildNum() {
       return childNum;
   }
   public void setChildNum(int childNum) {
       this.childNum = childNum;
   }
   public int getTotalNum() {
       return totalNum;
   }
   public void setTotalNum(int totalNum) {
       this.totalNum = totalNum;
   }
   public int getLevel() {
       return level;
   }
   public void setLevel(int level) {
       this.level = level;
   }
   public int getY() {
       return Y;
   }
   public void setY(int y) {
       Y = y;
   }

}

package com.huawei.response.improve.a.little;

import org.apache.poi.ss.util.CellRangeAddress;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CommonUtils {

public static int numLevel ,headNum, AllRow;
   public static XSSFSheet getSheet(String url) throws IOException {

       //读取xlsx文件
XSSFWorkbook xssfWorkbook = null;
//寻找目录读取文件
File excelFile = new File(url);
InputStream is = new FileInputStream(excelFile);
xssfWorkbook = new XSSFWorkbook(is);
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
return sheet;
   }
   public static Map<String,Integer> getCombineCell(XSSFSheet sheet) {
       Map<String,List<Integer>> cellMap = new HashMap<String,List<Integer>>();
       Map<String,Integer> map = new HashMap<String,Integer>();
       AllRow = sheet.getLastRowNum();
       String faStr = "";
       List<Integer> list = null;
       Map<Integer,Integer> sortMegion = sortMergedRegion(sheet);
       int lastRow = 0 ;
       int lastColumn =0 ;
       for (Map.Entry<Integer, Integer> entry : sortMegion.entrySet()) {
           int i = entry.getKey();
               CellRangeAddress ca = sheet.getMergedRegion(i);
               faStr = "";
               int firstRow = ca.getFirstRow();
               lastRow = ca.getLastRow();
               if(ca.getLastColumn()>lastColumn) {
                   lastColumn = ca.getLastColumn();
               }
               //第一级没有父字符串
               list =new ArrayList<Integer>();
               list.add(ca.getFirstColumn());
               list.add(ca.getLastColumn());
               faStr=getFaStr(sheet,cellMap,ca.getFirstColumn(),ca.getLastColumn());
               String cellValue =faStr+sheet.getRow(firstRow).getCell(ca.getFirstColumn()).getStringCellValue();
               cellMap.put(cellValue, list);
           }
       map = getLastRow(sheet,cellMap,lastRow,lastColumn);
       return map;
       }
   public static Map<Integer,Integer> sortMergedRegion(XSSFSheet sheet){
       int sheetmergerCount = sheet.getNumMergedRegions();
       Map<Integer,Integer> map = new HashMap<Integer,Integer>();
       for (int i = 0; i < sheetmergerCount; i++) {
           // 获得合并单元格加入list中
           CellRangeAddress ca = sheet.getMergedRegion(i);
           map.put(i, ca.getLastRow());
           }
       return sortByComparator(map) ;
   }
   public static Map<String,Integer> getLastRow(XSSFSheet sheet,Map<String,List<Integer>> cellMap,int row,int column) {
       String faStr = "";
       Map<String,Integer> map = new HashMap<String,Integer>();
       for(int i =0 ; i<=column;i++) {
           faStr=getFaStr(sheet,cellMap,i,i);
           String cellValue = faStr+sheet.getRow(row+1).getCell(i).getStringCellValue();
           //System.out.println(cellValue);
           map.put(cellValue, i);
       }
       return sortByComparator(map) ;

   }
   public static String getFaStr(XSSFSheet sheet,Map<String,List<Integer>> cellMap,int firstComumn,int lastColumn) {
       String returnStr = "";
       for (Map.Entry<String, List<Integer>> entry : cellMap.entrySet()) {
           if(firstComumn>=entry.getValue().get(0)&&lastColumn<=entry.getValue().get(1)) {
               if(entry.getKey().length()>returnStr.length()) {
                   returnStr=entry.getKey()+"####";
               }

           }
           }
       return returnStr;
   }
   /**
   * 对Map按照value进行排序
   * @param unsortMap
   * @return 已经按照升序排序的Map
   */
   public static Map sortByComparator(Map unsortMap){
       List list = new LinkedList(unsortMap.entrySet());
       // System.out.println("list:"+list);
       Collections.sort(list, new Comparator()
       {
       public int compare(Object o1, Object o2)
       {
       return ((Comparable) ((Map.Entry) (o1)).getValue())
       .compareTo(((Map.Entry) (o2)).getValue());
       }
       });
       Map sortedMap = new LinkedHashMap();
       for (Iterator it = list.iterator(); it.hasNext();) {
       Map.Entry entry = (Map.Entry)it.next();
       sortedMap.put(entry.getKey(), entry.getValue());
       }
       return sortedMap;
       }
   /**
   * 通过列名来获取该列的所有内容
   * @param sheet 页
   * @param map <列名，对应列号>
   * @param columnName 列名
   */
   public static void getValueByColumnName(XSSFSheet sheet,Map<String,Integer> map,String columnName) {

       List<Integer> list = new ArrayList<Integer>();
       List<String> listStr = new ArrayList<String>();
           Set<String> set = map.keySet();
           for(String str : set) {
               if(str.contains(columnName)) {
                   listStr.add(str);
                   list.add(map.get(str));
               }
       }
           int StrNum =0 ;
       for(Integer indexNum:list) {
           System.out.println("列名："+listStr.get(StrNum));
           StrNum++;
           for(int i = numLevel+ headNum; i<= AllRow;i++) {
           if(sheet.getRow(i)!=null) {
               if(sheet.getRow(i).getCell(indexNum)!=null) {
                       System.out.print("行号:"+(i+1)+",值："+sheet.getRow(i).getCell(indexNum).getStringCellValue()+";");
               }else {
                   //单元格为空
                   System.out.print("行号:"+(i+1)+"值："+"");
               }
           }else {
               //行为空
               System.out.print("行号："+(i+1)+"值："+"");
           }
       }
           System.out.println();
       }

   }
   /**
   * 直接调用这个函数就可以实现解析Excel表格了。
   * @param fileUrl xlsx文件所在的路径
   * @param searchUrl 需要寻找的列，列名格式采用一级列头+####+二级列头+####三级列头+####...
   * @param numLevel1 需要解析的Excel多少级列头
   * @param headNum1 从哪一行开始有数据，也就是一级列头对应的行号，Excel中第一行行号为0
   * @throws IOException
   */
   public static void parserExecl(String fileUrl,String searchUrl,int numLevel1,int headNum1) throws IOException {
       XSSFSheet sheet = getSheet(fileUrl);
       Map<String,Integer> map = getCombineCell(sheet);
       numLevel = numLevel1;
       headNum = headNum1;
       getValueByColumnName(sheet,map,searchUrl);
   }
   public static void main(String[] args) throws IOException {
       CommonUtils.parserExecl("C:/Users/gao/Desktop/gao.xlsx", "东南大学", 3, 0);

   }

}

<groupId>com.huawei.response</groupId>
<artifactId>improve.a.little</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>

<name>improve.a.little</name>
<url>http://maven.apache.org</url>

<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>

<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.2</version>
</dependency>

</dependencies>
</project>