Accessing Cell Content Type [SOLVED] lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Badger · 2020-06-15 04:14 There is obviously an easy way to do this - I just can't find it! I need to transfer the cell data from an FPSSpreadsheet as a string into a normal stringgrid when the Spreadsheet cells hold various formats.  If I use VarToStr on a cell formatted as a date, the program gives the numerical TDate as a string ('12356')  If I test cells for date format by using try..DecodeDate(... the program reads integers as dates and returns xx/xx/xxxx.  I need a way to find out the cell's content type but I can't find a way to access this data. My only solution to date is to focus each cell then read the TsCellEdit but this is very slow and looks terrible!!!  Can anyone help? ---------------------------------------------------------------------- wp · 2020-06-15 08:41 Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i uses  fpSpreadsheet, fpsTypes, fpsUtils, fpsAllFormats; procedure TForm1.WorksheetToGrid(AFileName: String; ASheetIndex: Integer; ADateFormat: String);var  book: TsWorkbook;  sheet: TsWorksheet;  cell: PCell;  row, col: Integer;begin  book := TsWorkbook.Create;  try    book.ReadFromFile(AFileName);    sheet := book.GetWorksheetByIndex(ASheetIndex);    StringGrid1.RowCount := sheet.GetLastRowIndex + 2;    StringGrid1.ColCount := sheet.GetLastColIndex + 2;    for cell in sheet.Cells do begin      row := cell^.Row + StringGrid1.FixedRows;      col := cell^.Col + StringGrid1.FixedCols;      case cell^.ContentType of        cctNumber: StringGrid1.Cells[col, row] := FloattoStr(cell^.NumberValue);        cctDateTime: StringGrid1.Cells[col, row] := FormatDateTime(ADateFormat, cell^.DateTimeValue);        cctUTF8String: StringGrid1.Cells[col, row] := cell^.UTF8StringValue;        cctBool: StringGrid1.cells[col, row] := BoolToStr(cell^.BoolValue, true);        cctEmpty: StringGrid1.Cells[col, row] := '';        cctError: StringGrid1.Cells[col, row] := GetErrorValueStr(TsErrorValue(cell^.ErrorValue))      end;    end;  finally    book.Free;  end;end; procedure TForm1.Button1Click(Sender: TObject);begin  WorksheetToGrid('testfile.xlsx', 0, 'yyyy_mm_dd');end; ---------------------------------------------------------------------- Badger · 2020-06-16 01:43 Thanks for that - I missed the need to access through PCell. Just 2 additional questions:- 1.   why the "+2" in lines 15 and 16? 2.  Could you confirm the relationship between WorkBook and WorkBookSource .  Having finally had the sense to read the help file in the spread sheet download, I understand the workbooksource is the interface for the visual components.  There is no WorkBook on the spreadsheet pallet, so if I want a workbook I can only create it at run time?? Is it an either/or relationship between the workbook and the workbooksource or can you have both working together?  If so, how do you link the workbooksource to the workbook? ---------------------------------------------------------------------- wp · 2020-06-16 11:02 Quote from: Badger on June 16, 2020, 03:43:55 am1.   why the "+2" in lines 15 and 16? Worksheet.GetLastRowIndex returns the index of the last row. Like in every zero-base array/list, this number must be incremented by 1 to get the count of rows. And in the StringGrid, where the fixed header row already occupies one row another 1 must be added in order to get the row count needed by the grid. Quote from: Badger on June 16, 2020, 03:43:55 am2.  Could you confirm the relationship between WorkBook and WorkBookSource .  Having finally had the sense to read the help file in the spread sheet download, I understand the workbooksource is the interface for the visual components.  There is no WorkBook on the spreadsheet pallet, so if I want a workbook I can only create it at run time?? Is it an either/or relationship between the workbook and the workbooksource or can you have both working together?  If so, how do you link the workbooksource to the workbook? The entire fpspreadsheet library consists of two parts: the data- related units in package laz_fpspreadsheet, and the visual componets in package laz_fpspreadsheet_visual (runtime) and laz_fpspreadsheet_visual_dsgn (designtime). The central object in the former package is the TsWorkbook. It is some kind of three-dimensional data storage, two dimensions for rows and columns, and the third dimension for the sheets. The central object in the latter package is the TsWorkbookSource. It interfaces the visual components (grid, cell edit, etc) with a workbook. You can access the workbook loaded into a WorkbookSource as WorkbookSouce.Workbook, and you can access the currently active worksheet as WorkbookSource.Worksheet. The visual components do not make sense if you only want to read a file and do some evaluation/data extraction etc. In this case my previous code is well-suited. However, if you also want to "see" the file you can use die following, modified code: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.WorksheetToGrid(AWorksheet: TsWorksheet; ADateFormat: String);var  cell: PCell;  row, col: Integer;begin    StringGrid1.RowCount := AWorksheet.GetLastRowIndex + 2;    StringGrid1.ColCount := AWorksheet.GetLastColIndex + 2;    for cell in AWorksheet.Cells do begin      row := cell^.Row + StringGrid1.FixedRows;      col := cell^.Col + StringGrid1.FixedCols;      case cell^.ContentType of        cctNumber: StringGrid1.Cells[col, row] := FloatToStr(cell^.NumberValue);        cctDateTime: StringGrid1.Cells[col, row] := FormatDateTime(ADateFormat, cell^.DateTimeValue);        cctUTF8String: StringGrid1.Cells[col, row] := cell^.UTF8StringValue;        cctBool: StringGrid1.cells[col, row] := BoolToStr(cell^.BoolValue, true);        cctEmpty: StringGrid1.Cells[col, row] := '';        cctError: StringGrid1.Cells[col, row] := GetErrorValueStr(TsErrorValue(cell^.ErrorValue))      end;    end;end; procedure TForm1.Button1Click(Sender: TObject);begin  WorksheetToGrid(WorkbookSource.Worksheet, 'yyyy_mm_dd');end; ---------------------------------------------------------------------- Badger · 2020-06-17 07:12 Works perfectly.  Thank you for your time and patience.