读取Excel工作表中的特定列

I have an Excel spreadsheet, but now my problem is that I want to be able to read only specific columns, the columns in the spreadsheet are more than 20, I need to read only 3 columns.

我有一个Excel电子表格,但现在我的问题是我希望能够只读取特定的列,电子表格中的列超过20,我只需要读取3列。

procedure TForm1.sh1(SheetIndex: integer);
Var
  Xlapp1,Xlrange, Sheet:Variant ;
  MaxRow, MaxCol,X, Y:integer ;
  str:string;
  arrData:Variant;
begin
 try
  Str:=trim(form1.OpenDialog1.FileName);

  XLApp1 := createoleobject('excel.application');
  XLApp1.Workbooks.open(Str) ;

  Sheet := XLApp1.WorkSheets[SheetIndex] ;

  MaxRow := Sheet.Usedrange.EntireRow.count ;
  MaxCol := sheet.Usedrange.EntireColumn.count;

  arrData:= Sheet.UsedRange.Value;

  stringgrid1.RowCount:=maxRow+1;
  StringGrid1.ColCount:=maxCol+1;

  for x := 1 to maxCol do
    for y := 1 to maxRow do
     stringgrid1.Cells[x,y]:=arrData[y, x];

  XLApp1.Workbooks.close;
 Except
  on E : Exception do begin
  XLApp1.Workbooks.close;
   ShowMessage(E.Message);
  end;
 end;
end;

1 个解决方案

#1

Here's an example of dynamically retrieving the content of three entire columns (H, I and J) from an Excel spreadsheet. While it's not tailored to your specific example, it should give you the basic concepts of doing so (and cleaning up properly afterward) that you can adapt to your specific needs. I've commented the code to make clear what it's doing.

这是从Excel电子表格动态检索三个完整列(H,I和J)的内容的示例。虽然它不是根据您的具体示例量身定制的,但它应该为您提供这样做的基本概念(以及之后正确清理),以便您能够适应您的特定需求。我已经对代码进行了评论,以明确它正在做什么。

procedure TForm1.Button1Click(Sender: TObject);
var
  Excel, Book, Sheet, Range1: OleVariant;
  i, j: Integer;
  Data: Variant;
const
  // Obtained at https://msdn.microsoft.com/en-us/library/office/ff820880.aspx
  xlDown = -4121;
begin
  Excel := CreateOleObject('Excel.Application');
  try
    Book := Excel.WorkBooks.Open('E:\TempFiles\Test.xlsx');
    Sheet := Book.Worksheets.Item['Sheet1'];

    // Get tne range we want to extract, in this case all rows of columns H-J.
    // .End(xlDown) finds the last used cell in the indicated column
    Range1 := Sheet.Range['H1', Sheet.Range['J1'].End[xlDown]];
    Data := Range1.Value;

    // Get the number of columns and rows from the array itself. The addition
    // of 1 is for the fixed row and column, and to synch up with the Data
    // array being 1 based instead of 0
    StringGrid1.ColCount := VarArrayHighBound(Data, 2) + 1;
    StringGrid1.RowCount := VarArrayHighBound(Data, 1) + 1;

    // Get the number of columns and rows from the array itself.
    // We know that what is being returned is a two dimensional array
    // (rows and columns).
    //
    // Add 1 to allow for the fixed row and column, and to synch up with the Data,
    // where the arrays are 1 based instead of 0
    //
    for i := 1 to StringGrid1.ColCount - 1 do
      for j := 1 to StringGrid1.RowCount - 1 do
        StringGrid1.Cells[i, j] := Data[j, i];

  finally
    // Clean up all references so Excel will close cleanly
    Range1 := Unassigned;
    Sheet := Unassigned;
    Book := Unassigned;
    Excel.Quit;
    Excel := Unassigned;
  end;
end;