Author |
Topic  |
|
hajo_old
Germany
141 Posts |
Posted - 10/28/2003 : 11:04:38 AM
|
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
|
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"); }
|
 |
|
hajo_old
Germany
141 Posts |
Posted - 10/29/2003 : 05:27:17 AM
|
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 |
 |
|
greg
USA
1379 Posts |
Posted - 10/29/2003 : 11:18:17 AM
|
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;
|
 |
|
hajo_old
Germany
141 Posts |
Posted - 10/29/2003 : 3:18:19 PM
|
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 |
 |
|
easwar
USA
1965 Posts |
Posted - 10/29/2003 : 4:20:12 PM
|
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.
|
 |
|
cpyang
USA
1406 Posts |
Posted - 10/29/2003 : 5:09:50 PM
|
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
|
 |
|
hajo_old
Germany
141 Posts |
Posted - 10/30/2003 : 05:11:25 AM
|
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 |
 |
|
hajo_old
Germany
141 Posts |
Posted - 11/05/2003 : 06:57:38 AM
|
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 |
 |
|
cpyang
USA
1406 Posts |
Posted - 11/05/2003 : 09:40:52 AM
|
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
|
 |
|
hajo_old
Germany
141 Posts |
Posted - 11/05/2003 : 1:42:27 PM
|
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 |
 |
|
cpyang
USA
1406 Posts |
Posted - 11/05/2003 : 5:04:21 PM
|
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 |
 |
|
schneide
Germany
47 Posts |
Posted - 05/13/2004 : 06:03:50 AM
|
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 |
 |
|
Mike Buess
USA
3037 Posts |
Posted - 05/13/2004 : 1:11:30 PM
|
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 |
 |
|
schneide
Germany
47 Posts |
Posted - 06/07/2004 : 03:14:52 AM
|
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 |
 |
|
schneide
Germany
47 Posts |
Posted - 06/08/2004 : 04:55:20 AM
|
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 |
 |
|
|
Topic  |
|