I am writing C# code in which at the end I would want to export the resulting array to Excel. For this I looked up example codes to run first mock results to then use for my code. I tried implementing the codes using Interop Excel and while the code does run, can open/create a workbook, open/create a worksheet, rename worksheets, save the results down I cannot alter the cells. Neither value changes nor formatting changes work for me. It saves down a blank Excel file with the changes to the worksheets lets say.
Please see below the example codes I tried to run: I am using Rider but tried the results and failed in Visual Studio as well. Also tried from multiple computers and didn't work. .NET framework is 4.0.3 and the Interop package installed is the most recent 15.0.4795 (while also Microsoft Office Core is installed with the most recent 15.0.0 version). CSV writing did work (see the commented part in the first snippet).
I do not know what else to try, if I can give further context I am very happy to. Thank you for your help!
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
public void ExcelExport()
{
var fileLoc = "...\\test.xlsx";
// CSV writer
// using (TextWriter sw = new StreamWriter(fileLoc))
// {
// string strData = "Zaara";
// float floatData = 324.563F;//Note it's a float not string
// sw.WriteLine("{0},{1}", strData, floatData.ToString("F2"));
// }
var excelApp = new Excel.Application();
excelApp.Visible = true;
excelApp.DisplayAlerts = false;
var workBook = (Excel.Workbook) excelApp.Workbooks.Add();
var reportSheet = (Excel.Worksheet) workBook.Worksheets.Add();
reportSheet.Name = "Report";
reportSheet.Cells[3, 4] = "Contract Name";
reportSheet.Range["A2, A2"].Value2 = 10;
workBook.SaveAs(fileLoc);
workBook.Close();
excelApp.DisplayAlerts = true;
excelApp.Quit();
}
public void ExcelExport2()
{
var fileLoc = "...\\test2.xlsx";
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary";
// Create an array to multiple values at once.
string[,] saNames = new string[5,2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[1, 1] = "Brown";
saNames[2, 0] = "Sue";
saNames[2, 1] = "Thomas";
saNames[3, 0] = "Jane";
saNames[3, 1] = "Jones";
saNames[4, 0] = "Adam";
saNames[4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
oSheet.get_Range("A2", "B6").Value2 = saNames;
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
oWB.SaveAs(fileLoc, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
oXL.Quit();
}
If using Excel interop, try the following:
using Excel = Microsoft.Office.Interop.Excel;
WriteToExcel:
public static void WriteToExcel(string filename, string[,] data)
{
//Write cell value using row number and column number
//*Note: Excel cells, can also be referenced by name, such as "E2" by using "Range"
//
// All indices in Excel (rowNumber, columnNumber, etc...) start with 1
// The format is: <rowNumber>, <columnNumber>
// The top left-most column, is: 1,1
object oMissing = System.Reflection.Missing.Value;
Excel.Application excelApp = null;
Excel.Range range = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
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 = true;
//disable user control while modifying the Excel Workbook
//to prevent user interference
//only necessary if Excel application Visibility property = true
//excelApp.UserControl = false;
//disable
//excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;
//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;
//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();
if (data != null)
{
for (int i = 0; i < data.GetLength(0); i++)
{
int rowNum = i + 1;
for (int j = 0; j < data.GetLength(1); j++)
{
int colNum = j + 1;
//set cell location that data needs to be written to
//range = worksheet.Cells[rowNum, colNum];
//set value of cell
//range.Value = data[i,j];
//set value of cell
worksheet.Cells[rowNum, colNum] = data[i,j];
}
}
}
//enable
//excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;
//excelApp.ScreenUpdating = true;
//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 (WriteToExcel) - " + 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)
{
//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);
}
}
}
Create some test data:
private string[,] CreateTestData()
{
string[,] data = new string[6, 4];
data[0, 0] = "First Name";
data[0, 1] = "Last Name";
data[0, 2] = "Full Name";
data[0, 3] = "Salary";
data[1, 0] = "John";
data[1, 1] = "Smith";
data[2, 0] = "Tom";
data[2, 1] = "Brown";
data[3, 0] = "Sue";
data[3, 1] = "Thomas";
data[4, 0] = "Jane";
data[4, 1] = "Jones";
data[5, 0] = "Adam";
data[5, 1] = "Johnson";
return data;
}
Update:
Here's the complete code:
Create a class (name: HelperExcelInterop.cs)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExcelInteropTest
{
public class HelperExcelInterop
{
public static void WriteToExcel(string filename, string[,] data)
{
//Write cell value using row number and column number
//*Note: Excel cells, can also be referenced by name, such as "E2" by using "Range"
//
// All indices in Excel (rowNumber, columnNumber, etc...) start with 1
// The format is: <rowNumber>, <columnNumber>
// The top left-most column, is: 1,1
object oMissing = System.Reflection.Missing.Value;
Excel.Application excelApp = null;
Excel.Range range = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
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 = true;
//disable user control while modifying the Excel Workbook
//to prevent user interference
//only necessary if Excel application Visibility property = true
//excelApp.UserControl = false;
//disable
//excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;
//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;
//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();
if (data != null)
{
for (int i = 0; i < data.GetLength(0); i++)
{
int rowNum = i + 1;
for (int j = 0; j < data.GetLength(1); j++)
{
int colNum = j + 1;
//set cell location that data needs to be written to
//range = worksheet.Cells[rowNum, colNum];
//set value of cell
//range.Value = data[i,j];
//set value of cell
worksheet.Cells[rowNum, colNum] = data[i,j];
}
}
}
//enable
//excelApp.Calculation = Excel.XlCalculation.xlCalculationManual;
//excelApp.ScreenUpdating = true;
//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 (WriteToExcel) - " + 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)
{
//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);
}
}
}
}
}
On Form1, add a button (name: btnRun)
Double-click the button to add the Click event handler.
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace ExcelInteropTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private string[,] CreateTestData()
{
string[,] data = new string[6, 4];
data[0, 0] = "First Name";
data[0, 1] = "Last Name";
data[0, 2] = "Full Name";
data[0, 3] = "Salary";
data[1, 0] = "John";
data[1, 1] = "Smith";
data[2, 0] = "Tom";
data[2, 1] = "Brown";
data[3, 0] = "Sue";
data[3, 1] = "Thomas";
data[4, 0] = "Jane";
data[4, 1] = "Jones";
data[5, 0] = "Adam";
data[5, 1] = "Johnson";
return data;
}
private void WriteData()
{
string[,] data = CreateTestData();
string filename = System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test123.xlsx");
System.Diagnostics.Debug.WriteLine("filename: " + filename);
HelperExcelInterop.WriteToExcel(filename, data);
}
private void btnRun_Click(object sender, EventArgs e)
{
WriteData();
}
}
}