How to make the header font bold while exporting dataset to excel?

c# asp.net export-to-excel htmlwriter

16,728

Solution 1

You need to set the HeaderStyle on the DataGrid to use bold font. That's all.

dataExportExcel.HeaderStyle.Font.Bold=true;

Solution 2

Cursor.Current = Cursors.WaitCursor;
        try
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "Total Expiry Inventories Data ";
            sfd.DefaultExt = "xls";
            sfd.Filter = "xlsx files(*.xlsx)|*.xlsx";
            if (sfd.ShowDialog() != System.Windows.Forms.DialogResult.OK)
            {
                return;
            }

            Excel.Application ExcelApp = new Excel.Application();
            Excel.Workbook workbook = ExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook.Worksheets[1];
            worksheet1.Name = "Expiry Data";
            for (int i = 1; i < GrdViewData.Columns.Count + 1; i++)
            {
                worksheet1.Cells[1, i] = GrdViewData.Columns[i - 1].HeaderText;
                worksheet1.Cells[1, i].Font.Bold = true;


            }
            for (int i = 0; i < GrdViewData.Rows.Count; i++)
            {
                for (int j = 0; j < GrdViewData.Columns.Count; j++)
                {
                    worksheet1.Cells[i + 2, j + 1] = GrdViewData.Rows[i].Cells[j].Value.ToString();
                }
            }
            worksheet1.Rows.Font.Size = 12;
            //  Excel.Range range_Consolidated = worksheet1.Rows.get_Range("a1", "d1");
            // range_Consolidated.Font.Bold = true;

            // range_Consolidated.Font.Italic = true;

            string ExcelFileName = sfd.FileName;
            workbook.SaveAs(ExcelFileName);
            workbook.Close(false, ExcelFileName, Missing.Value);
            ExcelApp.Quit();

            ExcelApp = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();
            MessageBox.Show("File Saved! you can open it from\n  '" + sfd.FileName + "'", "EXPORT", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
        }

16,728

Author by

Rooney

Updated on June 04, 2022

Comments

Rooney almost 2 years

Here i am exporting the datatables in a dataset to excel.How to make the header font of the datatable alone look bold.Here is my code

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + "");
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
DataGrid dataExportExcel = new DataGrid();
foreach (DataTable table in dtInputParameters.Tables)
{
   dataExportExcel.DataSource =  table;
   dataExportExcel.DataBind();
   dataExportExcel.RenderControl(htmlWrite);
   htmlWrite.WriteLine("<br/>");
   // htmlWrite.AddStyleAttribute(System.Web.UI.HtmlTextWriterStyle.FontWeight, "bold");
}
StringBuilder sbResponseString = new StringBuilder();
sbResponseString.Append("<html xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40\"> <head><meta http-equiv=\"Content-Type\" content=\"text/html;charset=windows-1252\"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" + worksheetName + "</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>");
sbResponseString.Append(stringWriter + "<table width='800' height='100' align='center' style='text-align:center'");
sbResponseString.Append("</table></body></html>");
HttpContext.Current.Response.Write(sbResponseString.ToString());
HttpContext.Current.Response.End();

Any suggestion?