XLSX Header And Footer Replacing Outputs An Inconsistent File in .NET

Aspose.Cells Product Family
1

I’m replacing tags in both header and footer in a XLSX document. For XLS, the following code works fine, but for the XLSX documents, I’m getting an inconsistent output file. I’m attaching the XLSX file for sample. My code is:


Workbook xls = new Workbook(pFileName);

foreach (DictionaryEntry item in htTagsRegs)

{

for (int i = 0; i < xls.Worksheets.Count; i++)

{

Aspose.Cells.PageSetup pageSetup = xls.Worksheets[i].PageSetup;

for (int j = 0; j < xls.Worksheets.Count; j++)

{

string header = pageSetup.GetHeader(j);

if (header != null && header != “”)

{

header = header.Replace(item.Key.ToString(), item.Value.ToString());

pageSetup.SetHeader(j, header);

}

string footer = pageSetup.GetFooter(j);

if (footer != null && footer != “”)

{

footer = footer.Replace(item.Key.ToString(), item.Value.ToString());

pageSetup.SetFooter(j, footer);

}

}

xls.Worksheets[i].Replace(item.Key.ToString(), item.Value.ToString());

}

}
2

Hi

Thanks for your request. I will move your request into Aspose.Cells forum. My colleagues from Aspose.Cells team will answer you shortly.

Best regards,

3

Hi,


Thank you for your source code and sample file.
I guess the sample file you have attached is the output. To investigate your said issue, we need your input file as well as complete source code. It would be of great help that you provide us a simple console application that could re-produce the error as described by you.

Right now, we tried a simpler version of your source code with latest release of Aspose.Cells for .NET and set the header/footer of your file [Excelxls.xlsx]. After re-saving the file we find both XLS and XLSX files consistent in this regard. My both output files are attached for your reference.
4

Well, that was my input file. I’m trying to replace the text which starts with “##”.

And the complete source code doesn’t affects the file after that. I’m just replacing the tags with a Hashtable. I wonder if the code I’ve provided is the best way to do the header/footer replacing. Do you think I can do it easier?
5

Hi,


Thank you for the elaboration.
By simpler source code, I meant without using the HashTable. Now I can reproduce the inconsistent results while saving the output to XLS and XLSX files with my below source code. So I have logged an Investigative Ticket to probe further into this matter. Ticket ID for your reference is CELLSNET-28200. We will keep you posted on this.

C# Code

Hashtable htTagsRegs = new Hashtable();

htTagsRegs.Add("##CodDoc", “CodDoc”);

htTagsRegs.Add("##NumRev", “NumRev”);

htTagsRegs.Add("##TitDoc", “TitDoc”);

htTagsRegs.Add("##DatCri", “DatCri”);

htTagsRegs.Add("##NmeApr", “NmeApr”);

htTagsRegs.Add("##TipCop", “TipCop”);

htTagsRegs.Add("##DatImp", “DatImp”);

htTagsRegs.Add("##SigUni", “SigUni”);


Workbook xls = new Workbook(“C:\temp\EXCELXLS.xlsx”);

foreach (DictionaryEntry item in htTagsRegs)

{

//Iterating over the worksheets

for (int i = 0; i < xls.Worksheets.Count; i++)

{

Aspose.Cells.PageSetup pageSetup = xls.Worksheets[i].PageSetup;

//Iterating over the 3 sets of Header / Footer

for (int j = 0; j < 3; j++)

{

string header = pageSetup.GetHeader(j);

if (header != null && header != “”)

{

header = header.Replace(item.Key.ToString(), item.Value.ToString());

pageSetup.SetHeader(j, header);

}

string footer = pageSetup.GetFooter(j);

if (footer != null && footer != “”)

{

footer = footer.Replace(item.Key.ToString(), item.Value.ToString());

pageSetup.SetFooter(j, footer);

}

}

xls.Worksheets[i].Replace(item.Key.ToString(), item.Value.ToString());

}


}

xls.Save(“C:\temp\out.xls”, SaveFormat.Excel97To2003);

xls.Save(“C:\temp\out.xlsx”, SaveFormat.Xlsx);

6

Hi,

We have fixed this issue. Please download Aspose.Cells for .NET v5.3.3.5.

7

Thank you very much!

8

mshakeel.faiz,


I’m sitll getting an inconsistent file as output with the latest version. I’m attaching the output file for your reference. Headers and footers are missing. My code is:
Workbook xls = new Workbook(pFileName);

foreach (DictionaryEntry item in htTagsRegs)

{

for (int i = 0; i < xls.Worksheets.Count; i++)

{

Aspose.Cells.PageSetup pageSetup = xls.Worksheets[i].PageSetup;

for (int j = 0; j < 3; j++)

{

string header = pageSetup.GetHeader(j);

if (header != null && header != “”)

{

header = header.Replace(item.Key.ToString(), item.Value.ToString());

pageSetup.SetHeader(j, header);

}

string footer = pageSetup.GetFooter(j);

if (footer != null && footer != “”)

{

footer = footer.Replace(item.Key.ToString(), item.Value.ToString());

pageSetup.SetFooter(j, footer);

}

}

xls.Worksheets[i].Replace(item.Key.ToString(), item.Value.ToString());

}

}

9

Hi,


I have tested your source code with latest version of Aspose.Cells for .NET v5.3.3.5 and your template file [ExcelXLS.xlsx]. I find no issue in Header or Footer of my both output files. They are attached for your reference.
10

Hello babar.raza,


Can you please try to reproduce with the following HashTable?

Hashtable hsTagsRegs = new Hashtable();

hsTagsRegs.Add("##DatImp", new DateTime(2011, 06, 15, 17, 05, 00));

hsTagsRegs.Add("##NmeElb", “Siteware”);

hsTagsRegs.Add("##DatApr", “”);

hsTagsRegs.Add("##SigUni", “SiteAir 2011”);

hsTagsRegs.Add("##TitDoc", “TESTE ROGGER”);

hsTagsRegs.Add("##NmeVrf", “”);

hsTagsRegs.Add("##DatVrf", “”);

hsTagsRegs.Add("##TipCop", “Copia Não Controlada”);

hsTagsRegs.Add("##DatCri", new DateTime(2011, 06, 14, 11, 10, 06));

hsTagsRegs.Add("##NumRev", “0”);

hsTagsRegs.Add("##NmeApr", “Siteware”);

hsTagsRegs.Add("##CodDoc", “AI_Site_Air2011_009”);
11

Hi Rogger,


Thanks for providing the HashTable values. Now I am able to re-produce the said problem, so I have re-opened the ticket associated with this thread. Also I have added your comments to it. We will sort this out very soon and let you know here.

12

Thank you very much babar.raza!

13

Hi Rogger,


Further investigation on your said issue revealed that in your output file the character limit for Header or Footer exceeds the limit provided by MS Excel. Excel Headers and Footers have limit to the number of 255 characters, Excel 2007 will reject an entry of more than 255 characters. As to this case, the Header’s character number is 297, so MS Excel 2007 drops the header and footer when you open the output document.
In order to bypass this limit, there are two methods:
  1. Reduce the character number to less or equal 255.
  2. Use picture to replace character. [Please see PageSetup Class for methods SetHeaderPicture and SetFooterPicture]
14

babar.raza,


Thank you for your investigation.