Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

export gridviews to multiple excel sheets

Posted on 2007-08-03
2
Medium Priority
?
1,479 Views
Last Modified: 2013-11-26
Ok I have the whole export to excel thing down - I can export a gridview to a new excel spreadsheet with no problem.

What I'd like to do now is export multiple gridviews to multiple sheets of a spreadsheet.  I'm not sure where to even begin on this.

Can anyone give me some direction on how how to do this or if it's even possible?

I'm using VS2005 Pro and C#
0
Comment
Question by:megatherian
  • 2
2 Comments
 
LVL 2

Expert Comment

by:SimonFerris
ID: 19630342
I have some code, which I had at my last job and hope I've copied, which used XML/XSLT. Getting the tables from a dataset and building the worksheets from each table and adding to the workbook.

I'll have a look and get back to you.
0
 
LVL 2

Accepted Solution

by:
SimonFerris earned 2000 total points
ID: 19630352
Here is the code I used to create the workbook: (I think it is complete): I created a dll which I then used in a project.

using System;
using System.Data;
using System.Xml;
using System.Diagnostics;
using System.ComponentModel;
using System.Security.Permissions;

[assembly:CLSCompliant(true)]
//[assembly:System.Runtime.InteropServices.ComVisible(false)]
//[assembly:SecurityPermission(SecurityAction.RequestRefuse,UnmanagedCode=false)]
//[assembly:FileIOPermission(SecurityAction.RequestOptional,Unrestricted=true)]

namespace HFEAExcelExport
{
      /// <summary>
      /// Summary description for Export.
      /// </summary>
      public class Export
      {
            private string _XlOutputPath;
            private string _fPath;
            private string strOutput;
/// <summary>
/// Create an Excel Spreadsheet from a DataTable
/// </summary>
/// <param name="author">Default value of HFEA</param>
/// <param name="dataTable">The table that is to be converted to Excel</param>
            public Export(string author, DataTable dataTable)
            {
                  try
                  {
                        if(dataTable==null)
                        {
                              throw new ArgumentNullException("dataTable","You need to pass a DataTable");
                        }
                        GetFilePaths();
                        string head = header(author);
                        strOutput += head;
                  
                        strOutput +=
StartWorkSheet(dataTable.TableName.ToString(),dataTable.Columns.Count,dataTable.Rows.Count+1);
                        strOutput += "<Row>" + "\n";
                        foreach(DataColumn dc in dataTable.Columns)
                        {
                              strOutput += "<Cell ss:StyleID=\"Header\"><Data ss:Type=\"String\">" + dc.ColumnName + "</Data></Cell>" + "\n";
                        }
                        strOutput += "</Row>" + "\n";
                        foreach(DataRow dr in dataTable.Rows)
                        {
                              strOutput += "<Row>" + "\n";
                              foreach(DataColumn dc in dataTable.Columns)
                              {
                                    if(dr[dc.ColumnName] is System.DBNull)
                                    {
                                          strOutput += "<Cell><Data ss:Type=\"String\"></Data></Cell>" + "\n";
                                    }
                                    else
                                    {
                                          strOutput += "<Cell><Data ss:Type=\"String\">" + dr[dc.ColumnName].ToString() + "</Data></Cell>" + "\n";
                                    }
                              }
                              strOutput += "</Row>" + "\n";
                        }
                        strOutput += EndWorkSheet();
                        strOutput += EndWorkBook();
                        System.IO.FileStream fs = new
System.IO.FileStream(_XlOutputPath,System.IO.FileMode.Create);
                        System.Text.Encoding ascii = System.Text.Encoding.ASCII;
                        Byte[] bytes =
ascii.GetBytes(strOutput.ToCharArray());
                        fs.Write(bytes,0,bytes.Length);
                        fs.Flush();
                        fs.Close();
                        fs = null;
                  }
                  catch (System.IO.FileNotFoundException ex)
                  {
            throw ex;
                        //throw new System.IO.FileNotFoundException("File not found");
                  }
                  catch(Exception ex)
                  {
                        throw ex;
                  }
                  }
/// <summary>
/// Create an Excel workbook from a DataSet
/// </summary>
/// <param name="author">Default value of HFEA</param>
/// <param name="dataSet">The DataSet that is to be converted to Excel</param>
            public Export(string author, DataSet dataSet)
            {
            try{
                  if(dataSet==null)
                  {
                        throw new ArgumentNullException("dataSet","You need to provide a DataSet");
                  }
            GetFilePaths();
                  string head = header(author);
                  strOutput += head;
                  foreach(DataTable dt in dataSet.Tables)
                  {
                        strOutput +=
StartWorkSheet(dt.TableName.ToString(),dt.Columns.Count,dt.Rows.Count+1);

                        strOutput += "<Row>" + "\n";
                        foreach(DataColumn dc in dt.Columns)
                        {
                              strOutput += "<Cell ss:StyleID=\"Header\"><Data ss:Type=\"String\">" + dc.ColumnName + "</Data></Cell>" + "\n";
                        }
                        strOutput += "</Row>" + "\n";
                        foreach(DataRow dr in dt.Rows)
                        {
                              strOutput += "<Row>" + "\n";
                              foreach(DataColumn dc in dt.Columns)
                              {
                                    if(dr[dc.ColumnName] is System.DBNull)
                                    {
                                          strOutput += "<Cell><Data ss:Type=\"String\"></Data></Cell>" + "\n";
                                    }
                                    else
                                    {
                                          strOutput += "<Cell><Data ss:Type=\"String\">"+ dr[dc.ColumnName].ToString() + "</Data></Cell>" +"\n";
                                    }
                              }
                              strOutput += "</Row>" + "\n";
                        }
                strOutput += EndWorkSheet();
                  }
                  strOutput += EndWorkBook();
                        System.IO.FileStream fs = new
System.IO.FileStream(_XlOutputPath,System.IO.FileMode.Create);
                        System.Text.Encoding ascii = System.Text.Encoding.ASCII;
                        Byte[] bytes =
ascii.GetBytes(strOutput.ToCharArray());
                        fs.Write(bytes,0,bytes.Length);
                        fs.Flush();
                        fs.Close();
                        fs = null;
                  }
                  catch (System.IO.FileNotFoundException ex)
                  {
            throw ex;
                        //throw new System.IO.FileNotFoundException("File not found");
                  }
                  catch(Exception ex)
                  {
                        throw ex;
                  }
            }
            /// <summary>
            /// Show the Excel file that has been created fromeither the DataTable or DataSet
            /// </summary>
            public void Show()
            {
                  if(!(System.IO.File.Exists(_XlOutputPath)))
                  {
                        //throw new Exception("File does not exist");
                        throw new System.IO.FileNotFoundException("File does not exist");
                  }
                  else
                  {
                  
Process.Start("Excel.exe",_XlOutputPath.ToString());
                  }
            
            }
        public string ShowFilePath ()
            {
            if (!(System.IO.File.Exists(_XlOutputPath)))
                {
                throw new System.IO.FileNotFoundException("File does not exist");
                }
            else
                {
                return _XlOutputPath;
                }
            }
            /// <summary>
            /// Create the header for the Excel workbook
            /// </summary>
            /// <param name="author">Default value of HFEA</param>
            /// <returns>A string of the header of a Excel workbook</returns>
            internal static string header(string author)
            {
                   string ret  = "";

        ret += "<?xml version=\"1.0\"?>" + "\n";
        ret += "<?mso-application progid=\"Excel.Sheet\"?>" + "\n";
        ret += "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""+ "\n";
        ret += "xmlns:o =\"urn:schemas-microsoft-com:office:office\"" + "\n";
        ret += "xmlns:x =\"urn:schemas-microsoft-com:office:excel\"" + "\n";
        ret += "xmlns:ss =\"urn:schemas-microsoft-com:office:spreadsheet\"" +"\n";
        ret += "xmlns:html=\"http://www.w3.org/TR/REC-html40\">" +"\n";
        ret += " <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">" +"\n";
        ret += "  <Author>" + author + "</Author>" + "\n";
        ret += "  <LastAuthor>" + author + "</LastAuthor>" + "\n";
        ret += "  <Created>" + System.DateTime.UtcNow.ToString() + "</Created>" +"\n";
        ret += "  <LastSaved>" + System.DateTime.UtcNow.ToString() + "</LastSaved>" +"\n";
        ret += "  <Company>HFEA</Company>" + "\n";
        ret += "  <Version>11.6360</Version>" + "\n";
        ret += " </DocumentProperties>" + "\n";
        ret += " <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">" +"\n";
        ret += "  <WindowHeight>11835</WindowHeight>"+ "\n";
        ret += "  <WindowWidth>18060</WindowWidth>" +"\n";
        ret += "  <WindowTopX>360</WindowTopX>" +"\n";
        ret += "  <WindowTopY>-255</WindowTopY>" +"\n";
        ret += " <ProtectStructure>False</ProtectStructure>" + "\n";
        ret += " <ProtectWindows>False</ProtectWindows>" + "\n";
        ret += " </ExcelWorkbook>" + "\n";
        ret += "<Styles>" + "\n";
        ret += "  <Style ss:ID=\"Default\" ss:Name=\"Normal\">" + "\n";
        ret += "   <Alignment ss:Vertical=\"Bottom\"/>" + "\n";
        ret += "   <Borders/>" + "\n";
        ret += "   <Font/>" + "\n";
        ret += "   <Interior/>" + "\n";
        ret += "   <NumberFormat/>" + "\n";
        ret += "   <Protection/>" + "\n";
        ret += "  </Style>" + "\n";
        ret += "  <Style ss:ID=\"Header\">" + "\n";
        ret += "   <Font x:Family=\"Swiss\" ss:Bold=\"1\"/>" + "\n";
        ret += "   <Interior ss:Color=\"#FFFF00\" ss:Pattern=\"Solid\"/>" + "\n";
        ret += "  </Style>" + "\n";
        ret += "</Styles>" + "\n";
            return ret;
            }
            /// <summary>
            /// Create a worksheet for Excel
            /// </summary>
            /// <param name="WorkBookname">Default value of HFEA</param>
            /// <param name="ColumnCount">Number of columns on the worksheet</param>
            /// <param name="RowCount">Number of rows on the worksheet</param>
            /// <returns></returns>
            internal static string StartWorkSheet(string WorkBookname, Int32 ColumnCount, Int32 RowCount)
            {
                  string ret = "";
                  ret += "<Worksheet ss:Name=\"" + WorkBookname +"\">" + "\n";
                  ret += " <Table ss:ExpandedColumnCount=\"" + ColumnCount.ToString() + "\" ss:ExpandedRowCount=\"" + RowCount.ToString() + "\" x:FullColumns=\"1\"" + "\n";
                  ret += " x:FullRows=\"1\">" + "\n";
                  return ret;
            }
            /// <summary>
            /// An Excel worksheet end XML string
            /// </summary>
            /// <returns>A string for the end of the worksheet</returns>
            internal static string EndWorkSheet()
            {
                  string ret = "";
                  ret += "  </Table>" + "\n";
                  ret += "<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">" + "\n";
                  ret += "   <ProtectObjects>False</ProtectObjects>"+ "\n";
                  ret += "  <ProtectScenarios>False</ProtectScenarios>" + "\n";
                  ret += "  </WorksheetOptions>" + "\n";
                  ret += " </Worksheet>" + "\n";

                  return ret;
            }
/// <summary>
/// An Excel workbook end XML string
/// </summary>
/// <returns>A string for the end of the workbook</returns>
            internal static string EndWorkBook()
            {
                   return "</Workbook>";
            }
            /// <summary>
            /// Set the working files for the class
            /// </summary>
            internal void GetFilePaths()
            {
            
this._fPath=Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)+"\\excelreport"+DateTime.UtcNow.Ticks.ToString()+".xml";
                  this._XlOutputPath =
Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)+"\\excelreport"+DateTime.UtcNow.Ticks.ToString()+".xml";
            }

      }
      public class Detail
      {
            public class Dimensions
            {
                  public int rows
            {
                  get{return rows;}
                  set{rows = value;}
            }
                  public int columns
                  {
                        get{return columns;}
                        set{columns = value;}
                  }
                  }
            }
      }
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question