Export dynamic query result to Excel using Microsoft Office Interop Library in Windows Application

Hi RPA,

Please refer below sample.

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat;

Code

C#

int iRowCnt = 0;
public Form1()
{
    InitializeComponent();
}

private void btnExport_Click(object sender, EventArgs e)
{
    string constr = @"Server=.;DataBase=Test;UID=sa;PWD=password";
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = @"CREATE TABLE #FOOD(Id INT, NAME VARCHAR(10))
                        INSERT INTO #FOOD(Id, NAME)
                        SELECT 1,'apple' UNION ALL SELECT 2,'orange' UNION ALL SELECT 3,'tomato'
   
                        CREATE TABLE #PEOPLE(Id INT, NAME VARCHAR(10))
                        INSERT INTO #PEOPLE(Id, NAME)
                        SELECT 1,'Imomdod' UNION ALL SELECT 2,'Muhammad' UNION ALL SELECT 3,'Alisher'
    
                        CREATE TABLE #SALES(Id INT, IdPer INT, IDFOOD INT, price float)
                        INSERT INTO #SALES(Id, IdPer, IDFOOD, price)
                        SELECT 1,2,1, 3 UNION ALL SELECT 2,3,1, 5 UNION ALL SELECT 3,1,3, 11 UNION ALL
                        SELECT 4,3,2, 2 UNION ALL SELECT 5,2,1, 5 UNION ALL SELECT 6,1,2, 8 UNION ALL
                        SELECT 7,2,3, 4 UNION ALL SELECT 8,3,2, 10 UNION ALL SELECT 9,2,3, 15 UNION ALL
                        SELECT 10,1,2, 18 UNION ALL SELECT 11,3,3, 13 UNION ALL SELECT 12,1,3, 19
  
                        DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
                        DECLARE @ColumnName AS NVARCHAR(MAX)
                        SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(NAME) FROM (SELECT DISTINCT [NAME] FROM #FOOD) AS [NAME]
                        DECLARE @ColumnForSum AS NVARCHAR(MAX)
                        SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
                        SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
                        SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
  
                        SET @DynamicPivotQuery = 'SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 0)) Id
                                                    ,PersonName,'+@ColumnName+'
                                                    ,SUM('+@ColumnForSum+') Summa
                                                    FROM (
                                                    SELECT SUM(s.Price) Price,p.NAME as PersonName,f.NAME as FoodName
                                                    from #SALES s
                                                    INNER JOIN #PEOPLE p ON s.IdPer = p.Id
                                                    INNER JOIN #FOOD f ON s.IDFOOD = f.Id
                                                    GROUP BY p.Name,f.Name)t
                                                    pivot
                                                    (
                                                        MAX([Price]) FOR FoodName IN ('+@ColumnName+')
                                                    ) piv GROUP BY PersonName,'+@ColumnName+''
                        EXEC (@DynamicPivotQuery)
  
                        DROP TABLE #SALES
                        DROP TABLE #FOOD
                        DROP TABLE #PEOPLE";
    cmd.Connection = con;
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    try
    {
        DataTable dt = new DataTable();
        sda.Fill(dt);                
        Excel.Application xlAppToUpload = new Excel.Application();
        xlAppToUpload.Workbooks.Add();
        Excel.Worksheet xlWorkSheetToUpload = default(Excel.Worksheet);
        xlWorkSheetToUpload = xlAppToUpload.Sheets["Sheet1"];

        // SHOW THE EXCEL SHEET.
        // SETTING IT VISIBLE WILL ALLOW YOU TO SEE HOW IT WRITES DATA TO EACH CELL.
        xlAppToUpload.Visible = true;

        if (dt.Rows.Count > 0)
        {
            // Adding Header.
            xlWorkSheetToUpload.Cells[3, 1].value = "Id";
            xlWorkSheetToUpload.Cells[3, 2].value = "PersonName";
            xlWorkSheetToUpload.Cells[3, 3].value = "apple";
            xlWorkSheetToUpload.Cells[3, 4].value = "orange";
            xlWorkSheetToUpload.Cells[3, 5].value = "tomato";
            xlWorkSheetToUpload.Cells[3, 6].value = "Summa";
            iRowCnt = 4; // ROW AT WHICH PRINT WILL START.

            // NOW WRITE DATA TO EACH CELL.
            for (var i = 0; i <= dt.Rows.Count - 1; i++)
            {
                xlWorkSheetToUpload.Cells[iRowCnt, 1].value = dt.Rows[i]["Id"];
                xlWorkSheetToUpload.Cells[iRowCnt, 2].value = dt.Rows[i]["PersonName"];
                xlWorkSheetToUpload.Cells[iRowCnt, 3].value = dt.Rows[i]["apple"];
                xlWorkSheetToUpload.Cells[iRowCnt, 4].value = dt.Rows[i]["orange"];
                xlWorkSheetToUpload.Cells[iRowCnt, 5].value = dt.Rows[i]["tomato"];
                xlWorkSheetToUpload.Cells[iRowCnt, 6].value = dt.Rows[i]["Summa"];
                iRowCnt = iRowCnt + 1;
            }

            // FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
            xlAppToUpload.ActiveCell.Worksheet.Cells[4, 1].AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList2);
            xlAppToUpload = null;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "You got an Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally { }
}

Screenshot