java poi处理excel多sheet并实现排序

2018-06-18 02:55:39来源：未知阅读 ()

需求：有一个数据字典全量汇总表，其中第一个sheet为目录，包括编号和表名，第二个以后为表的明细。其中sheet名就是表名但无序，sheet内字段序号无序有空行

现在要求将其中101，104，107，111表中的格式列和字段名称以及表名取出，生成批量语句，要求按给的编号有序输出，字段出要有序并排除窄。

输出结果如下：

insert into t_export(export_id,owner,table_name,col_name,format) values(101,'T_A','scott','id','20180308001');
insert into t_export(export_id,owner,table_name,col_name,format) values(101,'T_A','scott','sal','2000');
insert into t_export(export_id,owner,table_name,col_name,format) values(101,'T_A','scott','20','张三');
insert into t_export(export_id,owner,table_name,col_name,format) values(101,'T_A','scott','remark','hello');
insert into t_export(export_id,owner,table_name,col_name,format) values(101,'T_A','scott','birthday','40479');
insert into t_export(export_id,owner,table_name,col_name,format) values(101,'T_A','scott','age','20');
.
.
.
至111

excel如下:

下载poi包poi-3.17.jar并引入eclipse的java工程，仅需要RowInfo.java,ShowExcel.java

RowInfo.java:

package pu;

public class RowInfo implements Comparable<RowInfo>{//实现字段排序
    
    private int rownumb;
    private int expId;
    private String tableName;
    private String columnName;
    private String formatInfo;
    public RowInfo(int rownumb,int expId, String tableName, String columnName,String formatInfo) {
        super();
        this.rownumb=rownumb;
        this.expId = expId;
        this.tableName = tableName;
        this.columnName=columnName;
        this.formatInfo = formatInfo;
    }
    @Override
    public String toString(){
        return "insert into t_export(export_id,owner,table_name,col_name,format) values('"
                +this.expId+"','SCOTT','"
                +this.tableName+"','"
                +this.columnName+"','"
                +this.formatInfo+"');";
    }
    @Override
    public int compareTo(RowInfo row) {//重写排序方法
        // TODO Auto-generated method stub
        return this.rownumb - row.rownumb;
    }

    public int getExpId() {
        return expId;
    }
    public void setExpId(int expId) {
        this.expId = expId;
    }
    public String getTableName() {
        return tableName;
    }
    public void setTableName(String tableName) {
        this.tableName = tableName;
    }
    public String getFormatInfo() {
        return formatInfo;
    }
    public void setFormatInfo(String formatInfo) {
        this.formatInfo = formatInfo;
    }
    public int getRownumb() {
        return rownumb;
    }
    public void setRownumb(int rownumb) {
        this.rownumb = rownumb;
    }
    public String getColumnName() {
        return columnName;
    }
    public void setColumnName(String columnName) {
        this.columnName = columnName;
    }

}

ShowExcel.java

package pu;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ShowExcel {
    public static  void showExcelSheet(Map<Integer,ArrayList> map) throws Exception {//处理方法
        HashMap     hashmap     =     new     HashMap();
　　　　　//放入需求的表名
        hashmap.put("T_A","101");
        hashmap.put("T_D","104");
        hashmap.put("T_G","107");
        hashmap.put("T_K","111");

        HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(new File("E:\\data\\Dictionary.xls")));
        HSSFSheet sheet=null;
        FileOutputStream fs = new FileOutputStream(new File("E:\\output\\temp.txt"),false);//每次都覆盖
        PrintStream p = new PrintStream(fs);
        p.println("sheet amount==="+workbook.getNumberOfSheets());

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
             sheet=workbook.getSheetAt(i);
             ArrayList<RowInfo> list_rows = new ArrayList<>();//每个sheet需new一个list

            if (hashmap.containsKey(sheet.getSheetName())) 
             {
                p.println("reading the sheet "+sheet.getSheetName());    
             for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//获取每行
                HSSFRow row=sheet.getRow(j);
                if(row==null){//跳过空行
                    System.out.println("wowowo");
                    continue;
                }
                System.out.print("row amount=="+sheet.getPhysicalNumberOfRows()+" and now row"+j+"\t");
            //    p.print("table_name_"+sheet.getSheetName() +"\t");

                if(j>2 && row.getCell(1)!=null){//此处跳过sheet中的前三行
                    p.print("table_name_"+sheet.getSheetName()+" row=="+j +"==\t");
                    p.print(hashmap.get(sheet.getSheetName()).toString() +"\t");
                    p.print(sheet.getSheetName() +"\t");
                    p.print(row.getCell(1).toString().toUpperCase()+"\t");
                    p.print(row.getCell(5)+"\t");
                    p.println();
　　　　　　　　　　　　//装载每行的序号，字段名称，格式
                    list_rows.add(new RowInfo((int)row.getCell(0).getNumericCellValue(),Integer.parseInt(hashmap.get(sheet.getSheetName()).toString()),sheet.getSheetName().toString(),row.getCell(1).toString().toUpperCase().trim(),row.getCell(5).toString()));
                }
                for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//获取每个单元格
                    if(row.getCell(k)!=null){
                         System.out.print(row.getCell(k)+"\t");
                         //p.print(row.getCell(k)+"\t");
                    }else
                        System.out.print("row.getCell(k) is nullnull"+"\t");
                }
               // p.println(" row over");
                System.out.println("---Sheet表"+i+"处理完毕---");
    }
             p.println("---Sheet表"+i+"处理完毕---");
             // 排序
             Collections.sort(list_rows);
　　　　　　　　　
　　　　　　　　//读完一张装入一张
             map.put(Integer.parseInt(hashmap.get(sheet.getSheetName()).toString()), list_rows);
        
    }
 }
        p.close();

        }
public static void main(String[] args) throws Exception{
     FileOutputStream f = new FileOutputStream(new File("E:\\output\\result.txt"),false);
     PrintStream rs = new PrintStream(f);
    
    try{
        Map<Integer,ArrayList> map=new HashMap<>();
        showExcelSheet(map);
        Map<Integer,ArrayList> treemap=new TreeMap<>();//实现结果表对象按export_id排序

        treemap.putAll(map);
        for(int k:treemap.keySet()){
            System.out.println(k);
        }
        for(ArrayList<RowInfo> tab :treemap.values()){
            for(RowInfo row:tab){
                rs.println(row.toString());
            }
        }
    }catch(Exception e){e.printStackTrace();}
}

}