Saving All Columns

Feb 5, 2011 at 5:10 AM

First, great library.  I'm really looking forward to seeing the next set of developments.

The problem I'm having is that when I save an XLWorkbook (with XLWorksheet), it saves more columns than I need (I need 50, it saves 16384 to be exact).  I've verified array size at every step so I don't really think that is causing the issue.

I'm populating the data with a List<string[]>, with the string[] of size 50, in the manner of the example posted.  I'm hoping I'm missing something basic.  Any help would be great.  Thanks!

Coordinator
Feb 5, 2011 at 5:34 AM

That shouldn't happen, please post your code (using dummy data) so I can take a look. I tried to reproduce the problem with the following code but it works as expected:

        static void Main(string[] args)
        {
            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("New");
            List<String[]> list = GetList();
            ws.Cell(1, 1).Value = list;
            wb.SaveAs("Sandbox.xlsx");
        }

        static List<string[]> GetList()
        {
            List<String[]> list = new List<string[]>();
            foreach (Int32 i in Enumerable.Range(1, 50))
            {
                String[] arr = new string[50];
                foreach (Int32 h in Enumerable.Range(1, 50))
                {
                    arr[h - 1] = Guid.NewGuid().ToString();
                }
                list.Add(arr);
            }
            return list;
        }

Feb 5, 2011 at 4:26 PM
Edited Feb 5, 2011 at 5:50 PM

Edit:  The performance issues I'm describing here are strictly because I was compiling to an x86 configuration (because another dependency required it).  When I compile against Any CPU, the thing is quick (I'm on a x64 machine).  So now I have some more work to do... Hopefully if you weren't aware, you are now.  I appreciate the help and the time.  Again, great library!

Thanks for the quick reply... To be honest, I'm having a hard time recreating the problem now as well.  I had been using old assemblies up until about the time I posted last night (v0.41.1), but I'll believe you if you told me that I'm crazy.  Unless I can recreate the problem, consider it a moot point.

Now, with that said, I am having some performance issues.  I've looked at the earlier discussions and specifically your comment here:

Improved the performance even further. For 200K cells the average save time is now 14 seconds and the average load time is 13 seconds.

By my math, I'm trying to Save about 3K rows of data, each with 45 columns, so ~ 150K cells.  Specifically, the SaveAs method is taking about 5 minutes.  I'll post the relevant code below.  Basically, from a windows application, I'm exporting the contents of a ListView to excel.  The ListViewItems.Tag property is associated with an Item, and a method belonging to that instance is what converts all of its data to a string[].  BTW, any general comments would be appreciated as well... haven't been doing this for too long.  Also, if I can provide more info, just let me know.  Nothing I'm working on here is off-limits.  Thanks!

 

        private void exportToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;

            string tempFile = String.Format("{0}{1}", Path.Combine(Path.GetTempPath(), @"EzipExport"), @".xlsx");
            string templatePath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), @"Resources", @"EzipTemplate.xlsx");

            var xlWorkbook = new XLWorkbook(templatePath);
            var xlWorksheet = xlWorkbook.Worksheets.FirstOrDefault(w => w.Name == "Items");
            var listOfStringArrays = new List<string[]>();

            for (int i = 0; i < listViewEquipment.Items.Count; i++)
            {
                ListViewItem listViewItem = listViewEquipment.Items[i];

                if (listViewItem.Tag != null && listViewItem.Tag is Item)
                {
                    var item = (Item)listViewItem.Tag;
                    listOfStringArrays.Add(item.ToStringArray());
                }
            }

            xlWorksheet.Cell(3, 1).Value = listOfStringArrays;

            xlWorkbook.SaveAs(tempFile);

            Cursor.Current = Cursors.Default;

            Process.Start(tempFile);
        }


    public partial class Item
    {
        public string[] ToStringArray()
        {
            return this.ToStringList().ToArray();
        }

        public List<string> ToStringList()
        {
            List<string> stringList = new List<string>();

            try
            {
                stringList.Add(this.ManufacturerName != null ? this.ManufacturerName : string.Empty);
                stringList.Add(this.PriceSheetDate != null ? ((DateTime)this.PriceSheetDate).ToShortDateString() : string.Empty);
                stringList.Add(this.PartNumber != null ? this.PartNumber : string.Empty);
                stringList.Add(this.ItemDescriptionShort != null ? this.ItemDescriptionShort : string.Empty);
                stringList.Add(this.UnitOfMeasure != null ? this.UnitOfMeasure : string.Empty);
                stringList.Add(this.MsrpPrice != null ? this.MsrpPrice.ToString() : string.Empty);
                stringList.Add(this.CostColumn1 != null ? this.CostColumn1.ToString() : string.Empty);
                stringList.Add(this.CostColumn1Description != null ? this.CostColumn1Description : string.Empty);
                stringList.Add(this.CostColumn2 != null ? this.CostColumn2.ToString() : string.Empty);
                stringList.Add(this.CostColumn2Description != null ? this.CostColumn2Description : string.Empty);
                stringList.Add(this.CostColumn3 != null ? this.CostColumn3.ToString() : string.Empty);
                stringList.Add(this.CostColumn3Description != null ? this.CostColumn3Description : string.Empty);
                stringList.Add(this.CostColumn4 != null ? this.CostColumn4.ToString() : string.Empty);
                stringList.Add(this.CostColumn4Description != null ? this.CostColumn4Description : string.Empty);
                stringList.Add(this.CostColumn5 != null ? this.CostColumn5.ToString() : string.Empty);
                stringList.Add(this.CostColumn5Description != null ? this.CostColumn5Description : string.Empty);
                stringList.Add(this.Currency != null ? this.Currency : string.Empty);
                stringList.Add(this.Weight != null ? this.Weight.ToString() : string.Empty);
                stringList.Add(this.WeightUnitOfMeasure != null ? this.WeightUnitOfMeasure : string.Empty);
                stringList.Add(this.SkuUpc != null ? this.SkuUpc : string.Empty);
                stringList.Add(this.ModelName != null ? this.ModelName : string.Empty);
                stringList.Add(this.ItemDescriptionLong != null ? this.ItemDescriptionLong : string.Empty);
                stringList.Add(this.ItemDescriptionOther != null ? this.ItemDescriptionOther : string.Empty);
                stringList.Add(this.Serialized != null ? this.Serialized.ToString() : string.Empty);
                stringList.Add(this.NotForSale != null ? this.NotForSale.ToString() : string.Empty);
                stringList.Add(this.ManufacturerName != null ? this.ManufacturerName : string.Empty);
                stringList.Add(this.ItemStatus != null ? this.ItemStatus : string.Empty);
                stringList.Add(this.ManufacturerItemCategoryCode != null ? this.ManufacturerItemCategoryCode : string.Empty);
                stringList.Add(this.PartNumberReplacementItem != null ? this.PartNumberReplacementItem : string.Empty);
                stringList.Add(this.ModelNameReplacementItem != null ? this.ModelNameReplacementItem : string.Empty);
                stringList.Add(this.RequiredAccessories != null ? this.RequiredAccessories : string.Empty);
                stringList.Add(this.OptionalAccessories != null ? this.OptionalAccessories : string.Empty);
                stringList.Add(this.MapPrice != null ? this.MapPrice.ToString() : string.Empty);
                stringList.Add(this.GsaItemCost != null ? this.GsaItemCost.ToString() : string.Empty);
                stringList.Add(this.GsaSellPrice != null ? this.GsaSellPrice.ToString() : string.Empty);
                stringList.Add(this.DiscountOffList != null ? this.DiscountOffList.ToString() : string.Empty);
                stringList.Add(this.FreightPolicy != null ? this.FreightPolicy : string.Empty);
                stringList.Add(this.FreightCode != null ? this.FreightCode : string.Empty);
                stringList.Add(this.FreightClass != null ? this.FreightClass : string.Empty);
                stringList.Add(this.DropShip != null ? this.DropShip.ToString() : string.Empty);
                stringList.Add(this.CertificateForOrigin != null ? this.CertificateForOrigin : string.Empty);
                stringList.Add(this.ManufacturerWebsiteUrl != null ? this.ManufacturerWebsiteUrl : string.Empty);
                stringList.Add(this.ManufacturerDivisionName != null ? this.ManufacturerDivisionName : string.Empty);
                stringList.Add(this.ItemInfocommIqCategory != null ? this.ItemInfocommIqCategory : string.Empty);
                stringList.Add(this.InfocommMemberNumber != null ? this.InfocommMemberNumber.ToString() : string.Empty);
                stringList.Add(this.Notes != null ? this.Notes : string.Empty);
            }
            catch (Exception ex)
            {
                
            }


            return stringList;
        }
    }

 

Coordinator
Feb 6, 2011 at 2:32 AM

Please run the following piece of code and let me know the numbers you're getting.

        static void Main(string[] args)
        {
            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("New");
            List<String[]> list = GetList();
            ws.Cell(1, 1).Value = list;
            var start = DateTime.Now;
            wb.SaveAs("Sandbox.xlsx");
            var end = DateTime.Now;
            var saved = (end - start).TotalSeconds;
            Console.WriteLine("Saved in {0} secs.", saved);
            Console.ReadKey();
        }

        static List<string[]> GetList()
        {
            List<String[]> list = new List<string[]>();
            foreach (Int32 ro in Enumerable.Range(1, 3000))
            {
                String[] arr = new string[50];
                foreach (Int32 co in Enumerable.Range(1, 50))
                {
                    arr[co - 1] = Guid.NewGuid().ToString();
                }
                list.Add(arr);
            }
            return list;
        }

Feb 6, 2011 at 2:53 AM

When I compile to "Any CPU", 13.xxx seconds.

When I compile to "x86", 666.xxx seconds (no joke)...

Coordinator
Feb 6, 2011 at 1:29 PM

Then I honestly don't know what's going on because I get ~12 secs compiling to x86, x64, and Any CPU

Sorry =/

Feb 7, 2011 at 2:35 PM

Yeah, on a different computer I have no issues.  Thanks for the time.  I'll play around and let you know if I track it down.  Thanks!

Feb 9, 2011 at 2:40 AM
Edited Feb 9, 2011 at 2:44 AM

Edit:  Intellitrace, not Intellisense

 

Well, after some experimenting, my performance problem is because IntelliTrace was Enabled, and since it only affects x86 builds, I was having no performance issues using other configurations.  Disabling it solves everything.  This was verified by running the x86 compiled solution outside of VS 2010, and achieving normal times.  Sorry for the false alarm.