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


c-sharp excel

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

C. C++. C#. 차이점 알려드림. 5분 순삭. Explain C.C++.C#. Like I'm Five

C. C++. C#. 차이점 알려드림. 5분 순삭. Explain C.C++.C#. Like I'm Five. 아시죠? C는 C++.C# 줄인말이 아니라는거?

Dicey Issues in C/C++

C/C++ problems. If you are familiar with C/C++then you must have come across some unusual things and if you haven’t, then you are about to. The below codes are checked twice before adding, so feel free to share this article with your friends.

Learn how to perform CRUD operations using C Sharp

QuickStart: CRUD Operations in C Sharp. Learn how to perform CRUD operations using C Sharp for MongoDB databases.

Loops in C++ | For, While, and Do While Loops in C++

In this Video We are going to see how to use Loops in C++. We will see How to use For, While, and Do While Loops in C++.

Using isdigit() in C/C++

In this article, we'll take a look at using the isdigit() function in C/C++. This is a very simple way to check if any value is a digit or not. Let's look