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 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.
sw.Write(string.Format("\"=MAX((C{0}/1000)*(D{0}/1000);E{0})\"", lc));
It takes extra backslashes to escape the quotes, but Excel does calculate the maximum just as intended.
A CSV cannot contain layout, charts, cross-sheet references and the like. But for something as simple as the thing I needed it's hasta la Visto.