This project has moved. For the latest updates, please go here.

Reading data takes too much time.

Jul 19, 2012 at 3:38 AM
Edited Jul 19, 2012 at 3:49 AM

Hi

I am trying to read a excelsheet which contains 27 columns and around 6500 rows. I am using ClosedXML for writing which works really fast and fine but when it comes to reading its very slow. 

 

 var workbook = new XLWorkbook(fileName);
 var worksheet = workbook.Worksheet(1);

 var range = worksheet.Range(worksheet.FirstCellUsed().Address, worksheet.LastCellUsed().Address);

 for (int i = 1; i < range.Rows().Count(); i++)
 {
         var row = range.Row(i);
         object[] rowData = new object[dtData.Columns.Count];
         int cellCount = row.CellCount();
         for (int j = 1; j <= cellCount; j++)
         {
                 var cellValue = row.Cell(j).Value;
                 rowData[j - 1] = cellValue;
         }
         dtData.Rows.Add(rowData);
 }

 

I waited for 30 seconds after the reading method started i checked, it read around 350 records.

Please correct me if i am doing it wrong.

Thanks in advance,

Dinesh

Jul 19, 2012 at 3:54 AM

ok i get it.

i moved range.Rows().Count(); out of for condition and placed above, now its working fine.

Coordinator
Jul 19, 2012 at 4:18 AM

I'd clean it up a little bit more :)

            var workbook = new XLWorkbook(fileName);
            var worksheet = workbook.Worksheets.First();

            var range = worksheet.RangeUsed();
            var colCount = range.ColumnCount();
            foreach (var row in range.RowsUsed())
            {
                object[] rowData = new object[colCount];
                Int32 i = 0;
                row.Cells().ForEach(c => rowData[i++] = c.Value);
                dtData.Rows.Add(rowData);
            }

Jul 24, 2012 at 11:32 AM

Thanks a lot :)

Aug 8, 2012 at 7:55 AM
Edited Aug 8, 2012 at 8:11 AM

Hello MDeLeon,

I found the contingency plan and implemented it for my previous Workbook Save issue.

But now i am facing another issue with respect to opening / getting the handle (object) of an existing file.

var workBook = new XLWorkBook(strFileName);

When i execute the above statement, for an excel file with 450 rows and 150 columns, it takes about a minute and for an excel file with 10000 rows and 150 columns, it fails to execute, that is, i ended up with an Out of Memory exception.

But when i tried to open it up with Basic OpenXML, irrespective of the size or the contents of the file, it opened up in a second. Unfortunately i cannot use the basic openxml as i faced few restrictions with the formatting and styling.

Please have a look into this issue.

I have mailed you the respective project and the related files.

Thanks,

Krishna (kkvid007)

Coordinator
Aug 8, 2012 at 3:57 PM

Out of memory = too big of a file and running on x86. Run your program in x64.

OpenXML loads the file instantly = You're only opening the file, not reading it's contents or doing any kind of processing. ClosedXML reads the entire file at the beginning.

Taking too much time = The file you're loading has 5 sheets with a truckload of merged cells. These were taking extra time to load. I just checked in some changes that fixes this issue. Your file now loads in 1-2 seconds.

Aug 9, 2012 at 1:07 PM
Edited Aug 9, 2012 at 1:44 PM

Hi MDeLeon,

Thanks a lot for the quick fix.

File with 10000 and 150 columns is now opening in 24 seconds.

But I am facing a new issue Manuel. When i execute the statement

workbook.save();

It is throwing the following exception;

System.IO.IsolatedStorage.IsolatedStorageException : Unable to determine the domain of the caller.

I researched a little and debugged closedxml dll. I found out that the error was in the last line (closing the package) of the following code;

using(package)
{
   CreateParts(package);
   //package.close();
}

The code is from

CreatePackage(String filePath)

The above method is from XLWorkBook_Save.cs of ClosedXML DLL.

I am facing this error only when i write data into the file with more than 1000 rows. Unfortunately the requirement's range is that minimum data is of 1000 rows and the maximum data is of 10000 rows

Here is the complete error trace for your reference;

System.IO.IsolatedStorage.IsolatedStorageException: Unable to determine domain of the caller.

   at System.IO.IsolatedStorage.IsolatedStorage._InitStore(IsolatedStorageScope scope, Evidence domainEv, Type domainEvidenceType, Evidence assemEv, Type assemblyEvidenceType, Evidence appEv, Type appEvidenceType)

   at System.IO.IsolatedStorage.IsolatedStorage.InitStore(IsolatedStorageScope scope, Type domainEvidenceType, Type assemblyEvidenceType)

   at System.IO.IsolatedStorage.IsolatedStorageFile.GetStore(IsolatedStorageScope scope, Type domainEvidenceType, Type assemblyEvidenceType)

   at MS.Internal.IO.Packaging.PackagingUtilities.ReliableIsolatedStorageFileFolder.GetCurrentStore()

   at MS.Internal.IO.Packaging.PackagingUtilities.ReliableIsolatedStorageFileFolder..ctor()

   at MS.Internal.IO.Packaging.PackagingUtilities.GetDefaultIsolatedStorageFile()

   at MS.Internal.IO.Packaging.PackagingUtilities.CreateUserScopedIsolatedStorageFileStreamWithRandomName(Int32 retryCount, String& fileName)

   at MS.Internal.IO.Packaging.SparseMemoryStream.EnsureIsolatedStoreStream()

   at MS.Internal.IO.Packaging.SparseMemoryStream.SwitchModeIfNecessary()

   at MS.Internal.IO.Packaging.SparseMemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count)

   at MS.Internal.IO.Packaging.CompressEmulationStream.Write(Byte[] buffer, Int32 offset, Int32 count)

   at MS.Internal.IO.Packaging.CompressStream.Write(Byte[] buffer, Int32 offset, Int32 count)

   at MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Write(Byte[] buffer, Int32 offset, Int32 count)

   at MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Write(Byte[] buffer, Int32 offset, Int32 count)

   at System.Xml.XmlUtf8RawTextWriter.FlushBuffer()

   at System.Xml.XmlUtf8RawTextWriter.WriteElementTextBlock(Char* pSrc, Char* pSrcEnd)

   at System.Xml.XmlUtf8RawTextWriter.WriteString(String text)

   at System.Xml.XmlWellFormedWriter.WriteString(String text)

   at DocumentFormat.OpenXml.OpenXmlLeafTextElement.WriteContentTo(XmlWriter w)

   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)

   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)

   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)

   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)

   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)

   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)

   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)

   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)

   at DocumentFormat.OpenXml.OpenXmlPartRootElement.WriteTo(XmlWriter xmlWriter)

   at DocumentFormat.OpenXml.OpenXmlPartRootElement.SaveToPart(OpenXmlPart openXmlPart)

   at DocumentFormat.OpenXml.OpenXmlPartRootElement.Save()

   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContent(OpenXmlPart part)

   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.TrySavePartContent(OpenXmlPart part)

   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContents()

   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose(Boolean disposing)

   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose()

   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Close()

   at ClosedXML.Excel.XLWorkbook.CreatePackage(String filePath) in C:\Documents and Settings\k.nanjundaprasad\Desktop\ClosedXML Src\closedxml-76244\ClosedXML\ClosedXML\ClosedXML\Excel\XLWorkbook_Save.cs:line 90

   at ClosedXML.Excel.XLWorkbook.Save() in C:\Documents and Settings\k.nanjundaprasad\Desktop\ClosedXML Src\closedxml-76244\ClosedXML\ClosedXML\ClosedXML\Excel\XLWorkbook.cs:line 386

Please help me on this Manuel.

Thanks,

Krishna (kkvid007)

Coordinator
Aug 9, 2012 at 2:21 PM

That's a problem writing to the isolated storage not of ClosedXML. Google "IsolatedStorageException : Unable to determine the domain of the caller"

Aug 22, 2012 at 5:19 PM

Hi Manuel,

Sorry for such a delay. I was busy in implementing the functionality of my project. Now i am optimizing the application.

Thank you very much for such an amazing DLL, which has made my life easy with respect to OpenXML excel generation.

But I am facing the same issue again while opening the excel workbook.

var workbook = new XLWorkbook(strFileName);

I have mailed you the project along with the workbook that i am using to populate.

The file is of 3MB and has about 2424 rows and 130 columns. As you mentioned earlier, this file has Boeing 747 * 100 times merged cells.

ClosedXML is taking 3 minutes and 20 seconds to get the handle of this workbook. Which inturn is hampering the performance of the entire systems.

So plesae please please help me by optimizing ClosedXML while opening workbooks.

 

Thanks a ton in advance.

Coordinator
Aug 22, 2012 at 6:22 PM

Pickup the latest source code. The next time create an issue and attach the file(s).

Aug 23, 2012 at 7:15 AM

Thanks a lot Manuel.. That really boosted my application's performance.

From next time, i will raise an issue.

Thanks again.

 

Regards,

Krishna