How to extract data from excel spreadsheet stored in Sharepoint document library?

SharePoint is a great platform for content management.  we can archive excel spreadsheet with version control enable in the SharePoint document library.

we can use the following steps to achieve the result.

step one. Upload the Excel spreadsheet to the  sharepoint document library using asp.net File Upload Control

  var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);
                using (var clientAppContext = spContext.CreateUserClientContextForSPHost())
                {
                    List list = clientAppContext.Web.Lists.GetByTitle(sListTitle);
                    FileCreationInformation newFile = new FileCreationInformation();
                    newFile.Overwrite = true;
                    newFile.ContentStream = fileUploadDealerRules.PostedFile.InputStream;
                    string sFileName = System.IO.Path.GetFileName(fileUploadDealerRules.PostedFile.FileName);
                    sFileName = ReplaceIllegalChars(sFileName);
                    sNewFileName = string.Format("{0}_{1}{2}", System.IO.Path.GetFileNameWithoutExtension(fileUploadDealerRules.FileName), System.DateTime.Now.ToString("yyyy-MM-dd HHmmss"), System.IO.Path.GetExtension(fileUploadDealerRules.FileName));// Guid.NewGuid().ToString() + "_" + sFileName;
                    newFile.Url = sNewFileName;
                    Microsoft.SharePoint.Client.File fileToSp = list.RootFolder.Files.Add(newFile);
                    Microsoft.SharePoint.Client.ListItem item = fileToSp.ListItemAllFields;
                    clientAppContext.Load(item);
                    clientAppContext.Load(fileToSp);
                    clientAppContext.ExecuteQuery();
                }


step two. Read the excel spreadsheet content that stored in SP document library into the memory with C# memory stream

                using (var clientAppContext = spContext.CreateUserClientContextForSPHost())
                {
                    List list = clientAppContext.Web.Lists.GetByTitle(sListTitle);
                    clientAppContext.Load(list.RootFolder);
                    clientAppContext.ExecuteQuery();
                    string dealerRuleFile = list.RootFolder.ServerRelativeUrl + "/" + sNewFileName;
                    Microsoft.SharePoint.Client.File file = clientAppContext.Web.GetFileByServerRelativeUrl(dealerRuleFile);
                    ClientResult<System.IO.Stream> data = file.OpenBinaryStream();
                    clientAppContext.Load(file);
                    clientAppContext.ExecuteQuery();


step three. Read the content from the memory and store them in the data table using OpenXL library

using  DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;


using (var clientAppContext = spContext.CreateUserClientContextForSPHost())
                {
                    List list = clientAppContext.Web.Lists.GetByTitle(sListTitle);
                    clientAppContext.Load(list.RootFolder);
                    clientAppContext.ExecuteQuery();
                    string dealerRuleFile = list.RootFolder.ServerRelativeUrl + "/" + sNewFileName;
                    Microsoft.SharePoint.Client.File file = clientAppContext.Web.GetFileByServerRelativeUrl(dealerRuleFile);
                    ClientResult<System.IO.Stream> data = file.OpenBinaryStream();
                    clientAppContext.Load(file);
                    clientAppContext.ExecuteQuery();
                    using (System.IO.MemoryStream mStream = new System.IO.MemoryStream())
                    {
                        if (data != null)
                        {
                            data.Value.CopyTo(mStream);
                            using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(mStream, false))
                            {
                                WorkbookPart workbookPart = document.WorkbookPart;
                                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                                string relationshipId = sheets.First().Id.Value;
                                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
                                Worksheet workSheet = worksheetPart.Worksheet;
                                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                                foreach (Cell cell in rows.ElementAt(0))
                                {
                                    dataTable.Columns.Add(GetCellValue(document, cell));
                                }

                                foreach (Row row in rows)
                                {
                                    if (row != null)
                                    {
                                        DataRow dataRow = dataTable.NewRow();
                                        for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                                        {
                                            dataRow[i] = GetCellValue(document, row.Descendants<Cell>().ElementAt(i));
                                        }
                                        if (!string.IsNullOrEmpty(Common.Utility.SubaruUtil.ConvertToString(dataRow[0])))
                                        {
                                            dataTable.Rows.Add(dataRow);
                                        }
                                    }
                                }
                                dataTable.Rows.RemoveAt(0);

                            }
                        }
                    }
                }
            }


private helper method to get each individual cell value of the excel spreadsheet.

 private string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            string value = string.Empty;
            if (cell != null)
            {
                SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
                if (cell.CellValue != null)
                {
                    value = cell.CellValue.InnerXml;

                    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
                    {
                        if (stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)] != null)
                        {
                            return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
                        }
                    }
                    else
                    {
                        return value;
                    }
                }
            }
            return string.Empty;
        }

Step Four. use bulk SqlBulkCopy to batch the data into SQL Server.

How to dynamically generate unknow number of column in a table using AngularJS?

this is the second question being asked from my friend.. he is working on a project that need to generated a table with multiple number of columns based on the Json data.

you can view the first question regarding the AngularJS framework upgrade here

How to solve legacy AngularJS application failed in AngularJS Framework Upgrade 

we can put ng-repeat in the TD column attribute to implement this requirement, the following code demonstrate the implementation


html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script>
<%--<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.26/angular.min.js"></script>--%>

<script>
    angular.module('MyApp', []).controller('MyController1', ['$scope', MyController1]);
    function MyController1($scope) {

        $scope.mailLinks1 = [
            {
                "lnks": [
                { "txt": "YahooMail 11", "url": "www.yahoomail.ca" },
                { "txt": "Gmail", "url": "www.gmail.com" },
                { "txt": "Outlook", "url": "www.outlook.com" }
                ], "indx": "1"
            },

        {
            "lnks": [
               { "txt": "YahooMail 22", "url": "www.yahoomail.ca" },
               { "txt": "Gmail", "url": "www.gmail.com" },
               { "txt": "Outlook", "url": "www.outlook.com" }
            ], "indx": "2"
        },

        {
            "lnks": [
               { "txt": "YahooMail 33", "url": "www.yahoomail.ca" },
               { "txt": "Gmail", "url": "www.gmail.com" },
               { "txt": "Outlook", "url": "www.outlook.com" }
            ], "indx": "3"
        },

         {
             "lnks": [
                { "txt": "YahooMail 44", "url": "www.yahoomail.ca" },
                { "txt": "Gmail44", "url": "www.gmail.com" },
                { "txt": "Outlook44", "url": "www.outlook.com" }
             ], "indx": "3"
         },

        {
            "lnks": [
               { "txt": "xMail ...x", "url": "www.xxxxMail.ca" },
               { "txt": "xxxxmail", "url": "www.xxx.com" },
               { "txt": "xxxxxxxxx", "url": "www.xxx.com" }
            ], "indx": "x"
        }
        ];
    }
</script>
</head>
<body>
      <div  ng-app="MyApp"  ng-controller="MyController1" >
      <table id="dispPnlTable" border="1" cellspacing="0" cellpadding="5">
    <tr>
      <td ng-repeat="json1 in mailLinks1" >
          <table><tr  ng-repeat="lnk1 in json1.lnks"><td>
              {{lnk1.txt}} -- {{lnk1.url}}
          </td></tr></table>     
             
      </td>
    </tr>
  </table>
    </div>
</body>
</html>

 

Manually Clear the IE Cache will help fixing various issues from asp.net and sharepoint application

I had encountered various issue that application works fine in the development and testing environment.

but some times it failed in the user testing. the issues can be encoutnered in the following situation.

1. the modification of the stylesheet.

2. the update on the javascript files

3. the change of the user credential that user had been added to new AD group

usually we will think that we just go Tools Menu from the Tool Bar, then select Internet options









go to browsing history section and click on the delete button





















check all the check-boxes to clear everything



we thought that if we follow the above steps, we will be able to clear everything that IE had been cached.
 However this is not always true. if we want to really clear everything that IE had cached in the file system. we have to go the cache folder and manually delete them.

1. from the internet options popup windows, go to the browsing history section, click on the Settings Button



2. click on the View Files button to open the Temporary Internet Files Folder



3. you will see lots of files in these folder. Use Contrl A to select all files and delete them.. then you will finally clear the IE cache