知识收集3【原创】

1.对于韩国人写得jxl的问题（原创工作总结）

    最近用jxl做得模板导出数据出现了严重的性能问题：

        （1）在main方法中运行此业务逻辑时间大约1.662秒，打印时间如图：

      虚拟机运行情况如图：

    

     （2）在Tomcat中运行此业务逻辑时间大约62.182秒，打印时间如图：

     

 虚拟机运行情况如图：

    （3）分析情况

             通过数据和图表认真分析，main方法之所以运行快，因为JVM里面没有大量的垃圾回收，占用的内存又不是很大，jxl进行垃圾回收时，占用的消耗时间不大。

           在tomcat运行中，可以看到JVM里面程序占用的内存比较大，有垃圾存在，每次进行垃圾回收，都会占用大量的时间。

          通过数据分析，main方法的垃圾回收时间很多安基本上忽略不计，而在tomcat里，由于模板总共有47个模板工作表，每个工作表获取数据，要调用3次垃圾回事，每次大约用时0.4秒多，每个工作表用时1.3秒以上，通过计算，大约是60秒以上，可见对整个数据导出占用了大量的时间。

     （4）源码分析

       代码调用处：

             Workbook  wb = Workbook.getWorkbook(file);

             WritableWorkbook wwb = Workbook.createWorkbook(new File(dstFile),wb);

     此程序相当于打开一个文件的副本，进行修改数据，然后回写到源文件

      产生慢的原因：

    产生一个workbook，读取文件

    /**
   * A factory method which takes in an excel file and reads in the contents.
   *
   * @exception IOException
   * @exception BiffException
   * @param file the excel 97 spreadsheet to parse
   * @return a workbook instance
   */
  public static Workbook getWorkbook(java.io.File file)
    throws IOException, BiffException
  {
    return getWorkbook(file, new WorkbookSettings());
  }

 下面这个类的一些参数就是在读取或写入工作簿时用到的（根据一些参数处理）

/**
 * This is a bean which client applications may use to set various advanced
 * workbook properties.  Use of this bean is not mandatory, and its absence
 * will merely result in workbooks being read/written using the default
 * settings
 */
public final class WorkbookSettings

看一下它的构造器：

  /**
   * Default constructor
   */
  public WorkbookSettings()
  {
    initialFileSize = DEFAULT_INITIAL_FILE_SIZE;
    arrayGrowSize = DEFAULT_ARRAY_GROW_SIZE;
    localeFunctionNames = new HashMap();
    excelDisplayLanguage = CountryCode.USA.getCode();
    excelRegionalSettings = CountryCode.UK.getCode();
    refreshAll = false;
    template = false;
    excel9file = false;
    windowProtected = false;
    hideobj = HIDEOBJ_SHOW_ALL;

    // Initialize other properties from the system properties
    try
    {
      boolean suppressWarnings = Boolean.getBoolean("jxl.nowarnings");
      setSuppressWarnings(suppressWarnings);
      drawingsDisabled        = Boolean.getBoolean("jxl.nodrawings");
      namesDisabled           = Boolean.getBoolean("jxl.nonames");
      gcDisabled              = Boolean.getBoolean("jxl.nogc");
      rationalizationDisabled = Boolean.getBoolean("jxl.norat");
      mergedCellCheckingDisabled =
        Boolean.getBoolean("jxl.nomergedcellchecks");
      formulaReferenceAdjustDisabled =
                                Boolean.getBoolean("jxl.noformulaadjust");
      propertySetsDisabled = Boolean.getBoolean("jxl.nopropertysets");
      ignoreBlankCells = Boolean.getBoolean("jxl.ignoreblanks");
      cellValidationDisabled = Boolean.getBoolean("jxl.nocellvalidation");
      autoFilterDisabled = !Boolean.getBoolean("jxl.autofilter");
             // autofilter currently disabled by default
      useTemporaryFileDuringWrite =
        Boolean.getBoolean("jxl.usetemporaryfileduringwrite");
      String tempdir =
        System.getProperty("jxl.temporaryfileduringwritedirectory");

      if (tempdir != null)
      {
        temporaryFileDuringWriteDirectory = new File(tempdir);
      }
     
      encoding = System.getProperty("file.encoding");
    }
    catch (SecurityException e)
    {
      logger.warn("Error accessing system properties.", e);
    }

    // Initialize the locale to the system locale
    try
    {
      if (System.getProperty("jxl.lang")    == null ||
          System.getProperty("jxl.country") == null)
      {
        locale = Locale.getDefault();
      }
      else
      {
        locale = new Locale(System.getProperty("jxl.lang"),
                            System.getProperty("jxl.country"));
      }

      if (System.getProperty("jxl.encoding") != null)
      {
        encoding = System.getProperty("jxl.encoding");
      }
    }
    catch (SecurityException e)
    {
      logger.warn("Error accessing system properties.", e);
      locale = Locale.getDefault();
    }
  }

其中，  gcDisabled = Boolean.getBoolean("jxl.nogc");这个是读取这个是否垃圾回收的参数，默认是false

就是这个这个参数导致读取sheet对象非常的慢，下面会继续进行分析。

 Workbook.createWorkbook(new File(dstFile),wb); 这个方法调用了WorkBook

咱们来进行一些简单的分析：

 /**
   * Creates a writable workbook with the given filename as a copy of
   * the workbook passed in.  Once created, the contents of the writable
   * workbook may be modified
   *
   * @param file the output file for the copy
   * @param in the workbook to copy
   * @return a writable workbook
   * @exception IOException
   */
  public static WritableWorkbook createWorkbook(java.io.File file,
                                                Workbook in)
    throws IOException
  {
    return createWorkbook(file, in, new WorkbookSettings());
  }

这个类相当于产生对Excel修改对象，产生的是一个原文件副本的修改回写

 /**
   * Creates a writable workbook with the given filename as a copy of
   * the workbook passed in.  Once created, the contents of the writable
   * workbook may be modified
   *
   * @param file the output file for the copy
   * @param in the workbook to copy
   * @param ws the configuration for this workbook
   * @return a writable workbook
   */
  public static WritableWorkbook createWorkbook(java.io.File file,
                                                Workbook in,
                                                WorkbookSettings ws)
    throws IOException
  {
    FileOutputStream fos = new FileOutputStream(file);
    WritableWorkbook w = new WritableWorkbookImpl(fos, in, true, ws);
    return w;
  }

此处分析下：WritableWorkbook w = new WritableWorkbookImpl(fos, in, true, ws);中的WritableWorkbookImpl类。

  /**
   * A pseudo copy constructor.  Takes the handles to the font and formatting
   * records
   *
   * @exception IOException
   * @param w the workbook to copy
   * @param os the output stream to write the data to
   * @param cs TRUE if the workbook should close the output stream, FALSE
   * @param ws the configuration for this workbook
   */
  public WritableWorkbookImpl(OutputStream os,
                              Workbook w,
                              boolean cs,
                              WorkbookSettings ws) throws IOException
  {。。。。。。。。。。。。。。。。。。。}

这个构造方法中调用了copyWorkbook(w);，实现了对原Excel文件数据对象的拷贝，相当于一个副本

 /**
   * Produces a writable copy of the workbook passed in by
   * creating copies of each sheet in the specified workbook and adding
   * them to its own record
   *
   * @param w the workbook to copy
   */
  private void copyWorkbook(Workbook w)
  {
    int numSheets = w.getNumberOfSheets();
    wbProtected = w.isProtected();
    Sheet s = null;
    WritableSheetImpl ws = null;
    long start= System.currentTimeMillis();
    for (int i = 0 ; i < numSheets; i++)
    {
      long starts= System.currentTimeMillis();
      s = w.getSheet(i);
      System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"+( System.currentTimeMillis() - starts)/1000.0+" 秒");
      long start3= System.currentTimeMillis();
      ws = (WritableSheetImpl) createSheet(s.getName(),i, false);
      System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"+s.getName());
      System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"+( System.currentTimeMillis() - start3)/1000.0+" 秒");
      long start2= System.currentTimeMillis();
      ws.copy(s);
      System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"+( System.currentTimeMillis() - start2)/1000.0+" 秒");
    }
    long start1= System.currentTimeMillis();
 System.out.println("********************************************************************"+(start1 - start)/1000.0+" 秒");
  }

分析下这个影响性能的地方：s = w.getSheet(i);

sheet的实现类是：SheetImpl  （public class SheetImpl implements Sheet）

这个类的构造器：

SheetImpl(File f,
            SSTRecord sst,
            FormattingRecords fr,
            BOFRecord sb,
            BOFRecord wb,
            boolean nf,
            WorkbookParser wp)
    throws BiffException
  {
    excelFile = f;
    sharedStrings = sst;
    formattingRecords = fr;
    sheetBof = sb;
    workbookBof = wb;
    columnInfosArray = new ArrayList();
    sharedFormulas = new ArrayList();
    hyperlinks = new ArrayList();
    rowProperties = new ArrayList(10);
    columnInfosInitialized = false;
    rowRecordsInitialized = false;
    nineteenFour = nf;
    workbook = wp;
    workbookSettings = workbook.getSettings();

}中的workbookSettings = workbook.getSettings();就是WorkBook的参数的传递，里面有垃圾回收的控制参数

看一下WorkBook具体实现类WorkbookParser获取sheet的方法：

 /**
   * Gets the specified sheet within this workbook
   *
   * @param index the zero based index of the required sheet
   * @return The sheet specified by the index
   */
  public Sheet getSheet(int index)
  {
    // First see if the last sheet index is the same as this sheet index.
    // If so, then the same sheet is being re-requested, so simply
    // return it instead of rereading it
    if ((lastSheet != null) && lastSheetIndex == index)
    {
      return lastSheet;
    }

    // Flush out all of the cached data in the last sheet
    if (lastSheet != null)
    {
      lastSheet.clear();
      if (!settings.getGCDisabled())
      {
        System.gc();
      }
    }

    lastSheet = (SheetImpl) sheets.get(index);
    lastSheetIndex = index;
    lastSheet.readSheet();

    return lastSheet;
  }

从此方法中可以看出调用了垃圾回收 if (!settings.getGCDisabled()){
System.gc();
}
在  lastSheet.clear();中看下clear方法也调用垃圾回收方法

  final void clear()
  {
    cells = null;
    mergedCells = null;
    columnInfosArray.clear();
    sharedFormulas.clear();
    hyperlinks.clear();
    columnInfosInitialized = false;

    if (!workbookSettings.getGCDisabled())
    {
      System.gc();
    }
  }

 在readSheet()方法中也调用了垃圾回收方法

  /**
   * Reads in the contents of this sheet
   */
  final void readSheet()

   if (!workbookSettings.getGCDisabled())
    {
      System.gc();
    }

可以源码分析到复制副本，一个sheet总共用了3次垃圾回收，导致时间消耗很大。

为了提高性能，做了如下程序的修改：

     jxl在读取excel的时候，关闭了手工垃圾回收

    System.setProperty("jxl.nogc","true");

或者

     fis = new FileInputStream(file);
   WorkbookSettings wbs = new WorkbookSettings();
      wbs.setGCDisabled(true);
    wb = Workbook.getWorkbook(fis,wbs);
   WorkbookSettings wbss = new WorkbookSettings();
      wbss.setGCDisabled(true);
   wwb = Workbook.createWorkbook(new File(dstFile),wb,wbss);

      垃圾回收器"也许并不像许多人想象的一样会立即执行(当堆中的资源需要释放时),而是在引用类型的引用被删除和它在"堆"中的对象实例被删除中间有个间隔,为什么呢? 因为"垃圾回收器"的调用是比较消耗系统资源的,因此不可能经常被调用! (当然,用户代码可以用方法System.GC.Collect()来强制执行"垃圾回收器")

评论

1 楼 leo_lnx 2014-03-19  

very good，顶大兵！