I am getting an error while exporting to Excel
I want to transfer the data from dataGridView to Excel. For this, I added the "Microsoft.Office.Interop.Excel" plug-in to my project. I wrote the necessary codes properly. But when I run the project and click the "Export to Excel" button, I get an error. What should I do?
This is my code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DuyuTekStokApp
{
public partial class StockAll : Form
{
SqlConnection connect = new SqlConnection("Data Source=DESKTOP-L01MAVE;Initial Catalog='StockControl';Integrated Security=True");
public StockAll()
{
InitializeComponent();
}
public void VerileriGoster(string Veriler)
{
SqlDataAdapter da = new SqlDataAdapter(Veriler, connect);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridViewAll.DataSource = ds.Tables[0];
}
public void changeHeader()
{
dataGridViewAll.Columns[0].HeaderText = "Stok Kodu";
dataGridViewAll.Columns[1].HeaderText = "Stok Cinsi";
dataGridViewAll.Columns[2].HeaderText = "Barkod Numarası";
dataGridViewAll.Columns[3].HeaderText = "Birimi";
dataGridViewAll.Columns[4].HeaderText = "Grubu";
dataGridViewAll.Columns[5].HeaderText = "Tarih";
dataGridViewAll.Columns[6].HeaderText = "Açıklama";
dataGridViewAll.Columns[7].HeaderText = "Sınıfı";
dataGridViewAll.Columns[8].HeaderText = "Değişim Tarihi";
dataGridViewAll.Columns[9].HeaderText = "Adeti";
dataGridViewAll.Columns[10].HeaderText = "Kritik Miktar";
dataGridViewAll.Columns[11].HeaderText = "Resim";
}
private void StockAll_Load(object sender, EventArgs e)
{
connect.Open();
SqlCommand cmd1 = new SqlCommand("SELECT COUNT(*) FROM StockCard", connect);
SqlDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
labelToplam.Text = dr1[0].ToString();
}
connect.Close();
connect.Open();
SqlCommand cmd2 = new SqlCommand("SELECT SUM(StokAdet) FROM StockCard", connect);
SqlDataReader dr2 = cmd2.ExecuteReader();
while (dr2.Read())
{
labelGenel.Text = dr2[0].ToString();
}
connect.Close();
VerileriGoster("SELECT StokKodu, StokCinsi, StokBarkod, StokBirim, StokGrup, StokTarih, StokAciklama, StokSinif, StokSonTarih, StokAdet, StokKritik, StokResim FROM StockCard ORDER BY StokKodu ASC");
//VerileriGoster(@"SELECT dbo.StockCard.StokKodu, dbo.StockCard.StokBarkod, dbo.StockCard.StokBirim, dbo.StockCard.StokGrup, dbo.StockCard.StokTarih, dbo.StockCard.StokResim, dbo.StockMove.MoveAdet, dbo.StockMove.MoveMevcut, dbo.StockMove.MoveSinif, dbo.StockMove.MoveTarih
// FROM dbo.StockMove INNER JOIN
// dbo.StockCard ON dbo.StockMove.StokID = dbo.StockCard.StokID ORDER BY StokKodu ASC ");
changeHeader();
}
private void dataGridViewAll_CellClick(object sender, DataGridViewCellEventArgs e)
{
int secilen = dataGridViewAll.SelectedCells[0].RowIndex;
labelUrun.Text = dataGridViewAll.Rows[secilen].Cells[9].Value?.ToString();
labelKrit.Text = dataGridViewAll.Rows[secilen].Cells[10].Value.ToString();
byte[] bytes = (byte[])dataGridViewAll.Rows[secilen].Cells[11].Value;
MemoryStream ms = new MemoryStream(bytes);
pictureBoxResim.Image = Image.FromStream(ms);
}
private void btnExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Stok Bilgisi";
for (int i = 1; i < dataGridViewAll.Columns.Count + 1; i++)
{
worksheet.Cells[i, 1] = dataGridViewAll.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridViewAll.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewAll.Columns.Count; i++)
{
worksheet.Cells[i + 2, j + 1] = dataGridViewAll.Rows[i].Cells[j].Value.ToString();
}
}
var saveFileDialog = new SaveFileDialog();
saveFileDialog.FileName = "StokBilgisi";
saveFileDialog.DefaultExt = ".xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
app.Quit();
}
}
}
This is the error message I get:
System.InvalidCastException
HResult = 0x80004002
Message = COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' could not be assigned to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with the IID '{000208D5-0000-0000-C000-000000000046}' failed with the following error: Error loading type library / DLL. (HRESULT returned exception: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).
Source = mscorlib
StackTrace:
at System.StubHelpers.StubHelpers.GetCOMIPFromRCW (Object objSrc, IntPtr pCPCMD, IntPtr & ppTarget, Boolean & pfNeedsRelease)
at Microsoft.Office.Interop.Excel.ApplicationClass.get_Workbooks ()
at SenseTekStokApp.StockAll.btnExcel_Click (Object sender, EventArgs e) in D: \ yNs \ VisualTutorials \ SenseTekStokApp \ SenseTekStokApp \ StockAll.cs: line 84
at System.Windows.Forms.Control.OnClick (EventArgs e)
at System.Windows.Forms.Button.OnClick (EventArgs e)
at System.Windows.Forms.Button.OnMouseUp (MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp (Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc (Message & m)
at System.Windows.Forms.ButtonBase.WndProc (Message & m)
at System.Windows.Forms.Button.WndProc (Message & m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage (Message & m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc (Message & m)
at System.Windows.Forms.NativeWindow.DebuggableCallback (IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW (MSG & msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop (IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run (Form mainForm)
at SenseTekStokApp.Program.Main () in D: \ yNs \ VisualTutorials \ SenseTekStokApp \ SenseTekStokApp \ Program.cs: line 19
asked on Stack Overflow Nov 13, 2020 by 
Yunus Emre Söğüt
Yunus Emre Söğüt2 Answers
In essence, to use EPPlus (per comments) to save your grid to an excel file:
private void btnExcel_Click(object sender, EventArgs e)
{
var saveFileDialog = new SaveFileDialog();
saveFileDialog.FileName = "StokBilgisi";
saveFileDialog.DefaultExt = ".xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
using (ExcelPackage pck = new ExcelPackage(new FileInfo(saveFileDialog.FileName)))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("StokBilgisi");
ws.Cells["A1"].LoadFromDataTable(dataGridViewAll.DataSource as DataTable, true);
pck.Save();
}
}
This is the code from the linked question/answer, adjusted for your context by insertion of dataGridViewAll.DataSource as DataTable to retrieve the datatable you bound to the grid when you called dataGridViewAll.DataSource = ds.Tables[0]; in VerileriGoster, plus some other fluff like selecting the filename to save with a SaveFileDialog
Caius JardYou can try the following steps to convert datagirdview to excel.
First, please install nuget-package Microsoft.Office.Interop.Excel.
Second, please using it in the code.
using Excel=Microsoft.Office.Interop.Excel;
Third, I suggest that you can convert datagirdview to datatable first then you can convert datatable to excel.
private void button1_Click(object sender, EventArgs e)
{
DataTable table = (DataTable)dataGridView1.DataSource;
ExportToExcel(table);
}
public void ExportToExcel(DataTable tbl )
{
try
{
if (tbl == null || tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");
// load excel, and create a new workbook
var excelApp = new Excel.Application();
var workbook=excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (var i = 0; i < tbl.Columns.Count; i++)
{
workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
}
// rows
for (var i = 0; i < tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (var j = 0; j < tbl.Columns.Count; j++)
{
workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
}
}
try
{
var saveFileDialog = new SaveFileDialog();
saveFileDialog.FileName = "StokBilgisi";
saveFileDialog.DefaultExt = ".xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
excelApp.Quit();
Console.WriteLine("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
answered on Stack Overflow Nov 16, 2020 by 
Jack J Jun - MSFT
Jack J Jun - MSFTUser contributions licensed under CC BY-SA 3.0