可变参数导出EXCElL

 

 

//.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);//清理工作
}