OpenXML is very slow (more than 2 hours) to read .xlsx file with 2 lakh rows and 8 columns

R, Divya 0

I generated an excel file (.xlsx) using OpenXML in .Net 6 application. The file has 2 lakh rows and 8 columns.

I modified the file by updating few columns and I am trying to read the .xlsx file using OpenXML in .Net 6 application. It takes more than 2 hours to read 2 lakh rows.

But, if I try to read the unmodified version of OpenXML generated .xlsx file, it takes only 2 mins to read the 2 lakh rows. why is it very slow if I modify the data in the file? could you please help immediately?

Note: I tried with both DOM and SAX approaches, it does not help.

AgaveJoe 28,371 Reputation points

2023-05-06T11:39:05.2133333+00:00

why is it very slow if I modify the data in the file?

There is a bug in your code.

could you please help immediately?

We cannot see your code and do not have sample data. I recommend you troubleshooting your code.

R, Divya 0

I am able to successfully read the 2 lakh records, but it is taking more than 2 hours.
Here is the sample console application. Program.txt

// See https://aka.ms/new-console-template for more information

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;


Console.WriteLine("Hello, World!");
ReadExcel p = new ReadExcel();
p.ParseDevices();
Console.ReadLine();



public class ReadExcel
{
    public void ParseDevices()
    {

        byte[] bytes = File.ReadAllBytes("C:\\SampleExcel.xlsx");
        Console.WriteLine($"Started on {System.DateTime.Now} ");
        var devices = new List<DeviceDto>();
        using (var mem = new MemoryStream())
        {
            mem.Write(bytes, 0, bytes.Length);
            mem.Seek(0, SeekOrigin.Begin);
            
            using (var doc = SpreadsheetDocument.Open(mem, false))
            {
                var workbookPart = doc.WorkbookPart;
                var sharedStringTablePart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
                var sst = sharedStringTablePart.SharedStringTable;

                var deviceId = workbookPart.Workbook.Descendants<Sheet>().Single(s => s.Name == "Devices").Id;
                var worksheetPart = (WorksheetPart)workbookPart.GetPartById(deviceId);
                var sheet = worksheetPart.Worksheet;
                var rows = sheet.Descendants<Row>().Where(x => !string.IsNullOrEmpty(x.InnerText)).ToArray();
                

                for (var rowCount = 1; rowCount < rows.Length; rowCount++)
                {
                    var deviceDto = new DeviceDto();
                    var rowCells = rows[rowCount].Elements<Cell>().ToArray();
                    deviceDto.DeviceName = GetCellValue(rowCells, "A", sst);
                    var Id = GetCellValue(rowCells, "B", sst);
                    deviceDto.Id = int.TryParse(Id, out _) ? Convert.ToInt32(Id) : default(int);
                    deviceDto.DeviceId = GetCellValue(rowCells, "C", sst);
                    deviceDto.DeviceType = GetCellValue(rowCells, "D", sst);
                    deviceDto.MappedSpaceName = GetCellValue(rowCells, "E", sst);
                    var mappedSpaceId = GetCellValue(rowCells, "F", sst);
                    deviceDto.MappedSpaceId = int.TryParse(mappedSpaceId, out _) ? Convert.ToInt32(mappedSpaceId) : default(int?);
                    deviceDto.IpAddress = GetCellValue(rowCells, "G", sst);

                    devices.Add(deviceDto);
                }
            }

            Console.WriteLine($"Processed records count = {devices.Count} ");
        }
        
        Console.WriteLine($"Completed on {System.DateTime.Now} ");
        Console.ReadLine();
    }


    private static string GetCellValue(IEnumerable<Cell> rowCells, string columnReference, SharedStringTable sharedStringTable)
    {
        var cell = rowCells.FirstOrDefault(x => Regex.Replace(x.CellReference, "[0-9]", "") == columnReference);
        if (cell?.CellValue == null)
        {
            return String.Empty;
        }

        if (cell.DataType != null && cell.DataType == "s")
        {
            var ssid = int.Parse(cell.CellValue.Text);
            return sharedStringTable.ChildElements[ssid].InnerText;
        }

        return cell.CellValue.Text;
    }
}
    public class DeviceDto
    {
        public int Id { get; set; }
        public string DeviceId { get; set; }
        public string DeviceName { get; set; }
        public string DeviceType { get; set; }
        public int? MappedSpaceId { get; set; }
        public string MappedSpaceName { get; set; }
        public string IpAddress { get; set; }
    }

Sample Excel Data:
User's image

R, Divya 0 Reputation points

2023-05-07T06:01:52.78+00:00

Hi, any update on this issue?

Here is the output to read 4 lakh records.

Hello, World!

Started on 5/6/2023 9:37:05 PM

Processed records count = 440013

Completed on 5/7/2023 7:33:59 AM

We generated the input excel file with cell values as inline strings and we tried to read the file in C# Console App using OpenXML library, 4 lakh records were read in 3to5 minutes.

When we open the input excel file in Microsoft Excel Application and modify any cell values and then try to read the same file in C# Console App using OpenXML library, the time to read 4 lakh records is growing even more than few hours.

1 answer

Zhi Lv - MSFT 32,336 Microsoft Vendor

Hi @R, Divya

When we open the input excel file in Microsoft Excel Application and modify any cell values and then try to read the same file in C# Console App using OpenXML library, the time to read 4 lakh records is growing even more than few hours.

After modifying the excel file, whether the excel file size changed to much? And when read the excel file, whether the memory usage is very high?

As far as I know, when using SpreadsheetDocument and SheetData classes, which load the entire file into memory and can cause performance issues for large files. So, if the issue relates the memory, you can employ an OpenXMLReader to read the XML in the file one element at a time, without having to load the entire file into memory.

Code like this:

    // The SAX approach.
    static void ReadExcelFileSAX(string fileName)
    {
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
        {
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

            OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
            string text;
            while (reader.Read())
            {
                if (reader.ElementType == typeof(CellValue))
                {
                    text = reader.GetText();
                    Console.Write(text + " ");
                }
            }
            Console.WriteLine();
            Console.ReadKey();
        }
    }

More detail information, see Parse and read a large spreadsheet document (Open XML SDK) (#Approaches to Parsing Open XML Files).

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

R, Divya 0

we do not have any issues with memory, we were able to read the records completely, BUT it took around 10 hours to complete 4 lakh records. ( took 2.5 hours for 2 lakh records). Already we referred msdn for SAX approach and tried the same, I have mentioned that as well. But there is NO improvement.

could you help in reading the excel faster?

Note: If the excel has only inline strings, the retrieval is completed in few mins. If the excel has SharedStringTable, then it is taking more hours (direct proportional to no of rows) to read completely. By default, Excel application uses SST, we could not change that.

Here is the sample code using SAX approach for your reference.

// See https://aka.ms/new-console-template for more information
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Console.WriteLine("Hello, World!");
ReadExcelFileSAX("C:\\SampleExcel.xlsx");

static void ReadExcelFileSAX(string fileName)
{
    Console.WriteLine($"Started on {System.DateTime.Now} ");
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
    {
        var workbookPart = spreadsheetDocument.WorkbookPart;
        var sharedStringTablePart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
        var sst = sharedStringTablePart.SharedStringTable;

        var deviceId = workbookPart.Workbook.Descendants<Sheet>().Single(s => s.Name == "Devices").Id;
        var worksheetPart = (WorksheetPart)workbookPart.GetPartById(deviceId);
        //var sheet = worksheetPart.Worksheet;

        //WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        //WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
        string text;
        while (reader.Read())
        {
            if (reader.ElementType == typeof(Cell))
            {
                string cellValue = string.Empty;
                Cell c = (Cell)reader.LoadCurrentElement();

                if (c?.CellValue == null)
                {
                    cellValue = String.Empty;
                }
                else if (c.DataType != null && c.DataType == CellValues.SharedString)
                {
                   // int index = int.Parse(c.CellValue.InnerText);
                    // SharedStringItem ssi = sst.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));
                    cellValue = sst.ElementAt(int.Parse(c.CellValue.InnerText)).InnerText;
                }
                else
                {
                    cellValue = c.CellValue.InnerText;
                }
                Console.Write(cellValue + " ");

            }
        }
        Console.WriteLine();
        Console.WriteLine($"Completed on {System.DateTime.Now} ");
        Console.ReadKey();
    }
}

Share via

OpenXML is very slow (more than 2 hours) to read .xlsx file with 2 lakh rows and 8 columns

1 answer

Your answer