The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
Username:
Password:
Save Password
Forgot your Password? | Admin Options

 All Forums
 Origin Forum for Programming
 Forum for Origin C
 importing excel sheet to Wks
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

hajo_old

Germany
141 Posts

Posted - 10/28/2003 :  11:04:38 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Hello, all

I want to import only one specific Excel sheet (total sheets are 5)
to an Origin Workbook.

Here's the code I'm using for import (but this code imports all sheets contained in the Excel-Workbook):


LT_execute("system.excel.openAsExcel=0;"); // 0 = open as Origin worksheet
LT_execute("system.excel.openAsPrompt=0;"); // Abfragedialog unterdruecken
Worksheet wks;
wks.Open(fqfn);


Is there a chance to suppress the import dialog AND to specify the sheet I want to import(e.g. sheet 3)?
(Maybe a pure OriginC sollution)

Thanks
Hajo

-- --
Dipl.-Ing. Hans-Joerg Koch
Siemens VDO, Regensburg

SVDO_Origin1

easwar

USA
1965 Posts

Posted - 10/28/2003 :  2:24:03 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi Hajo,

Since you asked for a pure OC solution, here is one!

This uses COM programming to open the Excel file, extract the specified Excel wks to a disk text file, and then import that text file into the Origin wks.

So this needs OriginPro 7.0/7.5 since COM can be compiled only in Pro.

Please see comments in code to make appropriate changes to meet your file locations and template specifications etc.

Easwar
OriginLab.



int read_excel_sheet(string strExcelFileName, string strExcelSheetName, string strTempFileName)
{
// Declare Excel objects
Object oExcel, oExcelWkbks, oExcelWkbk, oExcelWksh;

// Create an Excel application object and set it as invisible
oExcel = CreateObject("excel.application");
oExcel.Visible = false;

// Get the workbooks collection of the Excel object
oExcelWkbks = oExcel.Workbooks;

// Open the workbook with data to be analyzed
oExcelWkbk = oExcelWkbks.Open(strExcelFileName);

// turn off Excel messages
oExcel.DisplayAlerts = FALSE;

// Loop over each worksheet in the Excel workbook and find the specified sheet
//write them out to the temp text file
StringArray saFileNames;
string strSingleFile;
BOOL bSheetFound = FALSE;
foreach (oExcelWksh in oExcelWkbk.Worksheets)
{
string strName = oExcelWksh.Name;
// Match wksheet name to specified name
if(0 == strName.CompareNoCase(strExcelSheetName))
{
bSheetFound = TRUE;
DWORD dwFileFormat = 20;
oExcelWksh.SaveAs(Filename:=strTempFileName, FileFormat:=dwFileFormat);
}
}
// Close Excel application
oExcel.Quit();

return bSheetFound;
}


// Use this function to test
//
void dd()
{
// Change to your Excel path and file, and sheet name etc.
string strExcelFileName = "D:\\temp\\MultipleWorksheets.XLS";
string strExcelSheetName = "Sheet2";
// Specify temp file name to use to store text file
string strTempFileName = "D:\\temp\\temp.txt";
int iFoundSheet = read_excel_sheet(strExcelFileName, strExcelSheetName, strTempFileName);
if(iFoundSheet)
{
printf("Sheet %s was found!\n", strExcelSheetName);
// Import text file into new wks
WorksheetPage wpg;
// Create using default template
// Replace with your own template if you have custom ASCIMP settings
wpg.Create("Origin.OTW");
Worksheet wks = wpg.Layers(0);
bool bRet = wks.ImportASCII(strTempFileName);
if(!bRet) printf("Failed to import temp text file into wks\n");
}
else
printf("Sheet %s was not found\n");
}


Go to Top of Page

hajo_old

Germany
141 Posts

Posted - 10/29/2003 :  05:27:17 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello, easwar

thanks for the OriginC code. I'm going to import that into my class ...

On the other hand, is there an option for "system.excel. .... " to select the sheet to import using LabTalk? Or is in this case also COM used?

Maybe as "system.excel.importSheetNo=2" (I would prefer that) or "system.excel.inmportSheetName="Sheet1""; May that be a feature?

Thanks so far
Hajo

-- --
Dipl.-Ing. Hans-Joerg Koch
Siemens VDO, Regensburg

SVDO_Origin1
Go to Top of Page

greg

USA
1379 Posts

Posted - 10/29/2003 :  11:18:17 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
LabTalk doesn't have an option to open only one sheet from an Excel workbook, so you would have to do the import, then delete the unwanted sheets.

// Prompt for the Excel file to open (if needed)
run.section(file,Init);
FDLOG.UseGroup(Excel);
if(FDLOG.Open(A)) return 1;

// Open the workbook as Origin worksheets
system.Excel.OpenAsExcel=0; // Open as Origin worksheets
system.Excel.OpenAsPrompt=0; // No prompt
%B=FDlog.path$;
doc -append %B%A;


Go to Top of Page

hajo_old

Germany
141 Posts

Posted - 10/29/2003 :  3:18:19 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello, greg

thanks for your code ...
That's what I have been thinking of...

An other problem:
As I told before I integrated the OriginC code easwar posted into my class.

The thing now is, that the call
 wks.ImportASCII(strTempFileName);

only opens an empty worksheet.
What I've done is, that I have created an user import filter (*.oif-file) for an other type of *.txt files to import. As the temporary stored Excel sheet also has the *.txt ending I guess Origin uses the import filter of the wrong type to import the text file and fails!

Can I somehow pass the right filter name ("D:\\Origin_User\\filter\\import_filter.oif") to the ImportASCII-function (ore is there an other function provided to do so)?
The "import_filter.oif" was created by the newly introduced ImportWizzard (Origin 7.5714) especially for the import of the exported excel file.

Thanks a lot
Hajo

-- --
Dipl.-Ing. Hans-Joerg Koch
Siemens VDO, Regensburg

SVDO_Origin1
Go to Top of Page

easwar

USA
1965 Posts

Posted - 10/29/2003 :  4:20:12 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:

Can I somehow pass the right filter name ("D:\\Origin_User\\filter\\import_filter.oif") to the ImportASCII-function (ore is there an other function provided to do so)?



Hi Hajo,

In ver 7.5, if you look in FileImport.h, under \OriginC\OriginLab subfolder, you will find the function ImportFile(), which takes as one of the arguments, a filter file that is on disk.

And for an example of how to use this, see the sample project:
\Samples\Programming\User-Defined Import
which opens an Excel file and imports each sheet of the Excel workbook into individual Origin worksheets using a saved import filter file, so that the text in the Excel worksheet is parsed for header variables etc. Hope that sample in combination with the code I posted earlier is what you are looking for.

Easwar
OriginLab.

Go to Top of Page

cpyang

USA
1406 Posts

Posted - 10/29/2003 :  5:09:50 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Maybe as "system.excel.importSheetNo=2" (I would prefer that) or "system.excel.inmportSheetName="Sheet1""; May that be a feature?


The LT command
layer -s;
will connect the top Excel sheet to an internal Origin worksheet. Once connected, you can access this just like any worksheet. Maybe we need to add option for the layer -s command to connect a particular sheet.

CP


Go to Top of Page

hajo_old

Germany
141 Posts

Posted - 10/30/2003 :  05:11:25 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello easwar, hello cpyung

thanks
Im going to try that out

-------
ImportFile() works fine!!
That's it!!

Cio
hajo

-- --
Dipl.-Ing. Hans-Joerg Koch
Siemens VDO, Regensburg

SVDO_Origin1

Edited by - SVDO_Origin1 on 10/30/2003 11:28:21 AM
Go to Top of Page

hajo_old

Germany
141 Posts

Posted - 11/05/2003 :  06:57:38 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:

The LT command
layer -s;
will connect the top Excel sheet to an internal Origin worksheet. Once connected, you can access this just like any worksheet. Maybe we need to add option for the layer -s command to connect a particular sheet.

CP



Hello, CP

The layer command is a Labtalk command ... Is there a possibility to directly access an imported excel sheet (or it's cells) out of OriginC?

Ciao
Hajo

-- --
Dipl.-Ing. Hans-Joerg Koch
Siemens VDO, Regensburg

SVDO_Origin1
Go to Top of Page

cpyang

USA
1406 Posts

Posted - 11/05/2003 :  09:40:52 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Is there a possibility to directly access an imported excel sheet (or it's cells) out of OriginC?



Origin access Excel via Origin's worksheet, so the layer -s command and the OC function Worksheet::UpdateOrigin are used to copy the data from Excel into an attached Origin worksheet. So to access the cells in Excel, you can already do it with the top sheet which is automatically attached to an Origin worksheet. You can look up the UpdateOrigin command. What seems like missing is a command in WorksheetPage class to connect to a particular sheet. I have added a tracker (#5443) for this and hope that we can take care of this in SR1.

CP


Go to Top of Page

hajo_old

Germany
141 Posts

Posted - 11/05/2003 :  1:42:27 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello, CP

If I get you right, I can access the excel worksheet cells by opening the excel worksheet directly into Origin, calling UpdateOrigin which binds the excel sheet to the first worksheet in the worksheet-collection (Project.Worksheets) and then I can access the interested cells like doing that with normal Origin worksheet?

May you give a short example / add that to the help files?

Thanks
Hajo

-- --
Dipl.-Ing. Hans-Joerg Koch
Siemens VDO, Regensburg

SVDO_Origin1
Go to Top of Page

cpyang

USA
1406 Posts

Posted - 11/05/2003 :  5:04:21 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
The following code does read Excel cells, but not very clean. Best to wait for SR1 when we clean up this area better (#5443).


void load_excel(int nCol=0, int nRow = 3)
{
string strFile;
strFile = GetOpenBox("*.xls");
if(!strFile.IsFile())
{
out_str("File not found");
return;
}

string strLT = "doc -append ";
strLT += strFile;
LT_execute(strLT); // Load Excel workbook, we will provide OC command in SR1

WorksheetPage pg = Project.Pages(); // not clean, but once OC command added, will return page
Worksheet wks;
wks = pg.Layers();
if(wks == NULL)
return; // something wrong
wks.UpdateOrigin();

printf("Exel value at R%dC%d is %f\n", nRow+1,nCol+1, wks.Cell(nRow, nCol));
pg.Destroy();
}






CP




Edited by - cpyang on 11/05/2003 5:24:03 PM
Go to Top of Page

schneide

Germany
47 Posts

Posted - 05/13/2004 :  06:03:50 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello,

I tried using fileimport(), but get a linker error, just as I do when I try running Excel_Import.c from \samples\programming\user-defined Import within the code builder environment, and not by calling it from the code builder wizzard.

What do I have to do when I want to import from within origin C an ascii-file using fileimport()?
Would anyone have a short example code which runs within the code builder environment without need to invoke the importfile wizzard?

Giselher

Giselher
Go to Top of Page

Mike Buess

USA
3037 Posts

Posted - 05/13/2004 :  1:11:30 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi Giselher,

Several Origin C files are compiled when you open the Import Wizard and at least one (not sure which) is needed for fileimport(). You can add all IW files to your CodeBuilder workspace without actually opening the Import Wizard.

In CodeBuilder...
File->Open Workspace and select the following workspace file.
Origin program folder\OriginC\OriginLab\ImportWiz.ocw
Tools->Rebuild All (compile and link)

- or -

In Origin...
Enter the following command in the script window.
run.loadOC(OriginLab\ImportWiz.ocw);

Either way should eliminate your linker error.

Mike Buess
Origin WebRing Member

Edited by - Mike Buess on 05/14/2004 08:59:39 AM
Go to Top of Page

schneide

Germany
47 Posts

Posted - 06/07/2004 :  03:14:52 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
I open an excel file as in Greg's example into an origin worksheet, which will appear as, say Data2.
Now I can't access these columns as Data2_A (e.g. for descriptive statistics from the menu), typing %C in the Skript window reveals this column to be Book1_A.
The same seems to happen when I use the menu command open Excel.
So the window title says Data2 as name, somewhere internally it seems to be named Book1.
Is this a bug?
How can I avoid this? Or do I have to rename the window to %C ?

Giselher
Go to Top of Page

schneide

Germany
47 Posts

Posted - 06/08/2004 :  04:55:20 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
I found the reason for this behaviour: My excel worksheet name contained "." and "[", what messes up origin's import filter function.
After renaming the worksheet things work as expected.

Giselher
Go to Top of Page
  Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000