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";
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)
{
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
{
}
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)
{
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.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();
worksheet.Cell(row, 5).StyleID = worksheet.Cell(1, 5).StyleID;
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();
worksheet.Cell(row, 6).StyleID = worksheet.Cell(1, 6).StyleID;
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); }
}
}
}