Excel spreadsheet from LINQ

  • Thread starter Thread starter Paolo
  • Start date Start date
P

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?
 
Paolo said:
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?
Click to expand...

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.
 
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?
 
Paolo said:
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?
Click to expand...

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});
 
Jeroen: brilliant. Now, am I missing a 'using' statement?
'File.WriteAllLines' is giving "The name 'Files' does not exist in the
current context".

Tim Jarvis said:
Paolo said:
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?
Click to expand...

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});
Click to expand...
 
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[]"





Tim Jarvis said:
Paolo said:
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?
Click to expand...

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});
Click to expand...
 
Paolo said:
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);
Click to expand...


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: many thanks. That does what I need perfectly.

Tim Jarvis said:
Paolo said:
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);
Click to expand...


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();
Click to expand...
 
Back
Top