Using Oledb To Export Excel File Returns Empty File
Feb 24, 2010
I am using asp.net oledb to export information to excel file. I encounter problems when the information to export becomes too big, in this case the code I have given below, the excel file generated becomes an empty spreadsheet. If I changed the loop to 1123 for insertion of the rows. The generated excel file is fine, 1125 rows, and 4 columns shown. A test program in windows form is also working fine regardless of how many rows.
Code has been simplified, "information ..." in the sql insertion command represents 1803 characters.
ExcelObjConn = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
"Data Source=" & fileName & ";Extended Properties=Excel 12.0 XML"
ExcelConnection = New System.Data.OleDb.OleDbConnection(ExcelObjConn)
ExcelConnection.Open()
Try
SqlCommand = "CREATE TABLE ABC ([row1] text, [row2] text, [row3] text, [row4] text)"
ExcelCommand = New OleDb.OleDbCommand(SqlCommand, ExcelConnection)
ExcelCommand.ExecuteNonQuery()
ExcelCommand.Dispose()
For i As Integer = 0 To 1124
SqlCommand = "Insert into ABC ([row1], [row2], [row3], [row4]) Values ('information...', 'information ...', 'information ...', 'information ...')"
ExcelCommand = New OleDb.OleDbCommand(SqlCommand, ExcelConnection)
ExcelCommand.ExecuteNonQuery()
ExcelCommand.Dispose()
Next
Catch ex As Exception
Finally
If ExcelConnection IsNot Nothing Then
ExcelConnection.Close()
ExcelConnection.Dispose()
End If
End Try
I couldn't find a solution to my problem as well. What I did eventually was to run the process using another separate windows service. The code works perfectly fine running from a windows form or service program, but not asp.net, not sure why.
But some times the file that is read, reports that there is more rows than there actually are. i.e. I only have 300 lines of data but the read reads in 500. My question is, and I am sure I have seen this somewhere but I cannot find it now, is how do I get the reader to stop reading at the first blank line?
I have tried using GridView control to display the data from SqlDataSource control but there are just so many columns and one of the fields contains a lot of texts so using GridView control to display the data does not look look since it goes out of the master template. I have tried using GridView control to display my data and use the following code to export as Excel but in case, there are just so many columns.
[Code]....
What are some other better ways to export to Excel file from my web page?
I am working in a project where i need to export the Gridview data into an Excel file. after that in the newly geretated excel sheet i want to put some formula so that it gives the sum of all the columns. Exporting data into an excel file is done Using following code
[Code]....
how can i enter formula dynamically in excel sheet.
on use this code on button click event.. file is generating but when i use same file for import in to database It gave me Error : External table is not in the expected format. For prob. of Format Type for Excel.
I am trying to import data from excel and Reading Excel sheet from asp.net returns empty cell, when the data type is changing from number to text example
I have an some code to export data form an SQL database to an Excel spread sheet. the data is entered via a web site and some of it contains multiple lines of information. I need to preserve the multiple lines but excel 2007 continues to put an [?] at the end of each line.Here's what I got so far
CellValue.ToString() // this returns [?][?] at the end of each line for excel 2007 // and a single [?] in excel 2010 CellValue.Replace("[//r]","") // this works correctly for 2010 but still leaves a [?] for 2007 Cellvalue.Replace(Environment.NewLine, "") // this removes all the line breaks.
what can I replace the Environment.NewLine with in order to still have line breaks in both excel 2007 and 2010 (with no [?]) ?
I'm trying to export data from an asp.net page to an Excel file and I'm basically just outputting HTML to the XLS file and let Excel interprets it, by using this type of coding:
[Code]....
Although this is doing fine for exporting raw text, I now have to export to the same Excel file an image. However, the same kind of method I'm using right now only seems to insert a "linked" image, I mean, the image appears in the file, however, the user viewing the file has to be online since it's a direct link to the file in the server. I would like to able to "embeded" the image in the file, just like how it's made when you insert manually an image using Excel.
However, after some online research I can't seem to find any solution for this.
I can't use Microsoft.Office.Interop.Excel library, and a third party software isn't advisable also, that's what made me try out the HTML output solution, which worked ok for the text. But now, for inserting an image, what can I do? Is it possible to achieve what I pretend?
anybody working on data exporting? I've 4 tables in my sql server database. I want to export these 4 tables data into one excel file in different sheets with column names.
i have different tables for product details, images, category mapping
is there any way to import/export products by excel file in database along with category mappying and pictures in one go rather that importing just the products and then categorizing each product and adding image one by one ?
I am using the following code to export a datset to excel sheet.
[Code]....
Problem is that its not raising file download and hence no export is taking place.The same code works fine in a normal method.But with the webmethod its not working.
I need to export SQL query results to an Excel file.
I use the following code to do it.
[Code]....
Works fine for majority of the cases. There is a column called Description in the query results which is of type nText in the sql database. This can have lengthy data ( couple fo paragraphs) . When the number of charcters are large only the first portion of the data is showed and it trims the rare portion. how I can avoid this? Or any efficient code example on how I can do this in a different manner
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then Dim dataSet1 As New DataSet dataSet1 Dim dataSet2 As New DataSet dataSet2 Dim dataSet3 As New DataSet dataSet3
Dim dataSets As New List(Of DataSet)() dataSets.Add(dataSet1) dataSets.Add(dataSet2) dataSets.Add(dataSet3) Dim strout As String = Server.MapPath("test1.xls") DataSetsToExcel(dataSets, strout) End If End Sub
[Code]..
How to create each excel sheet non-editable (read only)
i want to export an excel from my web application and want to save it on Web Server, but i am not able to save it on web server, i am able to do it on my local machine, but on web server it's not working.
