bijiaodlg.cpp

价格生成系统 热水器生产厂家价格管理、设备类型管理系统

m_combo7.GetLBText(index,m_str1[0]);
range.SetItem(COleVariant((long)9),COleVariant((long)4),COleVariant(m_str1[0]));
range.SetItem(COleVariant((long)9),COleVariant((long)7),COleVariant("15年"));
range.SetItem(COleVariant((long)10),COleVariant((long)1),COleVariant("燃值"));
index=m_combo9.GetCurSel();
m_combo9.GetLBText(u1,m_str1[0]);
range.SetItem(COleVariant((long)10),COleVariant((long)4),COleVariant(m_str1[0]));
range.SetItem(COleVariant((long)10),COleVariant((long)7),COleVariant("860kcal/kwh"));
range.SetItem(COleVariant((long)11),COleVariant((long)1),COleVariant("热效率"));
index=m_combo10.GetCurSel();
m_combo10.GetLBText(u1,m_str1[3]);
range.SetItem(COleVariant((long)11),COleVariant((long)4),COleVariant(m_str1[3]));
range.SetItem(COleVariant((long)11),COleVariant((long)7),COleVariant(m_nengxiaobi));
range.SetItem(COleVariant((long)12),COleVariant((long)1),COleVariant("能源单价"));

range.SetItem(COleVariant((long)12),COleVariant((long)4),COleVariant(Bijiao[u1].nengyuandanjia));
range.SetItem(COleVariant((long)12),COleVariant((long)7),COleVariant(m_nengyuandanjia1));

range.SetItem(COleVariant((long)14),COleVariant((long)1),COleVariant("日耗能费（元）"));

range.SetItem(COleVariant((long)14),COleVariant((long)4),COleVariant(m_rihao));
range.SetItem(COleVariant((long)14),COleVariant((long)7),COleVariant(m_rihao1));
range.SetItem(COleVariant((long)15),COleVariant((long)1),COleVariant("节省"));

range.SetItem(COleVariant((long)15),COleVariant((long)4),COleVariant(m_sheng));
range.SetItem(COleVariant((long)15),COleVariant((long)7),COleVariant(m_sheng1));
range.SetItem(COleVariant((long)16),COleVariant((long)1),COleVariant("1年能耗费合计 （元）"));

range.SetItem(COleVariant((long)16),COleVariant((long)4),COleVariant(m_1nianhao));
range.SetItem(COleVariant((long)16),COleVariant((long)7),COleVariant(m_1nianhao1));
range.SetItem(COleVariant((long)17),COleVariant((long)1),COleVariant("5年能耗费合计 （元）"));

range.SetItem(COleVariant((long)17),COleVariant((long)4),COleVariant(m_5nianhao));
range.SetItem(COleVariant((long)17),COleVariant((long)7),COleVariant(m_5nianhao1));
range.SetItem(COleVariant((long)18),COleVariant((long)1),COleVariant("8年能耗费合计 （元）"));

range.SetItem(COleVariant((long)18),COleVariant((long)4),COleVariant(m_8nianhao));
range.SetItem(COleVariant((long)18),COleVariant((long)7),COleVariant(m_8nianhao1));
range.SetItem(COleVariant((long)19),COleVariant((long)1),COleVariant("维护成本"));
index=m_combo13.GetCurSel();
m_combo13.GetLBText(index,m_str1[0]);
range.SetItem(COleVariant((long)19),COleVariant((long)4),COleVariant(m_str1[0]));
range.SetItem(COleVariant((long)19),COleVariant((long)7),COleVariant("小"));
range.SetItem(COleVariant((long)20),COleVariant((long)1),COleVariant("运行人力成本"));
index=m_combo15.GetCurSel();
m_combo15.GetLBText(index,m_str1[0]);
range.SetItem(COleVariant((long)20),COleVariant((long)4),COleVariant(m_str1[0]));
range.SetItem(COleVariant((long)20),COleVariant((long)7),COleVariant("  无"));
range.SetItem(COleVariant((long)21),COleVariant((long)1),COleVariant("污染情况"));
index=m_combo17.GetCurSel();
m_combo17.GetLBText(index,m_str1[0]);
range.SetItem(COleVariant((long)21),COleVariant((long)4),COleVariant(m_str1[0]));
range.SetItem(COleVariant((long)21),COleVariant((long)7),COleVariant(" 无"));
range.SetItem(COleVariant((long)22),COleVariant((long)1),COleVariant("外部因数影响"));
index=m_combo19.GetCurSel();
m_combo19.GetLBText(index,m_str1[0]);
range.SetItem(COleVariant((long)22),COleVariant((long)4),COleVariant(m_str1[0]));
range.SetItem(COleVariant((long)22),COleVariant((long)7),COleVariant("电价"));
range.SetItem(COleVariant((long)23),COleVariant((long)1),COleVariant("安全性"));
index=m_combo21.GetCurSel();
m_combo21.GetLBText(index,m_str1[0]);
range.SetItem(COleVariant((long)23),COleVariant((long)4),COleVariant(m_str1[0]));
range.SetItem(COleVariant((long)23),COleVariant((long)7),COleVariant("安全"));
range.SetItem(COleVariant((long)27),COleVariant((long)1),COleVariant((CString("项目：")+combo[5])));
 //设置第一个单元格字体颜色:红色
 Font font;
 range.AttachDispatch(sheet.GetCells());
 range.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal);
 font.SetColor(COleVariant((long)0xFF0000));  
 
 
 //*****
 //合并单元格的处理
 //包括判断第一个单元格是否为合并单元格，以及将第一个单元格进行合并
 /*Range unionRange;
 range.AttachDispatch(sheet.GetCells()); 
 unionRange.AttachDispatch(range.GetItem (COleVariant((long)1),COleVariant((long)1)).pdispVal );
  
 vResult=unionRange.GetMergeCells();    
 if(vResult.boolVal==-1)             //是合并的单元格    
 {
  //合并单元格的行数 
  range.AttachDispatch (unionRange.GetRows ());
  long iUnionRowNum=range.GetCount (); 
   
  //合并单元格的列数
  range.AttachDispatch (unionRange.GetColumns ());
  long iUnionColumnNum=range.GetCount ();   
  
  //合并区域的起始行，列
  long iUnionStartRow=unionRange.GetRow();       //起始行，从1开始
  long iUnionStartCol=unionRange.GetColumn();    //起始列，从1开始
  
 }
 else if(vResult.boolVal==0)   
 {//不是合并的单元格}
 
 //将第一个单元格合并成2行，3列
 range.AttachDispatch(sheet.GetCells()); 
 unionRange.AttachDispatch(range.GetItem (COleVariant((long)1),COleVariant((long)1)).pdispVal );
 unionRange.AttachDispatch(unionRange.GetResize(COleVariant((long)2),COleVariant((long)3)));
 unionRange.Merge(COleVariant((long)0));   //合并单元格
 */
 
 //*****
 //将文件保存为2.xls 

/* book.SaveAs(COleVariant("d:\\hjp.xls"),covOptional,covOptional, \
  covOptional,covOptional,covOptional,0,\
  covOptional,covOptional,covOptional,covOptional);  
*/
 book.Save();
 //*****
 //关闭所有的book，退出Excel 
book.Close(covOptional,COleVariant("OutFilename"),covOptional);
books.Close();      
app.Quit();
//释放对象 
range.ReleaseDispatch(); 
iCell.ReleaseDispatch();
sheet.ReleaseDispatch(); 
sheets.ReleaseDispatch(); 
book.ReleaseDispatch(); 
books.ReleaseDispatch(); 
app.ReleaseDispatch(); 
}	
}



void CBijiaoDlg::BijiaoExcel()
{
UpdateData(true);
for(int u1=0;u1<m_combo2.GetCount();u1++)
{m_combo2.SetCurSel(u1);
m_combo3.SetCurSel(u1);	
m_combo6.SetCurSel(u1);	
m_combo7.SetCurSel(u1);	
m_combo9.SetCurSel(u1);	
m_combo10.SetCurSel(1);	
//m_combo11.SetCurSel(u1);	
m_combo13.SetCurSel(u1);	
m_combo15.SetCurSel(u1);	
m_combo17.SetCurSel(u1);	
m_combo19.SetCurSel(u1);	
m_combo21.SetCurSel(u1);	

switch(u1)
{
case 0:
	m_richanshuiliang=a[1];
	m_rihao=a[0]/0.95/860*atof(Bijiao[0].nengyuandanjia);
	m_1nianhao=m_rihao*365;
	m_5nianhao=m_1nianhao*5;
	m_8nianhao=m_1nianhao*8;
	m_sheng1.Format("%f%%",(m_rihao-m_rihao1)/m_rihao*100);
	UpdateData(false);
	break;
case 1:m_richanshuiliang=a[1];
	m_rihao=a[1]*40/0.8/6500*atof(Bijiao[1].nengyuandanjia);
	m_1nianhao=m_rihao*365;
	m_5nianhao=m_1nianhao*5;
	m_8nianhao=m_1nianhao*8;
	m_sheng1.Format("%f%%",(m_rihao-m_rihao1)/m_rihao*100);
	UpdateData(false);
	break;
case 2:m_richanshuiliang=a[1];
	m_rihao=a[1]*40/0.85/10200*atof(Bijiao[2].nengyuandanjia);
	m_1nianhao=m_rihao*365;
	m_5nianhao=m_1nianhao*5;
	m_8nianhao=m_1nianhao*8;
	m_sheng1.Format("%f%%",(m_rihao-m_rihao1)/m_rihao*100);
	UpdateData(false);
	break;
case 3:m_richanshuiliang=a[1];
	m_rihao=a[0]/0.95/860/3*atof(Bijiao[3].nengyuandanjia);
	m_1nianhao=m_rihao*365;
	m_5nianhao=m_1nianhao*5;
	m_8nianhao=m_1nianhao*8;
	m_sheng1.Format("%f%%",(m_rihao-m_rihao1)/m_rihao*100);
	UpdateData(false);
	break;
case 4:m_richanshuiliang=a[1];
	m_rihao=a[1]*40/0.8/3800*atof(Bijiao[4].nengyuandanjia);
	m_1nianhao=m_rihao*365;
	m_5nianhao=m_1nianhao*5;
	m_8nianhao=m_1nianhao*8;
	m_sheng1.Format("%f%%",(m_rihao-m_rihao1)/m_rihao*100);
	UpdateData(false);
	break;
default:
	break;
}
//*****
 //变量定义
 _Application app;    
 Workbooks books;
 _Workbook book;
 Worksheets sheets;
 _Worksheet sheet;
 Range range;
 Range iCell;
 Range unionRange;
 CString m_str1[100];
 int index;
 LPDISPATCH lpDisp;    
 COleVariant vResult;
 COleVariant
        covTrue((short)TRUE),
        covFalse((short)FALSE),
        covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);    
 
 

 
 
 //*****
 //创建Excel 2000服务器(启动Excel)
 if(!app.CreateDispatch("Excel.Application"))
{
  AfxMessageBox("无法启动Excel服务器!");
    return;
 }
 
app.SetVisible(TRUE);          //使Excel可见
 app.SetUserControl(TRUE);      //允许其它用户控制Excel
 

 //*****   
 //打开c:\1.xls
 switch(u1)
 {
 case 0:books.AttachDispatch(app.GetWorkbooks());
 lpDisp = books.Open("d:\\Yornik2\\EXCEL文件\\电热水器.xls",      
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional );   
 break;

 case 1:books.AttachDispatch(app.GetWorkbooks());
 lpDisp = books.Open("d:\\Yornik2\\EXCEL文件\\煤    气.xls",      
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional );   
 break;

 case 2:books.AttachDispatch(app.GetWorkbooks());
 lpDisp = books.Open("d:\\Yornik2\\EXCEL文件\\燃油锅炉.xls",      
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional );   
 break;

 case 3:books.AttachDispatch(app.GetWorkbooks());
 lpDisp = books.Open("d:\\Yornik2\\EXCEL文件\\太阳能.xls",      
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional );   
 break;

 case 4:books.AttachDispatch(app.GetWorkbooks());
 lpDisp = books.Open("d:\\Yornik2\\EXCEL文件\\管道媒气.xls",      
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional );   
 break;


 default:
	 break;


 }   
 //*****
 //得到Workbook
 book.AttachDispatch(lpDisp);
 
 
 //*****
 //得到Worksheets 
 sheets.AttachDispatch(book.GetWorksheets()); 
 
 
 //*****
 //得到当前活跃sheet
 //如果有单元格正处于编辑状态中，此操作不能返回，会一直等待
 lpDisp=book.GetActiveSheet();
 sheet.AttachDispatch(lpDisp); 
  


 //读取已经使用区域的信息，包括已经使用的行数、列数、起始行、起始列

 Range usedRange;
 usedRange.AttachDispatch(sheet.GetUsedRange());
 range.AttachDispatch(usedRange.GetRows());
 long iRowNum=range.GetCount();                   //已经使用的行数
 
 range.AttachDispatch(usedRange.GetColumns());
 long iColNum=range.GetCount();                   //已经使用的列数
  
 long iStartRow=usedRange.GetRow();               //已使用区域的起始行，从1开始
 long iStartCol=usedRange.GetColumn();            //已使用区域的起始列，从1开始

 //读取第一个单元格的值
range.AttachDispatch(sheet.GetCells()); 
 range.AttachDispatch(range.GetItem (COleVariant((long)1),COleVariant((long)1)).pdispVal );
vResult =range.GetValue();
 CString str;
 if(vResult.vt == VT_BSTR)       //字符串
 {
  str=vResult.bstrVal;
 }
 else if (vResult.vt==VT_R8)     //8字节的数字 
 {
  str.Format("%f",vResult.dblVal);
 }
 else if(vResult.vt==VT_DATE)    //时间格式
 {
  SYSTEMTIME st;