Posts

Showing posts with the label Excel

Donate

Get Microsoft Excel Cell Value Using C# And Office.Interop.Excel

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.

Read Excel 2010 64 Bit File On A 64 Bit Machine Using OleDB Provider In VB.NET

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

Read Excel 2010 64 Bit File On A 64 Bit Machine Using OleDB Provider In C#

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

Change Cell Url Contents To Hyperlinks Using VBA In MS Excel

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!

How To Align Text A Merged Cell In Excel Using C#

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

Object does not contain a definition for get_Range (MS Excel) In C#

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!

How To Set Background Color Of Excel Cell Using C#

Image
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.

Method's type signature is not Interop compatible. [Copying DataGridValues to excel cell object]]

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();

Donate