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

Question

在将数据填充到数据表之前，我想在加载 Excel 文件时从 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 Los，您的问题似乎集中在“Excel”中的某些内容上，但是代码在“Excel”中没有做任何事情，以添加来自 ComboBox 的文本。到 DataGridView 中所有行的第三列.这很令人困惑，我将从DataGridView 的角度开始。 ，因为这是当前代码正在使用的。

从你的评论...

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

这不一定准确……由于“格式化”，代码没有更新它们……它正在“更新”它们，因为有十 (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;
}

关于从 Excel 文件中删除“空格式”单元格……

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

可能会有所帮助。我知道这使用“互操作”，但是，我相信使用 OLEDB 实现它并不难。基本上，将 Excel 工作表中的“usedRange”读入对象数组，该对象数组将删除此格式。

如果我遗漏了一些导入内容，请告诉我。希望这可以帮助。