Reading specific columns in Excel Sheet

Question

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.

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;

What is stopping you from reading the columns that you wish to read? — David Heffernan, Mar 30 '17 at 11:50
This code reads every row and column. What have you tried to read just the 3 columns you need? It would seem like a simple programming task in most any language... what is going wrong? — Frazz, Mar 30 '17 at 11:50
I am loading this data in a StringGrid, my challenge is that I don't want to be looping through each and every column — Alec, Mar 30 '17 at 11:59
"I don't want to be looping through each and every column" So what's stopping you setting your `maxCol` to 3? — MartynA, Mar 30 '17 at 12:21
I can't set MaxCol to 3 because this would loop through to only the first 3 columns, and this is not what I need — Alec, Mar 30 '17 at 12:27
Define each column you want to read, as a range, and transfer each range in one go. — Tom Brunberg, Mar 30 '17 at 12:38
@Tom this sounds more like what I need, can you please demonstrate this... — Alec, Mar 30 '17 at 12:40
I'm sure there's a q/a on the topic already. Search "copy excel range to delphi" — Tom Brunberg, Mar 30 '17 at 12:44
@Ken White, thats exactly what I want, instead of people being negative. I did not know about the range — Alec, Mar 30 '17 at 14:06
I have made a tiny edit to your q, just so that I could remove my previous downvote, as I rediscovered it while googling for an answer to smth. — MartynA, May 05 '18 at 16:39

Ken White · Accepted Answer · 2017-03-30T19:44:05.717

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.

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;

thanks, works perfect, just what I needed, language can be a barrier at times, but others wont see that. — Alec, Mar 30 '17 at 18:24
Instead of having a range of H-J, what if you want column B, N and O instead? I have tried this but no luck Range1 := Sheet.Range['B1,N1,O1']; — Alec, Mar 31 '17 at 07:29
@Fero68: That would be a distinctly separate question. This one has been answered. Of course, a somewhat simple solution is simply to use three separate ranges, since they're not contiguous. A valid range can be as small as one cell, or a column, or a partial column, or a row. — Ken White, Mar 31 '17 at 12:31
@KenWhite. Bravo! I was just trying to remind myself how to do something similar in Excel, and this was the top google hit. +1 — MartynA, May 05 '18 at 08:24
@MartynA: Glad to help. In case you need it, you can find the reverse operation (StringGrid to Excel) in [this answer](https://stackoverflow.com/a/16642049/62576) — Ken White, May 05 '18 at 15:13

Reading specific columns in Excel Sheet

1 Answers1