?
Solved

Excel files without Excel in C#

Posted on 2007-08-09
6
Medium Priority
?
2,253 Views
Last Modified: 2008-01-09
I need to create a Excel file in c#. Does anybody have a good Library that can create Excel files without using Excel itself. I think since Office stores data as xml file there should be solutions. It would be nice if its freeware but i am also interested if somebody has a comercial product.
0
Comment
Question by:zeebee
6 Comments
 
LVL 7

Expert Comment

by:jj819430
ID: 19664118
0
 
LVL 11

Expert Comment

by:William
ID: 19664227
0
 
LVL 35

Expert Comment

by:YZlat
ID: 19664784
bool WriteDataToExcel(string path, DataTable dt)
            {
                  string sheet = "Sheet1";
                  bool bRet=false;
                  OleDbCommand cmd=null;
                  OleDbConnection conn=null;
                  string query="", strCols="", strVals="";
                  int i =0;
                  try
                  {

                        conn = new OleDbConnection( "provider=Microsoft.Jet.OLEDB.4.0; data source=" + path + "; Extended Properties=Excel 8.0;");
                        conn.Open();
                        //build a query
                        //write out each data row
                        foreach (DataRow row in dt.Rows)
                        {
                              query = "INSERT INTO [" + sheet + "$] (";

                              i = 0;
                              try
                              {
                                    //clear strCols
                                    strCols = "";
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                          if (col.ColumnName.Trim() != "F9")
                                          {
                                                if (i == 0)
                                                {
                                                      strCols = "[" + col.ColumnName + "]";
                                                }
                                                else
                                                {
                                                      strCols = strCols + ",[" + col.ColumnName + "]";
                                                }
                                          }//End If
                                          i = i + 1;

                                    }//end for each

                                    i = 0;
                                    //clear strVals
                                    strVals = "";
                                    foreach (Object val in row.ItemArray)
                                    {
                                          if (val is string)
                                          {
                                                if (i == 0)
                                                {
                                                      strVals = strVals + "'" + val + "'";
                                                }
                                                else
                                                {
                                                      strVals = strVals + ",'" + val + "'";
                                                }//End If
                                          }
                                          else if (val is int)
                                          {
                                                                                                                                                                                                                                                                              
                                                if (i == 0)
                                                {
                                                      if (val!= null)
                                                      {
                                                            strVals = val.ToString();
                                                      }
                                                      else
                                                      {
                                                            strVals = "0";
                                                      }
                                                }                                                                                                                                                                                                                                                                                                                        
                                          }
                                          else
                                          {
                                                if (val != null)
                                                {
                                                      strVals = strVals + "," + val;
                                                }
                                                else
                                                {
                                                      strVals = strVals + ", 0";
                                                }//End If

                                          }//End If
                                          i = i + 1;
                                    }//end foreach
                              }
                              catch (Exception ex)
                              {
                                    Console.Write(ex.Message);
                              }//End Try
                              
                        }//end foreach
                  
                        //query = query & strCols & ")VALUES(" & strVals & ")"
                        query = query + " VALUES(" + strVals + ")";

                        cmd = new OleDbCommand(query, conn);
                        cmd.ExecuteNonQuery();

                        bRet = true;
                  }
                  catch (Exception ex)
                  {
                        bRet = false;
                        Console.Write(ex.Message);
                  }
                  finally
                  {
                        if (conn.State == ConnectionState.Open)
                        {
                              conn.Close();
                        }//End If
                  }//End Try
                  return bRet;
            }//End Function
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:Dux11
ID: 19682102
As for a very crude solution which I have used for sake of time and the lack of scalability requirements...

You already know Excel stored the spreadsheet in XML.  If you are always wanting the same formatting, headings etc but want to loop through records to populate the other cells you could always create the spreadsheet as you want it to appear, formatting etc.  Then save as XML.

Open in whatever text editor you use and you will see how easy it is to add new rows in the xml. <Row> </Row>

The only other thing I needed to update was ss:ExpandedRowCount depending on the number of records plus header rows...

As mentioned this is very crude and should have been put into a library as you are looking but this is a very quick way to get the functionality up for UAT.
0
 

Author Comment

by:zeebee
ID: 19684954
Hmmm that are all diffenrent aproaches. I'm searching for a Objectorientated way like

VirtualExcel ex=new Virtualexcel("C:\excelfile");
ex.cell(10,10)="test 10,10";
ex.cell(10,20)="test 10,20";
ex.WriteToFile();

This should be the Target. And with more flexibility like Color and FontStyles. I think it is so common that many should have faced this problem and i think there should be a clean object orientated solution.
0
 
LVL 11

Accepted Solution

by:
William earned 1000 total points
ID: 19693178
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2

809 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