Please, is there any way at all to add up (sum up) the contents of the cells (Rows) of a given column whose number of possible rows (cells) are dynamic, that is, the number of rows are not fixed, in an excel worksheet? I've been trying without any meaningful result, i tried using offset but keep getting some COM_Exception from HRESULT blah blah......
Below is a part of a demo code am trying to use in achieving my purpose.
private void button1_Click(object sender, RoutedEventArgs e)
{
excel.Application xlApp;
excel.Workbook xlWorkBook;
excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new excel.Application();
var MyExcel = new excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
excel.Range chartRange;
chartRange = xlWorkSheet.get_Range("$A:$A", Type.Missing);
xlWorkSheet.Cells[1, 1] = "Temperature(deg)";
xlWorkSheet.Cells[1, 2] = "Humidity(m-3)";
xlWorkSheet.Cells[1, 3] = "Wind Speed(m/s)";
xlWorkSheet.Cells[2, 1] = 33;
xlWorkSheet.Cells[2, 2] = 45;
xlWorkSheet.Cells[2, 3] = 34;
xlWorkSheet.Cells[3, 1] = 23;
xlWorkSheet.Cells[3, 2] = 26;
xlWorkSheet.Cells[3, 3] = 43;
xlWorkSheet.Cells[4, 1] = 45;
xlWorkSheet.Cells[4, 2] = 24;
xlWorkSheet.Cells[4, 3] = 34;
xlWorkSheet.Cells[5, 1] = 40;
xlWorkSheet.Cells[5, 2] = 32;
xlWorkSheet.Cells[5, 3] = 42;
decimal fdt = 0;
int i = 2;
excel.Range targetRange = (excel.Range)xlWorkSheet.Cells[2, 1];
while (xlWorkSheet.Cells.Offset[i, 0].Value2 != null)
{
i++;
fdt += xlWorkSheet.Cells.Offset[i, 0].Value2;
}
MessageBox.Show(fdt.ToString());
excel.ChartObjects xlCharts = (excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
excel.ChartObject myChart = (excel.ChartObject)xlCharts.Add(400, 50, 400, 300);
excel.Chart chartPage = myChart.Chart;
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = excel.XlChartType.xl3DColumnClustered;
try
{
myChart.Chart.HasTitle = true;
excel.Range objRange = (excel.Range)xlWorkSheet.Cells["$A:$A", Type.Missing];
String strData = objRange.get_Value(misValue).ToString();
myChart.Chart.ChartTitle.Text = strData;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
xlWorkBook.SaveAs("DemoChart_1.xls", excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlApp.Visible = true;
}