CODEWITHSUNDEEP
c#excel
Nic
Nic

Reputation: 1310

Set DataType in Excel Cell using OfficeOpenXml in C#

I am using the OfficeOpenXml library to create Excel files in C#. Specifically, I need to set the datatype of a specific cell to EUR programmatically. For example 1234.5 must be turned into 1234.5 €.

With the UI, this operation is fairly easy RightClick -->Format Cells --> Number --> Currency --> Symbol (see attached image). enter image description here.

Below is my code. Any clue on how to do that? 

string fullpath = @"\\SOME_PATH\test_file.xlsx";

// if file exists, overwrite
if (File.Exists(fullpath))
    File.Delete(fullpath);

var pck = new ExcelPackage(new FileInfo(fullpath));

var workSheet = pck.Workbook.Worksheets.Add("contract summary");

workSheet.Cell(1, 1).DataType = "Currency"; // this does not work.

workSheet.Cell(1, 1).Value = 1234.5.ToString();

Upvotes: 0

Views: 2518

Answers (2)

SteveCav
SteveCav

Reputation: 6729

What you're looking for is

workSheet.Cells[1,1].Style.Numberformat.Format = "0.00 €";

Upvotes: 1

Jeremy
Jeremy

Reputation: 161

I just tested the following code, and it successfully creates a custom format with the using Excel = Microsoft.Office.Interop.Excel; library.

workSheet.Cells[1, 1].NumberFormat = "#,###,###.00 €";

I believe it can also be used on columns, rows, or groups of cells.

I'm not sure how similar Microsoft.Office.Interop.Excel is to OpenOfficeXML, but I hope that can at least point you in the right direction.

Upvotes: 0

Related Questions