//.H文件
#include <stdarg.h>
extern void StringGridToExcelMutiSheet(String CentStr,int Strgridcount,...);//多个页面,可变参数
void StringGridToExcelMutiSheet(String CentStr,int Strgridcount,...)
{
Variant my_excel,all_workbooks,my_workbook,my_worksheet,Range;
try
{
my_excel=CreateOleObject("excel.Application");//启动Excel
}
catch(...)
{
MessageBox(GetFocus(),"无法启动Excel","警告",MB_OK|MB_ICONSTOP);
//异常中断
Abort();
}
my_excel.OlePropertySet("Visible",(Variant)true); //使Excel启动后可见
all_workbooks=my_excel.OlePropertyGet("WorkBooks");
Procedure Open("Open");
va_list ap;//声明一个va_list变量
va_start(ap,Strgridcount);//初始化,第二个参数为最后一个确定的形参
my_workbook=all_workbooks.OleFunction("Add");//新建一个工作薄 //单工作表
my_workbook=my_excel.OlePropertyGet("ActiveWorkbook");
for(int i = 0; i < Strgridcount; i++)
{
TStringGrid*StringGridHisData=va_arg(ap, TStringGrid*); //读取可变参数,的二个参数为可变参数的类型
TStringGridInfo*StringGridHisDataFault_Point=(TStringGridInfo*)StringGridHisData->Tag;
Variant bef1,aft1;
int count=my_excel.OlePropertyGet("sheets").OlePropertyGet("count");
if (i>=3)//默认创建三个,超过三个需要创建
{
aft1=my_excel.OlePropertyGet("sheets",count);
my_workbook.OlePropertyGet("sheets").OleProcedure("Add",bef1.NoParam(),aft1);
}
// my_worksheet=my_workbook.OlePropertyGet("Sheets");
// my_worksheet = my_workbook.OleProcedure("Add");
// my_worksheet=my_workbook.OlePropertyGet("Sheets", i+1);
my_excel.OlePropertyGet("Sheets",i+1).OleProcedure("Select");//选择第二工作表
my_worksheet=my_excel.OlePropertyGet("ActiveSheet");//选择第二工作表?
// String SheetName="数据表"+IntToStr(i+1);
String SheetName=AnsiString(StringGridHisDataFault_Point->SheetName);
my_worksheet.OlePropertySet("Name",SheetName.c_str());//重命名当前工作表 strTitle.c_str() CentStr.Trim().c_str()
char cellsstart = 0x41;
String strRanger = cellsstart;
strRanger+="1:";
cellsstart += StringGridHisData->ColCount-1;
String tmpstr = cellsstart;
strRanger += tmpstr+"1";
Range=my_excel.OlePropertyGet("Cells",1,1);
my_excel.OlePropertyGet("Range",strRanger.c_str()).OleFunction("Merge", false);//将指定的单元格合并
my_excel.OlePropertyGet("Cells",1,1).OlePropertySet("Value",CentStr.Trim().c_str());
my_excel.OlePropertyGet("Cells",1,1).OlePropertySet("HorizontalAlignment",xlCenter);// 居中
my_excel.OlePropertyGet("Rows", 1).OlePropertySet("RowHeight", 40);
Range.OlePropertyGet("Cells").OlePropertyGet("Font").OlePropertySet("size",25);
Range.OlePropertyGet("Cells").OlePropertyGet("Font").OlePropertySet("Name","宋体");
Range.OlePropertyGet("Cells").OlePropertyGet("Font").OlePropertySet("bold",true);
Range=my_excel.OlePropertyGet("Range",strRanger.c_str());
Range.OlePropertyGet("Borders",1).OlePropertySet("LineStyle",-4142); //左框
Range.OlePropertyGet("Borders",2).OlePropertySet("LineStyle",-4142); //下框
Range.OlePropertyGet("Borders",3).OlePropertySet("LineStyle",-4142); //上框
Range.OlePropertyGet("Borders",4).OlePropertySet("LineStyle",-4142); //下框
Range.OlePropertyGet("Borders").OlePropertySet("Weight",2);
Range.OlePropertyGet("Cells").OlePropertyGet("Font").OlePropertySet("size",15);
Range.OlePropertyGet("Cells").OlePropertyGet("Font").OlePropertySet("Name","宋体");
Range.OlePropertyGet("Cells").OlePropertyGet("Font").OlePropertySet("bold",true);
//设置列宽
Range=my_excel.OlePropertyGet("Cells",1);
Range.OlePropertySet("ColumnWidth",12);// 行宽
Range=my_excel.OlePropertyGet("Cells",2);
Range.OlePropertySet("ColumnWidth",20);// 行宽
Range=my_excel.OlePropertyGet("Cells",3);
Range.OlePropertySet("ColumnWidth",20);// 行宽
Range=my_excel.OlePropertyGet("Cells",4);
Range.OlePropertySet("ColumnWidth",20);// 行宽
Range=my_excel.OlePropertyGet("Cells",5);
Range.OlePropertySet("ColumnWidth",20);// 行宽
//输出表体
for(int i=0;i<StringGridHisData->ColCount;i++)
{
Range=my_excel.OlePropertyGet("Cells",i+1);
Range.OlePropertySet("HorizontalAlignment",xlCenter);// 居中
for(int k=0;k<StringGridHisData->RowCount;k++)
{
my_excel.OlePropertyGet("Rows", k+1).OlePropertySet("RowHeight", 25);
Range= my_excel.OlePropertyGet("Cells",(k+2),(i+1));
Range.OlePropertySet("NumberFormatLocal", "@");
Range.OlePropertySet("Value",StringGridHisData->Cells[i][k].Trim().c_str());
Range.OlePropertyGet("Borders",1).OlePropertySet("LineStyle",-4142); //左框
Range.OlePropertyGet("Borders",2).OlePropertySet("LineStyle",-4142); //下框
Range.OlePropertyGet("Borders",3).OlePropertySet("LineStyle",-4142); //上框
Range.OlePropertyGet("Borders",4).OlePropertySet("LineStyle",-4142); //下框
Range.OlePropertyGet("Borders").OlePropertySet("Weight",2);
Range.OlePropertySet("WrapText", true);
Application->ProcessMessages();
}
}
}
va_end(ap);//清理工作
}