Method and system for direct cell formatting in a spreadsheet

• US 5,598,519 A
• Filed: 11/08/1994
• Issued: 01/28/1997
• Est. Priority Date: 11/08/1994
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Narayanan, Raman
• Primary Examiner(s): Feild, Joseph H.
• Application Number:
  US08/335,999
• Time in Patent Office: 812 Days
• Field of Search: 395/148, 395/149, 395/155-161, 395/600, 395/500, 395/415, 395/421.06, 364/401, 364/408, 364/419.19, 364/419.13
• US Class Current: 715/219
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

A method and system wherein data elements in cells of a spreadsheet may be assigned character attribute information, such as font type, font size, bold, italics, underline, etc., wherein the character attribute information is referred to as a format. Format information is stored in a font palette table. Each cell in the spreadsheet is identified by a unique identifier that includes a row identifier and a column identifier. A direct cell format (DCF) table stores an index into the font palette in association with the unique cell identifier. When a cell is to be displayed, the system looks up the stored index in the direct cell format table, retrieves the format information from the font palette, and applies the format to the cell data elements. When a new format is created, the system determines if the format is already extant. If so, the index to the extant format is stored in the direct cell format table. A wild card character stored as a row identifier or column identifier indicates that the entire column or row, respectively, has the same format. Character format information is not stored in individual cells, and cells may share the same format combinations by merely setting the cell index to point to a desired format information in the font palette.

Claims

1. An improved method of formatting cells and characters in a computer spreadsheet, comprising the steps of:

allocating a worksheet data array having a plurality of cells containing data elements;

allocating a first table for storing cell format information which represents the appearance of the cells and the data elements within the cells;

allocating a second table separately from said data array for storing an index pointing to an item of cell format information stored in the first table, the second table storing the index in association with cell identifying information;

generating an index to the item of cell format information stored in the first table;

storing the cell format information in the first table according to the index;

storing the index to the cell format information in at least one location in the second table; and

displaying a selected cell in accordance with the stored cell format information.

2. The method of claim 1, wherein the step of displaying a selected cell in accordance with the cell format information comprises the steps of:

in response to a command to display data elements for a selected cell, conducting a binary search in the second table to locate an entry corresponding to the selected cell;

retrieving an index from the second table pointing to an item of cell format information stored in the first table;

utilizing the retrieved index, retrieving said item of cell format information stored in the first table;

imposing the cell format information retrieved from the first table upon the data elements for the selected cell to obtain formatted data elements; and

storing the formatted data elements in a display memory.

3. The method of claim 1, wherein the first table is a font palette that stores operating system font descriptor information, style information, and color information.

4. The method of claim 1, wherein the cells in the worksheet and the index in the second table are accessed with the cell identifying information.

5. The method of claim 1, further comprising the steps of:

locating an entry in the second table corresponding to the selected cell utilizing a binary search;

if the binary search indicates that a wild card character is stored in either a row identifier or a column identifier for the selected cell, retrieving the index for the initial entry of the entire row or column, respectively;

otherwise, retrieving the index associated with the row identifier and column identifier of the selected cell; and

utilizing the retrieved index from the preceding two retrieving steps to retrieve cell format information from the first table.

6. The method of claim 5, further comprising the steps of:

for a new format, searching the first table to determine if the new format is already extant;

if the new format is already extant, assigning the index for the extant format to the cell identifier in the second table;

if the new format is not already extant, creating a new index to the next location in the first table and assigning the new index for the new format to the cell identifier in the second table.

7. The method of claim 6, further comprising the steps of maintaining a format usage counter indicative of the instances of usage of a predetermined format;

storing the usage counter in the first table in association with the cell format information;

incrementing the format usage counter each time a new or modified format is created that corresponds to the predetermined format.

8. The method of claim 7, further comprising the step of decrementing the format usage counter each time a format modification results in the discontinuance of usage of the predetermined format from a cell, and eliminating the format information from the first table when the format usage counter reaches zero.

9. The method of claim 7, wherein the new format includes formatting information selected from the group consisting of character font, character size, style, and color.

10. An improved method of formatting cells and characters in a spreadsheet, comprising the steps of:

allocating a worksheet data array having a plurality of cells containing data elements;

allocating a first table for storing cell format information which represents the appearance of the cells and the data elements within the cells;

allocating a second table separate from said data array for storing an index pointing to cell format information stored in the first table;

combining predetermined elements of cell format information to create a default cell format;

generating an index to the default cell format;

storing the default cell format in the first table according to the index;

storing the index to the default cell format in at least one location in the second table; and

initializing each of the cells with the index to point to the default cell format.

11. The method of claim 10, wherein the first table is a font palette that stores operating system font descriptor information, style information, and color information.

12. The method of claim 10, wherein the second table is a direct cell formatting array that stores the index in combination with cell identifying information.

13. The method of claim 10, further comprising the step of changing a cell format, comprising the steps of:

recalling the index for a cell of interest;

recalling the format pointed to by the index;

modifying the cell format information in accordance with a desired format to generate modified cell format information;

determining whether the modified cell format information is already extant in the first table;

in response to determination that the modified cell format information is already extant in the first table, incrementing a format usage counter and storing the index to the extant format in the second table; and

in response to determination that the modified cell format information is not already extant in the first table, storing the modified cell format information in the first table, creating a new index to the modified cell format information, and storing the new index in the second table.

14. The method of claim 13, further including the step of scanning the first table for an existing format information upon creation of a modified format and returning the index for the existing format information to the second table if a duplicate entry is found.

15. For use with a computer application program, a system for displaying character information with formatting, comprising:

a data array for storing information corresponding to a character or group of characters that are displayed according to a selected one of a plurality of display formats;

a first table for storing a plurality of display formats;

a second table for storing an index to one of said plurality of display formats in association with an identifier associated with a character or group of characters stored in said data array;

means responsive to being provided with said identifier for retrieving said index to said one of said plurality of display formats from said second table;

means responsive to said retrieved index for retrieving a selected one of said plurality of display formats from said first table; and

means for imposing said selected one of said plurality of display formats upon said character or group of characters in said data array.

16. The system of claim 15, wherein said display format retrieving means comprises an eight bit indexed lookup table.

17. The system of claim 15, wherein said display format imposing means comprises a computer system display memory.

18. The system of claim 15, wherein said index retrieving means comprises a binary search engine.

19. The system of claim 18, wherein said binary search engine is operative for conducting a binary search based on a spreadsheet cell identifier comprising a row identifier and a column identifier.

20. The system of claim 19, wherein if the binary search indicates that a wild card character is stored in either the row identifier or column identifier for a selected cell, said index retrieving means is operative for retrieving the index for the initial entry of the entire column associated or row, respectively, as appropriate;

otherwise, said index retrieving means is operative for retrieving the index associated with the row identifier and column identifier of the selected cell.

21. The system of claim 15, further comprising:

means for searching said first table to determine if a particular display format is already stored therein, wherein for a new format, said searching means searches said first table to determine if said new format is already extant;

means for assigning the index for an extant format to said identifier in said second table if the new format is already extant; and

means for creating a new index to another location in said first table and assigning said new index for the new format to said identifier in said second table if the new format is not already extant.

22. The system of claim 21, further comprising;

means for maintaining a format usage counter indicative of the instances of usage of a predetermined format;

means for storing said format usage counter in said first table in association with said display formats; and

means for incrementing said format usage counter each time a new or modified format is created that corresponds to said predetermined format.

23. The system of claim 22, further comprising:

means for decrementing said format usage counter each time a format modification results in the discontinuance of usage of the predetermined format and eliminating said display format from said first table when said format usage counter reaches zero.

24. The system of claim 15, wherein said display format includes formatting information selected from the group consisting of character font, character size, style, and color.

25. The system of claim 15, further comprising:

means for modifying a display format associated with a character or group of characters; and

means for installing said modified display format in said first table; and

means for updating the index of said characters or group of characters in said second table to point to said modified display format in said first table.

26. The system of claim 15, further including means for detecting and eliminating duplicate entries in said first table.

27. A method of formatting a spreadsheet having data stored in a data array and displayed in a plurality of cells on a display, comprising the steps of:

allocating a first table that stores a plurality of display formats;

generating an index for each display format in the first table;

allocating a second table separately from the data array that stores a plurality of cell identifiers in association with indices into said first table;

storing an index to each utilized display format in said second table;

associating one of the indices with each cell in said second table; and

displaying each cell in the spreadsheet according to the display format stored in the first table for the index associated with the cell.

28. The method of claim 27, further including the steps of:

in response to a user command to modify a format for a selected cell, creating a modified display format;

searching the first table to determine if the modified format is already extant;

if the modified format is already extant, incrementing a format usage counter associated with the extant display format;

assigning the index for the extant display format to the selected cell; and

displaying the selected cell according to the modified format.

29. The method of claim 27, further comprising the steps of:

saving the spreadsheet including each cell together with the index associated with the cell; and

saving the first table after removing currently unused display formats.

30. An improved spreadsheet structure comprising:

a plurality of data cells displayed according to one or more display formats, each data cell associated with a cell identifier;

a first table for storing the display formats;

a second table separate from said data array for storing an index to one of the display formats stored in said first table in association with a cell identifier; and

means for associating one of the indices with the display identifier for each cell such that the data in each cell is displayed according to the display format associated with the index for the cell.

31. The improved spreadsheet structure of claim 30, wherein each index is located with a binary search in the second table.

32. The improved spreadsheet structure of claim 30, further including means for modifying the display format associated with the index for a cell and means for updating the index to correspond to the modified display format.

33. The improved spreadsheet structure of claim 30, further including means for maintaining a format usage counter indicative of the usages of display formats.

34. The improved spreadsheet structure of claim 33, further comprising means for incrementing the format usage counter when a cell is formatted with an already extant display format.

35. The improved spreadsheet structure of claim 34, further comprising means for decrementing the format usage counter when a display format is removed from a cell.

36. The improved spreadsheet structure of claim 30, further including:

means for modifying a display format;

means for scanning the table for an existing display format identical to the modified display format; and

means for replacing a previous index for a cell with the index for the existing identical display format.

37. An improved method of formatting cells and characters in a computer spreadsheet, comprising the steps of:

allocating a worksheet having a plurality of cells containing data elements;

allocating a first table for storing cell format information which represents the appearance of the cells and the data elements within the cells;

allocating a second table for storing an index pointing to an item of cell format information stored in the first table;

generating an index to the item of cell format information stored in the first table;

storing the cell format information in the first table according to the index;

storing the index to the cell format information in at least one location in the second table;

for a new format, searching the first table to determine if the new format is already extant;

if the new format is already extant, assigning the index for the extant format to the cell identifier in the second table;

if the new format is not already extant, creating a new index to the next location in the first table and assigning the new index for the new format to the cell identifier in the second table;

maintaining a format usage counter indicative of the instances of usage of a predetermined format;

storing the usage counter in the first table in association with the format information;

incrementing the format usage counter each time a new or modified format is created that corresponds to the predetermined format;

locating an entry in the second table corresponding to a selected cell utilizing a binary search;

if the binary search indicates that a wild card character is stored in either a row identifier or a column identifier for the selected cell, retrieving the index for the initial entry of the entire row or column, respectively;

otherwise, retrieving the index associated with the row identifier and column identifier of the selected cell;

utilizing the retrieved index from the preceding two retrieving steps to retrieve cell format information from the first table;

displaying the selected cell in accordance with the stored cell format information.

38. The method of claim 37, further comprising the step of decrementing the format usage counter each time a format modification results in the discontinuance of usage of the predetermined format from a cell, and eliminating the format information from the first table when the format usage counter reaches zero.

39. The method of claim 37, wherein the new format includes formatting information selected from the group consisting of character font, character size, style, and color.

40. For use with a computer application program, a system for displaying character information with formatting, comprising:

a first table for storing a plurality of display formats;

a second table for storing an index to one of said plurality of display formats in association with an identifier associated with a character or group of characters;

a character or group of characters that are displayed according to one of said plurality of display formats;

means responsive to being provided with said identifier for retrieving said index to said one of said plurality of display formats from said second table;

means responsive to said retrieved index for retrieving a selected one of said plurality of display formats from said first table; and

means for imposing said selected one of said plurality of display formats upon said character or group of characters;

means for searching said first table to determine if a particular display format is already stored therein, wherein for a new format, said searching means searches said first table to determine if said new format is already extant;

means for assigning the index for an extant format to said identifier in said second table if the new format is already extant;

means for creating a new index to another location in said first table and assigning said new index for the new format to said identifier in said second table if the new format is not already extant;

means for maintaining a format usage counter indicative of the instances of usage of a predetermined format;

means for storing said format usage counter in said first table in association with said display formats; and

means for incrementing said format usage counter each time a new or modified format is created that corresponds to said predetermined format.

41. The system of claim 40, further comprising:

means for decrementing said format usage counter each time a format modification results in the discontinuance of usage of the predetermined format and eliminating said display format from said first table when said format usage counter reaches zero.

42. An improved method of formatting cells and characters in a spreadsheet, comprising the steps of:

allocating a worksheet having a plurality of cells containing data elements;

allocating a first table for storing cell format information which represents the appearance of the cells and the data elements within the cells;

allocating a second table for storing an index pointing to cell format information stored in the first table;

combining predetermined elements of cell format information to create a default cell format;

generating an index to the default cell format;

storing the default cell format in the first table according to the index;

storing the index to the default cell format in at least one location in the second table; and

initializing each of the cells with the index to point to the default cell format;

recalling the index for a cell of interest;

recalling the format pointed to by the index;

modifying the cell format information in accordance with a desired format to generate modified cell format information;

determining whether the modified cell format information is already extant in the first table;

in response to determination that the modified cell format information is already extant in the first table, incrementing a format usage counter and storing the index to the extant format in the second table; and

in response to determination that the modified cell format information is not already extant in the first table, storing the modified cell format information in the first table, creating a new index to the modified cell format information, and storing the new index in the second table.

43. The method of claim 42, further including the step of scanning the first table for an existing format information upon creation of a modified format and returning the index for the existing format information to the second table if a duplicate entry is found.

44. A method of formatting a spreadsheet having data displayed in a plurality of cells, comprising the steps of:

allocating a first table that stores a plurality of display formats;

generating an index for each display format in the first table;

allocating a second table that stores a plurality of cell identifiers in association with indices into said first table;

storing an index to each utilized display format in said second table;

associating one of the indices with each cell in said second table;

displaying each cell according to the display format for the index associated with the cell;

in response to a user command to modify a format for a selected cell, creating a modified display format;

searching the first table to determine if the modified format is already extant;

if the modified format is already extant, incrementing a format usage counter associated with the extant display format;

assigning the index for the extant display format to the selected cell; and

displaying the selected cell according to the modified format.

45. An improved spreadsheet structure comprising:

a plurality of data cells displayed according to one or more display formats;

a first table for storing the display formats;

a second table for storing an index to one of the display formats stored in said first table in association with a cell identifier;

means for associating one of the indices with the display identifier for each cell such that each cell is displayed according to the display format associated with the index for the cell;

means for maintaining a format usage counter indicative of the usages of display formats; and

means for incrementing the format usage counter when a cell is formatted with an already extant display format.

46. The improved spreadsheet structure of claim 45, further comprising means for decrementing the format usage counter when a display format is removed from a cell.