Posts: 1,215
Threads: 162
Joined: Apr 2022
Reputation:
34
01-26-2026, 06:49 PM
(This post was last modified: 01-26-2026, 06:56 PM by madscijr.)
Excel has long been a favorite tool of mine for working with simple lists & tabulated data.
For personal use, I don't usually need the fancy connecting to database servers with power query or even pivot tables. It's just very useful to have a grid that you can easily copy & paste to & from a basic table in Word (or a rich text editor), tab-delimited text in Notepad, or from HTML tables, and then play with using Excel's formulas and my own VBA macros.
Anyway, it's not broke (yet) but with all the annoying and constant changes Microsoft is determined to force on us (the latest annoyance being the macro recorder now records "Office scripts" by default instead of the standard VBA macros, which although you can disable it, is just another unwanted change and a sign that MS is moving to phase BASIC out of their products (vbscript is already on the chopping block)). Also the cost of Office means you can't share this stuff with someone if they don't have a subscription.
So I started thinking about alternatatives. You have Google Sheets, which is Web-based and depends on their whole ecosystem and doesn't use a BASIC dialect for writing macros. Not to mention they now are using our data to train their ai and for whatever purpose they want. Hard pass.
The obvious choice would be QB64PE!
Has anyone played around with implementing an Excel-like grid, that can hold a large number of rows/columns comparable to Excel, that you can select cells in, which can be cut, copied & pasted with the standard Ctrl-x, Ctrl-c, Ctrl-v, to & from say, a Word table, or from an HTML table, or to/from Notepad as tab-delimited text? The next step would be implementing some basic formulas, which would just be QB64PE functions.
Any thoughts? Is this something that could be done with InForm? (I have yet to dive into that yet.)
If this is ridiculously hard then so be it, but I thought I would ask...
Much appreciated
Posts: 513
Threads: 65
Joined: May 2022
Reputation:
83
01-26-2026, 07:08 PM
(This post was last modified: 01-26-2026, 07:21 PM by Petr.)
In QB64PE. Yeah. And who will maintain it and make it compatible? XLS and XLSX is proprietary file format. You have many alternatives that are free and do exactly the same job as Excel and Word, plus you have it in one package, with support and can even process XLS and XLSX files. Check out libreoffice.org
But if you really want to write a micro excel in qb64pe, there is nothing easier! Create a 2D array. And then cell B3 which in Excel would be: B3 = (A1 + C7) can easily be written in QB64PE as Cell(2,3) = Cell (1,1) + Cell (3,7) if A = 1, B = 2, C = 3 and so on.
Of course! This array must carry a lot of other information! The data type (is it a date, an integer, a decimal number, text, or a symbol...; the type of font used, border information and many other things!)
Posts: 4,692
Threads: 222
Joined: Apr 2022
Reputation:
322
Interesting Challenge! I like.
724 855 599 923 575 468 400 206 147 564 878 823 652 556 bxor cross forever
Posts: 485
Threads: 24
Joined: May 2022
Reputation:
91
Ooh, Office Scripts definitely look a lot like JavaScript to me. Microsoft hasn't updated VBA in ages, so I wouldn't be surprised if it ends up on the chopping block at some point. On the Windows side, VBScript is already being phased out, and it seems like only JScript will remain for compatibility reasons. They've also been neglecting VB.NET for years now, with nearly all focus shifting to C#.
So it really does seem like Microsoft is deliberately distancing itself from BASIC‑style languages. It's sad, but probably just a business decision in the end.
Posts: 1,215
Threads: 162
Joined: Apr 2022
Reputation:
34
01-26-2026, 08:13 PM
(This post was last modified: 01-26-2026, 09:34 PM by madscijr.)
@Petr I'm not asking for it to be able to save to Excel file formats, but the core functionality of a programmable data grid that supports the clipboard functions, formulas with basic math operations (add, subtract, multiply, divide) and concatenate string data, Excel-like cell references like A1 or $A$1, ability to format a cell (# of decimal places for numbers, choose date format, etc.), and for anything more complex, call a built in or custom QB64PE function, which could receive a value or a cell address (or range of cell addresses) to operate on. For example a SUM function such as =SUM(A2:A5). Bonus if you can define named ranges and double bonus if you can define a dropdown control in a cell that pulls its values from a given range of cells on a given sheet.
I get that it's a tall order but beyond being a poor man's spreadsheet, there is value in this being a reusable programmable datagrid type control in your own QB64 programs.
Posts: 513
Threads: 65
Joined: May 2022
Reputation:
83
Code: (Select All)
Option _Explicit
' ============================================================
' MiniSheet GUI 10x10 for QB64PE
' Clickable cells, in-place editing, simple formulas:
' =A1 + D5*3
' Supports + - * / and parentheses.
' Text is allowed but treated as 0 in calculations.
' No formatting features (fonts/borders options/etc.).
' ============================================================
Const RMAX% = 10
Const CMAX% = 10
' --- UI layout (pixels) ---
Const WINW% = 980
Const WINH% = 420
Const X0% = 70 ' grid origin x (top-left of cell A1)
Const Y0% = 60 ' grid origin y
Const CELLW% = 85
Const CELLH% = 28
Const HDRH% = 24 ' header height area
Const HDRW% = 50 ' header width area
Const EDITY% = 360 ' editor/status line y
Dim Shared cellIn$(RMAX%, CMAX%)
Dim Shared cellVal!(RMAX%, CMAX%)
Dim Shared state%(RMAX%, CMAX%)
Dim Shared laster$
Dim Shared selR%, selC%
Dim Shared editing%
Dim Shared editText$
Dim Shared prevMouse1%
Dim Shared quit%
Screen _NewImage(WINW%, WINH%, 32)
_Title "MiniSheet GUI (QB64PE) - Click cells, type values/formulas"
InitGrid
MainLoop
End
'init
Sub InitGrid
Dim r%, c%
laster$ = ""
For r% = 1 To RMAX%
For c% = 1 To CMAX%
cellIn$(r%, c%) = ""
cellVal!(r%, c%) = 0!
state%(r%, c%) = 0
Next c%
Next r%
selR% = 1
selC% = 1
editing% = 0
editText$ = ""
prevMouse1% = 0
quit% = 0
End Sub
Sub MainLoop
Do While quit% = 0
_Limit 60
HandleMouse
HandleKeys
DrawUI
Loop
End Sub
Sub HandleMouse
Dim mx%, my%, m1%
Dim r%, c%
Do While _MouseInput
mx% = _MouseX
my% = _MouseY
m1% = _MouseButton(1)
' Detect left-click rising edge
If m1% <> 0 And prevMouse1% = 0 Then
If InGrid%(mx%, my%) Then
MouseToCell mx%, my%, r%, c%
selR% = r%
selC% = c%
' Start editing on click
editing% = -1
editText$ = cellIn$(selR%, selC%)
End If
End If
prevMouse1% = m1%
Loop
End Sub
Function InGrid% (mx%, my%)
Dim gx1%, gy1%, gx2%, gy2%
gx1% = X0%
gy1% = Y0%
gx2% = X0% + CMAX% * CELLW%
gy2% = Y0% + RMAX% * CELLH%
If mx% >= gx1% And mx% < gx2% And my% >= gy1% And my% < gy2% Then
InGrid% = -1
Else
InGrid% = 0
End If
End Function
Sub MouseToCell (mx%, my%, r%, c%)
Dim dx%, dy%
dx% = mx% - X0%
dy% = my% - Y0%
c% = (dx% \ CELLW%) + 1
r% = (dy% \ CELLH%) + 1
If c% < 1 Then c% = 1
If c% > CMAX% Then c% = CMAX%
If r% < 1 Then r% = 1
If r% > RMAX% Then r% = RMAX%
End Sub
'keyboard
Sub HandleKeys
Dim k%
Dim ch$
Dim raw$
k% = _KeyHit
If k% = 0 Then Exit Sub
If editing% <> 0 Then
' Editing mode: build editText$
If k% = 27 Then
' ESC cancels edit
editing% = 0
editText$ = ""
Exit Sub
End If
If k% = 13 Then
' ENTER commits
raw$ = TrimAll$(editText$)
cellIn$(selR%, selC%) = Unquote$(raw$)
editing% = 0
editText$ = ""
Exit Sub
End If
If k% = 8 Then
' BACKSPACE
If Len(editText$) > 0 Then editText$ = Left$(editText$, Len(editText$) - 1)
Exit Sub
End If
' Printable ASCII only (basic)
If k% >= 32 And k% <= 126 Then
ch$ = Chr$(k%)
editText$ = editText$ + ch$
End If
Else
' Not editing: ESC quits
If k% = 27 Then quit% = -1
End If
End Sub
'ui
Sub DrawUI
Cls
DrawHeaders
DrawGrid
DrawEditor
_Display
End Sub
Sub DrawHeaders
Dim c%, r%
Dim x%, y%
Dim s$
' Column letters
For c% = 1 To CMAX%
x% = X0% + (c% - 1) * CELLW% + 6
y% = Y0% - HDRH%
s$ = ColToLetter$(c%)
_PrintString (x%, y%), s$
Next c%
' Row numbers
For r% = 1 To RMAX%
x% = X0% - HDRW%
y% = Y0% + (r% - 1) * CELLH% + 6
s$ = LTrim$(Str$(r%))
_PrintString (x%, y%), s$
Next r%
End Sub
Sub DrawGrid
Dim r%, c%
Dim x1%, y1%, x2%, y2%
Dim raw$, show$
Dim v!
Dim wChars%
laster$ = ""
ClearStates
' Simple grid lines
For c% = 0 To CMAX%
x1% = X0% + c% * CELLW%
y1% = Y0%
x2% = x1%
y2% = Y0% + RMAX% * CELLH%
Line (x1%, y1%)-(x2%, y2%), _RGB32(120, 120, 120)
Next c%
For r% = 0 To RMAX%
x1% = X0%
y1% = Y0% + r% * CELLH%
x2% = X0% + CMAX% * CELLW%
y2% = y1%
Line (x1%, y1%)-(x2%, y2%), _RGB32(120, 120, 120)
Next r%
' Selected cell highlight (just a soft fill so you see where you are)
x1% = X0% + (selC% - 1) * CELLW% + 1
y1% = Y0% + (selR% - 1) * CELLH% + 1
x2% = x1% + CELLW% - 2
y2% = y1% + CELLH% - 2
Line (x1%, y1%)-(x2%, y2%), _RGB32(220, 235, 255), BF
' Cell contents
wChars% = 10 ' rough truncation count for default font
For r% = 1 To RMAX%
For c% = 1 To CMAX%
raw$ = TrimAll$(cellIn$(r%, c%))
show$ = ""
If raw$ <> "" Then
If Left$(raw$, 1) = "=" Then
v! = EvalCell!(r%, c%)
show$ = LTrim$(Str$(v!))
ElseIf IsPlainNumber%(raw$) Then
show$ = raw$
Else
' text
show$ = raw$
End If
End If
If Len(show$) > wChars% Then show$ = Left$(show$, wChars%)
x1% = X0% + (c% - 1) * CELLW% + 4
y1% = Y0% + (r% - 1) * CELLH% + 6
_PrintString (x1%, y1%), show$
Next c%
Next r%
End Sub
Sub DrawEditor
Dim s1$, s2$, s3$
s1$ = "Selected: " + CellName$(selR%, selC%)
s2$ = "Click a cell to edit. Enter=commit, Esc=cancel. Esc (when not editing)=quit."
s3$ = "Input: "
_PrintString (20, EDITY% - 26), s1$
_PrintString (20, EDITY% - 10), s2$
If editing% <> 0 Then
_PrintString (20, EDITY% + 10), s3$ + editText$
Else
_PrintString (20, EDITY% + 10), s3$ + cellIn$(selR%, selC%)
End If
If laster$ <> "" Then
_PrintString (20, EDITY% + 30), "Note: " + laster$
End If
End Sub
Sub ClearStates
Dim r%, c%
For r% = 1 To RMAX%
For c% = 1 To CMAX%
state%(r%, c%) = 0
Next c%
Next r%
End Sub
' Helpers
Function ColToLetter$ (c%)
ColToLetter$ = Chr$(Asc("A") + c% - 1)
End Function
Function CellName$ (r%, c%)
CellName$ = ColToLetter$(c%) + LTrim$(Str$(r%))
End Function
Function TrimAll$ (s$)
TrimAll$ = LTrim$(RTrim$(s$))
End Function
Function Unquote$ (s$)
Dim t$
t$ = TrimAll$(s$)
If Len(t$) >= 2 Then
If Left$(t$, 1) = Chr$(34) And Right$(t$, 1) = Chr$(34) Then
Unquote$ = Mid$(t$, 2, Len(t$) - 2)
Exit Function
End If
End If
Unquote$ = t$
End Function
' Evaluation / Parser
Function EvalCell! (r%, c%)
Dim raw$, s$
Dim idx%, er%
Dim v!
raw$ = TrimAll$(cellIn$(r%, c%))
If state%(r%, c%) = 2 Then
EvalCell! = cellVal!(r%, c%)
Exit Function
End If
If state%(r%, c%) = 1 Then
laster$ = "Cyclic reference detected. Returning 0 for cycle."
EvalCell! = 0!
Exit Function
End If
state%(r%, c%) = 1
If raw$ = "" Then
v! = 0!
ElseIf Left$(raw$, 1) = "=" Then
s$ = Mid$(raw$, 2)
idx% = 1
er% = 0
v! = ParseExpr!(s$, idx%, er%)
SkipSpaces s$, idx%
If er% <> 0 Or idx% <= Len(s$) Then
laster$ = "Formula eror in " + CellName$(r%, c%) + ". Returning 0."
v! = 0!
End If
ElseIf IsPlainNumber%(raw$) Then
idx% = 1
er% = 0
v! = ReadNumber!(raw$, idx%, er%)
If er% <> 0 Then v! = 0!
Else
' text counts as 0 in calculations
v! = 0!
End If
cellVal!(r%, c%) = v!
state%(r%, c%) = 2
EvalCell! = v!
End Function
Function ParseExpr! (s$, idx%, er%)
Dim v!, t!
Dim op$
v! = ParseTerm!(s$, idx%, er%)
If er% <> 0 Then ParseExpr! = 0!: Exit Function
Do
SkipSpaces s$, idx%
If idx% > Len(s$) Then Exit Do
op$ = Mid$(s$, idx%, 1)
If op$ <> "+" And op$ <> "-" Then Exit Do
idx% = idx% + 1
t! = ParseTerm!(s$, idx%, er%)
If er% <> 0 Then ParseExpr! = 0!: Exit Function
If op$ = "+" Then v! = v! + t! Else v! = v! - t!
Loop
ParseExpr! = v!
End Function
Function ParseTerm! (s$, idx%, er%)
Dim v!, f!
Dim op$
v! = ParseFactor!(s$, idx%, er%)
If er% <> 0 Then ParseTerm! = 0!: Exit Function
Do
SkipSpaces s$, idx%
If idx% > Len(s$) Then Exit Do
op$ = Mid$(s$, idx%, 1)
If op$ <> "*" And op$ <> "/" Then Exit Do
idx% = idx% + 1
f! = ParseFactor!(s$, idx%, er%)
If er% <> 0 Then ParseTerm! = 0!: Exit Function
If op$ = "*" Then
v! = v! * f!
Else
If f! = 0! Then
laster$ = "Division by zero. Returning 0."
v! = 0!
Else
v! = v! / f!
End If
End If
Loop
ParseTerm! = v!
End Function
Function ParseFactor! (s$, idx%, er%)
Dim ch$, sign!, v!
Dim cch$, col%, row%
Dim cell$, start%
SkipSpaces s$, idx%
If idx% > Len(s$) Then er% = 1: ParseFactor! = 0!: Exit Function
ch$ = Mid$(s$, idx%, 1)
' Unary +/-
If ch$ = "+" Or ch$ = "-" Then
If ch$ = "-" Then sign! = -1! Else sign! = 1!
idx% = idx% + 1
v! = ParseFactor!(s$, idx%, er%)
ParseFactor! = sign! * v!
Exit Function
End If
' Parentheses
If ch$ = "(" Then
idx% = idx% + 1
v! = ParseExpr!(s$, idx%, er%)
SkipSpaces s$, idx%
If idx% > Len(s$) Or Mid$(s$, idx%, 1) <> ")" Then
er% = 1
ParseFactor! = 0!
Exit Function
End If
idx% = idx% + 1
ParseFactor! = v!
Exit Function
End If
' Cell ref: A1..J10
If IsAlpha%(ch$) Then
start% = idx%
idx% = idx% + 1
Do While idx% <= Len(s$)
cch$ = Mid$(s$, idx%, 1)
If IsDigit%(cch$) = 0 Then Exit Do
idx% = idx% + 1
Loop
cell$ = UCase$(Mid$(s$, start%, idx% - start%))
If CellOK%(cell$, row%, col%) = 0 Then
er% = 1
ParseFactor! = 0!
Exit Function
End If
ParseFactor! = EvalCell!(row%, col%)
Exit Function
End If
' Number
v! = ReadNumber!(s$, idx%, er%)
ParseFactor! = v!
End Function
Sub SkipSpaces (s$, idx%)
Dim ch$
Do While idx% <= Len(s$)
ch$ = Mid$(s$, idx%, 1)
If ch$ <> " " And ch$ <> Chr$(9) Then Exit Do
idx% = idx% + 1
Loop
End Sub
Function ReadNumber! (s$, idx%, er%)
Dim start%, ch$, dotCount%, num$
SkipSpaces s$, idx%
If idx% > Len(s$) Then er% = 1: ReadNumber! = 0!: Exit Function
start% = idx%
dotCount% = 0
Do While idx% <= Len(s$)
ch$ = Mid$(s$, idx%, 1)
If IsDigit%(ch$) Then
idx% = idx% + 1
ElseIf ch$ = "." Then
dotCount% = dotCount% + 1
If dotCount% > 1 Then Exit Do
idx% = idx% + 1
Else
Exit Do
End If
Loop
num$ = Mid$(s$, start%, idx% - start%)
If num$ = "" Or num$ = "." Then
er% = 1
ReadNumber! = 0!
Exit Function
End If
ReadNumber! = CSng(Val(num$))
End Function
Function IsPlainNumber% (s$)
Dim idx%, er%
Dim v!
s$ = TrimAll$(s$)
If s$ = "" Then IsPlainNumber% = 0: Exit Function
idx% = 1
er% = 0
v! = ReadNumber!(s$, idx%, er%)
SkipSpaces s$, idx%
If er% <> 0 Then
IsPlainNumber% = 0
ElseIf idx% <= Len(s$) Then
IsPlainNumber% = 0
Else
IsPlainNumber% = -1
End If
End Function
Function IsAlpha% (ch$)
Dim a%
If ch$ = "" Then IsAlpha% = 0: Exit Function
a% = Asc(UCase$(Left$(ch$, 1)))
If a% >= Asc("A") And a% <= Asc("Z") Then IsAlpha% = -1 Else IsAlpha% = 0
End Function
Function IsDigit% (ch$)
Dim a%
If ch$ = "" Then IsDigit% = 0: Exit Function
a% = Asc(Left$(ch$, 1))
If a% >= Asc("0") And a% <= Asc("9") Then IsDigit% = -1 Else IsDigit% = 0
End Function
Function LetterToCol% (ch$)
Dim a%
ch$ = UCase$(ch$)
a% = Asc(ch$)
If a% < Asc("A") Or a% > Asc("Z") Then LetterToCol% = 0: Exit Function
LetterToCol% = (a% - Asc("A")) + 1
End Function
Function CellOK% (cell$, r%, c%)
Dim ch$, num$, i%, d$
Dim rr%
cell$ = TrimAll$(cell$)
If Len(cell$) < 2 Or Len(cell$) > 3 Then CellOK% = 0: Exit Function
ch$ = Mid$(cell$, 1, 1)
c% = LetterToCol%(ch$)
If c% < 1 Or c% > CMAX% Then CellOK% = 0: Exit Function
num$ = Mid$(cell$, 2)
If num$ = "" Then CellOK% = 0: Exit Function
For i% = 1 To Len(num$)
d$ = Mid$(num$, i%, 1)
If IsDigit%(d$) = 0 Then CellOK% = 0: Exit Function
Next i%
rr% = 0
For i% = 1 To Len(num$)
rr% = rr% * 10 + (Asc(Mid$(num$, i%, 1)) - Asc("0"))
Next i%
If rr% < 1 Or rr% > RMAX% Then CellOK% = 0: Exit Function
r% = rr%
CellOK% = -1
End Function
Posts: 1,215
Threads: 162
Joined: Apr 2022
Reputation:
34
01-26-2026, 10:04 PM
(This post was last modified: 01-26-2026, 10:07 PM by madscijr.)
@Petr wow, I thought this would be possible, but not quite so fast! 
Will have to play with the code later to add some UI stuff (tab & arrow keys, CTRL-X,C,V cut/copy/paste to/from clipboard, selecting a range with the mouse, sheets larger than the screen with scrolling, etc), load + save, simple formulas that operate on a range like =SUM(A2:A5), etc., but this is a great start - thanks for getting this jump started!
I am thinking that for the formulas we could go a few different ways
- Use a big CASE statement to redirect to known / supported functions.
- Support some kind of "eval" function that passes the parameters to the desired function, which is a security risk, so only support functions that are safe security-wise?
- Support QB64-script type parser to allow defining functions inside the data to save with the sheet. This is non-trivial but didn't someone write a program to do this already?
Anyway thanks again that is very impressive.
Posts: 513
Threads: 65
Joined: May 2022
Reputation:
83
I’ll see what I can do. I already know that if we get it up and running, it will be possible to save and load the ODS format. There’s a library for it, and while it originates from OpenOffice and LibreOffice, the important thing is that ODS is open-source and Excel can handle it. It could serve as a bridge for a limited version in QB64PE. I don't see the point in building something that isn't compatible—we wouldn't attract new users that way, would we?
Posts: 1,215
Threads: 162
Joined: Apr 2022
Reputation:
34
Posts: 346
Threads: 45
Joined: Jun 2024
Reputation:
32
01-26-2026, 11:07 PM
(This post was last modified: 01-26-2026, 11:23 PM by Unseen Machine.)
Code: (Select All) #ifndef QB_SHEETS_H
#define QB_SHEETS_H
#include <vector>
#include <string>
#include <fstream>
#include <cstdint>
extern "C" {
typedef intptr_t QBSHandle;
// Internal cell storage structure
struct QBCell {
std::string s;
int64_t i = 0;
double d = 0.0;
uint8_t type = 0; // 0:Empty, 1:Str, 2:Int, 3:Dbl
uint32_t bg = 0xFFFFFF;
uint32_t fg = 0x000000;
bool lock = false;
};
// Fast-fetch structure for QB64 (binary compatible)
struct QBCellInfo {
uint32_t bg;
uint32_t fg;
bool lock;
uint8_t varType;
};
struct QBRow {
int16_t height = 20;
std::vector<QBCell> cells;
};
struct QBSheet {
std::vector<QBRow> rows;
std::vector<int16_t> colWidths;
};
// --- Lifecycle Management ---
__declspec(dllexport) QBSHandle QBS_New(int r, int c) {
QBSheet* s = new QBSheet();
s->rows.resize(r);
for (int i = 0; i < r; ++i) s->rows[i].cells.resize(c);
s->colWidths.assign(c, 100);
return (QBSHandle)s;
}
__declspec(dllexport) void QBS_Free(QBSHandle h) {
delete (QBSheet*)h;
}
// --- Universal Data Setters ---
__declspec(dllexport) void QBS_SetStr(QBSHandle h, int r, int c, const char* v) {
auto* s = (QBSheet*)h;
if (s && r < s->rows.size() && c < s->rows[r].cells.size()) {
s->rows[r].cells[c].s = v; s->rows[r].cells[c].type = 1;
}
}
__declspec(dllexport) void QBS_SetInt(QBSHandle h, int r, int c, int64_t v) {
auto* s = (QBSheet*)h;
if (s && r < s->rows.size() && c < s->rows[r].cells.size()) {
s->rows[r].cells[c].i = v; s->rows[r].cells[c].type = 2;
}
}
__declspec(dllexport) void QBS_SetDbl(QBSHandle h, int r, int c, double v) {
auto* s = (QBSheet*)h;
if (s && r < s->rows.size() && c < s->rows[r].cells.size()) {
s->rows[r].cells[c].d = v; s->rows[r].cells[c].type = 3;
}
}
// --- Universal Data Getters ---
__declspec(dllexport) const char* QBS_GetStr(QBSHandle h, int r, int c) {
auto* s = (QBSheet*)h;
if (!s || r >= s->rows.size() || c >= s->rows[r].cells.size()) return "";
return s->rows[r].cells[c].s.c_str();
}
__declspec(dllexport) int64_t QBS_GetInt(QBSHandle h, int r, int c) {
auto* s = (QBSheet*)h;
return (s && r < s->rows.size() && c < s->rows[r].cells.size()) ? s->rows[r].cells[c].i : 0;
}
__declspec(dllexport) double QBS_GetDbl(QBSHandle h, int r, int c) {
auto* s = (QBSheet*)h;
return (s && r < s->rows.size() && c < s->rows[r].cells.size()) ? s->rows[r].cells[c].d : 0.0;
}
// --- High-Speed Info Fetch ---
// Update this function in QB_Sheets.h
__declspec(dllexport) void QBS_GetInfo(QBSHandle h, int r, int c, intptr_t infoPtr) {
auto* s = (QBSheet*)h;
if (s && r < s->rows.size() && c < s->rows[r].cells.size()) {
// Cast the raw address back to our struct type
QBCellInfo* info = (QBCellInfo*)infoPtr;
auto& cell = s->rows[r].cells[c];
info->bg = cell.bg;
info->fg = cell.fg;
info->lock = cell.lock;
info->varType = cell.type;
}
}
// --- Formatting & Layout ---
__declspec(dllexport) void QBS_Format(QBSHandle h, int r, int c, uint32_t bg, uint32_t fg, bool lock) {
auto* s = (QBSheet*)h;
if (s && r < s->rows.size() && c < s->rows[r].cells.size()) {
auto& cell = s->rows[r].cells[c];
cell.bg = bg; cell.fg = fg; cell.lock = lock;
}
}
__declspec(dllexport) void QBS_Size(QBSHandle h, int idx, int size, bool isRow) {
auto* s = (QBSheet*)h; if (!s) return;
if (isRow && idx < s->rows.size()) s->rows[idx].height = (int16_t)size;
else if (!isRow && idx < s->colWidths.size()) s->colWidths[idx] = (int16_t)size;
}
__declspec(dllexport) int QBS_GetSize(QBSHandle h, int idx, bool isRow) {
auto* s = (QBSheet*)h; if (!s) return 0;
return isRow ? s->rows[idx].height : s->colWidths[idx];
}
// --- Persistence (Binary Save/Load) ---
__declspec(dllexport) int QBS_Save(QBSHandle h, const char* filename) {
auto* s = (QBSheet*)h;
std::ofstream ofs(filename, std::ios::binary);
if (!ofs) return 0;
uint32_t rows = s->rows.size(), cols = s->colWidths.size();
ofs.write((char*)&rows, 4); ofs.write((char*)&cols, 4);
for (auto w : s->colWidths) ofs.write((char*)&w, 2);
for (auto& r : s->rows) {
ofs.write((char*)&r.height, 2);
for (auto& c : r.cells) {
ofs.write((char*)&c.type, 1); ofs.write((char*)&c.bg, 4);
ofs.write((char*)&c.fg, 4); ofs.write((char*)&c.lock, 1);
ofs.write((char*)&c.i, 8); ofs.write((char*)&c.d, 8);
uint32_t slen = c.s.size(); ofs.write((char*)&slen, 4);
ofs.write(c.s.data(), slen);
}
}
return 1;
}
__declspec(dllexport) QBSHandle QBS_Load(const char* filename) {
std::ifstream ifs(filename, std::ios::binary);
if (!ifs) return 0;
uint32_t rows, cols;
ifs.read((char*)&rows, 4); ifs.read((char*)&cols, 4);
QBSheet* s = new QBSheet();
s->rows.resize(rows); s->colWidths.resize(cols);
for (int i = 0; i < cols; ++i) ifs.read((char*)&s->colWidths[i], 2);
for (int i = 0; i < rows; ++i) {
s->rows[i].cells.resize(cols);
ifs.read((char*)&s->rows[i].height, 2);
for (int j = 0; j < cols; ++j) {
auto& c = s->rows[i].cells[j];
ifs.read((char*)&c.type, 1); ifs.read((char*)&c.bg, 4);
ifs.read((char*)&c.fg, 4); ifs.read((char*)&c.lock, 1);
ifs.read((char*)&c.i, 8); ifs.read((char*)&c.d, 8);
uint32_t slen; ifs.read((char*)&slen, 4);
c.s.resize(slen); ifs.read(&c.s[0], slen);
}
}
return (QBSHandle)s;
}
}
#endif
Save that as QB_Sheets.h in your Qb64 folder
Code: (Select All)
'-------------------------------------------------------------------------
' QB_Sheets.bi - Header for Grid Logic and C++ Interface
'-------------------------------------------------------------------------
' --- TYPES ---
TYPE MouseState
X AS LONG: Y AS LONG: B1 AS _BYTE: Wheel AS LONG
END TYPE
' Single-Cell Messenger UDT
' This is used to "fetch" and "commit" all properties of one cell at once.
TYPE CellProperties
Value AS STRING: IntValue AS _INTEGER64: DblValue AS DOUBLE
BG AS _UNSIGNED LONG: FG AS _UNSIGNED LONG: Locked AS _BYTE: VarType AS _BYTE
END TYPE
TYPE GridView
Handle AS _OFFSET
TopRow AS LONG: LeftCol AS LONG
SelR1 AS LONG: SelC1 AS LONG: SelR2 AS LONG: SelC2 AS LONG
IsDragging AS _BYTE
END TYPE
DECLARE LIBRARY "QB_Sheets"
' Lifecycle Management
FUNCTION Sheet_New%& ALIAS "QBS_New" (BYVAL r AS LONG, BYVAL c AS LONG)
SUB Sheet_Free ALIAS "QBS_Free" (BYVAL h AS _OFFSET)
' Persistence (Binary Save/Load)
FUNCTION Sheet_Save& ALIAS "QBS_Save" (BYVAL h AS _OFFSET, filename AS STRING)
FUNCTION Sheet_Load%& ALIAS "QBS_Load" (filename AS STRING)
' Data Setters (Individual)
SUB Sheet_SetSTR ALIAS "QBS_SetStr" (BYVAL h AS _OFFSET, BYVAL r AS LONG, BYVAL c AS LONG, v AS STRING)
SUB Sheet_SetINT ALIAS "QBS_SetInt" (BYVAL h AS _OFFSET, BYVAL r AS LONG, BYVAL c AS LONG, BYVAL v AS _INTEGER64)
SUB Sheet_SetDBL ALIAS "QBS_SetDbl" (BYVAL h AS _OFFSET, BYVAL r AS LONG, BYVAL c AS LONG, BYVAL v AS DOUBLE)
' Data Getters (Individual)
FUNCTION Sheet_GetSTR$ ALIAS "QBS_GetStr" (BYVAL h AS _OFFSET, BYVAL r AS LONG, BYVAL c AS LONG)
FUNCTION Sheet_GetINT& ALIAS "QBS_GetInt" (BYVAL h AS _OFFSET, BYVAL r AS LONG, BYVAL c AS LONG)
FUNCTION Sheet_GetDBL# ALIAS "QBS_GetDbl" (BYVAL h AS _OFFSET, BYVAL r AS LONG, BYVAL c AS LONG)
' Metadata Fetching
' Note: Pass _OFFSET(CellPropertiesVariable.BG) to fill the UDT instantly from C++
SUB Sheet_GetInfo ALIAS "QBS_GetInfo" (BYVAL h AS _OFFSET, BYVAL r AS LONG, BYVAL c AS LONG, BYVAL infoPtr AS _OFFSET)
' Formatting and Physical Layout
SUB Sheet_Format ALIAS "QBS_Format" (BYVAL h AS _OFFSET, BYVAL r AS LONG, BYVAL c AS LONG, BYVAL bg AS _UNSIGNED LONG, BYVAL fg AS _UNSIGNED LONG, BYVAL lock AS LONG)
SUB Sheet_Size ALIAS "QBS_Size" (BYVAL h AS _OFFSET, BYVAL idx AS LONG, BYVAL size AS LONG, BYVAL isRow AS LONG)
FUNCTION Sheet_GetSize% ALIAS "QBS_GetSize" (BYVAL h AS _OFFSET, BYVAL idx AS LONG, BYVAL isRow AS LONG)
END DECLARE
' QB64 Spreadsheet Core
'$DYNAMIC
SCREEN _NEWIMAGE(1024, 768, 32)
_TITLE "QB_Sheets Core Engine"
' --- GLOBALS ---
DIM SHARED Mouse AS MouseState, OldMouse AS MouseState
DIM SHARED MainGrid AS GridView
' Initialize
MainGrid.Handle = Sheet_New(10000, 100)
' Populate some test data
FOR i = 0 TO 50: Sheet_SetSTR MainGrid.Handle, i, 0, "Item" + STR$(i): NEXT
' --- MAIN LOOP ---
DO
_LIMIT 60
UpdateInput
HandleGridInput MainGrid
CLS '_RGB32(50, 50, 50)
DrawGrid MainGrid, 0, 0, _WIDTH, _HEIGHT
_DISPLAY
LOOP UNTIL _KEYDOWN(27)
Sheet_Free MainGrid.Handle
SYSTEM
' --- SUBS ---
SUB UpdateInput
OldMouse = Mouse
DO WHILE _MOUSEINPUT
Mouse.X = _MOUSEX: Mouse.Y = _MOUSEY: Mouse.B1 = _MOUSEBUTTON(1)
Mouse.Wheel = Mouse.Wheel + _MOUSEWHEEL
LOOP
END SUB
SUB HandleGridInput (G AS GridView)
DIM r AS LONG, c AS LONG
GetCellAtMouse G, Mouse.X, Mouse.Y, r, c
' Start Select
IF Mouse.B1 AND NOT OldMouse.B1 THEN
G.SelR1 = r: G.SelC1 = c
G.SelR2 = r: G.SelC2 = c
G.IsDragging = -1
END IF
' Drag Select
IF Mouse.B1 AND G.IsDragging THEN
G.SelR2 = r: G.SelC2 = c
END IF
' End Drag
IF NOT Mouse.B1 THEN G.IsDragging = 0
' Scroll
IF Mouse.Wheel <> OldMouse.Wheel THEN
G.TopRow = G.TopRow - (Mouse.Wheel - OldMouse.Wheel)
IF G.TopRow < 0 THEN G.TopRow = 0
Mouse.Wheel = 0: OldMouse.Wheel = 0
END IF
END SUB
SUB DrawGrid (G AS GridView, x1, y1, x2, y2)
' --- Move all DIMS to start ---
DIM curY AS LONG, curX AS LONG
DIM r AS LONG, c AS LONG
DIM rowH AS INTEGER, colW AS INTEGER
DIM Prop AS CellProperties
curY = y1
r = G.TopRow
DO WHILE curY < y2 AND r < 10000
rowH = Sheet_GetSize(G.Handle, r, 1)
curX = x1
c = G.LeftCol
DO WHILE curX < x2 AND c < 100
colW = Sheet_GetSize(G.Handle, c, 0)
' Fetch metadata (BG, FG, Lock, VarType)
Sheet_GetInfo G.Handle, r, c, _OFFSET(Prop.BG)
' Draw Cell Background and Border
LINE (curX, curY)-(curX + colW - 1, curY + rowH - 1), Prop.BG, BF
LINE (curX, curY)-(curX + colW - 1, curY + rowH - 1), _RGB32(200, 200, 200), B
' Selection Overlay
IF IsInRange(r, c, G.SelR1, G.SelC1, G.SelR2, G.SelC2) THEN
LINE (curX, curY)-(curX + colW - 1, curY + rowH - 1), _RGB32(0, 120, 215, 80), BF
END IF
' Draw Text Content
IF Prop.VarType > 0 THEN
COLOR _RGB32(255, 255, 255) 'Prop.FG, Prop.BG
_PRINTSTRING (curX + 4, curY + (rowH / 2 - 8)), Sheet_GetSTR$(G.Handle, r, c)
END IF
curX = curX + colW
c = c + 1
LOOP
curY = curY + rowH
r = r + 1
LOOP
END SUB
SUB GetCellAtMouse (G AS GridView, mx, my, outR AS LONG, outC AS LONG)
' --- Move all DIMS to start ---
DIM curX AS LONG, curY AS LONG
DIM w AS INTEGER, h AS INTEGER
' Find Column
curX = 0 ' Assuming grid starts at 0, adjust if x1 > 0
outC = G.LeftCol
DO
w = Sheet_GetSize(G.Handle, outC, 0)
IF mx >= curX AND mx < curX + w THEN EXIT DO
curX = curX + w
outC = outC + 1
' Safety break to prevent infinite loops on 0-width or off-screen
IF outC > 16384 OR curX > _WIDTH THEN EXIT DO
LOOP
' Find Row
curY = 0
outR = G.TopRow
DO
h = Sheet_GetSize(G.Handle, outR, 1)
IF my >= curY AND my < curY + h THEN EXIT DO
curY = curY + h
outR = outR + 1
IF outR > 1000000 OR curY > _HEIGHT THEN EXIT DO
LOOP
END SUB
FUNCTION IsInRange (r, c, r1, c1, r2, c2)
DIM minR, maxR, minC, maxC
IF r1 < r2 THEN minR = r1: maxR = r2 ELSE minR = r2: maxR = r1
IF c1 < c2 THEN minC = c1: maxC = c2 ELSE minC = c2: maxC = c1
IF r >= minR AND r <= maxR AND c >= minC AND c <= maxC THEN IsInRange = -1
END FUNCTION
SUB ExportToJSON (G AS GridView, FileName AS STRING)
DIM Q AS STRING: Q = CHR$(34) ' Store the quote character
OPEN FileName FOR OUTPUT AS #1
PRINT #1, "{"
PRINT #1, " " + Q + "rows" + Q + ": ["
FOR r = 0 TO TotalRows - 1
PRINT #1, " ["
FOR c = 0 TO TotalCols - 1
txt$ = Sheet_GetSTR$(G.Handle, r, c)
' Wraps content in quotes: "Value"
PRINT #1, " " + Q + txt$ + Q;
IF c < TotalCols - 1 THEN PRINT #1, "," ELSE PRINT #1, ""
NEXT c
PRINT #1, " ]";
IF r < TotalRows - 1 THEN PRINT #1, "," ELSE PRINT #1, ""
NEXT r
PRINT #1, " ]"
PRINT #1, "}"
CLOSE #1
END SUB
SUB ExportToXML (G AS GridView, FileName AS STRING)
DIM Q AS STRING: Q = CHR$(34)
OPEN FileName FOR OUTPUT AS #1
PRINT #1, "<?xml version=" + Q + "1.0" + Q + " encoding=" + Q + "UTF-8" + Q + "?>"
PRINT #1, "<Workbook>"
FOR r = 0 TO TotalRows - 1
' Example: <Row id="0">
PRINT #1, " <Row id=" + Q + LTRIM$(STR$(r)) + Q + ">"
FOR c = 0 TO TotalCols - 1
txt$ = Sheet_GetSTR$(G.Handle, r, c)
PRINT #1, " <Cell col=" + Q + LTRIM$(STR$(c)) + Q + ">" + txt$ + "</Cell>"
NEXT c
PRINT #1, " </Row>"
NEXT r
PRINT #1, "</Workbook>"
CLOSE #1
END SUB
Uses C++ for memory, has custom load and save functions but can also save to xml and JSON.
Allows for Copy/Paste from Word/Excell inherently.
I wont add anything more to it but as @Petr will grasp it in a heartbeat, maybe itll prove of some use.
Unseen
|
