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

Last Reply 11 days ago By dharmendr

Posted 11 days ago

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.

 

 

//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];
        }

 

You are viewing reply posted by: dharmendr 11 days ago.
Posted 11 days ago

As you have solved it please mark your reply as answer.