Usingnamespace – Blog

C#, XUL, Javascript, C++ …

Affichage des posts dans .NET

.NET is quite great to create and publish your own web services, and an eventually a client for it, almost without typing anything.
How to give your client the capacity to choose which server to connect to, in case of a distributed web service ?

When you create your web service in Visual studio, it asks you for the address of the service in order to generate the Service Reference, and then writes this address into the config file.
So, how to dynamically change the address of the targeted web service ?

//We set the server's new address
string server_address = "http://www.newaddress.com:80";
ServiceName.ServiceReferenceSoapClient server = new ServiceName.ServiceReferenceSoapClient();
System.ServiceModel.EndpointAddress addr = new System.ServiceModel.EndpointAddress(server_address);
server.Endpoint.Address = addr;
Uri site = new Uri(server_address);
server.Endpoint.ListenUri = site;
 
//Then use the server as usual :
server.HelloWorld();

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 !