Strip all formatting from Excel file on load

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.

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 = [email protected]"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 = [email protected]"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 = [email protected]"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();


Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Visual Studio Windows Forms C# Interop.Excel - Excel Class Object

I have created a C# Windows Form project in Visual Studio and I am trying to work with an Excel workbook via interop.excel. I have created a custom "excel class" and created an object of it in my Form1. What I am struggling with is whether it is possible to open an excel workbook via a button press, i.e. create the class object from a button press, and then be able to use that object in other button presses. Two versions of code are shown below. One works. One does not. The one that works just opens the Excel workbook when the program is launched. The other attempts to use a button press on the form to open the workbook after the program is launched. In the code that does not work, the object "does not exist in the current context". Any help on how to make the button press code work is most appreciated!

C/C++ vs. Rust: A developer’s perspective

In this post, you'll see the difference between Rust and C/C++ in a developer’s perspective