Insert Data into the cells from the database

Aspose.Cells Product Family
1

Hi

Is there any way to insert data in the excel sheet cells from the Database. I want to insert values from the datasource to the Excel Sheet ?

Thanks.

Monisha

2

Hi Monisha,

Thanks for considering Aspose.

Well, Aspose.Cells can import data to worksheets from any datasource with excellence. There are a number of Methods e.g., ImportDataTable, ImportDataView, ImportDataView, ImportFromDataReader, ImportObjectArray, ImportArray etc.

For a simple scenario, you will use ADO.Net components like Connection, Command, DataAdapter, DataSet, DataTable. Following might be some steps involved:

1. Establish a connection with the database using Connection component.

2. Use Command or DataAdapter component to specify your required Query (in which you want to include your required fields with group by class etc.)

3. Fill the DataSet or DataTable with the result of the Query.

4. Now use Aspose.Cells APIs like Cells.ImportDataTable, Cells.ImportDataView, Cells.ImportDataView or Cells.ImportFromDataReader

Sample Code:

Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\test\Northwind1.mdb")

con.Open()

Dim cmd As OleDbCommand = New OleDbCommand("Select * from employees", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter()

da.SelectCommand = cmd

Dim ds As DataSet = New DataSet()

da.Fill(ds, "Employees")

Dim workbook As Workbook = New Workbook()

workbook.Worksheets(0).Cells.Columns(0).Style.Number = 1

workbook.Worksheets(0).Cells.ImportDataTable(ds.Tables(0), True, 0, 0)

workbook.Save("d:\test\importtest.xls")

For further ref, Please check our wiki topic:

ImportData

Thank you.

3

Thanks for the help

In my application, I am trying to import a datatable to the excel sheet

using

objWorkSheet.Cells.ImportDataTable

but it inserts new row in the excel for each datarow in datatable. Since Im using a template the alignment is getting changed. I need to populate the datatable within a certain range defined in the template.

Is there anyway that I can populate the data into excel without creating new rows?

Monisha

4

Hi Monisha,

Please use the overloaded version of ImportDataTable method i.e.,

[Visual Basic]
Overloads Public Function ImportDataTable( _
ByVal dataTable As DataTable, _
ByVal isFieldNameShown As Boolean, _
ByVal firstRow As Integer, _
ByVal firstColumn As Byte, _
ByVal insertRows As Boolean _
) As Integer
[C#]
public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
byte firstColumn,
bool insertRows
);

Parameters

dataTable
The DataTable object to be imported.
isFieldNameShown
Indicates whether the field name of the datatable will be imported to the first row. Default is true.
firstRow
The row number of the first cell to import.
firstColumn
The column number of the first cell to import.
insertRows
Indicates whether extra rows are added to fit data.

Example:

...............
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * from Employees",con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds,"Employees");
Workbook wb = new Workbook();
wb.Open("d:\\test\\bktemplate.xls");
Worksheet ws = wb.Worksheets[0];
// Import the datatable
ws.Cells.ImportDataTable(ds.Tables["Employees"], true, 0,0,false);
wb.Save("d:\\test\\noinsertrows.xls");
Thank you.