c# winforms VS2017 linking with excel: error when closing form
see image
This is considered a duplicate post as the exact codes are used for another question (about 5 years ago.).Basically, I get the error System.NullreferenceException: 'Object reference not set to an instance on an object'. for line xlWorkBook.Close(true, misValue, misValue);
each time I closed the form (see image). Based on the original question: Inserting multiple textbox data into an Excel file, I cannot find others having the same problem as me. The code I've used is the same as the link:
`using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace Vehicledettry
{
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();
}
private void button1_Click(object sender, EventArgs e)
{
xlexcel = new Excel.Application();
xlexcel.Visible = true;
// Open a File
xlWorkBook = xlexcel.Workbooks.Open(" C:\vehicledet.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] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
}
private void button2_Click(object sender, EventArgs e)
{
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = Plate Number.Text;
xlWorkSheet.Cells[_lastRow, 2] = Car Model.Text;
xlWorkSheet.Cells[_lastRow, 3] = Car Brand.Text;
xlWorkSheet.Cells[_lastRow, 4] = Mileage.Text;
}
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();
}
}
}
I've tried copying xlWorkBook = xlexcel.Workbooks.Open(" C:\vehicledet.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, 1, 0);
after every button event clicked but the same error kept occurring. Hope that I could get some help. Thanks.
c# excel winforms
add a comment |
see image
This is considered a duplicate post as the exact codes are used for another question (about 5 years ago.).Basically, I get the error System.NullreferenceException: 'Object reference not set to an instance on an object'. for line xlWorkBook.Close(true, misValue, misValue);
each time I closed the form (see image). Based on the original question: Inserting multiple textbox data into an Excel file, I cannot find others having the same problem as me. The code I've used is the same as the link:
`using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace Vehicledettry
{
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();
}
private void button1_Click(object sender, EventArgs e)
{
xlexcel = new Excel.Application();
xlexcel.Visible = true;
// Open a File
xlWorkBook = xlexcel.Workbooks.Open(" C:\vehicledet.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] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
}
private void button2_Click(object sender, EventArgs e)
{
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = Plate Number.Text;
xlWorkSheet.Cells[_lastRow, 2] = Car Model.Text;
xlWorkSheet.Cells[_lastRow, 3] = Car Brand.Text;
xlWorkSheet.Cells[_lastRow, 4] = Mileage.Text;
}
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();
}
}
}
I've tried copying xlWorkBook = xlexcel.Workbooks.Open(" C:\vehicledet.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, 1, 0);
after every button event clicked but the same error kept occurring. Hope that I could get some help. Thanks.
c# excel winforms
3
That's a lot of bad practices. Not hard to get this exception, simply click button3 before clicking button1.
– Hans Passant
Nov 12 at 13:29
Hi Hans. When I click button 3, the error will popup and other buttons cannot be pressed. Anyway, button 3 is meant for closing the program so if I pressed it first, all my data will not even be saved (no diff since I cannot even run the program :( ). I'll keep trying.
– musa
Nov 13 at 1:02
1
Instead of using Excel interop you could use a library like EPPlus to generatexlsx
files directly. The API is more or less the same but you don't have to worry about installing Excel, closing and properly disposing the COM object.
– Panagiotis Kanavos
Nov 13 at 8:44
add a comment |
see image
This is considered a duplicate post as the exact codes are used for another question (about 5 years ago.).Basically, I get the error System.NullreferenceException: 'Object reference not set to an instance on an object'. for line xlWorkBook.Close(true, misValue, misValue);
each time I closed the form (see image). Based on the original question: Inserting multiple textbox data into an Excel file, I cannot find others having the same problem as me. The code I've used is the same as the link:
`using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace Vehicledettry
{
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();
}
private void button1_Click(object sender, EventArgs e)
{
xlexcel = new Excel.Application();
xlexcel.Visible = true;
// Open a File
xlWorkBook = xlexcel.Workbooks.Open(" C:\vehicledet.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] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
}
private void button2_Click(object sender, EventArgs e)
{
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = Plate Number.Text;
xlWorkSheet.Cells[_lastRow, 2] = Car Model.Text;
xlWorkSheet.Cells[_lastRow, 3] = Car Brand.Text;
xlWorkSheet.Cells[_lastRow, 4] = Mileage.Text;
}
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();
}
}
}
I've tried copying xlWorkBook = xlexcel.Workbooks.Open(" C:\vehicledet.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, 1, 0);
after every button event clicked but the same error kept occurring. Hope that I could get some help. Thanks.
c# excel winforms
see image
This is considered a duplicate post as the exact codes are used for another question (about 5 years ago.).Basically, I get the error System.NullreferenceException: 'Object reference not set to an instance on an object'. for line xlWorkBook.Close(true, misValue, misValue);
each time I closed the form (see image). Based on the original question: Inserting multiple textbox data into an Excel file, I cannot find others having the same problem as me. The code I've used is the same as the link:
`using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace Vehicledettry
{
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();
}
private void button1_Click(object sender, EventArgs e)
{
xlexcel = new Excel.Application();
xlexcel.Visible = true;
// Open a File
xlWorkBook = xlexcel.Workbooks.Open(" C:\vehicledet.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] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
}
private void button2_Click(object sender, EventArgs e)
{
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = Plate Number.Text;
xlWorkSheet.Cells[_lastRow, 2] = Car Model.Text;
xlWorkSheet.Cells[_lastRow, 3] = Car Brand.Text;
xlWorkSheet.Cells[_lastRow, 4] = Mileage.Text;
}
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();
}
}
}
I've tried copying xlWorkBook = xlexcel.Workbooks.Open(" C:\vehicledet.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, 1, 0);
after every button event clicked but the same error kept occurring. Hope that I could get some help. Thanks.
c# excel winforms
c# excel winforms
edited Nov 12 at 10:21
Partha
320214
320214
asked Nov 12 at 9:52
musa
235
235
3
That's a lot of bad practices. Not hard to get this exception, simply click button3 before clicking button1.
– Hans Passant
Nov 12 at 13:29
Hi Hans. When I click button 3, the error will popup and other buttons cannot be pressed. Anyway, button 3 is meant for closing the program so if I pressed it first, all my data will not even be saved (no diff since I cannot even run the program :( ). I'll keep trying.
– musa
Nov 13 at 1:02
1
Instead of using Excel interop you could use a library like EPPlus to generatexlsx
files directly. The API is more or less the same but you don't have to worry about installing Excel, closing and properly disposing the COM object.
– Panagiotis Kanavos
Nov 13 at 8:44
add a comment |
3
That's a lot of bad practices. Not hard to get this exception, simply click button3 before clicking button1.
– Hans Passant
Nov 12 at 13:29
Hi Hans. When I click button 3, the error will popup and other buttons cannot be pressed. Anyway, button 3 is meant for closing the program so if I pressed it first, all my data will not even be saved (no diff since I cannot even run the program :( ). I'll keep trying.
– musa
Nov 13 at 1:02
1
Instead of using Excel interop you could use a library like EPPlus to generatexlsx
files directly. The API is more or less the same but you don't have to worry about installing Excel, closing and properly disposing the COM object.
– Panagiotis Kanavos
Nov 13 at 8:44
3
3
That's a lot of bad practices. Not hard to get this exception, simply click button3 before clicking button1.
– Hans Passant
Nov 12 at 13:29
That's a lot of bad practices. Not hard to get this exception, simply click button3 before clicking button1.
– Hans Passant
Nov 12 at 13:29
Hi Hans. When I click button 3, the error will popup and other buttons cannot be pressed. Anyway, button 3 is meant for closing the program so if I pressed it first, all my data will not even be saved (no diff since I cannot even run the program :( ). I'll keep trying.
– musa
Nov 13 at 1:02
Hi Hans. When I click button 3, the error will popup and other buttons cannot be pressed. Anyway, button 3 is meant for closing the program so if I pressed it first, all my data will not even be saved (no diff since I cannot even run the program :( ). I'll keep trying.
– musa
Nov 13 at 1:02
1
1
Instead of using Excel interop you could use a library like EPPlus to generate
xlsx
files directly. The API is more or less the same but you don't have to worry about installing Excel, closing and properly disposing the COM object.– Panagiotis Kanavos
Nov 13 at 8:44
Instead of using Excel interop you could use a library like EPPlus to generate
xlsx
files directly. The API is more or less the same but you don't have to worry about installing Excel, closing and properly disposing the COM object.– Panagiotis Kanavos
Nov 13 at 8:44
add a comment |
2 Answers
2
active
oldest
votes
(Edited per feedback) See below. Data in textbox will automatically go into existing excel file of choice. Huge credit to https://support.microsoft.com/en-in/help/302084/how-to-automate-microsoft-excel-from-microsoft-visual-c-net for the help
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Windows.Forms;
namespace exceltry
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application xlexcel = null;
Excel._Workbook xlWorkbook = null;
Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//start excel and get application object
oXL = new Microsoft.Office.Interop.Excel.Application();
//open existing workbook
oWB = oXL.Workbooks.Open("C:\vehicledet.xlsx");
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = false;
xlexcel.UserControl = true;
//can save update to same file but wont close unless user press x button
xlWorkbook.Save();
MessageBox.Show("Data saved successfully");
xlWorkbook.Close();
//allow excel to auto close by itself
oXL.Quit();
Marshal.ReleaseComObject(oXL);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
1
This will throw an NRE immediatelly after the button is clicked -xlexcel
is null. Another serious problem is that it doesn't use ausing
block to ensure the COM object is disposed. If an error is thrown, Excel will remain open
– Panagiotis Kanavos
Nov 13 at 8:43
1
Hi guys. Updated the codings. Thanks all for the feedback
– masyita shariff
Nov 15 at 2:57
add a comment |
Recently, I have a project also that will send the data into an excel file but not similarly as yours, my data came from a text file and someone on this site help me to figure out how to export the values of my Array in Excel File. In your case maybe, this code will Help.
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application exc = new Excel.Application();
exc.Interactive = true;
var excelTemplate = "CompareResult.xlsx"; //Change it with your filename
string FromPath = Path.GetFullPath(excelTemplate); //Get the full path of your excel
//file.
Excel.Workbook wb = exc.Workbooks.Open(FromPath);
Excel.Worksheet sh = wb.Sheets[1];
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
sh.Cells[row, 1].Value2 = textBox1.Text;
sh.Cells[row, 2].Value2 = textBox1.Text;
sh.Cells[row, 3].Value2 = textBox1.Text;
sh.Cells[row, 4].Value2 = textBox1.Text;
wb.Save(); \Saving the file when changing
wb.Close();
exc.Quit();
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53259587%2fc-sharp-winforms-vs2017-linking-with-excel-error-when-closing-form%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
(Edited per feedback) See below. Data in textbox will automatically go into existing excel file of choice. Huge credit to https://support.microsoft.com/en-in/help/302084/how-to-automate-microsoft-excel-from-microsoft-visual-c-net for the help
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Windows.Forms;
namespace exceltry
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application xlexcel = null;
Excel._Workbook xlWorkbook = null;
Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//start excel and get application object
oXL = new Microsoft.Office.Interop.Excel.Application();
//open existing workbook
oWB = oXL.Workbooks.Open("C:\vehicledet.xlsx");
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = false;
xlexcel.UserControl = true;
//can save update to same file but wont close unless user press x button
xlWorkbook.Save();
MessageBox.Show("Data saved successfully");
xlWorkbook.Close();
//allow excel to auto close by itself
oXL.Quit();
Marshal.ReleaseComObject(oXL);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
1
This will throw an NRE immediatelly after the button is clicked -xlexcel
is null. Another serious problem is that it doesn't use ausing
block to ensure the COM object is disposed. If an error is thrown, Excel will remain open
– Panagiotis Kanavos
Nov 13 at 8:43
1
Hi guys. Updated the codings. Thanks all for the feedback
– masyita shariff
Nov 15 at 2:57
add a comment |
(Edited per feedback) See below. Data in textbox will automatically go into existing excel file of choice. Huge credit to https://support.microsoft.com/en-in/help/302084/how-to-automate-microsoft-excel-from-microsoft-visual-c-net for the help
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Windows.Forms;
namespace exceltry
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application xlexcel = null;
Excel._Workbook xlWorkbook = null;
Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//start excel and get application object
oXL = new Microsoft.Office.Interop.Excel.Application();
//open existing workbook
oWB = oXL.Workbooks.Open("C:\vehicledet.xlsx");
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = false;
xlexcel.UserControl = true;
//can save update to same file but wont close unless user press x button
xlWorkbook.Save();
MessageBox.Show("Data saved successfully");
xlWorkbook.Close();
//allow excel to auto close by itself
oXL.Quit();
Marshal.ReleaseComObject(oXL);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
1
This will throw an NRE immediatelly after the button is clicked -xlexcel
is null. Another serious problem is that it doesn't use ausing
block to ensure the COM object is disposed. If an error is thrown, Excel will remain open
– Panagiotis Kanavos
Nov 13 at 8:43
1
Hi guys. Updated the codings. Thanks all for the feedback
– masyita shariff
Nov 15 at 2:57
add a comment |
(Edited per feedback) See below. Data in textbox will automatically go into existing excel file of choice. Huge credit to https://support.microsoft.com/en-in/help/302084/how-to-automate-microsoft-excel-from-microsoft-visual-c-net for the help
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Windows.Forms;
namespace exceltry
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application xlexcel = null;
Excel._Workbook xlWorkbook = null;
Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//start excel and get application object
oXL = new Microsoft.Office.Interop.Excel.Application();
//open existing workbook
oWB = oXL.Workbooks.Open("C:\vehicledet.xlsx");
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = false;
xlexcel.UserControl = true;
//can save update to same file but wont close unless user press x button
xlWorkbook.Save();
MessageBox.Show("Data saved successfully");
xlWorkbook.Close();
//allow excel to auto close by itself
oXL.Quit();
Marshal.ReleaseComObject(oXL);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
(Edited per feedback) See below. Data in textbox will automatically go into existing excel file of choice. Huge credit to https://support.microsoft.com/en-in/help/302084/how-to-automate-microsoft-excel-from-microsoft-visual-c-net for the help
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Windows.Forms;
namespace exceltry
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application xlexcel = null;
Excel._Workbook xlWorkbook = null;
Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//start excel and get application object
oXL = new Microsoft.Office.Interop.Excel.Application();
//open existing workbook
oWB = oXL.Workbooks.Open("C:\vehicledet.xlsx");
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = false;
xlexcel.UserControl = true;
//can save update to same file but wont close unless user press x button
xlWorkbook.Save();
MessageBox.Show("Data saved successfully");
xlWorkbook.Close();
//allow excel to auto close by itself
oXL.Quit();
Marshal.ReleaseComObject(oXL);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
edited Nov 15 at 2:57
answered Nov 13 at 8:19
masyita shariff
336
336
1
This will throw an NRE immediatelly after the button is clicked -xlexcel
is null. Another serious problem is that it doesn't use ausing
block to ensure the COM object is disposed. If an error is thrown, Excel will remain open
– Panagiotis Kanavos
Nov 13 at 8:43
1
Hi guys. Updated the codings. Thanks all for the feedback
– masyita shariff
Nov 15 at 2:57
add a comment |
1
This will throw an NRE immediatelly after the button is clicked -xlexcel
is null. Another serious problem is that it doesn't use ausing
block to ensure the COM object is disposed. If an error is thrown, Excel will remain open
– Panagiotis Kanavos
Nov 13 at 8:43
1
Hi guys. Updated the codings. Thanks all for the feedback
– masyita shariff
Nov 15 at 2:57
1
1
This will throw an NRE immediatelly after the button is clicked -
xlexcel
is null. Another serious problem is that it doesn't use a using
block to ensure the COM object is disposed. If an error is thrown, Excel will remain open– Panagiotis Kanavos
Nov 13 at 8:43
This will throw an NRE immediatelly after the button is clicked -
xlexcel
is null. Another serious problem is that it doesn't use a using
block to ensure the COM object is disposed. If an error is thrown, Excel will remain open– Panagiotis Kanavos
Nov 13 at 8:43
1
1
Hi guys. Updated the codings. Thanks all for the feedback
– masyita shariff
Nov 15 at 2:57
Hi guys. Updated the codings. Thanks all for the feedback
– masyita shariff
Nov 15 at 2:57
add a comment |
Recently, I have a project also that will send the data into an excel file but not similarly as yours, my data came from a text file and someone on this site help me to figure out how to export the values of my Array in Excel File. In your case maybe, this code will Help.
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application exc = new Excel.Application();
exc.Interactive = true;
var excelTemplate = "CompareResult.xlsx"; //Change it with your filename
string FromPath = Path.GetFullPath(excelTemplate); //Get the full path of your excel
//file.
Excel.Workbook wb = exc.Workbooks.Open(FromPath);
Excel.Worksheet sh = wb.Sheets[1];
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
sh.Cells[row, 1].Value2 = textBox1.Text;
sh.Cells[row, 2].Value2 = textBox1.Text;
sh.Cells[row, 3].Value2 = textBox1.Text;
sh.Cells[row, 4].Value2 = textBox1.Text;
wb.Save(); \Saving the file when changing
wb.Close();
exc.Quit();
add a comment |
Recently, I have a project also that will send the data into an excel file but not similarly as yours, my data came from a text file and someone on this site help me to figure out how to export the values of my Array in Excel File. In your case maybe, this code will Help.
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application exc = new Excel.Application();
exc.Interactive = true;
var excelTemplate = "CompareResult.xlsx"; //Change it with your filename
string FromPath = Path.GetFullPath(excelTemplate); //Get the full path of your excel
//file.
Excel.Workbook wb = exc.Workbooks.Open(FromPath);
Excel.Worksheet sh = wb.Sheets[1];
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
sh.Cells[row, 1].Value2 = textBox1.Text;
sh.Cells[row, 2].Value2 = textBox1.Text;
sh.Cells[row, 3].Value2 = textBox1.Text;
sh.Cells[row, 4].Value2 = textBox1.Text;
wb.Save(); \Saving the file when changing
wb.Close();
exc.Quit();
add a comment |
Recently, I have a project also that will send the data into an excel file but not similarly as yours, my data came from a text file and someone on this site help me to figure out how to export the values of my Array in Excel File. In your case maybe, this code will Help.
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application exc = new Excel.Application();
exc.Interactive = true;
var excelTemplate = "CompareResult.xlsx"; //Change it with your filename
string FromPath = Path.GetFullPath(excelTemplate); //Get the full path of your excel
//file.
Excel.Workbook wb = exc.Workbooks.Open(FromPath);
Excel.Worksheet sh = wb.Sheets[1];
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
sh.Cells[row, 1].Value2 = textBox1.Text;
sh.Cells[row, 2].Value2 = textBox1.Text;
sh.Cells[row, 3].Value2 = textBox1.Text;
sh.Cells[row, 4].Value2 = textBox1.Text;
wb.Save(); \Saving the file when changing
wb.Close();
exc.Quit();
Recently, I have a project also that will send the data into an excel file but not similarly as yours, my data came from a text file and someone on this site help me to figure out how to export the values of my Array in Excel File. In your case maybe, this code will Help.
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application exc = new Excel.Application();
exc.Interactive = true;
var excelTemplate = "CompareResult.xlsx"; //Change it with your filename
string FromPath = Path.GetFullPath(excelTemplate); //Get the full path of your excel
//file.
Excel.Workbook wb = exc.Workbooks.Open(FromPath);
Excel.Worksheet sh = wb.Sheets[1];
int _lastRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row + 1;
sh.Cells[row, 1].Value2 = textBox1.Text;
sh.Cells[row, 2].Value2 = textBox1.Text;
sh.Cells[row, 3].Value2 = textBox1.Text;
sh.Cells[row, 4].Value2 = textBox1.Text;
wb.Save(); \Saving the file when changing
wb.Close();
exc.Quit();
answered Nov 14 at 6:00
Alexis Villar
34111
34111
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53259587%2fc-sharp-winforms-vs2017-linking-with-excel-error-when-closing-form%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
That's a lot of bad practices. Not hard to get this exception, simply click button3 before clicking button1.
– Hans Passant
Nov 12 at 13:29
Hi Hans. When I click button 3, the error will popup and other buttons cannot be pressed. Anyway, button 3 is meant for closing the program so if I pressed it first, all my data will not even be saved (no diff since I cannot even run the program :( ). I'll keep trying.
– musa
Nov 13 at 1:02
1
Instead of using Excel interop you could use a library like EPPlus to generate
xlsx
files directly. The API is more or less the same but you don't have to worry about installing Excel, closing and properly disposing the COM object.– Panagiotis Kanavos
Nov 13 at 8:44