We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Cell Value2

Hi,

I am able to set cell.value and cell.value2 from server (webapi) but how do I access cell.value2 from angular, I only see value, we basically need another field to store some data in each cell when processing the file on server.

Thanks.

11 Replies

SD Saranya Dhayalan Syncfusion Team January 2, 2020 01:13 PM UTC

Hi Umair, 
  
Good day to you. 
  
Before we start to work with this, we would like to know the following details, 
  
1.       Could you please confirm whether you need to add custom property in cell on server side and use it in client side. And also please confirm whether you need to access the same custom property in server side. 
2.       Please share your exact use case. 
  
Could you please get back to us with the above details? So that we can sort out and provide you the better solution quickly. 
  
Could you please check the below documentation link for Value and Value2 property usage in XlsIO. 
  
  
Regards, 
Saranya D 



UA Umair Ahmed replied to Saranya Dhayalan January 2, 2020 04:49 PM UTC

Hi Umair, 
  
Good day to you. 
  
Before we start to work with this, we would like to know the following details, 
  
1.       Could you please confirm whether you need to add custom property in cell on server side and use it in client side. And also please confirm whether you need to access the same custom property in server side. 
2.       Please share your exact use case. 
  
Could you please get back to us with the above details? So that we can sort out and provide you the better solution quickly. 
  
Could you please check the below documentation link for Value and Value2 property usage in XlsIO. 
  
  
Regards, 
Saranya D 


Hi,

Yes we need to add / set the custom property in cell on server side and use it in client side.
Basically we just need to set another field from server side that would contain some text, from the client when user clicks on a cell, this data would be displayed in a separate div element.


SD Saranya Dhayalan Syncfusion Team January 6, 2020 12:24 PM UTC

Hi Umair, 
 
Thanks for your update. 
 
We would like to suggest you to maintain a separate collection for each cell to add custom value when you are trying to add Value2 in server side and send the collection to client side by adding it to the string returned by Open method of Workbook, you can access it in openComplete event in the client side and based on the collection you can display the data in the div element in select event of Spreadsheet component. Please refer the below code snippets. 
 
HomeController.cs 
public IActionResult Open(IFormCollection openRequest) 
        { 
            ExcelEngine excelEngine = new ExcelEngine(); 
            IWorkbook workbook; 
            Stream memStream = (openRequest.Files[0] as IFormFile).OpenReadStream(); 
            workbook = excelEngine.Excel.Workbooks.Open(memStream, ExcelOpenType.Automatic); 
            List<CustomArgs> customArgs = GetCustomArgs(workbook); 
            MemoryStream outputStream = new MemoryStream(); 
            workbook.SaveAs(outputStream); 
            IFormFile formFile = new FormFile(outputStream, 0, outputStream.Length, "", openRequest.Files[0].FileName); // converting MemoryStream to IFormFile 
            OpenRequest open = new OpenRequest(); 
            open.File = formFile; 
            var content = Workbook.Open(open); 
            content = content.Insert(content.Length - 2, ", \"customArgs\":" + JsonConvert.SerializeObject(customArgs)); // Adding customArgs to the string returned by Open method 
            return Content(content); 
        } 
 
        private List<CustomArgs> GetCustomArgs(IWorkbook workbook) 
        { 
            List<CustomArgs> customArgs = new List<CustomArgs>(); 
            IWorksheet worksheet; 
            int i = 0, cnt = workbook.Worksheets.Count; 
            while (i < cnt) 
            { 
                worksheet = workbook.Worksheets[i]; 
                for (int j = 0; j < worksheet.UsedRange.LastRow; j++) 
                { 
                    for (int k = 0; k < worksheet.UsedRange.LastColumn; k++) 
                    { 
                        customArgs.Add(new CustomArgs { address = RangeImpl.GetCellName(k + 1, j + 1), customValue = "custom value for " + RangeImpl.GetCellName(k + 1, j + 1), sheetIndex = i }); 
                    } 
                } 
                i++; 
            } 
            return customArgs; 
        } 
 
 
public class CustomArgs 
    { 
        public string customValue { get; set; } 
        public string address { get; set; } 
        public int sheetIndex { get; set; } 
    } 
 
 
App.component.html 
<label>Custom Args:</label> 
<div id="customvalue"></div> 
<ejs-spreadsheet #default [openUrl]="openUrl" [allowOpen]="true" [saveUrl]="saveUrl" (created)="onCreated($event)" (select)="onSelect($event)" (openComplete)="onOpenComplete($event)"> 
</ejs-spreadsheet> 
 
 
App.component.ts 
export class AppComponent  {  
  @ViewChild('default') 
  public spreadsheetObj: SpreadsheetComponent; 
  public customArgs: any; 
 
  onCreated() { 
        …… 
  } 
 
  onOpenComplete(args: any) { 
    this.customArgs = args.response.data.customArgs; 
  } 
 
  onSelect(args: SelectEventArgs) { 
    for (var i = 0; i < this.customArgs.length; i++) { 
        if (this.spreadsheetObj.activeSheetTab - 1 === this.customArgs[i].sheetIndex && args.range === this.customArgs[i].address + ":" + this.customArgs[i].address) { 
            document.getElementById("customvalue").innerHTML = this.customArgs[i].customValue; 
            break; 
        } 
    } 
} 
} 
 
 
 
For your convenience we have prepared a sample. Please find the below sample link: 
 
 
 
Could you please check the above sample and get back to us if you need further assistance on this? 
 
Regards, 
Saranya D 



UA Umair Ahmed January 7, 2020 01:06 AM UTC

Thanks, that seems to be working but when the size of the file is a little bigger, may be greater then 100 KB it doesn't seems to load that in the angular front end, do we need register the key for client side as well ?


SD Saranya Dhayalan Syncfusion Team January 7, 2020 06:53 AM UTC

Hi Umair, 
  
Thanks for your update. 
  
Query #1: do we need register the key for client side as well? 
  
We would like to inform you that we have created a variable(customArgs), only to store the data sent by the server in the client side. 
  
Query #2: but when the size of the file is a little bigger, may be greater then 100 KB it doesn't seems to load 
  
Before we start to work with this, we would like to know the following details, 
  
1.       Could you please let us know what do you mean by 100 KB? 
2.       If the 100 KB file is an excel file, which is not loaded in the spreadsheet control, please share the issue reproducible excel file. 
  
Could you please get back to us with the above details? So that we can sort out and provide you the better solution quickly. 
  
Regards, 
Saranya D 



UA Umair Ahmed January 7, 2020 07:01 PM UTC

Sorry for the confusion, what I meant was if the excel file size is more than 100 KB it doesn't loads in the spreadsheet control in Angular, is there a file size limit ? this has nothing to do with the custom value issue, please advise.

Thanks.


SD Saranya Dhayalan Syncfusion Team January 8, 2020 10:28 AM UTC

Hi Umair, 
  
Thanks for your update. 
  
We would like to let you know that the loading time for excel will not depends on its size, it will depend on the number of data containing cells, cell formatting and number formatting applied cells, number of cells contains formula. 
  
We have checked with excel file having more than 100 KB and it is loading fine in our end. Could you please share a dummy excel file which reproduce the reported issue? So that we can sort out and provide you the better solution quickly. Please find the below sample link. 
  
  
Regards, 
Saranya D 



UA Umair Ahmed January 8, 2020 09:48 PM UTC

Please see the attached file, its not loading, throws some error, here is the code.


        public IActionResult TestOpen(IFormFile formFile)
        {
            //ExcelEngine excelEngine = new ExcelEngine();
            //IWorkbook workbook;
            //Stream memStream = (openRequest.Files[0] as IFormFile).OpenReadStream();
            //workbook = excelEngine.Excel.Workbooks.Open(memStream, ExcelOpenType.Automatic);
            //MemoryStream outputStream = new MemoryStream();
            //workbook.SaveAs(outputStream);
            //IFormFile formFile = new FormFile(outputStream, 0, outputStream.Length, "", openRequest.Files[0].FileName); // converting MemoryStream to IFormFile
            //outputStream.Close();
            OpenRequest open = new OpenRequest();
            open.File = formFile;
            var content = Workbook.Open(open);
            return Content(Workbook.Open((open)));
        }

Attachment: sample_7dc92d22.zip


SD Saranya Dhayalan Syncfusion Team January 9, 2020 12:23 PM UTC

Hi Umair, 
  
Thanks for your update. 
  
We have checked your issue, we are able to reproduce the reported issue in our end. We have confirmed that the issue is defect and logged a defect report for the same. The fix for this issue is estimated to be available on Volume 4 SP1 release, which is expected to be available by end of January month 2020 and appreciate your patience until then. You can track the same through the below feedback link. 
  
  
Regards, 
Saranya D 



UA Umair Ahmed March 14, 2020 12:17 AM UTC

Hi,

This issue was fixed in Volume 4 SP1 but the file takes almost 30 seconds to load on client, the size is only 165KB, is there a way to optimize this, I noticed that it created almost 16 thousand rows when there are only 85 rows on each sheet.

OpenRequest open = new OpenRequest(); open.File = formFile; var content = Workbook.Open(open);

Not sure why the size of contents grows to almost 10 MB, please try the sample file that we emailed earlier and let us know if there is a way to improve performance.

Thanks,
Umair


AD Arunkumar Devendiran Syncfusion Team March 16, 2020 12:28 PM UTC

Hi Umair Ahmed, 
 
We have checked your reported issue with your provided excel file and we would inform you that in your excel file styles applied to the entire row (44, 72). So that you are facing this kind of issue. We have removed these styles and attached the modified excel file. However, we have considered this performance issue and we need to validate on this issue, so we will update you with further details on March 18, 2020. Until then, we humbly request you to use the below excel file. 
 
Excel file: 
 
Regards, 
Arunkumar D 


Loader.
Up arrow icon