java 利用jxl读取,生成excel 工具类集合大全

摘要: 在以前的一篇文章中,曾经提到如何用 jxl  来操作excel ,但讲得比较简单,不够详细,一般这种操作,都会被封装成一个工具类,供给其他地方调用。现在将整个工具类的代码贴上来,以供以后参考。

在以前的一篇文章中,曾经提到如何用 jxl 来操作excel ,但讲得比较简单,不够详细,一般这种操作,都会被封装成一个工具类,供给其他地方调用。现在将整个工具类的代码贴上来,以供以后参考。

import java.io.*;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.util.*;
import com.tjhq.cee2.imp.tool.debug;


public class Excel {

  private jxl.write.WritableWorkbook WorkBuk=null ;
  private jxl.write.WritableSheet ws=null;
  private FileOutputStream fos = null;
  private InputStream is = null;
  private jxl.Workbook rwb = null;
  private jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,10, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
  private jxl.write.WritableCellFormat wcfFC= null;


  /**
   * 在指定路径下新建一个Excel文件。
   * @parma String FileName 文件名称+文件路径(文件绝对路径 如:D:\\sheet1.xls)
   * @return boolean
  */
 public boolean createExcel(String FileName) throws Exception {
   boolean Rtn = false;
   try {
     fos = new FileOutputStream(FileName);
     WorkBuk = Workbook.createWorkbook(fos);
     Rtn = true;
   }
   catch (Exception e) {
     debug.printDug("Excel.createExcel is error,this is errormessage="+e.getMessage());
     Rtn = false;
   }
   finally {
     try {

     }
     catch (Exception e) {

     }

   }
   return Rtn;
 }


  /**
   * 设置当前sheet工作薄的名称及sheet号
   * @parma int SheetIdx(sheet号) String SheetName(工作薄的名称)
   * @return boolean
  */
 public boolean setCurSheet(int SheetIdx, String SheetName) {
   boolean Rtn = false;
   try {
     ws = WorkBuk.createSheet(SheetName, SheetIdx);
     Rtn = true;
   }
   catch (Exception e) {
     debug.printDug("Excel.setCurSheet is error,this is errormessage="+e.getMessage());
     Rtn = false;
   }
   finally {
     try {

     }
     catch (Exception e) {
     }
   }
   return Rtn;
 }


   /**
   * 向cell单元格中写入String型数据并可以设置单元格背景
   * @parma int col(列号) int row(行号) Txt 内容
   * @return boolean
   */
  public boolean setCellText(int col, int row, String Txt, String bgcolor) {
    boolean Rtn = false;
    try {
      wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10,
                                       WritableFont.NO_BOLD, false,
                                       UnderlineStyle.NO_UNDERLINE,
                                       jxl.format.Colour.BLACK);
      setCellBgcolor(bgcolor);
      jxl.write.Label labelC = new jxl.write.Label(col, row, Txt, wcfFC);
      ws.addCell(labelC);
      Rtn = true;
    }
    catch (Exception e) {
      debug.printDug("Excel.setCellText is error,this is errormessage="+e.getMessage());
      Rtn = false;
    }
    finally {
      try {

      }
      catch (Exception e) {

      }
    }
    return Rtn;
  }



  /**
   * 向cell单元格中写入Date型数据并可以设置单元格背景
   * @parma int col(列号) int row(行号) Txt 内容
   * @return boolean
  */
 public boolean setCellDate(int col, int row, Date Txt, String bgcolor) {
   boolean Rtn = false;
   try {
     wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10,
                                      WritableFont.NO_BOLD, false,
                                      UnderlineStyle.NO_UNDERLINE,
                                      jxl.format.Colour.BLACK);
     setCellBgcolor(bgcolor);
     jxl.write.DateTime labelC = new jxl.write.DateTime(col, row, Txt, wcfFC);
     ws.addCell(labelC);
     Rtn = true;
   }
   catch (Exception e) {
     debug.printDug("Excel.setCellDate is error,this is errormessage="+e.getMessage());
     Rtn = false;
   }
   finally {
     try {

     }
     catch (Exception e) {

     }

   }
   return Rtn;

 }


  /**
   * 向cell单元格中写入数字:double型数字并可以设置单元格背景
   * @parma int col(列号) int row(行号) Txt 内容
   * @return boolean
  */
  public boolean setCellNumber(int col,int row,double Txt,String bgcolor)
  {
    boolean Rtn = false;
    try {
      setCellBgcolor(bgcolor);
      jxl.write.Number labelC = new jxl.write.Number(col, row, Txt, wcfFC);
      ws.addCell(labelC);
      Rtn = true;
    }
    catch (Exception e) {
      debug.printDug("Excel.setCellNumber is error,this is errormessage="+e.getMessage());
      Rtn = false;
    }
    finally {
      try {

      }
      catch (Exception e) {

      }

    }
    return Rtn;

  }


  /**
   * 向cell单元格中写入String型数据并可以设置字体样式
   * @parma int col(列号) int row(行号) Txt 内容 int format 字体样式编号 int fontSize 字体大小
   * @return boolean
  */
  public boolean setCellText(int col,int row,String Txt,int format,int fontSize)
  {
      boolean Rtn=false;
      try
      {
        setCellFont(format,fontSize);
        wcfFC=new jxl.write.WritableCellFormat(wfc);
        jxl.write.Label labelC = new jxl.write.Label(col, row, Txt,wcfFC);
        ws.addCell(labelC);
        Rtn=true;
      }
      catch(Exception e)
      {
        debug.printDug("Excel.setCellText is error,this is errormessage="+e.getMessage());
        Rtn=false;
      }
      finally
      {
        try
        {

        }
        catch(Exception e)
        {

        }
      }
      return Rtn;
    }



  /**
   * 向cell单元格中写入Date型数据并可以设置字体样式
   * @parma int col(列号) int row(行号) Txt 内容 int format 字体样式编号 int fontSize 字体大小
   * @return boolean
  */
  public boolean setCellDate(int col,int row,Date Txt,int format,int fontSize)
  {
      boolean Rtn=false;
      try
      {
          setCellFont(format,fontSize);
          wcfFC=new jxl.write.WritableCellFormat(wfc);
          jxl.write.DateTime  labelC = new jxl.write.DateTime (col, row, Txt,wcfFC);
          ws.addCell(labelC);
          Rtn=true;
      }
      catch(Exception e)
      {
          debug.printDug("Excel.setCellDate is error,this is errormessage="+e.getMessage());
          Rtn=false;
      }
      finally
      {
          try
          {

          }
          catch(Exception e)
          {

          }

      }
      return Rtn;
  }


  /**
   * 向cell单元格中写入数字:double型数字并可以设置字体样式
   * @parma int col(列号) int row(行号) Txt 内容 int format 字体样式编号 int fontSize 字体大小
   * @return boolean
  */
  public boolean setCellNumber(int col,int row,double Txt,int format,int fontSize)
  {
      boolean Rtn=false;
      try
      {
          setCellFont(format,fontSize);
          wcfFC=new jxl.write.WritableCellFormat(wfc);
          jxl.write.Number  labelC = new jxl.write.Number (col, row, Txt,wcfFC);
          ws.addCell(labelC);
          Rtn=true;
      }
      catch(Exception e)
      {
         debug.printDug("Excel.setCellNumber is error,this is errormessage="+e.getMessage());
         Rtn=false;
      }
      finally
      {
          try
          {

          }
          catch(Exception e)
          {

          }

      }
      return Rtn;
 }


  /**
   * 向cell单元格中写入String型数据
   * @parma int col(列号) int row(行号) Txt 内容
   * @return boolean
  */
  public boolean setCellText(int col,int row,String Txt)
  {
      boolean Rtn=false;
      try
      {
          jxl.write.Label labelC = new jxl.write.Label(col, row, Txt);
          ws.addCell(labelC);
          Rtn=true;
      }
      catch(Exception e)
      {
          debug.printDug("Excel.setCellText is error,this is errormessage="+e.getMessage());
          Rtn=false;
      }
      finally
      {
          try
          {

          }
          catch(Exception e)
          {

          }
      }
      return Rtn;
    }


  /**
   * 向cell单元格中写入Date型数据
   * @parma int col(列号) int row(行号) Txt 内容
   * @return boolean
  */
  public boolean setCellDate(int col,int row,Date Txt)
  {
      boolean Rtn=false;
      try
      {
              //String dd=DateUtils.getInstance().format(Txt,"yyyy-mm-dd");
              //System.out.println(dd);
              jxl.write.DateTime  labelC = new jxl.write.DateTime (col, row, Txt);
              ws.addCell(labelC);
              Rtn=true;
      }
      catch(Exception e)
      {
        debug.printDug("Excel.setCellDate is error,this is errormessage="+e.getMessage());
        Rtn=false;
      }
      finally
      {
              try
              {

              }
              catch(Exception e)
              {

              }

      }
      return Rtn;

  }


  /**
   * 向cell单元格中写入数字:double型数字。
   * @parma int col(列号) int row(行号) Txt 内容
   * @return boolean
  */
  public boolean setCellNumber(int col,int row,double Txt)
  {
      boolean Rtn=false;
      try
      {
              jxl.write.Number  labelC = new jxl.write.Number (col, row, Txt);
              ws.addCell(labelC);
              Rtn=true;
      }
      catch(Exception e)
      {
        debug.printDug("Excel.setCellNumber is error,this is errormessage="+e.getMessage());
        Rtn=false;
      }
      finally
      {
              try
              {

              }
              catch(Exception e)
              {

              }

      }
      return Rtn;

  }

  /**
   * 向cell单元格中写入数字:float型数字。
   * @parma int col(列号) int row(行号) Txt 内容
   * @return boolean
  */
  public boolean setCellNumber(int col,int row,float Txt)
  {
      boolean Rtn=false;
      try
      {
              jxl.write.Number  labelC = new jxl.write.Number (col, row, Txt);
              ws.addCell(labelC);
              Rtn=true;
      }
      catch(Exception e)
      {
        debug.printDug("Excel.setCellNumber is error,this is errormessage="+e.getMessage());
        Rtn=false;
      }
      finally
      {
              try
              {

              }
              catch(Exception e)
              {

              }

      }
      return Rtn;

  }

          /**
   * 将写入的cell的内容保存到Excel文件中.
   * @parma
   * @return boolean
  */
  public boolean saveExcel()
  {
    boolean Rtn=false;
    try
    {
        //写入Exel工作表
        WorkBuk.write();
        //关闭Excel工作薄对象
        WorkBuk.close();
        Rtn=true;
    }
    catch(Exception e)
    {
       debug.printDug("Excel.saveExcel is error,this is errormessage="+e.getMessage());
       Rtn=false;
    }
    finally
    {
        try
        {

        }
        catch(Exception e)
        {

        }

    }
    return Rtn;

  }

          /**
   * 打开Excel.
   * @parma fileName Excel文件名+文件路径(绝对路径)
   * @return boolean
  */
  public boolean openExcel(String fileName)
  {
      boolean Rtn=false;
      try
      {
          is = new FileInputStream(fileName);
          rwb = Workbook.getWorkbook(is);
          Rtn=true;
      }
      catch(Exception e)
      {
         debug.printDug("Excel.openExcel is error,this is errormessage="+e.getMessage());
         Rtn=false;
      }
      finally
      {
          try
          {

          }
          catch(Exception e)
          {

          }

      }
      return Rtn;
  }


          /**
   * 关闭打开的Excel.
   * @
   * @return boolean
  */
  public boolean closeExcel()
  {
    boolean Rtn=false;
    try
    {
      rwb.close();
      Rtn=true;
    }
    catch(Exception e)
    {
      debug.printDug("Excel.closeExcel is error,this is errormessage="+e.getMessage());
      Rtn=false;
    }
    finally
    {
      try
      {
              rwb.close();
      }
      catch(Exception e)
      {
              Rtn=false;
      }

    }
    return Rtn;

  }


 /**
   * 得到工作薄的名称
   * @parma sheetIndex 工作薄号
   * @return String
  */

  public String getSheetName(int sheetIndex) {

    String sheetNam = "";
    try {
      jxl.Sheet rs = rwb.getSheet(sheetIndex);
      sheetNam = rs.getName();
    }
    catch (Exception e) {
      debug.printDug("Excel.getSheetName is error,this is errormessage="+e.getMessage());
      sheetNam = "";
    }
    finally {
      try {

      }
      catch (Exception e) {
        sheetNam = "";
      }
    }
    return sheetNam;

  }

  /**
   * 得到当前工作薄的总列数
   * @parma sheetIndex 工作薄号
   * @return int
   */

  public int getColCount(int sheetIndex) {

    int colCnt = 0;
    try {
      jxl.Sheet rs = rwb.getSheet(sheetIndex);
      colCnt = rs.getColumns();
    }
    catch (Exception e) {
      debug.printDug("Excel.getColCount is error,this is errormessage="+e.getMessage());
      colCnt = 0;
    }
    finally {
      try {

      }
      catch (Exception e) {
        colCnt = 0;
      }

    }
    return colCnt ;

  }


  /**
   * 得到当前工作薄的总行数
   * @parma sheetIndex 工作薄号
   * @return int
   */

  public int getRowCount(int sheetIndex) {

    int colCnt = 0;
    try {
      jxl.Sheet rs = rwb.getSheet(sheetIndex);
      colCnt = rs.getRows();
    }
    catch (Exception e) {
      debug.printDug("Excel.getRowCount is error,this is errormessage="+e.getMessage());
      colCnt = 0;
    }
    finally {
      try {

      }
      catch (Exception e) {
        colCnt = 0;
      }

    }
    return colCnt ;

  }


  /**
   * 获取某一列的所有单元格
   * @parma col 列数
   * @return String[]
   */

  public String[] getColArray(int col) {

      Sheet rs = rwb.getSheet(0);
      Cell[] getArray = rs.getColumn(col);
      String Str[] = new String[getArray.length];
      try {
        for(int i=0;i

调用如下:


	int sheetIndex = 0;
	String fileName = "D:\\test.xls";
	String SheetName="Excel测试";
	boolean result=false;
        //在指定路径下新建一个Excel文件。(创建一个Excel文件的第一步,必须。)
	result = ex.createExcel(fileName);
        //设置当前sheet工作薄的名称及sheet号(创建一个Excel文件的第二步,必须。)
	result = ex.setCurSheet(sheetIndex,SheetName);
        //单元格赋值(创建一个Excel文件的第三步)
	if(result)
	{
            //向单元加文本型内容
            result=ex.setCellText(0,0,"语文");
            //向单元加内容并且设置单元格背景颜色(颜色值有限种如:red,green,blue,yellow,brown,dark_blue)
            result=ex.setCellText(1,0,"数学","green");
            result=ex.setCellText(2,0,"英语","gray");
            result=ex.setCellText(3,0,"化学","blue");
            result=ex.setCellText(0,1,"90");
            result=ex.setCellText(1,1,"93");
            result=ex.setCellText(2,1,"97");
            result=ex.setCellText(3,1,"80");
            //向单元加数字内容并且设置字体样式。setCellText(int col,int row,String txt,int format,int fontSize)在文档中有说明;
            result=ex.setCellNumber(0,2,68,3,10);
            result=ex.setCellText(1,2,"88");
            //向单元加数字内容
            result=ex.setCellNumber(2,2,93.23);
             //向单元加文本型内容并且设置字体样式。setCellText(int col,int row,String txt,int format,int fontSize)在文档中有说明;
            result=ex.setCellText(3,2,"设置字体样式",4,11);
            result=ex.setCellText(0,3,"99");
            result=ex.setCellText(1,3,"100");
            result=ex.setCellText(2,3,"60");
            Date xx = new Date("1982/12/24");
            result=ex.setCellDate(3,3,xx);
            result=ex.saveExcel();
             ex.closeExcel();
	}
        if(result)
        {
          out.println("创建Excel文件"+fileName+" 成功!");
        }

上一篇: java 根据 week num 利用calendar 得到这一周的起始时间
下一篇: java 字符串转换成枚举类型 enum.
 评论 ( What Do You Think )
名称
邮箱
网址
评论
验证
   
 

 


  • 微信公众号

  • 我的微信

站点声明:

1、一号门博客CMS,由Python, MySQL, Nginx, Wsgi 强力驱动

2、部分文章或者资源来源于互联网, 有时候很难判断是否侵权, 若有侵权, 请联系邮箱:summer@yihaomen.com, 同时欢迎大家注册用户,主动发布无版权争议的 文章/资源.

3、鄂ICP备14001754号-3, 鄂公网安备 42280202422812号