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;
}
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;
}
