将数据库中读取的文件写入Excel文件

最新推荐文章于 2021-09-16 22:22:46 发布

o0Linda0o

最新推荐文章于 2021-09-16 22:22:46 发布

阅读量713

点赞数

分类专栏： C++ Builder 文章标签： excel 数据库 buffer merge join 报表

本文链接：https://blog.csdn.net/o0Linda0o/article/details/4794125

版权

C++ Builder 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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;
}

o0Linda0o

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
将数据库中读取的文件写入Excel文件

1.使用SaveDialog控件，选中要导入的文件，得到用户要导入的文件路径。2.再打开Excel，得到对应的对象 Variant Ex = Variant::CreateObject("Excel.Application"); //file://设置Excel为不可见 Ex.OlePropertySet("Visible"
复制链接

扫一扫