Flexcell.NET performance

Oden_Marc · October 9, 2012, 3:23pm

Hi,

I have an ASP.NET website that uses the FlexCel.XlsAdapter.XlsFile to create an Excel spreadsheet. Some of these spreadsheets are quite large (15000+) rows. It takes five to ten minutes to generate one of these large spreadsheets.

Do you have any tips for getting better perfomance?

I have included a code snippet below:

public FileContentResult CreateExcel(string org, string loc, out string errormessage)
{
    FileContentResult retXLS = null;
    XlsFile Xls;
    try
    {

errormessage = string.Empty;

List<MarkItemModel> itemlist = (List<MarkItemModel>)GetMarkItems(org, loc, string.Empty, out errormessage);

if (itemlist != null)
{
Xls = new XlsFile(true);
Xls.NewFile(1);

//Global Workbook Options
Xls.OptionsAutoCompressPictures = false;

     //Styles.
     TFlxFormat StyleFmt;
     StyleFmt = Xls.GetStyle(Xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0));
     StyleFmt.Font.Name = "Arial";
     StyleFmt.Font.Size20 = 200;
     StyleFmt.Font.Color = TExcelColor.Automatic;
     StyleFmt.Font.Family = 0;
     StyleFmt.Font.Scheme = TFontScheme.None;
     Xls.SetStyle(Xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0), StyleFmt);
     //Set up rows and columns
     //Xls.DefaultColWidth = 2925;
     Xls.DefaultColWidth = 5000;
     Xls.DefaultRowHeight = 1200;
     Xls.DefaultRowHeightAutomatic = false;

int idx = 1;

     Xls.SetCellValue(idx, 1, "UIC");
     Xls.SetCellValue(idx, 2, "System Item Mark");
     Xls.SetCellValue(idx, 3, "NSN");
     Xls.SetCellValue(idx, 4, "Part Number");
     Xls.SetCellValue(idx, 5, "Nomenclature");
     Xls.SetCellValue(idx, 6, "Serial Number");
     Xls.SetCellValue(idx, 7, "Has Part Image");
     Xls.SetCellValue(idx, 8, "UCN");
     Xls.SetCellValue(idx, 9, "UID Mark");
     Xls.SetCellValue(idx, 10, "Not Installed Reason");

     foreach (MarkItemModel itm in itemlist)
     {
  idx += 1;
  Xls.SetAutoRowHeight(idx, false);
  Xls.SetRowHeight(idx, 1200);
  Xls.SetColWidth(1, 3200);
  Xls.SetCellValue(idx, 1, itm.UIC.LocationName);
  Xls.SetColWidth(2, 2925);
  Bitmap em = EncodeMark(itm.ItemIdentifier + Convert.ToChar(4));
  using (MemoryStream ms = new MemoryStream())
  {
      em.Save(ms, ImageFormat.Jpeg);
      TImageProperties ImgProps = new TImageProperties();
      ImgProps.Anchor = new TClientAnchor(TFlxAnchorType.MoveAndDontResize, idx, 32, 2, 32, idx + 1, 0, 3, 0);
      ImgProps.ShapeName = itm.ItemIdentifier;
      Xls.AddImage(ms.ToArray(), ImgProps);
  }
  Xls.SetColWidth(3, 4000);
  Xls.SetCellValue(idx, 3, itm.NSN);
  Xls.SetColWidth(4, 6000);
  Xls.SetCellValue(idx, 4, itm.OriginalPartNumber);
  Xls.SetColWidth(5, 4000);
  TFlxFormat fmt = Xls.GetFormat(Xls.GetColFormat(5));
  fmt.WrapText = true;
  Xls.SetColFormat(5, Xls.AddFormat(fmt));
  Xls.SetCellValue(idx, 5, itm.Description);
  Xls.SetColWidth(6, 6000);
  Xls.SetCellValue(idx, 6, itm.SerialNumber);
  Xls.SetColWidth(7, 6000);
  Xls.SetCellValue(idx, 7, itm.HasPartCatalogPicture.ToString());
  Xls.SetColWidth(8, 6000);
  Xls.SetCellValue(idx, 8, itm.UCN);
  Xls.SetColWidth(9, 2925);
  if (string.IsNullOrEmpty(itm.UII))
  {
      Xls.SetCellValue(idx, 9, itm.UII);
  }
  else
  {
      Xls.SetCellValue(idx, 9, "            ");
  }
  Xls.SetCellValue(idx, 10, itm.TagInstallationStatus);
     }

     byte[] filebytes;
     using (MemoryStream xlsfilestream = new MemoryStream())
     {
  Xls.Save(xlsfilestream, TFileFormats.Xlsx);
  filebytes = xlsfilestream.ToArray();
     }
     retXLS = new FileContentResult(filebytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

}

    }
    catch (Exception ex)
    {
errormessage = "Error attempting to create Excel file: " + ex.ToString();
return retXLS;
    }
    finally
    {
Xls = null;
    }

return retXLS;
}

adrian · October 9, 2012, 4:02pm

Hi,

15,000 rows is very little, it should be almost instant with that data. Were you referring to 150,000 rows?

But even for 150,000 rows, 10 minutes looks like too much.

Just a simple test. I've run this code in my machine:

       DateTime start = DateTime.Now;
        XlsFile xls = new XlsFile(1, true);
        for (int row = 1; row < 150000; row++)
        {
            for (int col = 1; col < 30; col++)
            {
                xls.SetCellValue(row, col, "Cell "+ row.ToString() + ", " + col.ToString());
            }
        }
        xls.Save("r:\\test.xlsx");
        MessageBox.Show((DateTime.Now - start).ToString());

And it takes 1 minute 18 secs to finish. A big part of that time is spent compressing the huge file (xlsx are zip files and this one is 28mb of compressed xml), and also generating the xml. Even excel takes its time to open the generated file too. Xlsx formats are slower than xls (because they are compressed and they are xml), but well, you wouldn't be able to save 150000 rows in xls anyway since the xls maximum is 65536.

Also note that I didn't "cheat" here, and the code above generates a file with a different string for every cell, which is slower since Excel has a shared string table where all similar strings are stored, and this example just blows the sst. If I change the line SetCellValue to be SetCellValue(row, col, row), then it takes 17 seconds to create the file. A real world spreadsheet is going to have a mix of numbers and repeated strings, so the result should be between this "worst case" (1 minute) and "best case" (17 seconds)

Now, as a fist step, can you run this code in your machine and report how long it takes? If it is taking about as long, then the bottleneck might be somewhere else. For example, reading the values form the database to fill the Excel sheet, if that is the case, maybe you can cache them. Or I see you have some images in your code, if those images are huge, it might affect perfomance too.

We are very serious about squeezing every drop of performance we can in FlexCel, in fact, when I got that main I was just profiling the code and making some small enhancements. (by the way, if you aren't in 5.7 update, 57 uses its own zip writer which is faster than the .net built in, so you might get some extra seconds there)

But huge files are going to take some time, no matter how much we optimize. The file has to be zipped (which is slow), it is also xml (which is incredibly verbose), well, I would say just that xlsx isn't a file format designed for performance.

On your side, make sure to read the "performance.pdf" doc in the documentation (even when I am not sure it would apply in your case), and make sure the time is actually spent in FlexCel and not anywhere else (maybe creating the images you insert, fetching the data, or even feeding the multi mb file to the browser).