如何使用C＃将SQL数据库表数据下载到excel表中？ [英] How to download SQL database table data into excel sheet using C#?
问题描述
if (txtbatch.Text != "" && txtp.Text == "1")
{
DataColumnCollection dcc = dt.Columns;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < dt.Rows.Count + 1; i++)
{
for (int j = 1; j < dt.Columns.Count + 1; j++)
{
if (i == 1)
{
ExcelApp.Cells[i, j] = dcc[j - 1].ToString();
}
else { ExcelApp.Cells[i, j] = dt.Rows[i - 1][j - 1].ToString();
}
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs("D:\\Export\\StudentRegistation.xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
lbl2.Visible = true;
lbl2.Text = "Students Registation File Pilot1 Successfuly Downloaded...!";
}
else
{
lbl2.Text = "No Record found...!";
}
}
我尝试了什么：
使用此代码我无法获得excel表中的第一条记录剩余的记录我得到的我无法理解任何人都可以帮助我
What I have tried:
Using this code i am not able to get first record in excel sheet rest of the record i am getting i am not able to understand can any one help me
推荐答案
您可以使用 EPPlus 库，除了.NET之外不需要任何依赖项：
GitHub - JanKallman / EPPlus：使用.NET创建高级Excel电子表格 [ ^ ]
You could use the EPPlus library, this does not not need any dependencies except .NET:
GitHub - JanKallman/EPPlus: Create advanced Excel spreadsheets using .NET[^]
使用此代码我无法在excel表中获得第一条记录
Using this code i am not able to get first record in excel sheet
您没有获得第一条记录，因为您的循环从1开始，如图所示在下面：
You are not getting the first record because your loop starts at 1 as shown in the following:
for (int j = 1; j < dt.Columns.Count + 1; j++)
然后你里面有以下代码：
and then you have the following code inside it:
ExcelApp.Cells[i, j] = dcc[j - 1].ToString();
请记住， Cells 的索引始于 0 所以我认为这会导致问题，为什么你没有看到第一条记录。请尝试这样的事情：
Keep in mind that index of Cells starts at 0 so I think that causes the issue why you don't see the first record. Try something like this instead:
if (i == 1)
{
ExcelApp.Cells[i-1, j-1] = dcc[j - 1].ToString();
}
else { ExcelApp.Cells[i-1, j-1] = dt.Rows[i - 1][j - 1].ToString();
}
或者您可以将循环更改为0而不是1，因此您的代码现在看起来像这样：
Or you could change your loop to start at 0 instead of 1 so your code would now look something like this:
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (i == 0)
{
ExcelApp.Cells[i, j] = dcc[j].ToString();
}
else { ExcelApp.Cells[i, j] = dt.Rows[i][j].ToString();
}
}
}
如果您仍有意想不到的结果，我建议你调试你的代码，为你的for循环代码设置一个断点，然后进入它以弄清楚数据的传输方式。
If you are still having an unexpected results, I would suggest you to debug your code, set a break point to your for-loop code and then step into it to figure out what the data is being transported.
你已经标记了这个问题是ASP.NET，这意味着你的代码有几个问题。
首先，你不能使用ASP.NET的Office Interop：
You've tagged this question as ASP.NET, which means your code has several problems.
Firstly, you cannot use Office Interop from ASP.NET:
Microsoft目前不推荐，也不推荐支持，从任何无人参与的非交互式客户端应用程序或组件（包括ASP，ASP.NET，DCOM和NT服务）自动化Microsoft Office应用程序，因为Office可能会出现不稳定Office在此环境中运行时的行为和/或死锁。
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
有多种方法可以在服务器上创建Excel电子表格而不使用Office互操作。例如：
