使用C#将新列追加到Excel工作表 [英] Append new columns to excel sheet, using C#

问题描述

我有一张Excel工作表.如何使用C#将新列追加到该Excel工作表的最后一列.

Hi,

I have an excel sheet. How can I append new columns to the last column of that excel sheet using C#.

public void AddColumn(string sheetName, string strConn)
        {
            try
            {          
                OleDbConnection oledbConn = new OleDbConnection(strConn);
                oledbConn.Open();
                string strQuery = string.Format("SELECT * FROM [" + sheetName + "]", oledbConn);
                OleDbDataAdapter oledbDA = new OleDbDataAdapter(strQuery, strConn);

                DataSet DS = new DataSet();
                oledbDA.Fill(DS);

                //add column to excel 
                xlWorkBook = new Excel.Workbook();
                xlWorkSheet = new Excel.Worksheet();
                xlSheets = xlWorkBook.Worksheets;

                int lastColumn = xlWorkSheet.Columns.CurrentRegion.Count;      

                xlApp = new Excel.Application();                
                xlWorkSheet = new Excel.Worksheet();               
                xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1],xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 1]).Insert(System.Reflection.Missing.Value, Excel.XlInsertShiftDirection.xlShiftToRight);

               

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

推荐答案

添加新使用Bytescout Spreadsheet SDK将现有列添加到现有Excel XLS文档中的列

我会像在Excel中执行VBA一样查看它.检查哪些列具有值，然后仅使用下一行.

更改值的方式:

I would look at it like just doing VBA in Excel. Check to see which columns have values, and then just use the the next row.

The way to change values:

sheet.Cells[row, column] = value;

同样，您可以从单元格中获取值.

Similarly you can get values from cells.

这是我的问题的解决方案.

Here is the solution for my question.

public static void AddNewColumn(string fileName, int sheetNum)
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range rng;
        object misValue = System.Reflection.Missing.Value;

        try
        {
            xlApp = new Excel.Application();
            xlApp.Visible = true;
            xlWorkBook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false,
                Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);   //@"H:\TestFile.xlsx"
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(sheetNum);
            //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetNum);
            rng = xlWorkSheet.UsedRange;

            int colCount = rng.Columns.Count;
            int rowCount = rng.Rows.Count;
            rng = (Excel.Range)xlWorkSheet.Cells[rowCount, colCount];
            Excel.Range newColumn = rng.EntireColumn;
            xlWorkSheet.Cells[1, colCount + 1] = "Visit Link";

            //save and quit
            //xlWorkBook.SaveAs(@"H:\TestFile.xlsx", misValue, misValue, misValue, misValue, misValue,
            //    Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Save();
            xlWorkBook.Close(misValue, misValue, misValue);
            xlApp.Quit();

            // release all the application object from the memory
            System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }

private void btnAddNewColumns_Click(object sender, EventArgs e)
       {
           try
           {
               string theFile = txtFileName.Text;
               int theSheet = cboGetSheet.SelectedIndex +1;
               ExcelTools.AddNewColumn(theFile, theSheet);
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.ToString());
           }
       }

这篇关于使用C#将新列追加到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！