• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2273
  • Last Modified:

Excel files without Excel in C#

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
zeebee
Asked:
zeebee
1 Solution
 
jj819430Commented:
0
 
WilliamCommented:
0
 
YZlatCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Dux11Commented:
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
 
zeebeeAuthor Commented:
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
 
WilliamCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now