将datagridview转储为ms excel。 [英] Dumping datagridview into ms excel.
问题描述
I have requirement to dump from SQL DB to excel file.There is only one database in SQL and query output in data grid view needs to be dumped in excel sheet.
I have written the following code and used <pre>Microsoft.Office.Interop.Excel.Application Excel。
成功转储DataGridView的所有内容后为了excel工作表，我收到了屏幕截图视频中附带的错误。如下所示，负责转储的代码部分。
我是C＃的新手并且无法解决问题。
用于将datagridview转储到excel的代码如下所示。
.
After all the contents of DataGridView successfully dumped to excel sheet, I am getting the error attached in the screenshot video.Attaching the portion of code responsible for dump as follows.
I am new to C# and not able to troubleshoot the issue since long.
The code used to dump datagridview to excel is as follwos.
//Start exporting to excel file.
-------------------------------------------------------------------------------
using Microsoft.Office.Interop.Excel;
//-------------------------
private void button2_Click(object sender, EventArgs e)
{
/* creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
//----------------------------------------
// Save the file
workbook.SaveAs(("D:\\EXCEL\\AFAS_Report_Created_on_" + DateTime.Now.ToShortDateString() + ".xls"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
MessageBox.Show("Your export in excel is complete.Please close this excel before createing a new one.");
// Exit from the application
app.Visible = true;
app.Quit();===============================*/
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)Excel.ActiveSheet;
Excel.Visible = true;
ws.Cells[1, 1] = "Name of place";
ws.Cells[1, 2] = "Element Name";
ws.Cells[1, 3] = "No";
ws.Cells[1, 4] = "Make Name";
ws.Cells[1, 5] = "Model name";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
ws.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
int rowCount = ((System.Data.DataTable)this.dataGridView1.DataSource).Rows.Count;
MessageBox.Show("You have exported " + rowCount.ToString() + " rows.Please close this excel before creating a new one.");
Excel.Quit();
}
//End of dump to excel file.
//------------------------------
------------------------------------------------- ----------------------------------
它给了，
--------------------------------------- -------------
发生COMException。
mscorlib中发生类型'System.Runtime.InteropServices.COMException'的第一次机会异常.dll文件。附加信息：HRESULT异常：0x800A03EC
---------------------------------- ---------
故障排除提示：
检查异常的错误代码属性，以确定COM对象返回的HRESULT。
---------------------------------------------- ---------------------------
同样在任务中管理多个实例EXCEL.exe正在运行。
有人可以帮我解决这个问题吗？
我尝试了什么：
尝试从不同的代码项目论坛和堆栈溢出。关于这个有很多帖子。但它对我不起作用。我也是C＃的新手。
-----------------------------------------------------------------------------------
It gives,
----------------------------------------------------
COMException occurred.
At first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll. Additional information: Exception from HRESULT:0x800A03EC
-------------------------------------------
Troubleshooting tips:
Check the Error Code property pf the exception to determine the HRESULT returned by the COM object.
-------------------------------------------------------------------------
Also in task manage multiple instances of EXCEL.exe is running.
Can somebody help me out to troubleshoot the issue?
What I have tried:
Tried a from different code project forum and stack overflow. There is a lot of post regarding this.But it did not work for me.I am new to C# too.
推荐答案
看看错误：它告诉你什么要做的事情。
Look at the error: it tells you what to do.
COMException occurred.
At first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll. Additional information: Exception from HRESULT:0x800A03EC
-------------------------------------------
Troubleshooting tips:
Check the Error Code property pf the exception to determine the HRESULT returned by the COM object.
所以使用调试器，在异常对象发生时查看它，并读取错误代码属性。这是Excel返回的HRESULT值，谷歌（可能是十六进制）将告诉你为什么Excel拒绝它。
但快速检查 HRESULT：0x800A03EC 表明这是一个超出范围的Excel地址问题。
So use the debugger, look at the exception object when it occurs, and read the Error Code property. That is the HRESULT value Excel returned, and a google for that (probably in hexadecimal) will tell you why Excel rejected it.
But a quick check on HRESULT:0x800A03EC suggests that it's an out of range Excel address problem.
这篇关于将datagridview转储为ms excel。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
