Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » Developing U++ » UppHub » Ole Automation [FEATURE REQUEST?]
Re: Ole Automation [FEATURE REQUEST?] [message #21169 is a reply to message #21168] Wed, 06 May 2009 12:25 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

Hello Koldo!
No problems! I created this temporarily, because I needed this today. You can change as you want, no problems, I only went to suggest this realization!

Will be great set cell/range format:

in MSExcel can be:

Cells(row, col).NumberFormat = "@"


The problems is when i try to write a number as text!

[Updated on: Wed, 06 May 2009 12:27]

Report message to a moderator

Re: Ole Automation [FEATURE REQUEST?] [message #21173 is a reply to message #21169] Wed, 06 May 2009 14:27 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
tojocky wrote on Wed, 06 May 2009 12:25

Hello Koldo!
No problems! I created this temporarily, because I needed this today. You can change as you want, no problems, I only went to suggest this realization!

Will be great set cell/range format:

in MSExcel can be:

Cells(row, col).NumberFormat = "@"


The problems is when i try to write a number as text!



Hello tojocky

Now there is not included a way to set the format, but when you write text, number or dates they has to appear right.

In the demo you see:
	sheet.SetValue(2, 2, "=A7*B5");
	sheet.SetValue(3, 21, "Hello");
	sheet.SetValue("BD25", 23242.343);
	sheet.SetValue("B26", GetSysTime());


The output of this should be as expected.

Could you explain me what you need with more detail?.

Best regards
Koldo


Best regards
Iñaki
Re: Ole Automation [FEATURE REQUEST?] [message #21174 is a reply to message #21173] Wed, 06 May 2009 15:58 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

Hello Koldo,

Try this for Ms Excel in demo example:
sheet.SetValue(1, 2, "123456789123456789");


I have lost data:
for this example in excel I have "123456789123456000" instead "123456789123456789"

I was modified a little the code:
bool MSSheet::SetValue(int col, int row, Value value)
{
	if (!Sheet)
		return false;

	VariantOle x, y, val;
	x.Int4(col);
	y.Int4(row);
	val.Value(value);
	
	// >>> Ion Lupascu 06.05.2009 Set Cell Format for string cell
	if (!(Range = Ole::GetObject(Sheet, "Cells", x, y)))
		return false;	

	if(IsString(value)){
		VariantOle txt_format_ole;
		txt_format_ole.BString("@");
		
		Ole::SetValue(Range, "NumberFormat", txt_format_ole);
	}
	
	return Ole::SetValue(Range, "Value", val);
	// <<< Ion Lupascu 06.05.2009
}


I thing that exist memory leak in the program!
Every time when I run the program that it only preparing excel file and text file (specific converting from text to excel file) I have memory leak. Memory usage is increasing every time!
Is it a normal situation?

[Updated on: Wed, 06 May 2009 16:12]

Report message to a moderator

Re: Ole Automation [FEATURE REQUEST?] [message #21179 is a reply to message #21174] Thu, 07 May 2009 00:24 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello tojocky

I have updated in Bazaar Automation and Sysinfo.

The functions added are:

class OfficeSheet
{
public:
...
	bool InsertTab(String name);
	bool ChooseTab(String name);
	bool ChooseTab(int index);
	bool RemoveTab(String name);
	bool RemoveTab(int index);
	int GetNumTabs();
...


They support both OpenOffice Calc and Excel and have the same behaviour.


I have included in demo

sheet.SetValue(1, 2, "'123456789123456789");


This is a special case because this number is over the Calc and Excel precision, so if you want to get all the digits you have to enter it as text. For me the easiest way is just adding a ' in the beginning.

I have not found any memory leak. Try to use the new code from svn and tell me the details if you get something wrong.

Thank you for your efforts
Koldo



Best regards
Iñaki
Re: Ole Automation [FEATURE REQUEST?] [message #21181 is a reply to message #21179] Thu, 07 May 2009 09:18 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

Hello Koldo!

I tested from svn and do not found your changes about adding in package organizer "gdi32" for package SysInfo and in demo package the code:
sheet.SetValue(1, 2, "'123456789123456789");


Maybe I do not check correct svn repository?

I use:
http://upp-mirror.googlecode.com/svn/trunk/


Edit: I want to add that to add a symbol as ' is not a solution!

May be will be great to add method SetFormatSelection that as parameter will be an enum with restricted values as (String)?
It will be nice because the format realization for MS Excel can differ by Open Office Calc!

[Updated on: Thu, 07 May 2009 09:27]

Report message to a moderator

Re: Ole Automation [FEATURE REQUEST?] [message #21185 is a reply to message #17864] Thu, 07 May 2009 22:55 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello tojocky

I have had difficulties with svn. Finally it is updated in bazaar.

- About the ' symbol

I really use it in the every day spreadsheet use. For me it is faster to do it than choosing the cell, open the format menu looking for the text format and after that, enter the string.

It is simple and it works. Neither Calc/Excel nor OfficeSheet knows better than the user the real format of strings that are not clear, like:

- 123456789123456789 or
- 00123

I do not know to include your code... that is really Ok
Quote:

if(IsString(value)){
VariantOle txt_format_ole;
txt_format_ole.BString("@");

Ole::SetValue(Range, "NumberFormat", txt_format_ole);
}

as for "123456789123456789" and "123", they will be considered as text and it will not be possible to do any mathematical operation with them. This can be a source of problems.


- About SetFormatSelection

Well, something has to be done. I considered that to have less priority as:
-- I have to prepare the same interface for Calc and Excel
-- I found more interesting and easier to do cells formating with colors, more font handling and borders


They are opinions. Please tell me what are the most important format options to be included. If you plan to do something please tell me in advance so that we will not repeat the job.

Best regards
Koldo




Best regards
Iñaki
Re: Ole Automation [FEATURE REQUEST?] [message #21186 is a reply to message #21185] Fri, 08 May 2009 08:16 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

Hello Koldo!

My Opinion about adding the method "SetSelectionFormat" is important, for me. This method can resolve my problems.

I suggest to add this method:
bool MSSheet::SetValue(Value value)
{
	if (!Sheet)
		return false;

	if (!Range)
		return false;

	VariantOle val;
	val.Value(value);
	
	return Ole::SetValue(Range, "Value", val);
}


I realized only for MSExcel variant!

Other methods to do is important too!

Thank you for realization!
Re: Ole Automation [FEATURE REQUEST?] [message #21318 is a reply to message #21186] Tue, 12 May 2009 23:12 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

Hello Koldo!

Seems I found a little interesting project that read and write excel in direct stream file for Excel 97-2003.

http://www.codeproject.com/KB/office/BasicExcel.aspx

And other example I found in c# for excel 2007 write and read.

http://www.codeproject.com/KB/office/OpenXML.aspx

In Excel 2007 the format is more readable.
Re: Ole Automation [FEATURE REQUEST?] [message #21330 is a reply to message #21318] Wed, 13 May 2009 14:09 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Thank you tojocky

I agree they are good samples of accesing directly Microsoft formats.

Other good effort is Jakarta POI. The problem of it is that it is addressed to Java.

When thinking in Automation package I was tempted to access directly the files. The advantage was clear: you do not need the Office software to do it and you do not depend on the OS (how to access Microsoft files from GNU/Linux?).

The problem I had was that .xls, .doc, .ods, .otd are from complex to very complex formats and I did not found libraries that covered their main features.

Finally I chose to go through Ole automation and UNO api. For me they have not been easy to use. Bad documented from a C point of view after many efforts I had to refuse to use UNO to handle OpenOffice and so to handle it from Linux as there is no Ole clone.

Well, that is my experience. More ideas are acknowledged.

Best regards
Koldo



Best regards
Iñaki
Re: Ole Automation [FEATURE REQUEST?] [message #21331 is a reply to message #21330] Wed, 13 May 2009 14:38 Go to previous messageGo to next message
Mindtraveller is currently offline  Mindtraveller
Messages: 917
Registered: August 2007
Location: Russia, Moscow rgn.
Experienced Contributor

People, could you please rename Automation package into, i.e. Docflow, Microsoft, or Office. "Automation" stands for many things, especially industrial automation. So "automation" IMO is much less informative and more confusing than "Docflow".
Re: Ole Automation [FEATURE REQUEST?] [message #21332 is a reply to message #21330] Wed, 13 May 2009 14:59 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

koldo wrote on Wed, 13 May 2009 15:09

Thank you tojocky

I agree they are good samples of accesing directly Microsoft formats.

Other good effort is Jakarta POI. The problem of it is that it is addressed to Java.

When thinking in Automation package I was tempted to access directly the files. The advantage was clear: you do not need the Office software to do it and you do not depend on the OS (how to access Microsoft files from GNU/Linux?).

The problem I had was that .xls, .doc, .ods, .otd are from complex to very complex formats and I did not found libraries that covered their main features.

Finally I chose to go through Ole automation and UNO api. For me they have not been easy to use. Bad documented from a C point of view after many efforts I had to refuse to use UNO to handle OpenOffice and so to handle it from Linux as there is no Ole clone.

Well, that is my experience. More ideas are acknowledged.

Best regards
Koldo




I founded and MSWord too!

But the MSOffice 2007 is based on XML file compressed with gzip.
May we try to concentrate our power to manipulate direct with file (.xls, xlsx, .doc, .docx)?
Other formats I do not use!
Re: Ole Automation [FEATURE REQUEST?] [message #21337 is a reply to message #21332] Wed, 13 May 2009 23:09 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello tojocky

.doc and .xls are not documented binary formats only recognized by hacking. The only way for an average human (like me) to access them is by using a library.

Open Document and Open XML are well documented open formats. The problem is the size of these standards:

- Open Document. Standard ISO/IEC 26300. 722 pag
- Open XML. Standard ISO/IEC 29500. 7228 pages

The huge size of 722 pages seems small comparing to the 7228 pages of the microsoft standard.

I can not answer properly as I do not know them, but it seems a hard work to try to use only a small percentage of them. I repeat, I am an average person. Smile

Best regards
Koldo


Best regards
Iñaki
Re: Ole Automation [FEATURE REQUEST?] [message #21345 is a reply to message #17864] Thu, 14 May 2009 08:14 Go to previous messageGo to next message
mr_ped is currently offline  mr_ped
Messages: 816
Registered: November 2005
Location: Czech Republic - Praha
Experienced Contributor

Many parts of Open XML definition are optional, i.e. you don't need to support them. edit: some of those optional are defined in a funny way like "following behavior of some legacy SW" without further details, so even if you try to support it, you will fail.

Then again it's just question of time when Microsoft Office will produce files (even simple ones) with so many optional tags as possible to prevent anybody else working with that format, so I would not bother too much with that ISO standard.

The ODF is other kind of beast, you can 100% support the ISO standard, yet for example it doesn't say nothing particular about formulas in spreadsheets, so you are still not supporting real world ODF files very good. If you want to support real world files, you must also follow current Open Office way of doing things. This situation has at least the advantage that the OOo legacy is de facto standard which will work, the only ODF vendor breaking this is Microsoft (are you surprised? Smile ) with latest MS Office service pack they do support the ISO part of ODF and everything else what will become part of version2 of ODF definition they do in different way, so their support of ODF "is there", but practically unusable.

[Updated on: Thu, 14 May 2009 08:16]

Report message to a moderator

Re: Ole Automation [FEATURE REQUEST?] [message #21362 is a reply to message #21345] Fri, 15 May 2009 14:15 Go to previous messageGo to next message
Mindtraveller is currently offline  Mindtraveller
Messages: 917
Registered: August 2007
Location: Russia, Moscow rgn.
Experienced Contributor

Well, I guess nobody liked the idea of renaming Automation to OleAutomation.
Re: Ole Automation [FEATURE REQUEST?] [message #21363 is a reply to message #21362] Fri, 15 May 2009 14:26 Go to previous messageGo to next message
mr_ped is currently offline  mr_ped
Messages: 816
Registered: November 2005
Location: Czech Republic - Praha
Experienced Contributor

Mindtraveller wrote on Fri, 15 May 2009 14:15

Well, I guess nobody liked the idea of renaming Automation to OleAutomation.


I certainly do, but I'm merely passing by, it's up to others.
Re: Ole Automation [FEATURE REQUEST?] [message #21364 is a reply to message #21363] Fri, 15 May 2009 16:18 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
mr_ped wrote on Fri, 15 May 2009 14:26

Mindtraveller wrote on Fri, 15 May 2009 14:15

Well, I guess nobody liked the idea of renaming Automation to OleAutomation.


I certainly do, but I'm merely passing by, it's up to others.


Well, I put Automation name because:

- I wanted to use it also in Linux for OpenOffice through its UNO interface
- Really "Ole Automation" is the old name as Microsoft renamed the technology to simply "Automation"

I have the hope that in the next future it would be possible to interface OpenOffice in Linux so I would not add "Ole" to the package name.

Best regards
Koldo


Best regards
Iñaki
Re: Ole Automation [FEATURE REQUEST?] [message #21365 is a reply to message #21364] Fri, 15 May 2009 18:55 Go to previous messageGo to next message
Mindtraveller is currently offline  Mindtraveller
Messages: 917
Registered: August 2007
Location: Russia, Moscow rgn.
Experienced Contributor

I`m afraid the word "Automation" has many meanings beyond plain docflow. Therefore it will be confusing for people who is not closely connected to office applications only (my first reaction was "Wow, industrial automation package for U++! Let`s look what microcontrollers are supported..."). My proposal is avoid confusion from the start and select more appropriate naming for what is really your package is doing, such as: Office (IMO the best choice), Docflow, OleAutomation, etc.
Re: Ole Automation [FEATURE REQUEST?] [message #21395 is a reply to message #17864] Mon, 18 May 2009 08:29 Go to previous messageGo to next message
mr_ped is currently offline  mr_ped
Messages: 816
Registered: November 2005
Location: Czech Republic - Praha
Experienced Contributor

OfficeAutomation ?
Re: Ole Automation [FEATURE REQUEST?] [message #21399 is a reply to message #21395] Mon, 18 May 2009 10:23 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3268
Registered: August 2008
Senior Veteran
Hello mr_ped

I like it. If everybody agrees this week I would change the name next Monday.

Best regards
Koldo


Best regards
Iñaki
Re: Ole Automation [FEATURE REQUEST?] [message #21404 is a reply to message #21399] Mon, 18 May 2009 13:56 Go to previous messageGo to previous message
Mindtraveller is currently offline  Mindtraveller
Messages: 917
Registered: August 2007
Location: Russia, Moscow rgn.
Experienced Contributor

I agree. Thank you everyone.
Previous Topic: MAPIEx works with MinGW
Next Topic: [Controls4U] IE shows script errors
Goto Forum:
  


Current Time: Tue Oct 26 15:29:38 CEST 2021

Total time taken to generate the page: 0.04173 seconds