xlsreadwriteii2.pas

一个经典的读写Excel的控件

procedure TXLSReadWriteII2.SetUserName(const Value: string);
begin
  FRecords.WRITEACCESS := Value;
end;

function TXLSReadWriteII2.GetBookProtected: boolean;
begin
  Result := FRecords.WINDOWPROTECT;
end;

procedure TXLSReadWriteII2.SetBookProtected(const Value: boolean);
begin
  FRecords.WINDOWPROTECT := Value;
end;

function TXLSReadWriteII2.GetBackup: boolean;
begin
  Result := FRecords.BACKUP;
end;

procedure TXLSReadWriteII2.SetBackup(const Value: boolean);
begin
  FRecords.BACKUP := Value;
end;

function TXLSReadWriteII2.GetRefreshAll: boolean;
begin
  Result := FRecords.REFRESHALL;
end;

procedure TXLSReadWriteII2.SetRefreshAll(const Value: boolean);
begin
  FRecords.REFRESHALL := Value;
end;

function TXLSReadWriteII2.GetNameAsFloat(AName: string): double;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsFloat[Col,Row];
end;

procedure TXLSReadWriteII2.SetNameAsFloat(AName: string; const Value: double);
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  FSheets[SheetIndex].AsFloat[Col,Row] := Value;
end;

function TXLSReadWriteII2.GetNameAsBoolean(AName: string): boolean;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsBoolean[Col,Row];
end;

function TXLSReadWriteII2.GetNameAsBoolFormulaValue(AName: string): boolean;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsBoolFormulaValue[Col,Row];
end;

function TXLSReadWriteII2.GetNameAsError(AName: string): TCellError;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsError[Col,Row];
end;

function TXLSReadWriteII2.GetNameAsFmtString(AName: string): WideString;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsFmtString[Col,Row];
end;

function TXLSReadWriteII2.GetNameAsFormula(AName: string): string;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsFormula[Col,Row];
end;

function TXLSReadWriteII2.GetNameAsNumFormulaValue(AName: string): double;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsNumFormulaValue[Col,Row];
end;

function TXLSReadWriteII2.GetNameAsStrFormulaValue(AName: string): WideString;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsStrFormulaValue[Col,Row];
end;

function TXLSReadWriteII2.GetNameAsString(AName: string): WideString;
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  Result := FSheets[SheetIndex].AsString[Col,Row];
end;

procedure TXLSReadWriteII2.SetNameAsBoolean(AName: string; const Value: boolean);
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  FSheets[SheetIndex].AsBoolean[Col,Row] := Value;
end;

procedure TXLSReadWriteII2.SetNameAsBoolFormulaValue(AName: string; const Value: boolean);
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  FSheets[SheetIndex].AsBoolFormulaValue[Col,Row] := Value;
end;

procedure TXLSReadWriteII2.SetNameAsError(AName: string; const Value: TCellError);
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  FSheets[SheetIndex].AsError[Col,Row] := Value;
end;

procedure TXLSReadWriteII2.SetNameAsFormula(AName: string; const Value: string);
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  FSheets[SheetIndex].AsFormula[Col,Row] := Value;
end;

procedure TXLSReadWriteII2.SetNameAsNumFormulaValue(AName: string; const Value: double);
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  FSheets[SheetIndex].AsNumFormulaValue[Col,Row] := Value;
end;

procedure TXLSReadWriteII2.SetNameAsStrFormulaValue(AName: string; const Value: WideString);
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  FSheets[SheetIndex].AsStrFormulaValue[Col,Row] := Value;
end;

procedure TXLSReadWriteII2.SetNameAsString(AName: string; const Value: WideString);
var
  SheetIndex,Col,Row: integer;
begin
  FFormulaHandler.GetNameRef(AName,SheetIndex,Col,Row);
  FSheets[SheetIndex].AsString[Col,Row] := Value;
end;

function TXLSReadWriteII2.GetVersionNumber: string;
begin
  Result := CurrentVersionNumber;
end;

procedure TXLSReadWriteII2.SetVerionNumber(const Value: string);
begin
//
end;

procedure TXLSReadWriteII2.FormulaHandlerSheetName(Name: WideString; var Index,Count: integer);
var
  Sheet: TSheet;
begin
  Sheet := FSheets.SheetByName(Name);
  if Sheet = Nil then begin
    Index := -1;
    Count := 0;
  end
  else begin
    Index := Sheet.Index;
    Count := FSheets.Count;
  end;
end;

function TXLSReadWriteII2.FormulaHandlerSheetData(DataType: TSheetDataType; SheetIndex, Col, Row: integer): WideString;
begin
  case DataType of
    sdtName:
      Result := FSheets[SheetIndex].Name;
    sdtCell:
      Result := FSheets[SheetIndex].AsString[Col,Row];
  end;
end;

function TXLSReadWriteII2.GetInternalNames: TInternalNames;
begin
  Result := FFormulaHandler.InternalNames;
end;

procedure TXLSReadWriteII2.SetInternalNames(const Value: TInternalNames);
begin

end;

procedure TXLSReadWriteII2.Calculate;
var
  i: integer;
begin
  for i := 0 to FSheets.Count - 1 do
    FSheets[i].CalculateSheet;
end;

procedure TXLSReadWriteII2.BeginUpdate;
begin
  FSheets.SST.BeginUpdate;
end;

procedure TXLSReadWriteII2.EndUpdate;
begin
  FSheets.SST.EndUpdate;
end;

procedure TXLSReadWriteII2.CopyColumns(SrcSheet, Col1, Col2, DestSheet, DestCol: integer; DoCopyCells: boolean = True);
var
  List: TList;
begin
  if SrcSheet = DestSheet then
    FSheets[SrcSheet].Columns.CopyColumns(Col1,Col2,DestCol)
  else begin
    List := TList.Create;
    try
      FSheets[SrcSheet].Columns.CopyList(List,Col1,Col2);
      FSheets[DestSheet].Columns.InsertList(List,DestCol,Col2 - Col2 + 1);
    finally
      List.Free;
    end;
  end;
  if DoCopyCells then
    CopyCells(SrcSheet,Col1,0,Col2,MAXROW,DestSheet,DestCol,0);
end;

procedure TXLSReadWriteII2.CopyRows(SrcSheet, Row1, Row2, DestSheet, DestRow: integer; DoCopyCells: boolean = True);
var
  List: TList;
begin
  if SrcSheet = DestSheet then
    FSheets[SrcSheet].Rows.CopyRows(Row1,Row2,DestRow)
  else begin
    List := TList.Create;
    try
      FSheets[SrcSheet].Rows.CopyList(List,Row1,Row2);
      FSheets[DestSheet].Rows.InsertList(List,DestRow,Row2 - Row1 + 1);
    finally
      List.Free;
    end;
  end;
  if DoCopyCells then
    CopyCells(SrcSheet,0,Row1,MAXCOL,Row2,DestSheet,0,DestRow);
end;

procedure TXLSReadWriteII2.DeleteColumns(Sheet, Col1, Col2: integer);
begin
  FSheets[Sheet].Columns.DeleteColumns(Col1,Col2);
  MoveCells(Sheet,Col2 + 1,0,MAXCOL,MAXROW,Sheet,Col1,0);
end;

procedure TXLSReadWriteII2.DeleteRows(Sheet, Row1, Row2: integer);
begin
  FSheets[Sheet].Rows.DeleteRows(Row1,Row2);
  MoveCells(Sheet,0,Row2 +1,MAXCOL,MAXROW,Sheet,0,Row1);
end;

procedure TXLSReadWriteII2.MoveColumns(SrcSheet, Col1, Col2, DestSheet, DestCol: integer; DoMoveCells: boolean = True);
var
  List: TList;
begin
  if SrcSheet = DestSheet then
    FSheets[SrcSheet].Columns.MoveColumns(Col1,Col2,DestCol)
  else begin
    List := TList.Create;
    try
      FSheets[SrcSheet].Columns.CopyList(List,Col1,Col2);
      FSheets[DestSheet].Columns.InsertList(List,DestCol,Col2 - Col1 + 1);
      FSheets[SrcSheet].Columns.DeleteColumns(Col1,Col2);
    finally
      List.Free;
    end;
  end;
  if DoMoveCells then
    MoveCells(SrcSheet,Col1,0,Col2,MAXROW,DestSheet,DestCol,0);
end;

procedure TXLSReadWriteII2.MoveRows(SrcSheet, Row1, Row2, DestSheet, DestRow: integer; DoMoveCells: boolean = True);
var
  List: TList;
begin
  if SrcSheet = DestSheet then 
    FSheets[SrcSheet].Rows.MoveRows(Row1,Row2,DestRow)
  else begin
    List := TList.Create;
    try
      FSheets[SrcSheet].Rows.CopyList(List,Row1,Row2);
      FSheets[DestSheet].Rows.InsertList(List,DestRow,Row2 - Row1 + 1);
    finally
      List.Free;
    end;
  end;
  if DoMoveCells then
    MoveCells(SrcSheet,0,Row1,MAXCOL,Row2,DestSheet,0,DestRow);
end;

procedure TXLSReadWriteII2.ClearColumns(Sheet, Col1, Col2: integer);
begin
  FSheets[Sheet].Columns.ClearColumns(Col1,Col2);
  FSheets[Sheet].DeleteCells(Col1,0,Col2,MAXROW);
end;

procedure TXLSReadWriteII2.ClearRows(Sheet, Row1, Row2: integer);
begin
  FSheets[Sheet].Rows.ClearRows(Row1,Row2);
end;

procedure TXLSReadWriteII2.InsertColumns(Sheet, Col, ColCount: integer);
begin
  FSheets[Sheet].Columns.InsertColumns(Col,ColCount);
  MoveCells(Sheet,Col,0,MAXCOL,MAXROW,Sheet,Col + ColCount,0);
end;

procedure TXLSReadWriteII2.InsertRows(Sheet, Row, RowCount: integer);
begin
  FSheets[Sheet].Rows.InsertRows(Row,RowCount);
  MoveCells(Sheet,0,Row,MAXCOL,MAXROW,Sheet,0,Row + RowCount);
end;

procedure TXLSReadWriteII2.CopySheet(SrcSheet, DestSheet: integer);
begin
  if SrcSheet <> DestSheet then begin
    Sheets[DestSheet].ClearWorksheet;
    CopyColumns(SrcSheet,0,MAXCOL,DestSheet,0,False);
    CopyRows(SrcSheet,0,MAXROW,DestSheet,0,False);
    CopyCells(SrcSheet,0,0,MAXCOL,MAXROW,DestSheet,0,0);
  end;
end;

procedure TXLSReadWriteII2.WrittenNCells;
var
  V: double;
  Last: integer;
begin
  if FCellCount > 0 then begin
    V := 100 / FCellCount;
    Last := Round(FProgressCount * V);
    Inc(FProgressCount,100);
    if Round(FProgressCount * V) <> Last then
      FProgressEvent(Self,Round(FProgressCount * V));
  end;
  Application.ProcessMessages;
end;

end.