Exemplo n.º 1
0
CString CMyExcel::GetItemText(long Row,long Col)
{
	CString strValue=_T("");
	long lRow=0,lCol=0;
	lRow=GetRowS();
	lCol=GetColS();
	if(Row>lRow ||Col>lCol)
	{
		CString strText;
		strText.Format("由于(%d,%d)已经超过了(%d,%d)的范围,所以返回空值"
			,Row,Col,lRow,lCol,Row,Col);
		AfxMessageBox(_T(strText),MB_OK|MB_ICONERROR);
		return strValue;
	}
	VARIANT lpDisp=MyRange.GetItem(_variant_t(Row), _variant_t(Col));
	Range rgRgeValue;
	rgRgeValue.AttachDispatch(lpDisp.pdispVal, TRUE);
	_variant_t vtVal = rgRgeValue.GetValue();
	if (vtVal.vt == VT_EMPTY)
	{
		rgRgeValue.ReleaseDispatch();
		strValue = _T("");
		rgRgeValue.ReleaseDispatch();
		return strValue;
	}
	vtVal.ChangeType(VT_BSTR);
	strValue= vtVal.bstrVal;
	rgRgeValue.ReleaseDispatch();
	return strValue;
}
void CXunJianDlg::OnOK() 
{
	try{
	pList= (CListBox *)GetDlgItem(IDC_HOSTLIST);
	/*if( pList->GetTextLen(0)>15 || pList->GetTextLen(0)<7 )
	{
		MessageBox("主机列表文件未加载,请重新选择!");
		return;
	}*/

	_GUID clsid;
	IUnknown *pUnk;
	IDispatch *pDisp;
	LPDISPATCH lpDisp;

	_Application app;
	Workbooks xj_books;
	_Workbook xj_book;
	Worksheets xj_sheets;
	_Worksheet xj_sheet;
	Range range;
	Range unionRange;
	Range cols;

	Font font;
//	COleVariant background;

	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

	::CLSIDFromProgID(L"Excel.Application",&clsid); // from registry
	if(GetActiveObject(clsid, NULL,&pUnk) == S_OK)
	{
		VERIFY(pUnk->QueryInterface(IID_IDispatch,(void**) &pDisp) == S_OK);
		app.AttachDispatch(pDisp);
		pUnk->Release();
	} 
	else
	{  
		if(!app.CreateDispatch("Excel.Application"))
		{
			MessageBox("Excel program not found");     
			app.Quit();     
			return;
		}
	}

	xj_books=app.GetWorkbooks();
	xj_book=   xj_books.Add(covOptional);
	xj_sheets= xj_book.GetSheets();
	xj_sheet=  xj_sheets.GetItem(COleVariant((short)1));

	int i;
	Range item;
	range=xj_sheet.GetRange(COleVariant("A1"),COleVariant("F1"));
	for(i= 0; i < 6; i++)
	{
		item.AttachDispatch(range.GetItem(COleVariant((long)1),COleVariant((long)i+1)).pdispVal);
		item.SetValue2(COleVariant(PROJ[i]));
	}  //描绘第一行目录

	//range=xj_sheet.GetRange(COleVariant("A1"),COleVariant("F1"));
	lpDisp=range.GetInterior();
	Interior   cellinterior;
	cellinterior.AttachDispatch(lpDisp);
	cellinterior.SetColor(COleVariant((long)0xc0c0c0));  //设置背景色为灰色
	cellinterior.ReleaseDispatch();
	//range=xj_sheet.GetRange(COleVariant("A1"),COleVariant("F1"));
	range.SetHorizontalAlignment(COleVariant((long)-4108)); //全部居中
	Borders bord;
	bord=range.GetBorders();
	bord.SetLineStyle(COleVariant((short)1));  //设置边框
	//range=xj_sheet.GetRange(COleVariant("A1"),COleVariant("F1"));
	cols=range.GetEntireColumn();
	cols.AutoFit();  //自动调整

/**************************表格初始绘画完成************************************/

	long usedRowNum; //行计数
	CString handleFile;
	CString hostFileName,hostip;
	bool error = false;
	CString infos,info;
	ExcelFile excelFile;
	ReadTxt xj_txt;
	xj_HostCount=pList->GetCount();
	for(int n_host=0;n_host<xj_HostCount;n_host++)  //主循环,一个文件一次循环。
	{		
		pList->GetText(n_host,hostFileName);
		hostip = hostFileName;
		handleFile = hostFileName + _T(" 正在处理...");
		pList->DeleteString(n_host);
		pList->InsertString(n_host,handleFile);
		pList->SetCurSel(n_host);
		pList->UpdateWindow();

		hostFileName = xj_FilePath + hostFileName;
		hostFileName += _T(".txt");
		CStdioFile hostFile;
		if(!hostFile.Open(hostFileName,CFile::modeRead,0))
		{  //记录不存在文件名
			handleFile.Replace("正在处理...","失败!");
			error = true;
			pList->DeleteString(n_host);
			pList->InsertString(n_host,handleFile);
			pList->UpdateWindow();
			continue;
		}
		usedRowNum = excelFile.GetRowCount(xj_sheet);
		range.AttachDispatch(xj_sheet.GetCells());

		//info.Format( _T("%d"), n_host+1);
		info = xj_txt.ReadHostName(&hostFile,COMMAND[0],COMMAND[1]);  //获取节点名称
		range.SetItem(COleVariant(usedRowNum+1),COleVariant(long(1)),COleVariant(info));

		int portCount = 0;   //端口数目,不包括7/1
		CString nSend, nRecv;
		float n_Send,n_Recv;

		while(hostFile.ReadString(info))
			if(info.Find( COMMAND[4]) > -1) break;
		while( hostFile.ReadString(info) && info.Find( "[local]" ) == -1 )  //端口号和流量
		{
			if( info.Find( "/" ) == -1 || info.Find( "7/1" ) > -1 ) continue;

			info.Replace( "ethernet","");
			info = _T("'") + info;
			infos = info;

			while( hostFile.ReadString(info) )
				if( info.Find( "send bit rate" ) > -1 ) break;
			nSend = info.Mid( 60 );
			hostFile.ReadString(info);
			nRecv = info.Mid( 60 );
			nSend.Trim();
			nRecv.Trim();
			n_Send = (float)atof(nSend);
			n_Recv = (float)atof(nRecv);

			if( n_Send < 1000 && n_Recv < 1000 ) continue;
			portCount++;
			range.SetItem(COleVariant(usedRowNum+portCount),COleVariant(long(2)),COleVariant(infos.Trim()));
			range.SetItem(COleVariant(usedRowNum+portCount),COleVariant(long(4)),COleVariant((n_Send>n_Recv)?nSend:nRecv));
		}

		hostFile.SeekToBegin();
		infos = xj_txt.ReadLine(&hostFile,"ubscriber Address");  //历史在线最大用户数
		if( infos == _T("") ) info = _T("0");
		else
		{
			int token = 0;
			for(i = 0; i < 5 ; i++) info = infos.Tokenize(" ",token);
		}
		range.SetItem(COleVariant(usedRowNum+1),COleVariant(long(6)),COleVariant(info.Trim()));  

		hostFile.Close();

		if(portCount > 1)
		{
			unionRange.AttachDispatch(range.GetItem(COleVariant(usedRowNum+1),COleVariant((long)1)).pdispVal);
			unionRange.AttachDispatch(unionRange.GetResize(COleVariant((long)portCount),COleVariant((long)1)));
			unionRange.Merge(COleVariant((long)0));  //节点名称单元格合并

			unionRange.AttachDispatch(range.GetItem(COleVariant(usedRowNum+1),COleVariant((long)6)).pdispVal);
			unionRange.AttachDispatch(unionRange.GetResize(COleVariant((long)portCount),COleVariant((long)1)));
			unionRange.Merge(COleVariant((long)0)); 			
			//历史最大用户数合并
		}

		unionRange.AttachDispatch(range.GetItem(COleVariant(usedRowNum+1),COleVariant((long)1)).pdispVal);
		unionRange.AttachDispatch(unionRange.GetResize(COleVariant((long)portCount),COleVariant((long)6)));
		unionRange.SetRowHeight(COleVariant(13.5));
		bord = unionRange.GetBorders();
		bord.SetLineStyle(COleVariant((short)1));  //设置边框



		handleFile.Replace("正在处理...","已完成");
		pList->DeleteString(n_host);
		pList->InsertString(n_host,handleFile);
		pList->UpdateWindow();
	}

	CTime time;
	time = time.GetCurrentTime();
	infos = time.Format("%Y%m%d%H%M%S");  //time.Format();
	info = _T("巡检报表") + infos + _T(".xlsx");
	info = xj_FilePath + info;
	info.Replace("\\\\","\\");

	xj_book.SaveAs(COleVariant(info),covOptional,covOptional,covOptional,covOptional,covOptional,0,covOptional,covOptional,covOptional,covOptional,covOptional);
	
	if(error == true )
	{
		MessageBox("巡检报表已完成,已保存到\r\n" + info + "\r\n有文件打开错误,点击\"确定\"返回查看","有文件打开错误!",MB_OK|MB_ICONWARNING);
		app.Quit();
	}
	else 
	{
		if(MessageBox("巡检报表已完成,已保存到\r\n" + info + "\r\n点击\"确定\"打开文件查看","生成报表完成",MB_OKCANCEL) == IDOK)
		{
			app.SetVisible(TRUE);
			app.SetUserControl(TRUE);
		}
		else app.Quit();
	}
}
catch (CFileException* e)
    {
        e->ReportError();
        e->Delete();
    }
	//CDialog::OnOK();
}
void CBatchSNDlg::OnBnClickedCalibrationPointReport()
{
    CString  Product_Head_File_Name;
    CString strFilter;
    CString strFilename;
    CString strTemp;
    CString RegisterName;
    CString RegisterID;
    CString logstr;
    _variant_t  temp_variant;
    _Application app;    
    Workbooks books;
    _Workbook book;
    Worksheets sheets;
    _Worksheet sheet;
    Range range;
    Range rgMyRge1, rgMyRge2; 	
    COleVariant covTrue((short)TRUE), covFalse((short)FALSE), covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
   
     int CurrentRow;


    if (!app.CreateDispatch(_T("Excel.Application"),NULL)) 
    { 
        AfxMessageBox(_T("Create Excel false!")); 
        return;
    } 
    //遍历所有行  
    strFilename=g_strExePth+_T("Calibration Sensor Report.xls");

    books.AttachDispatch(app.GetWorkbooks()); 
    book.AttachDispatch(books.Add(_variant_t(strFilename)));

    sheets.AttachDispatch(book.GetWorksheets());	
    sheet.AttachDispatch(sheets.GetItem(_variant_t("Sheet1")));

    range.AttachDispatch(sheet.GetCells()); 
    CString ContentData;
    CTime time = CTime::GetCurrentTime();
   
    ContentData.Format(_T("Calibration Date"));
    range.SetItem(_variant_t((long)(1)),_variant_t((long)(1)),_variant_t(ContentData));
   
    ContentData.Format(_T("%d-%d-%d"),time.GetYear(),time.GetMonth(),time.GetDay());
    range.SetItem(_variant_t((long)(1)),_variant_t((long)(3)),_variant_t(ContentData));
 
    range.AttachDispatch(sheet.GetCells());
 
    for (int i=0;i<(int)m_modbus_IDs.size();i++)
    {  
        if (!m_modbus_IDs.at(i).Result)
        {
        continue;
        }
        CurrentRow = 3 + 7*i;
        unsigned short databuffer[30];
        Read_Multi(m_modbus_IDs.at(i).ModbusID,&databuffer[0],2000,30);
        ContentData = _T("Serial Number");
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(1)),_variant_t(ContentData));
        ContentData.Format(_T("%d"),databuffer[2027 - 2000]);
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(2)),_variant_t(ContentData));

        ContentData = _T("Points");
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(3)),_variant_t(ContentData));
        ContentData.Format(_T("%d"),databuffer[2003 - 2000]);
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(4)),_variant_t(ContentData));
         ++CurrentRow;
        ContentData = _T("Temperature");
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(1)),_variant_t(ContentData));
        ContentData.Format(_T("%0.1f"),((float)((short)databuffer[2000 - 2000]))/10);
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(2)),_variant_t(ContentData));

        ContentData = _T("Humidity");
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(3)),_variant_t(ContentData));
        ContentData.Format(_T("%0.1f"),((float)((short)databuffer[2001 - 2000]))/10);
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(4)),_variant_t(ContentData));

        
        ++CurrentRow;
        ContentData = _T("RH");
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(1)),_variant_t(ContentData));

        for (int j = 0;j<10;j++)
        {
            ContentData.Format(_T("%0.1f"),((float)((short)databuffer[2004 - 2000 + 2*j]))/10);
            range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(2+j)),_variant_t(ContentData));
        }
        ++CurrentRow;
        ContentData = _T("Frequency");
        range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(1)),_variant_t(ContentData));
        for (int j = 0;j<10;j++)
        {
            ContentData.Format(_T("%d"),databuffer[2005 - 2000 + 2*j]);
            range.SetItem(_variant_t((long)(CurrentRow)),_variant_t((long)(2+j)),_variant_t(ContentData));
        } 
 

    }

    AfxMessageBox(_T("Export Completely"));

    app.SetVisible(true); 
    range.ReleaseDispatch(); 
    sheet.ReleaseDispatch(); 
    sheets.ReleaseDispatch(); 
    book.ReleaseDispatch(); 
    books.ReleaseDispatch();
    app.ReleaseDispatch(); 
}