Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to create the excel report file with C# and I don't know how to create the excel file with C# and display the data to the excel. I want to create the monthly income report according to the order ID and in the end of the records I want to display the total amounts and counts.
My Stored procedure is
CREATE	PROCEDURE	Rpt_MonthlyIncome
	,		@Month		INT
	,		@Year		INT
	,		@FromAmount	INT
	,		@ToAmount		INT
	
	AS
	Select	OrderID
,		'Count'=Count(OrderID)
,		'Total'=SUM(Amount)
,		'Month' = DATENAME(MONTH,OrderDate)
,		'Year' = @Year
From	Order
Where	YEAR(OrderDate) = @Year
AND		MONTH(OrderDate) = @Month
And		Amount BETWEEN @FromAmount AND @ToAmount

Group
by		OrderID
,		DATENAME(MONTH,ReadingDate)


What I have tried:

I don't know how to create excel file to display the data including total amounts and counts.
Posted
Updated 8-Dec-19 23:48pm
Comments
Ashirwad Satapathi 21-Jun-20 11:55am
   
You can try using EPPlus. It's an nuget package available which can get your work done.
Ashirwad Satapathi 21-Jun-20 11:55am
   
You can try using EPPlus. It's an nuget package available which can get your work done.
Rate this:
Please Sign up or sign in to vote.

Solution 1

There are numerous Code Project articles that will help you here is a list[^] of the highest rated
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

You have to:
1. connect to SQL Server by using SqlConnection[^]
2. get from stored procedure data by using SqlCommand[^]
3. read data into SqlDataReader[^]
4. load data into DataTable[^]
5. dump data into Excel sheet (by using Interop for example)

DataTable dt = new DataTable();

string sSqlConn = @"connection to your sql server here";
string sComm = @"Your stored procedure name";

using (SqlConnection oSqlConn = new SqlConnection(sSqlConn))
{
    oSqlConn.Open();
    using (SqlCommand oSqlComm = new SqlCommand(sComm, oSqlConn))
    {
        oSqlComm.CommandType = CommandType.StoredProcedure;
        oSqlComm.CommandText = sComm;
        //do not forget to add parameters to oSqlComm if it's necessary
        //oSqlComm.Parameters.AddWithValue("paramName", paramValue);
        SqlDataReader oSqlDr = oSqlComm.ExecuteReader();
        dt.Load(oSqlDr);
        oSqlComm.Dispose();
    }
    oSqlConn.Close();
    oSqlConn.Dispose();
}

Excel.Application xlApp = new Excel.Application();
//excel file location
string sFileName = @"D:\MyReport.xlsx";
Excel.Workbook xlBook = (Excel.Workbook)xlApp.Workbooks.Add();
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
//column headers
int r = 0;
int c = 0;
foreach(DataColumn dc in dt.Columns)
{
    xlSheet.Range["A1"].Offset[r, c].Value = dc.ColumnName;
    c++;
}
//data
r=2;
foreach(DataRow dr in dt.Rows)
{
    for(c=0;c<dt.Columns.Count;c++)
    {
        xlSheet.Cells[r, c+1] = dr[c] == DBNull.Value ? string.Empty : dr[c].ToString();
    }
    r++;
}
xlBook.SaveAs(sFileName);
xlBook.Close();
xlApp.Quit();
//clean up
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlSheet);
Marshal.ReleaseComObject(xlBook);
Marshal.ReleaseComObject(xlApp);


Above code needs references to:
Excel=Microsoft.Office.Interop.Excel
System.Data
System.Data.OleDb
System.Data.SqlClient
System.Runtime.InteropServices
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100