Hey,
is there any easy way to hide column or row by it's name? To me it's more convenient way to do it than using index. This is how I would like to use it: HideColumn("myColName").
Thanks.
Hi,
Thanks for using Aspose.Cells for .NET.
As a workaround, you can convert your Column Name into Column Index and then use your same code.
We have CellsHelper class, it has lot of useful and utilities methods that could simplify the user code.
You will use its CellsHelper.ColumnIndexToName to get the index of desired column.
For example, the following code will return 2 for Column C, please note unlike Ms-Excel, Aspose.Cells indices are 0-based. So first column will be 0, second column will be 1 and so on.
C#
//It will return the index 2 for column C
int colIndex = CellsHelper.ColumnIndexToName(“C”);
Thanks for quick reply.
When I use CellsHelper.ColumnIndexToName("MyColName"); I have this error "Invalid Column name."
When I use "B" it works. But I want to use "user given" column name, because later somebody may change column order on excel template and by using "MyColName" instead of "B" or index I don't need to change my code.
Second reason is I will hide several individual columns and when I remove first col then other col indexes are changed.
Thanks again.
Hi,
Thanks for your input.
Could you please provide me your source xls/xlsx file in which you have given some column some name, so that I could check your issue at my end and provide you a sample code.
Ok, here is example template. For example when you click column header of B, you can see in the Name Box given column name "LastName". This is my column reference name to use in code.
Hi,
Thanks for your sample file.
We have looked into your issue and found that your column is just another Range object. You can access any Named Ranges using Workbook.Worksheets.GetRangeByName() method.
Once, you will access your Range using its name, you can then used Range.FirstColumn property to get the column index of your named column.
Please use the following code to access your desired column index using its name.
int columnIndex = workbook.Worksheets.GetRangeByName(“LastName”).FirstColumn;
Please see the complete code below that loads your file and hides the column by finding its index using column name.
C#
Workbook workbook = new Workbook(“exampleTemplate.xls”);
Worksheet worksheet = workbook.Worksheets[0];
int columnIndex = workbook.Worksheets.GetRangeByName(“LastName”).FirstColumn;
worksheet.Cells.HideColumn(columnIndex);
workbook.Save(“output.xls”, SaveFormat.Excel97To2003);
Hi,
okey it is a range object. Thank you very much. This solves my problem and you saved my day!
-Aki
Hi,
We also recommend you to kindly see the document for your complete reference on Named Ranges:
Thank you.
- How to hide the row based on row number or row Index ?
- How to hide the row based on column name or column Index ?
I can successfully hide the column using below code but unable to do for row:
int columnIndex = CellsHelper.ColumnNameToIndex(“C”);
ws.Cells.HideColumn(columnIndex);
Please help
Please use the following code to hide row. Please also read the comments inside the code for more help.
C#
//Hide 4th rows - since index starts from 0, 3 means 4th row.
int rowIndex = 3;
ws.Cells.HideRow(rowIndex);Thank you so much for the reply.
The suggested code is not working. Please note that I am using Aspose cells version 7.0.4.0 and the Runtime version is v2.0.50727
I have already tried below line of codes to hide the row, but non of them if affecting:
//int rowIndex = CellsHelper.RowNameToIndex("C24");
//ws.Cells.HideRow(rowIndex);
//ws.Cells.HideRow(30);
//AutoFitterOptions opt = new AutoFitterOptions();
//opt.IgnoreHidden = true;
//ws.AutoFitRows(opt);
//ws.Cells.HideRow(rowIndex);
//Row row = ws.Cells.Rows[24];
//row.IsHidden = true;
Kindly let me know what else I can do to achieve it.
Thanks for using Aspose APIs.
We found error in this line.
int rowIndex = CellsHelper.RowNameToIndex(“C24”);
It should be
int rowIndex = CellsHelper.RowNameToIndex(“24”);
And it will fix this issue. Please see the following sample code, its output Excel file and its screenshot for more help.
Download Link:
Output Excel File.zip (5.7 KB)
C#
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
ws.Cells["C22"].PutValue("Row 22");
ws.Cells["C23"].PutValue("Row 23");
ws.Cells["C24"].PutValue("Row 24");
ws.Cells["C25"].PutValue("Row 25");
ws.Cells["C26"].PutValue("Row 26");
int rowIndex = CellsHelper.RowNameToIndex("24");
ws.Cells.HideRow(rowIndex);
AutoFitterOptions opt = new AutoFitterOptions();
opt.IgnoreHidden = true;
ws.AutoFitRows(opt);
ws.Cells.HideRow(rowIndex);
wb.Save("output.xlsx");
Screenshot:
Hi shakeel.faiz,
Thank you so much for the reply.
I tried your suggested code but still it is not affecting in the excel file.
Please see the whole code below, am I missing something ? Kindly let me know what is wrong in my code.
I tried all the possible ways to achieve my task but nothing works.
Please help me out.
public void EmptyReportTemplate(Dictionary<string, object> dictDatasource, string strReportName, CELLS.Workbook oReportWorkbook, bool blnAddToSubmissionYear = true)
{
CELLS.Workbook oTempWorkbook = null;
int iSubmissionYear = -1;
iSubmissionYear = _SubmissionYear;
if (blnAddToSubmissionYear)
{
for (int i = 0; i < 24; i++) { dictDatasource.Add(("NextYear" + (i + 1).ToString()), (iSubmissionYear + (i + 1)).ToString()); }
}
else
{
for (int i = 0; i < 24; i++) { dictDatasource.Add(("NextYear" + (i + 1).ToString()), (iSubmissionYear + i).ToString()); }
}
oTempWorkbook = new CELLS.Workbook(_Path + TEMPLATE_FOLDER + strReportName);
foreach (CELLS.Worksheet ws in oTempWorkbook.Worksheets)
{
#Hide Row Code
//--not working
int rowIndex = CellsHelper.RowNameToIndex("24");
ws.Cells.HideRow(rowIndex);
AutoFitterOptions opt = new AutoFitterOptions();
opt.IgnoreHidden = true;
ws.AutoFitRows(opt);
//-- successfully working, 'D' column is hidden in excel
Column column = ws.Cells.Columns[3];
column.IsHidden = true;
//ws.Protect(ProtectionType.All, "TEST", "");
#endregion
oReportWorkbook.Worksheets.Add(ws.Name);
oReportWorkbook.Worksheets[ws.Name].Copy(ws);
}
ProcessWorkBook(dictDatasource, oReportWorkbook, false);
}Please first try the code segment shared by Shakeel Faiz in his previous reply if it works fine or not? If it does not work fine with your older version, then I guess, as you are using older version of the product (e.g v7.0.4.x), so it might be an issue with the product you are using. We are very sorry but we cannot evaluate your issue in older version, nor we can fix any issue (if found) in older versions. The fixes are only based on latest APIs set. We recommend kindly upgrade to and use latest version of the product (i.e., Aspose.Cells for .NET v18.1.x), it should work fine.
If you still find the issue with latest version/fix v18.1.x for your scenario/case, kindly do create a simple standalone console application (runnable), zip the project and post us here (excluding Aspose.Cells assembly) to reproduce the issue on our end, we will check and help you soon. Please make sure that your project should be executable so you should make it simple and remove any inter-dependencies, external references, or other objects, etc., so we could run it to reproduce the issue on our end.
Hi Team,
I have used below lined of code to hide column.But somehow once i open file i am able to unhide it easily…How can we restrict the same? Or am i missing anything??
Pl help asap if possible
int lent = sheet.Cells.MaxDataColumn + 2;
string rowIndex = CellsHelper.ColumnIndexToName(lent);
Cell validcell = sheet.Cells[rowIndex + 1];
validcell.PutValue("GeneratedFileTrue");
sheet.Cells.HideColumn(lent);Please elaborate your issue in details with sample console application (runnable) and template file. You may zip the project (with its resource files) and attach it here (you may exclude Aspose.Cells.Dll to minimize the size), we will check your issue soon. Also, attach some screenshots to demonstrate the issue in MS Excel manually, this will help us really to evaluate your issue precisely to consequently figure it out soon.