locked
Report layout when exported to excel RRS feed

  • Question

  • User175056083 posted

    Hi All,

    I have a crystal report and everytime i export it to excel, all the columns and values are messed up.  Is there a way that the same report that the users see in the crystal report viewer be formatted differently and more organized when exported to excel? 

    Thank you,

    -Nici

    Monday, June 14, 2010 1:20 PM

Answers

  • User-481631678 posted

    I have created a very detailed exmple for you, it includes background color,number format, borders, formula which will be veryusefull for you if you want to develop reports through this tool. Here is the code, feel free to ask if you have any other query:-


      private void Form1_Load(object sender, EventArgs e)
            {
                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, 9]));
                    row[colIndex].Border.Sides = BorderSides.All;
                    row[colIndex].Style.Interior.Color = Color.LightGray;
                    colIndex++;
                }
    
                string s = "c:\\" + Guid.NewGuid().ToString() + ".xml";
                book.Export(s);
            }
    
            private void setIntegerFormat(Cell cell)
            {
                cell.DisplayFormat = DisplayFormatType.Custom;
                cell.CustomFormatString = "#,##0";
            }
    
            private void setDateFormat(Cell cell)
            {
                cell.DisplayFormat = DisplayFormatType.GeneralDate;
                cell.CustomFormatString = "dd\\-mmm\\-yyyy\\ hh:mm";
            }
    
            private static DataTable CreateDataTable()
            {
                DataTable dt = new DataTable();
                for (int i = 0; i < 10; 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;
            }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 24, 2010 11:40 AM

All replies

  • User-481631678 posted

    YOu need to make sure that data headers and details are properly aligned from left and right, if there is a slight difference between alignment then they will be displayed in different columns which is very annoying for end user


    Also 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

    Wednesday, June 16, 2010 7:57 AM
  • User175056083 posted

    I have a lot things to output in my report and it does not fit all on one line of the report.  I can see what you mean about the headers but how do I make a header for each detail section?  Right now I have about 10 detail sections and my lables are in the details sections and the detail sections don't have headers. 

    Wednesday, June 16, 2010 4:12 PM
  • User1508394307 posted

    Try to use third party libs which could format Excel output as you want. For Excel 97-2003 format I can recommend NPOI, for Excel 2007 - ExcelPackage.

    Wednesday, June 16, 2010 5:23 PM
  • User-481631678 posted

    For your understanding i have an example:-


    Label1          Field1               Field2

     Label2       Field3                 Field4


    In above Label1,Label 2 are not aligned vertically so they will appear in two different columns.

    Same is the case with Field1 and FIeld 3. Only Field2 and Field 4 will appear in same column.

    Thursday, June 17, 2010 12:34 AM
  • User175056083 posted

    ok, cool.  so there's no way to do something like this is crystal reports.  I can't seem to find an option to add header sections.

    Header Section:   Label1     Label2     Label3

    Detail Section:     Field1       Field2      Field3

    Header Section:   Label4     Label5     Label6

    Detail Section:     Field4       Field5      Field6

    - I have a lot of fields to output and they don't fit across on one line.  The data in the different fields run into eachother if a put them right next to eachother and try and scrunch them all on one line.

    If this isn't possible to add in crystal reports, I think I will try and doing the excel shreadsheet you meanted earlier.  Thank you for that link.

    -Nici

    Thursday, June 17, 2010 12:51 PM
  • User-481631678 posted

    I think try to experiment with Excel Library, it is really simple and easy to use. I can share few samples if you like.

    Monday, June 21, 2010 12:35 AM
  • User175056083 posted

    if you have an example, that would be awesome 

    Thursday, June 24, 2010 9:31 AM
  • User-481631678 posted

    I have created a very detailed exmple for you, it includes background color,number format, borders, formula which will be veryusefull for you if you want to develop reports through this tool. Here is the code, feel free to ask if you have any other query:-


      private void Form1_Load(object sender, EventArgs e)
            {
                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, 9]));
                    row[colIndex].Border.Sides = BorderSides.All;
                    row[colIndex].Style.Interior.Color = Color.LightGray;
                    colIndex++;
                }
    
                string s = "c:\\" + Guid.NewGuid().ToString() + ".xml";
                book.Export(s);
            }
    
            private void setIntegerFormat(Cell cell)
            {
                cell.DisplayFormat = DisplayFormatType.Custom;
                cell.CustomFormatString = "#,##0";
            }
    
            private void setDateFormat(Cell cell)
            {
                cell.DisplayFormat = DisplayFormatType.GeneralDate;
                cell.CustomFormatString = "dd\\-mmm\\-yyyy\\ hh:mm";
            }
    
            private static DataTable CreateDataTable()
            {
                DataTable dt = new DataTable();
                for (int i = 0; i < 10; 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;
            }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 24, 2010 11:40 AM