What gives Excel/csvs their formatting functionalities?

Question

When I open a csv file by double-clicking it, it opens, and as one would expect, displays the data in a big array of cells with a ribbon of formatting options at the top (Clipboard, Font, Alignment, etc.), like an Excel file.

When I open the same csv in VSCode, I see what looks more like a simple text file, something like this:

Sr,S,V
67,30,-7
27,80,123
200,15,300
84,78,135
151,25,167
-1,98,148
51,87,120

My question is, what is it that "creates" all the formatting functionality in the former case? How is all that stuff programmed?

Answer 1

First, note that if you apply formatting (font, alignment, etc) and save it back to CSV, your formatting won't be saved. You can see that CSV is just some text separated by commas (hence the format name - Comma-Separated Values). There is no place to put formatting.

So, the only difference is the cells. Excel splits the file into cells automatically by searching for commas and line ends. It also "interprets" the data, making determinations about number formats, dates, text, etc. (for example, by default, numbers and dates are aligned to the right and text to the left). It does this because it's a table processor and it's designed to work with tables. VSCode doesn't have this functionality, it displays just the plain text.

In other words, Excel converts CSV to its native format in memory, allowing full functionality.

You can save the table with formatting to another file format, for example XLSX, which is much more complex than CSV and has place to save formatting. Internally it's a ZIP file which contains XML documents (and may contain other files, for example, if you insert image into your table, it will be stored in the ZIP file in its native format). If you are interested, you can unzip it and study its contents.