Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » Sharepoint »
Difference between lists an excl sheets in sharepoint
Posted Date: 05 Dec 2008 Posted By: s.govardhan reddy Member Level: Bronze Points: 1
Responses:
3
|
what is difference between Lists and excel shaeets in Sharepoint.In which cases we go for Lists and which cases we go for Excel Sheets
|
Responses
|
| Author: Murali 05 Dec 2008 | Member Level: Gold | Rating:  Points: 6 |
http://office.microsoft.com/en-us/sharepointserver/HA101054571033.aspx
Excel Services is primarily designed as a web-based, data-exploration and reporting system for Excel workbooks, and supports a subset of features in Microsoft Office Excel 2007. The following sections summarize which Office Excel 2007 features are supported and unsupported in Excel Services.
In this article
--------------------------------------------------------------------------------
Supported and unsupported features when loading a workbook
Supported and unsupported worksheet functions
Supported and unsupported features when viewing a workbook
Supported and unsupported features when interacting with a workbook
--------------------------------------------------------------------------------
Supported and unsupported features when loading a workbook
You can load a workbook in Excel Services that is in Office Excel 2007 Workbook (.xlsx) or Binary Workbook (.xlsb) file format as a read-only workbook in three ways:
Specify a URL or UNC path in the the Excel Web Access Web Part Workbook property.
Connect a List View Web Part of a document library to an Excel Web Access Web Part, and then pass the URL of the workbook stored in the document library to display it in Microsoft Office Excel Web Access.
View a workbook saved in a document library in the browser. (Point to the item, click the arrow next to it, and then click View in Web Browser.)
All other Microsoft Office Excel file formats are unsupported, including Office Excel 2007 Macro-Enabled Workbook (.xlsm) and Office Excel 2007 97-2003 Workbook (.xls).
Note With appropriate permission, you can also open a workbook in Office Excel 2007 on your client computer from the Open menu on the Office Excel Web Access toolbar, either as a workbook or as a snapshot. For more information, see Open a workbook or snapshot in Excel from Excel Services.
Supported features
The following features are supported when you load a workbook:
Feature Comments
Functions All Excel worksheet functions are supported, with a few exceptions. For more information, see the following section, Supported and unsupported worksheet functions.
Dates The Windows and Macintosh date systems.
Excel tables Excel table data, column headers, calculated columns, total rows, structured references, and styles.
Cells Cell values, including merged cells and cell content overflow.
Names Defined names and named ranges.
Calculation Calculation and recalculation settings, including automatic, automatic except tables, manual, and iterative calculation settings for ranges or entire worksheets. For more information, see Calculate and recalculate data in Excel Services.
Charts Charts, chart ranges, and PivotChart reports. For more information, see Using charts and PivotChart reports in Excel Services.
Formatting Cell and cell range formatting, conditional formatting (except by using data bars and icons) in workbooks, and number formats.
Connections Connections to external data sources, including OLAP PivotTables.
What-If analysis The results of What-if analysis tools, including Goal Seek, Data Tables, Scenarios, Solver, and Series.
Consolidation Consolidated data from ranges.
Data sources SQL Server, OLAP providers, OLEDB providers, and ODBC drivers.
Unsupported features
Workbooks that contain the following unsupported features will not load or display in Excel Services. For best results, always save a workbook from Office Excel 2007 by using the Excel Services command. (Click the Microsoft Office Button , click the arrow next to Publish, and then click Excel Services under Distribute the document to other people.) You can confirm whether a feature is supported by clicking the Open this workbook in my browser after I save check box in the Save for Excel Services dialog box to attempt to display it in the browser. If a feature is not supported, Excel Services displays an alert.
The following features are not supported and prevent you from loading a workbook:
Feature Comments
VBA Visual Basic for Applications (VBA) code, macros, add-ins, and user-defined functions (UDFs).
Note A programmer can customize Excel Services in many ways, including the creation of a user-defined function (UDF). For more information, see the Microsoft Office SharePoint Server 2007 Software Development Kit (SDK).
Legacy macro languages Microsoft Excel 4.0 Macro Functions and Microsoft 5.0 dialog sheets.
Controls Form toolbar controls, Toolbox controls, and all ActiveX controls.
XML XML maps and embedded smart tags.
Security and privacy Workbooks, worksheets, or ranges with protection, and workbooks that have Information Rights Management (IRM).
Note To protect workbooks in Excel Services, use Microsoft Windows SharePoint Services rights and permissions.
Images and objects Linked or embedded objects or images, inserted pictures, AutoShapes, WordArt, and diagrams, such as organization charts.
Ink All ink features including drawing, writing, and annotations.
OLE and DDE Object Linking and Embedding (OLE) objects and Dynamic Data Exchange (DDE) links.
Displayed formulas Workbooks saved with the formulas that are displayed.
Data validation Preventing invalid data entry and creating drop-down lists.
Data sources Data retrieval services for Microsoft Business Solutions, Windows SharePoint Services lists, Microsoft SQL Server, external data ranges (also called query tables), and tables linked to Windows SharePoint Services lists.
Queries Web queries and text queries.
External references to linked workbooks Creating external references (also called links) to a specific cell range, to a defined name for the specific cell range, or as part of a name definition.
Comments Display of and adjustment of comments.
Consolidation Consolidated data from PivotTable reports.
Shared workbooks Sharing of workbooks and resolving conflicting changes.
Digital signatures Visible and invisible digital signatures in a workbook.
Attached toolbars Custom toolbars attached to the workbook by using Office Excel 2003 before the workbook was converted to Excel 2007.
| | Author: gowthami chowdary 05 Dec 2008 | Member Level: Gold | Rating: Points: 6 |
In Microsoft Windows SharePoint Services, you use list item forms to create, modify, or display individual items in the SharePoint list. we can explore best practices for customizing list item forms in Windows SharePoint Services, the layout of the SharePoint page containing a form, and the underlying XML Schema that defines the form's content. By working through a few common tasks, you can understand ways to extend the reach and functionality of forms for interacting with SharePoint lists. We provide examples of common customizations such as adding links to the form's left navigational area, displaying list data in a form, or displaying a form in the home page. We also provide examples of advanced customizations such as customizing the toolbar or body in a form, or adding script to interact with user clicks and to validate data.
Excel is arguably one of the top used tools for the information worker.
->Sheets are automatically versioned, a "god" copy is maintained - no more versions in emails and hard drives. Backups of all this important data are easy to do.
->You gain the capability of securing workbooks, so certain users with limited rights can see the data, but not edit the data. Also, you can establish rules such as "Show only this worksheet to anonymous users" etc.
->It is possible to create snapshots of the workbook and take them with you for that trip you had been waiting for.
->You get a major portion of the capability of Excel 2007 interactivity on a web based application. Thus, if you had colors, pie charts, filtering, you name it - a huge portion of that richness can work on a web front end.
->You can extend the excel sheet through .NET. Whoaaa!! You could do that through VSTO anyway right? Well, you could, but deploying those binaries and deploying updated versions to everyone's desktop is a royal bitch in any reasonable sized organization.
->The formulae in an excel sheet can now be subjected to the power of a huge and powerful server, running compute cluster, and thus this calculation load can be shared/distributed.
-> And don't forget, these excel sheets are stored in a document library, so you can have workflows, rss feeds, alerts and everything else that comes out of a sharepoint document library.
| | Author: NSG 13 Jan 2009 | Member Level: Silver | Rating: Points: 6 |
Hi.
The Microsoft BI Big Picture
There are many different ways to analyze external data and publish it to SharePoint sites, including using Excel Services. Depending on your particular business requirements, you may choose a data integration method that will allow an administrator to more selectively create and deploy data reports and minimize end user control, or you may choose to empower end users by enabling them to access data sources and build custom reports directly inside SharePoint. The Microsoft BI suite includes the flexibility to securely and selectively deploy data based on user credentials.
The Microsoft BI strategy includes three main components:
* The BI platform
* End-user tools and performance management applications
* The delivery mechanisms
The BI platform contains the various database and data warehousing components for data storage and schema. SQL Server 2005 Analysis Services (SSAS) provides a semantic model, referred to as a unified dimensional model (UDM). The UDM defines business entities, business logic, calculations, and metrics and provides a bridge between end users and data sources. End users can run queries directly against the UDM using BI tools, such as Excel and Office PerformancePoint Server 2007
BI Integration with SharePoint Server 2007
Excel, Excel Services, SharePoint Report Center, and the Business Data Catalog (BDC) are the default, or core, BI features within the Office suite of products. SQL Server 2005 Reporting Services, Analysis Services, and PerformancePoint Server 2007 extend the core BI functionality by offering enhanced reporting capabilities, data warehousing, in-depth data analysis, and real-time monitoring.
Using Excel for Analyzing Data
Excel integrates well with both SQL Server 2005 and SharePoint Server 2007 and plays a pivotal role in providing end users with the tools for data analysis, data-mining construction, and the creation of PivotTables. Excel 2007 enhances the user's ability to access and analyze data from SSAS cubes using PivotTables and PivotCharts. Analysis Services data can also be accessed with Excel 2003 via PivotTables, but this involves some additional configuration.
Excel 2007 provides new features, such as the ability to view and work with Analysis Services KPIs and flexibility for building KPI scorecards. (Note that we refer to reports, dashboards, scorecards, and key performance indicators—or KPIs—as the means for analyzing, preparing, and presenting data within SharePoint Server 2007.)
Office BI Integration with SharePoint Lists
Both Excel and Access continue to play a key role in the negotiation between client and server BI functionality within SharePoint Server 2007, and they provide the ability to expose data within SharePoint sites. For example, Excel worksheets can be published as a SharePoint list in which the data can be accessed and updated by multiple users.
The following are a few scenarios you should consider when using Access databases and assessing your data analysis needs:
* You can publish an Access database as a SharePoint list and perform additional data analysis in SharePoint. Other key benefits include filtering and sorting.
* Access 2007 databases can be upgraded to SQL Server 2005 databases using the Access 2007 Upsizing Wizard. For instance, you could upsize an existing Access 2007 database to SQL Server 2005 for a scalable database solution and then use Reporting Services to create custom reports, which you could then publish directly to SharePoint Server 2007.
* You could use Access 2007 as the front-end reporting mechanism for SharePoint lists. Access 2007 includes the ability to import a SharePoint list, or multiple SharePoint lists, into an Access 2007 database to perform additional reporting while maintaining an active link back to the list on the SharePoint server.
There are some key differences between working with data between Excel 2003 and Excel 2007, which are covered in the following sections.
Excel Services
Excel Services is part of SharePoint Server 2007 Enterprise Edition. A benefit of this edition is that you can publish an Excel workbook to a Web page and end users can view workbook contents without the presence of the Excel client. However, for users to interact with Excel Services, clients require the Office SharePoint 2007 Enterprise Client Access License (CAL).
The published workbook is effectively a read-only version of the source Excel workbook. This provides a good way to share information while protecting the integrity of the data—a published Excel workbook, such as an annual or fiscal budget, is a centralized, single version accessed by all. Here we will cover the key features of Excel Services.
Unlike creating a new SharePoint list by importing an Excel spreadsheet or publishing an Excel 2003 worksheet, you can use Excel 2007 to publish an entire Excel workbook to Excel Services. Within each workbook, you can limit the number of worksheets to be published or elect to publish all.
The three main Excel Services components are detailed here:
* The Excel Calculation Services (ECS) component loads the Excel workbook into a Web page, performs server-side calculations, and refreshes external data.
* The Excel Web Access (EWA) Web Part displays a workbook or interconnecting parts of a workbook on a Web page or dashboard. It uses DHTML and JavaScript for Web page interaction, thus avoiding the need to download ActiveX controls to the client. EWA Web Parts can be configured to filter on specific values. EWA is unlike the Datasheet view available in SharePoint lists, which is dependent upon ActiveX controls along with the Excel or Access client. And EWA offers additional features, such as the ability to create a snapshot of an Excel workbook.
* Excel Web services (EWS) is a Web service API that developers can use to develop custom or add-on applications for Excel Services. The typical address to access these Web services is server_site_name/_vti_bin/excelservice.asmx.
Numerous configuration options are available when you deploy Excel Services. Typically, Excel Services installs EWA and EWS on the front-end Web server and one ECS on the application server. In a single-server deployment, EWA, EWS, and ECS are installed on the same server. In multi-server deployments, the components can be installed on separate servers. A major consideration in deploying Excel Services, and one you will want to plan carefully, is how the authentication between servers and clients is negotiated.
Authentication between Client and Server
In a distributed server environment where SQL Server 2005 and Analysis Services are on a different machine than SharePoint Server 2007 and Excel Services, and where you are leveraging external data connections—that is, an Office Data Connection (ODC) file—to your SSAS as the basis for your published Excel workbook, you need to configure Kerberos to impersonate the user accessing the database server and trust/delegation between servers. The same also applies when you are implementing Reporting Services integration where SQL Server 2005 and Reporting Services are on a different machine than SharePoint Server 2007.
Publishing Excel Workbooks
As a best practice, be sure to establish trusted file locations and trusted data connection libraries, as defined in the Excel Services Settings in Shared Services, before publishing your Excel workbooks. They form the basis for successfully publishing Excel workbooks, such as publishing an online analysis processing (OLAP) PivotTable, which uses an ODC file located within a Trusted Data Connections Library. Excel 2007 includes an option within the main File menu to publish to Excel Services. (Note that the Excel workbook must first be saved to a trusted file location.)
ECS performs calculations included within the source Excel workbook on the server, which means that actual functions and formulas are not directly accessible for end users.
The main inference in utilizing Excel Services is that an Excel workbook can be published to a Web page, but the published version is not editable. Users cannot make edits or enter new data and then save the changes to the source Excel file/workbook. Instead, users can perform run-time calculations via the browser based on parameters created in the source Excel workbook. Once a Web page containing EWA is refreshed or a user ends her browser session, any calculated changes are lost.
Snapshots of Excel workbooks can be captured and saved as separate Excel files to the client machine. A snapshot includes the data values within the published Excel workbook and excludes any formulas or other business logic from the source workbook. End users with viewer permissions to the document library containing the source Excel file and location of the published Excel file are able to copy the published Excel workbook to an Excel file (including data values and formulas) and open a snapshot of an Excel workbook
All the best..
|
| Post Reply |
|
|
|
 | | This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post. |
|
|
|
|