c++ builder (BCB) Oracle 导出 Excel 表

//--------------------------------------------------------------------------- #include <vcl.h> #include <OleCtnrs.hpp> #include "ComObj.hpp" #pragma hdrstop #include "ExportXLS.h" #include "Excel_2K_SRVR.h" //--------------------------------------------------------------------------- #pragma package(smart_init) #define BEGIN_ROW 6 #define BEGIN_COL 2 //--------------------------------------------------------------------------- __fastcall ExportXLS::ExportXLS() { MaxRow = 0; MaxCol = 0; BeginRow = BEGIN_ROW; BeginCol = BEGIN_COL; List = new TList; } //--------------------------------------------------------------------------- __fastcall ExportXLS::~ExportXLS() { TitleDef *Item; for (int i=0; i<List->Count; i++) { Item = (TitleDef *)List->Items[i]; delete Item; } delete List; } //--------------------------------------------------------------------------- void __fastcall ExportXLS::MergeCell(AnsiString strRange,bool GridLines, int Align) { Variant ERange,font; ERange = Sh1.OlePropertyGet("Range",strRange.c_str()); if (GridLines) DrawGridLines(ERange); ERange.OleFunction("Merge"); if (Align == 0) // Left; ERange.OlePropertySet("HorizontalAlignment",xlLeft); else if (Align == 1) // Center ERange.OlePropertySet("HorizontalAlignment",xlCenter); else // Right ERange.OlePropertySet("HorizontalAlignment",xlRight); ERange.OlePropertySet("VerticalAlignment",xlCenter); font = ERange.OlePropertyGet("Font"); font.OlePropertySet("Bold",true); font.OlePropertySet("Name","宋体"); font.OlePropertySet("Size",9); } //--------------------------------------------------------------------------- void __fastcall ExportXLS::DrawGridLines(Variant ERange) { Variant EBorders,font; EBorders = ERange.OlePropertyGet("Borders"); EBorders.OlePropertySet("linestyle",xlContinuous); EBorders.OlePropertySet("weight",xlThin); EBorders.OlePropertySet("colorindex",xlAutomatic); font = ERange.OlePropertyGet("Font"); font.OlePropertySet("Name","宋体"); font.OlePropertySet("Size",9); } //--------------------------------------------------------------------------- void __fastcall ExportXLS::DrawGridLines(AnsiString strRange) { Variant ERange,EBorders,font; ERange = Sh1.OlePropertyGet("Range",strRange.c_str()); EBorders = ERange.OlePropertyGet("Borders"); EBorders.OlePropertySet("linestyle",xlContinuous); EBorders.OlePropertySet("weight",xlThin); EBorders.OlePropertySet("colorindex",xlAutomatic); font = ERange.OlePropertyGet("Font"); font.OlePropertySet("Name","宋体"); font.OlePropertySet("Size",9); } //--------------------------------------------------------------------------- AnsiString __fastcall ExportXLS::GetRange(int col1,int row1,int col2,int row2) { AnsiString col_1,col_2; int iTem,iMod,iCount; char ichar; if (col1<27) col_1 = (char)(col1+64); else { iMod=col1%26; iCount= col1/26; if( iMod==0) { col_1 = (char)(iCount-1 + 64); col_1 = col_1 + char(90); } else { col_1 = (char)(col1/26 + 64); col_1 = col_1 + char(col1%26 + 64); } } if (col2<27) col_2 = (char)(col2+64); else { iMod=col2%26; iCount= col2/26; if( iMod==0) { col_2 = (char)(iCount-1 + 64); col_2 = col_2 + char(90); } else { col_2 = (char)(col2/26 + 64); col_2 = col_2 + char(col2%26 + 64); } } return col_1+IntToStr(row1)+":" + col_2 +IntToStr(row2); } //--------------------------------------------------------------------------- void __fastcall ExportXLS::AnalyseGridTitle() { AnsiString Title,tmpTitle; int Row; int Pos; TitleDef *Item; MaxRow = 0; // 找出标题的最大行数 for (int i=0; i<Grid->Columns->Count; i++) { Row = 0; tmpTitle = Grid->Columns->Items[i]->Title->Caption; Pos = tmpTitle.Pos("|"); while (Pos > 0) { Row++; tmpTitle = tmpTitle.SubString(Pos+1,tmpTitle.Length()-Pos); Pos = tmpTitle.Pos("|"); } if (Row > MaxRow) MaxRow = Row; } for (int i=0; i<Grid->Columns->Count; i++) { Row = 0; tmpTitle = Grid->Columns->Items[i]->Title->Caption; Pos = tmpTitle.Pos("|"); if (Pos == 0) Title = tmpTitle; else Title = tmpTitle.SubString(1,Pos-1); Item = FindTitle(Title,i,0); if (Item) { Item->end_x = i; Item->end_y = 0; } else { Item = new TitleDef; Item->Title = Title; Item->begin_x = i; Item->begin_y = 0; Item->end_x = i; Item->end_y = 0; List->Add(Item); } while (Pos > 0) { Row++; tmpTitle = tmpTitle.SubString(Pos+1,tmpTitle.Length()-Pos); Pos = tmpTitle.Pos("|"); if (Pos == 0) Title = tmpTitle; else Title = tmpTitle.SubString(1,Pos-1); Item = FindTitle(Title,i,Row); if (Item) { Item->end_x = i; Item->end_y = Row; } else { Item = new TitleDef; Item->Title = Title; Item->begin_x = i; Item->begin_y = Row; Item->end_x = i; Item->end_y = Row; List->Add(Item); } } Item->end_y = MaxRow; } } //--------------------------------------------------------------------------- TitleDef* __fastcall ExportXLS::FindTitle(AnsiString Title, int x, int y) { TitleDef *Item = NULL; for (int i=0; i<List->Count; i++) { Item = (TitleDef *)List->Items[i]; if (Item->Title == Title) { if ((Item->begin_y == y) & (Item->end_x == x-1)) return Item; } } return NULL; } //--------------------------------------------------------------------------- void __fastcall ExportXLS::ConstructGridTitle() { int EndRow; int EndCol = Grid->Cols + BeginCol - 1; AnsiString Range; TitleDef *Item = NULL; AnalyseGridTitle(); EndRow = BeginRow + MaxRow; Range = GetRange(BeginCol, BeginRow, EndCol, EndRow); DrawGridLines(Range); for (int i=0; i<List->Count; i++) { Item = (TitleDef *)List->Items[i]; Range = GetRange(BeginCol + Item->begin_x, BeginRow + Item->begin_y, BeginCol + Item->end_x, BeginRow + Item->end_y); //ShowMessage( Range); MergeCell(Range,false); DrawGridLines(Range); Sh1.PG("Cells", BeginRow + Item->begin_y, BeginCol + Item->begin_x).PS("Value", Item->Title.c_str()); } } //--------------------------------------------------------------------------- /* void __fastcall ExportXLS::ConstructTitleArea(TList *TitleList) { UserTitle *Item; int max_x, max_y; int a,b; int i,j,k; int begin_row, begin_col; AnsiString Range; max_x = 0; max_y = 0; for (i=0; i<TitleList->Count; i++) { Item = (UserTitle *)TitleList->Items[i]; if (Item->x > max_x) max_x = Item->x; if (Item->y > max_y) max_y = Item->y; } a = MaxCol/(max_x+1); b = MaxCol%(max_x+1); if (((a+1)*max_x < MaxCol) & (b>0)) a++; for (j=max_y; j>=0; j--) { begin_row = BEGIN_ROW - j - 1; for (i=0; i<max_x; i++) { begin_col = BeginCol + i*a; Range = GetRange(begin_col, begin_row, begin_col+a-1, begin_row); for (k=0; k<TitleList->Count; k++) { Item = (UserTitle *)TitleList->Items[k]; if ((Item->y == (max_y-j)) & (Item->x == i)) { MergeCell(Range,false,Item->x); Sh1.PG("Cells", begin_row, begin_col).PS("Value", Item->Title.c_str()); break; } } } begin_col = begin_col + a; Range = GetRange(begin_col, begin_row, BeginCol + MaxCol - 1, begin_row); for (k=0; k<TitleList->Count; k++) { Item = (UserTitle *)