locked
Dynamicly Creating Report RRS feed

  • Question

  • User-4595090 posted

    Hi !

    I've searched many tutorials about Crystal Reports , but i haven't found any example of usage that i want.

    Is it possible to generating columns of a report automatically , just like Gridview does ?

    If it's possible , how ?


    Thanks in advance,

    Monday, July 12, 2010 6:12 AM

Answers

  • User-481631678 posted

    I have not done that in crystal report. But one of my clients requested dynamic reports and I concinced them that Excel based report is a better solution for dynamic reports.


    THere is a wonderfull open source Excel Library through which you can easily convert DataSet into multi sheet excel file, just by one line of code like this:-


    ExcelXmlWorksheet sheet = ExcelXmlWorksheet.DataSetToWorkbook(sourceDataSet)

    see this link:-

    http://www.codeproject.com/KB/office/excelxmllibrary.aspx


    ALso adding a sample code which will really help you to create dynamic reports:-

    private void YougeshSample()
            {
                DataTable dt = CreateDataTable();
    
                ExcelXmlWorkbook book = new ExcelXmlWorkbook();
                book.Properties.Author = "Zeeshan Umar";
                book.Properties.Company = "Sample COmpany";
                book.Properties.Title = "Sample Title";
                book.Properties.Subject = "Subject";
    
                Worksheet ws = book[0];
                ws.Name = "Sample Sheet Name"; //Sheet Name
                ws.Font.Name = "Calibri";//Setting font for all sheet
                ws.Font.Size = 11;
    
                int rowIndex = 0;
                Row row;
                row = ws[rowIndex++];
    
                int colIndex = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    row[colIndex].Value = "Heading " + dc.ColumnName;
                    row[colIndex].Border.Sides = BorderSides.All;
                    row[colIndex].Style.Interior.Color = Color.LightGray;
                    colIndex++;
                }
    
                foreach (DataRow dr in dt.Rows)
                {
                    colIndex = 0;
                    row = ws[rowIndex++];
                    foreach (DataColumn dc in dt.Columns)
                    {
                        row[colIndex].Value = Convert.ToInt32(dr[dc.ColumnName]);
                        row[colIndex].Border.Sides = BorderSides.Left | BorderSides.Right;
                        setIntegerFormat(row[colIndex]);
                        colIndex++;
                    }
                }
    
                row = ws[rowIndex++];
                colIndex = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    row[colIndex].Value = FormulaHelper.Formula("sum", new Range(ws[colIndex, 1], ws[colIndex, 10]));
                    row[colIndex].Border.Sides = BorderSides.All;
                    row[colIndex].Style.Interior.Color = Color.LightGray;
                    colIndex++;
                }
    
                string s = "c:\\" + Guid.NewGuid().ToString() + ".xml";
                book.Export(s);
                ShellExecute(this.Handle, "open", "excel", s, "", 3);
            }
    
            private void setIntegerFormat(Cell cell)
            {
                cell.DisplayFormat = DisplayFormatType.Custom;
                cell.CustomFormatString = "#,##0";
            }
    
    
            [DllImport("Shell32.dll", CharSet = CharSet.Auto)]
            public static extern IntPtr ShellExecute(
                IntPtr hwnd,
                string lpVerb,
                string lpFile,
                string lpParameters,
                string lpDirectory,
                int nShowCmd);
            private static DataTable CreateDataTable()
            {
                DataTable dt = new DataTable();
                for (int i = 0; i < 300; i++)
                {
                    dt.Columns.Add(i.ToString());
                }
                for (int i = 0; i < 10; i++)
                {
                    DataRow dr = dt.NewRow();
                    foreach (DataColumn dc in dt.Columns)
                    {
    
                        dr[dc.ToString()] = i;
                    }
                    dt.Rows.Add(dr);
                }
                return dt;
            }
    
            private void setDateFormat(Cell cell)
            {
                cell.DisplayFormat = DisplayFormatType.GeneralDate;
                cell.CustomFormatString = "dd\\-mmm\\-yyyy\\ hh:mm";
            }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 12, 2010 6:19 AM

All replies

  • User-481631678 posted

    I have not done that in crystal report. But one of my clients requested dynamic reports and I concinced them that Excel based report is a better solution for dynamic reports.


    THere is a wonderfull open source Excel Library through which you can easily convert DataSet into multi sheet excel file, just by one line of code like this:-


    ExcelXmlWorksheet sheet = ExcelXmlWorksheet.DataSetToWorkbook(sourceDataSet)

    see this link:-

    http://www.codeproject.com/KB/office/excelxmllibrary.aspx


    ALso adding a sample code which will really help you to create dynamic reports:-

    private void YougeshSample()
            {
                DataTable dt = CreateDataTable();
    
                ExcelXmlWorkbook book = new ExcelXmlWorkbook();
                book.Properties.Author = "Zeeshan Umar";
                book.Properties.Company = "Sample COmpany";
                book.Properties.Title = "Sample Title";
                book.Properties.Subject = "Subject";
    
                Worksheet ws = book[0];
                ws.Name = "Sample Sheet Name"; //Sheet Name
                ws.Font.Name = "Calibri";//Setting font for all sheet
                ws.Font.Size = 11;
    
                int rowIndex = 0;
                Row row;
                row = ws[rowIndex++];
    
                int colIndex = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    row[colIndex].Value = "Heading " + dc.ColumnName;
                    row[colIndex].Border.Sides = BorderSides.All;
                    row[colIndex].Style.Interior.Color = Color.LightGray;
                    colIndex++;
                }
    
                foreach (DataRow dr in dt.Rows)
                {
                    colIndex = 0;
                    row = ws[rowIndex++];
                    foreach (DataColumn dc in dt.Columns)
                    {
                        row[colIndex].Value = Convert.ToInt32(dr[dc.ColumnName]);
                        row[colIndex].Border.Sides = BorderSides.Left | BorderSides.Right;
                        setIntegerFormat(row[colIndex]);
                        colIndex++;
                    }
                }
    
                row = ws[rowIndex++];
                colIndex = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    row[colIndex].Value = FormulaHelper.Formula("sum", new Range(ws[colIndex, 1], ws[colIndex, 10]));
                    row[colIndex].Border.Sides = BorderSides.All;
                    row[colIndex].Style.Interior.Color = Color.LightGray;
                    colIndex++;
                }
    
                string s = "c:\\" + Guid.NewGuid().ToString() + ".xml";
                book.Export(s);
                ShellExecute(this.Handle, "open", "excel", s, "", 3);
            }
    
            private void setIntegerFormat(Cell cell)
            {
                cell.DisplayFormat = DisplayFormatType.Custom;
                cell.CustomFormatString = "#,##0";
            }
    
    
            [DllImport("Shell32.dll", CharSet = CharSet.Auto)]
            public static extern IntPtr ShellExecute(
                IntPtr hwnd,
                string lpVerb,
                string lpFile,
                string lpParameters,
                string lpDirectory,
                int nShowCmd);
            private static DataTable CreateDataTable()
            {
                DataTable dt = new DataTable();
                for (int i = 0; i < 300; i++)
                {
                    dt.Columns.Add(i.ToString());
                }
                for (int i = 0; i < 10; i++)
                {
                    DataRow dr = dt.NewRow();
                    foreach (DataColumn dc in dt.Columns)
                    {
    
                        dr[dc.ToString()] = i;
                    }
                    dt.Rows.Add(dr);
                }
                return dt;
            }
    
            private void setDateFormat(Cell cell)
            {
                cell.DisplayFormat = DisplayFormatType.GeneralDate;
                cell.CustomFormatString = "dd\\-mmm\\-yyyy\\ hh:mm";
            }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 12, 2010 6:19 AM
  • User-4595090 posted

    so can't we create report with Crystal Report  dynamically? Do we have to use Excel for it ?

    Thursday, July 22, 2010 8:30 AM
  • User-481631678 posted

    I have not done that but i think we can dynamically add textbox and label objects in CR. But making reports in excel is lot easier then in CR so i prefer to generate dynamic reports in Excel.

    Sunday, July 25, 2010 9:43 AM
  • User-4595090 posted

    Thanks,

    i got it now

    Monday, August 2, 2010 7:06 AM