Try the following:
Add a reference to Microsoft.Excel Object Library (ex: Microsoft.Excel 16.0 Object Library)
- On menu, click Project
- Select Add Reference
- Select COM
- Select Microsoft.Excel xx.x Object Library (ex: Microsoft.Excel 16.0 Object Library)
Add the following using statements:
using System.IO;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;
Option 1:
If you want to set a string value referencing the cell by row,column:
public void ExcelWriteValue(string filename, int rowNum, int colNum, string dataValue, string worksheetName = "")
{
//Write cell value using row number and column number
//*Note: Excel cells, can also be referenced by name, such as "B2"
//
// All indices in Excel (rowNumber, columnNumber, etc...) start with 1
// The format is: <rowNumber>, <columnNumber>
// The top left-most column, is: 1,1
bool specifiedWorksheetFound = false;
object oMissing = System.Reflection.Missing.Value;
Excel.Application excelApp = null;
Excel.Worksheet previousActiveSheet = null;
Excel.Range range = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
//keep track of existing worksheet names
SortedDictionary<string, bool> worksheetDict = new SortedDictionary<string, bool>();
int worksheetCount = 0;
try
{
//create new instance
excelApp = new Excel.Application();
//suppress displaying alerts (such as prompting to overwrite existing file)
excelApp.DisplayAlerts = false;
//set Excel visability
excelApp.Visible = false;
//if writing/updating a large amount of data
//disable screen updating by setting value to false
//for better performance.
//re-enable when done writing/updating data, if desired
//excelApp.ScreenUpdating = false;
if (excelApp != null)
{
if (File.Exists(filename))
{
System.Diagnostics.Debug.WriteLine("'" + filename + "' exists. Existing file will be modified.");
//open existing Excel file
workbook = excelApp.Workbooks.Open(filename);
//get number of existing worksheets
worksheetCount = workbook.Sheets.Count;
if (worksheetCount >= 1)
{
foreach (Excel.Worksheet ws in workbook.Sheets)
{
//System.Diagnostics.Debug.WriteLine("worksheet name: '" + ws.Name + "'");
if (!String.IsNullOrEmpty(worksheetName) && ws.Name == worksheetName)
{
//set value to desired/specified worksheet
worksheet = ws;
//set value
specifiedWorksheetFound = true;
}
if (!worksheetDict.ContainsKey(ws.Name))
{
//add worksheet name to dictionary
//the bool value isn't used, so it's value isn't important
//it exists because a Dictionary requires both a key and a value
worksheetDict.Add(ws.Name, true);
}
}
if (!String.IsNullOrEmpty(worksheetName) && !specifiedWorksheetFound)
{
//specified worksheet not found
System.Windows.Forms.MessageBox.Show("Error: Worksheet '" + worksheetName + "' not found in filename '" + filename + "'.", "Error - Worksheet Not Found", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
else if (!String.IsNullOrEmpty(worksheetName) && specifiedWorksheetFound)
{
//set value
previousActiveSheet = excelApp.ActiveSheet;
//specified worksheet found
//activate desired worksheet
worksheet.Activate();
}
else
{
//set value
previousActiveSheet = excelApp.ActiveSheet;
//no worksheet name specified
//default to 1st worksheet
worksheet = workbook.Sheets[1];
//active worksheet
worksheet.Activate();
}
}
else
{
//no worksheets exist
//add a worksheet and set the value to the new worksheet
worksheet = workbook.Sheets.Add();
}
}
else
{
System.Diagnostics.Debug.WriteLine("'" + filename + "' doesn't exit. Creating new workbook.");
//create new workbook
workbook = excelApp.Workbooks.Add();
//get number of existing worksheets
worksheetCount = workbook.Sheets.Count;
//add a worksheet and set the value to the new worksheet
worksheet = workbook.Sheets.Add();
}
//set cell location that data needs to be written to
//range = worksheet.Cells[rowNum, colNum];
//set value of cell
//range.Value = dataValue;
//set value of cell
worksheet.Cells[rowNum, colNum] = dataValue;
System.Diagnostics.Debug.WriteLine("Info: Value for cell " + rowNum + "," + colNum + " in worksheet '" + worksheet.Name + "' set.");
if (previousActiveSheet != null)
{
//restore active sheet to one that was previously the active sheet
//so that when the user opens the workbook, it will open to the last sheet he/she opened
worksheet = previousActiveSheet;
worksheet.Activate();
}
//save Workbook - if file exists, overwrite it
workbook.SaveAs(filename, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
System.Diagnostics.Debug.WriteLine("Status: Complete. " + DateTime.Now.ToString("HH:mm:ss"));
}
}
catch (Exception ex)
{
string errMsg = "Error: ExcelWriteValue - " + ex.Message;
System.Diagnostics.Debug.WriteLine(errMsg);
if (ex.Message.StartsWith("Cannot access read-only document"))
{
System.Windows.Forms.MessageBox.Show(ex.Message + "Please close the workbook, before trying again.", "Error - Unable To Write To Workbook", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
finally
{
if (workbook != null)
{
//set value
range = null;
previousActiveSheet = null;
worksheet = null;
//close workbook
workbook.Close();
//release all resources
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
}
if (excelApp != null)
{
//close Excel
excelApp.Quit();
//release all resources
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
}
}
}
*Note: The SortedDictionary isn't used. I added it in case you need to keep track of existing worksheet names.
Option 2:
If you want to set a string value referencing the cell by name (ex: "B2"):
public void ExcelWriteValue(string filename, string cellName, string dataValue, string worksheetName = "")
{
//Write cell value using the Excel cell name (ex: B2)
//*Note: Excel cells, can also be referenced by row,column such as "1,2"
//
// All indices in Excel (rowNumber, columnNumber, etc...) start with 1
// The format is: <rowNumber>, <columnNumber>
// The top left-most column, is: 1,1
bool specifiedWorksheetFound = false;
object oMissing = System.Reflection.Missing.Value;
Excel.Application excelApp = null;
Excel.Worksheet previousActiveSheet = null;
Excel.Range range = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
//keep track of existing worksheet names
SortedDictionary<string, bool> worksheetDict = new SortedDictionary<string, bool>();
int worksheetCount = 0;
try
{
//create new instance
excelApp = new Excel.Application();
//suppress displaying alerts (such as prompting to overwrite existing file)
excelApp.DisplayAlerts = false;
//set Excel visability
excelApp.Visible = false;
//if writing/updating a large amount of data
//disable screen updating by setting value to false
//for better performance.
//re-enable when done writing/updating data, if desired
//excelApp.ScreenUpdating = false;
if (excelApp != null)
{
if (File.Exists(filename))
{
System.Diagnostics.Debug.WriteLine("'" + filename + "' exists. Existing file will be modified.");
//open existing Excel file
workbook = excelApp.Workbooks.Open(filename);
//get number of existing worksheets
worksheetCount = workbook.Sheets.Count;
if (worksheetCount >= 1)
{
foreach (Excel.Worksheet ws in workbook.Sheets)
{
//System.Diagnostics.Debug.WriteLine("worksheet name: '" + ws.Name + "'");
if (!String.IsNullOrEmpty(worksheetName) && ws.Name == worksheetName)
{
//set value to desired/specified worksheet
worksheet = ws;
//set value
specifiedWorksheetFound = true;
}
if (!worksheetDict.ContainsKey(ws.Name))
{
//add worksheet name to dictionary
//the bool value isn't used, so it's value isn't important
//it exists because a Dictionary requires both a key and a value
worksheetDict.Add(ws.Name, true);
}
}
if (!String.IsNullOrEmpty(worksheetName) && !specifiedWorksheetFound)
{
//specified worksheet not found
System.Windows.Forms.MessageBox.Show("Worksheet '" + worksheetName + "' not found in filename '" + filename + "'.", "Error - Worksheet Not Found", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
else if (!String.IsNullOrEmpty(worksheetName) && specifiedWorksheetFound)
{
//set value
previousActiveSheet = excelApp.ActiveSheet;
//specified worksheet found
//activate desired worksheet
worksheet.Activate();
}
else
{
//set value
previousActiveSheet = excelApp.ActiveSheet;
//no worksheet name specified
//default to 1st worksheet
worksheet = workbook.Sheets[1];
//active worksheet
worksheet.Activate();
}
}
else
{
//no worksheets exist
//add a worksheet and set the value to the new worksheet
worksheet = workbook.Sheets.Add();
}
}
else
{
System.Diagnostics.Debug.WriteLine("'" + filename + "' doesn't exit. Creating new workbook.");
//create new workbook
workbook = excelApp.Workbooks.Add();
//get number of existing worksheets
worksheetCount = workbook.Sheets.Count;
//add a worksheet and set the value to the new worksheet
worksheet = workbook.Sheets.Add();
}
//set cell location that data needs to be written to
//range = worksheet.Range[cellName];
//set value of cell
//range.Value = dataValue;
//set value of cell
worksheet.Range[cellName].Value = dataValue;
System.Diagnostics.Debug.WriteLine("Info: Value for cell " + cellName + " in worksheet '" + worksheet.Name + "' set.");
if (previousActiveSheet != null)
{
//restore active sheet to one that was previously the active sheet
//so that when the user opens the workbook, it will open to the last sheet he/she opened
worksheet = previousActiveSheet;
worksheet.Activate();
}
//save Workbook - if file exists, overwrite it
workbook.SaveAs(filename, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
System.Diagnostics.Debug.WriteLine("Status: Complete. " + DateTime.Now.ToString("HH:mm:ss"));
}
}
catch (Exception ex)
{
string errMsg = "Error: ExcelWriteValue - " + ex.Message;
System.Diagnostics.Debug.WriteLine(errMsg);
if (ex.Message.StartsWith("Cannot access read-only document"))
{
System.Windows.Forms.MessageBox.Show(ex.Message + "Please close the workbook, before trying again.", "Error - Unable To Write To Workbook", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
finally
{
if (workbook != null)
{
//set value
range = null;
previousActiveSheet = null;
worksheet = null;
//close workbook
workbook.Close();
//release all resources
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
}
if (excelApp != null)
{
//close Excel
excelApp.Quit();
//release all resources
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
}
}
}
*Note: The SortedDictionary isn't used. I added it in case you need to keep track of existing worksheet names.
If this was helpful, please mark it as the answer.