Saving data from the Grid Control to Excel or SQL

Hi!

I have a Grid Web Object on my ASP.Net form, along with an asp.net fileupload control to get the filename I want to upload. I locate and upload a spreadsheet that contains 3 worksheets into my Grid Web Object. That works very well. I can even edit the data.

But, when I hit the 'Save' button, I have no clue what to do to get the data from the current worksheet into a SQL table. I can't even figure out how to tell which worksheet I'm on. If you could post some clear, readable sample code in VB.Net that takes the data from a single worksheet (the currently selected tab) and inserts each row and column (say the first 25 columns) into a SQL table, I'd appreciate it. Let's assume we have a table called tblImport that has just 26 columns (25 for the data and one for an identity column). The columns are called ImportID, F1, F2, F3 . . . F25. How do I get the data from the worksheet into this table?

I'm not even able to get the code you see below to work to save any changes I made to the data while it was displayed on the screen, back to the Excel file itself. What am I doing wrong? Please let me know.

Karen

______________________________________________________________________________________

Imports System.IO

Imports Aspose.Grid

Partial Class Inventory_Update

Inherits System.Web.UI.Page

Public ExcelFileName As String

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

ExcelFileName = FileUpload1.PostedFile.FileName 'asp.net fileupload control lets you browse

Label1.Text = ExcelFileName 'just to see the full pathname

GridWeb1.WebWorksheets.Clear()

GridWeb1.WebWorksheets.ImportExcelFile(ExcelFileName)

End Sub

Protected Sub GridWeb1_SaveCommand(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridWeb1.SaveCommand

GridWeb1.WebWorksheets.SaveToExcelFile(ExcelFileName)

End Sub

End Class

Hi,

I will answer your question about posting file first.

I read your code and I think that you didn't use the HtmlInputFile control correctly. The HtmlInputFile post the client-side file as a stream to the server-side. The filename is the client-side file's name. If the client-side and the server-side are two different mechine, you can't get the file with the file name at server-side. But you can get the file's stream at the server-side. Try to modify your code like this:

GridWeb1.WebWorksheets.ImportExcelFile(FileUpload1.PostedFile.InputStream)

And when you want to save the file at the server-side ,you should give the server-side file path instead of the client-side filename.

I'm sorry, but you've lost me. I was able to import the spreadsheet using the input stream syntax, but I don't have an example of how to save the file back using that syntax. The example in the doumentation shows the full pathname, which is what I thought I was doing.

So, I guess I still need to see some sample code that opens and saves an Excel file, or the syntax for using the full file pathname if I can use that. Feel free to copy my code and revise it as necessary. I still don't see what I was doing wrong in the 'save' command. The example in the WorkingWithExcelFileWeb is exactly what I have, except that I'm using a variable for the full file pathname. The syntax isn't giving me an error or anything, but when I open the file again after saving it, the data hasn't been saved.

And, I still don't know how to identify which worksheet tab the user is currently viewing - the currently 'active' worksheet. Is that possible?

And, I still need to see some sample code that will allow me to save the data on that worksheet to a SQL table.

I"m sorry to keep asking, but I really need to get this working.

Thanks!

Karen

Thank you! I think I understand what you're saying about client side vs. serverside. What I understand you to mean is this:

When the user navigates to a spreadsheet and opens it, the filepath coming back from the 'fileupload' control refers to their filesystem. That is, if they point to the 'C' drive, it's the 'C' drive on their workstation. But, when I process the 'save' command within the server-side code (the 'VB' code behind subroutine), the 'C' drive would probably refer to the 'C' drive on the server, not the user's workstation. I kind of understand that. But what I don't understand is how to save the Excel file back to the same place the user read it up from. How do I do that? I guess the question is how do I get the full network filepath, not just the local pathname.

What's confusing me is that I'm actully running the code and reading up a file from our development workstation, so really the two pathnames would be the same, at least I think so.

Sorry, I guess I just don't get this.

Thank you!!!

Karen

Hi,

I think the problem that you can't saving the file to the same mechine is probably caused by the filesystem's security settings. The asp.net application is running under a system user "ASPNET" by default. So it might haven't the access right to save file to some place. Is your drive's filesystem NTFS? If so, check the folder that containing your excel files. See the folder's security setting by right-click the folder and select properties. Try to modify the security setting to enable user "everyone" or "ASPNET" to read/write in this folder.

After doing that you will solve the problem of reading/writing files on one standalone machine. But you can't let your application run in a true client - server environment. To send the client the modified excel file, you may use the below code:

Private Sub GridWeb1_SaveCommand(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridWeb1.SaveCommand
' Creates a memory stream.
Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream()

' Saves to the stream.
Me.GridWeb1.WebWorksheets.SaveToExcelFile(ms)

' Sents the file to browser.
Response.ContentType = "application/vnd.ms-excel"

'Adds header.
Response.AddHeader("content-disposition","attachment; filename=book1.xls")

' Writes file content to the response stream.
Response.OutputStream.Write(ms.GetBuffer(), 0, CType(ms.Length, Integer))

' OK.
Response.End()

End Sub

At runtime, when user click the save button, he will get a saving file dialog, and he can accept the file and save the file to some place on his client-side computer.

You may use the GridWeb.WebWorksheets.ActiveSheetIndex to get the ActiveSheet.

WebWorksheet sheet = GridWeb1.WebWorksheets[GridWeb1.WebWorksheets.ActiveSheetIndex];

(VB)

Dim sheet As WebWorksheet = GridWeb1.WebWorksheets(GridWeb1.WebWorksheets.ActiveSheetIndex)

And I will show you how to save data to database later.

You may use the WebCells.Export method to export the sheet data to a datatable object. Create a OleDbDataAdapter object and use it's Update method to save the datatable's data to your database.

Dim dataTable As DataTable = New DataTable()
dataTable.Columns.Add("f1",System.Type.GetType("System.String"))
dataTable.Columns.Add("f2")
dataTable.Columns.Add("f3")
dataTable.Columns.Add("f4",System.Type.GetType("System.Double"))
dataTable.Columns.Add("f5",System.Type.GetType("System.Int32"))
dataTable.Columns.Add("f6",System.Type.GetType("System.Int32"))

GridWeb1.WebWorksheets(0).Cells.Export(dataTable,1,0,10,6,False)
Dim adp As OleDbDataAdapter = New OleDbDataAdapter("select f1, f2, f3, f4, f5, f6 from table1", OleDbConnection1)
Dim cmdbuilder As OleDbCommandBuilder = New OleDbCommandBuilder(adp)
adp.Update(dataTable)

Hi!

Thanks for the info on the export method. I'm not quite sure, however. what the number '10' represents below. Is that the number of rows? I may not know in advance the number of rows in the uploaded spreadsheet, however. Is there a way for me to count the rows in an uploaded spreadsheet so I can be sure to export them all, or something I can set here to export all rows?

GridWeb1.WebWorksheets(0).Cells.Export(dataTable,1,0,10,6,False)

I also wanted to make sure that using a SQL data adapter will work as well as an ole data adapter. Is that correct?

I understand you may be working on an update that will make exporting to a SQL table easier. If so, I'd appreciate any information you can provide on that.

Thanks!

Karen

Hi,

1. Yes, the number "10" means the number of rows that you want to export. You may use the WebCells.MaxRow property to get the max row number of the sheet. For example, if there is 5 rows in the sheet, the MaxRow will return 4. And you may use this value to calculate the rows to export.

2. Yes, if you are using SQL Server, the sql dataadapter will work as well as the olddb dataadapter.

3. The Export method doesn't support committing changes to a datatable, it only add all its data to a datatable. The new version 1.7 has a more powerful databinding feature, it enables you bind a sheet to a table and update changes to the database.

You may see our wiki document:

Binding Sheet With Designer

Aspose.Grid - Binding Sheet at RunTime

And you may see the online demos here:

Aspose.Grid Demos

At the home page, click "DataBinding Demos".

Thank you.