2 //#include "stdafx.h"
\r
11 //#include <TCHAR.H>
\r
13 using namespace std;
\r
15 #import "c:/program files (x86)/common files/microsoft shared/office12/mso.dll" \
\r
16 rename( "RGB", "MSORGB" )
\r
18 using namespace Office;
\r
20 #import "c:/program files (x86)/common files/microsoft shared/vba/vba6/vbe6ext.olb" \
\r
22 using namespace VBIDE;
\r
24 #import "c:/program files (x86)/microsoft office/office12/excel.exe" \
\r
25 rename( "DialogBox", "ExcelDialogBox" ) \
\r
26 rename( "RGB", "ExcelRGB" ) \
\r
27 rename( "CopyFile", "ExcelCopyFile" ) \
\r
28 rename( "ReplaceText", "ExcelReplaceText" )
\r
32 //#define SIMANTICS_DEBUG(...) \
\r
33 //{ FILE *fp = fopen("d:/excel.log", "ab"); fprintf(fp, __VA_ARGS__); fflush(fp); fclose(fp); }
\r
35 #define SIMANTICS_DEBUG(...)
\r
37 using namespace std;
\r
38 using namespace Excel;
\r
40 map<int, Excel::_WorksheetPtr> handles;
\r
41 static int handleCounter = 1;
\r
43 void replace(std::string &s, const std::string &s1, const std::string &s2) {
\r
47 index = s.find(s1, index);
\r
48 while(index != std::string::npos) {
\r
49 s.replace(index, s1.length(), s2.c_str(), s2.length());
\r
50 index = s.find(s1, index + 1);
\r
55 boolean getString(JNIEnv *env, jstring str, char *target) {
\r
58 const char *chars = env->GetStringUTFChars(str, &iscopy);
\r
59 if(!chars) return false;
\r
61 strcpy(target, chars);
\r
63 env->ReleaseStringUTFChars(str, chars);
\r
69 string getString(JNIEnv *env, jstring str) {
\r
72 const char *chars = env->GetStringUTFChars(str, &iscopy);
\r
73 if(!chars) return 0;
\r
74 string result = chars;
\r
75 env->ReleaseStringUTFChars(str, chars);
\r
80 Excel::_ApplicationPtr getApplication(bool create) {
\r
82 Excel::_ApplicationPtr application;
\r
85 HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
\r
90 if (!SUCCEEDED(application.GetActiveObject(clsid)))
\r
93 hr = application.CreateInstance(clsid);
\r
102 application->EnableEvents = VARIANT_FALSE;
\r
104 return application;
\r
108 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_init(JNIEnv *env, jobject) {
\r
110 SIMANTICS_DEBUG("Excel init\r\n");
\r
112 HRESULT hr = ::OleInitialize(NULL);
\r
118 Excel::_ApplicationPtr application = getApplication(true);
\r
119 if(!application) return -1;
\r
120 bstr_t version = application->GetVersion(0);
\r
122 os << version << endl;
\r
123 SIMANTICS_DEBUG("Init successful with version %s\r\n", os.str().c_str());
\r
125 application->put_Interactive( 0, VARIANT_FALSE );
\r
126 application->put_Visible( 0, VARIANT_FALSE );
\r
133 Excel::_WorkbookPtr getWorkbook(const Excel::_ApplicationPtr &application, const string &fileName) {
\r
135 SIMANTICS_DEBUG("getWorkbook(%s)\r\n", fileName.c_str());
\r
137 for(int i=0;i<application->Workbooks->Count;i++) {
\r
139 Excel::_WorkbookPtr workbook = application->Workbooks->GetItem( _variant_t(i+1) );
\r
140 workbook->get_FullName(0, &fullName.GetBSTR());
\r
141 std::string fullNameStr = fullName;
\r
142 SIMANTICS_DEBUG("'%s'\r\n", fullNameStr.c_str());
\r
143 if(fullNameStr == fileName) return workbook;
\r
150 Excel::_WorksheetPtr getWorksheet(const Excel::_WorkbookPtr workbook, const string &sheetName) {
\r
152 SIMANTICS_DEBUG("getWorksheet(%s)\r\n", sheetName.c_str());
\r
154 for(int i=0;i<workbook->Sheets->Count;i++) {
\r
155 Excel::_WorksheetPtr sheet = workbook->Sheets->GetItem(_variant_t(i+1));
\r
156 std:string name = sheet->Name;
\r
157 SIMANTICS_DEBUG("'%s'\r\n", name.c_str());
\r
158 if(name == sheetName) return sheet;
\r
165 char message[1024];
\r
167 JNIEXPORT jstring JNICALL Java_org_simantics_excel_Excel_open(JNIEnv *env, jobject, jstring fileName, jstring sheetName_) {
\r
169 string name = getString(env, fileName);
\r
170 string sheetName = getString(env, sheetName_);
\r
172 _variant_t varOption( (long) DISP_E_PARAMNOTFOUND, VT_ERROR );
\r
174 Excel::_ApplicationPtr application = getApplication(true);
\r
175 if(!application) return env->NewStringUTF("Excel application could not be started.");
\r
177 Excel::_WorkbookPtr workbook = getWorkbook(application, name);
\r
180 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Opening.\r\n", name.c_str());
\r
182 FILE *fp = fopen(name.c_str(), "r");
\r
185 SIMANTICS_DEBUG("getWorkbook(%s) book was found. Opening file.\r\n", name.c_str());
\r
188 workbook = application->Workbooks->OpenXML(_bstr_t(name.c_str()),
\r
189 varOption, varOption);
\r
190 if(!workbook) return env->NewStringUTF("File could not be opened.");
\r
194 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creating file.\r\n", name.c_str());
\r
196 workbook = application->Workbooks->Add(varOption);
\r
198 if(!workbook) return env->NewStringUTF("New workbook could not be created.");
\r
202 Excel::_WorksheetPtr sheet = workbook->Sheets->Add();
\r
203 sheet->Name = _bstr_t(sheetName.c_str());
\r
206 // Does not work for some unknown reason
\r
207 for(int i=0;i<workbook->Sheets->Count;i++) {
\r
208 Excel::_WorksheetPtr sheet = workbook->Sheets->GetItem(_variant_t(i+1));
\r
209 if(name != sheetName)
\r
210 sheet->PutVisible(0, Excel::XlSheetVisibility::xlSheetVeryHidden);
\r
214 workbook->SaveAs(_bstr_t(name.c_str()), varOption, varOption, varOption, varOption,
\r
215 varOption, Excel::xlExclusive, varOption, varOption, varOption, varOption, 0);
\r
217 } catch (_com_error& e) {
\r
218 _bstr_t bstr = _bstr_t(e.ErrorMessage());
\r
220 _bstr_t bstr2 = _bstr_t(e.Description());
\r
222 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 1 '%s' '%s'\r\n", name.c_str(), s.c_str(), s2.c_str());
\r
223 strcpy(message, s2.c_str());
\r
224 return env->NewStringUTF(message);
\r
225 } catch (exception& e) {
\r
226 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 2 '%s'\r\n", name.c_str(), e.what());
\r
227 return env->NewStringUTF(e.what());
\r
229 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 3 \r\n", name.c_str());
\r
230 return env->NewStringUTF("Undefined error");
\r
238 application->put_Interactive( 0, VARIANT_TRUE );
\r
239 application->put_Visible( 0, VARIANT_TRUE );
\r
241 workbook->Activate();
\r
243 SIMANTICS_DEBUG("getWorkbook(%s) searches for sheet '%s'\r\n", name.c_str(), sheetName.c_str());
\r
246 Excel::_WorksheetPtr sheet = getWorksheet(workbook, sheetName);
\r
248 SIMANTICS_DEBUG("creating sheet %s for '%s'\r\n", sheetName.c_str(), name.c_str());
\r
249 sheet = workbook->Sheets->Add();
\r
250 sheet->Name = _bstr_t(sheetName.c_str());
\r
252 int handle = handleCounter++;
\r
253 SIMANTICS_DEBUG("opened handle %d for '%s'\r\n", handle, name.c_str());
\r
254 handles[handle] = sheet;
\r
257 return env->NewStringUTF(os.str().c_str());
\r
259 } catch (_com_error& e) {
\r
260 _bstr_t bstr = _bstr_t(e.ErrorMessage());
\r
262 _bstr_t bstr2 = _bstr_t(e.Description());
\r
264 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 1 '%s' '%s'\r\n", name.c_str(), s.c_str(), s2.c_str());
\r
265 return env->NewStringUTF(s2.c_str());
\r
266 } catch (exception& e) {
\r
267 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error 2 '%s'\r\n", name.c_str(), e.what());
\r
268 return env->NewStringUTF(e.what());
\r
270 SIMANTICS_DEBUG("getWorkbook(%s) book was not found. Creation error\r\n", name.c_str());
\r
271 return env->NewStringUTF("Unhandled exception.");
\r
275 //Excel::_WorksheetPtr sheet = workbook->ActiveSheet;
\r
276 //if(!sheet) return -4;
\r
281 string cellName(int row, int column) {
\r
285 SIMANTICS_DEBUG("cellName(%d, %d) -> ", row, column);
\r
288 char rem = column % 26;
\r
289 char c = 'A' + rem;
\r
297 result += os.str();
\r
299 SIMANTICS_DEBUG("%s\r\n", result.c_str());
\r
306 Excel::_WorkbookPtr getWorkbook(int handle) {
\r
307 map<int, Excel::_WorkbookPtr>::iterator it = handles.find(handle);
\r
308 if(it != handles.end()) return (*it).second;
\r
313 Excel::_WorksheetPtr getWorksheet(int handle) {
\r
314 map<int, Excel::_WorksheetPtr>::iterator it = handles.find(handle);
\r
315 if(it != handles.end()) return (*it).second;
\r
319 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setDouble(JNIEnv *env, jobject, jint handle, jint row, jint column, jdouble value) {
\r
321 SIMANTICS_DEBUG("setDouble(%d, %d, %f)\r\n", row, column, value);
\r
323 Excel::_WorksheetPtr sheet = getWorksheet(handle);
\r
324 if(!sheet) return -1;
\r
325 Excel::RangePtr range = sheet->Cells;
\r
326 if(!range) return -2;
\r
327 range->Item[row+1][column+1] = value;
\r
333 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setString(JNIEnv *env, jobject, jint handle, jint row, jint column, jstring value) {
\r
335 string val = getString(env, value);
\r
337 SIMANTICS_DEBUG("setString(%d, %d, %s)\r\n", row, column, val.c_str());
\r
339 Excel::_WorksheetPtr sheet = getWorksheet(handle);
\r
340 if(!sheet) return -1;
\r
341 Excel::RangePtr range = sheet->Cells;
\r
342 if(!range) return -2;
\r
344 const char *text = val.c_str();
\r
346 range->Item[row+1][column+1] = text;
\r
347 //range->Name[row+1][column+1] = text;
\r
353 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setName(JNIEnv *env, jobject, jint handle, jint row, jint column, jstring value) {
\r
355 string val = getString(env, value);
\r
357 SIMANTICS_DEBUG("setName(%d, %d, %s)\r\n", row, column, val.c_str());
\r
359 Excel::_WorksheetPtr sheet = getWorksheet(handle);
\r
360 if(!sheet) return -1;
\r
362 Excel::RangePtr range = sheet->GetRange(_variant_t(cellName(row, column).c_str()));
\r
363 if(!range) return -2;
\r
364 range->Name = _variant_t(val.c_str());
\r
367 //range->Item[row+1][column+1] = text;
\r
368 //range->Name[row+1][column+1] = text;
\r
374 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_setVisible(JNIEnv *env, jobject, jint handle, jboolean value) {
\r
376 SIMANTICS_DEBUG("setVisible(%d)\r\n", value);
\r
378 Excel::_WorksheetPtr sheet = getWorksheet(handle);
\r
379 if(!sheet) return -1;
\r
380 Excel::_ApplicationPtr application = sheet->Application;
\r
381 if(!application) return -2;
\r
384 application->put_Interactive( 0, VARIANT_TRUE );
\r
385 application->put_Visible( 0, VARIANT_TRUE );
\r
387 application->put_Interactive( 0, VARIANT_FALSE );
\r
388 application->put_Visible( 0, VARIANT_FALSE );
\r
395 JNIEXPORT jint JNICALL Java_org_simantics_excel_Excel_close(JNIEnv *env, jobject, jint handle) {
\r
397 SIMANTICS_DEBUG("closing handle %d\r\n", handle);
\r
399 Excel::_WorksheetPtr sheet = getWorksheet(handle);
\r
400 if(!sheet) return -1;
\r
401 Excel::_ApplicationPtr application = sheet->Application;
\r
402 if(!application) return -2;
\r
404 Excel::_WorkbookPtr workbook = (Excel::_WorkbookPtr)sheet->Parent;
\r
405 if(!workbook) return -3;
\r
407 // SIMANTICS_DEBUG("About to save\r\n");
\r
408 HRESULT hr = workbook->Save();
\r
409 // workbook->put_Saved(0, VARIANT_TRUE);
\r
411 SIMANTICS_DEBUG("Save failed with %d\r\n", hr);
\r
414 // SIMANTICS_DEBUG("About to AcceptAllChanges\r\n");
\r
415 // HRESULT hr = workbook->AcceptAllChanges();
\r
416 // if(FAILED(hr)) {
\r
418 //SIMANTICS_DEBUG("Close failed with %d\r\n", hr);
\r
422 // Release this reference for Close
\r
423 handles.erase(handle);
\r
425 map<int, Excel::_WorksheetPtr>::iterator it = handles.begin();
\r
426 for(;it != handles.end();++it) {
\r
427 Excel::_WorksheetPtr sheet2 = (*it).second;
\r
428 Excel::_WorkbookPtr book = (Excel::_WorkbookPtr)sheet2->Parent;
\r
429 // The book remains open
\r
430 if(book->Name == workbook->Name) return 0;
\r
433 //SIMANTICS_DEBUG("About to PutUserControl\r\n");
\r
434 //workbook->PutUserControl(VARIANT_FALSE);
\r
435 SIMANTICS_DEBUG("About to Close\r\n");
\r
436 hr = workbook->Close(VARIANT_FALSE);
\r
438 SIMANTICS_DEBUG("Close failed with %d\r\n", hr);
\r
442 long remaining = 0;
\r
444 application->Workbooks->get_Count(&remaining);
\r
446 application->put_Interactive( 0, VARIANT_FALSE );
\r
447 application->put_Visible( 0, VARIANT_FALSE );
\r
448 hr = application->Quit();
\r
450 SIMANTICS_DEBUG("Close failed with %d\r\n", hr);
\r
455 // SIMANTICS_DEBUG("About to hide");
\r
457 // application->put_Interactive( 0, VARIANT_FALSE );
\r
458 // application->put_Visible( 0, VARIANT_FALSE );
\r
460 // if(handles.empty())
\r
461 // application->Quit();
\r
468 JNIEXPORT jdouble JNICALL Java_org_simantics_excel_Excel_getDouble(JNIEnv *env, jobject, jint handle, jint row, jint column) {
\r
470 SIMANTICS_DEBUG("getDouble(%d, %d)\r\n", row, column);
\r
472 Excel::_WorksheetPtr sheet = getWorksheet(handle);
\r
473 if(!sheet) return 0.0; // TODO: NAN
\r
474 Excel::RangePtr range = sheet->Cells;
\r
475 if(!range) return 0.0; // TODO: NAN
\r
477 variant_t val = range->Item[row+1][column+1];
\r
478 if (val.vt != VT_NULL) {
\r
481 SIMANTICS_DEBUG("%f\r\n",d);
\r
484 return 0.0; // TODO: NAN
\r
487 return 0.0; // TODO: NAN
\r
491 JNIEXPORT jstring JNICALL Java_org_simantics_excel_Excel_getString(JNIEnv *env, jobject, jint handle, jint row, jint column) {
\r
493 //string val = getString(env, value);
\r
495 SIMANTICS_DEBUG("getString(%d, %d)\r\n", row, column);
\r
497 Excel::_WorksheetPtr sheet = getWorksheet(handle);
\r
498 if(!sheet) return NULL;
\r
499 Excel::RangePtr range = sheet->Cells;
\r
500 if(!range) return NULL;
\r
502 variant_t val = range->Item[row+1][column+1];
\r
503 if (val.vt != VT_NULL) { // val.vt seems to be always VT_DISPATCH.
\r
505 //this call will crash the application, if the cell is empty!
\r
506 _bstr_t bstrt = val;
\r
508 // TODO: this uses wide character (16-bit) while NewStringUTF expext regular character (8-bit), hence Java gets only the fisrt charater of the whole string.
\r
509 _stprintf(text, _T("%s"), (LPCTSTR)bstrt);
\r
510 //const char *text = bstrt;
\r
511 SIMANTICS_DEBUG("%s\r\n",text);
\r
513 // this code returns "??" for all cells.
\r
514 //BSTR bstr = val.bstrVal;
\r
515 //const char *text = _com_util::ConvertBSTRToString(bstr);
\r
517 jstring ret = env->NewStringUTF((const char*)text);
\r
524 SIMANTICS_DEBUG("%d\r\n",val.vt);
\r