package com.excel;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
 * 生成excel表格
 * 
 * @author
 * 
 */
public class ExcelExportUtil {
  /**
   * 構造器
   * 
   */
  public ExcelExportUtil() {
  }
  /**
   * 生成具有一定格式excel
   * 
   * @param sheetName
   *      sheet名稱,默認為sheet1
   * @param nf
   *      數字類型的格式 如:jxl.write.NumberFormat nf = new
   *      jxl.write.NumberFormat("#.##");默認無格式
   * @param content
   *      二維數組,要生成excel的數據來源
   * @param 合并項
   *      每一項的數據格式為0,1,0,2 即：把(0,1)和(0,2)合并--->第1列的第一、二個元素合并
   * @param os
   *      excel輸出流
   * @param row
   *      需要水平居中的行,默認居左。以逗號分隔的字符串
   * @param col
   *      需要水平居中的列,默認居左。以逗號分隔的字符串
   */
  public void export(String sheetName, NumberFormat nf, String[][] content,
      String[] mergeInfo, OutputStream os, String row, String col) {
    if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
      return;
    }
    // 默認名稱
    if (VerifyUtil.isNullObject(sheetName)) {
      sheetName = "sheet1";
    }
    Set<Integer> rows = this.getInfo(row);
    Set<Integer> cols = this.getInfo(col);
    WritableWorkbook workbook = null;
    try {
      workbook = Workbook.createWorkbook(os);
      WritableSheet sheet = workbook.createSheet(sheetName, 0);
      for (int i = 0; i < content.length; i++) {
        for (int j = 0; j < content[i].length; j++) {
          if (content[i][j] == null) {
            content[i][j] = "";
          }
          if (isNumber(content[i][j]) && !rows.contains(i)
              && !cols.contains(j)) {// 處理數字
            Number number = null;
            if (VerifyUtil.isNullObject(nf)) {// 數字無格式
              number = new Number(j, i,
                  Double.valueOf(content[i][j]));
            } else {// 如果有格式,按格式生成
              jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(
                  nf);
              number = new Number(j, i,
                  Double.valueOf(content[i][j]), wcfn);
            }
            sheet.addCell(number);
          } else {// 處理非數字
            WritableCellFormat format = new WritableCellFormat();
            if (rows.contains(i) || cols.contains(j)) {
              format.setAlignment(jxl.format.Alignment.CENTRE);
            } else {
              format.setAlignment(jxl.format.Alignment.LEFT);
            }
            format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            Label label = new Label(j, i, content[i][j], format);
            sheet.addCell(label);
          }
        }
      }
      this.merge(sheet, mergeInfo);
      workbook.write();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        workbook.close();
        os.close();
      } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  /**
   * 生成固定格式的excel,表格都為文本,水平居左,垂直居中
   * 
   * @param sheetName
   *      sheet名稱,默認為sheet1
   * @param content
   *      二維數組,要生成excel的數據來源
   * @param os
   *      excel輸出流
   */
  public void exportFormatExcel(String[][] content, String sheetName,
      OutputStream os) {
    if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
      return;
    }
    // 默認名稱
    if (VerifyUtil.isNullObject(sheetName)) {
      sheetName = "sheet1";
    }
    WritableWorkbook workbook = null;
    try {
      workbook = Workbook.createWorkbook(os);
      WritableSheet sheet = workbook.createSheet(sheetName, 0);
      for (int i = 0; i < content.length; i++) {
        for (int j = 0; j < content[i].length; j++) {
          if (content[i][j] == null) {
            content[i][j] = "";
          }
          WritableCellFormat format = new WritableCellFormat();
          format.setAlignment(jxl.format.Alignment.LEFT);
          format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
          Label label = new Label(j, i, content[i][j], format);
          sheet.addCell(label);
        }
      }
      workbook.write();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        workbook.close();
      } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  /**
   * 生成固定格式的excel,表格都為文本,水平居左,垂直居中
   * 
   * @param sheetName
   *      sheet名稱,默認為sheet1
   * @param content
   *      Map,要生成excel的數據來源
   * @param os
   *      excel輸出流
   */
  public void exportFormatExcel(Map<String, String[][]> content,
      String[] salary_name_array, String sheetName, OutputStream os)
       {
    if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {
      return;
    }
    // 默認名稱
    if (VerifyUtil.isNullObject(sheetName)) {
      sheetName = "sheet1";
    }
    WritableWorkbook workbook = null;
    try {
      workbook = Workbook.createWorkbook(os);
      WritableSheet sheet = workbook.createSheet(sheetName, 0);
      int index = 0;
      for (int k = 0; k < salary_name_array.length; k++) {
        String[][] value = (String[][]) content
            .get(salary_name_array[k]);
        if (value != null && value.length > 0) {
          if (index != 0) {
            index++;
          }
          WritableCellFormat format1 = new WritableCellFormat();
          format1.setAlignment(jxl.format.Alignment.LEFT);
          format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
          Label label1 = new Label(0, index, salary_name_array[k],
              format1);
          sheet.addCell(label1);
          for (int i = 0; i < value.length; i++) {
            index++;
            for (int j = 0; j < value[i].length; j++) {
              if (value[i][j] == null) {
                value[i][j] = "";
              }
              WritableCellFormat format = new WritableCellFormat();
              format.setAlignment(jxl.format.Alignment.LEFT);
              format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
              Label label = new Label(j, index, value[i][j],
                  format);
              sheet.addCell(label);
            }
          }
        }
      }
      workbook.write();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        workbook.close();
      } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  /**
   * 合并表格
   * @param sheet
   *      工作表
   * @param mergeInfo
   *      要合并的表格的信息
   * @throws RowsExceededException
   * @throws NumberFormatException
   * @throws WriteException
   */
  private void merge(WritableSheet sheet, String[] mergeInfo)
      throws RowsExceededException, NumberFormatException, WriteException {
    if (VerifyUtil.isNullObject(sheet) || VerifyUtil.isNull1DArray(mergeInfo)) {
      return;
    } else if (!this.isMergeInfo(mergeInfo)) {
      return;
    } else {
      for (String str : mergeInfo) {
        String[] temp = str.split(",");
        sheet.mergeCells(Integer.parseInt(temp[1]),
            Integer.parseInt(temp[0]), Integer.parseInt(temp[3]),
            Integer.parseInt(temp[2]));
      }
    }
  }
  /**
   * 處理要居中的行或列的數據
   * 
   * @param indexes
   *      行標或列標
   * @return 行坐標或列坐標組成的集合
   */
  private Set<Integer> getInfo(String indexes) {
    Set<Integer> set = new HashSet<Integer>();
    if (VerifyUtil.isNullObject(indexes)) {
      return set;
    }
    String[] temp = indexes.split(",", 0);
    for (String str : temp) {
      if (isNumeric(str)) {
        set.add(Integer.parseInt(str));
      }
    }
    return set;
  }
  /**
   * 判斷字符串是否由純數字組成
   * 
   * @param str
   *      源字符串
   * @return true是，false否
   */
  private boolean isNumeric(String str) {
    if (VerifyUtil.isNullObject(str)) {
      return false;
    }
    Pattern pattern = Pattern.compile("[0-9]*");
    return pattern.matcher(str).matches();
  }
  /**
   * 判斷字符串是否是數字
   * 
   * @param str
   *      源字符串
   * @return true是，false否
   */
  private boolean isNumber(String number) {
    // 判斷參數
    if (VerifyUtil.isNullObject(number)) {
      return false;
    }
    // 查看是否有小數點
    int index = number.indexOf(".");
    if (index < 0) {
      return isNumeric(number);
    } else {
      // 如果有多個".",則不是數字
      if (number.indexOf(".") != number.lastIndexOf(".")) {
        return false;
      }
      String num1 = number.substring(0, index);
      String num2 = number.substring(index + 1);
      return isNumeric(num1) && isNumeric(num2);
    }
  }
  /**
   * 判斷合并項內容是否合法
   * 
   * @param mergeInfo
   *      合并項 每一項的數據格式為0,1,0,2即把(0,1)和(0,2)合并
   * @return true合法,false非法
   */
  private boolean isMergeInfo(String[] mergeInfo) {
    if (VerifyUtil.isNull1DArray(mergeInfo)) {
      return false;
    } else {
      for (String str : mergeInfo) {
        String[] temp = str.split(",");
        if (VerifyUtil.isNull1DArray(temp) || temp.length != 4) {
          return false;
        } else {
          for (String s : temp) {
            if (!isNumeric(s)) {
              return false;
            }
          }
        }
      }
    }
    return true;
  }
  public static void main(String[] args) {
    ExcelExportUtil ee = new ExcelExportUtil();
    String[][] content = new String[][] { { "", "第一列", null, "第三列" },
        { "第一行", "aa", "2.00", "22" }, { "第二行", "bb", "3.01", "32" },
        { "第三行", "cc", "4.00", "41" } };
    try {
      OutputStream os = new FileOutputStream("D:/test2.xls");
      // ee.export(null,null, content,null, os);
      ee.export(null, null, content,
          new String[] { "0,1,0,2", "1,0,3,0" }, os, "0,1", "0");
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
}

package com.excel;
import java.io.OutputStream;
import java.util.Map;
import jxl.write.NumberFormat;
import jxl.write.WritableSheet;
public class VerifyUtil {
  public static boolean isNullObject(String[][] content, OutputStream os) {
    // TODO Auto-generated method stub
    if(content != null && content.length > 0 && os != null)
    {
      return false;
    }
    return true;
  }
  public static boolean isNull2DArray(String[][] content) {
    // TODO Auto-generated method stub
    if(content != null && content.length > 0)
    {
      return false;
    }
    return true;
  }
  public static boolean isNullObject(NumberFormat nf) {
    // TODO Auto-generated method stub
    if(nf != null)
    {
      return false;
    }
    return true;
  }
  public static boolean isNullObject(String sheetName) {
    if(sheetName != null && !"".equals(sheetName.trim()))
    {
      return false;
    }
    return true;
  }
  public static boolean isNullObject(Map<String, String[][]> content,
      OutputStream os) {
    // TODO Auto-generated method stub
    if(content != null && content.size() > 0 && os != null)
    {
      return false;
    }
    return true;
  }
  public static boolean isNull1DArray(String[] mergeInfo) {
    // TODO Auto-generated method stub
    if(mergeInfo != null && mergeInfo.length > 0)
    {
      return false;
    }
    return true;
  }
  public static boolean isNullObject(WritableSheet sheet) {
    // TODO Auto-generated method stub
    if(sheet != null)
    {
      return false;
    }
    return true;
  }
}