This is a migrated thread and some comments may be shown as answers.

Hidden Row/Column error in server excel export

3 Answers 48 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Geoffrey
Top achievements
Rank 1
Geoffrey asked on 27 Sep 2017, 06:15 PM

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);
            }
        }

3 Answers, 1 is accepted

Sort by
0
Joana
Telerik team
answered on 29 Sep 2017, 08:41 AM
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
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Geoffrey
Top achievements
Rank 1
answered on 29 Sep 2017, 01:55 PM

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")
0
Joana
Telerik team
answered on 02 Oct 2017, 03:14 PM
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
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Tags
Spreadsheet
Asked by
Geoffrey
Top achievements
Rank 1
Answers by
Joana
Telerik team
Geoffrey
Top achievements
Rank 1
Share this question
or