java实现的导出Excel工具类实例

本文实例讲述了java实现的导出excel工具类。分享给大家供大家参考，具体如下：

excelexportutil：

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();
    }
  }
}

verifyutil：

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;
  }
}

更多关于java相关内容感兴趣的读者可查看本站专题：《java操作excel技巧总结》、《java+mysql数据库程序设计总结》、《java数据结构与算法教程》、《java文件与目录操作技巧汇总》及《java操作dom节点技巧总结》

希望本文所述对大家java程序设计有所帮助。