在加载时从Excel文件中删除所有格式

Question

当Excel文件在将数据填充到数据表之前加载时，我希望从它中删除所有格式(边框等)。

当我运行我的代码时，updateExcel_Click部件用ConsigneeCombo框中每一行的内容更新列C，但是如果我正在处理的文件有格式设置，例如10行带有边框，但其中只有8行带有文本，由于格式的原因，它会更新所有10行。

编辑

与其去掉边框，不如在updateExcel_Click部分中只将其添加到包含文本的行中？

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

我当前的GetData代码是：

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

我尝试过添加ClearFormats();方法，但无法让它工作。

完整代码：

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

我同意@Maciej的观点，您的问题似乎集中在“Excel”中的某些内容上，但代码并没有在“Excel”中执行任何有关将文本从ComboBox添加到DataGridView中所有行的第三列的操作。这是令人困惑的，我将从DataGridView的角度开始，因为这是当前代码所使用的。

从你的评论…

…。。如果我正在处理的文件具有格式设置，例如10行带有边框，但其中只有8行带有文本，则由于格式的原因，它会更新所有10行。

这不一定是精确的…。由于“格式化”…，代码没有更新它们。这是“更新”它们，因为有10 (10)行！…发布的代码只是简单地遍历网格中的所有行。它没有检查任何格式，也没有检查该行是否为“空”！

当您在空单元格(如您所描述的)中“读取”具有单元格格式的“Excel”文件时，…即使所有单元格都是空的，它也会在读取时被拾取，并成为数据源中的“行”。这是一个“Excel”问题，我知道一个解决方案，它将在“您的代码读取”“Excel”文件之前删除所有这些“空”单元格，从而从一开始就“消除”这些“空”行。

我希望我没有错过一些…。

要使用DatGridView实现这一点，可以创建一个小方法，在网格中给定行索引，如果行为“空”文本，则返回true。从现有的updateExcel_Click…调用此方法可能看起来像…下面的样子

 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;
}

引用从…文件中删除“空格式化”单元格

使用Interop从Excel文件中删除空行和列的最快方法

也许能帮上忙。我知道这使用了“interop”，但是，我相信使用OLEDB实现它并不困难。基本上，从Excel表中读取一个“usedRange”到一个对象数组中，该对象数组将删除此格式。

如果我缺少什么重要的东西，请告诉我。希望这能有所帮助。

Answer 2

当我运行我的代码时，updateExcel_Click部件用ConsigneeCombo框中每一行的内容更新列C，但是如果我正在处理的文件具有格式，例如10行带有边框，但其中只有8行带有文本，则由于格式的原因，它会更新所有10行。

马特，我很抱歉，但是你发布的代码与Excel无关。它不加任何条件地更新dataGridView1单元。因此，如果只想更新部分单元格，则必须添加条件：

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;
    }
}

但是，我确实相信这不是您想要的，因为您正在使用OleDb提供程序来获取/获取Excel数据。

注意，OleDb提供程序公开提供CRUD操作的方法。您可以通过INSERT (创建)、SELECT (读取)、UPDATE (修改)和DELETE (销毁) OleDbCommand数据。

因此，如果您想使用UPDATE数据，请使用以下语句：

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

您必须将它作为字符串传递给OleDbCommand.Command：

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

或

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

但是，，我必须警告您：JET/ACE的OleDb提供者不识别命名参数！因此，您必须按照正确的顺序向OleDbCommand添加参数！

最后，我建议重新考虑您的应用程序，并将业务逻辑从数据访问中分离出来。请参见：

创建数据访问层(C#)

创建业务逻辑层(C#)

编写便携式数据访问层

上面的文章提供了ASP.NET页面的信息，但是WinForms的逻辑必须是相同的！

用于Excel文件的DAL类的一部分可能如下所示：

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
}

可以随时根据您的需要改进ExcelDAL类。

祝好运!