CODEWITHSUNDEEP
c#excel
Rajesh D
Rajesh D

Reputation: 177

Append three columns in the Excel

I copy the data from one location to another and am able to read and print all values. But not able to append the column in the Excel file. I need to Append three columns in the beginning (Ex: 'A'). 

    private void btnUpload_Click(object sender, RoutedEventArgs e)
    {
        ApplicationClass app = new ApplicationClass();
        Workbook book = null;
        Worksheet sheet = null;
        Range range = null;

        try

        {
            app.Visible = false;
            app.ScreenUpdating = false;
            app.DisplayAlerts = false;


            book = app.Workbooks.Open(@"C:\Windows\Temp\" + FileName, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value);
            sheet = (Worksheet)book.Worksheets[1];

            // get a range to work with
            range = sheet.get_Range("A1", Missing.Value);
            // get the end of values to the right (will stop at the first empty cell)
            range = range.get_End(XlDirection.xlToRight);
            // get the end of values toward the bottom,  looking in the last column (will stop at first empty cell)
            range = range.get_End(XlDirection.xlDown);

            // get the address of the bottom, right cell
            string downAddress = range.get_Address(
                false, false, XlReferenceStyle.xlA1,
                Type.Missing, Type.Missing);

            // Get the range, then values from a1
            range = sheet.get_Range("A1", downAddress);
            object[,] values = (object[,])range.Value2;

            // Value2 is a two dimenial array dime one = row, dime two = column.
            Console.WriteLine("Col Count: " + values.GetLength(1).ToString());
            Console.WriteLine("Row Count: " + values.GetLength(0).ToString());

            Microsoft.Office.Interop.Excel.Range oRng = sheet.Range["A1"];
            oRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight,
                     Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
            oRng = sheet.Range["A1"];
            oRng.Value2 = "Discount";

        Microsoft.Office.Interop.Excel.Range oRng = sheet.Range["B1"];
                            oRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight,
                     Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
            oRng = sheet.Range["B1"];
            oRng.Value2 = "NOTHING";



         Microsoft.Office.Interop.Excel.Range oRng = sheet.Range["C1"];
                oRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight,
                         Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
                oRng = sheet.Range["C1"];
                oRng.Value2 = "HELLO";

        }
        catch (Exception k)
        {
            Console.WriteLine(k);
        }
        finally
        {
            range = null;
            sheet = null;
            if (book != null)
                book.Close(false, Missing.Value, Missing.Value);
            book = null;
            if (app != null)
                app.Quit();
            app = null;
            MessageBox.Show("File Uploaded Successfully. Please Wait...!");

        }

    }

Upvotes: 1

Views: 1075

Answers (1)

daniel59
daniel59

Reputation: 916

You only need to set the value of the cell. Don't use "A1" as cell because you need the column number: A is the first letter, so it needs to be column 1 and B is the second letter, needs to be column 2 etc. In the following code you can see how to do it correctly: 

        Excel.Application app = new Excel.Application();
        Excel.Workbooks workbooks = app.Workbooks;
        string filename = @"yourFilename";
        workbooks.Open(filename);
        Excel.Workbook workbook = workbooks.Item[1];
        Excel.Sheets worksheets = workbook.Worksheets;
        Excel.Worksheet worksheet = worksheets.Item[1];
        Excel.Range cells = worksheet.UsedRange;

        cells[1, 2] = "Hello";//Set value for row 1 and column 2 (A1)
        cells[1, 3] = "World";//Set value for row 1 and column 3 (B1)

        workbook.Save();//Save Excel-File
        app.Quit();
        Console.ReadKey();

Upvotes: 2

Related Questions