Subject: Problem w/ Exporting .csv file to Excel
Here is some code that can be used to export to Excel.
Just create a LS and put this in the declarations section.
'text constants
Const BACKSLASH$ = ""
Const FRONTSLASH$ = “/”
'excel object constant
Const APPLICATION_EXCEL$ = “Excel.Application”
'excel row and column limitations
Const ROW_UPPER_LIMIT& = 65536
Const COLUMN_UPPER_LIMIT& = 256
'sheet name constants for deleting extraneous sheets from the workbook
Const SHEET_2$ = “Sheet2”
Const SHEET_3$ = “Sheet3”
'position constants for adding a sheet. position determines if sheet comes before or after target sheet
Const SHEET_POS_BEFORE% = 0
Const SHEET_POS_AFTER% = 1
'position of month, day and year values for use in creating a report date for generic file name.
'used in conjunction with StrToken function
Const POSITION_MONTH% = 1
Const POSITION_DAY% = 2
Const POSITION_YEAR% = 3
'format constant for formatting dates correctly
Const FORMAT_DATE_MMDDYYYY = “mm/dd/yyyy”
'excel constants–must be defined because Notes does not natively know them. constants are equivalent to
'constant names in excel
Const xlContinuous% = 1
Const xlEdgeBottom% = 9
Const xlMedium% = 3
Const xlR1C1% = 2
'file name constants. used if no file path or file name is specified.
Const GENERIC_FILE_NAME$ = “ExcelReport.xls”
'notes constants
Const ENV_DIR$ = “Directory”
'error constants
Const ERR_TOO_MANY_ROWS$ = "The amount of rows indicated exceeds the amount of rows that an " +_
“Excel worksheet can contain. Report aborted.”
Const ERR_TOO_MANY_COLUMNS$ = "The amount of columns indicated exceeds the amount of columns " +_
“that an Excel worksheet can contain. Report aborted.”
Const ERR_IS_NOT_NUMERIC$ = “The column indicated is not numeric. This report will be aborted.”
Const ERR_END_GREATER_THAN_START$ = "The row indicated to start with is larger than the row indicated " +_
“to finish with. This report will be aborted.”
Class ExcelWorkSheetExtender
'this class is intended to take the guesswork out of generating excel reports from Notes data. It allows a
'programmer to create a new instance of an excel application and get a handle on an excel sheet. It also
'allows a programmer to easily import Notes data into an excel sheet so long as the data is passed as a
'String List object using the column number as the Listtag.
'Plans for future revisions of this class will be to allow a user to total cells based on cell coordinates,
'the ability to create charts, and the ability to add Excel formulas to cells.–[bf, 10/26/07]
m_s As NotesSession
m_dt As NotesDateTime
m_xlapp As Variant
m_xlsheet As Variant
m_targetXLSheet As Variant
m_row As Long
m_col As Long
m_minRow As Long
m_maxRow As Long
m_minCol As Long
m_maxCol As Long
m_filePath As String
m_fileName As String
m_workSheetName As String
m_isVisible As Boolean
m_closeExcel As Boolean
m_total As Double
x As Long
Sub New(isVisible As Boolean,displayAlerts As Boolean)
'Create the Excel Object, render it (in)visible, and delete unnecessary sheets. Set xlsheet to the remaining
'sheet.
Set Me.m_s = New NotesSession
Me.m_isVisible = isVisible
Set Me.m_xlapp = CreateObject(APPLICATION_EXCEL)
With Me.m_xlapp
.Visible = isVisible
.Workbooks.Add
.ReferenceStyle = xlR1C1
.DisplayAlerts = displayAlerts
.activeWorkbook.Worksheets(SHEET_2).Delete
.activeWorkbook.Worksheets(SHEET_3).Delete
End With
Set Me.m_xlsheet = Me.m_xlapp.Workbooks(1).Worksheets(1)
End Sub
Sub CreateHeaderRow(headers List As String,headerRowPos As Long,isBold As Boolean,_
isUnderLined As Boolean)
'This method populates the excel header row and allows for minor formatting. It calls the PopulateRow
'method to actually populate the cell data.
Call PopulateRow(headers,headerRowPos)
Me.m_xlapp.Range(Me.m_xlsheet.Cells(Me.m_row,Me.m_minCol),_
Me.m_xlsheet.Cells(Me.m_row,Me.m_maxCol)).Select
If isBold Then
Me.m_xlapp.Selection.Font.Bold = True
End If
If isUnderLined Then
With Me.m_xlapp.Selection.Borders(xlEdgeBottom)
.Linestyle = xlContinuous
.Weight = xlMedium
End With
End If
End Sub
Sub PopulateRow(cellData List As String,rowPos As Long)
'This method populates the cellData in the specified row. This method expects that the cellData list passed
'to the method uses a numeric value converted to a string for the ListTag. This numeric value is then
'converted to a Long value to specify the correct column to place the actual List value.
Me.m_row = rowPos
If Me.m_row > ROW_UPPER_LIMIT Then 'check to see if row # exceeds excel limits. If so, error out
Print ERR_TOO_MANY_ROWS
Exit Sub
End If
'Once we determine that the row # is within limits, figure out if it is the minimum or maximum row value.
'this will be used later if the FormatSheetAutofitSelection sub is called
If Me.m_minRow = 0 Then
Me.m_minRow = Me.m_row
Elseif Me.m_minRow > Me.m_row Then
Me.m_minRow = Me.m_row
End If
If Me.m_row > Me.m_maxRow Then
Me.m_maxRow = Me.m_row
End If
'Once we have determined if the row # is min or max or not, then go through all elements of the cellData list
'Convert the listtags into the column number. Any script leveraging this class should always set the listtags
'equal to the column number (or if using A1 reference style, letters,although this class is designed for R1C1)
Forall cellDatum In cellData
If Isnumeric(Listtag(cellDatum)) Then
Me.m_col = Clng(Listtag(cellDatum))
If Me.m_col > COLUMN_UPPER_LIMIT Then 'make the same check as for Row limit
Print ERR_TOO_MANY_COLUMNS
Exit Sub
End If
'find min and max column #s, just like with rows
If Me.m_minCol = 0 Then
Me.m_minCol = Me.m_col
Elseif Me.m_minCol > Me.m_col Then
Me.m_minCol = Me.m_col
End If
If Me.m_col > Me.m_maxCol Then
Me.m_maxCol = Me.m_col
End If
Else
Print ERR_IS_NOT_NUMERIC
Exit Sub
End If
Me.m_xlsheet.Cells(Me.m_row,Me.m_col).Value = cellDatum
End Forall
End Sub
Sub FormatSheetAutofitSelection
'This sub takes the min and max column and row values, selects the range and then sets it to autofit
Me.m_xlapp.Range(Me.m_xlsheet.Cells(Me.m_minRow,Me.m_minCol),_
Me.m_xlsheet.Cells(Me.m_maxRow,Me.m_maxCol)).Select
Me.m_xlapp.Selection.Columns.Autofit
End Sub
Sub SaveReport(closeExcel As Boolean)
'This sub sets filepath or filename and then saves the document to that path.
If Me.m_filePath = "" Then 'if filepath is not defined, try and find a filename and save to the data directory
If Me.m_fileName <> "" Then 'if file name is defined, set to data dir + filename
Me.m_filePath = Me.m_s.GetEnvironmentString(ENV_DIR,True) + Me.m_fileName
Else 'Otherwise, generate generic file name (Date+ExcelReport.xls) and save to data dir
Me.m_filePath = Me.m_s.GetEnvironmentString(ENV_DIR,True) + GetReportDate +_
GENERIC_FILE_NAME
End If
End If
Me.m_xlapp.ActiveWorkbook.SaveAs Me.m_filePath 'save file
If Not Me.m_isVisible Then 'if Excel is not visible, the application MUST close
Me.m_closeExcel = True
Else 'if it is visible, allow parameter to choose whether or not to close the program
Me.m_closeExcel = closeExcel
End If
If Me.m_closeExcel Then 'If excel should be closed, close the workbook and quit the application
Me.m_xlapp.ActiveWorkbook.Close
Me.m_xlapp.Quit
End If
End Sub
Sub AddSheet(sheetPos As Integer,sheetName As String)
'This sub allows for a new sheet to be added. The new sheet immediately becomes the new active sheet
Me.m_targetXLSheet = Me.m_xlsheet
Select Case sheetPos 'find out if new sheet comes before or after target sheet and place it accordingly
Case SHEET_POS_BEFORE
Me.m_xlsheet = Me.m_xlapp.ActiveWorkbook.Worksheets.Add(Me.m_targetXLSheet)
Case SHEET_POS_AFTER
Me.m_xlsheet = Me.m_xlapp.ActiveWorkbook.Worksheets.Add("",Me.m_targetXLSheet)
End Select
Me.m_workSheetName = sheetName
If Me.m_workSheetName <> "" Then 'if a sheetName has been defined, set it as the active sheet's name.
Me.m_xlsheet.Name = Me.m_workSheetName
End If
End Sub
Sub TotalColumn(colPos As Long,startRowPos As Long,endRowPos As Long,totalRowPos As Long,_
totalColPos As Long,isBold As Boolean,isUnderLined As Boolean,Autofit As Boolean)
'this sub will take the contents of a column, check to see if the contents of the cells are numeric and
'if so, will total the column below. It also allows for you to add some limited formatting to the total cell
If startRowPos > endRowPos Then 'start Row MUST be smaller than endRow
Print ERR_END_GREATER_THAN_START
Exit Sub
End If
For x = startRowPos To endRowPos
If Isnumeric(Me.m_xlsheet.Cells(x,colPos).Value) Then 'figure out if cell has numeric value and add to
'total
Me.m_total = Me.m_total + Cdbl(Me.m_xlsheet.Cells(x,colPos).Value)
Else 'if a value is not numeric, quit the sub completely and throw error
Print ERR_IS_NOT_NUMERIC
Exit Sub
End If
'Once the total has been calculated, add it to a cell and format if necessary
Me.m_xlsheet.Cells(totalRowPos,totalColPos).Value = Me.m_total
If isBold Then
Me.m_xlsheet.Cells(totalRowPos,totalColPos).Font.Bold = True
End If
If isUnderLined Then 'NOTE: this underline will be placed on the final individual cell accessed to
'generate the total, not on the total cell itself
With Me.m_xlsheet.Cells(endRowPos,colPos).Borders(xlEdgeBottom)
.Linestyle = xlContinuous
.Weight = xlMedium
End With
End If
If AutoFit Then
Me.m_xlsheet.Cells(totalRowPos,totalColPos).Columns.Autofit
End If
Next
End Sub
Private Function GetReportDate As String
'This function returns the current date to add to a generic spreadsheet title if no filename or path have
'been provided. At current, it is not available as a method to be called by an outside script using this class
Set Me.m_dt = New NotesDateTime(Today)
GetReportDate = Format(Me.m_dt.DateOnly,FORMAT_DATE_MMDDYYYY)
GetReportDate = Strtoken(GetReportDate,FRONTSLASH,POSITION_MONTH) +_
Strtoken(GetReportDate,FRONTSLASH,POSITION_DAY) +_
Strtoken(GetReportDate,FRONTSLASH,POSITION_YEAR)
End Function
Public Property Get FilePath As String
FilePath = Me.m_filePath
End Property
Public Property Set FilePath As String
Me.m_filePath = FilePath
End Property
Public Property Get FileName As String
'This property will try an exhaustive attempt to find the filename of the excel spreadsheet. If the filename is
'not already defined, it will attempt to extract if from the filepath, if it exists… If not, it returns NULL
If Me.m_fileName = "" Then
If Me.m_filePath <> "" Then
If Instr(Me.m_filePath,BACKSLASH) <> 0 Then
Me.m_fileName = Strrightback(Me.m_filePath,BACKSLASH)
Else
Me.m_fileName = Me.m_filePath
End If
End If
End If
FileName = Me.m_filePath
End Property
Public Property Set FileName As String
Me.m_fileName = FileName
End Property
Public Property Get WorksheetName As String
WorksheetName = Me.m_workSheetName
End Property
Public Property Set WorkSheetName As String
'This property, when set, will also set the worksheet name on the excel sheet.
Me.m_workSheetName = WorkSheetName
Me.m_xlsheet.Name = Me.m_workSheetName
End Property
Public Property Get CurrentRowPosition As Long
CurrentRowPosition = Me.m_row
End Property
Public Property Get CurrentColumnPosition As Long
CurrentColumnPosition = Me.m_col
End Property
Public Property Get FirstRowPosition As Long
FirstRowPosition = Me.m_minRow
End Property
Public Property Get LastRowPosition As Long
LastRowPosition = Me.m_maxRow
End Property
Public Property Get FirstColumnPosition As Long
FirstColumnPosition = Me.m_minCol
End Property
Public Property Get LastColumnPosition As Long
LastColumnPosition = Me.m_maxCol
End Property
End Class
Have a look at the functions and you will see how to use this.