| T O P I C R E V I E W |
| rainlane |
Posted - 09/04/2017 : 10:36:04 PM
Origin Ver. and Service Release (Select Help-->About Origin): OriginPro 2017
Operating System:Windows 7
The function ImportExcel can import an Excel sheet (97-2003 *.xls format) by replacing existing data.
Which funciton can import an Excel sheet(*.xlsx format) by replcing existing data?
forum |
| 6 L A T E S T R E P L I E S (Newest First) |
| Chris D |
Posted - 09/06/2017 : 12:33:54 PM
Hi,
As a correction to Yuki, you can import XLSX files without having to resort to COM.
You would, as previously mentioned by Nick, call the impMSExcel X-Function (new to 2017) via OriginC. See:
http://www.originlab.com/doc/X-Function/ref/impMSExcel
Nick illustrated a purely OriginC method to call X-Functions from within OriginC. It is discussed on this doc page:
http://www.originlab.com/doc/X-Function/guide/Calling-X-Functions-in-Origin-C
However, in many cases, it is simpler to call the X-Function using familiar LabTalk syntax and running the LabTalk from within OriginC using the LT_execute function as illustrated below. The two functions I used (LT_set_str and LT_execute) are documented here:
http://www.originlab.com/doc/OriginC/ref/LabTalk-Interface
To determine the proper syntax for your X-Function call, you can simply act as if you were importing the file via the File menu. Once the impMSExcel options dialog opens, choose your options and then click the little arrow button on the top right of the dialog and select "Generate Script". This will output the proper syntax to the Script Window (you can click the Cancel button in the dialog). You would simply modify the script to exclude the fname:="file.ext" portion.
Note: I actually assign the file that I want to open to the LabTalk fname$ variable via LT_set_str. This LabTalk variable is used as a default variable by all related importing X-Functions to specify the file to import. It does not have to be specifically assigned when calling the X-Function- Origin simply looks for its existance. This lets you avoid having to format or concatenate a string in OriginC which would make it harder to read and error prone.
void MyImportExcel()
{
string strFile = GetOpenBox("*.xlsx");
if( strFile.IsEmpty() )
return;
// Assign the file name to the fname$ LabTalk variable.
// impMSExcel X-Function will use this variable for the file name.
LT_set_str("fname$", strFile);
LT_execute("impMSExcel options.headers.SubHeaderLines:=3 options.headers.LongName:=1 options.headers.Unit:=2 options.headers.CommentFrom:=3;");
}
Thanks,
Chris Drozdowski
Originlab Technical Support
|
| rainlane |
Posted - 09/06/2017 : 02:51:29 AM
aha,I faced the same problem.
I try to solve it.
quote:
Originally posted by nick_n
Hi,
It should be:
if( !xfimpMSExcel.SetArg("options.headers.SubHeaderLines", -1) ) {out_str("Failed to set argument"); return 0;}
if( !xfimpMSExcel.SetArg("options.headers.LongName", 1) ) {out_str("Failed to set argument"); return 0;}
But I faced problem with passing arguments. Actually, if you have installed Excel the XFunction "impExcel" must work. Probably it would be easy to pass arguments with that function ("lname:=1"). Unfortunately, I have no time to check that right now.
Nikolay
forum |
| nick_n |
Posted - 09/06/2017 : 02:39:44 AM
Hi,
It should be:
if( !xfimpMSExcel.SetArg("options.headers.SubHeaderLines", -1) ) {out_str("Failed to set argument"); return 0;}
if( !xfimpMSExcel.SetArg("options.headers.LongName", 1) ) {out_str("Failed to set argument"); return 0;}
But I faced problem with passing arguments. Actually, if you have installed Excel the XFunction "impExcel" must work. Probably it would be easy to pass arguments with that function ("lname:=1"). Unfortunately, I have no time to check that right now.
Nikolay |
| rainlane |
Posted - 09/05/2017 : 11:30:16 PM
Your code is very useful. Thank you so much!
In the X-Function,
impMSExcel fname:="c:\test2.xlsx" options.sparklines:=1 options.Mode:=4 options.Headers.LongName:=1 options.Headers.Units:=2;
can import test2.xlsx and set LongName.
I want to achieve it in OriginC.
But I don't konw how to defied strOption.
if( !xfimpMSExcel.SetArg("options", strOption) )
{
out_str("Failed to set option");
return 0;
}
Is there any suggestions?
quote:
Originally posted by nick_n
Hi,
I would use XF instead of OC in that case. I didn't go deep, maybe there is another good way.
#include <Origin.h>
#include <XFbase.h>
int impxlsx()
{
string strFile = GetOpenBox("*.xlsx");
XFBase xfimpMSExcel("impMSExcel");
if (!xfimpMSExcel) {out_str("Failed to run X-Function."); return 0;}
if( !xfimpMSExcel.SetArg("fname", strFile) ) {out_str("Failed to set argument"); return 0;}
if( !xfimpMSExcel.Evaluate()) {out_str("Failed to evaluate X-Function."); return 0;}
return 1;
}
BR,
Nikolay
forum |
| yuki_wu |
Posted - 09/05/2017 : 05:45:04 AM
Hi,
To import Excel sheet with *.xlsx format, use Excel COM:
http://www.originlab.com/doc/OriginC/guide/Access-an-External-Application
Regards,
Yuki
OriginLab
|
| nick_n |
Posted - 09/05/2017 : 03:26:08 AM
Hi,
I would use XF instead of OC in that case. I didn't go deep, maybe there is another good way.
#include <Origin.h>
#include <XFbase.h>
int impxlsx()
{
string strFile = GetOpenBox("*.xlsx");
XFBase xfimpMSExcel("impMSExcel");
if (!xfimpMSExcel) {out_str("Failed to run X-Function."); return 0;}
if( !xfimpMSExcel.SetArg("fname", strFile) ) {out_str("Failed to set argument"); return 0;}
if( !xfimpMSExcel.Evaluate()) {out_str("Failed to evaluate X-Function."); return 0;}
return 1;
}
BR,
Nikolay |
|
|
