Creating Excel files with Cache...

[JJ]

Hello all:

Have another question here. Has anyone out there done this? Created
Excel files with Cache? I don't mean just exporting a delimited text
file that a user would then import to Excel...I mean actually created
an .xls type file from Cache, with some formatting, etc. already in
place?

I've done some PHP coding and there are classes you can get that will
generate Excel or PDF files for example. Does Cache have such a
thing? I haven't been able to find much in the documentation.

Thx in advance, JJ

[Roberto]

Hello JJ,

What we've done is create a text file using Excel XML format. There is
probably an easier way to create an XML file in Cache, but we have not
researched that.

But our technique works for us.

Google "Excel XML format" and you will get some good links.

-Roberto

[Pmkadow]

JJ,

 

Why would you want to do that?

 

-Mike

--
InterSystems: Advanced software technologies for breakthrough applications

Caché 2010.2.2 released on December 31, 2010

[JJ]

On Jan 4, 9:32 am, Roberto <rcaha...@gmail.com> wrote:
> Hello JJ,
>
> What we've done is create a text file using Excel XML format. There is
> probably an easier way to create an XML file in Cache, but we have not
> researched that.
>
> But our technique works for us.
>
> Google "Excel XML format" and you will get some good links.

Thanks so much for the info! I really appreciate it. JJ

[JJ]

On Jan 4, 9:33 am, Pmkadow <pmka...@gmail.com> wrote:
> JJ,
>
> Why would you want to do that?
>
> -Mike

Hi Mike,

Well, it's not a matter of wanting to <grin>, I just have to. I'm
currently taking data that prints to the screen as delimited text and
importing into a Excel spreadsheet that has some formatting already in
it. I have to do this daily. It's a drag. I'd like to automate it.
This data is then emailed to several people for daily management
meetings. My bosses want Excel, so they get Excel. :-) JJ

[Pmkadow]

JJ,

 

I do understand giving bosses what they want, however, it seems to be to be easier to create a delimited file and import it into Excel,

or is that what you are doing?

 

-Mike

[Roberto]

But why go through the import process in Excel when you can just
create the Excel file altogether ready to be printed or emailed right
from Cache?

-Roberto

On Jan 4, 10:48 am, Pmkadow <pmka...@gmail.com> wrote:
> JJ,
>

> > Caché 2010.2.2 released on December 31, 2010- Hide quoted text -
>
> - Show quoted text -

[Pmkadow]

>>But why go through the import process in Excel when you can just
>>create the Excel file altogether ready to be printed or emailed right
>>from Cache?

 

I would say for the sake of maintenance. Excel is going to evolve and change,

and do you want the burden of changing your Cache code everytime Excel

comes out with a new version. That is why I suggest just creating a simple

delimited text, that should almost never have to change.

 

That is my 2 cents.

 

-Mike

[infHos]

Hello JJ:

 

No canonic but enough:

set dev=”file.xls”

open dev:”wns” use dev

&html<

<table cellspacing=0 cellpadding=0 border=1>

<tr>

<td>#(text_cell_1)#</td>

<td>#(text_cell_2)#</td>

….

</tr>

<tr>

….

</tr>

 

</table>

> 

close dev

 

Hugo

[Mohamed Sami]

Did you consider using .Net (or PHP) to pull that data, create the Excel file and email it?

You can also use Job scheduler to run it daily without you being involved.

Mohamed

--

Mohamed Sami

Program: MBA (11/2010)

Student Id: 210654

Mentor: Chad Watson

Cell: (314) 605-5116

Fishers, Indiana (Eastern Time)

[JJ]

On Jan 4, 10:48 am, Pmkadow <pmka...@gmail.com> wrote:
> JJ,
>

> I do understand giving bosses what they want, however, it seems to be to be
> easier to create a delimited file and import it into Excel,
> or is that what you are doing?
>
> -Mike

That's what I'm already doing...every day. I'd like to automate it
and have it emailed, which is what I do manually. Thought I convert
to a PDF first, but the xls file would be fine as well. Thx, JJ

[JJ]

On Jan 4, 11:05 am, Pmkadow <pmka...@gmail.com> wrote:
> >>But why go through the import process in Excel when you can just
> >>create the Excel file altogether ready to be printed or emailed right
> >>from Cache?
>

> I would say for the sake of maintenance. Excel is going to evolve and
> change,
> and do you want the burden of changing your Cache code everytime Excel
> comes out with a new version. That is why I suggest just creating a simple
> delimited text, that should almost never have to change.
>
> That is my 2 cents.
>
> -Mike

They don't seem to update their Excel very often. I still have 2003
on my laptop. And these are fairly simple data files. I don't think
future versions of Excel would have a problem but if I have to update,
then I have to update. Thx, JJ

[JJ]

On Jan 4, 10:59 am, Mohamed Sami <ms...@wgu.edu> wrote:
> Did you consider using .Net (or PHP) to pull that data, create the Excel
> file and email it?
> You can also use Job scheduler to run it daily without you being involved.
>
> Mohamed

I don't have any .Net experience and while I did just finish a great
PHP college course, I'd like to stick to doing this w/i Cache. We are
on Cache now and do not have it configured to work with PHP. We
already have a scheduler running the compile for the data, I just
print it manually and import to Excel. Once I get something working,
that step would also be added to our scheduler. Thx! JJ

[Jason Warner]

If you are already sending a .PDF, have you looked at Zen Reports to
generate and then email the report you are putting together right now?
That way you can keep your current format and not have to worry about
Excel at all.

jason

[OldMster]

Creating an excel 'XML' file is the best way to go.  It will import directy into excel, you can apply formatting, and do multiple tabs, none of which you can do with the comma/tab delimited import.  The XML file standard is more stable than the XLS file, so you shouldn't have excel upgrade issues either.  It is a lot more work than a comma/tab delimited file, but that is the cost of capability.

Mark

[Anil Mathew]

Hi I have a rouitne like this
===============================
AdmCalcData ; Caluclate Value
GetCalc(P1)
    s oid=$p(P1,$C(1),1)
    s val=$p(P1,$C(1),2)
    s Oref=##class(MyUser.People).%OpenId(oid)
    s MinVal=Oref.MinVal+val
    s NOref=##class(MyUser.People).%New()
    s NOref.MinVal=Minval
    d NOref.%Save()
========================
The Problem is there are times when there is no record for 'oid' and in such a situation (the reason as to why is not important, this is just a scenario)
s MinVal=Oref.MinVal+val   gives me an <UNDEFINED> *Oref   error as there is no such record.
How do I overcome this so that even if there is no reocord it should recognize the Oref.MinVal property instead of giving an undefined error.
what i am doing right now is as follows:, is it correct to do so or is there any other way of doing it?
AdmCalcData ; Caluclate Value
GetCalc(P1)
    s oid=$p(P1,$C(1),1)
    s val=$p(P1,$C(1),2)
    s Oref=##class(MyUser.People).%OpenId(oid)
    i $g(Oref)="" s Oref=##class(MyUser.People).%New() ; Open a Dummy Oref
    s MinVal=Oref.MinVal+val
    s NOref=##class(MyUser.People).%New()
    s NOref.MinVal=MinFval
    d Oref.%Close() ; Close the Dummy Oref
    d NOref.%Save()
    d NOref.%Close()

Regards
Anil

[rtweed]

..and if it was me, the way I would programmatically generate and
manipulate the Excel XML document would be as an XML DOM rather than
by constructing a text file. Is this easy to do in Cache? Yep - if
you use EWD (www.mgateway.com/ewd.html). See
https://groups.google.com/group/enterprise-web-developer-community/browse_thread/thread/36c7503939bbc460/3930b573c5726fd0?hl=en&lnk=gst&q=xml+dom#3930b573c5726fd0

...which will get you started. All you now need to understand is the
structure and syntax needed for Excel XML files.

Rob

[servit]

Hello, JJ.

See
1) TMS Flexcel Studio for .NET: http://tmssoftware.com/site/flexcelnet.asp
2) Aspose.Cells for Java: http://www.aspose.com/categories/java-components/aspose.cells-for-java/default.aspx
or Aspose.Cells for .NET: http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx

Use with the Caché Gateway for .NET or Java Gateway.

[JJ]

On Jan 4, 11:24 am, Jason Warner <jas...@brashers.com> wrote:
> If you are already sending a .PDF, have you looked at Zen Reports to
> generate and then email the report you are putting together right now?
> That way you can keep your current format and not have to worry about
> Excel at all.
>
> jason

No sorry...I manually make the PDF after capturing the delimited text,
importing to Excel, making a few manual tweaks, then saving as a PDF.
It's a multi-step process, and that's why I'd like to automate it.
Thx, JJ

[JJ]

Thanks Mark. This sounds like what I'm going to need to do.
Appreciate it! JJ

[JJ]

On Jan 5, 2:55 am, rtweed <rob.tw...@gmail.com> wrote:
> On Jan 4, 6:06 pm, OldMster <msi...@verizon.net> wrote:
>
> > Creating an excel 'XML' file is the best way to go.  It will import directy
> > into excel, you can apply formatting, and do multiple tabs, none of which
> > you can do with the comma/tab delimited import.  The XML file standard is
> > more stable than the XLS file, so you shouldn't have excel upgrade issues
> > either.  It is a lot more work than a comma/tab delimited file, but that is
> > the cost of capability.
> > Mark
>
> ..and if it was me, the way I would programmatically generate and
> manipulate the Excel XML document would be as an XML DOM rather than
> by constructing a text file.  Is this easy to do in Cache?  Yep - if

> you use EWD (www.mgateway.com/ewd.html).  Seehttps://groups.google.com/group/enterprise-web-developer-community/br...

>
> ...which will get you started.  All you now need to understand is the
> structure and syntax needed for Excel XML files.
>
> Rob

HI Rob. I already follow EWD, thanks. But I'm not in a position to
recommend new software. I pretty much need to do what I can within
Cache as is. But thanks! I'm very interested in EWD and hope we can
look into it in the future. JJ

[JJ]

On Jan 5, 6:02 am, servit <Y2...@mail.ru> wrote:
> Hello, JJ.
>
> See
> 1) TMS Flexcel Studio for .NET:http://tmssoftware.com/site/flexcelnet.asp

> 2) Aspose.Cells for Java:http://www.aspose.com/categories/java-components/aspose.cells-for-jav...
> or Aspose.Cells for .NET:http://www.aspose.com/categories/.net-components/aspose.cells-for-.ne...

>
> Use with the Caché Gateway for .NET or Java Gateway.

Thanks for the links. I'll take a look. But as I said to Rob, I
pretty much need to try to do this with just Cache as is. COS. Thx,
JJ

[servit]

Hello, JJ.

> I pretty much need to try to do this with just Cache as is. COS.

Ok. See here: http://groups.google.com/group/intersystems-public-cache/t/c393ee98e0f511c

[Steve]

JJ,

clearly from the responses, you have a number of options. I thought
I'd add another possible option.

We use Cache Activate (Tools -> Add Ins -> Activate Wizard from the
Studio) to create Cache wrapper classes for Excel and then manipulate
files in Excel prior to loading into Cache.

We are doing the opposite of what you need to do - taking Excel format
files and saving as tab delimited output, but the reverse process
should be possible. The code below is a simple set of commands to take
in a file and re-save as tab delimited:

// Create a new instance of an Excel application
s obj=##class(Activate.Excel.Application).%New()
s obj.DisplayAlerts = 0
// Open a workbook at the location specified
s res=obj.Workbooks.Open(txtFilename)
// you now have access to the workbooks collection object
// e.g. "w obj.Workbooks.Count"
// Get the first worksheet in the worksheets collection
s ws=res.Worksheets.ItemGet(1)
// The worksheet is an IDespatch object and needs to be cast
// as a full Excel Worksheet object using "Become"
s wso=ws.Become("Activate.Excel.Worksheet")
// Use the Worksheet SaveAs method to save as a tab delimited text
file
// (constant xlTextWindows value 20)
do wso.SaveAs(newfilepath,20)
// etc ...

I hope that helps

Steve Richards

[servit]

Hello, Steve.

I had also used the Caché Activate to generate a Word/Excel, but then
I began to use a another technology because:

1) citation: "Microsoft does not currently recommend, and does not
support, Automation of Microsoft Office applications from any
unattended, non-interactive client application or component (including
ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit
unstable behavior and/or deadlock when Office is run in this
environment."
See http://support.microsoft.com/kb/257757

2) ActiveX and x64

3) MS Office and non Windows

[JJ]

On Jan 5, 7:08 am, servit <Y2...@mail.ru> wrote:
> Hello, JJ.
>

> > I pretty much need to try to do this with just Cache as is.  COS.
>

> Ok. See here:http://groups.google.com/group/intersystems-public-cache/t/c393ee98e0...

Thanks. JJ

[JJ]

Interesting! Thx, JJ

[Derek]

Hi JJ,

I decided to upload this to a code bin in case it can save you a great deal of time (working out all the intricacies of Excel XML is a bit of a pain!).  This is a COS routine I wrote several years back to automatically build Excel XML files:

https://gist.github.com/6fedb3ab0d0794f04677

It could use a lot of refactoring, and you'll have to excuse the mix of new and old style COS.  The parameter passing is also less than ideal, and the entire thing could stand to be a proper Cache class, but it got the job done for me, so feel free to use it and change it around however you like.

There's a note in the comments at the top of the routine that describes how you can reference it in your code.  In short, it will spit out Excel XML to your current device, so you would want to redirect output to a file first before calling this.

I hope this helps!
Derek

[JJ]

Wow! That's very cool. I can certainly learn a few things just by
looking at your code at the very least! I really appreciate your
generosity! :-) I will definitely be looking at this to help me
out. It certainly looks very much like what I was hoping to be able
to do. I've been digging around in the %XML classes, etc. in Cache
and it's all well and good...but I'm a "keep it simple" sorta of
person if at all possible and my OO programming skills just aren't
"there" yet. Many thanks! JJ

[Derek]

You're welcome!  I just noticed that I mis-copied the middle line of code in the small sample in the comments, so I corrected that on github.  Also, while the comments at the top of the routine suggest that we use it to send files to the network, I actually use it more now for generating automatic emails with XML attachments to the necessary people.

[JJ]

Thanks again. :-) Now the fun starts eh? That's exactly what I wish
to do...make a file, email to a few users. Take care, JJ

[Muthukumar Jeyaraj]

Hi Anil

Try the method $IsObject(Oref) for checking the whether the object is valid. It means if there is no record for oid, then $IsObject(Oref) will return zero. so you can avoid undefined errors.

Thanks,
Muthukumar.J

[Anil Mathew]

Thanks, will settle for something like this as suggested by 'servit'
s var1=$s($IsObject(Oref):Oref.Property1,1:0)+value1
Regards
Anil

[osag...@gmail.com]

Hi Derek,

Looks like I am doing the same research as Derek and came across your post. I didn't want to let it pass without saying thank you for the post and the COS routine. Very informative and very helpful!

Thanks,

Patrick

[Paras Batheja]

Hi Derek,

Could you please share the sample code that you put in code bin?

[Peter Smit]

Has anyone tried this using epplus http://epplus.codeplex.com/

together with the dotnet gateway?