Insert Formula in Cell using C# (OpenXML and EPPlus Library)


Sharing the sample code, we can use to insert forumala in a particular cell in Excel Spreadsheet.

First using Open XML SDK

</p>
<p>public static void UpdateExcelUsingOpenXMLSDK(string fileName)<br />
{<br />
// Open the document for editing.<br />
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(fileName, true))<br />
{<br />
// Access the main Workbook part, which contains all references.<br />
WorkbookPart workbookPart = spreadSheet.WorkbookPart;<br />
// get sheet by name<br />
Sheet sheet = workbookPart.Workbook.Descendants&lt;Sheet&gt;().Where(s =&gt; s.Name == "Sheet1").FirstOrDefault();</p>
<p>// get worksheetpart by sheet id<br />
WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;</p>
<p>// The SheetData object will contain all the data.<br />
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();</p>
<p>Cell formulaCell = InsertCellInWorksheet("B", 10, worksheetPart);<br />
formulaCell.DataType = new EnumValue&lt;CellValues&gt;(CellValues.Number);<br />
formulaCell.CellFormula = new CellFormula("SUM(B2:B7)");</p>
<p>// Save the worksheet.<br />
worksheetPart.Worksheet.Save();</p>
<p>// for recacluation of formula<br />
spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;<br />
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;</p>
<p>}<br />
}</p>
<p>private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)<br />
{<br />
Worksheet worksheet = worksheetPart.Worksheet;<br />
SheetData sheetData = worksheet.GetFirstChild&lt;SheetData&gt;();<br />
string cellReference = columnName + rowIndex;</p>
<p>// If the worksheet does not contain a row with the specified row index, insert one.<br />
Row row;<br />
if (sheetData.Elements&lt;Row&gt;().Where(r =&gt; r.RowIndex == rowIndex).Count() != 0)<br />
{<br />
row = sheetData.Elements&lt;Row&gt;().Where(r =&gt; r.RowIndex == rowIndex).First();<br />
}<br />
else<br />
{<br />
row = new Row() { RowIndex = rowIndex };<br />
sheetData.Append(row);<br />
}</p>
<p>// If there is not a cell with the specified column name, insert one.<br />
if (row.Elements&lt;Cell&gt;().Where(c =&gt; c.CellReference.Value == columnName + rowIndex).Count() &gt; 0)<br />
{<br />
return row.Elements&lt;Cell&gt;().Where(c =&gt; c.CellReference.Value == cellReference).First();<br />
}<br />
else<br />
{<br />
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.<br />
Cell refCell = null;<br />
foreach (Cell cell in row.Elements&lt;Cell&gt;())<br />
{<br />
if (cell.CellReference.Value.Length == cellReference.Length)<br />
{<br />
if (string.Compare(cell.CellReference.Value, cellReference, true) &gt; 0)<br />
{<br />
refCell = cell;<br />
break;<br />
}<br />
}<br />
}</p>
<p>Cell newCell = new Cell() { CellReference = cellReference };<br />
row.InsertBefore(newCell, refCell);</p>
<p>worksheet.Save();<br />
return newCell;<br />
}<br />
}</p>
<p>

Using EPPlus

https://www.nuget.org/packages/EPPlus/

</p>
<p>public void UpdateExcelUsingEPPlus(string fileName)<br />
{<br />
FileInfo fileInfo = new FileInfo(fileName);<br />
ExcelPackage p = new ExcelPackage(fileInfo);<br />
ExcelWorksheet myWorksheet = p.Workbook.Worksheets["Sheet1"];</p>
<p>myWorksheet.Cells["B15"].Formula = "SUM(B2:B7)";</p>
<p>p.Save();</p>
<p>}</p>
<p>

Hope it helps..

Advertisements

Update Cell value in Excel Spreadsheet using C# (Open XML and EPPlus library)


Sharing a sample code that updates a particular cell’s value in Excel Spreadsheet.

Here we are updating cell B4

First using Open XML SDK

</p>
<p>public static void UpdateExcelUsingOpenXMLSDK(string fileName)<br />
{<br />
// Open the document for editing.<br />
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(fileName, true))<br />
{<br />
// Access the main Workbook part, which contains all references.<br />
WorkbookPart workbookPart = spreadSheet.WorkbookPart;<br />
// get sheet by name<br />
Sheet sheet = workbookPart.Workbook.Descendants&lt;Sheet&gt;().Where(s =&gt; s.Name == "Sheet1").FirstOrDefault();</p>
<p>// get worksheetpart by sheet id<br />
WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;</p>
<p>// The SheetData object will contain all the data.<br />
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();</p>
<p>Cell cell = GetCell(worksheetPart.Worksheet, "B", 4);</p>
<p>cell.CellValue = new CellValue("10");<br />
cell.DataType = new EnumValue&lt;CellValues&gt;(CellValues.Number);</p>
<p>// Save the worksheet.<br />
worksheetPart.Worksheet.Save();</p>
<p>// for recacluation of formula<br />
spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;<br />
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;</p>
<p>}<br />
}</p>
<p>private static Cell GetCell(Worksheet worksheet,<br />
string columnName, uint rowIndex)<br />
{<br />
Row row = GetRow(worksheet, rowIndex);</p>
<p>if (row == null) return null;</p>
<p>var FirstRow = row.Elements&lt;Cell&gt;().Where(c =&gt; string.Compare<br />
(c.CellReference.Value, columnName +<br />
rowIndex, true) == 0).FirstOrDefault();</p>
<p>if (FirstRow == null) return null;</p>
<p>return FirstRow;<br />
}</p>
<p>private static Row GetRow(Worksheet worksheet , uint rowIndex)<br />
{<br />
Row row = worksheet.GetFirstChild&lt;SheetData&gt;().<br />
Elements&lt;Row&gt;().FirstOrDefault(r =&gt; r.RowIndex == rowIndex);<br />
if (row == null)<br />
{<br />
throw new ArgumentException(String.Format("No row with index {0} found in spreadsheet", rowIndex));<br />
}<br />
return row;<br />
}</p>
<p>

Now the same code using EPPlus

https://www.nuget.org/packages/EPPlus/

</p>
<p>public void UpdateExcelUsingEPPlus(string fileName)<br />
{<br />
FileInfo fileInfo = new FileInfo(fileName);<br />
ExcelPackage p = new ExcelPackage(fileInfo);<br />
ExcelWorksheet myWorksheet = p.Workbook.Worksheets["Sheet1"];<br />
myWorksheet.Cells[4, 2].Value = 10;<br />
p.Save();</p>
<p>}</p>
<p>

Hope it helps..

Advertisements

Sample Code to consume UNData API using JavaScript


Sharing a sample code that can be used to consume UNData API

http://data.un.org/Host.aspx?Content=API

The below code fetches the population data for country India.

Sample Code


var xmlHttp;
// creatng the xmlHttp object
xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
// Calling the web service using post and true means asynchronous call
xmlHttp.open("POST", "http://data.un.org/WS/NSIEstatV20Service.asmx", false);

// Setting the request header to let the web service find the soap request we would be sending
xmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");

xmlHttp.setRequestHeader("SOAPAction", "http://ec.europa.eu/eurostat/sri/service/2.0/extended/GetCompactData");

var soapRequest = "<!--?<span class="hiddenSpellError" pre="" data-mce-bogus="1"-->xml version='1.0' encoding='utf-8'?>"+
"<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+
"<soap:Body>"+
"<GetCompactData xmlns='http://ec.europa.eu/eurostat/sri/service/2.0/extended'>"+
"<Query>"+
"<message:QueryMessage xsi:schemaLocation='http://ec.europa.eu/eurostat/sri/service/2.0/SDMXMessage.xsd http://ec.europa.eu/eurostat/sri/service/2.0/%20SDMXMessage.xsd' xmlns:generic='http://www.SDMX.org/resources/SDMXML/schemas/v2_0/generic' xmlns:message='http://www.SDMX.org/resources/SDMXML/schemas/v2_0/message' xmlns:query='http://www.SDMX.org/resources/SDMXML/schemas/v2_0/query' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>"+
"<message:Header>"+
"<message:ID>onh14833545733929527</message:ID>"+
"<message:Test>false</message:Test>"+
"<message:Prepared>2017-01-01</message:Prepared>"+
"<message:Sender id='online-help' />"+
"</message:Header>"+
"<message:Query>"+
"<query:DataWhere>"+
"<query:And>"+
"<query:Dimension id='INDICATOR'>SP_POP_TOTL</query:Dimension>"+
"<query:Dimension id='AGE'>_T</query:Dimension>"+
"<query:Dimension id='SEX'>_T</query:Dimension>"+
"<query:Dimension id='LOCATION'>_T</query:Dimension>" +
"<query:Dimension id='REF_AREA'>DEU</query:Dimension>" +
"<query:Dimension id='SCENARIO'>H</query:Dimension>"+
"<query:Time>"+
"<query:StartTime>2014-01-02</query:StartTime>"+
"<query:EndTime>2017-01-02</query:EndTime>"+
"</query:Time>"+
"<query:Dataflow>DF_UNDATA_WPP</query:Dataflow>"+
"</query:And>"+
"</query:DataWhere>"+
"</message:Query>"+
"</message:QueryMessage>"+
"</Query>"+
"</GetCompactData>"+
"</soap:Body>" +
"</soap:Envelope>";

xmlHttp.send(soapRequest);
var xmlDoc = jQuery.parseXML(xmlHttp.responseText);
var popValue = xmlDoc.getElementsByTagName('ns1:Obs')['0'].attributes['OBS_VALUE'].nodeValue;
alert("Total Population " + popValue);

Output


<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body><GetCompactDataResponse
xmlns="http://ec.europa.eu/eurostat/sri/service/2.0/extended">
<GetCompactDataResult>
<CompactData
xmlns:ns1="urn:sdmx:org.sdmx.infomodel.datastructure.DataStructure
=UNSD:DSD_WPP_UNDATA(1.0):ObsLevelDim:TIME_PERIOD"
xmlns="http://www.SDMX.org/resources/SDMXML/schemas/v2_0/message"
xmlns:common="http://www.SDMX.org/resources/SDMXML/schemas/v2_0/common"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xml="http://www.w3.org/XML/1998/namespace"><Header>
<ID>DF_UNDATA_WPP</ID><Test>false</Test><Prepared>2017-01-06T08:25:46
</Prepared>
<Sender id="1B0">
<Name xml:lang="en">United Nations Statistics Division</Name>
<Contact><Email>statistics@un.org</Email></Contact></Sender>
<KeyFamilyRef>DSD_WPP_UNDATA</KeyFamilyRef><KeyFamilyAgency>UNSD
</KeyFamilyAgency><DataSetAgency>UNSD</DataSetAgency>
<DataSetID>DF_UNDATA_WPP_1B0_2017-01-06T082546</DataSetID>
<DataSetAction>Information</DataSetAction>
<Extracted>2017-01-06T08:25:46</Extracted>
<Source xml:lang="en">
United Nations Population Division World Population Prospects</Source>
</Header>
<ns1:DataSet DATASET_NAME="World Population Prospects"
DATASET_SOURCE="United Nations Population Division"
DATASET_DESC="The United Nations World Population Prospects contains estimates and projections for every country in the world, including estimates and projections of demographic indicators such as birth rates, deaths rates, infant mortality rates and life expectancy." UNDATA_LASTUPDATE="20 Aug 2013" UNDATA_NEXTUPDATE="2015 release" METADATA="http://esa.un.org/unpd/wpp/index.htm"><ns1:Series AGE="_T" FREQ="A" INDICATOR="SP_POP_TOTL" LOCATION="_T" SEX="_T" REF_AREA="DEU" SCENARIO="H"><ns1:Obs TIME_PERIOD="2015" OBS_VALUE="83178.615" UNIT="NUMBER" UNIT_MULT="3" TIME_SPAN="2015" /></ns1:Series></ns1:DataSet></CompactData></GetCompactDataResult></GetCompactDataResponse></soap:Body></soap:Envelope>

Hope it helps.

Sample code to consume UNData API in C#


Hi,

Recently we were working on an application that had to fetch country specific details. For this we used the UNData API.

http://data.un.org/Host.aspx?Content=API

We can make use of SDMX Browser in helping us to write a query

http://data.un.org/SdmxBrowser/start

Create a console app\windows application and add web reference to the following web service

http://data.un.org/WS/NSIEstatV20Service.asmx

Below is the sample C# Code


string queryMsg = @"<message:QueryMessage
xsi:schemaLocation='http://ec.europa.eu/eurostat/sri/service/2.0/SDMXMessage.xsd
http://ec.europa.eu/eurostat/sri/service/2.0/%20SDMXMessage.xsd'
xmlns:generic='http://www.SDMX.org/resources/SDMXML/schemas/v2_0/generic'
xmlns:message='http://www.SDMX.org/resources/SDMXML/schemas/v2_0/message'
xmlns:query='http://www.SDMX.org/resources/SDMXML/schemas/v2_0/query'
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
<message:Header>
<message:ID>onh14833545733929527</message:ID>
<message:Test>false</message:Test>
<message:Prepared>2017-01-01</message:Prepared>
<message:Sender id='online-help'/>
</message:Header>
<message:Query>
<query:DataWhere>
<query:And>
<query:Dimension id='INDICATOR'>SP_POP_TOTL</query:Dimension>
<query:Dimension id='AGE'>_T</query:Dimension>
<query:Dimension id='SEX'>_T</query:Dimension>
<query:Dimension id='LOCATION'>_T</query:Dimension>
<query:Dimension id='REF_AREA'>IND</query:Dimension>
<query:Time>
<query:StartTime>2014-01-02</query:StartTime>
<query:EndTime>2017-01-02</query:EndTime>
</query:Time>
<query:Dataflow>DF_UNDATA_WPP</query:Dataflow>
</query:And>
</query:DataWhere>
</message:Query>
</message:QueryMessage>";
NSIEstatV20Service service = new NSIEstatV20Service();
System.Xml.XmlDocument queryMessageDocument = new XmlDocument();
queryMessageDocument.LoadXml(queryMsg);
System.Xml.XmlNode queryMessage = queryMessageDocument.DocumentElement;
var result = service.GetCompactData(queryMessage);
MessageBox.Show(result.InnerXml);

The output

The query here basically returns the Total population of India

Hope it helps..

Code to upload multiple attachments to SharePoint Folder using Client Object Model



public static void UploadDocument(
string siteURL,
string documentListName,
string documentListURL,
string documentName,
byte[] documentStream,
string folderName,
string invoiceId)
{
using (var clientContext = new ClientContext(siteURL))
{
// Get Document List
var documentsList = clientContext.Web.Lists.GetByTitle(documentListName);

// check if folder already exists else create folder

if (!FolderExists(clientContext.Web, documentListName, folderName))
{
var info = new ListItemCreationInformation();
info.UnderlyingObjectType = FileSystemObjectType.Folder;
info.LeafName = folderName.Trim();
var newItem = documentsList.AddItem(info);
newItem["Title"] = folderName;
newItem.Update();
clientContext.ExecuteQuery();
}

var fileCreationInformation = new FileCreationInformation();

// Assign to content byte[] i.e. documentStream
fileCreationInformation.Content = documentStream;

// Allow owerwrite of document
fileCreationInformation.Overwrite = true;

// Upload URL
fileCreationInformation.Url = siteURL + documentListURL + folderName + "/" + documentName;
var uploadFile = documentsList.RootFolder.Files.Add(fileCreationInformation);

// Update the metadata for a field having name "DocType"
uploadFile.ListItemAllFields["Invoice_x0020_Id"] = invoiceId;

uploadFile.ListItemAllFields.Update();
clientContext.ExecuteQuery();
}
}


public static bool FolderExists(Web web, string listTitle, string folderUrl)
{
var list = web.Lists.GetByTitle(listTitle);
var folders = list.GetItems(CamlQuery.CreateAllFoldersQuery());
web.Context.Load(list.RootFolder);
web.Context.Load(folders);
web.Context.ExecuteQuery();
var folderRelativeUrl = string.Format("/{0}/{1}", list.RootFolder.Name, folderUrl);
return Enumerable.Any(folders, folderItem => (string)folderItem["FileRef"] == folderRelativeUrl);
}

Extracting Attachment from InfoPath form using C#


The sample code for extracting attachment from submitted InfoPath form

var lstTimeSheet = new List<RelatedTimesheet>();
 foreach (var timeSheet in invoiceFormData.Summary.Timesheet)
 {
 if (timeSheet.Attachment != null)
 {
 var tSheet = new RelatedTimesheet();
 var b = timeSheet.Attachment;
 var nameBufferLen = b[20] * 2;
 var fileNameBufffer = new byte[nameBufferLen];
 for (var i = 0; i < nameBufferLen; i++)
 {
 fileNameBufffer[i] = b[24 + i];
 }

 var charFileName = Encoding.Unicode.GetChars(fileNameBufffer);
 var fileName = new string(charFileName);
 tSheet.FileName = fileName.Substring(0, fileName.Length - 1); 

 var fileContent = new byte[b.Length - (24 + nameBufferLen)];
 for (var i = 0; i < fileContent.Length; i++)
 {
 fileContent[i] = b[24 + nameBufferLen + i];
 }

 tSheet.Attachment = fileContent;
 lstTimeSheet.Add(tSheet);
 }
 }

public class RelatedTimesheet
{
 public string FileName { get; set; }
 public byte[] Attachment { get; set; }
}