1.使用SaveDialog控件,选中要导入的文件,得到用户要导入的文件路径。
2.再打开Excel,得到对应的对象
Variant Ex = Variant::CreateObject("Excel.Application");
//file://设置Excel为不可见
Ex.OlePropertySet("Visible",false);
//file://打开指定的Excel报表文件。
Ex.OlePropertyGet("WorkBooks").OleProcedure("Open",StrApp.c_str());
Variant Wb = Ex.OlePropertyGet("ActiveWorkBook");
Variant Sheet = Wb.OlePropertyGet("ActiveSheet");//获得当前默认的Sheet
3.得到Sheet之后就可以对表开始操作了。
//合并单元格,从A1到C3
Sheet.OlePropertyGet("Range","A1:C3").OleFunction("Merge" , false);
//给单元格赋值
Sheet.OlePropertyGet("Cells",1,1).OlePropertySet("Value","项目计划书");
//通过数据集给表格赋值。循环数据集,得到每行数据
for(CDS_GX->First(); !CDS_GX->Eof; CDS_GX->Next())
{
//将得到的对应表格中的单元格由AnsiString类型转换成为char数组。
AAA=CDS_GX->FieldByName("Size")->AsString;
Sheet.OlePropertyGet("Cells",3,4).OlePropertySet("Value",AAA.c_str());
}
//表中的文字都可以用字符串拼接,但是都需要转换成char的数组///
4.关闭Excel文件,并且将资源释放。
Wb.OleProcedure("SaveAs",StrApp.c_str());//保存表格
Wb.OleProcedure("Close");//关闭表格
Ex.OleFunction("Quit");//退出Excel
Ex = Unassigned;
Wb = Unassigned;
Sheet = Unassigned;
------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------
if(SaveDialog1->Execute())
{
AnsiString StrApp=SaveDialog1->FileName;
try
{
Variant Ex = Variant::CreateObject("Excel.Application");
//file://设置Excel为不可见
Ex.OlePropertySet("Visible",false);
//file://打开指定的Excel报表文件。
//Ex.OlePropertyGet("WorkBooks").OleProcedure("Create",StrApp.c_str());
Ex.OlePropertyGet("WorkBooks").OleProcedure("Open",StrApp.c_str());
//("Open",str_ExcelFileName,NULL,IfReadOnly);
Variant Wb = Ex.OlePropertyGet("ActiveWorkBook");
Variant Sheet = Wb.OlePropertyGet("ActiveSheet");//获得当前默认的Sheet
//标题
Sheet.OlePropertyGet("Range","A1:K1").OleFunction("Merge" , false);
Sheet.OlePropertyGet("Cells",1,1).OlePropertySet("Value","项目计划书");
Sheet.OlePropertyGet("Cells",2,1).OlePropertySet("Value","序号");
Sheet.OlePropertyGet("Cells",2,2).OlePropertySet("Value","项目编号");
Sheet.OlePropertyGet("Cells",2,3).OlePropertySet("Value","名称");
Sheet.OlePropertyGet("Cells",2,4).OlePropertySet("Value","规格");
Sheet.OlePropertyGet("Cells",2,5).OlePropertySet("Value","数量");
Sheet.OlePropertyGet("Cells",2,6).OlePropertySet("Value","单位");
Sheet.OlePropertyGet("Cells",2,7).OlePropertySet("Value","工序");
Sheet.OlePropertyGet("Cells",2,8).OlePropertySet("Value","操作人");
Sheet.OlePropertyGet("Cells",2,9).OlePropertySet("Value","工时(h)");
Sheet.OlePropertyGet("Cells",2,10).OlePropertySet("Value","单价(元)");
Sheet.OlePropertyGet("Cells",2,11).OlePropertySet("Value","完工时间");
CDS_GX->CommandText="select ROW_NUMBER() OVER(ORDER BY a.[PreID] DESC) AS 'ID',b.ItemNo,b.ItemName,a.[Size],a.Number,a.Unit from dbo.t_PreMaterial as a right JOIN t_AppItem as b on a.ProductID=b.ProductID where b.ItemID="+IntToStr(CurItemID);
pDBAgent->GetDataSet(CDS_GX->CommandText,CDS_GX);
AnsiString strItemNo="";
AnsiString strItemName="";
AnsiString AAA="";
for(CDS_GX->First(); !CDS_GX->Eof; CDS_GX->Next())
{
Sheet.OlePropertyGet("Cells",3,1).OlePropertySet("Value","1");
AAA=CDS_GX->FieldByName("Size")->AsString;
Sheet.OlePropertyGet("Cells",3,4).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("Number")->AsString;
Sheet.OlePropertyGet("Cells",3,5).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("Unit")->AsString;
Sheet.OlePropertyGet("Cells",3,6).OlePropertySet("Value",AAA.c_str());
strItemNo=CDS_GX->FieldByName("ItemNo")->AsString;
strItemName=CDS_GX->FieldByName("ItemName")->AsString;
}
CDS_GX->CommandText="select B.Spec,B.Number,B.Unit,E.UserName,D.ProcessName,B.TimeNum,B.UnitPrice,convert(varchar,B.EndTime,23) as EndTime from t_Plan AS A right JOIN t_Planentry as B On A.ID=B.PlanID left join t_ProProcess as C on A.ProProcessID=C.ProProcessID left join t_Process as D On C.ProcessID=D.ProcessID left join t_User as E ON B.UserID=E.UserID where A.ItemID="+IntToStr(CurItemID);
pDBAgent->GetDataSet(CDS_GX->CommandText,CDS_GX);
int i=0;
for(CDS_GX->First(); !CDS_GX->Eof; CDS_GX->Next())
{
i++;
Sheet.OlePropertyGet("Cells",3+i,1).OlePropertySet("Value",IntToStr(3+i).c_str());
AAA=CDS_GX->FieldByName("Spec")->AsString;
Sheet.OlePropertyGet("Cells",3+i,4).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("Number")->AsString;
Sheet.OlePropertyGet("Cells",3+i,5).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("Unit")->AsString;
Sheet.OlePropertyGet("Cells",3+i,6).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("ProcessName")->AsString;
Sheet.OlePropertyGet("Cells",3+i,7).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("UserName")->AsString;
Sheet.OlePropertyGet("Cells",3+i,8).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("TimeNum")->AsString;
Sheet.OlePropertyGet("Cells",3+i,9).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("UnitPrice")->AsString;
Sheet.OlePropertyGet("Cells",3+i,10).OlePropertySet("Value",AAA.c_str());
AAA=CDS_GX->FieldByName("EndTime")->AsString;
Sheet.OlePropertyGet("Cells",3+i,11).OlePropertySet("Value",AAA.c_str());
}
AAA="B3:B"+IntToStr(3+i);
Sheet.OlePropertyGet("Range",AAA.c_str()).OleFunction("Merge" , false);
Sheet.OlePropertyGet("Cells",3,2).OlePropertySet("Value",strItemNo.c_str());
AAA="C3:C"+IntToStr(3+i);
Sheet.OlePropertyGet("Range",AAA.c_str()).OleFunction("Merge" , false);
Sheet.OlePropertyGet("Cells",3,3).OlePropertySet("Value",strItemName.c_str());
Wb.OleProcedure("SaveAs",StrApp.c_str());//保存表格
Wb.OleProcedure("Close");//关闭表格
Ex.OleFunction("Quit");//退出Excel
Ex = Unassigned;
Wb = Unassigned;
Sheet = Unassigned;
}
catch(...)
{
ShowMsg("启动Excel出错,可能由于Excel没有正确安装!");
return;
}
}
AnsiString __fastcall TScheduleFrame::ReturnChar(char c,int n)
{
const char end = 'Z';
int _span = end - c; //距离最后一个字母Z的距离
int _buffer = (_span==0) ? 1 : n / _span; //前缀 _buffer个A
int _last = n - _buffer * _span; //最后一个字母
char *_return = new char[_buffer + 2];
for(int i=0;i<_buffer;i++)
_return[i]='A';
_return[_buffer]=(char)(_last+64);
_return[_buffer+1]='/0';
return (AnsiString)_return;
}