C# DataGridView to Excel File errors

Question

I've coded this following a youtube tutorial, on the tutorial the DataSource is an AOD.NET Entity Data Model where I've used an Access Database instead. I've compiled the code but am getting various errors like

The name 'productBindingSource' does not exist in the current context
or
The name 'DB' does not exist in the current context
or
The type or namespace name 'Product' could not be found

I'm not sure if i've missed adding a reference or if these errors are due to the data source being different?

Visual Studio automatically added the //TODO: This line of code etc... and I changed it to how it's showing in the tutorial.

I'm hoping someone can help show me what i'm doing wrong?

Tutorial: https://www.youtube.com/watch?v=-wGzK1vsqS8

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExportWebsiteData
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            List<Product> list = ((DataParameter)e.Argument).ProductList;
            string filename = ((DataParameter)e.Argument).FileName;
            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 = false;
            int index = 1;
            int process = list.Count;
            //Add Column
            ws.Cells[1, 1] = "Item Number";
            ws.cells[1, 2] = "Model";
            ws.cells[1, 3] = "Manufacturer";
            ws.cells[1, 4] = "Category";
            ws.cells[1, 5] = "Subcategory";
            //
            foreach(Product p in list)
            {
                if (!backgroundWorker.CancellationPending)
                {
                    backgroundWorker.ReportProgress(index++ * 100 / process);
                    ws.Cells[index, 1] = p.ItemNumber.ToString();
                    ws.Cells[index, 2] = p.Model.ToString();
                    ws.Cells[index, 3] = p.Manufacturer.ToString();
                    ws.Cells[index, 4] = p.Category.ToString();
                    ws.Cells[index, 5] = p.SubCategory.ToString();
                }
            }
            //Save file
            ws.SaveAs(filename, XlFileFormat.xlWorkbookdefault, Type.Missing, Type.Missing, true, false, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
            excel.Quit();
        }

        struct DataParameter
        {
            public List<Product> ProductList;
            public string FileName { get; set; }
        }

        DataParameter _inputParameter;

        private void Form1_Load(object sender, EventArgs e)
        {
            using (this.inventoryTableAdapter.Fill(this._Wizard_Data_2016_10_17DataSet.Inventory); = new _Wizard_Data_2016_10_17DataSet())
            {
                productBindingSource.DataSource = DB.Products.ToList();
            }

            // TODO: This line of code loads data into the '_Wizard_Data_2016_10_17DataSet.Inventory' table. You can move, or remove it, as needed.
            //this.inventoryTableAdapter.Fill(this._Wizard_Data_2016_10_17DataSet.Inventory);

        }

        private void backgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar.Value = e.ProgressPercentage;
            lblStatus.Text = string.Format("Processing...{0}", e.ProgressPercentage);
            progressBar.Update();
        }

        private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if(e.Error == null)
            {
                Thread.Sleep(100);
                lblStatus.Text = "Your data has been successfully exported.";
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            if (backgroundWorker.IsBusy)
                return;
            using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Workbook|*.xls" })
            {
                if (sdf.ShowDialog() == DialogResult.OK)
                {
                    _inputParameter.FileName = sfd.FileName;
                    _inputParameter.ProductList = productBindingSource.DataSource as List<product>;
                    progressBar.Minimum = 0;
                    progressBar.Value = 0;
                    backgroundWorker.RunWorkerAsync(_inputParameter);
                }
            }
        }
    }
}

UPDATE:

John's answer did fix my errors but the data-grid is now being populated by the cs code instead of a database. I've made a video explaining the problem in more detail if anyone can let me know what they think is the problem.

https://www.dropbox.com/s/1l5iw1j32a6oroj/C%23Excel.wmv?dl=0

Answer 1

From the posted code there are several errors and typos. It appears there is some problem in the Form1_Load method that gets the data. To break this up and since it appears the data used is a List of Product objects, the code below uses a List of Product objects. The list is then populated with some test data. This test data will be used as a DataSource for the DataGridView. Once the data is in the DataGridView the export button will successfully export the data from the DataGridView to an Excel workbook.

Typos in the above posted code:

ws.Cells[1, 1] = "Item Number";
ws.cells[1, 2] = "Model";

Lower case “C” for cell columns 2,3,4,5 will throw a definition not found error.

ws.SaveAs(filename, XlFileFormat.xlWorkbookdefault, ……);

Above this may depend on version, xlWorkbookdefault should have an uppercase “D”:

XlFileFormat.xlWorkbookDefault   

Last typo is in the ShowDialog for the SaveFileDialog

SaveFileDialog sfd = new SaveFileDialog()

Then the next line tries to use:

sdf.ShowDialog()

The line above should be “sfd” not “sdf”.

Looking at the picture you have posted showing the errors and after watching the video you linked to, there are a couple of things the video may have missed. Starting from the top, it appears there is something called Product that is missing its definition. This looks like a class and from the code it appears the class has Item Number, Model, Manufacturer, Category and SubCategory. I am unsure if the video neglected this or Product is something else I am missing. In this case to fix this missing definition the code below made a Product class with the above properties.

public class Product {
  public string ItemNumber { get; set; }
  public string Model { get; set; }
  public string Manufacturer { get; set; }
  public string Category { get; set; }
  public string SubCategory { get; set; }

  public Product(string iNum, string model, string manuf, string cat, string subCat) {
    ItemNumber = iNum;
    Model = model;
    Manufacturer = manuf;
    Category = cat;
    SubCategory = subCat;
  }

The next 2 errors (XlFileFormat and XlSaveConflictResolution) are generated because you are missing a using statement in the code. This appears to be left out of the video. To fix these two errors, place the line below at the top of the file where the using statements are located.

using Microsoft.Office.Interop.Excel;

error: int: type used in a using statement… see below in form load.

The two errors productBindingSource does not exist. The video placed a BackgroundWorker on to the form but I did not see a BindingSource placed. To fix this… in the designer, select a BindingSource component from the tool box and drop it onto the form, then change its name to productBindingSource.

The “DB” error will be commented out to use the List of product objects. See below Form_Load.

“Thread” error indicates a missing using statement: place the line below with the other using statements.

using System.Threading;

The “sdf” was covered above. The last error is from the line below:

productBindingSource.DataSource as List<product>;

product should have an upper case “P”: List<Product>;

With the above changes made, the only errors should be in the Form_Load method.

private void Form1_Load(object sender, EventArgs e)
{
  using (this.inventoryTableAdapter.Fill(this._Wizard_Data_2016_10_17DataSet.Inventory); = new _Wizard_Data_2016_10_17DataSet())
  {
     productBindingSource.DataSource = DB.Products.ToList();
  }
}

The above line is malformed but appears to be trying to get a DataSet. I will leave this for your consideration later. Since the productBindingSource appears to be taking a List of Product objects, the code replaced this and created a List of Product objects for test data. The Form_Load method is changed to the code below which sets the productBindingSource to the list of test data, then this binding source is assigned as the DataSource for the DataGridView. This is missing in the video.

private void Form2_Load(object sender, EventArgs e) {
  List<Product> list = GetProductList();
  productBindingSource.DataSource = list;
  dataGridView1.DataSource = productBindingSource;
}

With the above changes the code exports as expected. Hope this helps.

private List<Product> GetProductList() {
  List<Product> products = new List<Product>();
  for (int i = 0; i < 14; i++) {
    products.Add(new Product(i.ToString(), "Model_" + i, "Manufacture_" + i, "Cat_" + i, "SubCat_" + i));
  }
  return products;
}

Edit update to get a table from the access DataBase without using a class

System.Data.DataTable dt = new System.Data.DataTable();

public Form2() {
  InitializeComponent();
}

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) {
  //List<Product> list = ((DataParameter)e.Argument).ProductList;
  System.Data.DataTable list = ((DataParameter)e.Argument).ProductList;
  string filename = ((DataParameter)e.Argument).FileName;
  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 = false;
  int index = 1;
  //int process = list.Count;
  int process = list.Rows.Count;
  //Add Column
  ws.Cells[1, 1] = "Item Number";
  ws.Cells[1, 2] = "Model";
  ws.Cells[1, 3] = "Manufacturer";
  ws.Cells[1, 4] = "Category";
  ws.Cells[1, 5] = "Subcategory";
   foreach (DataRow dr in list.Rows) {
    if (!backgroundWorker.CancellationPending) {
      backgroundWorker.ReportProgress(index++ * 100 / process);
      ws.Cells[index, 1] = dr.ItemArray[1].ToString();
      ws.Cells[index, 2] = dr.ItemArray[2].ToString();
      ws.Cells[index, 3] = dr.ItemArray[3].ToString();
      ws.Cells[index, 4] = dr.ItemArray[4].ToString();
      ws.Cells[index, 5] = dr.ItemArray[5].ToString();
    }
  }
  //Save file
  ws.SaveAs(filename, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
  excel.Quit();
}

struct DataParameter {
  public System.Data.DataTable ProductList;
  public string FileName { get; set; }
}

DataParameter _inputParameter;

private void Form2_Load(object sender, EventArgs e) {
  string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test\Test3.accdb";
  using (OleDbConnection olcon = new OleDbConnection(ConnectionString)) {
    using (OleDbDataAdapter adapter = new OleDbDataAdapter()) {

      string command = "SELECT * FROM [Products]";
      //cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

      OleDbCommand cmd = new OleDbCommand(command, olcon);
      //Fill Gridview with Data from Access
      try {
        dt.Clear();
        adapter.SelectCommand = cmd;
        adapter.Fill(dt);
        productBindingSource.DataSource = dt;
        dataGridView1.DataSource = productBindingSource;
      }
      catch (Exception ex) {
        MessageBox.Show(ex.ToString());
      }
      finally {
        olcon.Close();
        var totalWidth = dataGridView1.Columns.GetColumnsWidth(DataGridViewElementStates.None);
      }
    }
  }

}

 private void backgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e) {
  progressBar.Value = e.ProgressPercentage;
  lblStatus.Text = string.Format("Processing...{0}", e.ProgressPercentage);
  progressBar.Update();
}

private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) {
  if (e.Error == null) {
    Thread.Sleep(100);
    lblStatus.Text = "Your data has been successfully exported.";
  }
}

private void btnExport_Click(object sender, EventArgs e) {
  if (backgroundWorker.IsBusy)
    return;
  using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Workbook|*.xls" }) {
    if (sfd.ShowDialog() == DialogResult.OK) {
      _inputParameter.FileName = sfd.FileName;
      //_inputParameter.ProductList = GetProductsList2();
      _inputParameter.ProductList = (System.Data.DataTable)productBindingSource.DataSource;
      progressBar.Minimum = 0;
      progressBar.Value = 0;
      backgroundWorker.RunWorkerAsync(_inputParameter);
    }
  }
}