This post originated from an RSS feed registered with .NET Buzz
by Peter van Ooijen.
Original Post: Building an Excel sheet in C# the easy way
Feed Title: Peter's Gekko
Feed URL: /error.htm?aspxerrorpath=/blogs/peter.van.ooijen/rss.aspx
Feed Description: My weblog cotains tips tricks and opinions on ASP.NET, tablet PC's and tech in general.
My application had to produce a worksheet for Excel. At first I took Visual Studio Tools for Office (aka Visto). Very very nice but it gave me a hard time in registration problems with the Office COM servers used. Fearing a deployment nightmare I took the easy classical road: just create a CSV text file.
A CSV file is quite simple
A text line stands is a row in the sheet. CR/LF is the next row
Columns are delimited by ;
In a row you only specify the columns you need. A CSV file can be jagged
The good thing is that you're not limited to outputting plain data. The contents of every cell in an Excel sheet can be represented as a plain string. An Excel formula is a plain string which starts with "=". This snippet writes a 3 column sheet-row for every row of data. You will recognize the second column as a formula.
StreamWriter sw = new StreamWriter(saveFileDialog1.FileName, false);
DataView dv = new DataView(data.Regel);
lc++; for (int i = 0; i < dv.Count; i++) {
DataRowView dr = dv[i];
sw.Write(dr["Breed"]); sw.Write(";");
if (isTweeDimensionaal) sw.Write(string.Format("=(G{0}/1000)*(H{0}/1000)", lc)); else sw.Write(string.Format("=G{0}/1000", lc)); sw.Write(";"); sw.Write(dr["Aantal"]); sw.Write(";");
sw.WriteLine(); lc++;
}
The ; character not only separates the columns in a CSV file it is also separates the parameters of an Excel function. These would lead to an unexpected splitting of a column. To prevent this you wrap the contents of a column in double string quotes.
Previous Topic
