13 Replies - 698 Views - Last Post: 12 September 2019 - 09:10 AM Rate Topic: -----

#1 Exceedinglife   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 225
  • Joined: 01-July 12

Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 09:23 AM

Hello everyone i am trying to get the Last cell for a specific column so i can add a value to it. I can get the last row used but i cant get it for a specified column.

Not all my columns are going to be the same last row value. so thats why i need it specified.
xlWorkSheets = xlWorkBook.Worksheets;
                xlWorkSheet = xlWorkSheets.get_Item("Scale_Data");
                Excel.Range xlRange = xlWorkSheet.UsedRange;
                int columnCount = xlRange.Columns.Count;
                int rowCount = xlRange.Rows.Count;
                //Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                //Excel.Range range = xlWorkSheet.get_Range("A1", last);
                //int lastRow = range.Row;

                Excel.XlDirection goUp = Excel.XlDirection.xlUp;
                Excel.XlDirection goDown = Excel.XlDirection.xlDown;

                var colLetter = GetLastCellForColumn("6");

                /// var fempty = xlWorkSheet.Range[colLetter + rowCount].End[goDown].Row;
                Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                Excel.Range rng = xlWorkSheet.get_Range(colLetter+rowCount, last);
                int rr = rng.Row;

                //xlWorkSheet.Range(data, data,);
                //var firstEmpty = xlWorkSheet.Range["A100"].End[goDown].Row;


                /// Add New Values to their Specific Rows & Columns.
                //  Project IDrowCount + 1
                xlWorkSheet.Cells[rr, 6] = project;


Is This A Good Question/Topic? 0
  • +

Replies To: Get Office Interop Excel C# Specific Column last cell to add next row

#2 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,201
  • Joined: 12-January 10

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 09:31 AM

pretty sure in excel you must specify the the column name in order to use it
Was This Post Helpful? 0
  • +
  • -

#3 h4nnib4l   User is offline

  • The Noid
  • member icon

Reputation: 1416
  • View blog
  • Posts: 2,017
  • Joined: 24-August 11

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 10:16 AM

I'm confused about what you're trying to accomplish. The title sounds like you want to add a new row, and then specify a cell value within that row, but the code suggests that you're trying to change a value in the last existing row. Which one are you trying to do?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,184
  • Joined: 12-December 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 10:23 AM

What happens when you run your code?


I would first check what UsedRange is, it may not be what you think it is.
Was This Post Helpful? 0
  • +
  • -

#5 Exceedinglife   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 225
  • Joined: 01-July 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 10:24 AM

I am trying to add a new value to the end of the row specified by the column.

Each column may have a unique number or rows so I cant just get the row number and use it for all the columns.

I need to count each columns rows to be able to add at the end of it.

Currently my data will be added to the last row. So if my columns are not even it will leave gaps.

This post has been edited by Exceedinglife: 03 September 2019 - 10:27 AM

Was This Post Helpful? 0
  • +
  • -

#6 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7021
  • View blog
  • Posts: 23,845
  • Joined: 05-May 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 11:04 AM

Perhaps I'm missing two things:
First: Why not just use the brute force of examining each cell in the target column starting from the first row going down?
Second: If the answer to the first one is something like "is there something in Excel Automation that can do this form me", then why is this a C# question? Sounds like an Excel Automation question.
Was This Post Helpful? 0
  • +
  • -

#7 Exceedinglife   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 225
  • Joined: 01-July 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 11:08 AM

It needs to be done in C#.

Here is some of the things I have tried.
I would love to brute force the rows from the beginning specified by column but google has not been able to help me.
I can get ALL Rows and Columns but not specified by columns.
Do you have an example?

 //Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                //Excel.Range range = xlWorkSheet.get_Range("A1", last);
                //int lastRow = range.Row;

                Excel.XlDirection goUp = Excel.XlDirection.xlUp;
                Excel.XlDirection goDown = Excel.XlDirection.xlDown;

                var colLetter = GetLastCellForColumn("6");

                //Excel.Range last2 = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                //Excel.Range rng2 = xlWorkSheet.Range[colLetter + rowCount];
                //int r2r = rng2.Row;                /// var fempty = xlWorkSheet.Range[colLetter + rowCount].End[goDown].Row;

                //Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                //Excel.Range count;                //count = xlWorkSheet.UsedRange.Columns[7].Cells;
                ////int rr = rng.Row;                //int newin = 1;
                //foreach(Range cell in count.Cells)
                //{                //    newin++;          //    if(cell.Value == "") //    { return;    }       //}
                //for(int i=3; i<rowCount; i++)
                //{       //    if(rng.Cells[i,6].Value == "") //    { // newin = i; // return; //    }      //}
                //xlWorkSheet.Range(data, data,);
                //var firstEmpty = xlWorkSheet.Range["A100"].End[goDown].Row;

                int testCount = GetRowsForColumn(6);

                /// Add New Values to their Specific Rows & Columns.
                //  Project IDrowCount + 1
                xlWorkSheet.Cells[testCount, 6] = project;
                //  Scale WeightrowCount + 1
                xlWorkSheet.Cells[testCount, 7] = data;
                //  Time StamprowCount + 1
               // xlWorkSheet.Cells[firstEmpty+1, 8] = DateTime.Now.ToString();
                //  Sequence IDrowCount + 1
               // xlWorkSheet.Cells[firstEmpty+1, 9] = sequence;
                /// Save the Newly Added Row.
                xlWorkBook.Save();

 private int GetRowsForColumn(int columnNumber)
        {
            int columnCount = 0;
            try
            {
                xlWorkSheets = xlWorkBook.Worksheets;
                xlWorkSheet = xlWorkSheets.get_Item("Scale_Data");

                // columnCount = xlWorkSheet.Columns[columnNumber].Rows.Count;
                foreach (Range c in xlWorkSheet.Columns[columnNumber].Rows)
                {

                    if (xlWorkSheet.Columns[columnNumber].Rows.Cells.Value != "")
                    {
                        columnCount++;
                    }
                    else
                    {
                        break;
                    }
                }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }

            return columnCount;
        }

 private string GetLastCellForColumn(string columnNumber)
        {
            string[] Columns = new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S" };
            int transferNum = Convert.ToInt32(columnNumber);

            //Excel.XlDirection goUp = Excel.XlDirection.xlUp;
            //Excel.XlDirection goDown = Excel.XlDirection.xlDown;
            //xlWorkSheets = xlWorkBook.Worksheets;
            //xlWorkSheet = xlWorkSheets.get_Item("Scale_Data");
            //Excel.Range range = xlWorkSheet.UsedRange;
            //int columnCount = range.Columns.Count;
            //int rowCount = range.Rows.Count;
            //var firstEmpty = xlWorkSheet.Range;

            return Columns[transferNum - 1];
        }


This post has been edited by Exceedinglife: 03 September 2019 - 11:19 AM

Was This Post Helpful? 0
  • +
  • -

#8 Exceedinglife   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 225
  • Joined: 01-July 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 11:50 AM

This maybe the line of code I have been looking for.

Excel.XlDirection goUp = Excel.XlDirection.xlUp;
 int specRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 6].End(goUp).Row;


This post has been edited by Exceedinglife: 03 September 2019 - 11:51 AM

Was This Post Helpful? 0
  • +
  • -

#9 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7021
  • View blog
  • Posts: 23,845
  • Joined: 05-May 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 12:48 PM

View PostExceedinglife, on 03 September 2019 - 02:08 PM, said:

It needs to be done in C#.

If it needs to be done in C#, then using the Excel Automation Interop is cheating... To do it purely in C#, you'll need to spend some time reading the OpenXML specification/SDK. Using the Excel Automation Interop assumes that Excel in installed on the machine. All you are doing in C# is calling the Excel Automation APIs. Those APIs are actually written in C++.

View PostExceedinglife, on 03 September 2019 - 02:08 PM, said:

I would love to brute force the rows from the beginning specified by column but google has not been able to help me.

Seems to be pretty easy: C# Excel get cell value

And the first hit comes up with this: StackOverflow question and answer.

If you can get a single cell with a given row and column value, then you can start changing the row numbers for a given column to check what the cell values are. Once you hit a cell that is empty, viola! You've brute force searched through a column.
Was This Post Helpful? 0
  • +
  • -

#10 Exceedinglife   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 225
  • Joined: 01-July 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 02:55 PM

this is the solution that is working for me.

 private int GetRowsForColumn(int columnNumber)
        {
            int columnCount = 0;
            try
            {
                Excel.XlDirection goUp = Excel.XlDirection.xlUp;
                xlWorkSheets = xlWorkBook.Worksheets;
                xlWorkSheet = xlWorkSheets.get_Item("Scale_Data");

                columnCount = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, columnNumber].End(goUp).Row;

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

            return columnCount;
        }


Was This Post Helpful? 0
  • +
  • -

#11 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7021
  • View blog
  • Posts: 23,845
  • Joined: 05-May 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 03 September 2019 - 05:31 PM

I'm glad you found a way using the Excel Interop.
Was This Post Helpful? 0
  • +
  • -

#12 Exceedinglife   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 225
  • Joined: 01-July 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 04 September 2019 - 06:53 AM

View PostSkydiver, on 03 September 2019 - 05:31 PM, said:

I'm glad you found a way using the Excel Interop.


LOL,
Sorry I should have clarified that it wasn't PURELY C#. I created a program that worked with a program 'WinWedge' and it pulls data from it and places it into an excel sheet. I used the library Office Interop Excel to put the data into an excel file in C#. The last thing I have to do is organize the Excel sheets by Week with the data. I'm not sure how to do that yet. I'll do some research and may possibly have to make a new topic.

Thanks again!
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,184
  • Joined: 12-December 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 04 September 2019 - 09:14 AM

If a further question is on the same project or code base you can attach it here, we prefer to keep things together.

Shouldn't be too difficult. I'd guess that you could add an additional column using WEEKNUM (or whatever it's called) and either repeatedly filter or sort and collect rows for copying.
Was This Post Helpful? 0
  • +
  • -

#14 ScottinTexas   User is offline

  • D.I.C Regular

Reputation: 11
  • View blog
  • Posts: 309
  • Joined: 13-March 12

Re: Get Office Interop Excel C# Specific Column last cell to add next row

Posted 12 September 2019 - 09:10 AM

First off, I saw "Used Range" when browsing the thread. Don't use used range. Even if the cell is now empty, it was once used so that is misleading.

The excel method for last row or last column is;
        public int GetLastRangeColumn(int row)
        {
            WorksheetFunction wsf = ExcelApp.WorksheetFunction;
            return ExcelWorksheet.Cells[row, ExcelWorksheet.Columns.Count].End(-4159).Column;
        }

        public int GetLastRangeRow(int col)
        {
            WorksheetFunction wsf = ExcelApp.WorksheetFunction;
            return ExcelWorksheet.Cells[ExcelWorksheet.Rows.Count, col].End(-4162).Row;
        }



-4159 and -4162 are constants that equate to xkLeft and xlUp. They return the row or column number of the last row or column that has a value. Not the last used.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1