The Infolog

I got asked to find some missing labels in a report and when I counted them the report hit well over 300 labels. Rather than comparing by hand I built the below class to find the label Ids and then compare them. It allowed me to learn using Regular Expressions:

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using System.Drawing.ColorTranslator;
using System.IO.File;
class SRSLabelChecker
{

XmlDocument xmlDoc;
OfficeOpenXml.ExcelRange cells;
int currentRow;

public static void main (Args _args)
{
SRSLabelChecker labelChecker;

labelChecker = new SRSLabelChecker();
labelChecker.run();

}

public void run()
{

OfficeOpenXml.ExcelPackage excelPackage;
OfficeOpenXml.ExcelWorksheets worksheets;
OfficeOpenXml.ExcelWorksheet labelWorksheet;
OfficeOpenXml.ExcelRange cell;
MemoryStream stream;

//Create Excel document
stream = new MemoryStream();
excelPackage = new ExcelPackage(stream);
worksheets = excelPackage.get_Workbook().get_Worksheets();
labelWorksheet = worksheets.Add(“Labels”);
cells = labelWorksheet.get_Cells();
currentRow = 1;

// set header values

cell = cells.get_Item(currentRow, 1);
cell.get_Style().get_Font().set_Bold(true);
//cell.get_Style().get_Font().color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Grey); //not needed return
cell.set_Value(“Label ID”);

cell = cells.get_Item(currentRow, 2);
cell.get_Style().get_Font().set_Bold(true);
cell.set_Value(“English value”);

cell = cells.get_Item(currentRow, 3);
cell.get_Style().get_Font().set_Bold(true);
cell.set_Value(“German translation”);

this.getXML();

this.processFieldLabels();
this.processReportDesign();

//Finally save and publish the XML file to the user

excelPackage.Save();

file::SendFileToUser(stream, ‘MissingLabels.xlsx’);
}

private void getXML()
{
xmlDoc = XmlDocument::newFile(‘C:\\Temp\\report.xml’);
}

private void processFieldLabels()
{
XmlNodeList nodelList;
xMLNodeListIterator iterator;
XmlElement node;
XmlElement label;

nodelList = xmlDoc.getElementsByTagName(‘AxReportDataSetField’);

iterator = new XmlNodeListIterator(nodelList);

while (iterator.moreValues())
{
LabelId labelId;
str labelTxtDE, labelTxtUS;
XmlElement xmlEle;
node = iterator.value();

xmlEle = node.getNamedElement(‘Caption’);

if(xmlEle)
{
LabelId = xmlEle.text();
labelTxtDE = SysLabel::labelId2String2(LabelId,”de”);
labelTxtUS = SysLabel::labelId2String2(LabelId,”EN-US”);

if((LabelId == labelTxtDE) || (labelTxtDE == labelTxtUS))
{
OfficeOpenXml.ExcelRange cell;
currentRow++;
cell = cells.get_Item(currentRow, 1);
cell.set_Value(LabelId);

cell = cells.get_Item(currentRow, 2);
cell.set_Value(labelTxtUS);
}
}
iterator.nextValue();
}

}

private void processReportDesign()
{

XmlNodeList nodelList;
xMLNodeListIterator iterator;
XmlElement node;
XmlElement label;

nodelList = xmlDoc.getElementsByTagName(‘Designs’);

iterator = new XmlNodeListIterator(nodelList);

while (iterator.moreValues())
{

XmlElement xmlEle;
node = iterator.value();

xmlEle = node.getNamedElement(‘AxReportDesign’);

if(xmlEle)
{
System.String reportString, starttag, endtag,pattern;

reportString = xmlEle.text();
pattern = ‘Labels!(.*?)</’;
starttag = ‘Labels!’;
endtag = ‘</’;
this.matchTags( pattern, starttag, endtag, reportString);

pattern = ‘Labels!(.*?) &amp’;
starttag = ‘Labels!’;
endtag = ‘ &amp’;
this.matchTags( pattern, starttag, endtag, reportString);
}

iterator.nextValue();
}

}

private void matchTags( System.String _pattern,
System.String _starttag,
System.String _endtag,
System.String _reportString)
{

System.Text.RegularExpressions.Match myMatch;

myMatch = System.Text.RegularExpressions.Regex::Match(_reportString, _pattern);

while (myMatch.get_Success())
{
System.String labelId;
str labelTxtDE, labelTxtUS;

labelId = myMatch.get_Value();
labelId = labelId.Substring(_starttag.Length);
labelId = labelId.Substring(0, (labelId.Length – _endtag.Length));

labelTxtDE = SysLabel::labelId2String2(LabelId,”de”);
labelTxtUS = SysLabel::labelId2String2(LabelId,”EN-US”);

if((LabelId == labelTxtDE) || (labelTxtDE == labelTxtUS))
{
OfficeOpenXml.ExcelRange cell;

currentRow++;
cell = cells.get_Item(currentRow, 1);
cell.set_Value(LabelId);

cell = cells.get_Item(currentRow, 2);
cell.set_Value(labelTxtUS);
}

myMatch = myMatch.NextMatch();
}
}

}