如何使用Interop c#隐藏Excel列和行

I have made a simple inventory interface that will take data from access and show in datagrid view on my interface and then send the information to Excel via button click. This part works as needed but I would like to remove the unused columns and rows after the information is sent. I am currently using VS 2015. I cant figure out what to add to accomplish this.

我做了一个简单的库存界面，从access获取数据并在我的界面上的datagrid视图中显示，然后通过按钮点击将信息发送到Excel。这个部分按需要工作，但我希望在发送信息后删除未使用的列和行。我目前使用的是VS 2015。我想不出要做什么来达到这个目的。

//send to excel
    private void btnExport_Click(object sender, EventArgs e)
    {
        ActiveControl = txtSerial;
        // creating Excel Application
        Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

        // creating new WorkBook within Excel application
        Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

        // creating new Excelsheet in workbook
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

        // see the excel sheet behind the program
        app.Visible = true;

        // get the reference of first sheet. By default its name is Sheet1.
        // store its reference to worksheet
        worksheet = workbook.Sheets["Sheet1"];
        worksheet = workbook.ActiveSheet;

        // changing the name of active sheet
        worksheet.Name = "Inventory Search";

        // storing header part in Excel
        for (int i = 1; i < dataGridFB.Columns.Count + 1; i++)
        {
            worksheet.Cells[1, i] = dataGridFB.Columns[i - 1].HeaderText;
            worksheet.Cells[1, i].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
            worksheet.Cells[1, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            worksheet.Cells[1, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            worksheet.Cells[1, i].Font.Size = 14;
        }

        // storing Each row and column value to excel sheet
        for (int i = 0; i < dataGridFB.Rows.Count - 1; i++)
        {
            for (int j = 0; j < dataGridFB.Columns.Count; j++)
            {
                worksheet.Cells[i + 2, j + 1] = dataGridFB.Rows[i].Cells[j].Value.ToString();
                worksheet.Cells[i + 2, j + 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                worksheet.Cells[i + 2, j + 1].Font.Size = 12;
                worksheet.Columns["A:G"].AutoFit();
            }
        }
    }

1 个解决方案

#1

Your question is a bit too broad, thus the answer is generic: in order to delete entire Worksheet Column you may use VBA statement like: Columns("C").Delete, or Columns(3).EntireColumn.Delete, or Columns("F:K").Delete. Similar syntax may apply to: Rows(3).Delete.

您的问题有点太宽泛了，因此答案是通用的:为了删除整个工作表列，您可以使用VBA语句:列(“C”)。(3).EntireColumn删除或列。删除或列(“F:K”)Delete。类似的语法可以应用于:行(3). delete。

In order to just hide Rows/Columns use the VBA statement like shown below:

为了隐藏行/列，使用如下所示的VBA语句:

Rows("3:10").EntireRow.Hidden = True
Columns("C").Hidden = True

Hope this may help.

希望这可以帮助。

#1