最近在做一个任务item, 要求检查reporting services(2003/2005)格式的报表里hard code的label,并把它们变成从数据库的表里拿出来用， 当时要求是先一个报表， 我想，如果有好多报表，纯用手工来做，不累死才怪，学过一下.net3.5里的linq to XML的特性，好，就用这个来实现。
首先得有一个思路， 大家都知道micorsoft的reporting services无论2003或2005的rdl文件实际上就是一个XML文件，里边的节点格式大致相同, label在xml里都是<textbox>…</textbox>
思路:
1. 找出所有textbox
2. 找出这些textbox的内容里有hardcode中文或英文(用各自的正则表达式来匹配, 本例子只找英文的)
3. 自动根据需要生成不重复的label id给这个hardcode的label, 根据资源表结果生成insert语句
4. 将report的xml内容里hard code的地方,替换成刚加入的label id
5. 原本还可以在DataSets节点下找到取label的存储过程,为它加上新加的field, 不过算了,还不如直接打开报表,刷新一下数据源.
代码如下:
view plaincopy to clipboardprint?
private void button1_Click(object sender, EventArgs e)
{
//
XNamespace ns = "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition";
if (File.Exists("output2.txt"))
{
File.Delete("output2.txt");
}
using (FileStream fs = File.Open("output2.txt", FileMode.OpenOrCreate, FileAccess.Write, FileShare.None))
{
XNamespace ns2 = "urn:schemas-microsoft-com:office:spreadsheet";
XNamespace nsSS = "urn:schemas-microsoft-com:office:spreadsheet";
//File file in dir.GetFiles("*.rdl")
string fileName = Application.StartupPath + @"\StaffInjuryReport.rdl";
XElement eleReport = XElement.Load(fileName);
//Regex reg = new Regex(@"[<]Value[>][^=]*[<]/Value[>]");
Regex reg = new Regex(@"[<]Value[>][^=]*[<]/Value[>]");
IEnumerable<XElement> tbList =
from itemField in eleReport.Descendants(ns + "Textbox")
//where reg.IsMatch( itemField.Elements(ns + "Value").First().Value) == true
select itemField;
string lineStr = "";
string hardCodeString = "";
string reportLabelID = "";
string needReplaceValue = "";
System.Text.StringBuilder sb = new StringBuilder();
Dictionary<string, string> existsDict = new Dictionary<string, string>();
Dictionary<string, string> existsLabelIdDict = new Dictionary<string, string>();
int duplicateLabelIdIndex = 0;
//INSERT INTO ODM_CDReportLabel ([ReportLabelID],[Code1],[Code2],[Code3],[ReportID],[Version]) VALUES ('lblAccident',N'Accident',N'意外',N'意外',N'ODMStaffInjury','KKKK')
foreach (var item in tbList)
{
//reportLabelID = (string)item.Element(ns + "DataField");
//----------------1.-
hardCodeString = string.IsNullOrEmpty((string)item.Element(ns + "Value")) ? "" : (string)item.Element(ns + "Value");
if (hardCodeString.Length > 0)
{
//string ss = item.Value;
//----begin-----------hardcode了英文的-------------------------------------------------------------
if (reg.IsMatch(item.ToString()))
{
//判断是否已存在于这次已找出的结果里
if (existsDict.Values.Contains(hardCodeString))
{
reportLabelID = existsDict.Where(ca => ca.Value == hardCodeString).First().Key;
}
else
{
//reportLabelID = "";
//hardCodeString = "";
reportLabelID = "lbl" + (hardCodeString.Length < 5 ? hardCodeString.Replace(" ", "") : hardCodeString.Substring(0, 5).Replace(" ", ""));
reportLabelID = reportLabelID.Replace("'", "").Replace(":", "").Replace(".", "").Replace("Ⅰ", "").Replace("&", "").Replace("'", "").Replace("II", "").Replace(")", "").Replace("(", "");
//DB
if (this.oMISDBDataSet.ODM_CDReportLabel.Count > 0)
{
if (this.oMISDBDataSet.ODM_CDReportLabel.Where(ca => ca.ReportLabelID == reportLabelID).Any())
{
reportLabelID = reportLabelID + "ForStaffInjury";
}
}
//再判断reportLabelID有没有重复的
//existsLabelIdDict
if (existsLabelIdDict.Values.Contains(reportLabelID))
{
reportLabelID = reportLabelID + duplicateLabelIdIndex.ToString();
}
lineStr = @"INSERT INTO ODM_CDReportLabel ([ReportLabelID],[Code1],[Code2],[Code3],[ReportID],[Version]) VALUES ('"
+ "" + reportLabelID + "'"
+ ",N'" + hardCodeString.Replace("'", "''") + "'"
+ ",N'" + hardCodeString.Replace("'", "''") + "'"
+ ",N'" + hardCodeString.Replace("'", "''") + "'"
+ ",N'ODMStaffInjury'"
+ ", 'MTRC')"
+ "\r\n";
Byte[] info = new UTF8Encoding(true).GetBytes(lineStr);
// Add some information to the file.
fs.Write(info, 0, info.Length);
sb.Append("'" + reportLabelID + "',");
existsLabelIdDict.Add(reportLabelID, reportLabelID);
existsDict.Add(reportLabelID, hardCodeString);
}
//--------------------------2.--
//<Value>=First(Fields!Wasmahcineinmotion.value, "GetRptLabel")</Value>
//replace
needReplaceValue = "=First(Fields!" + reportLabelID + ".value, \"GetRptLabel\")";
item.Element(ns + "Value").Value = needReplaceValue;
}
//----end-----------hardcode了英文的-------------------------------------------------------------
}
duplicateLabelIdIndex ++;
}
Byte[] info2 = new UTF8Encoding(true).GetBytes(sb.ToString());
fs.Write(info2, 0, info2.Length);
eleReport.Save("StaffInjuryReport_FinishedReplace.rdl");
MessageBox.Show("finished!");
}
}
private void button1_Click(object sender, EventArgs e)
{
//
XNamespace ns = "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition";
if (File.Exists("output2.txt"))
{
File.Delete("output2.txt");
}
using (FileStream fs = File.Open("output2.txt", FileMode.OpenOrCreate, FileAccess.Write, FileShare.None))
{
XNamespace ns2 = "urn:schemas-microsoft-com:office:spreadsheet";
XNamespace nsSS = "urn:schemas-microsoft-com:office:spreadsheet";
//File file in dir.GetFiles("*.rdl")
string fileName = Application.StartupPath + @"\StaffInjuryReport.rdl";
XElement eleReport = XElement.Load(fileName);
//Regex reg = new Regex(@"[<]Value[>][^=]*[<]/Value[>]");
Regex reg = new Regex(@"[<]Value[>][^=]*[<]/Value[>]");
IEnumerable<XElement> tbList =
from itemField in eleReport.Descendants(ns + "Textbox")
//where reg.IsMatch( itemField.Elements(ns + "Value").First().Value) == true
select itemField;
string lineStr = "";
string hardCodeString = "";
string reportLabelID = "";
string needReplaceValue = "";
System.Text.StringBuilder sb = new StringBuilder();
Dictionary<string, string> existsDict = new Dictionary<string, string>();
Dictionary<string, string> existsLabelIdDict = new Dictionary<string, string>();
int duplicateLabelIdIndex = 0;
//INSERT INTO ODM_CDReportLabel ([ReportLabelID],[Code1],[Code2],[Code3],[ReportID],[Version]) VALUES ('lblAccident',N'Accident',N'意外',N'意外',N'ODMStaffInjury','KKKK')
foreach (var item in tbList)
{
//reportLabelID = (string)item.Element(ns + "DataField");
//----------------1.-
hardCodeString = string.IsNullOrEmpty((string)item.Element(ns + "Value")) ? "" : (string)item.Element(ns + "Value");
if (hardCodeString.Length > 0)
{
//string ss = item.Value;
//----begin-----------hardcode了英文的-------------------------------------------------------------
if (reg.IsMatch(item.ToString()))
{
//判断是否已存在于这次已找出的结果里
if (existsDict.Values.Contains(hardCodeString))
{
reportLabelID = existsDict.Where(ca => ca.Value == hardCodeString).First().Key;
}
else
{
//reportLabelID = "";
//hardCodeString = "";
reportLabelID = "lbl" + (hardCodeString.Length < 5 ? hardCodeString.Replace(" ", "") : hardCodeString.Substring(0, 5).Replace(" ", ""));
reportLabelID = reportLabelID.Replace("'", "").Replace(":", "").Replace(".", "").Replace("Ⅰ", "").Replace("&", "").Replace("'", "").Replace("II", "").Replace(")", "").Replace("(", "");
//DB
if (this.oMISDBDataSet.ODM_CDReportLabel.Count > 0)
{
if (this.oMISDBDataSet.ODM_CDReportLabel.Where(ca => ca.ReportLabelID == reportLabelID).Any())
{
reportLabelID = reportLabelID + "ForStaffInjury";
}
}
//再判断reportLabelID有没有重复的
//existsLabelIdDict
if (existsLabelIdDict.Values.Contains(reportLabelID))
{
reportLabelID = reportLabelID + duplicateLabelIdIndex.ToString();
}
lineStr = @"INSERT INTO ODM_CDReportLabel ([ReportLabelID],[Code1],[Code2],[Code3],[ReportID],[Version]) VALUES ('"
+ "" + reportLabelID + "'"
+ ",N'" + hardCodeString.Replace("'", "''") + "'"
+ ",N'" + hardCodeString.Replace("'", "''") + "'"
+ ",N'" + hardCodeString.Replace("'", "''") + "'"
+ ",N'ODMStaffInjury'"
+ ", 'MTRC')"
+ "\r\n";
Byte[] info = new UTF8Encoding(true).GetBytes(lineStr);
// Add some information to the file.
fs.Write(info, 0, info.Length);
sb.Append("'" + reportLabelID + "',");
existsLabelIdDict.Add(reportLabelID, reportLabelID);
existsDict.Add(reportLabelID, hardCodeString);
}
//--------------------------2.--
//<Value>=First(Fields!Wasmahcineinmotion.value, "GetRptLabel")</Value>
//replace
needReplaceValue = "=First(Fields!" + reportLabelID + ".value, \"GetRptLabel\")";
item.Element(ns + "Value").Value = needReplaceValue;
}
//----end-----------hardcode了英文的-------------------------------------------------------------
}
duplicateLabelIdIndex ++;
}
Byte[] info2 = new UTF8Encoding(true).GetBytes(sb.ToString());
fs.Write(info2, 0, info2.Length);
eleReport.Save("StaffInjuryReport_FinishedReplace.rdl");
MessageBox.Show("finished!");
}
}
后记： linq to XML比以前的xml操作方法方便太多了, 当然可以更自动，遍历多文件之类的，但这种方便的小程序，够用就好, 纯碎是一种方法论.
Subscribe