Hi,
I need to connect and export from Stored Procedure results to formatted excel file with Aspose.Cells. I develop windows vb.net console application. Could you help me and send me a example code please.
My code goes like this;
Dim cnn As SqlConnection
Dim connectionString As String
Dim sql As String
connectionString = “data source=192.168.1.2;initial catalog=MyDatabase;user id=sa;password=MyPass;”
cnn = New SqlConnection(connectionString)
cnn.Open()
sql = “EXEC Mng.GetAllData”
Dim dsOrders As DataSet = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, sql)
Thank you very much.
Hi,
I have written a sample code for your reference, please refer to it ( you may change the codes accordingly for your needs):
Sample code:
Dim con As New OleDbConnection(“provider=microsoft.jet.oledb.4.0;data source=e:\test\Northwind1.mdb”)
con.Open()
Dim cmd As New OleDbCommand(“Select * from Employees”, con)
Dim da As New OleDbDataAdapter()
da.SelectCommand = cmd
Dim ds As New DataSet()
da.Fill(ds, “Employees”)
'Similarly you may use your DataAdapter (specify the options/attributes) to execute your stord procdure and fill the dataset / datatable accordingly using the relevant ADO.NET APIs.
'Open the template file.
Dim workbook As New Workbook(“e:\test2\Book1.xlsx”)
‘Import data fromt he datatable to the worksheet … Put last parameter i.e. inserRows to false, so you existing formatting in the
’excel sheet will not be distrubed and the formatting (bg color, fonts etc.) would be retained.
workbook.Worksheets(0).Cells.ImportDataTable(ds.Tables(“Employees”), True, 0, 0, False)
workbook.Save(“e:\test2\outimportdata.xlsx”)
Also, we recommend you to kindly see the document on what importing options Aspose.Cells provides so you may import data from a number of data sources to the Excel spreadsheets:
Thank you.
Thanks for the code. That is exactly what I need.
So my last question is;
You know my executed SP is dynamic SP and the columns are too.
So, is it possible to add borders of these cells automaticaly?
Hi,
Well, I am afraid, you have to do it (add borders to the cells in the sheets) either in the template Excel file’s sheet manually in MS Excel or you have to do it (dynamically using Aspose.Cells APIs) after you have imported data to the worksheet cells. Please see the topic on adding borders to the cells for your reference:
Let us know if you still have any confusion or issue.
Thank you.