Hidden Row/Column error in server excel export

[Geoffrey]

When using this code from the server import/export demo, hidden rows get expanded to the default width, while using the frontend excel export preserves the row height

[HttpPost]
        public ActionResult Download(string data, string extension)
        {
            var workbook = Workbook.FromJson(data);
            using (var stream = new MemoryStream())
            {
                workbook.Save(stream, extension);

                var mimeType = Telerik.Web.Spreadsheet.MimeTypes.ByExtension[extension];
                return File(stream.ToArray(), mimeType, "Exported" + extension);
            }
        }

[Joana]

Hello Geoffrey,

For exporting only the visible columns of the Spreadsheet, you should add an event handler to the excelExport event and programmatically remove the columns that you'd like to export. You could find an example in the following forum post which is applicable to the Spreadsheet widget: 

http://www.telerik.com/forums/grid-export-columns-without-hiding#bJJD2xnvakmopnTg9YK7Cw

Another possibility would be to delete the row/columns during export and after export is completed insert them back and populate the values. For this approach, you could check out the following methods in the SpreadSheet API Documentation:

http://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/sheet#methods-deleteColumn

http://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/sheet#methods-insertColumn

http://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/range#methods-value

Regards,
Joana
Progress Telerik

[Geoffrey]

Our users might have a need for information in hidden rows, such as formulas,

what I ended up doing was storing the indexes of hidden rows & cols from the json, that is to say rows/columns with 0 height/width, 

using Telerik.Web.Spreadsheet.Workbook.FromJson, then using that object'sToDocument() function to get a Telerik.Windows.Documents.Spreadsheet.Model.Workbook object, where I could then use those indexes set true the Hidden attribute on those rows and columns.  This is in part because Kendo spreadsheet loses whitespace before text that TDP library does not, and our desire to move to server side processing of these spreadsheets for excel and pdf export

Deleting rows or columns might have destroyed hidden data by our users that factored into formulas

var workbook = Workbook.FromJson(KendoJson)
var serverWorkbook = workbook.ToDocument();

foreach(var hiddenRow in hiddenRows){
 serverWorkbook.ActiveWorksheet.Rows[hiddenRow].SetHidden(true);
}

using(var stream = new MemoryStream()){
new XlsxFormatProvider().Export(serverWorkbook, stream);
return new File(stream.ToArray(), ExcelMimeType, "workbook.xlsx")

[Joana]

Hi Geoffrey,

I am glad that you have managed to achieve the desired functionality. 

You could reopen the forum thread in case you have further questions.

Regards,
Joana
Progress Telerik