1. Michael Kochevar
  2. PowerBuilder
  3. Tuesday, 13 November 2018 19:41 PM

Hello,

 

I was wondering if there is another way to connect to and populate an Excel spreadsheet other than the way below...

 

OLEObject lole_xlapp

// Create the oleobject variable xlapp
lole_xlApp = Create OLEObject

// Connect to Excel and check the return code
li_return = lole_xlApp.ConnectToNewObject( "Excel.Application" ) // "Excel.Sheet"


if li_return < 0 then
theapp.invo_processor.uf_close_processor()
MessageBox("Connect to Excel Failed !",string(li_return))
return
end if

// Open a particular Excel file
lole_xlapp.Workbooks.Open(agrid.is_export_file)

** A lot of magical code here **

// Save opened file
lole_xlapp.Activeworkbook.Save()

lole_xlapp.Quit()
lole_xlapp.DisConnectObject()
Destroy lole_xlapp

 

Our clients are complaining that this process takes to long but I do not know of another way to use PB and Excel.

On a side note we will be upgrading to PB2017 hopefully this week.

Any help would be appreciated.

 

Thank You

 

René Ullrich Accepted Answer Pending Moderation
  1. Wednesday, 23 January 2019 06:59 AM
  2. PowerBuilder
  3. # 1
0
Votes
Undo

Hi Michael,

you don't wrote what code is to slow. Is it your "magical code"?

You can fast write a lot of data to excel if you set blocks of data in a single step:

- set data for one ow

- set data for one column

- set data for blocks of rows and columns

Or you write a tab delimited string to the clipboard and import it in excel. But this overwrites the excel formatting!

 

The fasted way for me was to set the data for a fixed number of rows of one column in a step:

e.g.

any la_data[100, 1]


// fill la_data here

lole_sheet.Range ("A1:A100").FormulaR1C1 = la_data

 

It's a little bit triggy because you need an array with two dimensions so you can only declare it in a fixed size.

I solved this with 10 arrays with size of 1, 2, 4, ... 512. (Using bigger arrays was slower for me.)

Comment
There are no comments made yet.
Michael Kochevar Accepted Answer Pending Moderation
  1. Tuesday, 22 January 2019 19:10 PM
  2. PowerBuilder
  3. # 2
0
Votes
Undo

Thank you for your time Aron. Sorry it has taken me so long to respond but something else came up and this project has been moved to the back burner.

Thanks Again,

Mike

Comment
There are no comments made yet.
Aron Cox Accepted Answer Pending Moderation
  1. Thursday, 15 November 2018 17:49 PM
  2. PowerBuilder
  3. # 3
1
Votes
Undo

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();

Comment
There are no comments made yet.
Marco Meoni Accepted Answer Pending Moderation
  1. Wednesday, 14 November 2018 10:08 AM
  2. PowerBuilder
  3. # 4
0
Votes
Undo

Hi Michael,

Oleobject as you described is the usual way to go.

Another approach consists of treating Excel as a database via MS Excel ODBC driver.

Best,

.m

Comment
Thank you for your time. I will do a POC to see if the MS Excel ODBC driver is the way to go for us.
  1. Michael Kochevar
  2. Wednesday, 14 November 2018 14:10 PM
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.