Data source write back and offline data editing and storage in a spreadsheet

• US 7,225,189 B1
• Filed: 02/19/2004
• Issued: 05/29/2007
• Est. Priority Date: 02/19/2004
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: McCormack, Michael J., Chauhan, Sumit, Wu, Su-Piao Bill, Patterson, Eric W., Eatough, Michael J., Dickinson, Richard L., Coffen, Robert W., Tseng, Larry, Hafner, Paul A., Pan, Xiaohui
• Primary Examiner(s): Chace; Christian
• Assistant Examiner(s): Lu; Kuen S.
• Application Number:
  US10/782,074
• Time in Patent Office: 1,195 Days
• Field of Search: 707/10, 715/503
• US Class Current: 1/1
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

Methods and systems provide for bi-directional communication between a spreadsheet application worksheet and a database data source that allows modified data to be written from a spreadsheet application to source data in a database data source from which the original pre-modified data was obtained. Offline data changes in a spreadsheet application are cached by a data provider application and are published to a remote data source when the spreadsheet application is synchronized with the data source. Error conditions and data conflicts resulting from new or modified data published to a database data source from a spreadsheet application worksheet are reported to a user of the spreadsheet application worksheet through a data provider application to allow the user an opportunity to resolve error conditions and data conflicts between the user'"'"'s data changes and other data changes made to the same source data contained in the database data source.

Claims

1. A method of bi-directional communication between a spreadsheet application and a database, comprising:

connecting a spreadsheet to a data source;

importing via the spreadsheet application data to the spreadsheet from the data source;

modifying the imported data in the spreadsheet

publishing data from the spreadsheet to the data source via the spreadsheet application;

determining whether publishing the data to the data source creates a conflict with data previously published to the data source via the spreadsheet application; and

storing the data at the data source including writing via the spreadsheet application any changes made to the data in the spreadsheet over corresponding data previously stored at the data source;

whereby publishing the data to the data source includes publishing imported data back to the data source and writing the modified imported data over the data in the data source from which the data was imported to the spreadsheet.

2. The method of claim 1, whereby the data source is a database data source.

3. The method of claim 1, whereby connecting a spreadsheet to a data source includes connecting the spreadsheet to the data source via a bi-directional communication protocol.

4. The method of claim 3, whereby the bi-directional communication protocol includes an OLE-DB communication protocol.

5. The method of claim 3, whereby connecting the spreadsheet to the data source via a bi-directional communication protocol includes connecting the spreadsheet to the data source via a data provider application.

6. The method of claim 5, prior to publishing the data from the spreadsheet to the data source, passing the data to the data provider application.

7. The method of claim 6, prior to publishing the data from the spreadsheet to the data source, storing the data in a memory cache via the data provider application.

8. The method of claim 1, prior to storing the data at the data source, determining whether publishing the data to the data source creates an error condition.

9. The method of claim 8, whereby determining whether publishing the data to the data source creates an error condition includes querying the data source by a data provider application for determining whether publishing the data to the data source creates an error condition.

10. The method of claim 8, whereby if publishing the data to the data source creates an error condition, returning an error message to the spreadsheet.

11. The method of claim 10, whereby returning an error message to the spreadsheet includes identifying a data publication error associated with the error condition.

12. The method of claim 11, whereby the data publication error is a user permission error.

13. The method of claim 11, whereby the data publication error is a data source not available error.

14. The method of claim 11, whereby the data publication error identifies that a data source schema does not support publication of the data.

15. The method of claim 11, whereby the data publication error identifies that the data source does not support one or more data types associated with the published data.

16. The method of claim 1, whereby determining whether publishing the data to the data source creates a conflict with data previously published to the data source includes comparing the data with data previously published to the data source.

17. The method of claim 16, whereby comparing the data with data previously published to the data source includes comparing the data by a data provider application.

18. The method of claim 1, whereby if publishing the data to the data source creates a conflict with data previously published to the data source, passing an identification of any of the data that creates a conflict with data previously published to the data source to the spreadsheet for alerting a user of the spreadsheet as to the data conflict.

19. The method of claim 1, whereby if publishing the data to the data source creates a conflict with data previously published to the data source, resolving the conflict by accepting a latest data published to the data source.

20. The method of claim 1, whereby if publishing the data to the data source creates a conflict with data previously published to the data source, resolving the conflict by discarding any of the data that conflicts with corresponding data of the data previously published to the data source.

21. The method of claim 1, whereby if publishing the data to the data source creates a conflict with data previously published to the data source, resolving the conflict by merging the data with the data previously published to the data source such that for any portion of the data that conflicts with a corresponding portion of the data previously published to the data source, writing over the corresponding portion of the data previously published to the data source with any portion of the data that conflicts with the corresponding portion of the data.

22. The method of claim 1, further comprising breaking a communication link between the spreadsheet and the data source;

and

persisting any data modified in the spreadsheet after breaking the communication link;

establishing a second communication link between the spreadsheet and the data source;

publishing to the data source the data modified in the spreadsheet after breaking the communication link to the data source; and

modifying data previously published to the data source with the data modified in the spreadsheet after breaking the communication link to the data source.

23. The method of claim 1, whereby publishing the data to the data source includes creating a new data row in the data source.

24. The method of claim 1, whereby publishing the data to the data source includes deleting a data row from the data source.

25. The method of claim 1, whereby publishing the data to the data source includes creating a new data column in the data source.

26. The method of claim 1, whereby publishing the data to the data source includes deleting a data column from the data source.

27. A computer storage medium readable by a computing system and encoding a computer program of instructions for executing a computer process for bi-directional communication between a spreadsheet application and a database, said computer process comprising:

connecting a spreadsheet to a data source;

importing data to the spreadsheet from the data source via the spreadsheet application;

modifying the imported data in the spreadsheet via the spreadsheet application;

publishing the modified imported data back to the data source and writing the modified imported data over the data in the data source from which the data was imported to the spreadsheet;

determining whether publishing the data to the data source creates a conflict with data previously published to the data source via the spreadsheet application; and

storing the data at the data source including writing via the spreadsheet application any changes made to the data in the spreadsheet over corresponding data previously stored at the data source.

28. The computer storage medium of claim 27, whereby connecting a spreadsheet to a data source includes connecting the ssheet to the data source via a bi-directional communication protocol through a data provider application integrated with the spreadsheet application.

29. The computer storage medium of claim 28, prior to publishing the modified imported data back to the data source, querying the data source by the data provider application for determining whether publishing the data to the data source creates an error condition;

and if publishing the data to the data source creates an error condition, returning an error message to the spreadsheet.

30. A method of bi-directional communication between a spreadsheet application and a database, comprising:

connecting a spreadsheet to a data source via a bi-directional communication protocol allowing data to flow between the spreadsheet and the data source;

importing via the spreadsheet application, data to the spreadsheet from the data source;

modifying the imported data in the spreadsheet via the spreadsheet application;

exporting via the spreadsheet application the modified imported data back to the data source and writing the modified imported data over the data in the data source from which the data was imported to the spreadsheet;

determining whether writing the modified imported data to the data source creates a conflict with data previously written to the data source by comparing the modified imported data with data previously written to the data source; and

storing the data at the data source including writing via the spreadsheet application any changes made to the data in the spreadsheet over corresponding data previously stored at the data source.

31. The method of claim 30, further comprising:

notifying a user of the spreadsheet of a conflict via a data conflict message passed to the spreadsheet via the bi-directional communication protocol; and

resolving the conflict by writing the modified imported data to the data source or by discarding the modified imported data so as to persist the data previously written to the data source.