Group Columns

sj_elaine · January 28, 2009, 8:04pm

i am trying to do like what's like in the attached file.

I would like to group columns so that the report would look like the image at the bottom of the attached picture.

this is how my code looks like but nothing is happening when i run the application. the columns are not grouped.

Workbook excel = new Workbook();

reportpath = FMConfiguration.GetAppSetting("ExcelReportPath").ToString();

excel.Open(reportpath"_dummy" + ".xls");

Worksheet worksheet = excel.Worksheets[0];

//Make gridlines visible.

worksheet.IsGridlinesVisible = true;

//Freeze panes

worksheet.FreezePanes(3, 5, 3, 5);

---=====some other formatting codes====---

worksheet.Cells.GroupColumns(5, 6, true);

---====code for saving the file====---

the codes were taken from the original file and not based on the attached file..i just made the attached file in excel for a clear example of what i want to do.

Thanks :D

sj_elaine · January 28, 2009, 8:45pm

ok so the gorupcolumns is now working..i don't know why it didn't yesterday :D

but the problem is now is that Col A is shown when the group is collapsed. I would like to have Col C be displayed when collapsed.

How do i do that?

thanks

amjad.sahi · January 29, 2009, 12:57am

Hi,

I think you are not including the Col A (index 0) while grouping the columns so it is always displayed whether you expand or collapse.

Following is my testing code which works fine and attached is the output file.

Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Add some text
for (int i = 0; i < 33; i++)
{
for (int j = 0; j < 9; j++)
{
worksheet.Cells[i, j].PutValue("Dummy content" + i.ToString() +"," + j.ToString());

}
}
//Group columns
worksheet.Cells.GroupColumns(0, 1, true);
worksheet.Outline.SummaryColumnRight = true;

workbook.Save(@"f:\test\GroupingCol.xls");

I have also attached the latest fix for you so you may use it for your need.

If you still find any problem, kindly paste your sample code (as I did above) and post your excel file(s) here, we will check it soon.

Thank you.

sj_elaine · January 29, 2009, 2:27am

i already tried that..but the + sign is placed on the wrong column..please see attached..+ sign should be on H and K columns and not in E and H columns

by the way here's the code for grouping

//Column Groupings

worksheet.Cells.GroupColumns(5, 6, true);

worksheet.Cells.GroupColumns(8, 9, true);

thanks for your reply

amjad.sahi · January 29, 2009, 2:53am

Hi,

Well, the "+" signs are on H and K columns and it should be. I have checked and tried again using the following code and the output file (attached) is fine, kindly check it

Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Add some text
for (int i = 0; i < 33; i++)
{
for (int j = 0; j < 14; j++)
{
worksheet.Cells[i, j].PutValue("Dummy content" + i.ToString() +"," + j.ToString());

}
}
worksheet.FreezePanes(3, 5, 3, 5);
//Group columns
worksheet.Cells.GroupColumns(5, 6, true);
worksheet.Cells.GroupColumns(8, 9, true);

workbook.Save(@"f:\test\GroupingCol1.xls");

Which version of Aspose.Cells for .NET you are using, kindly try the attached version (4.6.0.9) in my previous reply.

Thank you.

sj_elaine · January 29, 2009, 3:45am

i already tried the attached version, i was using 4.6.0.8 then i used the 4.6.0.9 attached, but still encountering the same problem.

here's the rest of the code

Workbook excel = new Workbook();

reportpath = FMConfiguration.GetAppSetting("ExcelReportPath").ToString();

excel.Open(reportpath"_dummy" + ".xls");

Worksheet worksheet = excel.Worksheets[0];

//Make gridlines visible.

worksheet.IsGridlinesVisible = true;

//Freeze panes

worksheet.FreezePanes(3, 5, 3, 5);

//Unmerge/Merge part of the matrix to be able to add column names for the report.

Range range = excel.Worksheets[0].Cells.CreateRange("A2", "E3");

range.UnMerge();

Range range2 = excel.Worksheets[0].Cells.CreateRange("A2", "E2");

range2.Merge();

//Add and format value of the column titles.

worksheet.Cells["A3"].PutValue("Column 1");

worksheet.Cells["B3"].PutValue("Column 2");

worksheet.Cells["C3"].PutValue("Column 3");

worksheet.Cells["D3"].PutValue("Column 4");

worksheet.Cells["E3"].PutValue("Column 5");

Range range3 = excel.Worksheets[0].Cells.CreateRange("A3", "E3");

excel.Styles.Add();

Style style = excel.Styles[0];

style.Font.Name = "Verdana";

style.Font.Size = 8;

style.Font.IsBold = true;

range3.Style = style;

//To italized the Report Name/Title

Cell cell = worksheet.Cells["A1"];

cell.Characters(19, 200).Font.IsItalic = true;

cell.Style.VerticalAlignment = TextAlignmentType.Bottom;

//Column Groupings

worksheet.Cells.GroupColumns(5, 6, true);

worksheet.Cells.GroupColumns(8, 9, true);

worksheet.Cells.GroupColumns(11, 12, true);

worksheet.Cells.GroupColumns(14, 15, true);

worksheet.Cells.GroupColumns(17, 18, true);

worksheet.Cells.GroupColumns(20, 21, true);

-----=====Password Protection Code=====-----

thanks for your help

amjad.sahi · January 29, 2009, 3:55am

Hi,

Could you post your input + output excel file here, we will check it soon.

Thank you.

sj_elaine · January 29, 2009, 6:01am

i'm not sure what you meant by input + output. But what we are doing is a web application that generates report using .rdlc in asp.net. Generated automatically in excel format and using aspose to format the excel file. so the data to the excel report is coming from the database depending on what the user inputs in our web application.

nausherwan.aslam · January 29, 2009, 6:40am

Hi,

Thank you for considering Aspose.

Well, I have tested the grouping with your provided code and it works abs fine with the latest dll. I have attached my generated file. After formatting the auto generated contents in your excel file (using Aspose.Cells), please save your excel file and post it here. This will help us figure out the issue.

Thank You & Best Regards,

amjad.sahi · January 29, 2009, 7:50am

Hi,

But what we are doing is a web application that generates report using .rdlc in asp.net. Generated automatically in excel format and using aspose to format the excel file......

Could you provide us the generated excel report file based on the rdlc file, we would like to implement the grouping columns functionality to check if we can trace the issue.

Thank you.

sj_elaine · January 31, 2009, 5:24am

Hi Sorry for the late feedback..anyway i have attached here the output file we are getting..i removed the grouping code for this one

Thanks

nausherwan.aslam · January 31, 2009, 8:33am

Hi,

Thanks for sharing the output file. But your file is password protected. Please unprotect your file and share it again, so we can check your mentioned issue.

Thank You & Best Regards,

sj_elaine · January 31, 2009, 9:06am

Oppps sorry…try this

nausherwan.aslam · February 1, 2009, 3:02am

Hi,

Thank you for considering Aspose.

Well, the property worksheet.Outline.SummaryColumnRight is false in your template file, so please assign it true in your code. Please see the following sample code,

Sample Code:

Workbook excel = new Workbook();

excel.Open(@"F:\File Temp\Test+Grouped.xls");

Worksheet worksheet = excel.Worksheets[0];

worksheet.Cells.GroupColumns(5, 6, true);

worksheet.Cells.GroupColumns(8, 9, true);

worksheet.Outline.SummaryColumnRight = true;

excel.Save(@"F:\File Temp\test.xls");

Please do let us know if you still face any problem.

Thank You & Best Regards,