Named Items In Excel Web Access Web Part

Named Items
Named Items is a feature of Excel which allows Naming few cell area. This makes us easier to integrate the Excel workbook with SharePoint pages. We can show different areas of the same sheet or different sheet in multiple Excel Web Access web parts.

Create Excel Workbooks
Create an Excel workbook with 2 charts as in the following,



Now select the cells for Chart1 and right click to get the following menu.



You will get the Name dialog box as shown below. Enter a name Chart1 without spaces.



Now repeat the same for Chart2 area.



SharePoint
Upload the above workbook to SharePoint document library. Create a new page using the Add Page menu item.



Insert two Excel Web Access web parts into the page.



For the first web part:

1. Choose excel document.
2. Set Named Item to Chart1 by typing.
   
   

Now go to the second web part & repeat for Chart2 named item.

Save the page & you can see as shown below.



You can see that different parts of the sheet are displayed here.

SP powershell scripts

For various SP powershell scripts, Please refer:

http://www.sharepointdiary.com/2014/09/clear-sharepoint-2013-configuration-cache-using-powershell.html

REST expand people column

Title

Name

EMail

MobilePhone

SipAddress

Department

JobTitle

FirstName

LastName

WorkPhone

UserName

Office

ID

Modified

Created

ImnName

NameWithPicture

NameWithPictureAndDetails

ContentTypeDisp

Getting data from host web list in sharepoint hosted app

Getting data from host web list in sharepoint hosted app

https://www.c-sharpcorner.com/blogs/get-host-web-list-data-in-sharepointhosted-app

https://www.c-sharpcorner.com/UploadFile/58e23e/sharepoint-hosted-app-using-rest-api-from-client-side-script/

Find and replace all hyperlinks in excel workbook

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Drawing;

namespace xlUpdate
{
    class Program
    {
        static void Main(string[] args)
        {
            string folderpath = "d:\\Editted_old";
            DirectoryInfo di = new DirectoryInfo(folderpath);
            FileInfo[] files = di.GetFiles("*.xlsx");
            foreach (FileInfo singleFile in files)
            {
                Excel.Application oExcel = new Excel.Application();

                //specify the file name where its actually exist 
                string filepath = singleFile.FullName;

                //pass that to workbook object 
                Excel.Workbook WB = oExcel.Workbooks.Open(filepath);

                object missing = System.Reflection.Missing.Value;
                // statement get the workbookname 
                string ExcelWorkbookname = WB.Name;

                // statement get the worksheet count 
                int worksheetcount = WB.Worksheets.Count;
                for (int ws = 1; ws <= worksheetcount; ws++)
                {
                    Excel.Worksheet wks = (Excel.Worksheet)WB.Worksheets[ws];

                    // statement get the firstworksheetname 

                    int linkCount = wks.UsedRange.Cells.Hyperlinks.Count;

                    for (int i = 1; i <= linkCount; i++)
                    {
                        object index = (object)i;
                        string currLink = wks.UsedRange.Cells.Hyperlinks[i].Address;
                        string TTD = wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay;
                        if (currLink == "https://www.google.co.in")
                        {
                            wks.UsedRange.Cells.Hyperlinks[i].Address = "https://www.google.com";
                            wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay = "Google";
                        }
                        else
                        {
                            wks.UsedRange.Cells.Hyperlinks[i].Address = "https://www.yahoo.com";
                            wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay = "Yahoo";
                        }
                    }
                }
                //we are saving the editted file into new folder"Editted" from old folder "Editted_old"
                object UpdatedFile = singleFile.FullName.Replace("Editted_old", "Editted");
                WB.SaveAs(UpdatedFile, missing,
                    missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
                    missing, missing, missing, missing, missing);
                WB.Close(true, missing, missing);
                oExcel.Quit();
            }
        }
    }
}

Find and replace hyperlinks in word document

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Word;

namespace WordHyperLink
{
    class Program
    {
        static void Main(string[] args)
        {
            string folderpath = "d:\\Editted_old";
            DirectoryInfo di = new DirectoryInfo(folderpath);
            FileInfo[] files = di.GetFiles("*.docx");
            foreach (FileInfo singleFile in files)
            {
                Application applicationObject = new Application();
                object missing = Type.Missing;
                //object fileName = @"d:\Editted_old\LinkWord.docx";
                object fileName = singleFile.FullName;
                object False = false;
                applicationObject.DisplayAlerts = WdAlertLevel.wdAlertsNone;
                Document documentObject = applicationObject.Documents.Open(
                ref fileName, ref missing, ref missing, ref missing,
                ref missing, ref missing, ref missing, ref missing, ref missing,
                ref missing, ref missing, ref False, ref missing, ref missing,
                ref missing, ref missing);
                Hyperlinks links = documentObject.Hyperlinks;

                for (int i = 1; i <= links.Count; i++)
                {
                    object index = (object)i;
                    Hyperlink link = links.get_Item(ref index);
                    string currLink = link.Address;

                    link.Address = "www.microsoft.com";
                    link.TextToDisplay = "Microsoft Home Page";
                }
                object fileName2 = singleFile.FullName.Replace("Editted_old","Editted");
                documentObject.SaveAs2(ref fileName2);
                applicationObject.Quit(ref missing, ref missing, ref missing);
            }
        }
    }
}

Update a File in SharePoint 2013 Using JavaScript Object Model (JSOM)

http://www.c-sharpcorner.com/UploadFile/91b369/how-to-update-a-file-in-sharepoint-2013-using-javascript-js/