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

需求：有一个数据字典全量汇总表，其中第一个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:

packagepu;public class RowInfo implements Comparable{//实现字段排序private intrownumb;private intexpId;privateString tableName;privateString columnName;privateString formatInfo;public RowInfo(int rownumb,intexpId, String tableName, String columnName,String formatInfo) {super();this.rownumb=rownumb;this.expId =expId;this.tableName =tableName;this.columnName=columnName;this.formatInfo =formatInfo;

}

@OverridepublicString toString(){return "insert into t_export(export_id,owner,table_name,col_name,format) values('"

+this.expId+"','SCOTT','"

+this.tableName+"','"

+this.columnName+"','"

+this.formatInfo+"');";

}

@Overridepublic intcompareTo(RowInfo row) {//重写排序方法//TODO Auto-generated method stub

return this.rownumb -row.rownumb;

}public intgetExpId() {returnexpId;

}public void setExpId(intexpId) {this.expId =expId;

}publicString getTableName() {returntableName;

}public voidsetTableName(String tableName) {this.tableName =tableName;

}publicString getFormatInfo() {returnformatInfo;

}public voidsetFormatInfo(String formatInfo) {this.formatInfo =formatInfo;

}public intgetRownumb() {returnrownumb;

}public void setRownumb(intrownumb) {this.rownumb =rownumb;

}publicString getColumnName() {returncolumnName;

}public voidsetColumnName(String columnName) {this.columnName =columnName;

}

}

ShowExcel.java

packagepu;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.io.PrintStream;importjava.util.ArrayList;importjava.util.Collections;importjava.util.HashMap;importjava.util.Map;importjava.util.TreeMap;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;public classShowExcel {public static void showExcelSheet(Map map) throwsException {//处理方法

HashMap hashmap= newHashMap();

//放入需求的表名

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= newPrintStream(fs);

p.println("sheet amount==="+workbook.getNumberOfSheets());for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表

sheet=workbook.getSheetAt(i);

ArrayList 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");

}elseSystem.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) throwsException{

FileOutputStream f= new FileOutputStream(new File("E:\\output\\result.txt"),false);

PrintStream rs= newPrintStream(f);try{

Map map=new HashMap<>();

showExcelSheet(map);

Map treemap=new TreeMap<>();//实现结果表对象按export_id排序

treemap.putAll(map);for(intk:treemap.keySet()){

System.out.println(k);

}for(ArrayListtab :treemap.values()){for(RowInfo row:tab){

rs.println(row.toString());

}

}

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

}

}