C# 将自定义标题添加到 Excel 文件

Adding Custom header to the excel file

提问by MAC

Is it possible to add a custom header to the Excel while exporting a DataSetto Excel in ASP.NET? I have one requirement like this. I can export the DataSetto the Excel successfully. But I can't add the custom header. Please help me if anybody have the solution. Thanks in advance.

DataSet在 ASP.NET中将 a 导出到 Excel 时，是否可以向 Excel 添加自定义标题？我有一个这样的要求。我可以DataSet成功导出到 Excel。但我无法添加自定义标题。如果有人有解决方案，请帮助我。提前致谢。

采纳答案by Arun Singh

IF you are using Response.Writeto Export the Excel. You can use the following code with minimal effort and the Headercan be customized as you want, just like HTML headers.

如果您正在使用Response.Write导出 Excel。您可以毫不费力地使用以下代码，并且Header可以根据需要进行自定义，就像 HTML 标头一样。

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls;");                
StringWriter stringWrite = new StringWriter();        
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);        
dgrExport.DataSource = dtExport;        
dgrExport.DataBind();
dgrExport.RenderControl(htmlWrite);
string headerTable = @"<Table><tr><td>Report Header</td></tr><tr><td><img src=""D:\Folder\Report Header.jpg"" \></td></tr></Table>";
Response.Write(headerTable);
Response.Write(stringWrite.ToString());        
Response.End();

Mac, Are you looking for the same?

麦克，你在找同样的吗？

回答by Zaki

As you are using dataset, you can change the value in the first row of the dataset if you are using that to be your column names and then export the dataset to excel:

在使用数据集时，如果您使用该数据集作为列名，则可以更改数据集第一行中的值，然后将数据集导出到 excel：

ds.Tables[0].Rows[0][0] = "New Column Name";  // change the column value

回答by Lukas

I got a little treat for you. This is a full blown Excel handler I wrote after lots of studying of Excel Interop. Look at the line "dataGridView1 = YOUR_DATATABLE_HERE;" I know it says dataGridView, it's DataTable, just FYI. Feed it that and you are golden. Of course you would need to convert a dataset to DataTable, but that's another question. Simply put, you can copy and paste this code, and all you have to change is the YOUR_DATATABLE_HERE variable with an actual DataTable and that thing will do the rest. There are lots of commented out sections. Uncomment them as needed. They should be self explanatory. Oh FYI... if your PageSetup does not work properly, that's a headache. It could be anything from you need to add a printer to some really fancy stuff, however it's host computer dependant, not code dependant. If that ends up crashing you, please comment out that section.

我得到了一点点款待给你。这是我在对 Excel Interop 进行大量研究后编写的一个完整的 Excel 处理程序。查看“dataGridView1 = YOUR_DATATABLE_HERE;”这一行 我知道它说 dataGridView，它是 DataTable，仅供参考。喂它，你就是金子。当然，您需要将数据集转换为 DataTable，但这是另一个问题。简而言之，您可以复制并粘贴此代码，您只需更改带有实际 DataTable 的 YOUR_DATATABLE_HERE 变量，剩下的事情将由它来完成。有很多注释掉的部分。根据需要取消注释。它们应该是不言自明的。哦，仅供参考...如果您的 PageSetup 无法正常工作，那就很头疼了。它可能是您需要将打印机添加到一些非常花哨的东西的任何东西，但是它依赖于主机计算机，而不依赖于代码。

Notice the "#region Column Headers" part of the code. Here you can change the headers. In my code I simply pull them from the table but you can customize them. Let me know if you need help with that portion, but again, it should be self explanatory. I know this is a huge chunk of code, but the nice thing is, it's practically ready to go as-is. All you have to do is throw it in your project, feed it a DataTable, maybe add some resources (i.e. anything that's underlined red, just right click it and choose resolve), and you should be set to go for Excel anything. Good luck to you!

注意代码的“#region Column Headers”部分。您可以在此处更改标题。在我的代码中，我只是从表格中提取它们，但您可以自定义它们。如果您需要有关该部分的帮助，请告诉我，但同样，它应该是不言自明的。我知道这是一大块代码，但好消息是，它几乎可以按原样运行了。你所要做的就是把它扔到你的项目中，给它一个数据表，也许添加一些资源（即任何带有红色下划线的东西，只需右键单击它并选择解决），你应该准备好使用 Excel 任何东西。祝你好运！

#region Excel Interop Object Private Methods
private void ExportToExcel()
{
    #region Initialize Variables
    DataTable dataGridView1 = new DataTable();

    //Load source
    dataGridView1 = YOUR_DATATABLE_HERE;

    //Declare Excel Interop variables
    Microsoft.Office.Interop.Excel.Application xlApp;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    //Initialize variables
    xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    #endregion

    #region Title
    //Add a title
    xlWorkSheet.Cells[1, 1] = "Your title here";

    //Span the title across columns A through H
    Microsoft.Office.Interop.Excel.Range titleRange = xlApp.get_Range(xlWorkSheet.Cells[1, "A"], xlWorkSheet.Cells[1, "F"]);
    titleRange.Merge(Type.Missing);

    //Center the title horizontally then vertically at the above defined range
    titleRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
    titleRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

    //Increase the font-size of the title
    titleRange.Font.Size = 16;

    //Make the title bold
    titleRange.Font.Bold = true;

    //Give the title background color
    titleRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);

    //Set the title row height
    titleRange.RowHeight = 50;
    #endregion

    #region Column Headers
    //Populate headers, assume row[0] contains the title and row[1] contains all the headers
    int iCol = 0;
    foreach (DataColumn c in dataGridView1.Columns)
    {
        iCol++;
        xlWorkSheet.Cells[2, iCol] = dgResults.Columns[iCol - 1].HeaderText;
    }

    //Populate rest of the data. Start at row[2] since row[1] contains title and row[0] contains headers
    int iRow = 2; //We start at row 2
    foreach (DataRow r in dataGridView1.Rows)
    {
        iRow++;
        iCol = 0;
        foreach (DataColumn c in dataGridView1.Columns)
        {
            iCol++;
            xlWorkSheet.Cells[iRow, iCol] = r[c.ColumnName];
        }
    }

    //Select the header row (row 2 aka row[1])
    Microsoft.Office.Interop.Excel.Range headerRange = xlApp.get_Range(xlWorkSheet.Cells[2, "A"], xlWorkSheet.Cells[2, "F"]);

    //Set the header row fonts bold
    headerRange.Font.Bold = true;

    //Center the header row horizontally
    headerRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

    //Put a border around the header row
    headerRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
        Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium,
        Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
        Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);

    //Give the header row background color
    headerRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.MediumPurple);
    #endregion

    #region Page Setup
    //Set page orientation to landscape
    xlWorkSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;

    //Set margins
    xlWorkSheet.PageSetup.TopMargin = 0;
    xlWorkSheet.PageSetup.RightMargin = 0;
    xlWorkSheet.PageSetup.BottomMargin = 30;
    xlWorkSheet.PageSetup.LeftMargin = 0;

    //Set Header and Footer (see code list below)
    //&P - the current page number.
    //&N - the total number of pages.
    //&B - use a bold font*.
    //&I - use an italic font*.
    //&U - use an underline font*.
    //&& - the '&' character.
    //&D - the current date.
    //&T - the current time.
    //&F - workbook name.
    //&A - worksheet name.
    //&"FontName" - use the specified font name*.
    //&N - use the specified font size*.
    //EXAMPLE: xlWorkSheet.PageSetup.RightFooter = "&F"
    xlWorkSheet.PageSetup.RightHeader = "";
    xlWorkSheet.PageSetup.CenterHeader = "";
    xlWorkSheet.PageSetup.LeftHeader = "";
    xlWorkSheet.PageSetup.RightFooter = "";
    xlWorkSheet.PageSetup.CenterFooter = "Page &P of &N";
    xlWorkSheet.PageSetup.LeftFooter = "";

    //Set gridlines
    xlWorkBook.Windows[1].DisplayGridlines = true;
    xlWorkSheet.PageSetup.PrintGridlines = true;
    #endregion

    #region Worksheet Style
    /* 
    //Color every other column but skip top two
    Microsoft.Office.Interop.Excel.Range workSheetMinusHeader = xlApp.get_Range("A1", "F1");
    Microsoft.Office.Interop.Excel.FormatCondition format =
        (Microsoft.Office.Interop.Excel.FormatCondition)workSheetMinusHeader.EntireColumn.FormatConditions.Add(
            Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression,
            Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlEqual,
            "=IF(ROW()<3,,MOD(ROW(),2)=0)");
    format.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhiteSmoke;

    //Put a border around the entire work sheet
    workSheetMinusHeader.EntireColumn.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
        Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium,
        Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
        Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
    */
    #endregion

    #region Specific Width, Height, Wrappings, and Format Types
    //Set the font size and text wrap of columns for the entire worksheet
    string[] strColumns = new string[] { "A", "B", "C", "D", "E", "F" };
    foreach (string s in strColumns)
    {
        ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns[s, Type.Missing]).Font.Size = 12;
        ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns[s, Type.Missing]).WrapText = true;
    }

    //Set Width of individual columns
    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns["A", Type.Missing]).ColumnWidth = 7.00;
    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns["B", Type.Missing]).ColumnWidth = 18.00;
    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns["C", Type.Missing]).ColumnWidth = 18.00;
    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns["D", Type.Missing]).ColumnWidth = 30.00;
    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns["E", Type.Missing]).ColumnWidth = 40.00;
    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns["F", Type.Missing]).ColumnWidth = 15.00;

    //Select everything except title row (first row) and set row height for the selected rows
    //xlWorkSheet.Range["a2", xlWorkSheet.Range["a2"].End[Microsoft.Office.Interop.Excel.XlDirection.xlDown].End[Microsoft.Office.Interop.Excel.XlDirection.xlToRight]].RowHeight = 45;

    //Format date columns
    //string[] dateColumns = new string[] { "N", "O", "P", "Q" };
    string[] dateColumns = new string[] { };
    foreach (string thisColumn in dateColumns)
    {
        Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, thisColumn];
        rg.EntireColumn.NumberFormat = "MM/DD/YY";
    }

    //Format ID column and prevent long numbers from showing up as scientific notation
    //Microsoft.Office.Interop.Excel.Range idRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, "C"];
    //idRange.EntireColumn.NumberFormat = "#";

    //Format Social Security Numbers so that Excel does not drop the leading zeros
    //Microsoft.Office.Interop.Excel.Range idRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, "C"];
    //idRange.EntireColumn.NumberFormat = "000000000";
    #endregion

    #region Save & Quit
    //Save and quit, use SaveCopyAs since SaveAs does not always work
    string fileName = Server.MapPath("~/YourFileNameHere.xls");
    xlApp.DisplayAlerts = false; //Supress overwrite request
    xlWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    //Release objects
    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);

    //Give the user the option to save the copy of the file anywhere they desire
    String FilePath = Server.MapPath("~/YourFileNameHere.xls");
    System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
    response.ClearContent();
    response.Clear();
    response.ContentType = "text/plain";
    response.AddHeader("Content-Disposition", "attachment; filename=YourFileNameHere-" + DateTime.Now.ToShortDateString() + ".xls;");
    response.TransmitFile(FilePath);
    response.Flush();
    response.Close();

    //Delete the temporary file
    DeleteFile(fileName);
    #endregion
}

private void DeleteFile(string fileName)
{
    if (File.Exists(fileName))
    {
        try
        {
            File.Delete(fileName);
        }
        catch (Exception ex)
        {
            //Could not delete the file, wait and try again
            try
            {
                System.GC.Collect();
                System.GC.WaitForPendingFinalizers();
                File.Delete(fileName);
            }
            catch
            {
                //Could not delete the file still
            }
        }
    }
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        Response.Write("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}
#endregion