Epplus Number Format

EPPlus number format

Here are some number format options for EPPlus:

//integer (not really needed unless you need to round numbers, Excel will use default cell properties)
ws.Cells["A1:A25"].Style.Numberformat.Format = "0";

//integer without displaying the number 0 in the cell
ws.Cells["A1:A25"].Style.Numberformat.Format = "#";

//number with 1 decimal place
ws.Cells["A1:A25"].Style.Numberformat.Format = "0.0";

//number with 2 decimal places
ws.Cells["A1:A25"].Style.Numberformat.Format = "0.00";

//number with 2 decimal places and thousand separator
ws.Cells["A1:A25"].Style.Numberformat.Format = "#,##0.00";

//number with 2 decimal places and thousand separator and money symbol
ws.Cells["A1:A25"].Style.Numberformat.Format = "€#,##0.00";

//percentage (1 = 100%, 0.01 = 1%)
ws.Cells["A1:A25"].Style.Numberformat.Format = "0%";

//accounting number format
ws.Cells["A1:A25"].Style.Numberformat.Format = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-";

Don't change the decimal and thousand separators to your own
localization. Excel will do that for you.

By request some DateTime formatting options.

//default DateTime pattern
worksheet.Cells["A1:A25"].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;

//custom DateTime pattern
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "dd-MM-yyyy HH:mm";

EPPlus Format Cell as Accounting Number

EPPlus does not include the number formats built into Excel so you must set it manually.

All the built-in formats in Excel have an actual number format. For accounting, the format is:

-$* #,##0.00-;-$* #,##0.00_-;-$* "-"??-;-@-

The format above may differ depending on your region/settings. To see what the format is for any built-in formats:

Right-click a cell and select Format Cells
Click on your built-in format, in this case Accounting
Click on Custom to see the format of the previously selected
format

Apply the format to your cells:

ws.Cells["A1:A5"].Style.Numberformat.Format = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-";

EPPlus Number Format according to different Culture

After many trials like

cell.Value = Convert.ToDouble(cellValue,new CultureInfo("it-IT").NumberFormat);

and

cell.Value = Convert.ToDouble(cellValue).ToString("N",System.Globalization.CultureInfo.CreateSpecificCulture("it-IT")).Split(',')[0];

I understood that number format or number according to culture is dependent on your system's region. If numbers are getting converted to different culture and were not showing or showing wrong format, that is cause of local system's region. If u change ur region to that particular culture. You'll get the perfect answer.
I just did as per my country format in code like using

cell.Style.Numberformat.Format = "#,##0";

it worked fine with this. After committing code to svn, when I tried the Export of Excel from main website it was working beautifully according to system's region. If I change to Italian, Excel output numbers were in italian format and when I change to Indian , the numbers were in Indian number format.

So Don't worry about whatever culture u want, do write according to ur culture all work's fine.
Over this if u still want to get number according to particular culture use the above second code, but problem is u will get numbers stored as text. that u can't add or do any valuations.

Custom number format with unit in EPPlus

convert the value to string

worksheet.Cells[row, col].Style.Numberformat.Format = @"@"" Mhz""";
worksheet.Cells[row, col].Value = "10.5";

C# formatting excel column with epplus as negative number with prentices

I figured it out myself. The only issue with this is if you have a large amount of data formatting the individual cell will slow down the export.

private static void CreateData(ExcelWorksheet workSheet, ref int rowIndex, DataTable dt)
    {
        int colIndex = 0;
        int intNumberCheck = 0;

        foreach (DataRow dr in dt.Rows) // Adding Data into rows
        {
            colIndex = 1;
            rowIndex++;

            foreach (DataColumn dc in dt.Columns)
            {
                var cell = workSheet.Cells[rowIndex, colIndex];

                bool isNumber = Int32.TryParse(dr[dc.ColumnName], out intNumberCheck);

                if (isNumber == true)
                {
                    //Setting Value in cell
                    
                    cell.Value = Convert.ToInt32(dr[dc.ColumnName]);
                    cell.Style.Numberformat.Format = "#,##0 ;[Red](#,##0)";
                    
                }
                else
                {
                    //Setting Value in cell
                    cell.Value = Convert.ToString(dr[dc.ColumnName]).Trim();
                }

                colIndex++;

            }
        }

        dt.Dispose();
    }

C# EPPlus multiple cell formatting not applying

Well, there are a couple of errors. First, you're saving before setting the formatting, so it's not being applied.

Second, Excel addresses are base 1, it doesn't exist "C0" and "P0". Also note that in the first row is the columns titles, so you probably want rows 2 and 3. Try the following:

ws.Cells["C2:P2"].Style.Numberformat.Format = "#,###,##0.0%;(#,###,##0.0%)";
ws.Cells["C3:P3"].Style.Numberformat.Format = "$##,##0.0;($##,##0.0)";
obj.Save();