on 2010 May 14 5:29 PM
I'm just planning a facility to export varying query results as Excel worksheets. This will be implemented in an older VBA application and is focussed on end users. Therefore I would like to have a "ready" Excel sheet with appropriate column formats - e.g., when dealing with date columns, users should be able to sort chronologically without first having to format those columns as dates (vs. text).
Basically I seem to have at least those options with help of SQL Anywhere's features (with unsatisfying results so far):
Use DBISQLG's OUTPUT statement with FORMAT EXCEL (or with USING an Excel-DSN)
This works generally (and could be used with options "-nogui -q" to run in the background). Drawbacks: Old Excel 2.1 format, columns seem to be regarded as text (with possibly "wrong" sort order), requires the Java ISQL (which is not wanted in my case).
Use DBISQLC's OUTPUT statement with FORMAT LOTUS
Drawbacks: As dbisqlc does not support the EXCEL format, format LOTUS seems to be the most similar. However, this format gives a very uncommon excel experience w.r.t. to layout and seems to get blocked completely under Excel 2003 and above.
Use proxy tables based on Excel ODBC driver
This requires to setup an Excel ODBC DSN beforehand and to declare an according remote server (cf. Breck's article with a similar approach to import Excel data.).
Drawbacks: For each worksheet with different schema, a table has to be created beforehand in order to specify column names and types. This might require to parse/interpret an "on-the-fly" generated SQL statement, which is not trivial IMHO. (SA 10 and above support this with the sa_describe_query procedure.) So this might require to temporarily create and drop remote tables just to create a remote resultset.
Besides that, the Excel ODBC driver seems quite restricted in the way it recognizes column types, and therefore "wrong column types" might result here, too.
My questions:
Based on the very helpful suggestion by TDuemesnil, I re-organized my approach:
I compared a few facilities to export data from MS Access to MS Excel (both V2003) and found out that even then, the resulting Excel files are not well formatted IMHO. Whereas numerical columns are mostly o.k., dates are usually exported as text and therefore cannot by ordered as expected without first reformatting them. Given there are several builtin ways in MS Access to export to Excel (primarily DoCmd.TransferSpreadsheet and DoCmd.OutputTo), I was really surprised of the rather poor results.
So I decided that I should take the opposite direction and think this is the better way:
If I want an optimal Excel formatting, I should not rely on any RDBMS's Excel export. Instead it is better to use Excel VBA from the calling application to create an worksheet, fill it with the resultset (by means of Excel's Range.CopyFromRecordset method), and explicitely format the wanted columns with Excel's own tools.
So my solution now uses something like the following code snippet:
' rs is an open recordset.
' xlSht an open Excel.Worksheet, xlRng an open Excel.Range
If Not rs.EOF Then
Call FormatExcelSheetColumns(rs, xlSht)
' Import data starting with the 2nd row
Set xlRng = xlSht.Cells(2, 1)
Call xlRng.CopyFromRecordset(rs)
End If
The mentioned VBA subroutine FormatExcelSheetColumns does the interesting formatting part:
Based on the resultset's column names and types, it creates a first row with the column headings and then formats the columns accordingly.
Sub FormatExcelSheetColumns(rs As Recordset, xlSht As Excel.Worksheet)
Dim xlCol As Excel.Range
Call xlSht.Rows(1).Insert(xlShiftDown)
' Note: rs.Fields-Collection is 0-based, xlSht.Columns is 1-based
Dim nFieldIndex As Integer
Dim nColIndex As Integer
For nFieldIndex = 0 To rs.Fields.Count - 1
Dim strFieldName As String
Dim fld As Field
nColIndex = nFieldIndex + 1
Set fld = rs(nFieldIndex)
' get column headings
strFieldName = fld.Name
' make column headings bold and centered
xlSht.Cells(1, nColIndex).Value = strFieldName
xlSht.Cells(1, nColIndex).Font.Bold = True
xlSht.Cells(1, nColIndex).HorizontalAlignment = xlCenter
Set xlCol = xlSht.Columns(nColIndex)
With xlCol
Select Case fld.Type
Case dbText
' adjust text fields to a reasonable size
.ColumnWidth = fld.Size / 2
Case dbDouble
' Based on user-defined types or column name prefixes, it is possible
' to refine the format, i.e. choose different formats for monetary,
' percentual and ordinary numerical values, here given as flags:
If bIsPercentage Then
.Style = "Percent"
ElseIf bIsMoney Then
.Style = "Currency"
Else
.Style = "Comma"
End If
Case dbLong
' e.g. use a fixed number without thousand separator
.NumberFormat = "#;-#;0"
.HorizontalAlignment = xlRight
Case dbByte, dbBoolean
If bIsFlag Then
' Boolean values as Yes/No
.NumberFormat = """Yes"";;""No"""
.HorizontalAlignment = xlCenter
End If
Case dbDate, dbTimeStamp
.NumberFormat = "dd.mm.yyyy" ' German conv.
.HorizontalAlignment = xlRight
End Select
End With
Next nFieldIndex
End Sub
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I have regular Spreadsheet that i need to fill with Data from our ASA Server. I use this VBA Script in Excel 2003 that connects with a ADODB connection, takes a connectionstring and a select statement.
Then i loop through the result set and push the Data in the correct fields. Existing Field formats will be preserved.
Private Function GetRecordSet(sheetName As String, strSQL1 As String, UserName As String, Password As String) As Boolean
Dim cnn As ADODB.connection
Dim rs1 As ADODB.Recordset
Dim Form As PasswordForm
Dim strConn As String
Dim i As Integer
Rem Stop Excel from recalculate with each Cell change
Application.Calculation = xlCalculationManual
Rem Connection String
strConn = "Provider=ASAProv.90;ENG=xxx;DBN=yyy;LINKS=TCPIP{host=x.x.x.x:2638,DOBROAD=NONE}"
Rem Setup Error Handlung
On Error GoTo ConnectError
Rem Prepare Connection and Login
Set cnn = New ADODB.connection
cnn.Open strConn, UserName, Password
Rem Error Handling
On Error GoTo RetrivalError
Rem Prepare Recordset
Set rs1 = New ADODB.Recordset
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Dim sheet As Worksheet
Set sheet = Sheets(sheetName)
sheet.Visible = True
Rem Only remove existing content from the worksheet !!
sheet.Cells.ClearContents
Dim field As ADODB.field
Dim fieldIndex As Integer
fieldIndex = 1
For Each field In rs1.Fields
sheet.Cells(1, fieldIndex).Value = field.Name
fieldIndex = fieldIndex + 1
Next field
Dim colCount As Integer
colCount = rs1.Fields.count
Dim rowIndex, index As Integer
rowIndex = 2
count = 0
While Not rs1 Is Nothing And Not rs1.BOF And Not rs1.EOF
For index = 1 To colCount
sheet.Cells(rowIndex, index).Value = rs1(index - 1).Value
Next index
rowIndex = rowIndex + 1
count = count + 1
rs1.MoveNext
Wend
rs1.Close
Set rs1 = Nothing
On Error GoTo 0
cnn.Close
Set cnn = Nothing
Rem Restart Excel automatic recalculate
Application.Calculation = xlCalculationAutomatic
GetRecordSet = True
Exit Function
ConnectError:
MsgBox ("Connection Error")
GetRecordSet = False
Exit Function
RetrivalError:
MsgBox ("Error during data retrival")
GetRecordSet = False
Exit Function
End Function
I think you can adopt this to your needs
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Though the DBISQL option will work, I almost always do one of 2 things.
1) If it is a one time thing, I use UNLOAD TABLE/SELECT which dumps to CSV. Then I just use Excel to open the csv file and convert it to the native excel format
2) If it is a regular query/report I want to mov to excel, I use the "From Other Sources" option on the data tab and use "From Microsoft Query" which lets me choose a DSN and then write the query I want to execute to get my data.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Jason: That's what I would do, too (and have often used the first option with automated Excel "import macros"). But my goal here is to create a programmatic facility for end users who are not at all supposed to work with data conversion tools or MS Query. Therefore my focus is on automatically "well-formatted" worksheets...
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.