kendo-excelexport component. change visibility of column inside excel

[Denys]

I use next simple code for creating excel for export.

<kendo-excelexport #export [data]="data" [fileName]="fileName"> 
<ng-container *ngFor="let column of columns"> 
    <kendo-excelexport-column [field]="column.datasource" [locked]="column.locked" [hidden]="column.hidden" [title]="column.title"          [width]="column.width"> 
</kendo-excelexport-column>
 </ng-container> 
</kendo-excelexport> 

Currently, hidden means remove (exclude) column from excel (actually don't add it).
My goal of the hidden property was - save the column inside excel, but make it visible none for the user. I mean, I want to set visible false for the first column in excel, and the user will see other columns. But when I read the excel file, I see all columns including visible false.
For example, we have columns Id, Name, Age. In excel we have default ordering of columns A(for Id column), B(for Name column), C(for Age column). After export users see inside the excel file only B, C columns. Column A visible false. When I read the file, I should see A, B, C columns. - This is my goal.
Is it possible (maybe with other property, or other libraries)?

 

[Martin]

Hi Denys,

Thank you for the provided code snippet.

Iterate the columns configuration object directly in <kendo-excelexport-column> component:

      <kendo-grid-excel fileName="Products.xlsx">
        <kendo-excelexport-column
          *ngFor="let column of columnsConfig"
          [field]="column.field"
          [title]="column.title"
          [hidden]="column.hidden"
        >
        </kendo-excelexport-column>

Here is an example:

https://stackblitz.com/edit/angular-xycxmh-afifel?file=app/app.component.ts

I hope this helps.-

Regards,
Martin
Progress Telerik

[Denys]

Hi. Thanks for answer. Generally, I used code, that was provided by you, but it does not cover my goal.

I don't know, my next goal is possible or isn't?

I will try explain using you code:
We have ID, Name, Category columns. After export excel we got ID, Category (Name hidden).
Currently, Name in excel actually removed. My goal - hide it only.

In excel, we have standard column names: A, B, C ...
I want export excel with columns A, C, .... (Column B, where Name is located, - hidden).
User open excel - see columns ID, Category.

But, when we read excel in code, I see all columns - ID, Name, Category.
For read excel I use xlsx library.  (import XLSX from 'xlsx')

[Svet]

Hi Denys,

If the requirement is to export a hidden column in the Excel file, which can be shown when needed in Excel as demonstrated in the following article:

https://www.excel-easy.com/examples/hide-columns-rows.html

then this isn't possible to be achieved. The limitation is caused by the Microsoft Excel WorkBook api: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook

which is used in order to export the files to Excel. The exported columns can be manipulated only with the available properties listed for the them which don't allow hiding a column:

https://www.telerik.com/kendo-angular-ui/components/excelexport/api/WorkbookSheetColumn/

Please let us know in case any further information is required for this or any other Kendo UI for Angular related topic. Thank you.

Regards,
Svetlin
Progress Telerik