Strip all formatting from Excel file on load

I want to strip all formatting (borders etc) from an Excel file when it is loaded before it fills the data into a data table.

When i run my code, the updateExcel_Click part updates column C with what is in ConsigneeCombobox for each row, however if the file i am processing has formatting, for example 10 rows with borders but only 8 of them rows with text it updates all 10 because of the formatting

EDIT

Rather than stripping out the borders, what about in the updateExcel_Click part only adding it to rows that have text in?

private void updateExcel_Click(object sender, EventArgs e)
{
    for (int i = 0; i < dataGridView1.RowCount - 1; i++)
    {
        dataGridView1[2, i].Value = ConsigneeCombo.Text;
    }
}

My current GetData code is:

    private DataTable GetData(string userFileName)
    {
        string dirName = Path.GetDirectoryName(userFileName);
        string fileName = Path.GetFileName(userFileName);
        string fileExtension = Path.GetExtension(userFileName);
        string connection = string.Empty;
        string query = string.Empty;
        switch (fileExtension)
        {
            case ".xls":
                connection = $@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={userFileName};" +
                             "Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=1\"";
                string sheetNamexls;
                using (OleDbConnection con = new OleDbConnection(connection))
                {
                    con.Open();
                    var dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    sheetNamexls = dtSchema.Rows[0].Field<string>("TABLE_NAME");
                }
            if (sheetNamexls.Length &lt;= 0) throw new InvalidDataException("No sheet found.");

            query = $"SELECT * FROM [{sheetNamexls}]";
            break;

        case ".xlsx":
            connection = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={userFileName};" +
                         "Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=1\"";
            string sheetName;
            using (OleDbConnection con = new OleDbConnection(connection))
            {
                con.Open();
                var dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                sheetName = dtSchema.Rows[0].Field&lt;string&gt;("TABLE_NAME");

            }

            if (sheetName.Length &lt;= 0) throw new InvalidDataException("No sheet found.");

            query = $"SELECT * FROM [{sheetName}]";
            break;
        case ".csv":
            connection = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dirName};" +
                           "Extended Properties=\"text; HDR=Yes; IMEX=1; FMT=Delimited\"";
            query = $"SELECT * FROM [{fileName}]";
            break;
    }
    return FillData(connection, query);
}

I have tried adding the ClearFormats(); method but cannot get it to work.

Full code:

using System;

using System.Data;

using System.Text;

using System.Windows.Forms;

using System.IO;

using System.Data.OleDb;

using System.Data.SqlClient;

namespace DrayIn

{

public partial class DrayIn : Form

{

public DrayIn()

{

InitializeComponent();

using (SqlConnection sqlConnection = new SqlConnection(“ConnDetails”))

{

SqlCommand sqlCmd = new SqlCommand(@“SELECT Id

FROM ref_bizunit_scoped sh

WHERE sh.role = ‘SHIPPER’

AND sh.Life_Cycle_State = ‘ACT’

ORDER BY ID”, sqlConnection);

sqlConnection.Open();

SqlDataReader sqlReader = sqlCmd.ExecuteReader();

while (sqlReader.Read())

{

ConsigneeCombo.Items.Add(sqlReader[“Id”].ToString());

}

sqlReader.Close();

}

ConsigneeCombo.SelectedIndex = 0;

}
    private DataTable FillData(string connection, string query)
    {
        DataTable dataTable = new DataTable();

#c-sharp #excel