How to write a datatable row into an existing excel file

Aspose.Cells Product Family
1

Hi,

I have a requirement, where I want to use an Excel Template and write DaaTable into it.

Let me ellobrate my requirement.

1. I have an Excel Template say MonthlyReport.xls

2. In my ASP.Net Application I should be able to open that excel file, using Aspose.Cell.

3. I will have a dataset, and I want to write a ds.Tables[0] into the excel sheet.

4. Inside Excel file I should be able to write a particular row at particular location, say ds.Tables[0].Rows[0] at A4 row

More practically I should be able to say where to write the DataTable row write inside the excel file.

My excel file template wil have some formulas.

I want to know is it possiable to write whole row from an datatable into an excel, or I need to write cell by cell.

Please advice the best approach with sample code.

Let me know if you need more clarification

Thanks

Siraj

2

Hi,

Aspose.Cells for .NET can fulfill your requirement. I think you my try Cells.ImportDataRow() method for your task.

See the sample code below:
string strPath = “…”;
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(strPath + “MonthlyReport.xls”);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

//…Your code goes here.

//import the data row at A4 in the sheet.
worksheet.Cells.ImportDataRow(ds.Tables[0].Rows[0],3,0);

workbook.Save(“MyExcelFile.xls”, FileFormatType.Excel97To2003, SaveType.OpenInExcel, Response);


For further reference, please check the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/importing-data-to-worksheets.html

Thank you.

3

Hi,

I am facing another problem.

As I said, Iam using Excel Template and Import DataSet method to write rows in my excel file.

I am able to acheive that quite successfully.

But know here the problem is my template contain some formula field, which is getting overwite during import process.

In my datatable for that particular cell, which has formula in my excel template, Iam sending value NULL, hoping that it will not overwrite the formula, but It is overwrting the formula and iam getting blank field.

Please suggest how to overcome this problem.

Please find the snap of my excel template.

Thanks

Siraj

4

Hi,

I think you my try some other versions of the ImportDataTable method, e.g

myWorksheet.Cells.ImportDataTable(dt, False, 24,0, True)
The last boolean parameter is insertRows that you may set to True, doing so, the component would first insert an extra row(s) first then paste the data from the datatable into the new row.

Alternatively, if you need to put the datarow in the same row where in a cell you need to retain the formulas, you should pick the formula from that cell first to store the formula value to some variable, then after inserting the data from the datatable you have to override the cell’s value with the formula again for your requirement. For your information, if a cell has a formula in it and when you put null value to that cell, it would overwrite the formula string with the null/value, Aspose.Cell’s behavior is same as MS Excel and you may check/confirm it in MS Excel too.


Thank you.