bundles/org.simantics.excel/native/SimanticsExcel2008/SimanticsExcel2008/Interface.cpp

   1
   2 #include "stdafx.h"
   3
   4 #include <sstream>
   5 #include <map>
   6
   7 using namespace std;
   8
   9 #import "c:/program files (x86)/common files/microsoft shared/office12/mso.dll" \
  10         rename( "RGB", "MSORGB" )
  11
  12 using namespace Office;
  13
  14 #import "c:/program files (x86)/common files/microsoft shared/vba/vba6/vbe6ext.olb" \
  15
  16 using namespace VBIDE;
  17
  18 #import "c:/program files (x86)/microsoft office/office12/excel.exe" \
  19         rename( "DialogBox", "ExcelDialogBox" ) \
  20         rename( "RGB", "ExcelRGB" ) \
  21         rename( "CopyFile", "ExcelCopyFile" ) \
  22         rename( "ReplaceText", "ExcelReplaceText" )
  23
  24 #include "jni2.h"
  25
  26 //#define SIMANTICS_DEBUG(...) \
  27 //{ FILE *fp = fopen("d:/excel.log", "ab"); fprintf(fp, __VA_ARGS__); fflush(fp); fclose(fp);  }
  28
  29 #define SIMANTICS_DEBUG(...)
  30
  31 using namespace std;
  32 using namespace Excel;
  33
  34 map<int, Excel::_WorksheetPtr> handles;
  35 static int handleCounter = 1;
  36
  37 void replace(std::string &s, const std::string &s1, const std::string &s2) {
  38
  39         int index = 0;
  40
  41         index = s.find(s1, index);
  42         while(index != std::string::npos) {
  43                 s.replace(index, s1.length(), s2.c_str(), s2.length());
  44                 index = s.find(s1, index + 1);
  45         }
  46
  47 }
  48
  49 boolean getString(JNIEnv *env, jstring str, char *target) {
  50
  51     jboolean iscopy;
  52     const char *chars = env->GetStringUTFChars(str, &iscopy);
  53         if(!chars) return false;
  54
  55         strcpy(target, chars);
  56
  57     env->ReleaseStringUTFChars(str, chars);
  58
  59         return true;
  60
  61 }
  62
  63 string getString(JNIEnv *env, jstring str) {
  64
  65     jboolean iscopy;
  66     const char *chars = env->GetStringUTFChars(str, &iscopy);
  67         if(!chars) return 0;
  68         string result = chars;
  69     env->ReleaseStringUTFChars(str, chars);
  70         return result;
  71
  72 }
  73
  74 Excel::_ApplicationPtr getApplication(bool create) {
  75
  76         Excel::_ApplicationPtr application;
  77
  78         CLSID clsid;
  79         HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
  80         if(FAILED(hr)) {
  81                 return 0;
  82         }
  83
  84         if (!SUCCEEDED(application.GetActiveObject(clsid)))
  85         {
  86                 if(create) {
  87                         hr = application.CreateInstance(clsid);
  88                         if(FAILED(hr)) {
  89                                 return 0;
  90                         }
  91                 } else {
  92                         return 0;
  93                 }
  94         }
  95
  96         application->EnableEvents = VARIANT_FALSE;
  97
  98         return application;
  99
 100 }
 101
 102 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_init(JNIEnv *env, jobject) {
 103
 104         SIMANTICS_DEBUG("Excel init\r\n");
 105
 106         HRESULT hr = ::OleInitialize(NULL);
 107         if(FAILED(hr)) {
 108                 return hr;
 109         }
 110
 111         /*
 112         Excel::_ApplicationPtr application = getApplication(true);
 113         if(!application) return -1;
 114         bstr_t version = application->GetVersion(0);
 115         ostringstream os;
 116         os << version << endl;
 117         SIMANTICS_DEBUG("Init successful with version %s\r\n",  os.str().c_str());
 118
 119         application->put_Interactive( 0, VARIANT_FALSE );
 120         application->put_Visible( 0, VARIANT_FALSE );
 121         */
 122
 123         return 0;
 124
 125 }
 126
 127 Excel::_WorkbookPtr getWorkbook(const Excel::_ApplicationPtr &application, const string &fileName) {
 128
 129         SIMANTICS_DEBUG("getWorkbook(%s)\r\n", fileName.c_str());
 130
 131         for(int i=0;i<application->Workbooks->Count;i++) {
 132                 _bstr_t fullName;
 133                 Excel::_WorkbookPtr workbook = application->Workbooks->GetItem( _variant_t(i+1) );
 134                 workbook->get_FullName(0, &fullName.GetBSTR());
 135                 std::string fullNameStr = fullName;
 136                 SIMANTICS_DEBUG("'%s'\r\n", fullNameStr.c_str());
 137                 if(fullNameStr == fileName) return workbook;
 138         }
 139
 140         return 0;
 141
 142 }
 143
 144 Excel::_WorksheetPtr getWorksheet(const Excel::_WorkbookPtr workbook, const string &sheetName) {
 145
 146         SIMANTICS_DEBUG("getWorksheet(%s)\r\n", sheetName.c_str());
 147
 148         for(int i=0;i<workbook->Sheets->Count;i++) {
 149                 Excel::_WorksheetPtr sheet = workbook->Sheets->GetItem(_variant_t(i+1));
 150                 std:string name = sheet->Name;
 151                 SIMANTICS_DEBUG("'%s'\r\n", name.c_str());
 152                 if(name == sheetName) return sheet;
 153         }
 154
 155         return 0;
 156
 157 }
 158
 159 char message[1024];
 160
 161 JNIEXPORT jstring JNICALL Java_org_simantics_excel_Excel_open(JNIEnv *env, jobject, jstring fileName, jstring sheetName_) {
 162
 163         string name = getString(env, fileName);
 164         string sheetName = getString(env, sheetName_);
 165
 166         _variant_t varOption( (long) DISP_E_PARAMNOTFOUND, VT_ERROR );
 167
 168         Excel::_ApplicationPtr application = getApplication(true);
 169         if(!application) return env->NewStringUTF("Excel application could not be started.");
 170
 171         Excel::_WorkbookPtr workbook = getWorkbook(application, name);
 172         if(!workbook) {
 173
 174                 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Opening.\r\n", name.c_str());
 175
 176                 FILE *fp = fopen(name.c_str(), "r");
 177                 if(fp) {
 178
 179                 SIMANTICS_DEBUG("getWorkbook(%s) book was found. Opening file.\r\n", name.c_str());
 180
 181                         fclose(fp);
 182                         workbook = application->Workbooks->OpenXML(_bstr_t(name.c_str()),
 183                                 varOption, varOption);
 184                         if(!workbook) return env->NewStringUTF("File could not be opened.");
 185
 186                 } else {
 187
 188                 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creating file.\r\n", name.c_str());
 189
 190                         workbook = application->Workbooks->Add(varOption);
 191
 192                         if(!workbook) return env->NewStringUTF("New workbook could not be created.");
 193
 194                         try {
 195
 196                                 Excel::_WorksheetPtr sheet = workbook->Sheets->Add();
 197                                 sheet->Name = _bstr_t(sheetName.c_str());
 198
 199                                 /*
 200                                 // Does not work for some unknown reason
 201                                 for(int i=0;i<workbook->Sheets->Count;i++) {
 202                                         Excel::_WorksheetPtr sheet = workbook->Sheets->GetItem(_variant_t(i+1));
 203                                         if(name != sheetName)
 204                                                 sheet->PutVisible(0, Excel::XlSheetVisibility::xlSheetVeryHidden);
 205                                 }
 206                                 */
 207
 208                                 workbook->SaveAs(_bstr_t(name.c_str()), varOption, varOption, varOption, varOption,
 209                                         varOption, Excel::xlExclusive, varOption, varOption, varOption, varOption, 0);
 210
 211                         } catch (_com_error& e) {
 212                                 _bstr_t bstr = _bstr_t(e.ErrorMessage());
 213                                 string s(bstr);
 214                                 _bstr_t bstr2 = _bstr_t(e.Description());
 215                                 string s2(bstr2);
 216                                 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 1 '%s' '%s'\r\n", name.c_str(), s.c_str(), s2.c_str());
 217                                 strcpy(message, s2.c_str());
 218                                 return env->NewStringUTF(message);
 219                         } catch (exception& e) {
 220                                 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 2 '%s'\r\n", name.c_str(), e.what());
 221                                 return env->NewStringUTF(e.what());
 222                         } catch (...) {
 223                                 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 3 \r\n", name.c_str());
 224                                 return env->NewStringUTF("Undefined error");
 225                         }
 226
 227
 228                 }
 229
 230         }
 231
 232         application->put_Interactive( 0, VARIANT_TRUE );
 233         application->put_Visible( 0, VARIANT_TRUE );
 234
 235         workbook->Activate();
 236
 237         SIMANTICS_DEBUG("getWorkbook(%s) searches for sheet '%s'\r\n", name.c_str(), sheetName.c_str());
 238
 239         try {
 240                 Excel::_WorksheetPtr sheet = getWorksheet(workbook, sheetName);
 241                 if(!sheet) {
 242                         SIMANTICS_DEBUG("creating sheet %s for '%s'\r\n", sheetName.c_str(), name.c_str());
 243                         sheet = workbook->Sheets->Add();
 244                         sheet->Name = _bstr_t(sheetName.c_str());
 245                 }
 246             int handle = handleCounter++;
 247         SIMANTICS_DEBUG("opened handle %d for '%s'\r\n", handle, name.c_str());
 248         handles[handle] = sheet;
 249                 ostringstream os;
 250                 os << handle;
 251                 return env->NewStringUTF(os.str().c_str());
 252
 253         } catch (_com_error& e) {
 254                 _bstr_t bstr = _bstr_t(e.ErrorMessage());
 255                 string s(bstr);
 256                 _bstr_t bstr2 = _bstr_t(e.Description());
 257                 string s2(bstr2);
 258                 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 1 '%s' '%s'\r\n", name.c_str(), s.c_str(), s2.c_str());
 259                 return env->NewStringUTF(s2.c_str());
 260         } catch (exception& e) {
 261                 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 2 '%s'\r\n", name.c_str(), e.what());
 262                 return env->NewStringUTF(e.what());
 263         } catch (...) {
 264                 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error\r\n", name.c_str());
 265                 return env->NewStringUTF("Unhandled exception.");
 266         }
 267
 268
 269         //Excel::_WorksheetPtr sheet = workbook->ActiveSheet;
 270         //if(!sheet) return -4;
 271
 272
 273 }
 274
 275 string cellName(int row, int column) {
 276
 277         string result;
 278
 279         SIMANTICS_DEBUG("cellName(%d, %d) -> ", row, column);
 280
 281         do {
 282                 char rem = column % 26;
 283                 char c = 'A' + rem;
 284                 result += c;
 285                 column -= rem;
 286                 column /= 26;
 287         } while(column);
 288
 289         ostringstream os;
 290         os << (row + 1);
 291         result += os.str();
 292
 293         SIMANTICS_DEBUG("%s\r\n", result.c_str());
 294
 295         return result;
 296
 297 }
 298
 299 /*
 300 Excel::_WorkbookPtr getWorkbook(int handle) {
 301         map<int, Excel::_WorkbookPtr>::iterator it = handles.find(handle);
 302         if(it != handles.end()) return (*it).second;
 303         else return 0;
 304 }
 305 */
 306
 307 Excel::_WorksheetPtr getWorksheet(int handle) {
 308         map<int, Excel::_WorksheetPtr>::iterator it = handles.find(handle);
 309         if(it != handles.end()) return (*it).second;
 310         else return 0;
 311 }
 312
 313 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setDouble(JNIEnv *env, jobject, jint handle, jint row, jint column, jdouble value) {
 314
 315         SIMANTICS_DEBUG("setDouble(%d, %d, %f)\r\n", row, column, value);
 316
 317         Excel::_WorksheetPtr sheet = getWorksheet(handle);
 318         if(!sheet) return -1;
 319         Excel::RangePtr range = sheet->Cells;
 320         if(!range) return -2;
 321         range->Item[row+1][column+1] = value;
 322
 323         return 0;
 324
 325 }
 326
 327 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setString(JNIEnv *env, jobject, jint handle, jint row, jint column, jstring value) {
 328
 329         try {
 330
 331                 string val = getString(env, value);
 332
 333                 SIMANTICS_DEBUG("setString(%d, %d, %s)\r\n", row, column, val.c_str());
 334
 335                 Excel::_WorksheetPtr sheet = getWorksheet(handle);
 336
 337                 SIMANTICS_DEBUG("setString2(%d, %d, %s)\r\n", row, column, val.c_str());
 338
 339                 if(!sheet) return -1;
 340
 341                 struct Range *_range = 0;
 342                 HRESULT _hr = sheet->get_Cells(&_range);
 343                 if (FAILED(_hr)) return -1;
 344                 Excel::RangePtr range = RangePtr(_range, false);
 345
 346                 SIMANTICS_DEBUG("setString3(%d, %d, %s)\r\n", row, column, val.c_str());
 347
 348                 if(!range) return -2;
 349
 350                 const char *text = val.c_str();
 351
 352                 SIMANTICS_DEBUG("setString4(%d, %d, %s)\r\n", row, column, val.c_str());
 353
 354                 Excel::RangePtr cell = range->Item[row+1][column+1];
 355                 if(!cell) return -4;
 356
 357                 SIMANTICS_DEBUG("setString5(%d, %d, %s)\r\n", row, column, val.c_str());
 358
 359                 cell->Item[1][1] = text;
 360
 361 //              cell = text;
 362
 363                 return 0;
 364
 365         } catch (_com_error& e) {
 366
 367                 _bstr_t bstr = _bstr_t(e.ErrorMessage());
 368                 string s(bstr);
 369                 _bstr_t bstr2 = _bstr_t(e.Description());
 370                 string s2(bstr2);
 371                 SIMANTICS_DEBUG("SetString error 1 '%s' '%s'\r\n", s.c_str(), s2.c_str());
 372                 return -5;
 373
 374         } catch (exception& e) {
 375
 376                 SIMANTICS_DEBUG("SetString error 2 '%s'\r\n", e.what());
 377                 return -5;
 378
 379         } catch (...) {
 380
 381                 SIMANTICS_DEBUG("SetString error\r\n");
 382                 return -5;
 383
 384         }
 385
 386 }
 387
 388 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setName(JNIEnv *env, jobject, jint handle, jint row, jint column, jstring value) {
 389
 390         string val = getString(env, value);
 391
 392         SIMANTICS_DEBUG("setName(%d, %d, %s)\r\n", row, column, val.c_str());
 393
 394         Excel::_WorksheetPtr sheet = getWorksheet(handle);
 395         if(!sheet) return -1;
 396         //sheet->c
 397         Excel::RangePtr range = sheet->GetRange(_variant_t(cellName(row, column).c_str()));
 398         if(!range) return -2;
 399         range->Name = _variant_t(val.c_str());
 400
 401
 402         //range->Item[row+1][column+1] = text;
 403         //range->Name[row+1][column+1] = text;
 404
 405         return 0;
 406
 407 }
 408
 409 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setVisible(JNIEnv *env, jobject, jint handle, jboolean value) {
 410
 411         SIMANTICS_DEBUG("setVisible(%d)\r\n", value);
 412
 413         Excel::_WorksheetPtr sheet = getWorksheet(handle);
 414         if(!sheet) return -1;
 415         Excel::_ApplicationPtr application = sheet->Application;
 416         if(!application) return -2;
 417
 418         if(value) {
 419                 application->put_Interactive( 0, VARIANT_TRUE );
 420                 application->put_Visible( 0, VARIANT_TRUE );
 421         } else {
 422                 application->put_Interactive( 0, VARIANT_FALSE );
 423                 application->put_Visible( 0, VARIANT_FALSE );
 424         }
 425
 426         return 0;
 427
 428 }
 429
 430 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_close(JNIEnv *env, jobject, jint handle) {
 431
 432         SIMANTICS_DEBUG("closing handle %d\r\n", handle);
 433
 434         try {
 435
 436                 Excel::_WorksheetPtr sheet = getWorksheet(handle);
 437                 if(!sheet) return -1;
 438
 439                 // Release this reference for Close
 440                 handles.erase(handle);
 441
 442                                         SIMANTICS_DEBUG("closing 1\r\n");
 443
 444                 struct Range *_range = 0;
 445                 HRESULT _hr = sheet->get_Cells(&_range);
 446                 if (FAILED(_hr)) return -1;
 447
 448                                         SIMANTICS_DEBUG("closing 2\r\n");
 449
 450                 Excel::_ApplicationPtr application = sheet->Application;
 451                 if(!application) return -2;
 452
 453                 Excel::_WorkbookPtr workbook = (Excel::_WorkbookPtr)sheet->Parent;
 454                 if(!workbook) return -3;
 455
 456         //      SIMANTICS_DEBUG("About to save\r\n");
 457                 HRESULT hr = workbook->Save();
 458         //      workbook->put_Saved(0, VARIANT_TRUE);
 459                 if(FAILED(hr)) {
 460                         SIMANTICS_DEBUG("Save failed with %d\r\n", hr);
 461                         return hr;
 462                 }
 463
 464                 map<int, Excel::_WorksheetPtr>::iterator it = handles.begin();
 465                 for(;it != handles.end();++it) {
 466                         Excel::_WorksheetPtr sheet2 = (*it).second;
 467                         Excel::_WorkbookPtr book = (Excel::_WorkbookPtr)sheet2->Parent;
 468                         // The book remains open
 469                         if(book->Name == workbook->Name) return 0;
 470                 }
 471
 472                 SIMANTICS_DEBUG("About to Close\r\n");
 473                 hr = workbook->Close(VARIANT_FALSE);
 474                 if(FAILED(hr)) {
 475                         SIMANTICS_DEBUG("Close failed with %d\r\n", hr);
 476                         return hr;
 477                 }
 478
 479                 long remaining = 0;
 480
 481                 application->Workbooks->get_Count(&remaining);
 482                 if(!remaining) {
 483                         application->put_Interactive( 0, VARIANT_FALSE );
 484                         application->put_Visible( 0, VARIANT_FALSE );
 485                         hr = application->Quit();
 486                         if(FAILED(hr)) {
 487                                 SIMANTICS_DEBUG("Close failed with %d\r\n", hr);
 488                                 return hr;
 489                         }
 490                 }
 491
 492         } catch (_com_error& e) {
 493                 _bstr_t bstr = _bstr_t(e.ErrorMessage());
 494                 string s(bstr);
 495                 _bstr_t bstr2 = _bstr_t(e.Description());
 496                 string s2(bstr2);
 497                 SIMANTICS_DEBUG("Close error 1 '%s' '%s'\r\n", s.c_str(), s2.c_str());
 498                 return -5;
 499         } catch (exception& e) {
 500                 SIMANTICS_DEBUG("Close error 2 '%s'\r\n", e.what());
 501                 return -5;
 502         } catch (...) {
 503                 SIMANTICS_DEBUG("Close error\r\n");
 504                 return -5;
 505         }
 506
 507
 508
 509 //      SIMANTICS_DEBUG("About to hide");
 510
 511 //      application->put_Interactive( 0, VARIANT_FALSE );
 512 //      application->put_Visible( 0, VARIANT_FALSE );
 513
 514 //      if(handles.empty())
 515 //              application->Quit();
 516
 517         return 0;
 518
 519 }