problem with FileStream and excel

[Ben]

Hi,

I use FileStream to write data to excel 2003. It works good but I have two
problems:

1) the data written to excel come from a database. When i write the actual
data to excel, and then later and without closing the browser, i do it again
with the updated database (more or modified records), i still get the
records as sent the first time. I have to close the browser, then restarting
it to get the updated records in excel. Why is that and how to avoid closing
the browser?

2) now we use excel 2007 and there is an error saying something that the
version is not correct ..but excel 2007 opens with the written data.

The code: (vb.net)
Dim objFileStream As FileStream
Dim objStreamWriter As StreamWriter
fileExcel = "mysheet.xls" (with this: fileExcel = "mysheet.xlsx", excel 2007
doesn't open at all)
filePath = Server.MapPath("\myapp\excel")
fileName = filePath & "\" & fileExcel
objFileStream = New FileStream(fileName, FileMode.OpenOrCreate,
FileAccess.Write)
objStreamWriter = New StreamWriter(objFileStream)
objStreamWriter.WriteLine("this line must come into excel")
objStreamWriter.Close()
objFileStream.Close()

What have to change here for excel 2007 (except the "xlsx")?

Thanks
Ben

 

[Gregory A. Beamer]

Hi,

I use FileStream to write data to excel 2003. It works good but I have
two problems:

1) the data written to excel come from a database. When i write the
actual data to excel, and then later and without closing the browser,
i do it again with the updated database (more or modified records), i
still get the records as sent the first time. I have to close the
browser, then restarting it to get the updated records in excel. Why
is that and how to avoid closing the browser?

One of two things:

1. You have caching on
2. You have not refreshed the actual "data set" you are writing out to
Excel, despite having updated the data

2) now we use excel 2007 and there is an error saying something that
the version is not correct ..but excel 2007 opens with the written
data.

The code: (vb.net)
Dim objFileStream As FileStream
Dim objStreamWriter As StreamWriter
fileExcel = "mysheet.xls" (with this: fileExcel = "mysheet.xlsx",
excel 2007 doesn't open at all)
filePath = Server.MapPath("\myapp\excel")
fileName = filePath & "\" & fileExcel
objFileStream = New FileStream(fileName, FileMode.OpenOrCreate,
FileAccess.Write)
objStreamWriter = New StreamWriter(objFileStream)
objStreamWriter.WriteLine("this line must come into
excel")
objStreamWriter.Close()
objFileStream.Close()

What have to change here for excel 2007 (except the "xlsx")?

Unless you really want to hunt this dog, you are best to leave as XLS,
which Excel 2007 users can open.

If you must hunt, you are best to examine the file format docs:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=
33071438-110d-4845-9430-92775eacbee7

It is more than just writing a single file, btw. XLSX is a compressed
container that has multiple XML documents inside the compressed archive.
You cannot simply change XLS to XLSX.

 

[Ben]

Thanks for replying, but still not solved.

1)I inspected the file and web.config but could'nt find anything about
cache. Can you tell me where i should search, assuming cache is on?
2)The right table containing the data to be sent to excel is chosen with a
dropdownlist. The aspx file which produces the excel file (lets' call it
excel.aspx) reads in the DropDownList1_SelectedIndexChanged procedure the
present data from the database (with a select). So when i quit excel.aspx,
returning to the main menu of the application and then starting excel.aspx
again, it must read the new data from the database, no? So i don't know
where i could refresh the "data set". According to me, it's refreshed
because there is a new select statement.

Here my code:

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
Dim kz As String
Dim strLine As String
Dim i As Integer
Dim objFileStream As FileStream
Dim objStreamWriter As StreamWriter

kz = DropDownList1.SelectedValue

fileExcel = kz & ".xls"

filePath = Server.MapPath("\tts\excel")
fileName = filePath & "\" & fileExcel
If File.Exists(fileName) Then File.Delete(fileName)

objFileStream = New FileStream(fileName, FileMode.OpenOrCreate,
FileAccess.Write)
objStreamWriter = New StreamWriter(objFileStream)

connection.Open()
Dim sql As String = "SELECT * FROM " & kz
Dim cmd As SqlCommand = New SqlCommand(sql, connection)
dr = cmd.ExecuteReader()

While dr.Read()
For i = 0 To dr.FieldCount - 1
strLine = strLine & dr.GetString(i) & Chr(9)
Next
objStreamWriter.WriteLine(strLine)
strLine = ""
End While

dr.Close()
connection.Close()
objStreamWriter.Close()
objFileStream.Close()
End Sub

 

[Gregory A. Beamer]

Thanks for replying, but still not solved.

1)I inspected the file and web.config but could'nt find anything about
cache. Can you tell me where i should search, assuming cache is on?
2)The right table containing the data to be sent to excel is chosen
with a dropdownlist. The aspx file which produces the excel file
(lets' call it excel.aspx) reads in the
DropDownList1_SelectedIndexChanged procedure the present data from the
database (with a select). So when i quit excel.aspx, returning to the
main menu of the application and then starting excel.aspx again, it
must read the new data from the database, no? So i don't know where i
could refresh the "data set". According to me, it's refreshed because
there is a new select statement.

Here my code:

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As
Object, ByVal e As System.EventArgs) Handles
DropDownList1.SelectedIndexChanged
Dim kz As String
Dim strLine As String
Dim i As Integer
Dim objFileStream As FileStream
Dim objStreamWriter As StreamWriter

kz = DropDownList1.SelectedValue

fileExcel = kz & ".xls"

filePath = Server.MapPath("\tts\excel")
fileName = filePath & "\" & fileExcel
If File.Exists(fileName) Then File.Delete(fileName)

objFileStream = New FileStream(fileName,
FileMode.OpenOrCreate,
FileAccess.Write)
objStreamWriter = New StreamWriter(objFileStream)

connection.Open()
Dim sql As String = "SELECT * FROM " & kz
Dim cmd As SqlCommand = New SqlCommand(sql, connection)
dr = cmd.ExecuteReader()

While dr.Read()
For i = 0 To dr.FieldCount - 1
strLine = strLine & dr.GetString(i) & Chr(9)
Next
objStreamWriter.WriteLine(strLine)
strLine = ""
End While

dr.Close()
connection.Close()
objStreamWriter.Close()
objFileStream.Close()
End Sub

Dim cmd As SqlCommand = New SqlCommand(sql, connection)

WHere is the connection being instantiated? In this routine, it is
magic. Are you using a global (static) connection object?

The answer to that question is important, as SQL Server proper uses
transactions, even if you do not. It is possible, by using a "global"
connection object, to leave items uncommitted.

By default, the command object will read only committed items. Anything
on a connection that is not disposed, or does not have the command
committed, will stay uncommitted and in the transaction log.

You can read these items by switching your isolation level to read
uncommitted, but this can have other consequences, depending on the rest
of your code, so it is not my "best" solution.

If I were coding this, I would do somethign more like this:


Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
Dim kz As String
Dim strLine As String
Dim i As Integer
Dim objFileStream As FileStream
Dim objStreamWriter As StreamWriter

kz = DropDownList1.SelectedValue

fileExcel = kz & ".xls"

filePath = Server.MapPath("\tts\excel")
fileName = filePath & "\" & fileExcel
If File.Exists(fileName) Then File.Delete(fileName)

objFileStream = New FileStream(fileName,
FileMode.OpenOrCreate,
FileAccess.Write)
objStreamWriter = New StreamWriter(objFileStream)

connection.Open()
Dim sql As String = "SELECT * FROM " & kz

' Get connection string from routine (this is more
' so I can avoid assumptions in your code). you will
' have to code the routine to do it this way
Dim connString = GetConnectionString

'Changed name to avoid clashes
Dim conn as New SqlConnection(connString)

'Name change here as well
Dim cmd As SqlCommand = New SqlCommand(sql, conn)

Try
conn.Open()
dr = cmd.ExecuteReader()

While dr.Read()
For i = 0 To dr.FieldCount - 1
strLine = strLine & dr.GetString(i) & Chr(9)
Next
objStreamWriter.WriteLine(strLine)
strLine = ""
End While

dr.Close()
connection.Close()
objStreamWriter.Close()
objFileStream.Close()

Finally
conn.Dispose()
End Try
End Sub

Now, you might argue, "but closing the connection creates overhead", but
the connection will go back into the pool and have items committed when
you opt for this pattern, so it is not an issue.

For the record, it is problematic to try to use a single connection
object directly for performance reasons (if that is why the magic global
connection object pattern was used). This is not saying there are not
times to use a connection in this manner, but there are fewer global
connection scenarios than create/destroy scenarios.

I think adopting the pattern I suggest will probably solve your problem.
You will likely have to employ it on the save to the database, as well.

 

[Ben]

Thanks again, Gregory

i show you my "magic" connection string which is in the Sub Page_Load
procedure. So you can have a better idea of my problem:

Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Partial Class excel
Inherits System.Web.UI.Page
Friend connection As SqlConnection
Friend dr As SqlDataReader
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

Dim connectionstr As String
Dim comd As SqlCommand
connectionstr =
ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
connection = New SqlConnection(connectionstr)
comd = New SqlCommand()
comd.Connection = connection

.......
End Sub

And in directory App_Code, there is a file.vb with this:

Imports Microsoft.VisualBasic
Public Class param
Public Shared ReadOnly Property ConnectionString() As String
Get
Return
ConfigurationManager.ConnectionStrings("myconn").ConnectionString
End Get
End Property
End Class

 

[Gregory A. Beamer]

Thanks again, Gregory

i show you my "magic" connection string which is in the Sub Page_Load
procedure. So you can have a better idea of my problem:

Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Partial Class excel
Inherits System.Web.UI.Page
Friend connection As SqlConnection
Friend dr As SqlDataReader
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

Dim connectionstr As String
Dim comd As SqlCommand
connectionstr =
ConfigurationManager.ConnectionStrings ("myconn").ConnectionString.ToStr
ing()
connection = New SqlConnection(connectionstr)
comd = New SqlCommand()
comd.Connection = connection

......
End Sub

And in directory App_Code, there is a file.vb with this:

Imports Microsoft.VisualBasic
Public Class param
Public Shared ReadOnly Property ConnectionString() As String
Get
Return
ConfigurationManager.ConnectionStrings("myconn").ConnectionString
End Get
End Property
End Class

The static (Shared) property to get hte connection string is fine. The
mostly likely issue is the pulling of hte connection in Page_Load to be
used all over the place.

In general, the pattern is to instantiate a connection object for every
call you make. When you Dispose(), it is not really killed off, but put
into the pool until connection pool timeout, which is 30 seconds, by
default.

But, it does commit all SQL transactions (not transaction objects, as
they have to be committed in code, but the actual signal to SQL it is
done and they can be committed from the transaction log). This means any
work you might have done while that object was spun up can now be used.

This may not solve all of the problems, but it is a better pattern, and
does prevent some problems from happening.

If I can get a chance tonight, I will see if I can run through the code
you have sent on this thread and see anything strange.

Where is the code that saves the new items to the database? Did I miss
that?

 

[Ben]

Where is the code that saves the new items to the database? Did I miss

that?

It's in the Sub DropDownList1_SelectedIndexChanged in my second mail ...

 

[Ben]

Hi Gregory,

i read carefully all our messages about this problem, but i still don't know
why and where exactly my code fails to export the updated data ...