Appending to existing excel document

Aspose.Cells Product Family
1

Hi

I have a batch process that needs to create an excel file and then subsequently append to this file. Can this be done? If so can you please provide me with some samples?

Thanks

Kan

2

Hi,

Thanks for considering Aspose.

Well, yes, Aspose.Cells not only can create excel files from the scratch but also update / modify the existing (template) files with ease. The manipulation of files is done with excellence and optimal performance. For reference, please check Aspose.Cells documentation: `http://www.aspose.com/documentation/default.aspx`. Further, you may check our featured demos (when you install the component using its msi installer on your machine the demo projects also get installed, so you can open those project in VS.Net and check the source codes): `http://www.aspose.com/demos/aspose.cells/default.aspx`

Thank you.

3

Hi

I really appreciate your reply. I tried using the documentation first before I posted this question but I was having a hard time locating what I needed. Is there any way you can point me to the appropriate search term or what I should be looking for? I would really love any help you can provide.

- Kan

4

Hi Kan,

Could you check these doc links that may help you:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/your-first-application-using-aspose-cells-hello-world.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/opening-files.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/saving-files.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/accessing-cells-of-a-worksheet.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/adding-data-to-cells.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/retrieving-data-from-cells.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/importing-data-to-worksheets.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/approaches-to-format-data-in-cells.html

Thank you.

5

Hi

I found these links quite useful but they are all making the assumption that you know exactly where to insert the new data after opening it. I am creating an Excel file dynamically and I need to reinsert into the next unpopulated row in the same file. So I have no way of knowing which row this will be.

Thanks

Kan

6

Hi,

Thanks for considering Aspose.

Well, there are no. of options for it. If you are creating / filling sets of data into the cells in rows / columns of a worksheet, you may utilize MaxDataRow / MaxDataRowInColumn, MaxDataColumn members of Cells class. These method will give you the index no. of the farthest row / column in the worksheet or in a column for your requirement.

E.g..,

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * from Products",con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;

DataSet ds = new DataSet();
da.Fill(ds,"Products");
DataTable dt = ds.Tables["Products"];
cells.ImportDataTable(dt, false, 0, 0,false);
DataTable outDataTable = cells.ExportDataTable(0, 0, cells.MaxDataRow +1, cells.MaxDataColumn+1);
Worksheet sheet2 = excel.Worksheets[excel.Worksheets.Add()];
sheet2.Cells.ImportDataTable(outDataTable,false,0,0,false);
Cells cells = wd.Workbook.Worksheets[0].Cells;
//Get last row index in A column.

int lastrowindex = cells.MaxDataRowInColumn(0);
int maxrow = lastrowindex +1;
cells[maxrow,0].PutValue("After last cell.");
wd.Save("d:\\test\\mybook.xls");

Hoping it will give you some insight.

Thank you.