excel.cpp

orange源码 数据挖掘技术

/*
    This file is part of Orange.

    Orange is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    Orange is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Orange; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

    Authors: Janez Demsar, Blaz Zupan, 1996--2002
    Contact: [e-mail]
*/


#ifdef INCLUDE_EXCEL

#include "table.hpp"
#include "stringvars.hpp"
#include "domaindepot.hpp"

#include "filegen.hpp"
#include <list>

#include <direct.h>
#include <ole2.h>

// These functions are wrapped into a class for easier implementation of clean-up (especially at exceptions).
class TExcelReader {
public:
  TExcelReader();
  ~TExcelReader();

  TExampleTable *operator ()(char *filename, char *sheet, PVarList sourceVars, PDomain sourceDomain, bool dontCheckStored, bool dontStore);

protected:
  static TDomainDepot domainDepot;

private:
  IDispatch *pXlApp;
  IDispatch *pXlBooks;
  IDispatch *pXlBook;
  IDispatch *pXlSheet;
  IDispatch *pXlUsedRange;

  SAFEARRAY *cells;
  long rowsLow, rowsHigh;
  long columnsLow, columnsHigh;
  long nExamples, nAttrs;

  VARIANT result, args[2];
  DISPPARAMS dp;
  DISPID dispidNamed;
  LPWSTR lfilename;
  LPWSTR lsheet;
  char *cellvalue;
  char celcol[3];

  void Invoke(int autoType, IDispatch *pDisp, LPOLESTR ptName, int cArgs);
  void getProperty(IDispatch *pDisp, LPOLESTR ptName);

  void openFile(char *filename, char *sheet);

  void cellAsVariant(const int &row, const int &col);
  char *cellAsText(const int &row, const int &col);
  int cellType(const int &row, const int &col);

  PDomain constructDomain(vector<int> &specials, PVarList sourceVars, PDomain sourceDomain, bool dontCheckStored, bool dontStore);
  TExampleTable *readExamples(PDomain domain, const vector<int> &specials);
  void readValue(const int &row, const int &col, PVariable var, TValue &value);

  char *column2Chars(const int &);

  static void destroyNotifier(TDomain *domain);

  void setArg(const int argno, const int arg);
};


TDomainDepot TExcelReader::domainDepot;


TExcelReader::TExcelReader()
: pXlApp(NULL),
  pXlBooks(NULL),
  pXlBook(NULL),
  pXlSheet(NULL),
  pXlUsedRange(NULL),
  dispidNamed(DISPID_PROPERTYPUT),
  lfilename(NULL),
  lsheet(NULL),
  cells(NULL),
  cellvalue(NULL)
{
  dp.rgvarg = args;
  dp.rgdispidNamedArgs = &dispidNamed;
  dp.cNamedArgs = 0;

  VariantInit(args);
  VariantInit(args+1);
  VariantInit(&result);
  CoInitialize(NULL);
}


TExcelReader::~TExcelReader()
{
  if (pXlBook) {
    setArg(0, 1);
    Invoke(DISPATCH_PROPERTYPUT, pXlBook, L"Saved", 1);
  }

  if (pXlApp)
    Invoke(DISPATCH_METHOD, pXlApp, L"Quit", 0);

  if (pXlUsedRange)
    pXlUsedRange->Release();
  if (pXlSheet)
    pXlSheet->Release();
  if (pXlBook)
    pXlBook->Release();
  if (pXlBooks)
    pXlBooks->Release();
  if (pXlApp)
    pXlApp->Release();

  if (cells)
    SafeArrayDestroy(cells);

  if (lfilename)
    free(lfilename);
  if (lsheet)
    free(lsheet);
  if (cellvalue)
    free(cellvalue);

  CoUninitialize();
}



void TExcelReader::Invoke(int autoType, IDispatch *pDisp, LPOLESTR ptName, int cArgs)
{
  char name[32];
  WideCharToMultiByte(CP_ACP, 0, ptName, -1, name, 32, NULL, NULL);

  if(!pDisp)
    raiseError("NULL IDispatch passed to AutoWrap()");

  VariantInit(&result); // not variantClear! the previous caller got the result and is responsible for it!
  dp.cArgs = cArgs;
  dp.cNamedArgs = (autoType & DISPATCH_PROPERTYPUT) ? 1 : 0;

  DISPID dispID;

  if (FAILED(pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID)))
    raiseError("IDispatch::GetIDsOfNames(\"%s\") failed", name);
  
  if (FAILED(pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, &result, NULL, NULL)))
    if (strcmp(name, "Open"))
      raiseError("IDispatch::Invoke(\"%s\") failed", name);
    else
      raiseError("File not found (or cannot be opened)");
}


void TExcelReader::getProperty(IDispatch *pDisp, LPOLESTR ptName)
{ Invoke(DISPATCH_PROPERTYGET, pDisp, ptName, 0); }


void TExcelReader::setArg(const int argno, const int arg)
{ args[argno].vt = VT_I4;
  args[argno].lVal = arg;
}


void TExcelReader::openFile(char *filename, char *sheet)
{
  // Get the full path name and the sheet name (if given)
  char fnamebuf[1024], *foo;
  long fulllen;
  if (*sheet) {
    *sheet = 0;
    fulllen = GetFullPathName(filename, 1024, fnamebuf, &foo);

    const int slen = 2+2*strlen(sheet+1);
    lsheet = (LPWSTR)malloc(slen);
    MultiByteToWideChar(CP_ACP, 0, sheet+1, -1, lsheet, slen);

    *sheet = '#';
  }
  else
    fulllen = GetFullPathName(filename, 1024, fnamebuf, &foo);

  if (!fulllen)
    raiseError("invalid filename or path too long");

  lfilename = (LPWSTR)malloc(fulllen*2+2);
  MultiByteToWideChar(CP_ACP, 0, fnamebuf, -1, lfilename, fulllen*2+2);

  
  // Open Excel
  CLSID clsid;
  HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

  if(FAILED(hr))
    raiseError("CLSIDFromProgID() failed");

  hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
  if(FAILED(hr))
    raiseError("Excel not registered properly");

/*  // Make it visible (i.e. app.visible = 1)
  setArg(0, 1);
  Invoke(DISPATCH_PROPERTYPUT, pXlApp, L"Visible", 1);
*/

  // Open the worksheet and get the range
  getProperty(pXlApp, L"Workbooks");
  pXlBooks = result.pdispVal;

  args->vt = VT_BSTR;
  args->bstrVal = SysAllocString(lfilename);
  Invoke(DISPATCH_PROPERTYGET, pXlBooks, L"Open", 1);
  pXlBook = result.pdispVal;
  VariantClear(args);

  if (lsheet) {
    args->vt = VT_BSTR;
    args->bstrVal = SysAllocString(lsheet);
    Invoke(DISPATCH_PROPERTYGET, pXlBook, L"Sheets", 1);
    VariantClear(args);
  }
  else
    getProperty(pXlApp, L"ActiveSheet");

  pXlSheet = result.pdispVal;

  getProperty(pXlSheet, L"UsedRange");
  pXlUsedRange = result.pdispVal;

  getProperty(pXlUsedRange, L"Value");
  cells = result.parray;

  SafeArrayGetLBound(cells, 1, &rowsLow);
  SafeArrayGetUBound(cells, 1, &rowsHigh);
  nExamples = rowsHigh - rowsLow; // no -1 -- these are inclusive limits!

  SafeArrayGetLBound(cells, 2, &columnsLow);
  SafeArrayGetUBound(cells, 2, &columnsHigh);
  nAttrs = columnsHigh - columnsLow + 1;
}


char *TExcelReader::column2Chars(const int &col)
{ if (col<=26) {
    celcol[0] = col+'A';
    celcol[1] = 0;
  }
  else {
    celcol[0] = col/26 + 'A';
    celcol[1] = col%26 + 'A';
    celcol[2] = 0;
  }

  return celcol;
}