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.
Unfortunately, activation email could not send to your email. Please try again.

Data Validation List in spreadsheet not working

Thread ID:

Created:

Updated:

Platform:

Replies:

130264 May 3,2017 01:13 AM Dec 4,2017 03:59 AM JavaScript 8
loading
Tags: ejSpreadSheet
neeraj
Asked On May 3, 2017 06:40 AM

I am working with spreadsheet using javascript (ejSpreadsheet). and i want to make a list from sheet 1 to sheet2. but the formula is not working (=DataSheet1!$A$2:$A$18)this formula works good in ms excel.my code sample is this:        var xlObj = $("#Spreadsheet").data("ejSpreadsheet");        xlObj.sheetRename("DataSheet");        xlObj.XLCellType.addCellTypes('C1', { 'type': ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': "=DataSheet!$A$2:$A$18" },1);

Silambarasan I [Syncfusion]
Replied On May 3, 2017 07:39 AM

Hi Neeraj, 
 
Thank you for using Syncfusion products. 
 
We have checked the reported query “To make a list from sheet 1 to sheet2” and we would like to suggest you to use ‘dataSourceSheetIndex’ property to achieve your requirement.  Please refer the following modified code example. 
 
 
$("#Spreadsheet").ejSpreadsheet({ 
    //... 
    loadComplete: "onLoadComplete" 
}); 
 
function onLoadComplete(args) { 
    var xlObj = this; 
    if (!this.isImport) { 
        //... 
        xlObj.XLCellType.addCellTypes('C1', { 'type': ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': "A2:A18", 'dataSourceSheetIndex': 2 }, 1); 
    } 
} 
 
 
For your convenience, we have prepared a sample to demonstrate this and the same can be downloaded from the below link, 
 
Regards, 
Silambarasan 


neeraj
Replied On May 4, 2017 06:48 AM

Thank You Silambarasan. its working now.

neeraj
Replied On May 5, 2017 02:28 AM

Hi Silambarasan,my dropdown problem was solved. but i am facing a new issue when i select any option from list and post my sheet to server i am getting null value for dropdown

Silambarasan I [Syncfusion]
Replied On May 5, 2017 07:07 AM

Hi Neeraj, 
 
Thanks for your update. 
 
Before we provide solution, we would like to know the following details, 
 
1. In which way you are sending a sheet data to server (as JSON – using saveAsJSON() or XLExport.export() or manual AJAX POST) and where the dropdownlist getting null value? If possible, please share issue replication steps. 
 
2. Do you get a null value for a dropdown list option on exporting as excel file? Please share the details or code snippet if you did any customization in Spreadsheet (i.e. using client-side event). 
 
Could you please get back to us with the above information so we can analyze based on that and provide you a better solution? The information provided would be great help for us to proceed further. 
 
Meanwhile, we have prepared a sample to demonstrate ‘To get dropdown value in Spreadsheet’ and the same can be downloaded from the below sample link. Please refer the following code example. 
 
<button onclick="getDropDownValue()" value="getDDvalue">Get DropDown value</button> 
<div id="Spreadsheet"></div> 
 
 
$("#Spreadsheet").ejSpreadsheet({ 
    //... 
}); 
 
 
function getDropDownValue() { 
    var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), dropDownValue; 
    dropDownValue = xlObj.XLEdit.getPropertyValue(1, 0); // 1=> row index, 0 => column index 
     
} 
 
 
 
         
Regards, 
Silambarasan 


neeraj
Replied On May 9, 2017 05:16 AM

Hi Silambarasan,Thanks for your reply

My client side code is:

 $(function () {
        $("#Spreadsheet").ejSpreadsheet({
            allowWrap: true,
            sheets: [
                {
                    rangeSettings: [{ dataSource: sheet2DataSrc }],
                }],
            loadComplete: "onLoadComplete"
        });
    });
    function onLoadComplete(args) {
        console.log("sample", args);
        var xlObj = this;
        if (!this.isImport) {
            this.setWidthToColumns([200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200]);
            this.addNewSheet();
            this.XLEdit.updateValue("A1", "Scholarship Name");
            this.XLEdit.updateValue("B1", "Page Url");
            this.XLFormat.format({ "style": { "vertical-align": "middle", "text-align": "center", "font-weight": "bold" } }, "A1:C1");
            this.setWidthToColumns([200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200]);
            this.performSelection("A4");
            this.XLCellType.addCellTypes("C1", { 'type': ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': 'A2:A23', 'dataSourceSheetIndex': 1 }, 2);
            //this.XLCellType.addCellTypes("D1", { 'type': ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': 'A2:A23', 'dataSourceSheetIndex': 1 }, 2);
            //this.XLCellType.addCellTypes("E1", { 'type': ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': 'A2:A23', 'dataSourceSheetIndex': 1 }, 2);
            //this.XLCellType.addCellTypes("F1", { 'type': ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': 'A2:A23', 'dataSourceSheetIndex': 1 }, 2);
        }
        $('#saveSheet').on('click', function () {
            var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), exportProp = xlObj.XLExport.getExportProps(), filename = "Milga";
            $.ajax({
                type: "POST",
                data: { fileName: filename, sheetModel: exportProp.model, sheetData: exportProp.data },
                url: "/Milga/SaveFiletoDB",
                success: function (data) {
                    // Success code here.
                }
            });
        });
    }


and server side is:

 if (fileName.Length > 0)
                {
                    ExcelEngine excelEngine = new ExcelEngine();

                    IApplication application = excelEngine.Excel;
                    application.DefaultVersion = ExcelVersion.Excel2013;
                    Stream dataStream = Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013);
                    dataStream.Position = 0;
                    //Byte[] dataBytes = new BinaryReader(dataStream).ReadBytes(Convert.ToInt32(dataStream.Length));
                    IWorkbook workbook = excelEngine.Excel.Workbooks.Open(dataStream);
                    var rows = workbook.ActiveSheet.Rows;
                }

problem is in row cells i am getting all the data but only dropdown value on C1 i got null

Silambarasan I [Syncfusion]
Replied On May 9, 2017 05:21 AM

Hi Neeraj, 
 
Thanks for your update. 
 
We considered this as a defect and a new support incident has been created under your account to track the status of this requirement.  Please log on to our support website to check for further updates.  
 
 
Regards, 
Silambarasan 


Mark Lummus
Replied On December 1, 2017 12:18 PM

I had a similar problem.

where in the documentation is the 'dataSourceSheetIndex' described?

thanks,
Mark

Silambarasan I [Syncfusion]
Replied On December 4, 2017 03:59 AM

Hi Mark, 
 
Sorry for the inconvenience. Currently, the description for ‘dataSourceSheetIndex’ property in ‘XLCellType.addCellTypes()’ method is not available in our API documentation and we are working on that. We will update you once it added in online help documentation. 
 
Regards, 
Silambarasan 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;