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();
}
}
}