Delphi StringGrid 加载excel表格文件内容自动宽度-木庄网络博客

本文整理自网络，侵删。
 unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, Grids, ExtCtrls,ComObj;

type
  TForm1 = class(TForm)
    strngrd1: TStringGrid;
    pb1: TProgressBar;
    btn1: TButton;
    dlgOpen1: TOpenDialog;
    lbl7: TLabel;
    procedure btn1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}
//删除列
procedure delcol(vcol: integer; stringgrid: tstringgrid); stdcall;
var
  i: integer; 
begin 
  if stringgrid.colcount > vcol then 
  begin 
    for i := vcol to stringgrid.colcount - vcol do
      stringgrid.cols[i] := stringgrid.cols[i+1];
      
    stringgrid.colcount := stringgrid.colcount - 1;
  end;
end;

//取得某一列数据的最大长度
function GetColMaxDataLength(ASGrid: TStringGrid; ACol, AStartRow: Integer): Integer;stdcall;
var
   RowIndex: Integer;
   MaxColLength: Integer; //列数据的最大长度
begin
   MaxColLength := 0;
   with ASGrid do
   begin
     //取得列数据的最大长度
     for RowIndex := AStartRow to RowCount - 1 do
     begin
       if length(Cells[ACol, RowIndex]) > MaxColLength then
       begin
         MaxColLength:= length(Cells[ACol, RowIndex]);
       end;
     end;
   end;
   result := MaxColLength;
end;

//根据数据长度自动设置指定列的列宽
procedure SetOneColWidth(ASGrid: TStringGrid; W,ACol: Integer);stdcall;
var
   OneCharPixel: Integer; //一个字符所占的像素数
   RightSpaceWidth: Integer; //右边距空隙
begin
   RightSpaceWidth := W; //设置为3达到和左边距一致的效果
   OneCharPixel := 8; //6对应9号字[*此处最好写成一个根据字号获得像素值的函数*]
   ASGrid.ColWidths[ACol] := GetColMaxDataLength(ASGrid, ACol, 0) * OneCharPixel
   + RightSpaceWidth+10;
end;


//根据数据长度自动设置全部列的列宽
procedure SetAllColWidth(ASGrid: TStringGrid); stdcall;
var
   ColIndex: Integer; //需要设置的列
begin
   for ColIndex := 0 to ASGrid.ColCount - 1 do
   begin
     SetOneColWidth(ASGrid, 10,ColIndex);
   end;
end;

//排序***************************************************************
procedure Quicksort(Grid:TStringGrid; var List:array of integer;
min, max,sortcol,datatype: Integer);
{List is a list of rownumbers in the grid being sorted}
var
  med_value : integer;
  hi, lo, i : Integer;

function compare(val1,val2:string):integer;
var
  int1,int2:integer;
  float1,float2:extended;
  errcode:integer;
begin
  case datatype of
  0: result:=ANSIComparetext(val1,val2);
  1: begin
    int1:=strtointdef(val1,0);
    int2:=strtointdef(val2,0);
    if int1>int2 then result:=1
    else if int1<int2 then result:=-1
    else result:=0;
  end;

  2: begin
    val(val1,float1,errcode);
    if errcode<>0 then float1:=0;
    val(val2,float2,errcode);
    if errcode<>0 then float2:=0;
    if float1>float2 then result:=1
    else if float1<float2 then result:=-1
    else result:=0;
  end;
  else result:=0;
  end;
end;

begin
  {If the list has <= 1 element, it's sorted}
  if (min >= max) then Exit;
  {Pick a dividing item randomly}
  i := min + Trunc(Random(max - min + 1));
  med_value := List[i];
  List[i] := List[min]; { Swap it to the front so we can find it easily}
  {Move the items smaller than this into the left
  half of the list. Move the others into the right}
  lo := min;
  hi := max;
  while (True) do
  begin
  // Look down from hi for a value < med_value.
  while compare(Grid.cells[sortcol,List[hi]]
  ,grid.cells[sortcol,med_value])>=0 do
  (*ANSIComparetext(Grid.cells[sortcol,List[hi]]
  ,grid.cells[sortcol,med_value])>=0 do*)
  begin
    hi := hi - 1;
    if (hi <= lo) then Break;
  end;
  if (hi <= lo) then
  begin {We're done separating the items}
    List[lo] := med_value;
    Break;
  end;

  // Swap the lo and hi values.
  List[lo] := List[hi];
  inc(lo); {Look up from lo for a value >= med_value}
  while Compare(grid.cells[sortcol,List[lo]],
  grid.cells[sortcol,med_value])<0 do
  begin
    inc(lo);
    if (lo >= hi) then break;
  end;
  if (lo >= hi) then
  begin {We're done separating the items}
    lo := hi;
    List[hi] := med_value;
    break;
  end;
  List[hi] := List[lo];
  end;
  {Sort the two sublists}
  Quicksort(Grid,List, min, lo - 1,sortcol,datatype);
  Quicksort(Grid,List, lo + 1, max,sortcol,datatype);
end;

//datatype 0：按字符排序 1：按整型排序 2：按浮点型排序
procedure Sortgrid(Grid : TStringGrid; sortcol,datatype:integer);
var
  i : integer;
  tempgrid:tstringGrid;
  list:array of integer;
begin
  screen.cursor:=crhourglass;
  tempgrid:=TStringgrid.create(nil);
  with tempgrid do
  begin
    rowcount:=grid.rowcount;
    colcount:=grid.colcount;
    fixedrows:=grid.fixedrows;
  end;

  with Grid do
  begin
    setlength(list,rowcount-fixedrows);
    for i:= fixedrows to rowcount-1 do
    begin
      list[i-fixedrows]:=i;
      tempgrid.rows[i].assign(grid.rows[i]);
    end;
    quicksort(Grid, list,0,rowcount-fixedrows-1,sortcol,datatype);
    for i:=0 to rowcount-fixedrows-1 do
    begin
      rows[i+fixedrows].assign(tempgrid.rows[list[i]])
    end;
    row:=fixedrows;
  end;
  tempgrid.free;
  setlength(list,0);
  screen.cursor:=crdefault;
end;
//排序结束*********************************************************************


procedure TForm1.btn1Click(Sender: TObject);
var
  excelx:string;
  ExcelApp:Variant;
  workBook:OleVariant;
  excelRowCount,excelColCount:longint;
  i,j:integer;
begin
  pb1.Min:=0;
  dlgOpen1.Filter:='Excel文件|*.xls|*.xlsx';
  if dlgOpen1.Execute then
    begin
      try
        ExcelApp:=CreateOleObject('Excel.Application');
        workBook:=ExcelApp.WorkBooks.open(dlgOpen1.FileName);
        ExcelApp.visible:=False;

        excelRowCount:=ExcelApp.ActiveSheet.UsedRange.Rows.count;// 行数
        excelColCount:=ExcelApp.ActiveSheet.UsedRange.Columns.Count; // 列数

        strngrd1.RowCount :=  excelRowCount;
        strngrd1.ColCount :=  excelColCount;


        pb1.Max:=excelRowCount;

        for i:=0 to strngrd1.RowCount-1 do
        begin
         pb1.StepBy(1);
          for j:=0 to strngrd1.ColCount-1 do

          begin


            excelx:=excelapp.cells[i+1,j+1].value;
            if (excelx<>'') then
            strngrd1.Cells[j,i]:=excelx else
            strngrd1.Cells[j,i]:='0'
           end;
        end;
        pb1.Position:=0;
        strngrd1.Cols[6]:=strngrd1.Cols[7];
        delcol(7,strngrd1);
        SetAllColWidth(strngrd1);
   //     ComboBox1.Items.Text:=strngrd1.Rows[0].Text;
   //     ComboBox1.Items.Delete(0);
        lbl7.Caption:='共有：'+inttostr(excelRowCount-1)+'  条记录';
       
      finally
        workBook.Close;
        ExcelApp.quit;
        ExcelApp := Unassigned;
        WorkBook := Unassigned;
      end;

    end;

end;

end.
Delphi StringGrid 加载excel表格文件内容自动宽度

相关推荐

评论
