jxl.jar的应用

package com.nec.yjg.jxl;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class Poker {
 
 public static final String WRITEPATH = "E://11.xls";
 public static final String FILEPATH = "E://缺失的表";
 public static final int COLUMNS = 8;
 public static int SHEETINDEX;
 public static void main(String[] args) throws Exception{
  selHandle();
  inputFilePath();
 }
 
 // 引入文件路径
 public static void inputFilePath() throws Exception{
  File file = new File(FILEPATH);
  readFile(file);
  
 }
 
 // 读取文件夹下所有文件夹及文件
 public static void readFile(File file) throws Exception{
  if(file.isFile()){
   String str = file.getName();
   InputStream is = new FileInputStream(file.getAbsolutePath());
   String filePath = file.getAbsolutePath();
   int index = filePath.indexOf(".");
   filePath = filePath.substring(0,index);
   if(str.indexOf("temp") == -1){
    
    //整理成规则表
    modifyFile(is,filePath);
   }else {
    
    // 处理规则表
    int pos = str.indexOf(".");
    String filebegin = str.substring(0,pos);
    String filebegintemp = str.substring(0,pos - 4);
    findSheetByName(is,filebegin,filebegintemp);
   }
  }else{
   File[] directory = file.listFiles();
   for(int i = 0; i < directory.length; i++){
    readFile(directory[i]);
   }
  }
 }
 
 // 改造要读的文件，使之符合读取规范
 public static void modifyFile(InputStream file,String filePath) throws Exception{
  Workbook bookOri = Workbook.getWorkbook(file);
  Sheet readSheet = bookOri.getSheet(0);
  Cell[] cell4 = readSheet.getColumn(4);
  Cell[] cell5 = readSheet.getColumn(5);
  int rows = readSheet.getRows();
  WritableWorkbook bookCop = Workbook.createWorkbook(new File(filePath + "temp.xls"), bookOri);
  WritableSheet writeSheet = bookCop.getSheet(0);
  for(int i = 1; i < rows; i++){
   String cell4Content = cell4[i].getContents();
   int index = cell4Content.indexOf(".");
   cell4Content = cell4Content.substring(0,index);
   String cell5Content = cell5[i].getContents();
   if(!"0.00".equals(cell5Content)){
    int index5 = cell5Content.indexOf(".");
    cell5Content = cell5Content.substring(0,index5);
    cell4Content = cell4Content + "," +  cell5Content;
   }
   Label label = new Label(4,i,cell4Content);
   writeSheet.addCell(label);
  }
  bookCop.write();
  bookCop.close();
  bookOri.close();
 }
 
 // 取得要读取的数据
 public static void findSheetByName(InputStream file, String filePath,
   String filebegintemp) throws Exception {
  
  // 打开要读的Excel文件
  Workbook rwb = Workbook.getWorkbook(file);
  Sheet readSheet = rwb.getSheet(0);
  int rows = readSheet.getRows();
  Workbook bookOri = Workbook.getWorkbook(new File("E://cop1.xls"));
  WritableWorkbook bookCop = Workbook.createWorkbook(new File("E://cop2.xls"), bookOri);
  int saveNum = 0;
  for(int i = 1;i < COLUMNS; i++){
   List<String> list = new ArrayList<String>();
   for(int j = 1; j < rows; j++){
    Cell cell = readSheet.getCell(i,j);
    String cellContent = cell.getContents();
    
    // 处理主码
    if("TRUE".equals(cellContent.toUpperCase()) && i == 6){
     cellContent = "●";
     System.out.println(cellContent);
    }else if(i == 6 && !"TRUE".equals(cellContent.toUpperCase())){
     cellContent = "";
    }
    
    // 处理是否为空
    if("TRUE".equals(cellContent.toUpperCase()) && i == 7){
     cellContent = "●";
    }else if(i == 7 && !"TRUE".equals(cellContent.toUpperCase())){
     cellContent="";
    }
    if(i == 5){
     break;
    }
    list.add(cellContent);
   }
   if(i > 3){
    saveNum = 3 + saveNum;
   }else{
    saveNum = saveNum + 5;
   }
   writeExcel(saveNum,list,bookCop,filebegintemp);
  }
  bookCop.write();
  bookCop.close();
  bookOri.close();
  rwb.close();
  delTempFile("E://cop1.xls","E://cop2.xls");
 }
 
 // 写入 Excel文件
 public static void writeExcel(int column,List<String> list,
   WritableWorkbook bookCop,String sheetName) throws Exception{
  WritableSheet writeSheet = bookCop.getSheet(sheetName);
  for(int row = 0; row < list.size(); row++){
   String cellContent = list.get(row);
   Label label = null;
   if(column == 15){
    if ("VARCHAR".equals(cellContent)) {
     cellContent = "文本";
    } else if ("DATE".equals(cellContent)) {
     cellContent = "日期";
    } else if ("INTEGER".equals(cellContent)
      || "NUMERIC".equals(cellContent)
      || "SMALLINT".equals(cellContent)
      || "货币".equals(cellContent)) {
     cellContent = "数值";
    }
    label = new Label(column,row+7,cellContent);
   }else if(column == 24 && "●".equals(cellContent)){
    label = new Label(1,row+7,cellContent);
    
   }else if(column == 27 && "●".equals(cellContent)){
    label = new Label(21,row+7,cellContent);
   }else {
    label = new Label(column,row+7,cellContent);
   }
   
   // 加入内容
   writeSheet.addCell(label);
  }
 }
 
 // 首先处理自身表的设置,Excel表的更新
 public static void selHandle() throws Exception{
  
  // 打开要写的Excel文件
  Workbook bookOri = Workbook.getWorkbook(new File(WRITEPATH));
  
  // 打开临时表
  WritableWorkbook bookCop = Workbook.createWorkbook(new File("E://cop1.xls"),bookOri);
  WritableSheet[] ws = bookCop.getSheets();
  for(int i = 0; i < ws.length; i++){
   String writeSheetName = ws[i].getName();
   String name[] = sheetHandle(writeSheetName);
   
   // 写入中文名字
   WritableCell wcEng = ws[i].getWritableCell(0,5);
   Label labelEng = (Label)wcEng;
   
   // 更新内容
   labelEng.setString(name[1]);
   
   // 写入英文名字
   WritableCell wcChi = ws[i].getWritableCell(7,5);
   Label labelChi = (Label)wcChi;
   labelChi.setString(name[0]);
  }
  bookCop.write();
        bookCop.close();
        bookOri.close();
 }
 
 // sheet名字处理
 public static String[] sheetHandle(String sheetName){
  int len = sheetName.length();
  int index = sheetName.indexOf("(");
  String engName = sheetName.substring(0,index);
  String chiName = sheetName.substring(index+1,len - 1);
  String[] name =  new String[]{engName,chiName};
  return name;
 }
 
 // 临时文件的删除
 public static void delTempFile(String cop1,String cop2){
  File file1 = new File(cop1);
  File file2 = new File(cop2);
  file1.delete();
  file2.renameTo(file1);
 }
}