If you ever wondered how to open an excel file and write data into it from your .NET project, here is the solution :
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application xlApp = new Excel.ApplicationClass();
xlApp.Visible = true; //If true, the Excel window will be visible during the process, if not, it is done in background
Excel.Workbook xlWBook = xlApp.Workbooks.Open(filename, 0, false, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false, System.Reflection.Missing.Value, false, false, false);//Opens the Excel sheets
Excel.Worksheet xlSheet = (Excel.Worksheet)xlWBook.Worksheets[1]; //Get the first Sheet of the Excel file
//Write Something at Cell A1 :
string value = "Test";
Excel.Range excelCell2 = (Excel.Range)xlSheet.get_Range("A1:A1", Type.Missing); //Selects a Cell A1
excelCell2.Cells.Value2 = value;
//Duplicate a line
int line = 5; //Will copy line 5 and paste the copy above
Excel.Range R1 = xlSheet.get_Range(xlSheet.Cells[line, 1], xlSheet.Cells[line, 1]).EntireRow;
xlSheet.get_Range(xlSheet.Cells[line, 1], xlSheet.Cells[line, 1]).EntireRow.Copy(Type.Missing);
R1.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, true);
//Here we copy the cells' formats, style and formulas.
R1.EntireRow.PasteSpecial(Excel.XlPasteType.xlPasteFormulasAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, true)More info about the XlPasteType and XlPasteSpecialOperation :
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.pastespecial%28VS.80%29.aspx
Also helpful :
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.applicationclass%28office.11%29.aspx
Troubleshooting:
If like me you have an Operating System and an Excel which uses different money and number formats (for me French OS and US Excel), you will encounter the following exception while opening Workbook :
System.Runtime.InteropServices.COMException (0×80028018): Ancien format ou bibliothèque de types non valide. (Exception de HRESULT : 0×80028018 (TYPE_E_INVDATAREAD))
à Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)
You can fix this bug by setting the current thread culture to « en – US » :
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");Be careful though, if you do so it changes to whole behaviour of you program concerning number and money formats. For instance the float.Parse() function will behave differently if the new Culture has a different number formatting than yours !
