示例#1
0
extern "C" BOOL KTAPI KTExcelStatus()
{
    Excel::_ApplicationPtr app;

    if ( FAILED(app.CreateInstance( _T("Excel.Application")))) {
        return FALSE;
    }

    app->Quit( );
    return TRUE;
}
void CreateExcelSpreadsheet(std::map<std::wstring, std::wstring> &mapProperties)
{
	CoInitialize(nullptr);
	auto aShutdown = std::experimental::make_scope_exit([]() ->void
	{
		CoUninitialize();
	});

	//
	// Make sure Excel is present...
	CLSID clsid;
	HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
	if(FAILED(hr))
	{
		MessageBoxW(HWND_TOP, L"Unable to find Excel.Application's CLSID.", L"Excel unavailable...", MB_TOPMOST | MB_ICONERROR);
		return;
	}

	Excel::_ApplicationPtr pXLApp;
	hr = pXLApp.CreateInstance(__uuidof(Excel::Application));
	if(FAILED(hr))
	{
		MessageBoxW(HWND_TOP, L"Unable to connect with Excel.", L"Excel unavailable...", MB_TOPMOST | MB_ICONERROR);
		return;
	}

	std::vector<std::wstring> vwstrInputFiles;
	if(false == ObtainInputFiles(vwstrInputFiles))
	{
		return;
	}

	//
	// Sort input files by nozzle diameter
	SortInputFiles(vwstrInputFiles);

	// Make Excel invisible. (i.e. Application.Visible = 0)
	pXLApp->Visible[0] = VARIANT_TRUE;

	// Create a new Excel Workbook. (i.e. Application.Workbooks.Add)
	Excel::WorkbooksPtr pXLBooks = pXLApp->Workbooks;
	_ASSERT(pXLBooks);
	if(nullptr == pXLBooks)
	{
		return;
	}

	Excel::_WorkbookPtr pXLBook = pXLBooks->Add();
	_ASSERT(pXLBook);
	if(nullptr == pXLBook)
	{
		return;
	}
	pXLApp->ActiveWindow->PutWindowState(Excel::xlMaximized);

	std::vector<std::wstring> vTabnames;
	bool bFirst = true;
	for(auto wstrFile : vwstrInputFiles)
	{
		std::array<wchar_t, 1024> wzFilename;
		_wsplitpath_s(wstrFile.c_str(), nullptr, 0, nullptr, 0, &wzFilename[0], wzFilename.size(), nullptr, 0);

		boost::property_tree::ptree tree;
		std::filebuf file;
		if(false == file.open(wstrFile.c_str(), std::ios::in))
		{
			continue;
		}
		std::istream inputStream(&file);
		boost::property_tree::xml_parser::read_xml(inputStream, tree);
		std::string strPropellantName(tree.get("Document.MotorData.Propellant", "-"));
		printf("NAME: %hs\n", strPropellantName.c_str());

		const std::string strMetric(tree.get("Document.MotorData.Metric", "False"));
		bool bMetric = (strMetric == "True");

		double dSelectedAvgStartTime = tree.get("Document.MotorData.SelectedAvgStartTime", 0.0f);
		double dSelectedAvgEndTime = tree.get("Document.MotorData.SelectedAvgEndTime", 0.0f);

		auto motorData = tree.get_child("Document.MotorData");
		std::vector<std::tuple<double, double, double, double, double> > vReadings;
		double dLastTime = dSelectedAvgStartTime;

		for(auto data : motorData)
		{
			if(data.first != "Item")
			{
				continue;
			}
			double dTime = data.second.get<double>("Time", -1.0f);
			double dThrust = data.second.get<double>("Thrust", -1.0f);
			double dPressure = data.second.get<double>("Pressure", -1.0f);
			//
			// Skip readings that were "selected" out in the TC Logger program
			// Useful for example to skip over an ignition spike...
			if(dSelectedAvgEndTime > 0.0f)
			{
				if(dTime < dSelectedAvgStartTime ||
					dTime > dSelectedAvgEndTime)
				{
					continue;
				}
			}
			double dTimeDelta = dTime - dLastTime;
			dLastTime = dTime;
			double dThrustElementN = bMetric ? (dTimeDelta * dThrust) : (dTimeDelta * (dThrust * g_dNewtonsPerPound));
			vReadings.push_back(std::make_tuple(dTime, dThrust, dPressure, dTimeDelta, dThrustElementN));
		}

		if(bFirst)
		{
			bFirst = false;
		}
		else
		{
			//
			// Create a new worksheet
			Excel::SheetsPtr pWorksheets = pXLBook->Worksheets;
			pWorksheets->Add();		// Becomes the active sheet...
		}

		//
		// Generate a workbook with data and a chart marking the 10% line, etc.
		// If successful and there are at least 3 files we will generate a sheet with a & n and average ISP
		std::wstring sanitizedFilename(&wzFilename[0]);
		SanitizeStringName(sanitizedFilename);
		if(true == ProduceExcelWorkbook(pXLBook, sanitizedFilename, strPropellantName, bMetric, tree, vReadings, mapProperties))
		{
			vTabnames.push_back(sanitizedFilename);
		}
	}

	//
	// If we have three or more sets of data we can generate an a & n:
	if(vTabnames.size() > 2)
	{
		Excel::SheetsPtr pWorksheets = pXLBook->Worksheets;
		pWorksheets->Add();		// Becomes the active sheet...
		ProduceAandNTab(pXLBook, vTabnames);
	}
	return;
}
示例#3
0
DWORD WINAPI AutomateExcelByImport(LPVOID lpParam)
{
	// 在当前线程上初始化COM类库并通过调用CoInitializeEx或CoInitialize方法确认
    // 并发模型为单线程单元(STA)。
	// [-或者-] CoInitialize(NULL);
	// [-或者-] CoCreateInstance(NULL);
	CoInitializeEx(NULL, COINIT_APARTMENTTHREADED);

	try
	{

		/////////////////////////////////////////////////////////////////////
	    // 使用#import指令和智能指针创建Excel.Application COM对象

		// 选择1) 使用智能指针构造函数创建对象。
		// _ApplicationPtr 为原接口名称, _Application加上"Ptr"后缀。

		// Excel::_ApplicationPtr spXlApp(
		//	__uuidof(Excel::Application)	// 组件的CLSID 
		//	);

		// [-或者-]

		// 选择2) 使用智能指针函数CreateInstance创建对象
		Excel::_ApplicationPtr spXlApp;
		HRESULT hr = spXlApp.CreateInstance(__uuidof(Excel::Application));
		if (FAILED(hr))
		{
			wprintf(L"CreateInstance failed w/err 0x%08lx\n", hr);
			return 1;
		}

		_putws(L"Excel.Application is started");


		/////////////////////////////////////////////////////////////////////
		// 令Excel不可见(如: Application.Visible = 0)

		spXlApp->Visible[0] = VARIANT_FALSE;


		/////////////////////////////////////////////////////////////////////
		// 创建一个新的工作簿 (如: Application.Workbooks.Add)

		Excel::WorkbooksPtr spXlBooks = spXlApp->Workbooks;
		Excel::_WorkbookPtr spXlBook = spXlBooks->Add();

		_putws(L"A new workbook is created");


		/////////////////////////////////////////////////////////////////////
		// 获取处于活动状态的工作表并为其设置名称

		Excel::_WorksheetPtr spXlSheet = spXlBook->ActiveSheet;
		spXlSheet->Name = _bstr_t(L"Report");
		_putws(L"The active worksheet is renamed as Report");


		/////////////////////////////////////////////////////////////////////
		// 填充数据到工作表单元

		_putws(L"Filling data into the worksheet ...");

		// 为用户姓名构建一个5*2的安全数组
		VARIANT saNames;
		saNames.vt = VT_ARRAY | VT_VARIANT;
		{
			SAFEARRAYBOUND sab[2];
			sab[0].lLbound = 1; sab[0].cElements = 5;
			sab[1].lLbound = 1; sab[1].cElements = 2;
			saNames.parray = SafeArrayCreate(VT_VARIANT, 2, sab);

			SafeArrayPutName(saNames.parray, 1, L"John", L"Smith");
			SafeArrayPutName(saNames.parray, 2, L"Tom", L"Brown");
			SafeArrayPutName(saNames.parray, 3, L"Sue", L"Thomas");
			SafeArrayPutName(saNames.parray, 4, L"Jane", L"Jones");
			SafeArrayPutName(saNames.parray, 5, L"Adam", L"Johnson");
		}

		 // 将数组值填充到A2:B6区域(姓与名)
		// 获取区域A2:B6的Range对象

		param.vt = VT_BSTR;
		param.bstrVal = SysAllocString(L"A2:B6");
		Excel::RangePtr spXlRange = spXlSheet->Range[param];

		spXlRange->Value2 = saNames;

		// 清除数组
		VariantClear(&saNames);


		/////////////////////////////////////////////////////////////////////
		// 将工作簿保存为xlsx文件并关闭
		

		_putws(L"Save and close the workbook");

		// 确定文件名

		// 获取当前exe的目录
		wchar_t szFileName[MAX_PATH];
		if (!GetModuleDirectory(szFileName, ARRAYSIZE(szFileName)))
		{
			_putws(L"GetModuleDirectory failed");
			return 1;
		}

		// 将"Sample1.xlsx" 合并到目录
		wcsncat_s(szFileName, ARRAYSIZE(szFileName), L"Sample1.xlsx", 12);

		// 将空终止字符串转换为BSTR
		variant_t vtFileName(szFileName);

		spXlBook->SaveAs(vtFileName, Excel::xlOpenXMLWorkbook, vtMissing, 
			vtMissing, vtMissing, vtMissing, Excel::xlNoChange);

		spXlBook->Close();


		/////////////////////////////////////////////////////////////////////
		// 退出Excel应用程序(如:Application.Quit)
		 

		_putws(L"Quit the Excel application");
		spXlApp->Quit();


		/////////////////////////////////////////////////////////////////////
		// 释放COM对象
		// 

		// 为智能指针释放不必要的引用
		// ...
		// spXlApp.Release();
		// ...

	}
	catch (_com_error &err)
	{
		wprintf(L"Excel throws the error: %s\n", err.ErrorMessage());
		wprintf(L"Description: %s\n", (LPCWSTR) err.Description());
	}

	// 为该线程撤销COM
	CoUninitialize();

	return 0;
}
示例#4
0
extern "C" BOOL LoadExcelFile(const TCHAR* filename)
{

    // Load the Excel application in the background.
    Excel::_ApplicationPtr pApplication;

    if ( FAILED( pApplication.CreateInstance( _T("Excel.Application") ) ) )
    {
        Errorf( _T("Failed to initialize Excel::_Application!") );
        return FALSE;
    }

    _variant_t	varOption( (long) DISP_E_PARAMNOTFOUND, VT_ERROR );
    _ftprintf(stdout, _T("%s\n"), filename);
    if (!pApplication || !pApplication->Workbooks) {
        Errorf(_T("Workbooks is empty\n"));
        return FALSE;
    }

    Excel::_WorkbookPtr pBook = pApplication->Workbooks->Open(filename);//, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption );
    if ( pBook == NULL )
    {
        Errorf( _T("Failed to open Excel file!") );
        return FALSE;
    }
    pApplication->PutVisible(0, FALSE);

    Excel::_WorksheetPtr pSheet = pBook->Sheets->Item[1];

    if ( pSheet == NULL )
    {
        Errorf( _T("Failed to get first Worksheet!") );
        return FALSE;
    }

    Excel::RangePtr pRange = pSheet->Cells;

    for (int i = 24; i < 29; ++i) {
        for (int j = 3; j < 27; ++j) {
            pRange->Item[i][j] = j / 10 + i / 100.f;
        }
    }
#if 0
    fprintf(stdout, "2\n");
    // Load the column headers.
    Excel::RangePtr pRange = pSheet->GetRange( _bstr_t( _T("A1") ), _bstr_t( _T("Z1" ) ) );

    if ( pRange == NULL )
    {
        Errorf( _T("Failed to get header cell range( A1:Z1 )!") );
        return FALSE;
    }

    int			iColumns = 0;

    for ( int iColumn = 1; iColumn < 26; ++iColumn )
    {
        _variant_t	vItem = pRange->Item[ 1 ][ iColumn ];
        _bstr_t		bstrText( vItem );

        if ( bstrText.length( ) == 0 )
            break;

        //m_list.AddColumn( bstrText, iColumns++ );
        //_ftprintf(stdout, _T("%s\n"), bstrText);
        Errorf(bstrText);
    }

    // Load the rows (up to the first blank one).
    pRange = pSheet->GetRange( _bstr_t( _T("A2") ), _bstr_t( _T("Z16384" ) ) );

    for ( int iRow = 1; ; ++iRow )
    {
        int iColumn;
        for (  iColumn = 1; iColumn <= iColumns; ++iColumn )
        {
            _variant_t	vItem = pRange->Item[ iRow ][ iColumn ];
            _bstr_t		bstrText( vItem );

            if ( bstrText.length( ) == 0 )
                break;

            if ( iColumn == 1 )
                ;//m_list.AddItem( iRow - 1, 0, bstrText );
            else
                ;//m_list.SetItemText( iRow - 1, iColumn - 1, bstrText );
        }

        if ( iColumn == 1 )
            break;
    }

    pRange->Item[ 1 ][ 1 ] = 1234;
#endif
    // Make it all look pretty.
    //for ( int iColumn = 1; iColumn <= iColumns; ++iColumn )
    //m_list.SetColumnWidth( iColumn, LVSCW_AUTOSIZE_USEHEADER );




    // Switch off alert prompting to save as
    pApplication->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_FALSE );

    // Save the values in book.xml
    pSheet->SaveAs(_T("d:\\tesstbook.xls"));
    pApplication->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_TRUE );
    // Don't save any inadvertant changes to the .xls file.
    pBook->Close( VARIANT_FALSE );
    // And switch back on again...


    // Need to quit, otherwise Excel remains active and locks the .xls file.
    pApplication->Quit( );

    return TRUE;
}
示例#5
0
UINT CNav_ExportCustomsDlg::ExportData(LPVOID p)
{
	
	HRESULT hRes;
	Excel::_ApplicationPtr appExcel;
	hRes = appExcel.CreateInstance( _T("Excel.Application"));

	CNav_ExportCustomsDlg *Dialog;
	Dialog = (CNav_ExportCustomsDlg*)p;

	if(Dialog != NULL)
	{
		

		CString sStart, sEnd;
		Dialog->m_EndDate.GetWindowText(sEnd);
		Dialog->m_StartDate.GetWindowText(sStart);

		CString sGTD;
		Dialog->m_EdGTDNUMBER.GetWindowText(sGTD);
		COleDateTime datStart,datEnd, cDate;
		datStart.ParseDateTime(sStart);
		datEnd.ParseDateTime(sEnd);
	
		CString sConnect;
		CString sServer, sDatabase;
		sServer = sReadFromIni(_T("DB"),_T("SERVER"),_T("svbyminssq3"));
		//sWriteToIni(_T("DB"),_T("SERVER"),sServer);
		sDatabase = sReadFromIni(_T("DB"),_T("DATABASE"),_T("SHATE-M-8"));
		//sWriteToIni(_T("DB"),_T("DATABASE"),sDatabase);


		
		Excel::WorkbooksPtr ExcelBooks;
		Excel::_WorkbookPtr ExcelBook;
		Excel::_WorksheetPtr ExcelSheet;
		Excel::RangePtr range;

		
		
		

		VARIANT bTRUE;
		bTRUE.vt = 11;
		bTRUE.boolVal = TRUE;
		appExcel->Visible[0] = FALSE;
		ExcelBook= appExcel->Workbooks->Add();
		ExcelSheet = ExcelBook->Worksheets->Item[1];

		sConnect.Format(_T("DRIVER=SQL Server;SERVER=%s;UID=;WSID=%s;Trusted_Connection=Yes;DATABASE=%s;LANGUAGE=русский"),sServer,GetWinUserName(),sDatabase);
		CDatabase* dBase;
		dBase = NULL;
		try
		{
			dBase = new(CDatabase);
			dBase->SetQueryTimeout(600);
			dBase->OpenEx(sConnect,CDatabase::noOdbcDialog);
			sConnect.Format(_T("EXEC [sp_setapprole] '%s', '%s', 'none', 0, 0"),_T("$ndo$shadow"),_T("FF5EC4E40F67BD4EDF3D04F8B84364DAD0")); 
			dBase->ExecuteSQL(sConnect);
		}
		catch(CDBException *exsept)
		{
			appExcel->Visible[0] = TRUE;
			Dialog->m_stState.SetWindowTextW(exsept->m_strError);
			exsept->Delete();
			if(dBase != NULL)
			{
				if(dBase->IsOpen())
				{
					dBase->Close();
				}
				delete(dBase);
			}
			dBase = NULL;
			Dialog->m_BtOK.ShowWindow(1);
			Dialog->SecondThread = NULL;
			return 0;
		}
		CString sSQL;
		try
		{
			

			CRecordset Query(dBase);
		
			int iField;
			CDBVariant dbValue;
			Dialog->m_stState.SetWindowTextW(_T("Формирование"));
			sGTD = sGTD + _T("'");
			sSQL = _T("select distinct SIH.[Posting Date],(SIL.[TTN Series]+SIL.[TTN Number]) AS TTN,SIL.[Item No_ 2], SIH.[Bill-to Name] ");
			sSQL = sSQL + _T(" from [")+sDatabase;
			sSQL = sSQL + _T("$Sales Invoice Header] as SIH join [");
			sSQL = sSQL + sDatabase;
			sSQL = sSQL + _T("$Sales Invoice Line] as SIL on SIL.[Document No_] = SIH.[No_] and SIL.[No_] is not null and SIL.[No_] <> ''  and SIL.[TTN Series] <> '' and SIL.[TTN Number] <> '' join [");
			sSQL = sSQL + sDatabase;
			sSQL = sSQL + _T("$Custom Declaration Relation] as CDR on CDR.[Item No_] = SIL.[No_] and CDR.[Document Type] = 5 and CDR.[CD No_] = '") + sGTD;
			sSQL = sSQL + _T(" where [Sales Process Type Code] = 'Б/Н_ДОСТАВКА' and Left(CONVERT ( nchar , SIH.[Posting Date], 112),8) >= '")+ datStart.Format(_T("%Y%m%d"))+_T("'");
			sSQL = sSQL + _T(" and Left(CONVERT ( nchar , SIH.[Posting Date], 112),8) <= '")+ datEnd.Format(_T("%Y%m%d"))+_T("'");
			//sSQL = sSQL + _T(" and CDR.[CD No_] = '") + sGTD;

			CString sDat;
			int iRow;
			iRow = 1;
			sDat = _T("Дата учета продажи");
			ExcelSheet->Cells->Item[iRow,2] = sDat.AllocSysString();
			sDat = _T("Номер ТТН, ТН");
			ExcelSheet->Cells->Item[iRow,3] = sDat.AllocSysString();
			sDat = _T("Код проданного товара (код товара2)");
			ExcelSheet->Cells->Item[iRow,4] = sDat.AllocSysString();
			sDat = _T("Клиент");
			ExcelSheet->Cells->Item[iRow,5] = sDat.AllocSysString();
	

			
			Query.Open(CRecordset::snapshot,sSQL, CRecordset::readOnly);
			while(!Query.IsEOF())
			{
				iRow++;
				
				if((iRow -2) % 100 == 0)
				{
					sSQL.Format(_T("Обработанно %d"),iRow-2);
					Dialog->m_stState.SetWindowTextW(sSQL);
				}
				iField = 0;
				Query.GetFieldValue(iField, dbValue);
				sDat = GetValue(&dbValue);
				ExcelSheet->Cells->Item[iRow,2] = sDat.AllocSysString();

				iField = 1;
				Query.GetFieldValue(iField, dbValue);
				sDat = GetValue(&dbValue);
				ExcelSheet->Cells->Item[iRow,3] = sDat.AllocSysString();

				iField = 2;
				Query.GetFieldValue(iField, dbValue);
				sDat = GetValue(&dbValue);
				ExcelSheet->Cells->Item[iRow,4] = sDat.AllocSysString();

				iField = 3;
				Query.GetFieldValue(iField, dbValue);
				sDat = GetValue(&dbValue);
				ExcelSheet->Cells->Item[iRow,5] = sDat.AllocSysString();

				Query.MoveNext();
			}
			Query.Close();

		}
		catch(CDBException *exsept)
		{
			/*appExcel->Visible[0] = TRUE;
			appExcel = NULL;*/
			appExcel->Visible[0] = TRUE;
			Dialog->m_stState.SetWindowTextW(exsept->m_strError);
			Dialog->m_EdError.SetWindowTextW(sSQL);
			
			exsept->Delete();
			if(dBase != NULL)
			{
				if(dBase->IsOpen())
				{
					dBase->Close();
				}
				delete(dBase);
			}
			Dialog->m_BtOK.ShowWindow(1);
			Dialog->SecondThread = NULL;
			dBase = NULL;

			return 0;
		}


		Dialog->m_stState.SetWindowTextW(_T("Выполненно"));
		Dialog->m_BtOK.ShowWindow(1);
	}
	appExcel->Visible[0] = TRUE;
	Dialog->SecondThread = NULL;
	return 1;
}