how to set column value cell in excel for date dd-mmm-yyyy using openoffice xml

See more:

C#

hi,
I want to set the excel columns values to date (UK) and format is dd-MMM-yyyy
how to set date value in openofficexml.and first rows header font bold

my code:

protected void btnExportEarnings_Click(object sender, EventArgs e)
    {
        try
        {
            FileInfo newFile;
            FileInfo template;
            OfficeOpenXml.ExcelCell cell;
            OfficeOpenXml.ExcelPackage xlPackage;
            OfficeOpenXml.ExcelWorksheet worksheet;
            const int startRow = 2;
           
            int row = startRow;
            string strTemplateFilePath = "", strNewFileName = "", strTemplateFileName = "";
            strTemplateFilePath = System.Configuration.ConfigurationManager.AppSettings["TemplatePath"].ToString();
            strTemplateFileName = strTemplateFilePath + "EarningsTemplate.xlsx";
            strNewFileName = strTemplateFilePath + "Earnings_" + DateTime.Now.Month + "." + DateTime.Now.Day + "." + DateTime.Now.Year + "_" + DateTime.Now.Hour + "." + DateTime.Now.Minute + "." + DateTime.Now.Second + ".xlsx";

            //strCName = strCName.Replace("(", "_").Replace(")", "_");
            strNewFileName = strNewFileName.Replace("(", "_").Replace(")", "_");
            
            template = new FileInfo(strTemplateFileName);
            newFile = new FileInfo(strNewFileName);
            SqlConnection con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "uspGetAllEarningsByQuarter";
                cmd.Connection = con;
                con.Open();
                if (ddlQuarterMonth1.SelectedIndex > 0)
                {
                    cmd.Parameters.AddWithValue("@QuarterMonthID", Convert.ToInt32(ddlQuarterMonth1.SelectedValue.ToString()));
                }
                else
                {
                    cmd.Parameters.AddWithValue("@QuarterMonthID", DBNull.Value);
                }
                if (ddlQuarterYear1.SelectedIndex > 0)
                {
                    cmd.Parameters.AddWithValue("@QuarterYearID", Convert.ToInt32(ddlQuarterYear1.SelectedValue.ToString()));
                }
                else
                {
                    cmd.Parameters.AddWithValue("@QuarterYearID", DBNull.Value);
                }
                da.SelectCommand = cmd;
                da.Fill(ds);
                con.Close();
            }
            catch (Exception ex)
            {
                //throw ex;
                AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(this, typeof(string), "Error", "alert('Problem while retrieving data. Please try again later.');", true);
                return;
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (File.Exists(strNewFileName))
                {
                    File.Delete(strNewFileName);
                }
                else
                {
                    //file dont exist
                }

                using (xlPackage = new ExcelPackage(newFile, template))
                {
                    worksheet = xlPackage.Workbook.Worksheets["Earnings"];

                    if (worksheet != null)
                    {
                        row = startRow;

                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                            {
                                if (row > startRow)
                                {
                                    worksheet.InsertRow(row);
                                }

                                if (ds.Tables[0].Rows[i]["TickerName"].ToString() != "")
                                {
                                    worksheet.Cell(row, 1).Value = ds.Tables[0].Rows[i]["TickerName"].ToString();
                                }
                                if (ds.Tables[0].Rows[i]["CompanyName"].ToString() != "")
                                {
                                    worksheet.Cell(row, 2).Value = ds.Tables[0].Rows[i]["CompanyName"].ToString();
                                }
                                if (ds.Tables[0].Rows[i]["Quarter"].ToString() != "")
                                {
                                    worksheet.Cell(row, 3).Value = ds.Tables[0].Rows[i]["Quarter"].ToString();
                                }
                                if (ds.Tables[0].Rows[i]["Comments"].ToString() != "")
                                {
                                    worksheet.Cell(row, 7).Value = ds.Tables[0].Rows[i]["Comments"].ToString();
                                }
                                if (ds.Tables[0].Rows[i]["FileNameOnDisk"].ToString() != "")
                                {
                                    worksheet.Cell(row, 8).Value = ds.Tables[0].Rows[i]["FileNameOnDisk"].ToString();
                                }
                                if (ds.Tables[0].Rows[i]["FileNameOnDiskInteractive"].ToString() != "")
                                {
                                    worksheet.Cell(row, 9).Value = ds.Tables[0].Rows[i]["FileNameOnDiskInteractive"].ToString();
                                }
                                if (ds.Tables[0].Rows[i]["IndustryName"].ToString() != "")
                                {
                                    worksheet.Cell(row, 10).Value = ds.Tables[0].Rows[i]["IndustryName"].ToString();
                                }
                                if (ds.Tables[0].Rows[i]["SubSector"].ToString() != "")
                                {
                                    worksheet.Cell(row, 11).Value = ds.Tables[0].Rows[i]["SubSector"].ToString();
                                }

                                if (ds.Tables[0].Rows[i]["PublishedDate"] != DBNull.Value)
                                {
                                    //DateTime dtPublishedDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["PublishedDate"]);
                                    string dtPublishedDate = ds.Tables[0].Rows[i]["PublishedDate"].ToString();
                                    worksheet.Cell(row, 4).StyleID = worksheet.Cell(1, 4).StyleID;
                                    //worksheet.Cell(row, 4).Value = dtPublishedDate.ToShortDateString();
                                    worksheet.Cell(row, 4).Value = dtPublishedDate.ToString();
                                }
                                else
                                {
                                    worksheet.Cell(row, 4).StyleID = worksheet.Cell(1, 4).StyleID;
                                    worksheet.Cell(row, 4).Value = "";
                                }
                                if (ds.Tables[0].Rows[i]["TentativeStartDate"] != DBNull.Value)
                                {
                                    string dtTentativeStartDate = ds.Tables[0].Rows[i]["TentativeStartDate"].ToString();
                                    //DateTime dtTentativeStartDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["TentativeStartDate"]);
                                    worksheet.Cell(row, 5).StyleID = worksheet.Cell(1, 5).StyleID;
                                    //worksheet.Cell(row, 5).Value = dtTentativeStartDate.ToShortDateString();
                                    worksheet.Cell(row, 5).Value = dtTentativeStartDate.ToString();

                                }
                                else
                                {
                                    worksheet.Cell(row, 5).StyleID = worksheet.Cell(1, 5).StyleID;
                                    worksheet.Cell(row, 5).Value = "";
                                }

                                if (ds.Tables[0].Rows[i]["TentativeEndDate"] != DBNull.Value)
                                {
                                    string dtTentativeEndDate = ds.Tables[0].Rows[i]["TentativeEndDate"].ToString();
                                    //DateTime dtTentativeEndDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["TentativeEndDate"]);
                                    worksheet.Cell(row, 6).StyleID = worksheet.Cell(1, 6).StyleID;
                                    //worksheet.Cell(row, 6).Value = dtTentativeEndDate.ToShortDateString();
                                    worksheet.Cell(row, 6).Value = dtTentativeEndDate.ToString();
                                }
                                else
                                {
                                    worksheet.Cell(row, 6).StyleID = worksheet.Cell(1, 6).StyleID;
                                    worksheet.Cell(row, 6).Value = "";
                                }



                                ++row;
                            }
                        }
                    }


                    ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["TickerID"]);
                    ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["SubSectorID"]);
                    ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["Comments1"]);
                    ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["IsDeleted"]);
                    ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["IndustryID"]);
                 

                    for (int iCol = 1; iCol <= ds.Tables[0].Columns.Count; iCol++)
                        {
                            cell = worksheet.Cell(startRow, iCol);
                             
                            for (int iRow = startRow; iRow <= row; iRow++)
                            {
                                worksheet.Cell(iRow, iCol).StyleID = cell.StyleID;
                                
                            }

                         }


                  
                    for (int i = 1; i < ds.Tables[0].Columns.Count + 1; i++)
                    {
                        worksheet.Cell(1, i).Value = ds.Tables[0].Columns[i-1].ColumnName.ToString();
                         
                    }


                      
                    xlPackage.Save();
                    xlPackage.Dispose();
                    AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(this, typeof(string), "Success", "alert('The data has been exported successfully');", true);
                    ViewFile(strNewFileName);                }
            }
        }
    }