Creating and managing structured data in an electronic spreadsheet

• US 7,127,672 B1
• Filed: 08/22/2003
• Issued: 10/24/2006
• Est. Priority Date: 08/22/2003
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: McCormack, Michael J., Wu, Su-Piao Bill, Patterson, Eric W., Coffen, Robert Wayne
• Primary Examiner(s): Hong, Stephen
• Assistant Examiner(s): PATEL, MANGLESH M
• Application Number:
  US10/646,131
• Time in Patent Office: 1,159 Days
• Field of Search: 715/503, 715/507, 715/508, 715/509, 715/505
• US Class Current: 715/220
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 40/226 Validation

Abstract

An electronic spreadsheet is provided for automatically creating, expanding, and validating lists of cells. A list is created by receiving a user request for selecting a cell range, identifying a list range for the selected cell range, determining a header row for the list range, and generating a border for surrounding the list range. A list is expanded by determining that one or more cells are adjacent to the list and automatically expanding the list to include the new cells. A list is validated by determining a data type of data entered into the list and comparing the data type of the data entered into the list to a list data type. If, as a result of the comparison, the data type of the data entered into the list does not match the list data type, then the data type of the data entered into the list is invalid.

Claims

1. A method in a computer system for automatically creating a list in an electronic spreadsheet, comprising the steps of:

receiving a user request for selecting a cell range in the electronic spreadsheet for displaying data, the cell range including one or more cells in one or more columns;

identifying a list range for the selected cell range in the electronic spreadsheet, wherein identifying a list range for the selected cell range comprises;

determining the number of cells in the selected cell range;

if the selected cell range contains two or more cells, then identifying the list range as the selected cell range; and

if the selected cell range contains exactly one cell, then identifying the list range based on the location of the cell in a current region of the electronic spreadsheet;

determining a header row for the list range, wherein determining a header row for the list range comprises;

determining whether the list range includes a field name for each column in the cell range; and

if the identified list range does not include any field names, then automatically generating a field name for each column in the cell range, wherein the automatically generated field names comprise the header row for the list range; and

generating a border for surrounding the list range to create the list.

2. The method of claim 1, wherein each field name is unique.

3. The method of claim 1, wherein the list range includes user-generated data.

4. The method of claim 1, wherein the list range includes a blank row for inserting data.

5. The method of claim 3, further comprising:

determining whether the selected cell range includes a user total row for calculating a plurality of aggregation functions for the user-generated data; and

if the selected cell range includes a user total row, then excluding the user total row from the list range.

6. The method of claim 5, wherein the user total row is one row adjacent to the bottom of the at list range.

7. The method of claim 5, wherein the user total row comprises at least one cell for calculating the plurality of aggregation functions.

8. A method for automatically expanding a list in an electronic spreadsheet, the method comprising:

determining that at least one previously blank newly edited cell is adjacent to the list, wherein determining that at least one previously blank newly edited cell is adjacent to the list further comprises;

determining that the at least one previously blank newly edited cell is adjacent to a header row in the list, the header row comprising at least one field name; and

determining that the at least one previously blank newly edited cell is adjacent to a data row in the list, the data row comprising at least one cell containing data;

automatically expanding the list to include the at least one previously blank newly edited cell, wherein automatically expanding the list to include the at least one previously blank newly edited cell comprises automatically expanding the list to include a new column containing the at least one previously blank newly edited cell;

if the at least one previously blank newly edited cell is adjacent to a header row in the list, then determining whether a data entry in the at least one previously blank newly edited cell duplicates an existing field name in the header row in the list;

if the data entry in the at least one previously blank newly edited cell is identical to an existing field name in the header row in the list, then changing the data entry to a unique field name;

if the data entry in the at least one previously blank newly edited cell is not identical to an existing field name in the header row in the list, then assigning the data entry as a field name for the new column; and

if the at least one previously blank newly edited cell is adjacent to a data row in the list, then automatically generating a field name for the new column.

9. The method of claim 8, wherein automatically expanding the list to include the at least one previously blank newly edited cell comprises automatically expanding the list to include a new row containing the at least one previously blank newly edited cell.

10. The method of claim 8, wherein determining that at least one previously blank previously blank newly edited cell is adjacent to the list comprises determining that the previously blank newly edited cell is to a right side of the list.

11. The method of claim 8, wherein the list is structured data.

12. A computer-readable medium having computer-executable instructions for performing a method for displaying an automatically created list in an electronic spreadsheet, comprising the steps of:

receiving a user request for selecting a cell range in the electronic spreadsheet, the cell range including one or more cells in one or more columns for displaying data;

identifying a list range for the selected cell range in the electronic spreadsheet, wherein identifying a list range for the selected cell range comprises;

determining the number of cells in the selected cell range;

if the selected cell range contains two or more cells, then identifying the list range as the selected cell range; and

if the selected cell range contains exactly one cell, then identifying the list range based on the location of the cell in a current region of the electronic spreadsheet;

determining a header row for the list range, wherein determining a header row for the list range comprises;

determining whether the list range includes a field name for each column in the cell range; and

if the identified list range does not include any field names, then automatically generating a field name for each column in the cell range, wherein the automatically generated field names comprise the header row for the list range;

generating a border for surrounding the list range to create the list; and

displaying the automatically created list on the display device.

13. The computer-readable medium of claim 12, wherein the list range comprises a blank row for inserting data.

14. The computer-readable medium of claim 12, further comprising:

determining whether the selected cell range includes a user total row for calculating a plurality of aggregation functions for the user-generated data; and

if the selected cell range includes a total row, then excluding the user total row from the list range.

15. A computer-readable medium having computer-executable instructions for performing a method for automatically expanding a list in an electronic spreadsheet, comprising the steps of:

determining that at least one previously blank newly edited cell is adjacent to the list, wherein determining that at least one previously blank newly edited cell is adjacent to the list further comprises;

determining that the at least one previously blank newly edited cell is adjacent to a header row in the list, the header row comprising at least one field name;

determining that the at least one previously blank newly edited cell is adjacent to a data row in the list, the data row comprising at least one cell containing data;

if the at least one previously blank newly edited cell is adjacent to a header row in the list, then determining whether a data entry in the at least one previously blank newly edited cell duplicates an existing field name in the header row in the list;

if the data entry in the at least one previously blank newly edited cell is identical to an existing field name in the header row in the list, then changing the data entry to a unique field name;

if the data entry in the at least one previously blank newly edited cell is not identical to an existing field name in the header row in the list, then assigning the data entry as a field name for the new column; and

if the at least one previously blank newly edited cell is adjacent to a data row in the list, then automatically generating a field name for the new column; and

automatically expanding the list to include the at least one previously blank newly edited cell, wherein automatically expanding the list to include the at least one previously blank newly edited cell comprises automatically expanding the list to include a new column containing the at least one previously blank newly edited cell.

16. The computer-readable medium of claim 15, wherein automatically expanding the list to include the at least one previously blank newly edited cell comprises automatically expanding the list to include a new row containing the at least one previously blank newly edited cell.

17. The computer-readable medium of claim 15, wherein data range references in cells in existing list rows are automatically adjusted to include cells in the new column.

18. The computer-readable medium of claim 16, wherein data range references in cells in existing list columns are automatically updated to include cells in the new row.