I have written a class that deals with opening, writing to, and closing an Excel file.
The methods in the Excel class are being called by a different call. When I call the addDatatoExcel
method the first time, everything is working, however when I call it again, I get a "System.NullReferenceException: Excel C#". VB says I am passing a null object. I know what the error is I just can not figure out how to prevent it.
public class ExcelFile
{
public static ExcelFile C1;
private string excelFilePath = "C:\\Final.xlsx";
private int rowNumber = 1; // define first row number to enter data in Excel
Excel.Application excelApp;
Excel.Workbook excelWorkbook;
Excel.Worksheet excelWorksheet;
int ExcelCounter = 0;
int checker = 0;
string str4 = "h";
public void openExcel()
{
excelApp = null;
excelApp = new Excel.Application(); // create Excel App
excelWorkbook = excelApp.Workbooks.Add();
excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets.Add();
}
public void addDataToExcel(string str4)
{
excelWorksheet.Cells[5, 1] = str4;
ExcelCounter++;
}
public void closeExcel()
{
excelWorkbook.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorksheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorkbook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
I want to be able to add data to the first same Excel sheet that was created.
This is the method that calls the functions in the Excel class.
public void AddTextToLabel(string str)
{
ExcelFile _Excel = new ExcelFile();
if (flag == 1) // Celsius
{
//list Adding Function 1;
temp = Double.Parse(str);
x++;
list.Add(x, temp);
zedGraphControl1.Invalidate();
CreateChart(zedGraphControl1);
//Example();
if (Excelcounter == 0)
{
_Excel.openExcel();
Excelcounter++;
}
_Excel.addDataToExcel(str);
if (Excelcounter == 15)
{
_Excel.closeExcel();
}
}
}
I have checked and the variable str
that is being passed to addDatatoExcel
is not null. There are other parameters that change to null when the function is called the second time. Is this because I am not saving the Excel file between every call to AddDatatoExecl();
1st call to function
2nd call to function
I am new to C# so please be specific. Thanks
You are opening the Excel, only the first time:
// Each time, you call this:
ExcelFile _Excel = new ExcelFile();
...
//The first time, this is called
if (Excelcounter == 0)
{
_Excel.openExcel();
Excelcounter++;
}
Then the next time you call the AddTextToLabel
function
// A new instance is created
ExcelFile _Excel = new ExcelFile();
// The excel counter is not 0 so the _Excel.openExcel() will not be called.
if (Excelcounter == 0)
Change your code to this:
// Now your excel file is an instance member
// as is your Excelcounter
ExcelFile _Excel = new ExcelFile();
public void AddTextToLabel(string str)
{
if (flag == 1) // Celcuis
{
//list Adding Function 1;
temp = Double.Parse(str);
x++;
list.Add(x, temp);
zedGraphControl1.Invalidate();
CreateChart(zedGraphControl1);
//Example();
if (Excelcounter == 0)
{
_Excel.openExcel();
Excelcounter++;
}
_Excel.addDataToExcel(str);
if (Excelcounter == 15)
{
_Excel.closeExcel();
}
}
My OpenExcel Opens a whole new Excel Sheet. If i was to add that code to where you said. It would just open multiple sheets and only add one value in each sheet.
The solution I'm proposing, opens only once.