9 #import "c:/program files (x86)/common files/microsoft shared/office12/mso.dll" \
10 rename( "RGB", "MSORGB" )
12 using namespace Office;
14 #import "c:/program files (x86)/common files/microsoft shared/vba/vba6/vbe6ext.olb" \
16 using namespace VBIDE;
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" )
26 //#define SIMANTICS_DEBUG(...) \
27 //{ FILE *fp = fopen("d:/excel.log", "ab"); fprintf(fp, __VA_ARGS__); fflush(fp); fclose(fp); }
29 #define SIMANTICS_DEBUG(...)
32 using namespace Excel;
34 map<int, Excel::_WorksheetPtr> handles;
35 static int handleCounter = 1;
37 void replace(std::string &s, const std::string &s1, const std::string &s2) {
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);
49 boolean getString(JNIEnv *env, jstring str, char *target) {
52 const char *chars = env->GetStringUTFChars(str, &iscopy);
53 if(!chars) return false;
55 strcpy(target, chars);
57 env->ReleaseStringUTFChars(str, chars);
63 string getString(JNIEnv *env, jstring str) {
66 const char *chars = env->GetStringUTFChars(str, &iscopy);
68 string result = chars;
69 env->ReleaseStringUTFChars(str, chars);
74 Excel::_ApplicationPtr getApplication(bool create) {
76 Excel::_ApplicationPtr application;
79 HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
84 if (!SUCCEEDED(application.GetActiveObject(clsid)))
87 hr = application.CreateInstance(clsid);
96 application->EnableEvents = VARIANT_FALSE;
102 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_init(JNIEnv *env, jobject) {
104 SIMANTICS_DEBUG("Excel init\r\n");
106 HRESULT hr = ::OleInitialize(NULL);
112 Excel::_ApplicationPtr application = getApplication(true);
113 if(!application) return -1;
114 bstr_t version = application->GetVersion(0);
116 os << version << endl;
117 SIMANTICS_DEBUG("Init successful with version %s\r\n", os.str().c_str());
119 application->put_Interactive( 0, VARIANT_FALSE );
120 application->put_Visible( 0, VARIANT_FALSE );
127 Excel::_WorkbookPtr getWorkbook(const Excel::_ApplicationPtr &application, const string &fileName) {
129 SIMANTICS_DEBUG("getWorkbook(%s)\r\n", fileName.c_str());
131 for(int i=0;i<application->Workbooks->Count;i++) {
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;
144 Excel::_WorksheetPtr getWorksheet(const Excel::_WorkbookPtr workbook, const string &sheetName) {
146 SIMANTICS_DEBUG("getWorksheet(%s)\r\n", sheetName.c_str());
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;
161 JNIEXPORT jstring JNICALL Java_org_simantics_excel_Excel_open(JNIEnv *env, jobject, jstring fileName, jstring sheetName_) {
163 string name = getString(env, fileName);
164 string sheetName = getString(env, sheetName_);
166 _variant_t varOption( (long) DISP_E_PARAMNOTFOUND, VT_ERROR );
168 Excel::_ApplicationPtr application = getApplication(true);
169 if(!application) return env->NewStringUTF("Excel application could not be started.");
171 Excel::_WorkbookPtr workbook = getWorkbook(application, name);
174 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Opening.\r\n", name.c_str());
176 FILE *fp = fopen(name.c_str(), "r");
179 SIMANTICS_DEBUG("getWorkbook(%s) book was found. Opening file.\r\n", name.c_str());
182 workbook = application->Workbooks->OpenXML(_bstr_t(name.c_str()),
183 varOption, varOption);
184 if(!workbook) return env->NewStringUTF("File could not be opened.");
188 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creating file.\r\n", name.c_str());
190 workbook = application->Workbooks->Add(varOption);
192 if(!workbook) return env->NewStringUTF("New workbook could not be created.");
196 Excel::_WorksheetPtr sheet = workbook->Sheets->Add();
197 sheet->Name = _bstr_t(sheetName.c_str());
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);
208 workbook->SaveAs(_bstr_t(name.c_str()), varOption, varOption, varOption, varOption,
209 varOption, Excel::xlExclusive, varOption, varOption, varOption, varOption, 0);
211 } catch (_com_error& e) {
212 _bstr_t bstr = _bstr_t(e.ErrorMessage());
214 _bstr_t bstr2 = _bstr_t(e.Description());
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());
223 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 3 \r\n", name.c_str());
224 return env->NewStringUTF("Undefined error");
232 application->put_Interactive( 0, VARIANT_TRUE );
233 application->put_Visible( 0, VARIANT_TRUE );
235 workbook->Activate();
237 SIMANTICS_DEBUG("getWorkbook(%s) searches for sheet '%s'\r\n", name.c_str(), sheetName.c_str());
240 Excel::_WorksheetPtr sheet = getWorksheet(workbook, sheetName);
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());
246 int handle = handleCounter++;
247 SIMANTICS_DEBUG("opened handle %d for '%s'\r\n", handle, name.c_str());
248 handles[handle] = sheet;
251 return env->NewStringUTF(os.str().c_str());
253 } catch (_com_error& e) {
254 _bstr_t bstr = _bstr_t(e.ErrorMessage());
256 _bstr_t bstr2 = _bstr_t(e.Description());
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());
264 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error\r\n", name.c_str());
265 return env->NewStringUTF("Unhandled exception.");
269 //Excel::_WorksheetPtr sheet = workbook->ActiveSheet;
270 //if(!sheet) return -4;
275 string cellName(int row, int column) {
279 SIMANTICS_DEBUG("cellName(%d, %d) -> ", row, column);
282 char rem = column % 26;
293 SIMANTICS_DEBUG("%s\r\n", result.c_str());
300 Excel::_WorkbookPtr getWorkbook(int handle) {
301 map<int, Excel::_WorkbookPtr>::iterator it = handles.find(handle);
302 if(it != handles.end()) return (*it).second;
307 Excel::_WorksheetPtr getWorksheet(int handle) {
308 map<int, Excel::_WorksheetPtr>::iterator it = handles.find(handle);
309 if(it != handles.end()) return (*it).second;
313 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setDouble(JNIEnv *env, jobject, jint handle, jint row, jint column, jdouble value) {
315 SIMANTICS_DEBUG("setDouble(%d, %d, %f)\r\n", row, column, value);
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;
327 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setString(JNIEnv *env, jobject, jint handle, jint row, jint column, jstring value) {
331 string val = getString(env, value);
333 SIMANTICS_DEBUG("setString(%d, %d, %s)\r\n", row, column, val.c_str());
335 Excel::_WorksheetPtr sheet = getWorksheet(handle);
337 SIMANTICS_DEBUG("setString2(%d, %d, %s)\r\n", row, column, val.c_str());
339 if(!sheet) return -1;
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);
346 SIMANTICS_DEBUG("setString3(%d, %d, %s)\r\n", row, column, val.c_str());
348 if(!range) return -2;
350 const char *text = val.c_str();
352 SIMANTICS_DEBUG("setString4(%d, %d, %s)\r\n", row, column, val.c_str());
354 Excel::RangePtr cell = range->Item[row+1][column+1];
357 SIMANTICS_DEBUG("setString5(%d, %d, %s)\r\n", row, column, val.c_str());
359 cell->Item[1][1] = text;
365 } catch (_com_error& e) {
367 _bstr_t bstr = _bstr_t(e.ErrorMessage());
369 _bstr_t bstr2 = _bstr_t(e.Description());
371 SIMANTICS_DEBUG("SetString error 1 '%s' '%s'\r\n", s.c_str(), s2.c_str());
374 } catch (exception& e) {
376 SIMANTICS_DEBUG("SetString error 2 '%s'\r\n", e.what());
381 SIMANTICS_DEBUG("SetString error\r\n");
388 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setName(JNIEnv *env, jobject, jint handle, jint row, jint column, jstring value) {
390 string val = getString(env, value);
392 SIMANTICS_DEBUG("setName(%d, %d, %s)\r\n", row, column, val.c_str());
394 Excel::_WorksheetPtr sheet = getWorksheet(handle);
395 if(!sheet) return -1;
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());
402 //range->Item[row+1][column+1] = text;
403 //range->Name[row+1][column+1] = text;
409 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setVisible(JNIEnv *env, jobject, jint handle, jboolean value) {
411 SIMANTICS_DEBUG("setVisible(%d)\r\n", value);
413 Excel::_WorksheetPtr sheet = getWorksheet(handle);
414 if(!sheet) return -1;
415 Excel::_ApplicationPtr application = sheet->Application;
416 if(!application) return -2;
419 application->put_Interactive( 0, VARIANT_TRUE );
420 application->put_Visible( 0, VARIANT_TRUE );
422 application->put_Interactive( 0, VARIANT_FALSE );
423 application->put_Visible( 0, VARIANT_FALSE );
430 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_close(JNIEnv *env, jobject, jint handle) {
432 SIMANTICS_DEBUG("closing handle %d\r\n", handle);
436 Excel::_WorksheetPtr sheet = getWorksheet(handle);
437 if(!sheet) return -1;
439 // Release this reference for Close
440 handles.erase(handle);
442 SIMANTICS_DEBUG("closing 1\r\n");
444 struct Range *_range = 0;
445 HRESULT _hr = sheet->get_Cells(&_range);
446 if (FAILED(_hr)) return -1;
448 SIMANTICS_DEBUG("closing 2\r\n");
450 Excel::_ApplicationPtr application = sheet->Application;
451 if(!application) return -2;
453 Excel::_WorkbookPtr workbook = (Excel::_WorkbookPtr)sheet->Parent;
454 if(!workbook) return -3;
456 // SIMANTICS_DEBUG("About to save\r\n");
457 HRESULT hr = workbook->Save();
458 // workbook->put_Saved(0, VARIANT_TRUE);
460 SIMANTICS_DEBUG("Save failed with %d\r\n", hr);
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;
472 SIMANTICS_DEBUG("About to Close\r\n");
473 hr = workbook->Close(VARIANT_FALSE);
475 SIMANTICS_DEBUG("Close failed with %d\r\n", hr);
481 application->Workbooks->get_Count(&remaining);
483 application->put_Interactive( 0, VARIANT_FALSE );
484 application->put_Visible( 0, VARIANT_FALSE );
485 hr = application->Quit();
487 SIMANTICS_DEBUG("Close failed with %d\r\n", hr);
492 } catch (_com_error& e) {
493 _bstr_t bstr = _bstr_t(e.ErrorMessage());
495 _bstr_t bstr2 = _bstr_t(e.Description());
497 SIMANTICS_DEBUG("Close error 1 '%s' '%s'\r\n", s.c_str(), s2.c_str());
499 } catch (exception& e) {
500 SIMANTICS_DEBUG("Close error 2 '%s'\r\n", e.what());
503 SIMANTICS_DEBUG("Close error\r\n");
509 // SIMANTICS_DEBUG("About to hide");
511 // application->put_Interactive( 0, VARIANT_FALSE );
512 // application->put_Visible( 0, VARIANT_FALSE );
514 // if(handles.empty())
515 // application->Quit();