Error when importing spreadsheet

[Georgios]

Hello,

I am trying to import a slightly more complicated spreadsheet which includes formulas, calculations etc but I get the following error :

Telerik.Windows.Documents.Spreadsheet.Utilities.LocalizableException: 'Format string is not in the correct format.'

I am using your demo as an example : https://demos.telerik.com/aspnet-mvc/spreadsheet/server-side-import-export .

Could you please help me solve this ?

Thanks,

Georgios

 

 

[Veselin Tsvetanov]

Hello Georgios,

May I ask you to send us the .xlsx file that causes the error observed? This way we will be able to test the behaviour of the Spreadsheet locally and to provide you with the most appropriate assistance for this case.

Regards,
Veselin Tsvetanov
Progress Telerik

[Georgios]

Hi Veselin,

Many thanks for your quick response to my inquiry.

Please find attached a small sample that we are trying to upload and it fails.

Kind Regards

Georgios Athanasakos

[Veselin Tsvetanov]

Hello Georgios,

Thank you for the file sent.

I have tested that one with the Server side import / export demo and I have noticed, that the error is present even if the Worksheet is empty. This leads to issue in the .xlsx file itself and not in its content. Therefore, I have created a blank xlsx file, where I have placed the same content as on the test4.xlsx file. I was able to successfully upload that file (attached) and its content was displayed properly in the Spreadsheet widget.

Previously, such xlsx file issues have been observed in cases when the file has been automatically generated (not created in Excel), or when it has been converted from an .xls file.

I would suggest you to test locally the attached file and let me know if you still face any issues.

Regards,
Veselin Tsvetanov
Progress Telerik

[Georgios]

Hi Veselin,

Many thanks for your reply, your file actually uploads fine but my question still remains as this was only a small sample extract from a big file.

What is the exact reason in the file itself (or content) that is causing the error ?

I have also tried to copy another excel content to a brand new excel file but I am still getting the same error.

Thanks,

Georgios

[Veselin Tsvetanov]

Hello Georgios,

The issue is caused by unrecognized date format, that has been embedded in the .xlsx file definition. Therefore, I would suggest you to review the Excel file for any custom date format definitions. Also, try to change the Regional settings of your machine to English (United States) (if they are not configured this way) and create a new file in Excel to test again. Do you experience the same issue as before, when trying to upload that file to the Kendo Spreadsheet?

Regards,
Veselin Tsvetanov
Progress Telerik

[Georgios]

Hi , we still have problem importing excel files. We have tried what you suggested but with no luck. Please find attached one more document for testing. Thanks.

[Veselin Tsvetanov]

Hi Georgios,

After careful review of the structure and the content of the .xslx files, we have isolated the cause for the observed error. As I already mentioned, it is a custom formatting, which is defined in the following way:

<numFmts count="1">
    <numFmt numFmtId="164" formatCode="dd\ mmm\ yy_);\(###0\);"-  ";" "@" ""/>
</numFmts>

You could find the above by unzipping the .xlsx file (xlsx is simply a zip archive with altered extension). The mark-up is placed in the styles.xml file (attached), which is located in the xl folder of the archive. Note that the attached styles.xml file is from  the test4.xlsx.

After altering the above lines to:

<numFmts count="1">
    <numFmt numFmtId="164" formatCode="dd\mmm\yy"/>
</numFmts>

and creating the .xlsx archive again (also attached and named fixed.xlsx), the file has been successfully manipulated on the server and loaded in the Spreadsheet.

Note, that the FinModelTest.xlsx contains 7 format entries and most of them are also corrupted. The FinModelTestFormatIssues.xml file shows them.

Regards,
Veselin Tsvetanov
Progress Telerik

[Neil]

I'm getting a slightly different error. In my example I'm storing off the spreadsheet as a byte array in a SQL server database. When the user wants to view the spreadsheet, I convert it back to a MemoryStream and pass back the content using the example from the MVC Import/Export example.

Nothing gets loaded in the Spreadsheet control, but I do get a javascript error(attached). I have also attached the excel spreadsheet in question.

[Veselin Tsvetanov]

Hello Neil,

I have tested the sample sent on our Server Import / Export demo, but I was not able to observe any errors while importing the sample file sent (video here). May I ask you to prepare and send us a runnable sample project, that reproduces the error observed? This way we will be able to troubleshoot the issue locally and to provide you with the most appropriate assistance for this case.

Regards,
Veselin Tsvetanov
Progress Telerik

[Neil]

Yeah, I don't have time to separate out code from my project. I told you what I'm doing. Storing the spreadsheet in a SQL server database and fetching the byte array, transposing it to a MemoryStream and using the code in the Import/Export example.Here's the code block that executes when I receive the Java script error.

DatasheetTemplate template = (from q in _db.Datasheets
                    join ds in _db.DatasheetTemplates on q.TemplateId equals ds.Id
                    where q.Id == datasheetid
                    select ds).FirstOrDefault();
                if (template != null)
                {
                    MemoryStream stream = new MemoryStream(template.BinaryData.ToArray());
                    var workbook = Workbook.Load(stream, Path.GetExtension(template.Name));
                    return Content(workbook.ToJson(), MimeTypes.JSON);
                }

[Neil]

After some more testing I can also get the example provided to work. Loading the spreadsheet control from the upload control directly. Here's my razor view as well. Please note that DatasheetViewModel currently only has 1 property in it (Id), which is an integer.

I get the java script error when I initially load the view, but the upload control successfully loads the import into the spreadsheet. I would like to remove the upload control completely and import directly from my copy stored in a varbinary(max) column in SQL Server.

@model SocketWorx.Site.ViewModels.DatasheetViewModel

@{
    ViewBag.Title = "Datasheet Mode";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<div class="body admin-body">
    <p>
        @Html.ValidationSummary(false, "", new { @class = "text-danger" })
    </p>
    <hr />
    <p>
        @(Html.Kendo().Spreadsheet()
            .Name("spreadsheet_Datasheet")
              .HtmlAttributes(new { style = "width:100%; height: 70vh" })
              .Toolbar(false)
              .Sheetsbar(true)
              .BindTo((IEnumerable<SpreadsheetSheet>)ViewBag.Sheets)
        )
        <br />
        @(Html.Kendo().Button()
            .Name("button_Save")
            .HtmlAttributes(new { type = "button" })
            .Content("Save")
            .Events(ev => ev.Click("Save_OnClick"))
        )
        @(Html.Kendo().Button()
            .Name("button_Cancel")
            .HtmlAttributes(new { type = "button" })
            .Content("Cancel")
            .Events(ev => ev.Click("Cancel_OnClick"))
              )
        <br />
        <br />
        <input type="file" name="file" id="upload" style="width:50%" />
    </p>
</div>


<script type="text/javascript">
    $(document).ready(function() {
        try {
            var spreadsheet = $("#spreadsheet_Datasheet").data("kendoSpreadsheet");
            var ALLOWED_EXTENSIONS = [".xlsx", ".csv", ".txt", ".json"];

            $.ajax({
                type: "POST",
                url: '@Url.Action("Get", "Datasheet")',
                contentType: "application/json; charset=utf-8",
                data: JSON.stringify({ datasheetid: @Model.Id }),
                dataType: "json",
                success: function (e) {
                    var spreadsheet = $("#spreadsheet_Datasheet").data("kendoSpreadsheet");
                    spreadsheet.fromJSON(e.response);
                },
                error: function (result) {
                    $(".validation-summary-valid").text(result.message);
                }
            });

            $("#upload").kendoUpload({
                async: {
                    saveUrl: "@Url.Action("Upload", "Datasheet")"
                },
                multiple: false,
                localization: {
                    "select": "Select file to import..."
                },
                select: function (e) {
                    var extension = e.files[0].extension.toLowerCase();
                    if (ALLOWED_EXTENSIONS.indexOf(extension) == -1) {
                        alert("Please, select a supported file format");
                        e.preventDefault();
                    }
                },
                success: function (e) {
                    // Load the converted document into the spreadsheet
                    spreadsheet.fromJSON(e.response);
                }
            });
        }
        catch (err) {
            $(".validation-summary-valid").text(err);
        }
    });

    function Save_OnClick() {
        $(".validation-summary-valid").text("");
        $("#button_Save").prop('disabled', true);
        $("#button_Cancel").prop('disabled', true);
    }

    function Cancel_OnClick() {
    }

</script>

[Veselin Tsvetanov]

Hi Neil,

As far as I can see, after retrieving the ByteArray data for the xlsx file from the database, you are returning that data as a JSON. This means that in the success handler of the jQuery.ajax() call you will get the workbook data as parameter. Therefore, that handler needs to be altered in the following way in order to properly load the JSON data:

success: function (data) {
    var spreadsheet = $("#spreadsheet_Datasheet").data("kendoSpreadsheet");
    spreadsheet.fromJSON(data);
},

Attached you will find a simple .NET MVC project implementing the discussed scenario. An xlsx file stored on the server is loaded in to a byte[], then it is transformed to a MemoryStreem, it is loaded to a Telerik.Web.Spreadsheet.Workbook and it is finally returned as a JSON to the client.

In case you still face any issues loading the content from the database, I would suggest you to modify the attached sample, so it reproduces the problem observed and send it back to us. Also, send us the returned JSON result from the Get AJAX call. This way we will be able to troubleshoot the problem locally and to provide you with the most appropriate assistance for this case.

Regards,
Veselin Tsvetanov
Progress Telerik