使用C＃编码从Sql Server将数据导出到Excel文件 [英] Export Data to Excel file from Sql Server using C# coding

问题描述

 System.Data.DataTable dtMainSQLData =  new  System.Data.DataTable（）; 
 da.Fill（dtMainSQLData）; 
 DataColumnCollection dcCollection = dtMainSQLData.Columns; 
  //  将数据导出到EXCEL表 
 Microsoft.Office.Interop.Excel ._Application ExcelApp =  new  Excel.Application（）; 
 ExcelApp.Application.Workbooks.Add（Type.Missing）; 
 
 
  //   ExcelApp.Cells.CopyFromRecordset（objRS）;  
  for （ int  i =  1 ; i <  dtMainSQLData.Rows.Count +  1 ; i ++）
 { 
 
  for （ int  j =  1 ; j <  dtMainSQLData.Columns.Count +  1 ; j ++）
 {
  if （i ==  1 ）
 
 ExcelApp.Cells [i，j] = dcCollection [j  -   1 ]。ToString（）; 
 
  else  
 
 ExcelApp.Cells [i，j] = dtMainSQLData.Rows [i  -   1 ] [j  -   1 ]。ToString（）; 
 
 
 
} 
} 
 ExcelApp.ActiveWorkbook.SaveCopyAs（  E：\\1.xls）; 
 ExcelApp.ActiveWorkbook.Saved =  true ; 
 ExcelApp.Quit（）; 
 MessageBox.Show（ 数据导出成功）; 

它生成成功，但需要像A1 B2那样放置柱子和范围，垂直





格式如下。我想要这种格式。

 header L U R 
 
 
 Sr.no A B C 1 1 1 
 1特殊A A 11 1 0 0 
 2特殊A B 12 5 0 0 
特殊A 7 1 1 
 
 3特殊B A 13 5 1 0 
 4特殊B B 14 0 0 3 
特殊B 5 1 3 

解决方案

参见这些链接

出口

将数据从数据库表导出到ASP.NET MVC中的Excel文件 [ ^ ]



和For Import



将数据从Excel文件导入ASP.NET MVC 4中的数据库表 [ ^ ]

安装包ClosedXML

安装包EnterpriseLibrary.Data



新XLWorkbook

{

Worksheets = {new DatabaseProviderFactory（）。Create（DatabaseContext）。ExecuteDataSet（new SqlCommand（SELECT * FROM Bilhete WHERE NOT OlxId IS NULL））。Tables [0]}

。.SaveAs（

％WEBROOT_PATH％\\relatorios \\ {DateTime.Today.ToString（yyyy-MM-dd）}。xlsx）;

System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
da.Fill(dtMainSQLData);
DataColumnCollection dcCollection = dtMainSQLData.Columns;
// Export Data into EXCEL Sheet
Microsoft.Office.Interop.Excel._Application ExcelApp = new Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);


// ExcelApp.Cells.CopyFromRecordset(objRS);
for (int i = 1; i < dtMainSQLData.Rows.Count + 1; i++)
{
   
    for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
    {
        if (i == 1)

            ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
            
        else

            ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString();

        

    }
}
ExcelApp.ActiveWorkbook.SaveCopyAs("E:\\1.xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Data Exported Successfully");

Its generated Successfully but need to put Column and range wise like A1 B2 and vertically


Format is given below. I want in This format.

header				L	U	R
						
						
Sr.no	A	B	C	1	1	1
1	Particular A	A	11	1	0	0
2	Particular A	B	12	5	0	0
	Particular A			7	1	1
						
3	Particular B	A	13	5	1	0
4	Particular B	B	14	0	0	3
	Particular B			5	1	3

解决方案

See These Link
For Export
Export Data from Database Table to Excel File in ASP.NET MVC[^]

and For Import

Import Data from Excel File to Database Table in ASP.NET MVC 4[^]

Install-Package ClosedXML
Install-Package EnterpriseLibrary.Data

new XLWorkbook
{
Worksheets = { new DatabaseProviderFactory().Create("DatabaseContext").ExecuteDataSet(new SqlCommand("SELECT * FROM Bilhete WHERE NOT OlxId IS NULL")).Tables[0] }
}.SaveAs(

"%WEBROOT_PATH%\\relatorios\\{DateTime.Today.ToString("yyyy-MM-dd")}.xlsx");

这篇关于使用C＃编码从Sql Server将数据导出到Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！