Forum Discussion
scot1967
Champion Level 1
3 months agoHere are a few functions I have used. I use aqFile.Exists to check the file path. If the sheet you are referring to does not exist this may cause the same or a similar issue. See if any of this helps. It is JavaScript. I am not sure what you are using but you can pick out the methods here and look them up to find equivalent calls. Good Luck!!
if (!aqFile.Exists(_fileName))
{
// Create a new Excel file and add a new empty sheet to it
Log.Message(aqString.Format("The %s file does not exist and will be created.", _fileName));
excelFile = Excel.Create(_fileName);
excelSheet = excelFile.AddSheet(_sheetName);
excelFile.Save();
}
if (aqFile.Exists(_fileName))
{
// Open the existing Excel file
excelFile = Excel.Open(_fileName);
if(SheetWithTitleExists(excelFile,_sheetName) == false)
{
excelSheet = excelFile.AddSheet(_sheetName);
}
else
{
excelSheet = excelFile.SheetByTitle(_sheetName);
}
Object.entries(_valuesObj).forEach(([key, value]) =>
{
if(excelSheet.RowCount == 0)
{
// Write the header row
Object.entries(value).forEach(([key, value]) =>
{
//Write Line Item Values to rows.
let cellData = "";
Object.entries(value).forEach(([key, value]) =>
{
cellHeader = key;
cellData = value;
});
excelSheet.Cell(key, 1).Value = cellHeader;
excelSheet.Cell(key, 2).Value = cellData;
});
}
else
{
rowIndex = excelSheet.RowCount + 1;
Object.entries(value).forEach(([key, value]) =>
{
//Write Line Item Values to rows.
let cellData = "";
Object.entries(value).forEach(([key, value]) =>
{
cellData = value;
});
excelSheet.Cell(key, rowIndex).Value = cellData;
});
}
});
}
// Save the file to apply the changes
excelFile.Save();
//Excel.Close(fileName);
}
function SheetWithTitleExists(_fileName,_sheetName)
{
if (! equal(_fileName, null))
{
if (_fileName.SheetCount > 0)
{
for (let i = 0; i < _fileName.SheetCount; i++)
{
if (equal(_fileName.SheetByIndex(i).Title, _sheetName))
{
return true;
}
}
return false;
}
else
{
return false;
}
}
}