Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

623
Visualizações
Using EPPlus how can I generate a spreadsheet where numbers are numbers not text

I am creating a spreadsheet from a List<object[]> using LoadFromArrays

The first entry of the array is a title, the other entries are possibly numbers, text or dates (but the same for each array in the list).

The generated Excel sheet has the green triangle warning that numbers are formatted as text.

I loop through all the cells and set their format to Number like so ws.Cells[i, j].Style.Numberformat.Format = "0";

However the problem remains and I still see the green warning, even though the number format is set to number when I look in the Format Cell... dialogue.

What are my options here? It is possible for me to know a bit more about what type is in each column, but how do I then set a column title?

Is there a better solution than EPPlus? or some more post processing of the spreadsheet I can do before downloading it?

over 4 years ago · Santiago Trujillo
3 Respostas
Responde à pergunta

0

Since you are using objects arrays they can contain numbers and strings that look like numbers you will have to go through each object and determine its type:

[TestMethod]
public void Object_Type_Write_Test()
{
    //http://stackoverflow.com/questions/31537981/using-epplus-how-can-i-generate-a-spreadsheet-where-numbers-are-numbers-not-text
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    //Some data
    var list = new List<Object[]>
    {
        new object[]
        {
            "111.11",
            111.11,
            DateTime.Now
        }
    };

    using (var package = new ExcelPackage(existingFile))
    {
        var ws = package.Workbook.Worksheets.Add("Sheet1");
        ws.Cells[1, 1, 2, 2].Style.Numberformat.Format = "0";
        ws.Cells[1, 3, 2, 3].Style.Numberformat.Format = "[$-F400]h:mm:ss\\ AM/PM";

        //This will cause numbers in string to be stored as string in excel regardless of cell format
        ws.Cells["A1"].LoadFromArrays(list);

        //Have to go through the objects to deal with numbers as strings
        for (var i = 0; i < list.Count; i++)
        {
            for (var j = 0; j < list[i].Count(); j++)
            {

                if (list[i][j] is string)
                    ws.Cells[i + 2, j + 1].Value = Double.Parse((string) list[i][j]);
                else if (list[i][j] is double)
                    ws.Cells[i + 2, j + 1].Value = (double)list[i][j];
                else
                    ws.Cells[i + 2, j + 1].Value = list[i][j];

            }
        }

        package.Save();
    }
}

With the above, you see the image below as the output Note the upper left corner cell with the green arrow because it was a string that was written by LoadFromArray which looks like a number:

Excel Output

over 4 years ago · Santiago Trujillo Relatório

0

I created an extension method LoadFormulasFromArray, based on EPPlus LoadFromArray. The method assumes all objects in the list are to be treated as formulas (as opposed to LoadFromArray). The big picture is that both Value and Formula properties take string instead of a specific Type. I see this as a mistake because there's no way to differentiate if the string is Text or Formula. Implementing a Formula Type would enable overloading and type checking thus making it possible to always do the right thing.

// usage: ws.Cells[2,2].LoadFormulasFromArrays(MyListOfObjectArrays)

public static class EppPlusExtensions
{
    public static ExcelRangeBase LoadFormulasFromArrays(this ExcelRange Cells, IEnumerable<object[]> Data)
    {
        //thanx to Abdullin for the code contribution
        ExcelWorksheet _worksheet = Cells.Worksheet;
        int _fromRow = Cells.Start.Row;
        int _fromCol = Cells.Start.Column;
        if (Data == null) throw new ArgumentNullException("data");

        int column = _fromCol, row = _fromRow;

        foreach (var rowData in Data)
        {
            column = _fromCol;
            foreach (var cellData in rowData)
            {
                Cells[row, column].Formula = cellData.ToString();
                column += 1;
            }
            row += 1;
        }
        return Cells[_fromRow, _fromCol, row - 1, column - 1];
    }
}
over 4 years ago · Santiago Trujillo Relatório

0

The trick is to not pass the numbers as "raw objects" to EPPlus but casting them properly.

Here's how I did that in a DataTable-to-Excel export method I made with EPPlus:

if (dc.DataType == typeof(int)) ws.SetValue(row, col, !r.IsNull(dc) ? (int)r[dc] : (int?)null);
else if (dc.DataType == typeof(decimal)) ws.SetValue(row, col, !r.IsNull(dc) ? (decimal)r[dc] : (decimal?)null);
else if (dc.DataType == typeof(double)) ws.SetValue(row, col, !r.IsNull(dc) ? (double)r[dc] : (double?)null);
else if (dc.DataType == typeof(float)) ws.SetValue(row, col, !r.IsNull(dc) ? (float)r[dc] : (float?)null);
else if (dc.DataType == typeof(string)) ws.SetValue(row, col, !r.IsNull(dc) ? (string)r[dc] : null);
else if (dc.DataType == typeof(DateTime))
{
    if (!r.IsNull(dc))
    {
        ws.SetValue(row, col, (DateTime)r[dc]);
        // Change the following line if you need a different DateTime format
        var dtFormat = "dd/MM/yyyy";
        ws.Cells[row, col].Style.Numberformat.Format = dtFormat;
    }
    else ws.SetValue(row, col, null);
}

IMPORTANT: It's worth noting that DateTime values will require more work to be handled properly, since we would want to have it formatted in a certain way AND, arguably, support NULL values in the column: the above method fullfills both these requirements.

I published the full code sample (DataTable to Excel file with EPPlus) in this post on my blog.

over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda