How to – 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.

Dll not getting copied to bin directory in Visual Studio


Hi,

In one of our projects we were referencing custom assemblies that were part of the same solution. However those assemblies were not getting copied to the bin directory of the project upon building the project.

We had also set property Copy Local as true for those class library projects.

The reason for this was as we were adding those assemblies in GAC.

VS.NET will copy the dll to the bin directory if your system cannot find the dll in the GAC.”

After removing those assemblies from GAC it added those dlls in the bin.

The helpful thread

http://stackoverflow.com/questions/3548731/visual-studio-2010-add-reference-is-copying-dll-to-bin-directory

Hope it helps..

Using EncryptedXml class for encrypting XML file in C#


Hi,

We had a requirement to encrypt a particular node and its corresponding child elements in one of our xml file. While searching for the best and the simplest way to do so, I came across these wonderful posts which make use of EncrptedXml class.

http://www.devx.com/dotnet/Article/21564/1954

http://dotnetslackers.com/articles/xml/XMLEncryption.aspx

http://blogs.msdn.com/b/shawnfa/archive/2003/11/14/57032.aspx

Hope it helps

.NET Framework missing from Visual Studio 2010 new project dialog box


To resolve this issue, download and install .NET Framework 3.5 sp1.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ab99342f-5d1a-413d-8319-81da479ab0d7&displaylang=en

Or if it is Windows Server 2008 R2,  just enable the .NET Framework 3.5.1 Features using Server Manager or else you will get the following error

“You must use role management tool to install or configure Microsoft .NET Framework 3.5”

Bye.

Sign an already built assembly


Hi,

These are the steps to be followed to sign an already built assembly

Open Visual Studio Command prompt.

Generate a KeyFile:

sn -k keyPair.snk

Obtain the MSIL for the provided assembly:

ildasm myAssembly.dll /out:myAssembly.il

Rename/move the original assembly

Create a new assembly from the MSIL output and your assembly

ilasm myAssembly.il /dll /key= keyPair.snk

Bye..

%d bloggers like this: