Move to the right row reading an Excel file in c#

Question

I need to read an .xlsx file without use 3rd-part library.

I do in this way:

private void Upload(string filename)
{
    FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);

    // Reading from a OpenXml Excel file (2007 format; *.xlsx)
    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

    //DataSet - The result of each spreadsheet will be created in the result.Tables
    excelReader.IsFirstRowAsColumnNames = false;
    DataSet result = excelReader.AsDataSet();

    //5. Data Reader methods
   string value = GetValue(0, 0, excelReader);

    //6. Free resources (IExcelDataReader is IDisposable)
    excelReader.Close();
}

I don't know how to read in the right cell. The problem isn't the column position (I can use , but the row position.

public string GetValue(int row, int col, IExcelDataReader excelReader)
{
    string s;

    // ??? how to positionate on the right row?

    s = excelReader(column_value);

    return s;
}

I have found the correct solution, in this discussion: http://stackoverflow.com/questions/33389393/reading-excel-in-c-sharp-where-some-columns-are-empty — Gioce90, Oct 28 '15 at 12:07

rotgers · Answer 1 · 2015-10-28T12:14:04.793

I created, and use, the following class to read the first sheet from the .xlsx or .xls file:

/// <summary>
/// Reads a table from a spreadsheet.
/// </summary>
public sealed class XlsxReader
{
    /// <summary>
    /// Loads an xlsx file from a filepath into the datatable.
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns>Returns a DataTable with data from the first sheet.</returns>
    public static DataTable FromXLSX(string filePath)
    {
        try
        {
            // Create the new datatable.
            DataTable dtexcel = new DataTable();

            // Define the SQL for querying the Excel spreadsheet.
            bool hasHeaders = true;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;

            // If it is a xlsx file
            if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=1;\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1;\"";

            // Create connection
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            // Get scheme
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            DataRow schemaRow = schemaTable.Rows[0];

            // Get sheet name
            string sheet = schemaRow["TABLE_NAME"].ToString();
            if (!sheet.EndsWith("_"))
            {
                // Query data from the sheet
                string query = "SELECT  * FROM [" + sheet + "]";
                OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                dtexcel.Locale = CultureInfo.CurrentCulture;

                // Fill the datatable.
                daexcel.Fill(dtexcel);
            }

            // Close connection.
            conn.Close();

            // Set the datatable.
            return dtexcel;
        }
        catch { throw; }
    }
}

It returns the sheet as a DataTable.

No, you have to get the correct row. It is possible to use the column name like `dt.Rows[2]["MyColumn"].ToString()` or just the column number, like: `dt.Rows[2][2].ToString()`. — rotgers, Oct 26 '15 at 13:13
Doesn't work with the column names... if I use `dt.Rows[i]["A"].ToString();` doesn't work... if I use an index like `dt.Rows[i][j].ToString();` then works. — Gioce90, Oct 26 '15 at 14:15
The A is not the column name. If I have a table `Name | Description` then Description is valid to use in syntax. If I want the description for the first row, `dt.Rows[0]["Description"].ToString()`. It uses the data in the first row to create the column names in the `DataTable`. — rotgers, Oct 26 '15 at 14:17
Okay I understood... so now I have another problem, 'cause I need to use the... coordinates, if we want call them in this way.. — Gioce90, Oct 26 '15 at 14:21
Just create a function that takes a `letter` as input and outputs a `number`. Or make it accept a cell name like `B2` and let it output 'coordinates'. You can even make a function that takes the column name (`string`) `B2` and the `DataTable` as `input`, and let it `output` just that cell value as `string`. — rotgers, Oct 26 '15 at 14:25

Move to the right row reading an Excel file in c#

1 Answers1