Excel export not working with hidden columns

[rstrelau]

I have a need to hide certain columns in the GUI, but when a user chooses to export to csv or excel ALL of the columns need to be exported (including hidden ones).  An export to CSV works just fine, but Excel doesn't.

Excel won't open the workbook and I see this error in a log file when trying to open the .xls file:
XML ERROR in ExcelWorkbook description
REASON: Bad Value
FILE:   Test_Leads.xlsyan:downloads:test_leads.xls
GROUP:  Table
TAG:    Column
ATTRIB: Width
VALUE:  undefined

The raw .xls file opened in a regular text editor shows this snippet:
<Column ss:AutoFitWidth="1"  ss:Width="110" />
<Column ss:AutoFitWidth="1"  ss:Width="undefined" />

...and I can tell that the "undefined" column is one that's marked as hidden.  So it looks like the excel export doesn't grab the predefined column width for a hidden column.  Any suggestions? 

Thanks!

[paramvir]

Thanks for reporting this issue (unable to export hidden columns), it would be fixed in next version.

Meanwhile you can apply this workaround.

Code: [Select]

var CM = $("#grid_export").pqGrid('getColModel');
for(var i=0;i< CM.length; i++){
var column = CM[i];
if(column.hidden){
column._width = parseInt( column.width);
}
}
$("#grid_export").pqGrid("exportExcel", { url: "/pro/demos/excel", sheetName: "pqGrid sheet" });


[rstrelau]

Thanks for this.  I just applied the workaround and it works, but now I get a different error.  On a Windows machine I see the following error message when attempting to open the spreadsheet:

"The file format and extension of 'Test_export.xls' don't match.  The file could be corrupted or unsafe.  Unless you trust the source, don't open it.  Do you want to open it anyway?"

I open it anyway and it opens fine...why the format warning??  Didn't have this before we had hidden columns.

And on a Mac I can open the file just fine, but most of the columns are empty (13 of 14 actually).  Is there some kind of known bug for the excel export on a Mac (with Excel)?  I've actually always seen this...not specific to the addition of hidden columns.

thanks!
ryan

[paramvir]

1) The error message is displayed with xls extension because the format of the file is Excel xml. The error message would go away if you use xml extension.


2) Export to excel feature is designed mainly for Windows PCs with Excel installed, however if it works on Mac that would be nice. Is there any difference in format between displayed and non -displayed column on Mac. Do you get empty columns with Export to Excel demo too.

http://paramquery.com/pro/demos/export

[rstrelau]

1) So the Excel export works best with an .xml file extension?  Is there any plans to optimize this with the default Excel format .xlsx (which is in .xml format)?  It's a vastly better user experience to have an .xlsx extension as no regular user (and most advanced users for that matter) would think to open an .xml file in Excel.  That and warnings always scare users.  Highly recommend changing this.  We removed this function from our grid because of this.

2) No difference between display/non-displayed columns on a Mac for exports.  Mac in general simply doesn't work well for Excel exports.  CSV is fine.