Excel multi-sheet

kraiemtehno

Hi,

I want to get a multi-page document from Excel. Prior to the appearance of the version with XLSX, there was a manager who generated N pages and compiled them into a single report.
Now, I want to do this in a template. For some reports, a fixed number of pages are suitable, in which case they are marked in the .frx template.
But for the case when there is an undefined amount of such an approach, this approach is not correct. I tried using the attribute StartNewPage = "true" in conjunction with Condition = "[DataSource.Code]", but this didn't work.
With this approach, Condition works and splits the data into pages, but these pages are displayed in one Sheet.

Can anyone come across a similar problem. Tell me how to implement dynamic multi-page through the template.

ipong

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

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

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

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;
    }
  }