How to write excel file with formulas in C# (Microsoft.Office.Interop.Excel)
download from this link: https://github.com/hamza3344/writeexcel
My fiverr link: https://www.fiverr.com/hamzakhalid178
My Upwork Profile: https://www.upwork.com/freelancers/~0...
Whatsapp Number: +923338672398
if you have any problem comment below. please like, share and subscribe the channel. Contact me on [email protected] if you need any custom software, website and mobile Application.
code:
using System.Diagnostics;
using System.Runtime.InteropServices;
using excel = Microsoft.Office.Interop.Excel;
//intialization
excel._Application oXL;
excel._Workbook oWB;
excel._Worksheet oSheet;
excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
//Start Excel and get Application object.
oXL = new excel.Application();
//Get a new workbook.
oWB = (excel._Workbook)(oXL.Workbooks.Add(""));
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";
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").VerticalAlignment = excel.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Zhang";
saNames[1, 0] = "Tom";
saNames[4, 1] = "Arjun";
//Fill A2:B6 with an array of values (First and Last Names).
oSheet.get_Range("A2", "B6").Value2 = saNames;
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \"\"&B2";
//Fill D2:D6 with a formula(=RAND()*1000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*1000";
oRng.NumberFormat = "$0.00";
//Find average of D2:D6 with a formula
oRng = oSheet.get_Range("E6");
oRng.Formula = "=AVERAGE(D2:D6)";
//AutoFit columns A:D.
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
oWB.SaveAs("C:\\sample\\test.xlsx", excel.XlFileFormat.xlWorkbookDefault, misvalue, misvalue,
false, false, excel.XlSaveAsAccessMode.xlNoChange, misvalue, misvalue, misvalue, misvalue, misvalue);
ReleaseComObject(oRng);
ReleaseComObject(oSheet);
ReleaseComObject(oXL);
ReleaseComObject(oWB);
private void ReleaseComObject(object obj)
{
try
{
Marshal.ReleaseComObject(obj);
obj = null;
}
catch(Exception)
{
obj = null;
}
}