Click here to Skip to main content
15,391,989 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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);
            }

        }
Posted

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


C#
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());
           }
       }
   
Hi, have a check at this; Summarize C# Control Excel Skills[^].
This is a good collection of skills.

Good Luck.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900