Excel Generation taking too much time

Hi,

I have a problem with adding data into the cell as it is taking too much time. Time taking is due to multiple loops being iterating to populate a cell.

I have almost like 8000 rows and about 15 columns.

This is my code

public IActionResult GenerateExcelMultiLevel()
{
    JObject response = new JObject();
    try
    {
        //Json object,Json Array objects
        var jsonObject = new JObject();
        var jsonArray = new JArray();

        var requestProperty = _context.HttpContext.Request;
        IFormFile templateFile = null;
        IFormFile jsonFile = null;

        bool IsFileValid = CommonMethods.IsFileValid(requestProperty.Form.Files, _config, Constants.Report.ExcelReportValidExtension);

        if (IsFileValid)
        {
            //Run loop and store the file comming from Form body in the appropriate object.
            foreach (var file in requestProperty.Form.Files)
            {
                if (file.Name == Constants.Api.TemplateFileName)
                {
                    templateFile = file;
                }
                else
                {
                    jsonFile = file;
                }
            }
        }
        else
        {
            response = new JObject();
            response.Add(Constants.Api.Status, Constants.Api.Failure);
            response.Add(Constants.Api.ResponseBody, CommonMethods.GetSystemMessage("4")); //"The file is not valid. Please upload the valid extension files."
            CommonMethods.logApiRequestResponse(_context, response);

            return NotFound(response.ToString());
        }

        //Check if the uploaded file is exist or not
        if ((templateFile != null && templateFile.Length > 0) && (jsonFile != null && jsonFile.Length > 0))
        {
            string path = Path.Combine(Directory.GetCurrentDirectory(), _config.GetValue<string>(Constants.Gembox.DownloadPath));

            //Create the directory if not exist.
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            //Get the template file name
            var tempFileName = ContentDispositionHeaderValue.Parse(templateFile.ContentDisposition).FileName.Trim('"');

            //Adding the time stamp after the name for making it unique.
            tempFileName = "Template_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + Path.GetExtension(tempFileName);

            //Save the file in the directory.
            using (FileStream stream = new FileStream(Path.Combine(path, tempFileName), FileMode.Create))
            {
                templateFile.CopyTo(stream);
            }

            //Get the json file name.
            var jsonFileName = ContentDispositionHeaderValue.Parse(jsonFile.ContentDisposition).FileName.Trim('"');

            //Adding the time stamp for making the name as unique
            jsonFileName = "Json_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + Path.GetExtension(jsonFileName);

            //Save the file in the directory.
            using (FileStream stream = new FileStream(Path.Combine(path, jsonFileName), FileMode.Create))
            {
                jsonFile.CopyTo(stream);
            }

            //Saving the json file path in variable
            var jsonFilePath = Path.Combine(Directory.GetCurrentDirectory(), _config.GetValue<string>(Constants.Gembox.DownloadPath), jsonFileName);

            //Check if file exists or not
            if (System.IO.File.Exists(jsonFilePath))
            {
                //getting the jobject from the json file
                JArray fileJsonArray = new JArray();
                using (StreamReader r = new StreamReader(jsonFilePath))
                {
                    string json = r.ReadToEnd();
                    if (CommonMethods.IsValidJson(json))
                    {
                        fileJsonArray = JsonConvert.DeserializeObject<JArray>(json);
                    }
                    else
                    {
                        response = new JObject();
                        response.Add(Constants.Api.Status, Constants.Api.Failure);
                        response.Add(Constants.Api.ResponseBody, CommonMethods.GetSystemMessage("5")); //"The json is not valid."
                        CommonMethods.logApiRequestResponse(_context, response);

                        return NotFound(response.ToString());
                    }
                }

                //Loading the template
                // If using Professional version, put your serial key below.
                string key = _config.GetValue<string>(Constants.Gembox.ExcelKey);
                SpreadsheetInfo.SetLicense(key);

                //Getting the template file path
                string filepath = Path.Combine(Directory.GetCurrentDirectory(), _config.GetValue<string>(Constants.Gembox.DownloadPath), tempFileName);

                // Load an Excel template.
                var workbook = ExcelFile.Load(filepath);

                // Find cells with placeholder text and set their values.
                int row = 0, column = 0;

                //Running the loop for getting the sheet object of excel
                foreach (var sheetObject in fileJsonArray.SelectMany(o => (IDictionary<string, JToken>)o))
                {
                    // Get template sheet.
                    var worksheet = workbook.Worksheets[sheetObject.Key.ToString()];

                    // Getting the sheet details with key and value pair
                    var sheetDetails = JsonConvert.DeserializeObject<JObject>(sheetObject.Value.ToString());

                    List<int> dataRowList = new List<int>();

                    //Running the loop for parsing the values of one sheet object
                    foreach (var details in sheetDetails)
                    {
                        try
                        {
                            //Add a check for json array
                            if (details.Value.Type != JTokenType.Array)
                            {
                                if (worksheet.Cells.FindText(details.Key, out row, out column))
                                {
                                    string value = details.Value.ToString();

                                    //Checking whether the value is Integer or not
                                    long outIntValue = 0;
                                    bool IsInteger = long.TryParse(value, out outIntValue);

                                    //Checking whether the value is decimal or not
                                    decimal outDecimalValue = 0;
                                    bool IsDecimal = decimal.TryParse(value, out outDecimalValue);

                                    if (IsInteger)
                                    {
                                        worksheet.Cells[row, column].Value = outIntValue;
                                    }
                                    else if (IsDecimal)
                                    {
                                        worksheet.Cells[row, column].Value = outDecimalValue;
                                    }
                                    else if (details.Key.ToLower().Contains("image:"))
                                    {
                                        // Get the height of the cell.
                                        var cellHeight = worksheet.Rows[row].Height;

                                        // Get the width of the cell.
                                        var cellWidth = worksheet.Columns[column].Width;

                                        var cellHeightInCM = GemBox.Spreadsheet.LengthUnitConverter.Convert(cellHeight, GemBox.Spreadsheet.LengthUnit.Twip, GemBox.Spreadsheet.LengthUnit.Centimeter);
                                        var cellWidthInCM = GemBox.Spreadsheet.LengthUnitConverter.Convert(cellWidth, GemBox.Spreadsheet.LengthUnit.ZeroCharacterWidth256thPart, GemBox.Spreadsheet.LengthUnit.Centimeter);

                                        if (!string.IsNullOrEmpty(value))
                                        {
                                            byte[] imageByte = null;
                                            if (CommonMethods.IsImageUrl(value, _config, Constants.Report.ImagesValidExtenstion))
                                            {
                                                imageByte = CommonMethods.GetURLImage(value);
                                            }
                                            else
                                            {
                                                // Load the image data from a base64 string.
                                                imageByte = Convert.FromBase64String(value);
                                            }

                                            MemoryStream imageMemoryStream = new MemoryStream(imageByte);
                                            var imageDetails = Image.FromStream(imageMemoryStream);

                                            List<string> validExtension = _config.GetSection(Constants.Report.ImagesValidExtenstion).Get<List<string>>();

                                            var cellIndex = worksheet.Cells[row, column].ToString();
                                            var imageType = imageDetails.RawFormat.ToString().ToLower();

                                            if (validExtension.Any(x => x.ToLower() == imageType))
                                            {
                                                worksheet.Pictures.Add(imageMemoryStream, (imageType == "jpeg" || imageType == "jpg" ? ExcelPictureFormat.Jpeg : ExcelPictureFormat.Png), cellIndex, cellWidthInCM, cellHeightInCM, GemBox.Spreadsheet.LengthUnit.Centimeter);
                                                worksheet.Cells[row, column].Value = "";
                                            }
                                            else
                                            {
                                                worksheet.Cells[row, column].Value = CommonMethods.GetSystemMessage("4");
                                            }
                                        }
                                    }
                                    else
                                    {
                                        worksheet.Cells[row, column].Value = details.Value.ToString();
                                    }
                                }
                            }
                            else
                            {
                                //Getting the array data
                                var arrayData = JsonConvert.DeserializeObject<JArray>(details.Value.ToString());
                                putArrayInExcel(worksheet, arrayData, row, column, dataRowList);
                            }
                        }
                        catch (Exception ex)
                        {
                            CommonMethods.logExceptionMessage(_context, ex);
                        }
                    }
                }

                // Save the modified Excel template to output file.
                workbook.Save(filepath);

                //Converting the file content to memory stream.
                MemoryStream stream = null;
                using (FileStream fileStream = System.IO.File.Open(filepath, FileMode.Open))
                {
                    stream = new MemoryStream();
                    fileStream.CopyTo(stream);
                }

                //After converting the file to stream, deleting the file from the path.
                if (System.IO.File.Exists(filepath))
                {
                    System.IO.File.Delete(filepath);
                }

                if (System.IO.File.Exists(jsonFilePath))
                {
                    System.IO.File.Delete(jsonFilePath);
                }

                //Checking the stream for content and then download file with formatted data.
                if (stream != null)
                {
                    stream.Position = 0;
                    string fileName = string.Format("{0}_{1}{2}", "GeneratedReport", DateTime.Now.ToString(), Path.GetExtension(tempFileName));

                    response = new JObject();
                    response.Add(Constants.Api.Status, Constants.Api.Success);
                    response.Add(Constants.Api.ResponseBody, CommonMethods.GetSystemMessage("7")); //File Memory Stream Generated
                    CommonMethods.logApiRequestResponse(_context, response);

                    return File(stream, Constants.Gembox.ExcelContentType, fileName);
                }
            }
        }

        response = new JObject();
        response.Add(Constants.Api.Status, Constants.Api.Failure);
        response.Add(Constants.Api.ResponseBody, CommonMethods.GetSystemMessage("8")); //File Memory Stream Not Generated
        CommonMethods.logApiRequestResponse(_context, response);

        return Ok(response.ToString());
    }
    catch (Exception ex)
    {
        CommonMethods.logExceptionMessage(_context, ex);

        response = new JObject();
        response.Add(Constants.Api.Status, Constants.Api.Failure);
        response.Add(Constants.Api.ResponseBody, string.Format(CommonMethods.GetSystemMessage("6"), ex.Message));
        //Exception : {0}
        CommonMethods.logApiRequestResponse(_context, response);

        return NotFound(response.ToString());
    }
}

private void putArrayInExcel(ExcelWorksheet worksheet, JArray arrayData, int row, int column, List<int> dataRowList, bool isFirstLevel = true)
{

    //Getting the count of all the array object in json array.
    int count = arrayData.Count;
    int nestedCount = 0;

    bool isRowsCreated = false;

    //Created the list for storing the column index in that. In multiple rows data only row index will change
    //and column index will be same.
    List<int> columnList = new List<int>();

    List<int> keyRowList = new List<int>();

    //Running the loop in array for taking the array object
    foreach (var arrayObj in arrayData)
    {
        var arrayObjDetails = JsonConvert.DeserializeObject<JObject>(arrayObj.ToString());

        //Checking if row created or not for multiple rows data
        if (!isRowsCreated)
        {
            //Running the loop in array object for getting the row and column index.
            foreach (var data in arrayObjDetails)
            {
                if (data.Value.Type == JTokenType.Array)
                {
                    //Getting the array data
                    var arrayChild = JsonConvert.DeserializeObject<JArray>(data.Value.ToString());
                    var obj = JsonConvert.DeserializeObject<JObject>(arrayChild[0].ToString());
                    worksheet.Cells.FindText(obj.Properties().Select(p => p.Name).FirstOrDefault(), true, true, out row, out column);
                }
                else
                {
                    worksheet.Cells.FindText(data.Key, true, true, out row, out column);

                    //adding the column index in the column list.
                    columnList.Add(column);
                }
            }

            //Created the multiple rows
            worksheet.Rows.InsertCopy(row + 1, count - 1, worksheet.Rows[row]);

            for (int j = row + 1; j < row + 1 + count - 1; j++)
            {
                keyRowList.Add(j);
            }
            isRowsCreated = true;
        }

        int i = 0;
        //Running the loop for setting the value of array object in the excel.
        foreach (var data in arrayObjDetails)
        {
            if (data.Value.Type == JTokenType.Array)
            {
                //Getting the array data
                var arrayChild = JsonConvert.DeserializeObject<JArray>(data.Value.ToString());

                putArrayInExcel(worksheet, arrayChild, row, column, dataRowList, false);

                var nestedArrayCount = CommonMethods.CountElementInNestedArray(arrayChild);

                nestedCount = arrayChild.Count >= nestedArrayCount ? arrayChild.Count : nestedArrayCount;

                foreach (var col in columnList)
                {
                    var range = worksheet.Cells.GetSubrangeAbsolute(row, col, row + nestedCount - 1, col);
                    range.Merged = true;
                }
                row = row + nestedCount - 1;
            }
            else
            {
                worksheet.Cells.FindText(data.Key, true, true, out row, out column);

                if (!dataRowList.Contains(row))
                {
                    dataRowList.Add(row);
                }

                var currentRow = worksheet.Rows[row + i];

                string value = data.Value.ToString();

                //Checking whether the value is Integer or not
                long outIntValue = 0;
                bool IsInteger = long.TryParse(value, out outIntValue);

                //Checking whether the value is decimal or not
                decimal outDecimalValue = 0;
                bool IsDecimal = decimal.TryParse(value, out outDecimalValue);

                if (IsInteger)
                {
                    worksheet.Cells[row, columnList[i++]].Value = outIntValue;
                }
                else if (IsDecimal)
                {
                    worksheet.Cells[row, columnList[i++]].Value = outDecimalValue;
                }
                else if (data.Key.ToLower().Contains("image:"))
                {
                    // Get the height of the cell.
                    var cellHeight = worksheet.Rows[row].Height;
                    column = columnList[i++];
                    // Get the width of the cell.
                    var cellWidth = worksheet.Columns[column].Width;

                    var cellHeightInCM = GemBox.Spreadsheet.LengthUnitConverter.Convert(cellHeight, GemBox.Spreadsheet.LengthUnit.Twip, GemBox.Spreadsheet.LengthUnit.Centimeter);
                    var cellWidthInCM = GemBox.Spreadsheet.LengthUnitConverter.Convert(cellWidth, GemBox.Spreadsheet.LengthUnit.ZeroCharacterWidth256thPart, GemBox.Spreadsheet.LengthUnit.Centimeter);

                    if (!string.IsNullOrEmpty(value))
                    {
                        byte[] imageByte = null;
                        if (CommonMethods.IsImageUrl(value, _config, Constants.Report.ImagesValidExtenstion))
                        {
                            imageByte = CommonMethods.GetURLImage(value);
                        }
                        else
                        {
                            // Load the image data from a base64 string.
                            imageByte = Convert.FromBase64String(value);
                        }

                        MemoryStream imageMemoryStream = new MemoryStream(imageByte);
                        var imageDetails = Image.FromStream(imageMemoryStream);

                        List<string> validExtension = _config.GetSection(Constants.Report.ImagesValidExtenstion).Get<List<string>>();

                        var cellIndex = worksheet.Cells[row, column].ToString();
                        var imageType = imageDetails.RawFormat.ToString().ToLower();

                        if (validExtension.Any(x => x.ToLower() == imageType))
                        {
                            worksheet.Pictures.Add(imageMemoryStream, (imageType == "jpeg" || imageType == "jpg" ? ExcelPictureFormat.Jpeg : ExcelPictureFormat.Png), cellIndex, cellWidthInCM, cellHeightInCM, GemBox.Spreadsheet.LengthUnit.Centimeter);
                            worksheet.Cells[row, column].Value = "";
                        }
                        else
                        {
                            worksheet.Cells[row, column].Value = CommonMethods.GetSystemMessage("4");
                        }
                    }
                }
                else
                {
                    worksheet.Cells[row, columnList[i++]].Value = data.Value.ToString();
                }
            }
        }
        row++;
    }

    if (isFirstLevel)
    {
        foreach (var keyRow in keyRowList)
        {
            if (!dataRowList.Contains(keyRow))
            {
                worksheet.Rows.Remove(keyRow);
            }
        }

        keyRowList.Clear();
    }
}
#endregion Report Generated for Excel template Nested Level

Any help will be highly appreciated.

Thank you in advance.

Hi Vishal,

There is a lot of code here, did you measure different parts of it?
I’m unsure how much time is spent, for instance, on deserializing JSON… I believe that setting the ExcelCell.Value property multiple times (8000x15) could be less compared to that.

Anyway, can you create a small Visual Studio project with some input data so that we can investigate your solution?

Currently, the only thing that I noticed is that you’re using worksheet.Cells.FindText inside the loops. Note that this method will iterate through the cells in the sheet until it finds the first occurrence of the searched text. You could improve this part by iterating through cells by yourself and finding all the searched text and storing them in let’s say a dictionary. However, given your context here I doubt that this will be a significant improvement.

To help you with anything specific, I would need to run your repro project locally and measure different parts of your code to see the general execution time of your implementation.

Regards,
Mario