I have a worksheet with some template name as Sheet1.
I need to write each employee details in sheet by taking Worksheet.AddCopy(1).
But if hide a of the row of second employee by using – worksheet1.Cells.HideRow(rowIndex); then it hide the same for the first employee as well. But I need that row for first employee and not for second employee.
using FileStream fstream = new(“Filepath”, FileMode.Open);
using (Workbook workbook = new(fstream))
{
Worksheet worksheet0 = workbook.Worksheets[0];
Worksheet worksheet1 = workbook.Worksheets[1];
if (somecondition)
{
bonusCell=workbook.Worksheets.GetRangeByName("Bonus");
bonusCell.PutValue("1000");
}
workbook.Worksheets.AddCopy(1);
int rowIndex = workbook.Worksheets.GetRangeByName("Bonus").FirstRow;
worksheet1.Cells.HideRow(rowIndex);
}
But the row for bonus 1000 for is also not showing…
This Topic is created by amjad.sahi using Email to Topic tool.
@amjad_sahi Sorry, I can’t share the code or template.
My issue is a straight forward. I wrote data to excel and I took the copy of the sheet using workbook.Worksheets.AddCopy(1) in order to write second employee details. then I wrote details of the second employee as well. Then If I hide any row for the second employee, its hide the same row of the employee as well. But I need to show that row for the first employee
Since you did not share your sample files and code, so I have to create a sample (input) Excel file (attached in the zipped archive) to evaluate/test your scenario/case. I used the following sample code with input Excel file to generate an output file (also attached in the zipped archive). The output Excel file is fine tuned.
e.g. Sample code:
Please note, in the template (input) Excel file, I created a Workbook level named range “Bonus” on A2:A23 of the first worksheet. In code segment, I added a copy for the second sheet “Sheet2” to get “Sheet4” in the final workbook. Only 20th row of the second sheet “Sheet2” is hidden, it did not add any other row in any other worksheet what so ever. Everything is fine tuned.
I am using latest version of the APIs, so you are recommended to use latest version of Aspose.Cells for .NET. In case, you still find any issue, please create a sample file with dummy data and share complete (runnable) sample code to reproduce the issue (in the output Excel file) on our end, we will check it soon.
Here is my sample code, sample template and sample output in pdf
using FileStream fstream = new(“C:\Users\acm\source\repos\pdfgenerator\Aspose\testBonus.xlsx”, FileMode.Open);
using (Workbook workbook = new(fstream))
{
Worksheet sheet0 = workbook.Worksheets[0];
Worksheet sheet1 = workbook.Worksheets[1];
string name = "";
int bonus = 0;
for (int i = 3; i > 0; i--)
{
name = i==1?"Employee1":i==2?"Employee2":i==3?"Employee3":"";
bonus= i == 1 ? 5000 : i == 2 ? 0 : i == 3 ? 0 : 0;
Aspose.Cells.Range nameCell = workbook.Worksheets.GetRangeByName("name");
Aspose.Cells.Range bonusAllowedCell = workbook.Worksheets.GetRangeByName("isBonusAllowed");
Aspose.Cells.Range bonusAmountCell = workbook.Worksheets.GetRangeByName("bonusAmount");
nameCell.PutValue(name, false, false);
if(bonus!=0)
{
bonusAllowedCell.PutValue("yes", false, false);
bonusAmountCell.PutValue(bonus.ToString(), false, false);
}
else
{
bonusAllowedCell.PutValue("no", false, false);
int rowIndex = workbook.Worksheets.GetRangeByName("bonusAmount").FirstRow;
sheet1.Cells.HideRow(rowIndex);
}
if (i > 1)
{
workbook.Worksheets.AddCopy(1);
}
}
workbook.Save("C:\\Users\\acm\\source\\repos\\pdfgenerator\\Aspose\\output.pdf", Aspose.Cells.SaveFormat.Pdf);
}
==================================================================
Explanation
I have 3 employee named Employee1, Employee2, Employee3
Employee1 has bonus amount 5000 which is not getting printed in the output pdf.
Employee 2 and Employee 3 has no bonus, So I hide that particular row for Employee2 and Employee3
Moreover it should display in the order Employee1, Employee2 and Employee3 instead of Employee1, Employee3 and Employee2
Thanks for the template Excel file and sample code segment.
I found you have defined some Workbook (scope) level named ranges upon B4, B5 and B6 cells in the second worksheet. So, when you add copy of this worksheet, these workbook scoped named ranges become worksheet level named ranges in the (new) copied sheet(s). This is MS Excel’s behavior as you may confirm this by performing the task manually. Please note, in a workbook, you cannot have two workbook level named ranges with same name. In short, your code segment will not work properly as you are always retrieving workbook level named ranges and not the worksheet specific (scoped) named ranges from the copied sheet(s):
I simply used the following sample code by directly specifying cells in relevant (copied) worksheets and it works fine and as expected:
e.g. Sample code:
using FileStream fstream = new("g:\\test2\\testBonus.xlsx", FileMode.Open);
using (Workbook workbook = new(fstream))
{
Worksheet sheet0 = workbook.Worksheets[0];
Worksheet sheet1 = workbook.Worksheets[1];
//Add two copies first.
workbook.Worksheets.AddCopy(1);
workbook.Worksheets.AddCopy(1);
string name = "";
int bonus = 0;
for (int i = 3; i > 0; i--)
{
name = i==1?"Employee1":i==2?"Employee2":i==3?"Employee3":"";
bonus= i == 1 ? 5000 : i == 2 ? 0 : i == 3 ? 0 : 0;
Aspose.Cells.Cell nameCell = workbook.Worksheets[i].Cells["B4"];
Aspose.Cells.Cell bonusAllowedCell = workbook.Worksheets[i].Cells["B5"];
Aspose.Cells.Cell bonusAmountCell = workbook.Worksheets[i].Cells["B6"];
nameCell.PutValue(name, false, false);
if(bonus!=0)
{
bonusAllowedCell.PutValue("yes", false, false);
bonusAmountCell.PutValue(bonus.ToString(), false, false);
}
else
{
bonusAllowedCell.PutValue("no", false, false);
int rowIndex = workbook.Worksheets.GetRangeByName("bonusAmount").FirstRow;
workbook.Worksheets[i].Cells.HideRow(rowIndex);
}
}
workbook.Save("g:\\test2\\out1.pdf", Aspose.Cells.SaveFormat.Pdf);
}
Is there any other options for this? Because I can’t hardcode the cell like B4,B5,B6. That’s why I used named ranges…
If I use like B4,B5,B6 then in the future if I need to add one row in between B5 and B6 then I need to made changes in the code level, which is not a good coding practice.
So I hope there will be some other options.
Looks forwards to hearing from you…
thanks…
I think you may define or make use of worksheet (scoped) level named ranges instead of Workbook level named ranges in the template Excel file. Then, you have to update/write your code segment accordingly. See the document on how to create worksheet scoped named ranges for your reference. https://docs.aspose.com/cells/net/create-workbook-and-worksheet-scoped-named-ranges/