Strip all formatting from Excel file on load

Question

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 ConsigneeCombo box 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 <= 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<string>("TABLE_NAME");

                }

                if (sheetName.Length <= 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();
            using (OleDbConnection con = new OleDbConnection(connection))
            {
                con.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);

                adapter.Fill(dataTable);
                adapter.Dispose();
            };
            return dataTable;
        }

    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 <= 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<string>("TABLE_NAME");

                }

                if (sheetName.Length <= 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);
    }

    private void Browse_Click(object sender, EventArgs e)
    {
        fileTextBox.Visible = true;
        ConsigneeCombo.Visible = true;
        updateExcel.Visible = true;
        dataGridView1.Visible = true;
        saveExcel.Visible = true;
        consigneeLabel.Visible = true;
        fileLabel.Visible = true;
        string userFileNameUT = string.Empty;
        string fileExtensionUT = string.Empty;
        using (OpenFileDialog ofd = new OpenFileDialog())
        {
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            ofd.Filter = "CSV Files|*.csv|Excel '97-2003|*.xls|Excel 2007-2019|*.xlsx";
            if (ofd.ShowDialog(this) == DialogResult.OK) 
            {
                fileExtensionUT = Path.GetExtension(ofd.FileName);
                userFileNameUT = ofd.FileName;
            }
            else
            {
                fileTextBox.Visible = false;
                ConsigneeCombo.Visible = false;
                updateExcel.Visible = false;
                dataGridView1.Visible = false;
                saveExcel.Visible = false;
                consigneeLabel.Visible = false;
                fileLabel.Visible = false;
            }
        }
        string extensionMix = string.Empty;
        if (fileExtensionUT == ".csv")  extensionMix = ".csv";
        else if (fileExtensionUT == ".xls") extensionMix = ".xls";
        else if (fileExtensionUT == ".xlsx") extensionMix = ".xlsx";
        if (userFileNameUT.Length == 0) return;
        string userFileName = Path.Combine(Path.GetDirectoryName(userFileNameUT), Path.GetFileNameWithoutExtension(userFileNameUT.Replace(".", "")) + extensionMix);
        File.Copy(userFileNameUT, userFileName, true);
        this.dataGridView1.DataSource = GetData(userFileName);
        fileTextBox.Text = userFileNameUT;
        textBox4.Text = userFileName;
        textBox1.Text = Path.GetFileName(userFileNameUT);
    }

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

    public void ToCsV(DataGridView dGV, string filename)
    {
        string stOutput = "";
        string sHeaders = "";
        for (int j = 0; j < dataGridView1.Columns.Count; j++)
            sHeaders = sHeaders.ToString() + Convert.ToString(dataGridView1.Columns[j].HeaderText) + ",";
        stOutput += sHeaders + "\r\n";
        for (int i = 0; i < dataGridView1.RowCount - 1; i++)
        {
            string stLine = "";
            for (int j = 0; j < dataGridView1.Rows[i].Cells.Count; j++)
                stLine = stLine.ToString() + Convert.ToString(dataGridView1.Rows[i].Cells[j].Value) + ",";
            stOutput += stLine + "\r\n";
        }
        Encoding utf16 = Encoding.GetEncoding(1254);
        byte[] output = utf16.GetBytes(stOutput);
        FileStream fs = new FileStream(filename, FileMode.Create);
        BinaryWriter bw = new BinaryWriter(fs);
        bw.Write(output, 0, output.Length); 
        bw.Flush();
        bw.Close();
        fs.Close();
    }

    private void saveExcel_Click_1(object sender, EventArgs e)
    {
        SaveFileDialog sfd = new SaveFileDialog();
        sfd.Title = "Save Excel Files";
        sfd.CheckPathExists = true;
        sfd.DefaultExt = "csv";
        sfd.Filter = "Excel Files|*.csv";
        string saveFileName = textBox1.Text;
        string fileExtensionTrim = Path.GetExtension(saveFileName);
        string subFinalSaveName = textBox1.Text;
        string finalSaveName = Path.GetFileNameWithoutExtension(subFinalSaveName) + ".csv";
        textBox3.Text = finalSaveName;
        sfd.FileName = finalSaveName;
        sfd.InitialDirectory = @"C:";
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            ToCsV(dataGridView1, sfd.FileName);
            string userFileName = textBox4.Text;
            File.Delete(userFileName);
            fileTextBox.Visible = false;
            ConsigneeCombo.Visible = false;
            updateExcel.Visible = false;
            dataGridView1.Visible = false;
            saveExcel.Visible = false;
            consigneeLabel.Visible = false;
            fileLabel.Visible = false;
        }
        else
        {
            fileTextBox.Visible = true;
            ConsigneeCombo.Visible = true;
            updateExcel.Visible = true;
            dataGridView1.Visible = true;
            saveExcel.Visible = true;
            consigneeLabel.Visible = true;
            fileLabel.Visible = true;
        }
    }
}
}

Answer 1

When i run my code, the updateExcel_Click part updates column C with what is in ConsigneeCombo box 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

Matt, i'm sorry, but the code you've posted is not related to Excel. It updates dataGridView1 cells without any condition. So, if you want to update only part of cells, you have to add condition:

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

But, i really do believe that it isn't what you're looking for, because you're using OleDb provider to get/fetch Excel data.

Note, that OleDb provider exposes methods to provide CRUD operations. You can INSERT (create), SELECT (read), UPDATE (modify) and DELETE (destroy) Excel data through OleDbCommand.

So, if you would like to UPDATE data, use below statement:

UPDATE [SheetNameOrTableName$]
    SET FieldName=NewValue
    WHERE FieldName=OldValue

You have to pass it to OleDbCommand.Command as a string:

string sSQL = @"UPDATE [SheetNameOrTableName$]
    SET FieldName=?
    WHERE FieldName=?";

or

string sSQL = @"UPDATE [SheetNameOrTableName$]
    SET FieldName=@param1
    WHERE FieldName=@param2";

But i have to warn you: OleDb provider for JET/ACE does not recognize named parameters! So, you have to add parameters to the OleDbCommand in correct order!

Finally, i'd suggest to re-think your application and split business logic from data access. See:
Creating a Data Access Layer (C#)
Creating a Business Logic Layer (C#)
Writing a Portable Data Access Layer

Above articles provide information for ASP.NET pages, but the logic for WinForms has to be the same!

A part of DAL class for Excel file may look like:

public class ExcelDAL
{
    private string sExcelFile = string.Empty;
    private string sExcelSheet = string.Empty;
    private bool bUseHeaders = false;
    private bool bUseIMEX = false;

    public ExcelDAL(string _ExcelFile, string _ExcelSheet, bool _UseHeaders, bool _UseIMEX)
    {
        sExcelFile = _ExcelFile;
        sExcelSheet = _ExcelSheet;
        bUseHeaders = _UseHeaders;
    }


    private string GetConnString()
    {
        string suh = bUseHeaders ? "YES" : "NO";
        string sui = bUseIMEX ? "IMEX=1;" : "";
        return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}';{2}", sExcelFile, suh, sui);
    }

    public DataTable GetSheetData()
    {
        DataTable dt = new DataTable();
        using (OleDbConnection connection = new OleDbConnection(GetConnString()))
        {
            string sql = string.Format(@"SELECT * FROM [{0}$];", sExcelSheet);
            connection.Open();
            using(OleDbCommand command = new OleDbCommand(sql, connection))
            {
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    dt.Load(reader);
                }
            }
        }
        return dt;
    }

    //other members and methods of DAL class
}

Feel free to improve ExcelDAL class to your needs.

Good luck!

Answer 2

I am in agreement with @Maciej Los, your question appears focused on something in “Excel”, but the code is not doing anything in “Excel” in reference to adding the text from a ComboBox to the third column of all the rows in a DataGridView. This is confusing and I will start from the perspective of the DataGridView, as this is what the current code is using.

From your comment…

….. 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.

This is not necessarily accurate… the code is NOT updating them because of the “formatting”… it is “updating” them because there are ten (10) rows! … the posted code is simply looping through ALL the rows in the grid. It is not checking for any formatting nor is it checking if the row is “empty”!

When you “read” an “Excel” file “that has cell formatting” in an empty cell (as you described)… it WILL get picked up on the read and will become an “row” in the data source, even though all the cells may be empty. This is an “Excel” issue and I know of a solution that will remove all of these “empty” cells “before” your code reads the “Excel” file, thus “eliminating” these “empty” rows from the start.

I hope I am not missing something….

To do this using the DatGridView, it may be possible to create a small method that given a row index in the grid, returns true if the row is “empty” of text. Calling this method from the existing updateExcel_Click … may look something like below…

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

private bool RowIsEmpty(int rowIndex) {
  for (int i = 0; i < dataGridView1.ColumnCount; i++) {
    if (dataGridView1.Rows[rowIndex].Cells[i].Value != null &&
        dataGridView1.Rows[rowIndex].Cells[i].Value.ToString() != "") {
      return false;
    }
  }
  return true;
}

In reference to removing the “empty formatted” cells from the Excel file…

Fastest method to remove Empty rows and Columns From Excel Files using Interop

May help. I am aware this uses “interop”, however, I am confident it would not be difficult to implement it using OLEDB. Basically, a “usedRange” from an Excel sheet is read into an object array which will drop this formatting.

Please let me know if I am missing something import. Hope this helps.