使用C＃创建报告excel文件 [英] Create report excel file with C#

问题描述

我想用C＃创建excel报告文件，我不知道如何使用C＃创建excel文件并将数据显示到excel。我想根据订单ID创建月收入报告，并在记录的最后我想显示总金额和计数。

我的存储过程是

  CREATE   PROCEDURE  Rpt_MonthlyIncome 
， @ Month   INT  
， @ Year   INT  
， @ FromAmount   INT  
， @ ToAmount   INT  
 
  AS  
 选择 OrderID 
，'  Count' = Count（OrderID）
，'  Total' = SUM（金额）
，' 月' = DATENAME（MONTH，OrderDate）
，' 年' =  @年 
 来自 订单 
 其中​​年（OrderDate）=  @ Year  
  AND  MONTH（OrderDate）=  @ Month  
 和金额 BETWEEN   @ FromAmount   AND   @ ToAmount  
 
 组 
 按订单ID 
，DATENAME（MONTH，ReadingDate）

我尝试了什么：



我不知道如何创建excel文件Ť o显示数据，包括总金额和计数。

解决方案

有很多代码项目文章可以帮助你这里是 list [ ^ ]评分最高的

你必须：

1.使用SqlConnection [ ^ ]

2.从存储中获取程序数据使用 SqlCommand [< a href =https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx\"target =_ blanktitle =New Window> ^ ]

3.将数据读入 SqlDataReader [ ^ ]

4.将数据加载到DataTable [ ^ ]

5.将数据转储到Excel工作表中（例如使用Interop ）

 DataTable dt =  new  DataTable（）; 
 
  string  sSqlConn =  @ 在这里连接到你的SQL Server; 
  string  sComm =  @ 您的存储过程名称; 
 
 使用（SqlConnection oSqlConn =  new  SqlConnection（sSqlConn））
 {
 oSqlConn.Open（）; 
 使用（SqlCommand oSqlComm =  new  SqlCommand（sComm，oSqlConn））
 { 
 oSqlComm.CommandType = CommandType.StoredProcedure; 
 oSqlComm.CommandText = sComm; 
  //  如果必要，请不要忘记向oSqlComm添加参数 
 < span class =code-comment> //   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文件位置 
  string  sFileName =  @  D：\ MyDeport.xlsx; 
 Excel.Workbook xlBook =（Excel.Workbook）xlApp.Workbooks.Add（）; 
 Excel.Worksheet xlSheet =（Excel.Worksheet）xlBook.Worksheets [ 1 ]; 
  //  列标题 
  int  r =  0 ; 
  int  c =  0 ; 
  foreach （DataColumn dc  in  dt.Columns）
 {
 xlSheet.Range [  A1]。偏移量[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（）; 
  //  清理 
 GC.Collect（）; 
 GC.WaitForPendingFinalizers（）; 
 Marshal.ReleaseComObject（xlSheet）; 
 Marshal.ReleaseComObject（xlBook）; 
 Marshal.ReleaseComObject（xlApp）; 
 

以上代码需要参考：

 Excel = Microsoft.Office.Interop.Excel 
 System.Data 
 System.Data.OleDb 
 System.Data.SqlClient 
 System.Runtime.InteropServices 

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.

解决方案

There are numerous Code Project articles that will help you here is a list[^] of the highest rated

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

这篇关于使用C＃创建报告excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！