Tag: excel

Excel dropdown list with number and text

There are two methods;

Data validation drop down

There is a list option in Data validation. If this is combined with a VLOOKUP formula you would be able to convert the selected value into a number.

The steps in Excel 2010/2019 are:

  • Create your list with matching values.
  • On the Data tab choose Data Validation
  • The Data validation form will be displayed
  • Set the Allow dropdown to List
  • Set the Source range to the first part of your list
  • Click on OK (User messages can be added if required)

In a cell enter a formula like this

=VLOOKUP(A5,$E$5:$F$8,2,FALSE)

which will return the matching value from the second part of your list.

Form control drop down

Alternatively, Form controls can be placed on a worksheet. They can be linked to a range and return the position number of the selected value to a specific cell.

If you don’t see Developer tab in Excel ribbon, follow this link;

Show the Developer Tab

The steps in Excel 2010/2019 are:

  • Create your list of data in a worksheet
  • Click on the Developer tab and dropdown on the Insert option
  • In the Form section choose Combo box or List box
  • Use the mouse to draw the box on the worksheet
  • Right click on the box and select Format control
  • The Format control form will be displayed
  • Click on the Control tab
  • Set the Input range to your list of data
  • Set the Cell link range to the cell where you want the number of the selected item to appear
  • Click on OK

Read Excel Data using C#

I am assuming that you already have an Excel file with one “Sheet1” data table in it. Microsoft ACE driver is already installed;

Standard syntax to query Excel table is “SELECT * FROM [Sheet1$]”. Here is the code that does the magic.

// Connect EXCEL sheet with OLEDB using connection string
 using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
        ("select * from [Sheet1$]", conn);
        DataSet excelDataSet = new DataSet();
        objDA.Fill(excelDataSet);
        dataGridView1.DataSource = excelDataSet.Tables[0];
    }
			
	//In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
        // with the help of data adaptor we can load records in dataset		
	
	//write data in EXCEL sheet (Insert data)
 using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange) 
            VALUES ('DEC','40','60','80');";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //exception here
        }
        finally
        {
             conn.Close();
             conn.Dispose();
        }
    }
			
//update data in EXCEL sheet (update data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
	{
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "UPDATE [Sheet1$] SET month = 'DEC' WHERE apple = 74;";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //exception here
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

$ in select statement means that table already exists in Excel File. If we are going to create a new worksheet then we will not use $ sign. OLEDB does not support DELETE query.

Resource;

https://www.codingame.com/playgrounds/9014/read-write-excel-file-with-oledb-in-c-without-interop

All Excel – Programmatically Reading/Writing

ERR: External table is not in the expected format;

The error might be the result of wrong extended properties in connection string. Check code examples below;

Following code will help you to open *.xls files;

string connString = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source ={0}; Extended Properties = 'EXCEL 8.0 XML;HDR=YES;IMEX=1'", fullPathToExcel);
using (OleDbConnection conn = new OleDbConnection(connString))
{
   conn.Open();
  //...any other instruction
}

Following code will help you to open *.xlsx files;

string connString = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source ={0}; Extended Properties = 'EXCEL 12.0 XML;HDR=YES;IMEX=1'", fullPathToExcel);
using (OleDbConnection conn = new OleDbConnection(connString))
{
   conn.Open();
  //...any other instruction
}

Excel ADO.NET properties for different excel version are;

Excel 97-2003 Workbook (.XLS) –> “Excel 80”

Excel 2007-2010 Workbook (.XLSX) –> “Excel 12.0 XML”

Excel 2007-2010 Macro-enabled Workbook (.XLSM) –> “Excel 12.0 MACRO”

This is how you can run a select query;

"SELECT * FROM [StoreSheet$A13:I]"

If you are not able to solve External table is not in the expected format error, consider changing driver. try NPOI or ExcelDataReader.

ExcelDataReader Library

This library doesn’t work with .NET core.

EPPlus Library

EPPlus library works with .NET core.

Working with Excel Files with the help of Script Task

Integration Services provides the Excel connection manager, Excel source, and Excel destination for working with data stored in spreadsheets in the Microsoft Excel file format. The techniques described in this topic use the Script task to obtain information about available Excel databases (workbook files) and tables (worksheets and named ranges).

You can read more here.

How to unprotect the Excel sheet

https://techcommunity.microsoft.com/t5/excel/how-to-unprotect-the-excel-sheet-if-forgot-the-password/m-p/1574559

Common variables and Namespaces used to read Excel files

Variables. Open the Variables window and define the following variables:
1- ExcelFile, of type String. Enter the complete path and filename to an existing Excel workbook.
2 -ExcelTable, of type String. Enter the name of an existing worksheet or named range in the workbook
named in the value of the ExcelFile variable. This value is case-sensitive.
3 – ExcelFileExists, of type Boolean.
4 – ExcelTableExists, of type Boolean.
5 – ExcelFolder, of type String. Enter the complete path of a folder that contains at least one Excel workbook.
6 – ExcelFiles, of type Object.
7 – ExcelTables, of type Object.

Imports statements
You will need following .NET Framework namespaces;
1 – System.IO, for file system operations.
2- System.Data.OleDb, to open Excel files as data sources.

References.
The code that read schema information from Excel files require an additional reference in the script project to the System.Xml namespace.