stocksmain.pas

DevExpress公司出品的Borland Delphi和C++ Builder的控件(包含完整源代码)。 ExpressSpreadSheet:交叉数据表格控件。 一款Delphi

            end;
          finally
            Free;                                          // free it
          end;
end;

procedure TStocksMainForm.actCellsExecute(Sender: TObject);
var
  AForm :TStocksModifyForm;
const
  AFormType: array[Boolean] of TcxSSModifyType = (mtDelete, mtInsert);
begin
   if FIsUpdate then Exit;
   AForm := TStocksModifyForm.Create(Self);
   AForm.Top := (Top + Height) shr 1;
   AForm.Left := (Left + Width) shr 1;
   try
     if AForm.Execute(AFormType[TCustomAction(Sender).Tag = 1]) then
       with cxSpreadBook.ActiveSheet do
         case TCustomAction(Sender).Tag of
           0:
             DeleteCells(SelectionRect, AForm.Modify);
           1:
             InsertCells(SelectionRect, AForm.Modify);
         end;
   finally
     AForm.Free;
   end;
end;

procedure TStocksMainForm.actFormatCellsExecute(Sender: TObject);
begin
  with cxSpreadBook.ActiveSheet do
    FormatCells(SelectionRect);
end;

procedure TStocksMainForm.actHideCellsExecute(Sender: TObject);
begin
  if FIsUpdate then Exit;
  with cxSpreadBook.ActiveSheet do
    SetVisibleState(SelectionRect, True, True, False);
end;

procedure TStocksMainForm.actShowCellsExecute(Sender: TObject);
begin
  if FIsUpdate then Exit;
  with cxSpreadBook.ActiveSheet do
    SetVisibleState(SelectionRect, True, True, True);
end;

procedure TStocksMainForm.actHideColExecute(Sender: TObject);
begin
  if FIsUpdate then Exit;
  with cxSpreadBook.ActiveSheet do
    SetVisibleState(SelectionRect, True, False, False);
end;

procedure TStocksMainForm.actShowColExecute(Sender: TObject);
begin
  if FIsUpdate then Exit;
  with cxSpreadBook.ActiveSheet do
    SetVisibleState(SelectionRect, True, False, True);
end;

procedure TStocksMainForm.actHideRowExecute(Sender: TObject);
begin
  if FIsUpdate then Exit;
  with cxSpreadBook.ActiveSheet do
    SetVisibleState(SelectionRect, False, True, False);
end;

procedure TStocksMainForm.actShowRowExecute(Sender: TObject);
begin
  if FIsUpdate then Exit;
  with cxSpreadBook.ActiveSheet do
    SetVisibleState(SelectionRect, False, True, True);
end;

procedure TStocksMainForm.actCutExecute(Sender: TObject);
begin
  with cxSpreadBook.ActiveSheet do
    Copy(SelectionRect, True);
end;

procedure TStocksMainForm.actCopyExecute(Sender: TObject);
begin
  with cxSpreadBook.ActiveSheet do
    Copy(SelectionRect, False);
end;

procedure TStocksMainForm.actPasteExecute(Sender: TObject);
begin
  with cxSpreadBook.ActiveSheet do
    Paste(SelectionRect.TopLeft);
end;

procedure TStocksMainForm.actSaveSpeadSheetExecute(Sender: TObject);
begin
  SaveSpreadSheet;
end;

procedure TStocksMainForm.SaveSpreadSheet;
var
  AFileName: string;
begin
  if SaveDialog.Execute then
  begin
    AFileName := ChangeFileExt(SaveDialog.FileName, '.xls');
    cxSpreadBook.SaveToFile(AFileName );
    ActiveMDIChild.Caption := AFileName;
  end;
end;

procedure TStocksMainForm.actExitExecute(Sender: TObject);
begin
  Close;
end;

procedure TStocksMainForm.actApplyFormattingUpdate(Sender: TObject);
begin
  TCustomAction(Sender).Enabled := FIsApplyFormatting;
end;

procedure TStocksMainForm.actSaveSpeadSheetUpdate(Sender: TObject);
begin
  TCustomAction(Sender).Enabled := FSaveSpreadSheet;
end;

procedure TStocksMainForm.actLoadDataExecute(Sender: TObject);
var
  CurCursor : TCursor;
begin
  CurCursor := Screen.Cursor;                              // preserve the current cursor
  Screen.Cursor := crHourGlass;
  try
    cxSpreadBook.BeginUpdate;
    cxSpreadBook.ActivePage := 0;                          // ensure we are looking at the right page
    CurRow := 3;                                           // set the starting current row
    With Query do                                          // and using our database query
    begin
      Open;                                                // open the data
      While NOT EOF do                                     // and for every record
      begin
        SetCellText(0,CurRow,                              // set the company name
                         FieldByName('CO_NAME').AsString);
        SetCellText(1,CurRow,                              // the number of shares
                          FieldByName('SHARES').AsString);
        SetCellDate(2,CurRow,                              // the purchase date
                      FieldByName('PUR_DATE').AsDateTime);
        SetCellText(3,CurRow,                              // the purchase price
                       FieldByName('PUR_PRICE').AsString);
        SetCellText(4,CurRow,                              // calculate the purchase cost = number of shares * purchase price
              Format('=B%d*D%d/100',[CurRow+1,CurRow+1]));
        SetCellText(5,CurRow,                              // the current price
                       FieldByName('CUR_PRICE').AsString);
        SetCellText(6,CurRow,                              // calculate the current valuation = number of shares * current price
              Format('=B%d*F%d/100',[CurRow+1,CurRow+1]));
        SetCellText(7,CurRow,                              // calculate the gain/loss = current valuation - purchase cost
                  Format('=G%d-E%d',[CurRow+1,CurRow+1]));
        SetCellText(8,CurRow,                              // calculate the % gain/loss
                  Format('=H%d/E%d',[CurRow+1,CurRow+1]));
        Next;
        Inc(CurRow);                                       // bump the row number
      end;
      Inc(CurRow);
      SetCellText(4,CurRow,                                // set the formula for the total purchase cost
                       Format('=SUM(E3:E%d)',[CurRow-1]));
      SetCellText(6,CurRow,                                //                         total current valuation
                       Format('=SUM(G3:G%d)',[CurRow-1]));
      SetCellText(7,CurRow,                                //                         total gain/loss
                       Format('=SUM(H3:H%d)',[CurRow-1]));
      SetCellText(8,CurRow,                                //                         and overall percentage
                  Format('=H%d/E%d',[CurRow+1,CurRow+1]));
      First;                                               // back to the first record
      cxSpreadBook.ActivePage := 1;                        // and move to the second page
      // and repeat the process to setup the data
      CurRow := 3;
      While NOT EOF do
      begin
        SetCellText(0,CurRow,                              // company name
                         FieldByName('CO_NAME').AsString);
        SetCellText(1,CurRow,                              // current price
                       FieldByName('CUR_PRICE').AsString);
        SetCellText(2,CurRow,                              // year high
                        FieldByName('YRL_HIGH').AsString);
        SetCellText(3,CurRow,                              // year low
                         FieldByName('YRL_LOW').AsString);
        SetCellText(4,CurRow,                              // average of Hi/Lo prices
              Format('=(C%d+D%d)/2',[CurRow+1,CurRow+1]));
        Next;
        Inc(CurRow);
      end;
      Inc(CurRow);
      Close;
      FIsApplyFormatting := True;
    end;
  Finally
    cxSpreadBook.EndUpdate;
    cxSpreadBook.Recalc;
    cxSpreadBook.ActivePage := 0;                          // back to first page
    Screen.Cursor := CurCursor;                            // restore original cursor
  end;
end;

function TStocksMainForm.GetCellText(SelectionRect: TRect; R1C1: Boolean): String;
begin
  with TcxSSUtils do
  begin
    Result := ColumnNameByIndex(SelectionRect.Left, R1C1) +
      RowNameByIndex(SelectionRect.Top, R1C1);
    if (SelectionRect.Left <> SelectionRect.Right) or (SelectionRect.Top <> SelectionRect.Bottom) then
      Result := Result + ' x ' + ColumnNameByIndex(SelectionRect.Right, R1C1) +
        RowNameByIndex(SelectionRect.Bottom, R1C1);
  end;
end;


procedure TStocksMainForm.AlwaysEnabled(Sender: TObject);
begin
  TCustomAction(Sender).Enabled := True;
end;

procedure TStocksMainForm.edtCellEditChange(Sender: TObject);
begin
  if FIsUpdate then Exit;
  with cxSpreadBook do
  begin
    with ActiveSheet.GetCellObject(ActiveSheet.SelectionRect.Left, ActiveSheet.SelectionRect.Top) do
      SetCellText((Sender as TEdit).Text);
    UpdateControl;
  end;
end;

procedure TStocksMainForm.edtCellEditExit(Sender: TObject);
begin
  with cxSpreadBook do
  begin
    with ActiveSheet.GetCellObject(ActiveSheet.SelectionRect.Left, ActiveSheet.SelectionRect.Top) do
    begin
      Text := Text;
      Free;
    end;
    UpdateControl;
    SetFocus;
  end;
  cxSpreadBookSetSelection(Self, cxSpreadBook.ActiveSheet);
end;

procedure TStocksMainForm.actApplyFormattingExecute(Sender: TObject);
var
  CHeader, RHeader : TcxSSHeader;
  I : Integer;
  CurCursor : TCursor;
begin
  CurCursor := Screen.Cursor;                              // preserve the current cursor
  Screen.Cursor := crHourGlass;
  FSaveSpreadSheet := False;
  cxSpreadBook.BeginUpdate;
  try
    cxSpreadBook.ActivePage := 0;                          // ensure we are looking at the first page
    // First set the column widths
    CHeader := cxSpreadBook.ActiveSheet.Cols;
    CHeader.Size[0] := 140;
    for I := 1 to 8 do CHeader.Size[I] :=  80;
    // and then the Row heights for the title and column descriptors
    RHeader := cxSpreadBook.ActiveSheet.Rows;
    RHeader.Size[0] :=  30;
    RHeader.Size[2] :=  30;
    // and do some cell formatting
    SetCellFont(0,0,0,0,[fsBold, fsUnderline],12);         // set the title and column header fonts
    SetCellPattern(0,0,8,1,16,1,fsSolid);                  // and add some cell shading for the sheet title
    SetCellPattern(0,2,8,2,23,1,fsSolid);                  // and for the column headers
    SetCellPattern(0,2,0,CurRow-2,23,1,fsSolid);           // and the company names
    // and now some number formatting
    SetCellFormat(1,3,1,CurRow-1, 3);                      // Holding Column #,##0
    SetCellFormat(2,3,2,CurRow-1,15);                      // Date Purchased d-mmm-yy
    SetCellFormat(3,3,6,CurRow-1, 4);                      // Price/Cost/Value & Worth Column #,##0.00
    SetCellFormat(7,3,7,CurRow-1, 8);                      // Gain/(Loss) value (#,##0.00_);[Red](#,##0.00)
    SetCellFormat(8,3,8,CurRow-1,10);                      // Gain/(Loss) percentage 0.00%
    // and now for the totals
    SetCellFormat(4,CurRow,4,CurRow, 8);                   // Total Purchase Cost ($#,##0.00_);[Red]($#,##0.00)
    SetCellFormat(6,CurRow,7,CurRow, 8);                   // Total Current Valuation ($#,##0.00_);[Red]($#,##0.00)
    SetCellFormat(8,CurRow,8,CurRow,10);                   // Total Gain/(Loss) percentage 0.00%
    // now align all the data
    SetCellAlignment(1,3,8,CurRow,haRight,vaCenter);       // Right Align all the numeric fields
    // set borders for the totals
    SetCellBorders(4,CurRow,4,CurRow, 1, lsThin);          // thin single line at the top of the cell
    SetCellBorders(6,CurRow,6,CurRow, 1, lsThin);
    SetCellBorders(7,CurRow,7,CurRow, 1, lsThin);
    SetCellBorders(4,CurRow,4,CurRow, 3, lsDouble);        // double line at the bottom of the cell
    SetCellBorders(6,CurRow,6,CurRow, 3, lsDouble);        // ditto
    SetCellBorders(7,CurRow,7,CurRow, 3, lsDouble);        // ditto

    // and now a similar process for the Hi/Lo valuations page
    cxSpreadBook.ActivePage := 1;                          // ensure we are looking at the second page
    // First set the column widths
    CHeader := cxSpreadBook.ActiveSheet.Cols;
    CHeader.Size[0] := 140;
    for I := 1 to 4 do CHeader.Size[I] :=  80;
    // and then the Row heights for the title and column descriptors
    RHeader := cxSpreadBook.ActiveSheet.Rows;
    RHeader.Size[0] :=  30;
    RHeader.Size[2] :=  30;
    SetCellFont(0,0,0,0,[fsBold, fsUnderline],12);
    // and now adding some cell shading
    SetCellPattern(0,0,4,1,16,1,fsSolid);                  // for the sheet title
    SetCellPattern(0,2,4,2,23,1,fsSolid);                  // for the column headers
    SetCellPattern(0,2,0,CurRow-2,23,1,fsSolid);           // and the company names
    SetCellFormat(1,3,4,CurRow-1, 4);                      //  Current, Hi, Lo and Average #,##0.00
    SetCellAlignment(1,3,4,CurRow,haRight,vaCenter);       // Right Align all the numeric fields

  finally
    FSaveSpreadSheet := True;                              // enable the save spreadsheet button
    cxSpreadBook.ActivePage := 0;                          // return to the first page
    cxSpreadBook.EndUpdate;
    Screen.Cursor := CurCursor;
  end;
end;

procedure TStocksMainForm.FormCreate(Sender: TObject);
begin
  FSaveSpreadSheet := False;
  FIsApplyFormatting := False;
end;

procedure TStocksMainForm.actMergeCellsExecute(Sender: TObject);
begin
  with cxSpreadBook.ActiveSheet do
    SetMergedState(SelectionRect, True);
end;

procedure TStocksMainForm.actSplitCellsExecute(Sender: TObject);
begin
  with cxSpreadBook.ActiveSheet do
    SetMergedState(SelectionRect, False);
end;

end.