Excel multi-sheet

Comments

• ipong

  May 2018 edited May 2018

  1. the rule is 'one report page equals one sheet in excel'
  2. i have a report template
  3. i want to output customer with prefix 'A' 'B' 'C' to excel and must be in separate sheets
  4. see attached project
  

  string frxFile = @"..\..\..\Repeat Headers.frx";
  XmlDocument doc = new XmlDocument();
  doc.Load(frxFile);
  
  XmlNode page1 = doc.SelectSingleNode("/Report/ReportPage");
  page1.Attributes["Name"].Value = "Customer_Prefix_A";
  XmlNode page2 = page1.Clone();
  page2.Attributes["Name"].Value = "Customer_Prefix_B";
  XmlNode page3 = page1.Clone();
  page3.Attributes["Name"].Value = "Customer_Prefix_C";
  
  XmlAttribute attr1 = doc.CreateAttribute("Filter");
  attr1.Value = "Substring([Orders.Customers.CompanyName],0,1) == \"A\"";
  XmlAttribute attr2 = doc.CreateAttribute("Filter");
  attr2.Value = "Substring([Orders.Customers.CompanyName],0,1) == \"B\"";
  XmlAttribute attr3 = doc.CreateAttribute("Filter");
  attr3.Value = "Substring([Orders.Customers.CompanyName],0,1) == \"C\"";
  
  XmlNode dataBand1 = page1.SelectSingleNode("//GroupHeaderBand/DataBand");
  dataBand1.Attributes.Append(attr1);
  XmlNode dataBand2 = page2.SelectSingleNode("/GroupHeaderBand/DataBand");
  dataBand2.Attributes.Append(attr2);
  XmlNode dataBand3 = page3.SelectSingleNode("/GroupHeaderBand/DataBand");
  dataBand3.Attributes.Append(attr3);
  
  XmlNode root = doc.SelectSingleNode("/Report");
  root.AppendChild(page2);
  root.AppendChild(page3);
  string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + root.OuterXml.Replace("#xD", "#13").Replace("#xA", "#10");
  
  DataSet dataSet = new DataSet();
  dataSet.ReadXml(@"..\..\..\nwind.xml");
  
  FastReport.Report report = new FastReport.Report();
  report.Preview = previewControl1;
  report.FileName = "One Page One Sheet";
  report.LoadFromString(xml);
  report.RegisterData(dataSet, "NorthWind");
  report.Show();
  
• kraiemtehno

  May 2018 edited 9:03AM

  ipong wrote: »

  1. the rule is 'one report page equals one sheet in excel'
  2. i have a report template
  3. i want to output customer with prefix 'A' 'B' 'C' to excel and must be in separate sheets
  4. see attached project

  
  Probably incorrectly explained.
  But, the module which, according to the template of one page, collects N pages for different criteria or different sets of data we have. I want to get rid of it and implement it only through the .frx template.
• BorisM

  May 2018 edited 9:03AM

  Create those pages in the Script section of the FRX template for each sheet you want added in the Excel output.
  

  kraiemtehno wrote: »

  kraiemtehno wrote: »

  1. the rule is 'one report page equals one sheet in excel'
  2. i have a report template
  3. i want to output customer with prefix 'A' 'B' 'C' to excel and must be in separate sheets
  4. see attached project

  
  Probably incorrectly explained.
  But, the module which, according to the template of one page, collects N pages for different criteria or different sets of data we have. I want to get rid of it and implement it only through the .frx template.
• ipong

  May 2018 edited May 2018

  for example, in fastreport demo, 'Repeat Headers', add this script :
  

    public class ReportScript
    {
  
      private void _StartReport(object sender, EventArgs e)
      {
        string frxFile = @"C:\Program Files\FastReports\FastReport.Net Trial\Demos\Reports\Repeat Headers.frx";
        System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
        doc.Load(frxFile);
  
        // ReportPage
        System.Xml.XmlNode page1 = doc.SelectSingleNode("/Report/ReportPage");
        page1.Attributes["Name"].Value = "Customer_Prefix_A";
        System.Xml.XmlNode page2 = page1.Clone();
        page2.Attributes["Name"].Value = "Customer_Prefix_B";
        System.Xml.XmlNode page3 = page1.Clone();
        page3.Attributes["Name"].Value = "Customer_Prefix_C";
  
        // DataBand
        System.Xml.XmlNode dataBand1 = page1.SelectSingleNode("//GroupHeaderBand/DataBand");
        AppendAttribute(doc, dataBand1, "Filter", "Substring([Orders.Customers.CompanyName],0,1) == \"A\""); 
        System.Xml.XmlNode dataBand2 = page2.SelectSingleNode("/GroupHeaderBand/DataBand");
        AppendAttribute(doc, dataBand2, "Filter", "Substring([Orders.Customers.CompanyName],0,1) == \"B\"");
        System.Xml.XmlNode dataBand3 = page3.SelectSingleNode("/GroupHeaderBand/DataBand");
        AppendAttribute(doc, dataBand3, "Filter", "Substring([Orders.Customers.CompanyName],0,1) == \"C\"");
   
        ChangeAttribute(dataBand1, "Name", "Data1");     
        ChangeAttribute(dataBand2, "Name", "Data2");
        ChangeAttribute(dataBand3, "Name", "Data3");
        
        // Footer
        System.Xml.XmlNode footer2 = page2.SelectSingleNode("/GroupHeaderBand/GroupFooterBand");
        ChangeAttribute(footer2, "Name", "GroupFooter2");
        System.Xml.XmlNode footer3 = page3.SelectSingleNode("/GroupHeaderBand/GroupFooterBand");
        ChangeAttribute(footer3, "Name", "GroupFooter3");
        
        // TextTotal
        System.Xml.XmlNode textTotal2 = page2.SelectSingleNode("/GroupHeaderBand/GroupFooterBand/TextObject");
        ChangeAttribute(textTotal2, "Text", "Total orders: [TotalOrders2]");
        System.Xml.XmlNode textTotal3 = page3.SelectSingleNode("/GroupHeaderBand/GroupFooterBand/TextObject");
        ChangeAttribute(textTotal3, "Text", "Total orders: [TotalOrders3]");
        
        // Dictionary : Total
        System.Xml.XmlNode total1 = doc.SelectSingleNode("/Report/Dictionary/Total");
        ChangeAttribute(total1, "Evaluator", "Data1");
        System.Xml.XmlNode dict = doc.SelectSingleNode("/Report/Dictionary");
        System.Xml.XmlNode total2 = doc.CreateNode("element", "Total", "");
        AppendAttribute(doc, total2, "Name", "TotalOrders2");
        AppendAttribute(doc, total2, "TotalType", "Count");
        AppendAttribute(doc, total2, "Evaluator", "Data2");
        AppendAttribute(doc, total2, "PrintOn", "GroupFooter2");
        System.Xml.XmlNode total3 = doc.CreateNode("element", "Total", "");
        AppendAttribute(doc, total3, "Name", "TotalOrders3");
        AppendAttribute(doc, total3, "TotalType", "Count");
        AppendAttribute(doc, total3, "Evaluator", "Data3");
        AppendAttribute(doc, total3, "PrintOn", "GroupFooter3");
        dict.AppendChild(total2);
        dict.AppendChild(total3);
  
        // Finalize
        System.Xml.XmlNode root = doc.SelectSingleNode("/Report");
        root.AppendChild(page2);
        root.AppendChild(page3);
        string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + root.OuterXml.Replace("#xD", "#13").Replace("#xA", "#10");
        
        DataSet ds = new DataSet();
        TableDataSource tds1 = Report.GetDataSource("Customers") as TableDataSource;
        TableDataSource tds2 = Report.GetDataSource("Orders") as TableDataSource;
        ds.Tables.Add(tds1.Table.Copy());
        ds.Tables.Add(tds2.Table.Copy());
        
        FastReport.Report rpt = new FastReport.Report();
        rpt.FileName = "One Page One Sheet";
        rpt.ReportResourceString = xml;
        rpt.RegisterData(ds, "NorthWind");
        rpt.Show();
      }
      
      private void AppendAttribute(System.Xml.XmlDocument document, System.Xml.XmlNode node, string name, string content)
      {
        System.Xml.XmlAttribute attribute = document.CreateAttribute(name);
        attribute.Value = content;
        node.Attributes.Append(attribute);
      }
      
      private void ChangeAttribute(System.Xml.XmlNode node, string name, string content)
      {
        node.Attributes[name].Value = content;
      }
    }