unit IWAdvWebGridExcel;

interface

uses
  Classes, IWWebGrid
  ,tmsiwXLSAdapter, tmsiwUExcelAdapter, tmsiwUFlxUtils, tmsiwUFlxFormats,
  tmsiwUFlxMessages, tmsiwUXlsPictures, tmsiwXlsMessages, tmsiwUFlxNumberFormat
  ,Graphics, SysUtils, tmsiwUTextDelim, IWFont, Variants,
  IWTMSBase, IWServerControllerBase, IWApplication, JPEG, IWColor, Types, Windows
  ;

type

  TFlxFormatCellEvent = procedure(Sender: TTIWCustomWebGrid; const GridCol, GridRow, XlsCol, XlsRow: integer; const Value: widestring; var DateFormat, TimeFormat: Widestring) of object;
  TFlxFormatCellGenericEvent = procedure(Sender: TTIWCustomWebGrid; const GridCol, GridRow, XlsCol, XlsRow: integer; const Value: widestring; var Format: TFlxFormat) of object;

  TASGIOProgressEvent = procedure(Sender: TObject; SheetNum, SheetTot, RowNum, RowTot: integer) of object;
  TExportColumnFormatEvent = procedure (Sender: TObject; GridCol, GridRow, XlsCol, XlsRow: integer; const Value: widestring; var ExportCellAsString: boolean) of object;

  TOverwriteMode = (omNever, omAlways, omWarn);

  TInsertInSheet =
  (
      //Clears everything on the sheet before exporting the grid.
      InsertInSheet_Clear,

      //Overwrites existing cells, but does not clear any existing information on the sheet.
      InsertInSheet_OverwriteCells,

      //Inserts the grid inside the existing sheet, moving all the other rows down.
      InsertInSheet_InsertRows,

      //Inserts the grid inside the existing sheet, moving all the other columns to the right.
      InsertInSheet_InsertCols,

      //Inserts the grid inside the existing sheet, moving all the other rows down (Grid.RowCount - 2) rows.
      //The first two rows will be overwrited. This can be used to export for example inside of a chart.
      InsertInSheet_InsertRowsExceptFirstAndSecond,

      //Inserts the grid inside the existing sheet, moving all the other columns right (Grid.ColCount - 2) columns.
      //The first two columns will be overwrited. This can be used to export for example inside of a chart.
      InsertInSheet_InsertColsExceptFirstAndSecond

  )  ;  

  TAWGIOOptions = class(TPersistent)
  private
    FImportCellProperties: Boolean;
    FImportFormulas: Boolean;
    FExportCellFormats: Boolean;
    FExportCellProperties: Boolean;
    FExportImages: Boolean;
    FExportReadonlyCellsAsLocked: boolean;    
    FExportWordWrapped: Boolean;
    FExportHTMLTags: Boolean;
    FExportHeader: Boolean;
    FUseExcelStandardColorPalette: Boolean;
  public
    constructor Create;
    procedure Assign(Source: TPersistent); override;
  published
    property ImportFormulas: Boolean read FImportFormulas write FImportFormulas default True;
    property ImportCellProperties: Boolean read FImportCellProperties write FImportCellProperties default False;
    property ExportCellFormats: Boolean read FExportCellFormats write FExportCellFormats default True;
    property ExportCellProperties: Boolean read FExportCellProperties write FExportCellProperties default True;
    property ExportImages: Boolean read FExportImages write FExportImages default False;
    property ExportReadonlyCellsAsLocked: Boolean read FExportReadonlyCellsAsLocked write FExportReadonlyCellsAsLocked default False;
    property ExportWordWrapped: Boolean read FExportWordWrapped write FExportWordWrapped default False;
    property ExportHTMLTags: Boolean read FExportHTMLTags write FExportHTMLTags default True;
    property ExportHeader: Boolean read FExportHeader write FExportHeader default False;

    ///<summary>
    /// When true, the standard Excel color palette will be used while exporting. Excel 97/2003 have only 53 available colors,
    /// and any color that does not match must be replaced with the nearest one. If this property is false,
    /// the Excel color palette will be changed to better display the real grid colors. Note that when you want to
    /// edit the generated file, having a custom palette might make it difficult to find the color you need.
    ///</summary>
    property UseExcelStandardColorPalette: Boolean read FUseExcelStandardColorPalette write FUseExcelStandardColorPalette default True;
  end;

  TAGrid = class(TTIWCustomWebGrid)
  end;


  TTIWAdvWebGridExcelIO = class(TComponent)
  private
    { Private declarations }
    FAdvWebGrid: TTIWCustomWebGrid;
    FAdapter: TExcelAdapter;
    FSheetNames: array of Widestring;
    FGridStartCol: Integer;
    FGridEndCol: Integer;
    FXlsStartCol: Integer;
    FXlsStartRow: Integer;    
    FZoomSaved: Boolean;
    FZoom: Integer;
    FWorkSheet: Integer;
    FWorkSheetNum: Integer;
    FDateFormat: widestring;
    FTimeFormat: widestring;
    FOptions : TAWGIOOptions;
    FOnDateTimeFormat: TFlxFormatCellEvent;
    FOnCellFormat: TFlxFormatCellGenericEvent;
    FOnProgress: TASGIOProgressEvent;
    FOnExportColumnFormat: TExportColumnFormatEvent;

    procedure SetAdvWebGrid(const Value: TTIWCustomWebGrid);
    function CurrentGrid: TAGrid;
    procedure ImportData(const Workbook: TExcelFile);
    procedure ExportData(const Workbook: TExcelFile);
    procedure CloseFile(var Workbook: TExcelFile);
    function CellFormatDef(const Workbook: TExcelFile; const aRow, aCol: integer): TFlxFormat;
    function GetColor(const Workbook: TExcelFile; const Fm: TFlxFormat): TIWColor;
    function GetSheetNames(index: integer): widestring;
    function GetSheetNamesCount: integer;
    procedure SetGridStartCol(const Value: integer);
    procedure SetGridEndCol(const Value: integer);
    procedure SetXlsStartCol(const Value: integer);
    procedure SetXlsStartRow(const Value: integer);
    procedure OpenText(const Workbook: TExcelFile; const FileName: TFileName; const Delimiter: char);
    procedure SetZoom(const Value: integer);
    procedure ExportImage(const Workbook: TExcelFile; const Pic: TGraphic; const rx, cx: integer);
    procedure InternalXLSImport(const FileName: TFileName; const SheetNumber: integer; const SheetName: widestring);
//    function FindSheet(const Workbook: TExcelFile; const SheetName: widestring; var index: integer): boolean;
    procedure SetOptions(const Value: TAWGIOOptions);
    function NearestColorIndex(const Workbook: TExcelFile; const aColor: TColor; const UsedColors: BooleanArray): integer;
    function GetUsedPaletteColors(const Workbook: TExcelFile): BooleanArray;

  protected
    { Protected declarations }
    procedure Notification(AComponent: TComponent; Operation: TOperation); override;
  public
    { Public declarations }
    procedure XLSImport(const FileName: TFileName); overload;
    procedure XLSImport(const FileName: TFileName; const SheetNumber: integer); overload;
    procedure XLSImport(const FileName: TFileName; const SheetName: widestring); overload;
    procedure XLSExport(const FileName: TFileName; WebApplication: TIWApplication = nil;
      const SheetName: widestring = ''; const SheetPos: integer = -1;
      const SelectSheet: integer = 1; const InsertInSheet: TInsertInSheet = InsertInSheet_Clear);

    procedure LoadSheetNames(const FileName: string);
    property SheetNames[index: integer]: widestring read GetSheetNames;
    property SheetNamesCount: integer read GetSheetNamesCount;
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
  published
   { Published declarations }
    //property Adapter: TExcelAdapter read FAdapter write SetAdapter;
    property AdvWebGrid: TTIWCustomWebGrid read FAdvWebGrid write SetAdvWebGrid;

    property Options: TAWGIOOptions read FOptions write SetOptions;

    property GridStartCol: integer read FGridStartCol write SetGridStartCol default -1;
    property GridEndCol: integer read FGridEndCol write SetGridEndCol default -1;
    property XlsStartRow: integer read FXlsStartRow write SetXlsStartRow default 1;
    property XlsStartCol: integer read FXlsStartCol write SetXlsStartCol default 1;

    property ZoomSaved: boolean read FZoomSaved write FZoomSaved default true;
    property Zoom: integer read FZoom write SetZoom default 100;

    property DateFormat: widestring read FDateFormat write FDateFormat;
    property TimeFormat: widestring read FTimeFormat write FTimeFormat;

    //Events

    property OnDateTimeFormat: TFlxFormatCellEvent read FOnDateTimeFormat write FOnDateTimeFormat;
    property OnCellFormat: TFlxFormatCellGenericEvent read FOnCellFormat write FOnCellFormat;
    property OnProgress: TASGIOProgressEvent read FOnProgress write FOnProgress;
    property OnExportColumnFormat: TExportColumnFormatEvent read FOnExportColumnFormat write FOnExportColumnFormat;
    
  end;

procedure Register;

implementation

uses
  StrUtils;

procedure Register;
begin
  RegisterComponents('TMS IW',[TTIWAdvWebGridExcelIO]);
end;

function VarPos(su,s:string;var Res:Integer):Integer;
begin
  Res := Pos(su,s);
  Result := Res;
end;


function HTMLStrip(s:string):string;
var
  TagPos: integer;
begin
  Result := '';
  // replace line breaks by linefeeds
  while (pos('<BR>',uppercase(s))>0) do s := StringReplace(s,'<BR>',chr(13)+chr(10),[rfIgnoreCase]);
  while (pos('<HR>',uppercase(s))>0) do s := StringReplace(s,'<HR>',chr(13)+chr(10),[rfIgnoreCase]);

  {remove all other tags}
  while (VarPos('<',s,TagPos) > 0) do
  begin
    Result := Result + Copy(s,1,TagPos-1);
    if (VarPos('>',s,TagPos)>0) then
      Delete(s,1,TagPos)
    else
      Break;
  end;
  Result := Result + s;
end;


{ TTIWAdvWebGridExcelIO }

function TTIWAdvWebGridExcelIO.CellFormatDef(const Workbook: TExcelFile;
  const aRow, aCol: integer): TFlxFormat;
var
  XF: integer;
begin
  XF := Workbook.CellFormat[aRow, aCol];
  if XF < 0 then
  begin

    XF := Workbook.RowFormat[aRow];
    if XF <= 0 then XF := Workbook.ColumnFormat[aCol];
  end;
  if (XF < 0) then XF := 15;
  Result := Workbook.FormatList[XF];
end;

procedure TTIWAdvWebGridExcelIO.CloseFile(var Workbook: TExcelFile);
begin
  if Workbook = nil then exit;
  try
    Workbook.CloseFile;
  except
    //nothing;
  end; //Except
  try
    Workbook.Disconnect;
  except
    //nothing;
  end; //Except
  FreeAndNil(Workbook);
end;

constructor TTIWAdvWebGridExcelIO.Create(AOwner: TComponent);
begin
  inherited;

  FGridStartCol := -1;
  FGridEndCol := -1;
  FXlsStartCol := 1;
  FXlsStartRow := 1;

  FZoomSaved := true;
  FZoom := 100;

  FOptions := TAWGIOOptions.Create;
end;

function TTIWAdvWebGridExcelIO.CurrentGrid: TAGrid;
begin
  Result := TAGrid(FAdvWebGrid);
end;

destructor TTIWAdvWebGridExcelIO.Destroy;
begin
  FOptions.Free;
  inherited;
end;

procedure TTIWAdvWebGridExcelIO.ExportData(const Workbook: TExcelFile);
var
  rx, cx, rwb, cwb: integer;
  w: UTF16String;
  Fm: TFlxFormat;
  UsedColors: BooleanArray;
  pic: TPicture;
  TempStartCol, TempEndCol, HeaderRow: integer;
  NumRows, RowOffset, PageOffset: integer;
  ExportCellAsString: boolean;
  aDateFormat, aTimeFormat: widestring;
  hasImg: boolean;
begin
  Assert(CurrentGrid <> nil, 'AdvStringGrid can''t be nil');

  TempStartCol := 0;
  if (GridStartCol > 0) and (GridStartCol <= CurrentGrid.Columns.Count)
    and (GridStartCol <= GridEndCol) then
    TempStartCol := GridStartCol;

  TempEndCol := CurrentGrid.Columns.Count;
  if (GridEndCol > 0) and (GridEndCol <= CurrentGrid.Columns.Count)
    and (GridEndCol >= GridStartCol) then
    TempEndCol := GridEndCol;

  UsedColors := GetUsedPaletteColors(Workbook);    

  //Export data
  CurrentGrid.GetRowInfo(NumRows, RowOffset, PageOffset);

  HeaderRow := 0;
  if Options.ExportHeader then
  begin
    HeaderRow := 1;
    for cx := TempStartCol to TempEndCol - 1 do
      Workbook.CellValue[1, cx + 1] := CurrentGrid.Columns[cx].Title;
  end;

  // renderprepare
  CurrentGrid.RenderPrepare;
  // gotorow(0);

  for rx := 0 to NumRows - 1 do
  begin
    CurrentGrid.GotoRecord(rx);

    for cx := TempStartCol to TempEndCol - 1 do
    begin
      rwb := rx + 1 + HeaderRow;
      cwb := cx + 1;

      Fm := CellFormatDef(Workbook, rwb, cwb);

      if (Options.ExportReadonlyCellsAsLocked) then
      begin
        if CurrentGrid.Columns[cx].Editor = edNone then
          Fm.Locked := True
        else
          Fm.Locked := False;
      end;

      hasImg := false;

      if Options.ExportImages then
      begin
        pic := CurrentGrid.GetCellImage(cx, rx);
        if Assigned(Pic) then
        begin
          ExportImage(WorkBook,Pic.Graphic,rwb,cwb);
          hasImg := true;
          pic.Free;
        end;
      end;

      w := '';
      if not hasImg then      
        w := StringReplace(CurrentGrid.GetCellData(cx, rx), '&nbsp;', '', [rfReplaceAll, rfIgnoreCase]);

      if not Options.ExportHTMLTags then
      begin
        w := StringReplace(w,'<br>',#10,[rfReplaceAll, rfIgnoreCase]);
      end;

      if (pos(#10, w) > 0) or ({*CurrentGrid.WordWrap and *} Options.ExportWordWrapped) then
        Fm.WrapText := true;

      if (pos('</',w) > 0) and not Options.ExportHTMLTags then
        w := HTMLStrip(w);

      //Color
      if CurrentGrid.Columns[cx].Color = clNone then
      begin
        //Fm.FillPattern.Pattern := 1; //no fill
      end
      else
      begin
        Fm.FillPattern.Pattern := 2; //Solid fill
        Fm.FillPattern.FgColorIndex := NearestColorIndex(Workbook, ColorToRGB(CurrentGrid.Columns[cx].Color), UsedColors);
      end;

      //Font
      if CurrentGrid.Columns[cx].Font.Color <> ClNone then
        Fm.Font.ColorIndex := NearestColorIndex(Workbook, CurrentGrid.Columns[cx].Font.Color, UsedColors);
      Fm.Font.Size20 := CurrentGrid.Columns[cx].Font.Size * 20;

      if CurrentGrid.Columns[cx].Font.FontFamily <> '' then
        Fm.Font.Name := CurrentGrid.Columns[cx].Font.FontFamily
      else if CurrentGrid.Columns[cx].Font.FontName <> '' then
        Fm.Font.Name := CurrentGrid.Columns[cx].Font.FontName;

      if fsBold in CurrentGrid.Columns[cx].Font.Style then
        Fm.Font.Style := Fm.Font.Style + [flsBold] else Fm.Font.Style := Fm.Font.Style - [flsBold];
      if fsItalic in CurrentGrid.Columns[cx].Font.Style then
        Fm.Font.Style := Fm.Font.Style + [flsItalic] else Fm.Font.Style := Fm.Font.Style - [flsItalic];
      if fsStrikeOut in CurrentGrid.Columns[cx].Font.Style then
        Fm.Font.Style := Fm.Font.Style + [flsStrikeOut] else Fm.Font.Style := Fm.Font.Style - [flsStrikeOut];
      if fsUnderline in CurrentGrid.Columns[cx].Font.Style then
        Fm.Font.Underline := fu_Single else Fm.Font.Underline := fu_None;

      //Cell Align
      case CurrentGrid.Columns[cx].Alignment of
        taLeftJustify: Fm.HAlignment := fha_left;
        taCenter: Fm.HAlignment := fha_center;
        taRightJustify: Fm.HAlignment := fha_right;
      else Fm.HAlignment := fha_general;
      end; //case

      //Cell Format
      if Assigned(OnCellFormat) then
        OnCellFormat(CurrentGrid, cx, rx, cwb, rwb, w, Fm);

      ExportCellAsString := not FOptions.ExportCellFormats;
      if Assigned(OnExportColumnFormat) then OnExportColumnFormat(CurrentGrid, cx, rx, cwb, rwb, w, ExportCellAsString);
      if not ExportCellAsString then
      begin
        aDateFormat := FDateFormat;
        aTimeFormat := FTimeFormat;
        if Assigned(OnDateTimeFormat) then
          OnDateTimeFormat(CurrentGrid, cx, rx, cwb, rwb, w, aDateFormat, aTimeFormat);

        if Options.ExportCellProperties then
          Workbook.SetCellString(rwb, cwb, w, Fm, aDateFormat, aTimeFormat)
        else
        begin
          Fm := CellFormatDef(Workbook, rwb, cwb);
          Workbook.SetCellString(rwb, cwb, w, Fm, aDateFormat, aTimeFormat);
        end;
        //CopyFmToMerged(Workbook, cp, rx, cx, Fm) ;
      end
      else
      begin
        Workbook.CellValue[rwb, cwb] := w;

        if Options.ExportCellProperties then
        begin
          Workbook.CellFormat[rwb, cwb] := Workbook.AddFormat(Fm);
          //CopyFmToMerged(Workbook, cp, rx, cx, Fm) ;
        end;
      end;

//      Workbook.CellValue[rwb, cwb] := w;

//      if Options.ExportCellProperties then
//        Workbook.CellFormat[rwb, cwb] := Workbook.AddFormat(Fm);

      if Assigned(FOnProgress) then
        FOnProgress(Self, FWorksheet, FWorkSheetNum, rx, NumRows - 1);
    end;

    // nextrow;
    CurrentGrid.NextRecord;
  end;

  // renderdone
  CurrentGrid.RenderDone;
end;

procedure TTIWAdvWebGridExcelIO.ExportImage(const Workbook: TExcelFile;
  const Pic: TGraphic; const rx, cx: integer);
//Adapted from FlexCelImport.AddPicture
var
  s: ByteArray;
  Ms: TMemoryStream;
  Props: TImageProperties;
  PicType: TXlsImgTypes;
  JPic: TJPEGImage;
  BPic: Graphics.TBitmap;
  //PSize, CSize: TPoint;
  dh, dw: integer;
begin
  PicType := xli_Jpeg;
{$IFDEF USEPNGLIB}
  if Pic is TPNGObject then PicType := xli_Png;
{$ENDIF}

  Ms := TMemoryStream.Create;
  try
    if (PicType = xli_Jpeg) and not (Pic is TJPEGImage) then
    begin //Convert the image
      JPic := TJPEGImage.Create;
      try
        BPic := Graphics.TBitmap.Create; //we can't assign a metafile to a jpeg, so the temporary bitmap.
        try
          BPic.Width := Pic.Width;
          BPic.Height := Pic.Height;
          BPic.Canvas.Draw(0, 0, Pic);
          JPic.Assign(BPic);
        finally
          FreeAndNil(BPic);
        end; //finally
        JPic.SaveToStream(Ms);
      finally
        FreeAndNil(JPic);
      end; //finally
    end
    else
      Pic.SaveToStream(Ms);

    Ms.Position := 0;
    SetLength(s, Ms.Size);
    Ms.Read(s[1], Ms.Size);
//    PSize := CurrentGrid.CellGraphicSize[cg, rg];
//    CSize := CurrentGrid.CellSize(cg, rg);
    dh := 1;
    dw := 1;

//    CalcImgCells(Workbook, rx, cx, dh, dw, PSize.y, PSize.x, Props);
    CalcImgCells(Workbook, rx, cx, dh, dw, Pic.Height, Pic.Width, Props);
    Workbook.AddImage(s, PicType, Props, at_MoveAndDontResize);
  finally
    FreeAndNil(Ms);
  end; //finally
end;

{
function TTIWAdvWebGridExcelIO.FindSheet(const Workbook: TExcelFile;
  const SheetName: widestring; var index: integer): boolean;
var
  i: integer;
begin
  Result := False;
  for i := 1 to Workbook.SheetCount do
  begin
    Workbook.ActiveSheet := i;
    if (WideUpperCase98(Workbook.ActiveSheetName) = WideUpperCase98(SheetName)) then
    begin
      Result := True;
      Index := i;
      Exit;
    end;
  end;
end;
}

function TTIWAdvWebGridExcelIO.GetColor(const Workbook: TExcelFile;
  const Fm: TFlxFormat): TIWColor;
var
  bc: TColor;
begin
  if Fm.FillPattern.Pattern = 1 then Result := $FFFFFF
  else
  begin
    bc := Fm.FillPattern.FgColorIndex;
    if (bc > 0) and (integer(bc) < 56) then
      Result := Workbook.ColorPalette[bc] else
      Result := $FFFFFF;
  end;
end;

function TTIWAdvWebGridExcelIO.GetSheetNames(index: integer): widestring;
begin
  Result := FSheetNames[index];
end;

function TTIWAdvWebGridExcelIO.GetSheetNamesCount: integer;
begin
  Result := Length(FSheetNames);
end;

function TTIWAdvWebGridExcelIO.GetUsedPaletteColors(
  const Workbook: TExcelFile): BooleanArray;
begin
  if Options.UseExcelStandardColorPalette then
    begin Result := nil; exit; end;

  Result := Workbook.GetUsedPaletteColors;
end;

procedure TTIWAdvWebGridExcelIO.ImportData(const Workbook: TExcelFile);
var
  r, c, rg, cg: integer;
  Fm: TFlxFormat;
  HAlign: TAlignment;
  v: variant;
  TempStartCol {, TempEndCol}: integer;
  NumRows, RowOffset, PageOffset: integer;
  TempVal, Formula: string;
begin
  Assert(CurrentGrid <> nil, 'AdvWebgGrid can''t be nil');

  //not available in DB Grid
  CurrentGrid.ClearCells;

  TempStartCol := 0;
  if (GridStartCol > 0) and (GridStartCol <= CurrentGrid.Columns.Count)
    and (GridStartCol <= GridEndCol) then
    TempStartCol := GridStartCol;

  //TempEndCol := CurrentGrid.Columns.Count;
  //if (GridEndCol > 0) and (GridEndCol <= CurrentGrid.Columns.Count)
  //  and (GridEndCol >= GridStartCol) then
  //  TempEndCol := GridEndCol;

  CurrentGrid.GetRowInfo(NumRows, RowOffset, PageOffset);

  //if Workbook.MaxRow > NumRows + XlsStartRow - 1
  //  then MaxR := NumRows + XlsStartRow - 1 else MaxR := Workbook.MaxRow;

  //if Workbook.MaxCol > TempEndCol + XlsStartCol - 1 - TempStartCol
  //  then MaxC := TempEndCol + XlsStartCol - 1 - TempStartCol else MaxC := Workbook.MaxCol;

  CurrentGrid.UpdateTotalRows(Workbook.MaxRow - XlsStartRow + 1);

  while CurrentGrid.Columns.Count < Workbook.MaxCol - XlsStartCol + 1 + TempStartCol do
    CurrentGrid.Columns.Add.ColumnType := ctNormal;

  while CurrentGrid.Columns.Count > Workbook.MaxCol - XlsStartCol + 1 + TempStartCol do
    CurrentGrid.Columns[CurrentGrid.Columns.Count - 1].Free;

  //Import data
  for r := XlsStartRow to Workbook.MaxRow do
  begin
    rg := r - XlsStartRow;

    CurrentGrid.PostValues.Clear;

    for c := XlsStartCol to Workbook.MaxCol do
    begin
      cg := c + TempStartCol - XlsStartCol;
      Fm := CellFormatDef(Workbook, rg + 1, cg + 1);

      //Font
      if FOptions.ImportCellProperties then
      begin
        if (Fm.Font.ColorIndex > 0) and (integer(Fm.Font.ColorIndex) < 56) then
          CurrentGrid.Columns[cg].Font.Color := Workbook.ColorPalette[Fm.Font.ColorIndex]
        else
          CurrentGrid.Columns[cg].Font.Color := clNone;

        CurrentGrid.Columns[cg].Font.Size := Trunc(Fm.Font.Size20 / 20);

        CurrentGrid.Columns[cg].Font.FontName := Fm.Font.Name;

        if flsBold in Fm.Font.Style then
          CurrentGrid.Columns[cg].Font.Style := CurrentGrid.Columns[cg].Font.Style + [fsBold];
        if flsItalic in Fm.Font.Style then
          CurrentGrid.Columns[cg].Font.Style := CurrentGrid.Columns[cg].Font.Style + [fsItalic];
        if flsStrikeOut in Fm.Font.Style then
          CurrentGrid.Columns[cg].Font.Style := CurrentGrid.Columns[cg].Font.Style + [fsStrikeOut];
        if Fm.Font.Underline <> fu_None then
          CurrentGrid.Columns[cg].Font.Style := CurrentGrid.Columns[cg].Font.Style + [fsUnderline];
      end;

      if FOptions.ImportCellProperties then
      begin
        CurrentGrid.Columns[cg].Color := GetColor(Workbook, Fm);
      end;

      //Cell Align
      v := Workbook.CellValue[rg + 1, cg + 1];
      HAlign := taLeftJustify;

      if FOptions.ImportCellProperties then
      begin
        case Fm.HAlignment of
          fha_left: HAlign := taLeftJustify;
          fha_center: HAlign := taCenter;
          fha_right: HAlign := taRightJustify;
        else
          begin
            if VarType(v) = VarBoolean then HAlign := taCenter else
              if (VarType(v) <> VarOleStr) and (VarType(v) <> VarString) then HAlign := taRightJustify
              else HAlign := taLeftJustify;
          end;
        end; //case
      end;

       //this must be done after reading the alignment, since it depends on the formula value.
      if FOptions.ImportFormulas then
      begin
        Formula := Workbook.CellFormula[r,c];
        if (Pos('=',Formula) = 1) then
          v := Formula;
      end;      

      CurrentGrid.Columns[cg].Alignment := HAlign;

      TempVal := v;
      CurrentGrid.PostValues.Add(TempVal);
    end;

    if not CurrentGrid.IsDBAware then
    begin
      CurrentGrid.GotoRecord(rg);
      CurrentGrid.DoEdit;
    end
    else //Always insert new records when importing to a DBGrid
      CurrentGrid.DoInsert;

    CurrentGrid.DoPost(true);

    if Assigned(FOnProgress) then
      FOnProgress(Self, FWorkSheet, FWorkSheetNum, r - XlsStartRow, Workbook.MaxRow - XlsStartRow);
  end;
end;

procedure TTIWAdvWebGridExcelIO.InternalXLSImport(const FileName: TFileName;
  const SheetNumber: integer; const SheetName: widestring);
var
  Workbook: TExcelFile;
  i: integer;
  Ext: string;
  aSheetNumber: integer;
begin
  aSheetNumber := SheetNumber;

  if CurrentGrid = nil then raise Exception.Create(ErrNoAdvStrGrid);
  //Open the file
  if FAdapter = nil then
    Workbook := TXLSFile.Create(nil) else
    Workbook := FAdapter.GetWorkbook;
  try
    Workbook.Connect;
    Ext := UpperCase(ExtractFileExt(FileName));
    if Ext = '.CSV' then OpenText(Workbook, FileName, ListSeparator) else //Note that ListSeparator mightbe other than "," (for example ";") so CSV might not be "comma" separated. This is the way excel handles it.
      if Ext = '.TXT' then OpenText(Workbook, FileName, #9) else
        Workbook.OpenFile(FileName);

    SetLength(FSheetNames, Workbook.SheetCount);

    FWorkSheetNum := Workbook.SheetCount;

    for i := 0 to Workbook.SheetCount - 1 do
    begin
      FWorkSheet := i + 1;
      Workbook.ActiveSheet := i + 1;
      FSheetNames[i] := Workbook.ActiveSheetName;
      if WideUpperCase98(SheetName) = WideUpperCase98(FSheetNames[i]) then aSheetNumber := i + 1;
    end;

    if aSheetNumber < 1 then
      aSheetNumber := 1;

    FWorkSheetNum := 1;
    FWorkSheet := 1;
    if (aSheetNumber = 0) and (SheetName <> '') then raise Exception.CreateFmt(ErrInvalidSheetName, [SheetName]);
    if (aSheetNumber > 0) and (aSheetNumber <= Workbook.SheetCount) then
    begin
      Workbook.ActiveSheet := aSheetNumber;
      Workbook.SelectSheet(aSheetNumber);
    end
    else raise Exception.CreateFmt(ErrIndexOutBounds, [aSheetNumber, 'ActiveSheet', 1, Workbook.SheetCount]);

    Workbook.ParseComments;
    ImportData(Workbook);

  finally
    CloseFile(Workbook);
  end;
end;

procedure TTIWAdvWebGridExcelIO.LoadSheetNames(const FileName: string);
var
  Workbook: TExcelFile;
  ext: string;
  i: integer;
begin
  if FAdapter = nil then
    Workbook := TXLSFile.Create(nil) else
    Workbook := FAdapter.GetWorkbook;
  try
    Workbook.Connect;
    Ext := UpperCase(ExtractFileExt(FileName));
    if Ext = '.CSV' then OpenText(Workbook, FileName, ListSeparator) else //Note that ListSeparator mightbe other than "," (for example ";") so CSV might not be "comma" separated. This is the way excel handles it.
      if Ext = '.TXT' then OpenText(Workbook, FileName, #9) else
        Workbook.OpenFile(FileName);

    SetLength(FSheetNames, Workbook.SheetCount);
    FWorkSheetNum := Workbook.SheetCount;

    for i := 0 to Workbook.SheetCount - 1 do
    begin
      FWorkSheet := i + 1;
      Workbook.ActiveSheet := i + 1;
      FSheetNames[i] := Workbook.ActiveSheetName;
      end;
  finally
    CloseFile(Workbook);
  end;
end;

function TTIWAdvWebGridExcelIO.NearestColorIndex(const Workbook: TExcelFile;
  const aColor: TColor; const UsedColors: BooleanArray): integer;
type
  TCb= array[0..3] of byte;
var
  i: integer;
  sq, MinSq: extended;
  ac1, ac2: TCb;
  Result2: integer;
begin
  Result:=1;
  MinSq:=-1;
  ac1:=TCb(ColorToRgb(aColor));
  for i:=1 to 55 do
  begin
    ac2:=TCb(Workbook.ColorPalette[i]);
    sq := Sqr(ac2[0] - ac1[0]) +
          Sqr(ac2[1] - ac1[1]) +
          Sqr(ac2[2] - ac1[2]);
    if (MinSq<0) or (sq< MinSq) then
    begin
      MinSq:=sq;
      Result:=i;
      if sq=0 then exit; //exact match...
    end;
  end;

  if (UsedColors = nil) then exit;

  //Find the nearest color between the ones that are not in use.
  UsedColors[0] := true; //not really used
  UsedColors[1] := true; //pure black
  UsedColors[2] := true; //pure white

  Result2:=-1;
  MinSq:=-1;
  for i:=1 to 55 do
  begin
    if (Length(UsedColors) <= i) or UsedColors[i] then continue;

    ac2:=TCb(Workbook.ColorPalette[i]);
    sq := Sqr(ac2[0] - ac1[0]) +
          Sqr(ac2[1] - ac1[1]) +
          Sqr(ac2[2] - ac1[2]);
    if (MinSq<0) or (sq< MinSq) then
    begin
      MinSq:=sq;
      Result2:=i;
      if sq=0 then
      begin
        Result := Result2;
        exit; //exact match...
      end;
    end;
  end;

  if (Result2 < 0) or (Result2 >= Length(UsedColors)) then exit;  //Not available colors to modify
  Workbook.ColorPalette[Result2] := ColorToRGB(aColor);
  UsedColors[Result2] := true;
  Result:= Result2;
end;

procedure TTIWAdvWebGridExcelIO.Notification(AComponent: TComponent;
  Operation: TOperation);
begin
  inherited Notification(AComponent, Operation);
  if Operation = opRemove then
  begin
    if AComponent = FAdapter then
      FAdapter := nil;
    if AComponent = FAdvWebGrid then
      FAdvWebGrid := nil;
  end;
end;

procedure TTIWAdvWebGridExcelIO.OpenText(const Workbook: TExcelFile;
  const FileName: TFileName; const Delimiter: char);
var
  DataStream: TFileStream;
begin
  DataStream := TFileStream.Create(FileName, fmOpenRead);
  try
    Workbook.NewFile;
    LoadFromTextDelim(DataStream, Workbook, Delimiter, 1, 1, []);
  finally
    FreeAndNil(DataStream);
  end; //finally
end;

procedure TTIWAdvWebGridExcelIO.SetAdvWebGrid(const Value: TTIWCustomWebGrid);
begin
  FAdvWebGrid := Value;
end;

procedure TTIWAdvWebGridExcelIO.SetGridStartCol(const Value: integer);
begin
  if Value >= -1 then FGridStartCol := Value else FGridStartCol := -1;
end;

procedure TTIWAdvWebGridExcelIO.SetGridEndCol(const Value: integer);
begin
  if Value >= -1 then FGridEndCol := Value else FGridEndCol := -1;
end;

procedure TTIWAdvWebGridExcelIO.SetOptions(const Value: TAWGIOOptions);
begin
  FOptions := Value;
end;

procedure TTIWAdvWebGridExcelIO.SetXlsStartCol(const Value: integer);
begin
  if Value > 0 then FXlsStartCol := Value else FXlsStartCol := 1;
end;

procedure TTIWAdvWebGridExcelIO.SetXlsStartRow(const Value: integer);
begin
  if Value > 0 then FXlsStartRow := Value else FXlsStartRow := 1;
end;

procedure TTIWAdvWebGridExcelIO.SetZoom(const Value: integer);
begin
  if Value < 10 then FZoom := 10 else if Value > 400 then FZoom := 400 else
    FZoom := Value;
end;

procedure TTIWAdvWebGridExcelIO.XLSExport(const FileName: TFileName;
  WebApplication: TIWApplication; const SheetName: widestring; const SheetPos,
  SelectSheet: integer; const InsertInSheet: TInsertInSheet);
var
  Workbook: TExcelFile;
  TempFileName: string;
//  Sp, dr, dc, rows, cols: integer;
//  GridRowCount, GridColCount: integer;
//  NumRows, RowOffset, PageOffset: integer;
begin
  if CurrentGrid = nil then
    raise Exception.Create(ErrNoAdvStrGrid);

  //Open the file
  if FAdapter = nil then
    Workbook := TXLSFile.Create(nil) else
    Workbook := FAdapter.GetWorkbook;
  try
    TempFileName := TIWServerControllerBase.NewCacheFile('XLS', true);
    TempFileName := ExtractFilePath(TempFileName) + FileName;

    Workbook.Connect;
    {
    if FileExists(FileName) then
    begin
      Workbook.OpenFile(FileName);
      if FindSheet(Workbook, SheetName, Sp) then
      begin
        Workbook.ActiveSheet := Sp;
        case InsertInSheet of
           InsertInSheet_Clear:
             Workbook.ClearSheet;
           InsertInSheet_InsertRows,
           InsertInSheet_InsertRowsExceptFirstAndSecond:
           begin
             CurrentGrid.GetRowInfo(NumRows, RowOffset, PageOffset);
             dr := 0;
             if (InsertInSheet = InsertInSheet_InsertRowsExceptFirstAndSecond) then dr:=1;
             GridRowCount := NumRows;
             rows := GridRowCount - dr * 2;
             if rows > 0 then Workbook.InsertAndCopyRows(Max_Rows + 1, Max_Rows + 1, XlsStartRow + dr, rows ,true);
           end;

           InsertInSheet_InsertCols,
           InsertInSheet_InsertColsExceptFirstAndSecond:
           begin
             dc:=0;
             if (InsertInSheet = InsertInSheet_InsertColsExceptFirstAndSecond) then dc:=1;
             GridColCount := CurrentGrid.Columns.Count;
             cols := GridColCount - GridStartCol - dc * 2;
             if cols > 0 then Workbook.InsertAndCopyRows(Max_Columns + 1, Max_Columns + 1, XlsStartCol + dc, cols ,true);
           end;
        end; //case.
      end
      else
      begin
        if (SheetPos <= 0) or (SheetPos > Workbook.SheetCount) then
          Sp := Workbook.SheetCount + 1 else Sp := SheetPos;
        Workbook.InsertAndCopySheets(-1, Sp, 1);
        Workbook.ActiveSheet := Sp;
      end;
    end
    else
    }
      Workbook.NewFile(1);

    FWorkSheetNum := 1;
    FWorkSheet := 1;

    ExportData(Workbook);

    if SheetName <> '' then Workbook.ActiveSheetName := SheetName;
    if FileExists(TempFileName) then
      DeleteFile(pchar(TempFileName));
    Workbook.SelectSheet(SelectSheet);
    Workbook.Save(true, TempFileName, nil);
  finally
    CloseFile(Workbook);
  end;

  if WebApplication <> nil then
    WebApplication.SendFile(TempFileName);
end;

procedure TTIWAdvWebGridExcelIO.XLSImport(const FileName: TFileName);
begin
  XlsImport(FileName, -1);
end;

procedure TTIWAdvWebGridExcelIO.XLSImport(const FileName: TFileName;
  const SheetName: widestring);
begin
  if SheetName = '' then raise Exception.CreateFmt(ErrInvalidSheetName, [SheetName]);
  InternalXLSImport(FileName, 0, SheetName);
end;

procedure TTIWAdvWebGridExcelIO.XLSImport(const FileName: TFileName;
  const SheetNumber: integer);
begin
  InternalXLSImport(FileName, SheetNumber, '');
end;

{ TAWGIOOptions }

procedure TAWGIOOptions.Assign(Source: TPersistent);
begin
  inherited;
  if Source is TAWGIOOptions then
  begin
    FImportCellProperties := (Source as TAWGIOOptions).ImportCellProperties;
  end;
end;

constructor TAWGIOOptions.Create;
begin
  inherited Create;
  FImportFormulas := True;
  FImportCellProperties := False;
  FExportCellFormats := True;
  FExportCellProperties := True;
  FExportImages := False;
  FExportWordWrapped := False;
  FExportHTMLTags := True;
  FExportHeader := False;
  FUseExcelStandardColorPalette := True;
end;

end.
