Office Interop in .Net
Office interop cannot be avoided, if you are developing applications for Windows platform. It is 90% sure that your customers will ask for MS Office integration when the project grows or your marketing people will add office integration regardless the client actually needs or not.
Before
Open XML era (docx,xlsx etc...) developers had only one way to interact with office files from .Net or any other applications. Yes the Office interop APIs. In .Net it can be done by Adding reference to the interop assemblies.Only issue I had faced is the non type safe APIs provided. Another issue is the memory leak due to unmanaged objects but that can be avoided by using
Marshal.ReleaseComObject method.
If you are working in .Net for 2 years and don't know what is Office interop and how to use it to create an Excel file programatically, just
google for it and try at least once. Let me know, if you are not able to get any result in google
Adding formula to Excel cells from C#.Net
Formula in Excel should be known to everyone who uses Windows operating system seriously. It can be added from the Excel application by prefixing a '=' symbol to mathematical or logical expressions when we edit cells. For example if you want to display the sum of values in cells E1 to E9 in E10 cell, write the below into E10 cell
=SUM(E1:E9)
The same can be done from our .Net application as well. Below is an code which adds little more complicated formulae to the cell
Application app = new Application();
Workbook wb= app.Workbooks.Open(excelFilePath);
Worksheet firstSheet = wb.Sheets[1] as Worksheet;
string message = "There is a 'No' in the range B1-B10";
string formula = @"=IF(COUNTIF(B1:B10,""No"") >0,"""+message+@""","""")";
firstSheet.Cells[1, 1]= formula;//This is cell A1
wb.Save();
wb.Close();
Marshal.ReleaseComObject(app);
This means
- It will create an excel file in the path mentioned in the variable excelFilePath
- It will add a formula in the cell A1.
- The formula will be '=IF(COUNTIF(B1:B10,"No") > 0,"There is a No in the range B1-B10","")
- The formula means, if any cell in the range B1 to B10 contains "No" string it will show the message "There is a No in the range B1-B10" in the cell A1
This is simple right. But what about the below ie I want to set a big text to the A1 cell when there is a "No" in the range.
Application app = new Application();
Workbook wb= app.Workbooks.Open(excelFilePath);
Worksheet firstSheet = wb.Sheets[1] as Worksheet;
string message = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur";
string formula = @"=IF(COUNTIF(B1:B10,""No"") >0,"""+message+@""","""")";
firstSheet.Cells[1, 1]= formula;
wb.Save();
wb.Close();
Marshal.ReleaseComObject(app);
This simply fails with a message
A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
Additional information: Exception from HRESULT: 0x800A03EC
Any idea? It doesn't talk about what happened inside the code.Or at least what is the actual error?
This is the time to show the your practical debugging skills. Most of the developers complain here that, its just not working. But the intelligent developers think in a different way. Is it because of long message? Can't I assign long text via interop? or Excel itself just don't accept this much big message?
Most of them ends up in trying out the same scenario in Excel. Technically this is called as "reproducing the issue in a stripped down environment". Whoever try it in Excel will get the actual problem.
Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&)
Yes there is a limitation in Excel and it itself giving the solution. So lets modify our code to have concatenation.
Application app = new Application();
Workbook wb = app.Workbooks.Open(excelFilePath);
Worksheet firstSheet = wb.Sheets[1] as Worksheet;
string message = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. ";
message+=@"""&"""+"Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur";
string formula = @"=IF(COUNTIF(B1:B10,""No"") >0,""" + message + @""","""")";
firstSheet.Cells[1, 1] = formula;
wb.Save();
wb.Close();
Marshal.ReleaseComObject(app);
Looks good. But in real scenario things will not be easy like this. Your application may be getting the message from some other sources which requires a splitting on the fly.
Happy debugging.
