C#: How to export to an excel spreadsheet? using XSLT / LINQ / Other methods?

Question

I need to be able to export some data that is received from a stored procedure in SQL Server 2008. Once the data is returned I need to be able to output it or export it to a new excel spreadsheet.

What is the easiest way of doing this, Can LINQ do this? or am i forced to use XSLT? I presume that i must first convert my data that is returned to XML and then apply XSLT - as XSLT works against XML documents.

XSLT 2 is not available in VS 2008 so we still have to use XSLT 1 - but is this really the way to go or best option?

I would think that it would be possible using an alternative method but maybe i am wrong.

I would really appreciate any advice, tutorials etc

Thanks

Answer 1

for outputting to csv or xml you really don't need any functionality that is not in xpath 1.0 ... its rare that i ran into a situation that required anything more complex.

you could select into an xelement with linq ... however doing this in one statement would mean you cannot validate your data. I usually end up iterating over a collection of elements to handle the edge cases.

HOwever out putting as csv is easier and takes less space than xml ... i think xml is overused tbh.

An alternative (*and i dont recommend it) would be to query sql server from inside the excel document. That was you can select your data directly into a spread sheet. This is fairly old and I don't much like it tbh.

Answer 2

this a code that export an array of object (you can easily fill it with your data) to an excel spreadsheat :

public static void SaveToExcel(object[,] data) 
{ 
    Excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", String.Empty); 
    Excel.ScreenUpdating = false; 
    dynamic workbook = Excel.workbooks; 
    workbook.Add(); 

    dynamic worksheet = Excel.ActiveSheet; 

    const int left = 1; 
    const int top = 1; 
    int height = data.GetLength(0); 
    int width = data.GetLength(1); 
    int bottom = top + height - 1; 
    int right = left + width - 1; 

    if (height == 0 || width == 0) 
        return; 

    dynamic rg = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[bottom, right]]; 
    rg.Value = data; 

    // Set borders 
    for (var i = 1; i <= 4; i++) 
        rg.Borders[i].LineStyle = 1; 

    // Set header view 
    dynamic rgHeader = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[top, right]]; 
    rgHeader.Font.Bold = true; 
    rgHeader.Interior.Color = 189 * (int)Math.Pow(16, 4) + 129 * (int)Math.Pow(16, 2) + 78; 
    rg.EntireColumn.AutoFit(); 

    // Show excel app 
    Excel.ScreenUpdating = true; 
    Excel.Visible = true; 
}