Spire.DataExport for .NET is a 100% pure data .NET library suit for exporting data into MS Word, Excel, RTF, Access, PDF, XPS, HTML, XML, Text, CSV, DBF, SYLK, SQL Script, DIF, Clipboard, etc.

Thu Apr 21, 2011 7:12 pm

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

billtepe
 
Posts: 1
Joined: Wed Apr 20, 2011 10:06 pm

Mon Apr 25, 2011 10:09 am

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.
Attachments
AddFormula.zip
(62.54 KiB) Downloaded 571 times
Harry
Technical Support / Developer,
e-iceblue Support Team
User avatar

harry.support
 
Posts: 180
Joined: Mon Nov 08, 2010 3:11 pm

Return to Spire.DataExport