Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
C# Programming questions specific to the Microsoft C# language. See also the forum Beginning Visual C# to discuss that specific Wrox book and code.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 16th, 2003, 04:09 PM
NoXuS
Guest
 
Posts: n/a
Default Create excel file without Excel being installed

Hello,

I want to create an excel file from all the data gathered in my application. The problem I have is that Excel is not installed on my PC so creating the xls file by means of communicating with Excel.exe (OLE, ActiveX, ...) is not an option.


Installing Excel on the PC also isn't an option since the PC's are used inside a production environment and installing Excel on every PC would simply cost too much.

So, in short, I'm looking for code/examples/dll's/third party toolkits that allow me to create an excel xls file from within my application.
Note: don't restrict your answer to C#. If you have a better alternative in C, C++, ... I would also like to hear it.

Thanks in advance for all the help!

Dennis
NoXuS@pandora.be
Reply With Quote
  #2 (permalink)  
Old July 17th, 2003, 12:07 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Slovakia.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

on msdn are some articles about exporting data to XLS by using XML files


Generate XML from a DataSet For Use In Excel 2002
This section illustrates how to create a DataSet object and export the data that it contains to an XML file by using the WriteXML method. The XML file that is generated can be opened directly in Excel. For illustration purposes, the DataSet object is created from the Microsoft Access Northwind sample database by using the Jet OLEDB Provider. However, similar code works with any DataSet object that you create with Visual C# .NET.
Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Select Windows Application from the Visual C# Projects types. Form1 is created by default.
On the View menu, select Toolbox to display the Toolbox and add a button to Form1.
Double-click Button1. The code window for the Form appears.
Add the following using directives to the top of Form1.cs:
using System.Data.OleDb;
using System.Xml;

Add the following private member variable to the Form1 class:
private string strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    + " C:\\Program Files\\Microsoft Office\\Office10\\Samples\\"
    + "Northwind.mdb;";

NOTE: If you did not install Excel 2002 in the default folder, C:\Program Files\Microsoft Office, you must modify the path to Northwind.mdb in the connection string to match your installation.


Add the following code to the button1_Click handler:
//Connect to the data source.
         OleDbConnection objConn = new OleDbConnection (strConn);
         try
         {
            objConn.Open();

            //Fill a dataset with records from the Customers table.
            OleDbCommand objCmd = new OleDbCommand(
               "Select CustomerID, CompanyName, ContactName, "
               + "Country, Phone from Customers", objConn);
            OleDbDataAdapter objAdapter = new OleDbDataAdapter();
            objAdapter.SelectCommand = objCmd;
            DataSet objDataset = new DataSet();
            objAdapter.Fill(objDataset);


            //Create the FileStream to write with.
            System.IO.FileStream fs = new System.IO.FileStream(
               "C:\\Customers.xml", System.IO.FileMode.Create);

            //Create an XmlTextWriter for the FileStream.
            System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(
               fs, System.Text.Encoding.Unicode);

            //Add processing instructions to the beginning of the XML file, one
            //of which indicates a style sheet.
            xtw.WriteProcessingInstruction("xml", "version='1.0'");
            xtw.WriteProcessingInstruction("xml-stylesheet",
               "type='text/xsl' href='customers.xsl'");

            //Write the XML from the dataset to the file.
            objDataset.WriteXml(xtw);
            xtw.Close();

            //Close the database connection.
            objConn.Close();
         }
         catch (System.Exception ex)
         {
            MessageBox.Show(ex.Message);
         }

Press F5 to build and run the program.
Click Button1 to create the XML file, then close Form1 to end the program.
Start Excel 2002 and open the C:\Customers.xml output file.
After you have observed how the XML has been parsed into rows and columns in the new workbook, close the file and quit Excel.
back to the top
Format the XML Using a Stylesheet
This step shows you how to use a stylesheet (XSL) to transform how XML data is formatted and arranged in an Excel workbook.
Using any HTML editor or a text editor (such as Notepad.exe), save the following XSL as C:\Customers.xsl:
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:template match="/">
    <HTML>
      <HEAD>
        <STYLE>
          .HDR { background-color:bisque;font-weight:bold }
        </STYLE>
      </HEAD>
      <BODY>
        <TABLE>
          <COLGROUP WIDTH="100" ALIGN="CENTER"></COLGROUP>
          <COLGROUP WIDTH="200" ALIGN="LEFT"></COLGROUP>
          <COLGROUP WIDTH="200" ALIGN="LEFT"></COLGROUP>
          <COLGROUP WIDTH="100" ALIGN="LEFT"></COLGROUP>
          <COLGROUP WIDTH="100" ALIGN="LEFT"></COLGROUP>
          <TD CLASS="HDR">Customer ID</TD>
          <TD CLASS="HDR">Company</TD>
          <TD CLASS="HDR">Contact</TD>
          <TD CLASS="HDR">Country</TD>
          <TD CLASS="HDR">Phone</TD>
          <xsl:for-each select="NewDataSet/Table">
            <TR>
              <TD><xsl:value-of select="CustomerID"/></TD>
              <TD><xsl:value-of select="CompanyName"/></TD>
              <TD><xsl:value-of select="ContactName"/></TD>
              <TD><xsl:value-of select="Country"/></TD>
              <TD><xsl:value-of select="Phone"/></TD>
            </TR>
          </xsl:for-each>
        </TABLE>
      </BODY>
    </HTML>
  </xsl:template>
</xsl:stylesheet>

Uncomment the following line of code in the button1_Click handler:
xtw.WriteProcessingInstruction("xml-stylesheet",
    "type='text/xsl' href='customers.xsl'");

This line of code writes a processing instruction to the XML file that Excel uses to locate the stylesheet (Customers.xsl).


Press F5 to build and run the program.
Click Button1 to create the XML file, then close Form1 to end the program.
Start Excel 2002 and open the C:\Customers.xml output file.
Because Excel sees the processing instruction for the stylesheet in the XML, you receive a dialog box prompt when you open the file. In the Import XML dialog box, select Open the file with the following stylesheet applied. In the list, select Customers.xsl and click OK. Note that the XML data is formatted and that the columns have been arranged according to the stylesheet.
Close the file and quit Excel.
back to the top
Use Code to Open the Transformed XML
Up to this point, you have opened the XML file by using the user interface in Excel. This section demonstrates how to automate Excel to open the workbook programmatically. The following sample illustrates how to open the transformed XML without user intervention by first transforming the XML in the DataSet object to HTML.
Add a reference to the Microsoft Excel 10.0 Object Library. To do this, follow these steps:
On the Project menu, click Add Reference.
On the COM tab, locate Microsoft Excel 10.0 Object Library and click Select.
Click OK in the Add References dialog box to accept your selection. If you receive a prompt to generate wrappers for the library that you selected, click Yes.
Add the following using directives to the top of Form1.cs:
using Excel = Microsoft.Office.Interop.Excel;

In the Visual C# .NET project, add another button to Form1.
Double-click Button2. When the code window for the form appears, add the following code to the Button2_Click handler:
//Connect to the data source.
OleDbConnection objConn = new OleDbConnection (strConn);
objConn.Open();

//Fill a dataset with records from the Customers table.
OleDbCommand objCmd = new OleDbCommand(
    "Select CustomerID, CompanyName, ContactName, "
    + "Country, Phone from Customers", objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmd;
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset);

//Create the FileStream to write with.
System.IO.FileStream fs = new System.IO.FileStream(
    "C:\\Customers.htm", System.IO.FileMode.Create);

//Create an XmlTextWriter for the FileStream.
System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(
    fs, System.Text.Encoding.Unicode);

//Transform the XML using the stylesheet.
XmlDataDocument xmlDoc = new XmlDataDocument(objDataset);
System.Xml.Xsl.XslTransform xslTran = new System.Xml.Xsl.XslTransform();
xslTran.Load("C:\\Customers.xsl");
xslTran.Transform(xmlDoc, null, xtw);

//Open the HTML file in Excel.
Excel.Application oExcel = new Excel.Application();
oExcel.Visible=true;
oExcel.UserControl=true;
Excel.Workbooks oBooks = oExcel.Workbooks;
object oOpt = System.Reflection.Missing.Value; //for optional arguments
oBooks.Open("c:\\customers.htm", oOpt, oOpt, oOpt,
    oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt,
    oOpt, oOpt, oOpt);


Press F5 to build and run the program.
Click Button2 to open the transformed XML in Excel.
NOTE: While the Excel 2002 Object Model does expose an OpenXML method that enables you to programmatically open an XML file with stylesheets applied, the previous sample does not call this method due to a known problem with using this method from an Automation client. The OpenXML method works as expected when it is called from an Excel macro; however, when this method is called from an Automation client, the StyleSheet parameter is ignored. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
307230 BUG: StyleSheets Parameter of the OpenXML Method Ignored When Automating Excel 2002

back to the top
Reply With Quote
  #3 (permalink)  
Old December 13th, 2005, 02:44 PM
Registered User
 
Join Date: Dec 2005
Location: Naperville, IL, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to ThreeRavens
Default

I have a similar issue that i am working on and I am not too sure how to go about this. Our problem is that the excel instances that are created when our reports are generated via an ASP call, it bogs the heck out of our server, causing processes to crash. Most of the reports are multi-worksheet reports with data items linking to other pages within the workbook. The queries are going to be generated via ColdFusion. Is there a good resource...or can someone help me with the XML to create the spreadsheets on the fly, without using excel on the server, or using any other option to push the processing client side, instead of server side. Any help would be appreciated. Thanks in advance!

Eric
Reply With Quote
  #4 (permalink)  
Old January 4th, 2007, 03:40 AM
Registered User
 
Join Date: Jan 2007
Location: Bangalore, Karnataka, India.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Writing data to excel using oledb

public void writeSheets(string GeneratedFile,DataTable dt,string shtname)
        {
            const int DEFAULT_ROWS_BEFORE_CONN_CLOSE = 5000;
            int i,excelsheet,excelrow,sheetno;
            string sheetName2,sqlCommandText,sheetName;
            string sqlCommandTextTemplate = null;

            sheetName=shtname;
            sheetName2=sheetName;
            excelrow=0;
            excelsheet=1;
            sheetno=0;
            long MaximumNumberOfRowsPerSheet=50000;

            String excelconnstring ="Provider=Microsoft.Jet.OLEDB.4.0;" +
                "OLE DB Services=-4;Data Source=" + GeneratedFile + ";" +
                "Extended Properties=Excel 8.0;";
            OleDbConnection excelconn= new OleDbConnection(excelconnstring);

            OleDbCommand cmd = new OleDbCommand();
            bool bCloseExcelWriterConnPeriodically = false;
            string closeExcelWriterConnPeriodically = ConfigurationSettings.AppSettings["ExcelWriter.CloseConnectionWhileWriting"] as string;

                if (closeExcelWriterConnPeriodically != null)
                {
                    if (closeExcelWriterConnPeriodically.ToLower() == "true")
                    {
                        bCloseExcelWriterConnPeriodically = true;
                    }
                }
                int numRowsBeforeConnectionClose = DEFAULT_ROWS_BEFORE_CONN_CLOSE;
                string strNumRowsBeforeConnectionClose = ConfigurationSettings.AppSettings["ExcelWriter.NumRowsBeforeClose"] as string;
                if (strNumRowsBeforeConnectionClose != null)
                {
                    try
                    {
                        numRowsBeforeConnectionClose = int.Parse(strNumRowsBeforeConnectionClose);
                    }
                    catch (FormatException)
                    {
                        numRowsBeforeConnectionClose = DEFAULT_ROWS_BEFORE_CONN_CLOSE;
                    }
                }
            try
            {
                excelconn.Open();
                try
                {
                    cmd.Connection = excelconn;
                    sqlCommandText="CREATE TABLE [" + sheetName + "$] (";
                    for (i=0;i< dt.Columns.Count;i++)
                    {
                        sqlCommandText+="[" + dt.Columns[i].ColumnName+ "] char(255)";
                        if (i< dt.Columns.Count-1) sqlCommandText+=",";
                    }
                    sqlCommandText+=")";
                    cmd.CommandText =sqlCommandText;
                    cmd.ExecuteNonQuery();
                    OleDbCommand insertCmd = null;
                    foreach( DataRow dataRow in dt.Rows)
                    {
                        if (insertCmd == null)
                        {
                            sqlCommandTextTemplate="Insert INTO ["+sheetName+"$] values (";
                            OleDbParameter[] arrOleDbParams = new OleDbParameter[dt.Columns.Count];
                            for (i=0; i < dt.Columns.Count; i++)
                            {
                                sqlCommandTextTemplate+="?";
                                if (i<dt.Columns.Count-1)
                                {
                                    sqlCommandTextTemplate+=",";
                                }
                                arrOleDbParams[i] = new OleDbParameter("param" + i, OleDbType.VarWChar);
                            }

                            sqlCommandTextTemplate+=")";
                            sqlCommandText = string.Format(sqlCommandTextTemplate, sheetName2);
                            insertCmd = new OleDbCommand(sqlCommandText, excelconn);
                            for (i=0; i < dt.Columns.Count; i++)
                            {
                                insertCmd.Parameters.Add(arrOleDbParams[i]);
                            }
                        }

                        if (excelrow>=MaximumNumberOfRowsPerSheet)
                        {
                            excelrow=0;
                            excelsheet++;
                            sheetName2=sheetName + excelsheet.ToString();
                            sqlCommandText="CREATE TABLE [" + sheetName2 + "] (";
                            for (i=0;i< dt.Columns.Count;i++)
                            {
                                sqlCommandText+="[" + dt.Columns[i].ColumnName + "] char(255)";
                                if (i<dt.Columns.Count-1) sqlCommandText+=",";
                            }
                            sqlCommandText+=")";
                            cmd.CommandText =sqlCommandText;
                            cmd.ExecuteNonQuery();
                            if (sqlCommandTextTemplate != null)
                            {
                                insertCmd.CommandText = string.Format(sqlCommandTextTemplate, sheetName2);

                            }
                            sheetno++;
                            insertCmd = null;
                            sqlCommandTextTemplate="Insert INTO ["+sheetName2+"] values (";
                            OleDbParameter[] arrOleDbParams = new OleDbParameter[dt.Columns.Count];
                            for (i=0; i < dt.Columns.Count; i++)
                            {
                                sqlCommandText+="'"+ CorrectQuotations(dataRow[i].ToString())+"'";
                                sqlCommandTextTemplate+="?";
                                if (i<dt.Columns.Count-1)
                                {
                                    sqlCommandTextTemplate+=",";
                                }
                                arrOleDbParams[i] = new OleDbParameter("param" + i, OleDbType.VarWChar);
                            }
                            sqlCommandTextTemplate+=")";
                            sqlCommandText = string.Format(sqlCommandTextTemplate, sheetName2);
                            insertCmd = new OleDbCommand(sqlCommandText, excelconn);
                            for (i=0; i < dt.Columns.Count; i++)
                            {
                                insertCmd.Parameters.Add(arrOleDbParams[i]);
                            }
                        }
                        excelrow++;

                        if (bCloseExcelWriterConnPeriodically && (excelrow % numRowsBeforeConnectionClose) == 0)
                        {
                            excelconn.Close();
                            excelconn.Open();
                        }

                        for (i=0;i< dt.Columns.Count;i++)
                        {
                            insertCmd.Parameters[i].Value = dataRow[i].ToString();
                        }
                        cmd.CommandTimeout=3000;

                        insertCmd.ExecuteNonQuery();
                    }

                }
                catch (OleDbException e)
                {
                    Log.LogCSCode.Info("sheetname2"+sheetName2);
                    Log.LogCSCode.Info("excelrow"+excelrow);
                    Log.LogCSCode.Error("OleDb error in writing to excel sheet",e);
                    throw new ExcelWriterException("OleDb error in writing to excel sheet "+sheetName+". "+e.Message,e);
                }
                catch (NullReferenceException e)
                {
                    Log.LogCSCode.Error("NullReference error in writing to excel sheet",e);
                    throw new ExcelWriterException("NullReference error in writing to excel sheet "+sheetName+". "+e.Message,e);
                }
                finally
                {
                    // the closing of the reader is done in the BaseReportGenerator

                    if (cmd != null)
                        cmd.Dispose();


                    if (excelconn != null)
                    {
                        //drdr.Close();

                        excelconn.Close();
                        excelconn.Dispose();
                    }

                }
            }
            catch (OleDbException ex)
            {
                Log.LogCSCode.Error("OleDb has problem to Open connection to excel file",ex);
                throw new ExcelWriterException("OleDb has problem to Open connection to excel file "+shtname+". "+ex.Message,ex);
            }
            catch(Exception ex)
            {
             Log.LogCSCode.Error("Exception that could not be caught" + ex.ToString());
            }

        }

Ishhita Singh
Bangalore
Reply With Quote
  #5 (permalink)  
Old August 29th, 2009, 12:38 PM
Registered User
 
Join Date: Jul 2009
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can read and write to Excel files without having Excel installed with GemBox Excel .NET component. It's very fast and easy to work with. Here's a list of reasons why it's better to use that then regular Excel Automation.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create excel file in vb monika.vasvani VB Databases Basics 0 July 17th, 2008 10:24 PM
Mapping one excel file to another excel file Roshanjoshi2001 Excel VBA 0 December 1st, 2007 07:12 AM
Problem in opening excel file in MS Excel 2000 kallol Visual C++ 0 November 16th, 2007 05:48 AM
excel create keyvanjan Classic ASP Professional 2 July 26th, 2005 01:52 AM
Excel in win 2000 to excel winxp (excel 2002) Max Excel VBA 3 August 28th, 2003 04:44 AM



All times are GMT -4. The time now is 02:13 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.