Excel spreadsheet from LINQ

[Paolo]

I have a WInForms application which generates output from numerous
LINQ(toDataSet) queries to DataGridViews and Pie/Bar Charts.

I want to persist historical transaction analyses in an Excel spreadsheet. I
gather there are various ways of doing this (LINQToXML, connecting to Excel
via OleDBProvider, creating a DataTable from LINQ and adding that to Excel,
using a 3rd party spreadsheet application - I have done some research.)

I guess I am looking for the least complex way (given that I am still
learning C#/LINQ and am no expert on Excel internals) and all the above look
quite complex to me. I am just looking to use Excel to store query output
data - nothing fancy re formatting ( I can do that in Excel itself once the
data is in there) or adding data to an existing spreadsheet - I am happy to
create a new spreadsheet each time and manually copy this to a 'master'
spreadsheet, so my requirements are fairly basic.

So - the 'how long is a piece of string' question - which approach would
best satisfy my requirements?

 

[Jeroen Mostert]

I have a WInForms application which generates output from numerous
LINQ(toDataSet) queries to DataGridViews and Pie/Bar Charts.

I want to persist historical transaction analyses in an Excel spreadsheet. I
gather there are various ways of doing this (LINQToXML, connecting to Excel
via OleDBProvider, creating a DataTable from LINQ and adding that to Excel,
using a 3rd party spreadsheet application - I have done some research.)

I guess I am looking for the least complex way (given that I am still
learning C#/LINQ and am no expert on Excel internals) and all the above look
quite complex to me. I am just looking to use Excel to store query output
data - nothing fancy re formatting ( I can do that in Excel itself once the
data is in there) or adding data to an existing spreadsheet - I am happy to
create a new spreadsheet each time and manually copy this to a 'master'
spreadsheet, so my requirements are fairly basic.

So - the 'how long is a piece of string' question - which approach would
best satisfy my requirements?

The least complex way is to generate CSV or HTML and import that in Excel.
If you do not need to modify existing spreadsheets or output formulas, you
don't need to involve Excel at all.

You can also generate OOXML that Excel can read directly, but this only
works with Excel 2007 and up and is more involved.

The least complex solution after that is probably to use the OLE DB provider.

 

[Paolo]

Jeroen: thank you. As I understand it, I would need to get the output of my
LINQ query into a TextWriter object, something alongthese lines:


class TextFileWriter
{
static void Main(string[] args)
{
// create a writer and open the file
TextWriter tw = new StreamWriter("Data.txt");
tw.WriteLine(myData);
tw.Close();
}
}

Given that my data is coming from an anonymous query (var groupQuery) how
would I get this into the riteLine() method?

 

[Tim Jarvis]

Jeroen: thank you. As I understand it, I would need to get the output
of my LINQ query into a TextWriter object, something alongthese lines:


class TextFileWriter
{
static void Main(string[] args)
{
// create a writer and open the file
TextWriter tw = new StreamWriter("Data.txt");
tw.WriteLine(myData);
tw.Close();
}
}

Given that my data is coming from an anonymous query (var groupQuery)
how would I get this into the riteLine() method?

I'd be inclined to simply use

File.WriteAllLines("MyFile.csv", MyArrayOfStrings);

not sure of the shape of your data, but from your query I guess you
have a list of objects

so you just need to create your string array for each row, something
like

var MyArrayOfStrings = prevResult.Select( o => string.Join(",", new
string[] {o.Field1, o.Field2, o.Field3});

 

[Paolo]

Jeroen: brilliant. Now, am I missing a 'using' statement?
'File.WriteAllLines' is giving "The name 'Files' does not exist in the
current context".

Jeroen: thank you. As I understand it, I would need to get the output
of my LINQ query into a TextWriter object, something alongthese lines:


class TextFileWriter
{
static void Main(string[] args)
{
// create a writer and open the file
TextWriter tw = new StreamWriter("Data.txt");
tw.WriteLine(myData);
tw.Close();
}
}

Given that my data is coming from an anonymous query (var groupQuery)
how would I get this into the riteLine() method?

I'd be inclined to simply use

File.WriteAllLines("MyFile.csv", MyArrayOfStrings);

not sure of the shape of your data, but from your query I guess you
have a list of objects

so you just need to create your string array for each row, something
like

var MyArrayOfStrings = prevResult.Select( o => string.Join(",", new
string[] {o.Field1, o.Field2, o.Field3});

 

[Paolo]

OK, I've added 'using System.IO . My modified code (from your example) is:

var arrayOfReturnedObjects =
groupQuery.Select( o => string.Join(",", new string[]
{o.Category,
o.Count,
o.Mean.ToString(),
o.Sum.ToString()}
));

File.WriteAllLines("AnalysisFile.csv", arrayOfReturnedObjects);

I'm now getting:

"The best overloaded method match for 'System.IO.File.WriteAllLines(string,
string[])' has some invalid arguments" and

"Argument '2': cannot convert from
'System.Collections.Generic.IEnumerable<string>' to 'string[]"

Jeroen: thank you. As I understand it, I would need to get the output
of my LINQ query into a TextWriter object, something alongthese lines:


class TextFileWriter
{
static void Main(string[] args)
{
// create a writer and open the file
TextWriter tw = new StreamWriter("Data.txt");
tw.WriteLine(myData);
tw.Close();
}
}

Given that my data is coming from an anonymous query (var groupQuery)
how would I get this into the riteLine() method?

I'd be inclined to simply use

File.WriteAllLines("MyFile.csv", MyArrayOfStrings);

not sure of the shape of your data, but from your query I guess you
have a list of objects

so you just need to create your string array for each row, something
like

var MyArrayOfStrings = prevResult.Select( o => string.Join(",", new
string[] {o.Field1, o.Field2, o.Field3});

 

[Tim Jarvis]

OK, I've added 'using System.IO . My modified code (from your
example) is:

var arrayOfReturnedObjects =
groupQuery.Select( o => string.Join(",", new string[]
{o.Category,
o.Count,
o.Mean.ToString(),
o.Sum.ToString()}
));

File.WriteAllLines("AnalysisFile.csv",
arrayOfReturnedObjects);

oops, sorry you need to convert the IEnumerable<String> that select
returns to an array....


var arrayOfReturnedObjects =
groupQuery.Select( o => string.Join(",", new string[]
{o.Category,
o.Count,
o.Mean.ToString(),
o.Sum.ToString()}
)).ToArray();

 

[Paolo]

Tim: many thanks. That does what I need perfectly.

OK, I've added 'using System.IO . My modified code (from your
example) is:

var arrayOfReturnedObjects =
groupQuery.Select( o => string.Join(",", new string[]
{o.Category,
o.Count,
o.Mean.ToString(),
o.Sum.ToString()}
));

File.WriteAllLines("AnalysisFile.csv",
arrayOfReturnedObjects);

oops, sorry you need to convert the IEnumerable<String> that select
returns to an array....


var arrayOfReturnedObjects =
groupQuery.Select( o => string.Join(",", new string[]
{o.Category,
o.Count,
o.Mean.ToString(),
o.Sum.ToString()}
)).ToArray();

 

[Tim Jarvis]

Tim: many thanks. That does what I need perfectly.

Cool, happy to help.