How to expand colums to print area

[azerios]

Dear,

I have an excel template with 12 columns for header with different widths.

I need to hide header column if all rows after my header row are empty, so my exported files could contains any titles between 1 and 12 titles.

I manage to do the hiding part by dynamically checking my conditions and let's say that I have columns A, B, C with titles.

How to expand my content to the column M (limit of my printing area) with autoMerge cell with the good individual width to allow all rows next to be display correctly if I have a 500 letters text to display in the colum C for example. Just to prevent all my columns to occupy only 20% of the width and not 100%.

Thanks for your help

[amjad-sahi]

@azerios ,

You may try to use grouping rows and columns feature or hiding rows/cols if it works for your needs.

If you still could not accomplish the task, please create your task in MS Excel manually for sample data, save the Excel file and provide us, we will check it on how to do it via Aspose.Cells APIs.

Moreover, we recommend you to kindly post your queries/issues in the dedicated forum. We handle clients queries efficiently in the forums.

[azerios]

Dear,

My company has strict network policies so it's impossible for me to go in your forum.

Please find attached a sample with at top a sample from my data with multiple possibilities in terms of titles count; at the bottom is my wished export with merged cells.

I set a printing area to note that occurences of titles row could be one or more in a single page.

Sample.xlsx

Regards

[amjad-sahi]

@azerios ,

Thanks for the sample file.

I checked your file. I am not entirely sure about your requirements. The content shows that you have three sets of data (min, random and mix data) with header row. It looks like you want to move certain data in the cells to other place and merge the destination cells (to make it one). There might not any automatic way but you have to manually perform the task based on your desired needs using Aspose.Cells APIs. For example, you may use Cell.StringValue to get the value from the cell and use Cell.PutValue to insert that value in other cell or move a range of cells to other place. To merge/unmerge cells, you may refer to the document with examples for reference.

If you still could not evaluate or accomplish the task by yourselves, kindly elaborate it more and please provide a real time example with relevant data intact. You may also demonstrate different sets of data display in different sheets in the file. This will help us to understand you better so we can assist you accordingly.

[azerios]

Dear,

Imagine a class with 5 fields as string, you need to export multiple list of these.

The first list will have all 5 fields with value, a second with only first/second/last fields, a third with only four/five.

All these list need to exported using a template, my current export contains all fields as Header and all rows have the correct field content in the right field header.

Now my users want to only export fields with value because they don't care about empty columns.

I manage to calculate for each list which columns I want to export 5 for the first list, 3 for the second list and two for the third one.

How to mix these differences to always take the full width of the file exported with Aspose.Cells ?

I mean an algorithm or a method to be sure that all width of the file is occupy.

Regards

[amjad-sahi]

@azerios ,

I requested you to provide a real time MS Excel (example) file containing all your data/fields. Also, provide your desired outputs for first, second and third lists. What is your actual output format type (XLSX, PDF, datatable, etc.)?

Also, could you elaborate on the following?

How to mix these differences to always take the full width of the file exported with Aspose.Cells ?

I mean an algorithm or a method to be sure that all width of the file is occupy

[azerios]

Dear,

Please find a sample with my 3 list and the current export and the wished export

Sample2.xlsx

My output is an excel file and I'll export in PDF after.

As xou can see in my sample between the row 2 and 6 all columns are occupy with merged cells where fields with values are exported and all empty headers are not present. Same for row 9 with only two fields with value so only two headers.

Regards

[amjad-sahi]

@azerios ,

Thanks for the sample file.

I have evaluated your sample file. It looks like you want to export specific ranges (list1, list2, list3) with your custom display/style (it should occupy all the space and should merge cells where appropriate) to PDF file format. Not sure if we could build a better logic for it except manually do every thing via Aspose.Cells APIs. Seeing your Current Export based on Data to Export, we are curious about your code and logic you already have built to generate such kind of layer with data. For example, how could you export data from A9:B10 in the format, e.g., G9:K10 automatically, you must have adjusted the data before exporting/saving to PDF. In short, we need your complete sample (runnable) code that exports your Date to Export to render as Current Export. We might take it as starting point and then try to update your code segment (if appropriate) to accommodate it to render as WishedExport. Please do the needful and provide your sample console app/program (source code without compilation errors) to get your current export for our reference. We will check it soon.

[azerios]

Dear,

I manage to extract my code that generate the export; I added the template; the code and the current export.

My actual code is an Handler of an API so you may need to inject the license file and setup all path correctly if you want to use it inside a console application.

Template - Copy.xlsx ExportForAspose.xlsx test.txt

The txt file can be renamed to .cs file without any issue.

Regards

[amjad-sahi]

@azerios,

Thanks the sample code segment and files.

We evaluated your code snippet a bit. Could you please check the attached "Expected.xlsx" file. It is manually adjusted from your current resultant file. Expected.xlsx

Please confirm whether it is what you want to get/achieve? After your confirmation, we will devise or update your code a bit to shift the data to the correct position.

[azerios]

Dear,

Please find attached the wished export

Expected.xlsx

There are not any empty headers all columns are merged to take the full width of the file. My sample is with 5 headers but in my application I have 12.

Regards

[amjad-sahi]

@azerios,

Thanks for the expected file.

We have modified your sample code to accomplish your desired task (as per your expected file). Please find attached the updated sample code and try, it should create your expected results. Test.txt

[azerios]

Dear,

Thanks you so much for your code and the output created by it.

Is it possible to fix the border for merged cells because the color is only at left, top, bottom borders of the first cells and not others.

Also, do you have an idea how to block the merge width for the Header 5 ONLY if not all headers are present ? Like I want that this header keep 2 merged columns at minimum and others take all other columns. It's the case for the regions B and C, so mybe for the region B the Header2 take 2 columns and not 3, same for the region C where the Header4 is merged with a reduced number of columns.

Regards

[amjad-sahi]

@azerios,

We will evaluate your new requirements and get back to you soon.

[amjad-sahi]

@azerios,

We evaluated your custom requirements. For your requirement about the border, you may try to add sample code snippet like following:

                //.......
                for (int i = 0; i < 5; i++)
                {
                    var cell = sheet.Cells[headerRow - 1, i];
                    if (cell.Type == CellValueType.IsNull)
                    {
                        var style = cell.GetStyle();
                        style.SetBorder(BorderType.LeftBorder, CellBorderType.Thin,
                            Color.White);
                        style.SetBorder(BorderType.TopBorder, CellBorderType.Thin,
                            Color.White);
                        cell.SetStyle(style);
                    }
                }

after filling data to columns A-E. For example, before the line:

sheet.Cells.SetRowHeight(headerRow - 1, 120);

For your second requirement, it is a bit complicated and we are afraid you have to find your own (custom) logic according to your needs. For example, because you cannot know whether there are one or more blank columns until processing the last column, maybe you need two steps, i.e., first one is to check data and gather the blank columns, in the second step, you fill and merge cells according to the gathered information.

[azerios]

Dear,

The while loop for borders is working well so thanks a lot for this.

I'll always keep one column for my requirements and will re-write the method I use to create all cells content to fit it better.

Regards