CODEWITHSUNDEEP
asp.netexcelepplus
Danieboy
Danieboy

Reputation: 4511

How do I change the format of a specific column in EPPlus?

I've used EPPlus to download my datatable from my website / database to an Excel sheet and the first picture is the result I get. The second picture is what I would like it to be.

Questions:

Obviously title would still be a string format.

So that 80% of the message isn't hidden and you have to drag the column out to read the entire message.

Edit: Completely forgot to add my code...

public ActionResult ExportData()
        {
            DataTable dataTable = GetData();

            using (ExcelPackage package = new ExcelPackage())
            {
                var ws = package.Workbook.Worksheets.Add("My Sheet");
                //true generates headers
                ws.Cells["1:1"].Style.Font.Bold = true;
                ws.Cells["A1"].LoadFromDataTable(dataTable, true);
                ws.Cells[ws.Dimension.Address].AutoFitColumns();

                var stream = new MemoryStream();
                package.SaveAs(stream);

                string fileName = "Log.xlsx";
                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                stream.Position = 0;
                return File(stream, contentType, fileName);
            }
        }

        public DataTable GetData()
        {
            DataTable dt = new DataTable();

            if (ModelState.IsValid)
            {
                using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString))
                {
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.Parameters.AddWithValue("@val1", Session["myID"]);
                        comm.Parameters.AddWithValue("@val2", "%" + Session["mySearchString"] + "%");
                        comm.CommandText = "SELECT * FROM dbo.Log WHERE CustomerId = @val1 AND Message LIKE @val2";
                        try
                        {
                            conn.Open();
                            dt.Load(comm.ExecuteReader());
                        }
                        catch (SqlException e)
                        {
                            throw new Exception(e.ToString());
                        }
                    }
                }
            }
            return dt;
        }

enter image description here

enter image description here

Upvotes: 1

Views: 1431

Answers (1)

Ernie S
Ernie S

Reputation: 14250

Just need to set the Numberformat.Format string. Like this:

ws.Column(2).Style.Numberformat.Format = "hh:mm:ss";

If you want to customize the actual just there are plenty of resource online like http://www.ozgrid.com/Excel/excel-custom-number-formats.htm. Or you can just open it in excel, set the format to Custom and experiment with the string.

Upvotes: 1

Related Questions