使用Linq to XML来将hardcode在reporting services报表里的label文字变成从数据库取值

• 最近在做一个任务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, 不过算了,还不如直接打开报表,刷新一下数据源.

   

  代码如下:

• 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!"); } }

• 后记： 当然可以更自动，遍历多文件之类的，但这种方便的小程序，够用就好, 纯碎是一种方法论.