16,471
社区成员
发帖
与我相关
我的任务
分享
/*#import "libid:2DF8D04C-5BFA-101B-BDE5-00AA0044DE52" \
rename("RGB", "ExcelRGB") \
rename("DocumentProperties", "ExcelDocumentProperties")
// Microsoft Visual Basic for Applications Extensibility 5.3
#import "libid:0002E157-0000-0000-C000-000000000046"
#import "libid:00020813-0000-0000-C000-000000000046" \
rename("DialogBox", "ExcelDialogBox") \
rename("RGB", "ExcelRGB") \
exclude("IFont", "IPicture") \
rename("CopyFile", "ExcelCopyFile") \
rename("ReplaceText", "ExcelReplaceText") \
named_guids \
no_dual_interfaces*/
//using namespace Excel;
#include "Excel\MSO.tlh"
#include "Excel\VBE6EXT.tlh"
#include "Excel\EXCEL.tlh"
void CExcelExportDataDlg::OnCapexcel()
{
// TODO: Add your control notification handler code here
// _Application m_oExcelApp; // Excel程序
// _Worksheet m_oWorkSheet; // 工作表
// _Workbook m_oWorkBook; // 工作簿
// Workbooks m_oWorkBooks; // 工作簿集合
// Worksheets m_oWorkSheets; // 工作表集合
// Range m_oCurrRange; // 使用区域
_Application exlapp; //组件服务器的各个classes
_Workbook m_WorkBook;
Workbooks m_WorkBooks;
_Worksheet m_WorkSheet;
Worksheets m_WorkSheets;
CFileDialog dlg(TRUE,".xlsx",NULL,NULL,"Excel Files (*.xls)|*.xls||",NULL);
if(dlg.DoModal()==IDOK)
{
FilePath=dlg.GetPathName();
}
else
{
return;
}
int i,j,k,Index;
//创建Excel服务器
COleVariant avar((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
if(!exlapp.CreateDispatch("Excel.Application"))//CreateDispatch("Excel.Application")
{
AfxMessageBox("无法启动Excel服务器!");
return;
}
//Open an excel file
m_WorkBooks.AttachDispatch(exlapp.GetWorkbooks());
m_WorkBook=m_WorkBooks.Open(FilePath,avar,avar,avar,
avar,avar,avar,avar,avar,
avar,avar,avar,avar,avar,
avar);
m_WorkSheets=m_WorkBook.GetWorksheets();
Range range;
//// 读取SheetData表及其内部数据
char Temp[100];
wchar_t Buff_Attr[MAXLEN_STRING];
wchar_t Buff_Item[MAXLEN_STRING];
memset(Temp,0,100);
for (k=0;k<100;++k)
{
memset(SheetData[k],0,30);
}
pFile=fopen(SheetPath,"r");
if (!pFile)
{
MessageBox("打开SheetNamePath文本失败。","信息提示",MB_OK);
return;
}
k=0;
unsigned ColumnSize[100]={0},RowSize[100]={0};
while(1)
{
memset(Temp,0,100);
fgets(Temp,100,pFile);
if (strcmp(Temp,"")==0||strcmp(Temp,"\n")==0)
{
break;
}
if (strstr(Temp,"\n")!=NULL)
{
Temp[strlen(Temp)-1]='\0';
}
if(sscanf(Temp,"%s%d%d",SheetData[k],&RowSize[k],&ColumnSize[k])!=3)
{
MessageBox("读取SheetData文本失败。","信息提示",MB_OK);
return;
}
++k;
}
fclose(pFile);
//// 处理XML存储路径
m_SavePath.GetWindowText(szSavePath);
if (szSavePath.Find(".xml")==-1)
{
if (!szSavePath.IsEmpty())
{
szSavePath+="\\jobsvr_tstdt.xml";
}
else
{
szSavePath="C:\\jobsvr_tstdt.xml";
}
}
//////生成指定存储位置的xml文件
/*
节点嵌套层
Node_schemas
Node_class
Node_case
Node_normal
Node_value
*/
k=0;
IXMLBuilder* builder = Ablaze_XMLBuilder();
Ablaze::IXMLDocument* doc = NULL;
IXMLNode* Node_schemas = NULL;
doc = builder->CreateDocument();
UnicodeProcess("schemas",Buff_Item,MAXLEN_STRING);
CString ValCells,Attr;
builder->AppendRoot(doc, UnicodeToUTF8(Buff_Item), &Node_schemas);
VARIANT ret;
while (strcmp(SheetData[k],"")!=0)
{
Index=0;
// <attr n = "UUID_L" >96626b00-3aa6-11e1-8000-5dfc00005cfc</attr>
IXMLNode* Node_class = NULL;
UnicodeProcess("class ",Buff_Item,MAXLEN_STRING);
builder->AppendChildNode(Node_schemas,UnicodeToUTF8(Buff_Item), &Node_class);
UnicodeProcess("n ",Buff_Item,MAXLEN_STRING);
builder->SetNodeAttribute(Node_class,UnicodeToUTF8(Buff_Item),SheetData[k]);
////遍历工作簿文件下的所有Sheet表,每一个表即是一个Ablaze类
m_WorkSheet = m_WorkSheets.GetItem(COleVariant((short)(k+1)));
// if (strcmp(SheetData[k],"rBrkDwnTsk")==0)
// {
// MessageBox("等等","信息提示",MB_OK);
// }
NumToLetter(ColumnSize[k],Temp,100,RowSize[k]);
if(strcmp(Temp,"")==0)
{
MessageBox("SheetData配置文件不正确。","信息提示",MB_OK);
return;
}
range=m_WorkSheet.GetRange(COleVariant("A1"), COleVariant(Temp));//选定有效的读取范围
ret=range.GetValue(avar);
COleSafeArray sa(ret);
long lNumRows;
long lNumCols;
sa.GetUBound(1, &lNumRows);
sa.GetUBound(2, &lNumCols);
for (i=7;i<=lNumRows;++i)
{
ValCells.Empty();
IXMLNode* Node_case= NULL;
IXMLNode* Node_normal = NULL;
UnicodeProcess("case ",Buff_Item,MAXLEN_STRING);
builder->AppendChildNode(Node_class,UnicodeToUTF8(Buff_Item),&Node_case);
memset(Temp,0,100);
sprintf(Temp,"%d",++Index);
UnicodeProcess(" id ",Buff_Item,MAXLEN_STRING);
builder->SetNodeAttribute(Node_case,UnicodeToUTF8(Buff_Item),Temp);////一个Node_case就是
UnicodeProcess("normal",Buff_Item,MAXLEN_STRING);////Excel的一行数据。
builder->AppendChildNode(Node_case,UnicodeToUTF8(Buff_Item),&Node_normal);
for (j=2;j<=lNumCols;++j)
{
ValCells.Empty();
IXMLNode* Node_value = NULL;
UnicodeProcess("attr",Buff_Item,MAXLEN_STRING);
builder->AppendChildNode(Node_normal, UnicodeToUTF8(Buff_Item), &Node_value);
GetCellsValue(Attr,ret,1,j);//获取属性名存入AttrList表中
GetCellsValue(ValCells,ret,i,j);//获取具体属性值
wcscpy(Buff_Attr,ANSIToUnicode(Attr));
UnicodeProcess("n ",Buff_Item,MAXLEN_STRING);
builder->SetNodeAttribute(Node_value,UnicodeToUTF8(Buff_Item),UnicodeToUTF8(Buff_Attr));
wcscpy(Buff_Item,ANSIToUnicode(ValCells));
builder->SetNodeValue(Node_value,UnicodeToUTF8(Buff_Item));
Node_value->Release();
}
Node_case->Release();
Node_normal->Release();
}
Node_class->Release();
++k;
}
pFile=fopen(szSavePath,"w");
if (!pFile)
{
MessageBox("创建XML文本出错。","信息提示",MB_OK);
return;
}
builder->DumpDocumentToFile(doc,pFile);
fclose(pFile);
doc->Release();
if ( builder != NULL ) builder->Release();
m_WorkBooks.ReleaseDispatch();
exlapp.ReleaseDispatch();
m_WorkBook.Close(avar,COleVariant(FilePath),avar);
m_WorkBooks.Close();
exlapp.Quit();
memset(Temp,0,100);
sprintf(Temp,"总共处理了%d个类。",k);
::MessageBox(m_hWnd,Temp,"信息提示",MB_OK);
}
void GetCellsValue(CString &ValCells,VARIANT &ret,int iRow,int jColumn)
{
ValCells.Empty();
CString cstr;
VARIANT val;
COleSafeArray sa(ret);
long index[2];
index[0]=iRow;
index[1]=jColumn;
sa.GetElement(index, &val);
switch(val.vt)
{
case VT_R8: //数字
{
//TRACE("\t\t%1.2f", val.dblVal);
cstr="";
cstr.Format("%1.1f",val.dblVal);
ValCells+=cstr;
break;
}
case VT_BSTR: //文字
{ //TRACE("\t\t%s",(CString)val.bstrVal);
ValCells+=(CString)val.bstrVal+"\n";
break;
}
case VT_EMPTY:
ValCells="";
break;
}
}