CODEWITHSUNDEEP
c#datatableepplus
gene
gene

Reputation: 2108

How to get a value from Excel spreadsheet cell using EPPLUS?

I'm using EPPlus library to create excel from datatable.

This is what I do:

using (ExcelPackage pck = new ExcelPackage())
{
   ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
   ws.Cells["A5:I5"].LoadFromDataTable(dt, true); 
   ws.DefaultColWidth = 25;

   var headerCell = ws.Cells["A5:I5"];
   headerCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
   headerCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BurlyWood);
   var headerFont = headerCell.Style.Font;
   headerFont.Bold = true; 

   int totalRow = ws.Dimension.End.Row;
   int totalCol = ws.Dimension.End.Column;
   using (ExcelRange rng = ws.Cells[6,1,totalRow,totalCol])   
   {
       rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
       rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Bisque);

       long n = 0;
       for (int row = 6; row <= totalRow; row++)
       {
           for (int col = 1; col <= 9; col++)
           {
                string colVol = (string)ws.Cells[row, col].Value;
                bool isNumeric = long.TryParse(colVol, out n);
                if (isNumeric && colVol.Length > 10)
                {
                      //ws.Cells[row, col] //need to apply a css style
                }
           }
       }
   }
   ws.Cells["A4"].LoadFromText(name);
   Response.AddHeader("content-disposition", "inline;filename=" + name + ".xls");
   Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
   Response.BinaryWrite(pck.GetAsByteArray());   
}

I need to apply the styles to some cells in spreadsheet that meet certain condition.

This is how it is done before when code was building the table in the loop:

for (int i = 0; i < dt.Columns.Count; i++)
{
    td = new TableCell();
    td.Text = dt.Rows[j][i].ToString();

    n = 0;
    bool isNumeric = long.TryParse(td.Text, out n);
    if (isNumeric && td.Text.Length > 10)
       td.Attributes.Add("style", @"mso-number-format:\@");

    tr.Cells.Add(td);
 }

How can I get the value from the cell to check for the condition to format the value using EPPlus approach?

Upvotes: 2

Views: 5172

Answers (1)

Ernie S
Ernie S

Reputation: 14250

(updated answer to reflect OP Question edits and comments)

Just do:

ws.Cells[row, col].Style.Numberformat.Format = "@";

This will tell excel to use a text format instead of numeric.

Upvotes: 1

Related Questions