Author Topic: How to create a new Excel file for export using JavaScript?  (Read 203 times)

DeBAAT

  • EA User
  • **
  • Posts: 50
  • Karma: +2/-0
    • View Profile
For a project, I'm trying to export some information to an Excel file using a JavaScript.
Most of the code now seems to work.
Except for creating a new Excel file on the location I want it.
Export works fine when I open an existing file.
Saving a newly created Excel file also works, it only refuses to write it to the strExcelFileName I specified using GetFileNameDialog().
When I close the newly generated Excel workbook, it saves it as "Map1.xlsx" in the default folder used by the Excel application.

The code below shows what I have used for testing (after cleaning up a bit).
Hope to get some pointers on how to fix this.
Thanks!

Code: [Select]
let objExcelWorkBook = null;
let objExcelApplication = null;

// Start the Excel Application as COMObject
objExcelApplication = new COMObject( "Excel.Application", true );

// Get the strExcelFileName to get the information from
projectInterface = Repository.GetProjectInterface();
strExcelFileName = projectInterface.GetFileNameDialog( strExcelFileName, FilterString, Filterindex, Flags, InitialDirectory, OpenorSave );

// Get objExcelWorkBook from the strExcelFileName provided
objExcelWorkBook = objExcelApplication.Workbooks.Open( strExcelFileName );
if ( objExcelWorkBook != null ) {
// objExcelWorkBook FOUND
Session.Output("EXCELWOpenWorkbook found Workbook with strExcelFileName = " + strExcelFileName + " !" );
} else {

// If the objExcelWorkBook file does not exist, then create one
objExcelWorkBook = objExcelApplication.Workbooks.Add();
if ( objExcelWorkBook != null ) {
// If the file added ok, then save it with new name
objExcelWorkBook.SaveAs( strExcelFileName );
} else {
LOGError( "EXCELWOpenWorkbook could NOT open nor add strExcelFileName " + strExcelFileName + "!" );
}
}

// Close and save the previously opened or created objExcelWorkBook
objExcelApplication.Workbooks.Close();

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 12978
  • Karma: +541/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to create a new Excel file for export using JavaScript?
« Reply #1 on: June 20, 2024, 11:15:11 pm »
I can't immediately see what you are doing wrong.

Here's the code I use for dealing with excel files. It's vbscript, but the concept remains the same.

Code: [Select]
'[path=\Framework\Utils]
'[group=Utils]

'Name: ExcelFile
'Author: Geert Bellekens
'Purpose: Wrapper script class for Excel files
'Date: 2017-03-20

!INC Utils.Include

const xlCalculationAutomatic = -4105 'Excel controls recalculation.
const xlCalculationManual = -4135 'Calculation is done when the user requests it.
const xlCenter = -4108
const xlLeft = -4131
const xlBelow = 1
const xlAbove = 0
'vertical alignment values
const xlVAlignBottom = -4107 'Bottom
const xlVAlignCenter = -4108 'Center
const xlVAlignDistributed = -4117 'Distributed
const xlVAlignJustify = -4130 'Justify
const xlVAlignTop = -4160 'Top
'XlFormatConditionType
const xlAboveAverageCondition = 12 'Above average condition
const xlBlanksCondition = 10 'Blanks condition
const xlCellValue = 1 'Cell value
const xlColorScale = 3 'Color scale
const xlDataBar = 4 'DataBar
const xlErrorsCondition = 16 'Errors condition
const xlExpression = 2 'Expression
const xlIconSet = 6 'Icon set
const xlNoBlanksCondition = 13 'No blanks condition
const xlNoErrorsCondition = 17 'No errors condition
const xlTextString = 9 'Text string
const xlTimePeriod = 11 'Time period
const xlTop10 = 5 'Top 10 values
const xlUniqueValues = 8 'Unique values
'XlFormatConditionOperator
const xlBetween = 1 'Between. Can be used only if two formulas are provided.
const xlEqual = 3 'Equal.
const xlGreater = 5 'Greater than.
const xlGreaterEqual = 7 'Greater than or equal to.
const xlLess = 6 'Less than.
const xlLessEqual = 8 'Less than or equal to.
const xlNotBetween = 2 'Not between. Can be used only if two formulas are provided.
const xlNotEqual = 4 'Not equal.
'XlWindowState
const xlMaximized = -4137 'Maximized
const xlMinimized = -4140 'Minimized
const xlNormal = -4143 'Normal

Class ExcelFile
'private variables
Private m_ExcelApp
Private m_FileName
Private m_WorkBook
private m_isExisting

Private Sub Class_Initialize
m_FileName = ""
set m_ExcelApp = CreateObject("Excel.Application")
set m_WorkBook = nothing
m_isExisting = false
End Sub


' FileName property.
Public Property Get FileName
  FileName = m_FileName
End Property
Public Property Let FileName(value)
  m_FileName = value
End Property
public Property Get worksheets
set worksheets = m_WorkBook.Sheets
end property

public function freezePanes(ws, row, column)
'select the worksheet
ws.Activate
m_ExcelApp.ActiveWindow.WindowState = xlMaximized
m_ExcelApp.ActiveWindow.SplitRow = row
m_ExcelApp.ActiveWindow.SplitColumn = column
m_ExcelApp.ActiveWindow.FreezePanes = true
end function

public function quit
m_ExcelApp.Quit
end function

private function createNewTab(tabName,beforeSheetIndex)
'check if the workbook has been created already
if m_WorkBook is nothing then
set m_WorkBook = m_ExcelApp.Workbooks.Add()
end if
Dim ws
set ws = nothing
dim currentWs
'check if it exists
for each currentWs in m_WorkBook.Sheets
if currentWs.Name = tabName then
set ws = currentWs
exit for
end if
next
'if not exist yet then create
if ws is nothing then
'check the beforeIndex. In -1 then add in the back
if beforeSheetIndex > 0 and beforeSheetIndex <= m_Workbook.Sheets.Count then
Set ws = m_WorkBook.Sheets.Add(m_Workbook.Sheets(beforeSheetIndex)) 'add before the given sheetIndex
else
Set ws = m_WorkBook.Sheets.Add(,m_Workbook.Sheets(m_Workbook.Sheets.Count)) 'add after the last one
end if
ws.Name = tabName
end if
'return
set createNewTab = ws
end function

'public operations
'create a tab with the given name. The contents should parameter should be a two dimensional array
'anything int he contents that starts with "=" will be interpreted as a formula
public Function createTabWithFormulas(tabName, contents,formatAsTable, tableStyle, beforeSheetIndex)
'turn off automatic calculation
m_ExcelApp.Calculation = xlCalculationManual
'create the tab
Dim ws
set ws = createNewTab(tabName,beforeSheetIndex)
'fill the contents
'loop content
dim i
dim j
for i = 0 to Ubound(contents,1)
for j = 0 to Ubound(Contents,2)
dim cellValue
cellValue = contents(i,j)
if left(cellValue,1) = "=" then
ws.Cells(i + 1,j + 1).Formula = cellValue
else
ws.Cells(i + 1,j + 1).Value = cellValue
end if
next
next
dim targetRange
set targetRange = ws.Range(ws.Cells(1,1), ws.Cells(Ubound(contents,1) +1, Ubound(Contents,2) +1))
'format as table if needed
if formatAsTable then
formatSheetAsTable ws, targetRange, tableStyle
end if
'set autofit
targetRange.Columns.Autofit
targetRange.Rows.Autofit
'turn on automatic calculation
m_ExcelApp.Calculation = xlCalculationAutomatic
end function
'public operations
'create a tab with the given name. The contents should parameter should be a two dimensional array
public Function createTab(tabName, contents,formatAsTable, tableStyle)
'return
set createTab = createTabAtIndex(tabName, contents,formatAsTable, tableStyle, -1)
end function

public function createTabAtIndex(tabName, contents,formatAsTable, tableStyle, beforeSheetIndex)
'create the tab
Dim ws
set ws = createNewTab(tabName,beforeSheetIndex)
'fill the contents
dim targetRange
set targetRange = ws.Range(ws.Cells(1,1), ws.Cells(Ubound(contents,1) +1, Ubound(Contents,2) +1))
targetRange.Value2 = contents
'format as table if needed
if formatAsTable then
formatSheetAsTable ws, targetRange, tableStyle
end if
'set autofit
targetRange.Columns.Autofit
targetRange.Rows.Autofit
'return
set createTabAtIndex = ws
end function

'delete the sheet with the given tabName
public function deleteTab(tabName)
dim i
For i = me.worksheets.Count To 1 Step -1
If me.worksheets.Item(i).Name = tabName Then
me.deleteTab i
End If
next
end function

'delete the sheet at the given index
public function deleteTabAt(index)
if index >= 1 and index <= me.worksheets.Count then
m_ExcelApp.DisplayAlerts = False
me.worksheets.Item(index).Delete
m_ExcelApp.DisplayAlerts = True
end if
end function

public function formatSheetAsTable(worksheet, targetRange, tableStyle)
dim table
Set table = worksheet.ListObjects.Add(1, targetRange, 1, 1)
table.TableStyle = tableStyle
end function

public Function getUserSelectedFileName()
dim selectedFileName
dim project
set project = Repository.GetProjectInterface()
me.FileName = project.GetFileNameDialog ("", "Excel Files|*.xls;*.xlsx;*.xlsm", 1, 2 ,"", 1) 'save as with overwrite prompt: OFN_OVERWRITEPROMPT
end function

public Function openUserSelectedFile(readOnly)
dim selectedFileName
dim project
set project = Repository.GetProjectInterface()
me.FileName = project.GetFileNameDialog ("", "Excel Files|*.xls;*.xlsx;*.xlsm|Excel Templates|*.xlt;*.xltx;*.xltm", 1, 0 ,"", 0) 'save as with overwrite prompt: OFN_OVERWRITEPROMPT
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
'return default false
openUserSelectedFile = false
if fso.FileExists(me.FileName) then
openUserSelectedFile = true 'file selected
'check the extension
dim extension
extension = lcase(fso.GetExtensionName(me.FileName))
select case extension
case "xlt","xltx","xltm"
me.NewFile me.FileName
case else
if readOnly then
me.OpenReadOnly me.FileName
else
me.Open me.FileName
end if
m_isExisting = true
end select
end if
end function

public function Open(filePath)
me.FileName = filePath
set m_WorkBook = m_ExcelApp.Workbooks.Open(me.FileName)
end function

public function OpenReadOnly(filePath)
me.FileName = filePath
set m_WorkBook = m_ExcelApp.Workbooks.Open(me.FileName, true, true, , , , , , , , True)
end function

public function NewFile(filePath)
dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.FileExists(filePath) then
set m_WorkBook = m_ExcelApp.Workbooks.Add(filePath)
end if
'reset filename
me.FileName = ""
end function

public function formatRange (range, backColor, fontColor, fontName, fontSize, bold, horizontalAlignment)
if backColor <> "default" then
range.Interior.Color = backColor
end if
if fontColor <> "default" then
range.Font.Color = fontColor
end if
if fontName <> "default" then
range.Font.Name = fontName
end if
if fontSize <> "default" then
range.Font.Size = fontSize
end if
if horizontalAlignment <> "default" then
range.HorizontalAlignment = horizontalAlignment
end if
end function

public function hideColumn(sheet, columnNumber)
sheet.Columns(columnNumber).Hidden = true
end function

public function setVerticalAlignment(range, verticalAlignment)
range.VerticalAlignment = verticalAlignment
end function

public function getContents(sheet)
getContents = sheet.UsedRange.Value2
end function

public function addConditionalFormatting(range, formattingType, operator , formula1, formula2, backColor, fontColor)
dim formatting
set formatting = range.FormatConditions.Add(formattingType, operator , formula1, formula2)
formatting.Interior.Color = backColor
formatting.Font.Color = fontColor
end function

public Function save()
'make sure we have a filename
if len(me.FileName) = 0 then
getUserSelectedFileName
end if
'if the file name is still empty then exit
if len(me.FileName) = 0 then
exit function
end if
if m_isExisting then
m_WorkBook.Save
else
'Delete the existing file if it exists
dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.FileExists(me.FileName) then
fso.DeleteFile me.FileName
end if
'save the workbook at the given filename
m_WorkBook.Saveas me.FileName
end if
'make excel visible
m_ExcelApp.visible = True
m_ExcelApp.WindowState = -4137 'xlMaximized
end function


end Class

Geert

DeBAAT

  • EA User
  • **
  • Posts: 50
  • Karma: +2/-0
    • View Profile
Re: How to create a new Excel file for export using JavaScript?
« Reply #2 on: June 21, 2024, 03:25:21 pm »
Hai Geert,
Thanks for looking and sharing your code.
It may not be directly translatable but it did give me a hint to a workaround.
As I see, when the excel file does not exist, you are using the filesystem object to create a new file and open that as excel file.
Think this might be worth trying.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 12978
  • Karma: +541/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to create a new Excel file for export using JavaScript?
« Reply #3 on: June 21, 2024, 04:14:27 pm »
Hai Geert,
Thanks for looking and sharing your code.
It may not be directly translatable but it did give me a hint to a workaround.
As I see, when the excel file does not exist, you are using the filesystem object to create a new file and open that as excel file.
Think this might be worth trying.
Not really, I'm doing the opposite. If the file I'm trying to SaveAs already exists, I use the filesystem to delete the file first.
There might be another solution using Excel, but this works for me.

Geert

DeBAAT

  • EA User
  • **
  • Posts: 50
  • Karma: +2/-0
    • View Profile
Re: How to create a new Excel file for export using JavaScript?
« Reply #4 on: June 21, 2024, 06:21:39 pm »
Sorry Geert to have misinterpreted your code.
The hint I got from it turns out to work now.
I dreate a new worksheet (using .Add), Save it using the default filename and path and use the file system to move it to the filename desired.

Summary of the code:

Code: [Select]
objExcelWorkBook = objExcelApplication.Workbooks.Add();
objExcelWorkBook.Save( true );
strExcelWorkBookFullName = objExcelWorkBook.FullName;
objExcelWorkBook.Close();
objExcelFile = fileSystemObject.GetFile( strExcelWorkBookFullName );
objExcelFile.Move( strExcelFileName );

Please beware of the order of the statements.
And I use Save(true) to prevent the system to ask for confirmation to save.