ClosedXML convert percentage in template

Question

I`m using closedXML to generate a simple template with 3 columns.

To create this template i`m using this code:

protected void btnTemplate_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            var tipo = "TARGET";
            if (ddlTipo.SelectedValue == "RESULTADO")
            {
                tipo = "RESULTADO";
            }
            dt.Columns.AddRange(new DataColumn[3] { 
           new DataColumn("BU", typeof(string)),
          new DataColumn("MÉTRICA", typeof(string)),
          
          new DataColumn(tipo,typeof(string)) });
            //Exporting to Excel
            
            //Codes for the Closed XML
            using (XLWorkbook wb = new XLWorkbook())
            {
                
                var worksheet = wb.Worksheets.Add(dt, "BASE");
                worksheet.Cell("C1").DataType = XLDataType.Text;

                //wb.SaveAs(folderPath + "DataGridViewExport.xlsx");
                string myName = ("Template.xlsx");
                MemoryStream stream = GetStream(wb);// The method is defined below
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition",
                "attachment; filename=" + myName);
                Response.ContentType = "application/vnd.ms-excel";
                Response.BinaryWrite(stream.ToArray());
                Response.End();
            }


        }

Its working fine, the problem is in the C column, it can receive percentage, and it changed the type in the excel.

Is there a way to force in closedXML that the column C is always treated as TEXT? To not convert the numbers. i tried using worksheet.Cell("C1").DataType = XLDataType.Text; to force as text,but it doesnt`t work.

try using setter, `worksheet.Column("C").SetDataType(XLDataType.Text);` — coder_b, Nov 26 '20 at 21:52

score 1 · Accepted Answer · answered Nov 26 '20 at 21:21

Well I never used closedXML I rater EPPLUS (version 4.5.3.3 is totally free), But I have a lot of cases like yours, what I do is set the type of the column as text, in closed xml you can do:

worksheet.Cell(rowIndex, columnIndex).Style.NumberFormat.Format = "@";

But there's a simpler solution that works for any Excel XML library! Instead of creating your excel template with code you can create it manually and save on a folder in your project. Setup any attribute you want to with the Excel interface itself save it, and then open the file with closed xml and just set the values wherever you want to. Last 'save as' somewhere (dont overrite the template) and do what you need.

there is a lot benefits in doing like this:

1 - A few time to create the template (compared with coding).

2 - Reduce your code.

3 - You can place formulas easier.

4 - No need to change code to modify the template.

5 - Any one who knows a bit of Excel can change the template.

ClosedXML convert percentage in template

1 Answers1