How to add formula to last row of an export

[billtepe]

I am exporting data via an sql command. However I want to add a sum function to a new row after the last row that has been exported. My question is how do I insert the row. And how do I know how many rows were exported, so I can programaticaly create the sum like sum(sheet1!c4:c??) Where ?? is the last row exported.

Thanks so much.

Bill Tepe

[harry.support]

Dear Bill Tepe,

You could fill your data to a DataTable and then you could know the index of the last row.

Code: Select all

DataTable dataTable = new DataTable();
using (OleDbConnection oleDbConnection = new OleDbConnection())
{
    oleDbConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\demo.mdb";
    System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
    oleDbCommand.CommandText = " select Name, Capital, Continent, Area, Population from country ";
    oleDbCommand.Connection = oleDbConnection;
    using (OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
    {
        da.Fill(dataTable);
    }
}
int lastRowIndex = dataTable.Rows.Count + 1;


Class CellExport and WorkSheet both provide a function named AddFormula to allow program to add a formula to a specified cell.

Code: Select all

CellExport cellExport = new CellExport();
WorkSheet worksheet1 = new WorkSheet();
worksheet1.AutoFitColWidth = true;
worksheet1.DataSource = ExportSource.DataTable;
worksheet1.DataTable = dataTable;

//add formula
String formula = String.Format("=SUM(D2:D{0}", lastRowIndex);
Cell cell = worksheet1.AddFormula(4, (ushort)(lastRowIndex + 1), formula);
cell.Format.Font.Bold = true;
cell.Format.FillStyle.Background = CellColor.LightGreen;
cell.Format.FillStyle.Pattern = Pattern.Solid;
cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium;

cellExport.Sheets.Add(worksheet1);
cellExport.SaveToFile("Sample.xls");


A full demo is attached, please check.