The best way I have found to speed things up, depending on all the Excel wizardry you require, is to directly create the Excel file rather than create it via OLE and opening Excel. This has the advantage of being much faster, and not requiring Excel to be installed on the user's PC.
I did this by creating a .NET dll, that compile as a COM / ActiveX control so PowerBuilder can use it. There are examples on the internet of how to do this that can be found, and once you learn how to do it you can do all kinds of useful things with the dll.
I then used a library for .NET (something similar probably exists for other languages) called EPlus (https://github.com/JanKallman/EPPlus) which lets you directly create an xlsx file. It's pretty simple once you get the hang of it, and takes very few lines to create a file with data in it
The .NET code below is the bulk of the code I use, it takes arrays of data read from a CSV file (could be passed in instead, or just do a SaveAs and pass the filename to the .NET object) and inserts them into tan xlsx worksheet, formatting them as required using some an array that specifies the datatype / format for each column. As you can see it's not a lot of code, most of it is handling the formatting, and for me at least it was much, much faster (can't promise it will be for everyone, but I don't see why not).
// NOTE: Using EPPlus, so doesn't require Excel to be installed / started
using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(outputFile)))
{
// Create a new worksheet
ExcelWorksheet ws = excelPackage.Workbook.Worksheets.Add(worksheetName);
// Loop round all the data and insert it in the correct format as
// specified in the CSV Definition XML
for (int row = 1; row <= inputData.Count; row++)
{
string[] rowData = inputData[row - 1];
for (int col = 1; col <= rowData.Length; col++)
{
int index = col - 1;
// If there was a header row in the CSV file then it's the first row and
// each column will be general text data for the column names
if (csvDefinition.HeaderIncluded && row == 1)
{
ws.Cells[row, col].Value = rowData[index];
ws.Cells[row, col].Style.Numberformat.Format = "@";
// T01-I Start
ws.Cells[row, col].Style.Font.Bold = true;
ws.Cells[row, col].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[row, col].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Lavender);
// T01-I End
}
else
{
switch (csvDefinition.ColumnDefinitionList[index].DataType)
{
case "Date":
{
// Dates have not always been set and so could be blank
if (String.IsNullOrEmpty(rowData[index].Trim()))
{
break;
}
// Determine the required date format, use a default one if none found
var dateFormat = csvDefinition.ColumnDefinitionList[index].Format;
if (dateFormat == null)
{
dateFormat = "dd-mm-yyyy";
}
// Convert the string into a date using the date format
try
{
DateTime testDateTime = DateTime.ParseExact(rowData[index],
csvDefinition.ColumnDefinitionList[index].Format, null);
ws.Cells[row, col].Value = testDateTime.ToOADate();
ws.Cells[row, col].Style.Numberformat.Format = "dd-MMM-yyyy";
}
catch (Exception e)
{
_logger.LogWrite(LoggerHandler.Level.Error, "Failed to convert date string '" + rowData[index] + "' to a date using the specied format '" + dateFormat + "'", e);
ShowMessage("Failed to convert date string '" + rowData[index] + "' to a date using the specied format '" + dateFormat + "'");
return false;
}
break;
}
case "Currency":
{
// Covert.ToDate seems to handle most string formats very well
ws.Cells[row, col].Value = Convert.ToDecimal(rowData[index]);
ws.Cells[row, col].Style.Numberformat.Format = "#,##0.00";
break;
}
case "Number":
{
// Covert.ToDate seems to handle most string formats very well
ws.Cells[row, col].Value = Convert.ToDecimal(rowData[index]);
ws.Cells[row, col].Style.Numberformat.Format = "@";
break;
}
case "Logical":
{
// Covert.ToDate seems to handle most string formats very well
if (rowData[index].Trim() == "1")
{
ws.Cells[row, col].Value = "Y";
}
else
{
ws.Cells[row, col].Value = "N";
}
break;
}
default:
{
ws.Cells[row, col].Value = rowData[index];
ws.Cells[row, col].Style.Numberformat.Format = "@";
// T01-I Start
// If the text is particularly long then wrap it
if (rowData[index].Length > 60)
{
ws.Cells[row, col].Style.WrapText = true;
}
// T01-I End
break;
}
}
}
}
}
// Autofit all columns - This may need to be changed to only do some
for (int col = 1; col <= csvDefinition.ColumnDefinitionList.Count; col++)
{
ws.Column(col).AutoFit();
}
excelPackage.Save();