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.