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.
Zeebee,
Have a look at these as I recently went through this myself.
There are ways to do this using Transforms and StyleSheets.
https://www.experts-exchange.com/questions/22710372/XSL-Excel-Row-Height-Formatting-Rows-vs-Columns-Cells.html
https://www.experts-exchange.com/questions/22686407/Need-formattable-XSLT.html
https://www.experts-exchange.com/questions/22673584/C-NET-2-0-Export-DataGrid-to-Excel-WINDOWS-APPLICATION.html
BillyDvd
Have a look at these as I recently went through this myself.
There are ways to do this using Transforms and StyleSheets.
https://www.experts-exchange.com/questions/22710372/XSL-Excel-Row-Height-Formatting-Rows-vs-Columns-Cells.html
https://www.experts-exchange.com/questions/22686407/Need-formattable-XSLT.html
https://www.experts-exchange.com/questions/22673584/C-NET-2-0-Export-DataGrid-to-Excel-WINDOWS-APPLICATION.html
BillyDvd
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.OL
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
{
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.OL
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
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://codebetter.com/blogs/peter.van.ooijen/archive/2005/10/20/133409.aspx