Hi,
I want to hide cells between 2 cells. Eg, between B41 - B44.
Also, is there any way; once the sheet is protected, no one can unhide the rows?
I've already seen the example which demonstrates hiding rows based on cells. But I want to hide the entire range.
Thanks, Vinay
Hi,
Well, you need to hide those two rows b/w B41 and B44. For protection, you can protect the sheet utilizing some advanced options for your need. See the following sample code:
Workbook excel = new Workbook();
excel.Open(“e:\test\Book1.xls”);
Worksheet worksheet = excel.Worksheets[0];
//Hide the rows 42nd and 43rd in B Column
for(int i = 41;i<43;i++)
{
worksheet.Cells.Rows[i].IsHidden = true;
}
//Allowing users to edit contents of the worksheet
worksheet.Protection.IsEditingContentsAllowed = false;
//Allowing users to edit objects of the worksheet
worksheet.Protection.IsEditingObjectsAllowed = false;
//Allowing users to edit scenarios of the worksheet
worksheet.Protection.IsEditingScenariosAllowed = false;
//Disallowing users to format rows of the worksheet
//It will include hiding/unhiding, set heights and set formatting style for
//the rows etc.
worksheet.Protection.IsFormattingRowsAllowed = false;
//Protecting the worksheet with a password
worksheet.Protect(ProtectionType.All, “amjad”, null);
//Saving the modified Excel file Excel XP format
excel.Save(“e:\test\outbook1.xls”);
For further reference, kindly see the documents:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/hidingunhiding-rows-and-columns.html
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/advanced-protection-settings-since-excel-xp.html
Thank you.
okay, couple of more questions:
1. I am generating columns on the sheet dynamically. Is it possible to close the column/row progression there itself. What I mean is; excel sheet can keep showing columns as long as I scroll in right direction. What I want is; I don't want to see any emptly columns/rows after the last column and row which I've generated/populated.
2. There have been instances when the end use is able to change the sequence of the sheets on the excel sheet. How to prevent user from doing that?
dvd.vinay:
okay, couple of more questions:
1. I am generating columns on the sheet dynamically. Is it possible to close the column/row progression there itself. What I mean is; excel sheet can keep showing columns as long as I scroll in right direction. What I want is; I don't want to see any emptly columns/rows after the last column and row which I've generated/populated.
2. There have been instances when the end use is able to change the sequence of the sheets on the excel sheet. How to prevent user from doing that?
Also, It looks like a very bad practice of iterating through rows once by one to hide it. Excel sheet itself supports selecting a range of columns and rows together and hiding them. Why is this not supported in Aspose?
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Thank you for the feedback.
1. I am generating columns on the sheet dynamically. Is it possible to close the column/row progression there itself. What I mean is; excel sheet can keep showing columns as long as I scroll in right direction. What I want is; I don't want to see any emptly columns/rows after the last column and row which I've generated/populated.
Well, I don’t think it is possible in MS Excel either to restrict the column / row view. If you know a way, please create a sample file in MS Excel and post it here. We will check it soon.
2. There have been instances when the end use is able to change the sequence of the sheets on the excel sheet. How to prevent user from doing that?
Well, you may try Workbook protection as it will restrict the users to not change the tab sequence. Please see the following link in this regard.
Also, It looks like a very bad practice of iterating through rows once by one to hide it. Excel sheet itself supports selecting a range of columns and rows together and hiding them. Why is this not supported in Aspose?
We have registered your requested feature in our internal issue tracking system with issue id: CELLSNET-15802. We will look into it and get back to you soon.
Thank You & Best Regards,
Hi,
Thank you for the feedback.
1. I am generating columns on the sheet dynamically. Is it possible to close the column/row progression there itself. What I mean is; excel sheet can keep showing columns as long as I scroll in right direction. What I want is; I don't want to see any emptly columns/rows after the last column and row which I've generated/populated.Well, I don’t think it is possible in MS Excel either to restrict the column / row view. If you know a way, please create a sample file in MS Excel and post it here. We will check it soon.
Well that's very simple. Select the column from where u want to stop the column progression. Then press control and right arrow. This will select all the remaining empty columns on the sheet. Now go to sheet-->format-->columns--> hide.
The only problem while following this approach in Aspose has been... while hiding rows one has to loop more than 60000 times to hide the blank rows (since I think there are max 256 columns and more than 60000 rows on the sheet). So if you could expose any method which could do this in an efficient way in one go; that will be great.
Thanks
2. There have been instances when the end use is able to change the sequence of the sheets on the excel sheet. How to prevent user from doing that?
Well, you may try Workbook protection as it will restrict the users to not change the tab sequence. Please see the following link in this regard.
Also, It looks like a very bad practice of iterating through rows once by one to hide it. Excel sheet itself supports selecting a range of columns and rows together and hiding them. Why is this not supported in Aspose?
We have registered your requested feature in our internal issue tracking system with issue id: CELLSNET-15802. We will look into it and get back to you soon.
Thank You & Best Regards,
Please see the sample sheet attached my embedded reply in above post. If possible, give us this feature as soon as possible.
Hi,
Thank you for the details. We will look into your required feature and get back to you soon.
Thank You & Best Regards,
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Also, the better approach to hide rows/columns in bulk is to set Cells. StandardWidth and Cells. StandardHeight.<?xml:namespace prefix = u1 /> This way we will not create many rows or columns objects in the memory and it will improve performance.
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells.StandardWidth = 0;
cells.StandardHeight = 0;
for (int i = 0; i < 6; i++)
{
cells.SetColumnWidth(i, 8.43);
}
for (int i = 0; i < 17; i++)
{
cells.SetRowHeight(i, 12.75);
}
workbook.Save(@"F:\FileTemp\dest.xlsx");
Also, we will look into your requested feature of hiding multiple rows & columns.
Thank You & Best Regards,
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Please try the attached latest version of Aspose.Cells. We have supported Cells.HideRows and Cells.HideColumns Apis.
Thank You & Best Regards,