0

I'm trying to store data to an Excel (.xlsx) file. I'm using VS2010 and Office 2013. I thought I'd found what I wanted but no joy.

My program is not opening the .xlsx when clicked nothing happens.

My program throws a NullReferenceException was unhandled error on my first line within the Button2_Click method.

I were trying to use Siddharth Rout's Post here.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace FixedRateReview
{
public partial class Form1 : Form
{
    Microsoft.Office.Interop.Excel.Application xlexcel;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    public Form1()
    {
        InitializeComponent();
    }

    //Open File
    private void button1_Click(object sender, EventArgs e)
    {
        xlexcel = new Excel.Application();

        xlexcel.Visible = true;

        // Open a File
        xlWorkBook = xlexcel.Workbooks.Open("D:\\MyFile.xlsx", 0, true, 5, "", "", true,
        Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlWorkSheet.Cells[1, 1] = "Name";
        xlWorkSheet.Cells[1, 2] = "Bank";
        xlWorkSheet.Cells[1, 3] = "Settlement Date";
        xlWorkSheet.Cells[1, 4] = "Total Lending";
        xlWorkSheet.Cells[1, 5] = "Split #1 Amount";
        xlWorkSheet.Cells[1, 6] = "Split #1 Rate";
        xlWorkSheet.Cells[1, 7] = "Split #1 Expiry Date";            
    }

    //Save Data
    private void button2_Click(object sender, EventArgs e)
    {
        int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
        string theDate = dateTimePicker1.Value.ToShortDateString();
        /////////////////////////////////////////////////////////////////////////////////////////////////
        //MUST WORKOUT DATES FOR EXPIRY. POSSIBALY USE SETTLEMENT DATE PLUSS TERM OMOUND USING CALANDER//
        /////////////////////////////////////////////////////////////////////////////////////////////////

        //Column 1 includes title, first, and last name
        xlWorkSheet.Cells[_lastRow, 1] = comboBox1.Text + " " + textBox10.Text + " " + textBox11.Text;
        //Column 2 includes Bank name
        xlWorkSheet.Cells[_lastRow, 2] = textBox13.Text;
        //Column 3 includes settlement of initial loan
        xlWorkSheet.Cells[_lastRow, 3] = theDate; //textBox17.Text;
        //Column 4 includes total lending
        xlWorkSheet.Cells[_lastRow, 4] = textBox12.Text;
        //Column 5 includes split #1 amount
        xlWorkSheet.Cells[_lastRow, 5] = "$" + textBox1.Text;
        //Column 6 includes Split #1 Rate
        xlWorkSheet.Cells[_lastRow, 6] = textBox4.Text;
        //Column 7 includes split #1 expiry date
        xlWorkSheet.Cells[_lastRow, 7] = textBox14.Text;
    }

    //Once done close and quit Excel
    private void button3_Click(object sender, EventArgs e)
    {
        xlWorkBook.Close(true, misValue, misValue);
        xlexcel.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlexcel);
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Unable to release the Object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

    private void textBox10_MouseClick(object sender, MouseEventArgs e)
    {
        textBox10.Clear();
    }

    private void textBox11_MouseClick(object sender, MouseEventArgs e)
    {
        textBox11.Clear();
    }
}
}
Community
  • 1
  • 1
Daniel Lucas
  • 71
  • 1
  • 1
  • 5
  • 1
    The first line of `button2_Click` references `xlWorkSheet`, which is instantiated in `button1_Click`. Is it possible that you didn't click `button1` before clicking `button2`? – Simon MᶜKenzie Feb 11 '15 at 02:45
  • 1
    Thanks for pointing that out. it triggered my mind to look at the events of `button_1`. My amature mistake I had made the event `button1_Click1`. thanks for the reply. all is working now – Daniel Lucas Feb 12 '15 at 03:58

0 Answers0