.NET GENES

Hello, I was assigned to convert a VB.NET project that will generate an excel file using Excel COM interop into C# with ClosedXML as the tool to produce the report. When the project was finished coding and proceed to testing, the logic to get the last row isn't working and throws "Object Reference Not Set To An Instance" error. The VB.NET logic that I need to convert to ClosedXML is presented below. Private Function GetLastRow () As Int32 xlRange = xlWSheet.Range( "A65536" ).End(Excel.XlDirection.xlUp) GetLastRow = xlRange.Row() xlRange = Nothing End Function After reading the ClosedXML documentation and doing some debugging stuff, I successfully converted the Excel Interop function to ClosedXML counterpart. Below is the function that returns the last row used. All I need to do is to check first if last row used is null, then return row one. Otherwise, return the succeeding rows. private int GetLastRow (IXLWorksheet xlWSheet)

Hello, In this article, I'll show you two options to get the cell value of excel using C#. Given that you are using VSTO and have referenced the assembly Microsoft.Office.Interop.Excel. 1 2 3 var cellValue = ( string )(xlWorkSheet.Cells[3, 6] as Excel.Range).Value2; //or this var cellValue = xlWorkSheet.get_Range( "F3" , Type.Missing).Value2; Note: xlWorkSheet is an Excel Worksheet object.

Hello, Here's the VB.NET Version of this post Read Excel 2010 64 bit file using OleDB Provider in C# on how to read Microsoft Excel 64 Bit File On a 64 bit maching using OleDB Provide and VB.NET as the language. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Option Strict On Option Infer On Imports System.Data Public Class Form1 Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase .Load Dim MyConnection As New OleDb.OleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='c:\\temp\\test.xlsx';Extended Properties=Excel 12.0;" ) Dim DtSet As New DataSet() Dim MyCommand As New OleDb.OleDbDataAdapter( "select * from [Sheet1$]" , MyConnection) Try MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConnection.Close() Catch ex As ApplicationException Throw ex Finally End T

Good afternoon! In the forums where I am a contributor, here's a tip on how to read an excel 2010 64/32 bit file using C#. The Operating System of my machine is Windows 8 64 bit. So, here are the steps. 1. Download AccessDatabaseEngine_x64.exe For 32 bit excel on windows 8 64 bit machines, download AccessDatabaseEngine.exe 2. Install it in my laptop. 3. Restart my laptop. 3. Change Active Config of Visual Studio Solution to Debug|Any CPU. For 32 bit excel, choose Debug|X86 4. Clean and Rebuild The Solution. Code: private void Form1_Load( object sender, EventArgs e) { try { System.Data.OleDbOleDbConnection MyConnection; System.DataDataSet DtSet; System.Data.OleDbOleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDbOleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='c:\\temp\\test.xlsx';Extended Properties=Excel 12.0;" ); MyCommand = new System.Data.OleDb

Hello! Assuming in your MS Excel Worksheet you have thousands of cells that contains url and you want them to be a hyperlink. It can be done manually, but tedious. Another solution is to write a VBA (Visual Basic for Applications) Script to do that for you using the Sub procedure below that will convert an Excel cell content to hyperlink. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Sub ChangeCellsToHyperlinks() Dim rng As Range Dim ctr As Integer Dim range_name As String ctr = 2 'starting cell to change Do While ctr <= 10000 'end cell range Set rng = ActiveSheet.Range( "A" & ctr) rng.Parent.Hyperlinks.Add Anchor:=rng, Address:=rng With rng.Font .ColorIndex = 25 .Underline = xlUnderlineStyleSingle End With ctr = ctr + 1 Loop End Sub That's it!

The code below shows how to align a text in a merged cell in excel using C#. chartRange.Merge(Type.Missing); chartRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Source: sanket's blog

Team, Given that you encounter an error such as Object does not contain a definition for get_Range given the original snippet that shows the error below. chartRange = sheet.get_Range(sheet.Cells[row_indicator, column_indicator], sheet.Cells[row_indicator, column_indicator + 11]); The fix to the code snippet above is to cast the cells with object. chartRange = sheet.get_Range(( object )sheet.Cells[row_indicator, column_indicator], ( object )sheet.Cells[row_indicator, column_indicator + 11]); Cheers!

Good evening! Here's how to set MS Excel cell or cells background color using C# Excel.Range class in two different solutions. chartRange = sheet.Cells[ 1 , i + 1 ]; chartRange.Interior.Color = Color.LightBlue; or using get_Range() function and ColorTranslator.ToOle() to set the background color. Excel.Range chartRange; chartRange = xlWorkSheet.get_Range( "b1" , "b3" ); chartRange.Interior.Color = ColorTranslator.ToOle(Color.Red); Where chartRange is a RangeObject.

I have this original snippet which copies datagridview values into the microsoft excel sheet: for ( int i = 0; i < dataGridViewObjects[gcount].Columns.Count; i++) { sheet.Cells[1, i + 1] = dataGridViewObjects[gcount].Columns[i].Name; for ( int row = 0; row < dataGridViewObjects[gcount].Rows.Count; row++) { for ( int column = 0; column < dataGridViewObjects[gcount].Columns.Count; column++) { sheet.Cells[row + 2, column + 1] =dataGridViewObjects[gcount].Rows[row].Cells[column].Value; } } } The code above throws an exception of method type signature is not interop compatible. I was debugging and checking MSDN classes on Workbook. When I debugged at adding cell values, i did catch the exception. The solution is simple. Just add ToString() after the value, since value is of type object. sheet.Cells[row + 2, column + 1] = dataGridViewObjects[gcount].Rows[row].Cells[column].Value.ToString();