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
