如何使用C#格式化导出的Excel文件 [英] How To format an exported excel file with C#

问题描述

您好，我正在使用Visual C#，并且有一种将DataGridView导出到Excel文件的方法，我的问题是如何为Excel文件赋予格式，使其看起来更好，例如使标题看起来更好粗体，并用蓝色等着色.

我正在使用的方法是这样的:

Hi i''m working with Visual C# and i''ve a method that exports a DataGridView to an Excel File, my question is how can I give format to the Excel file so it looks better, like making the headers look bold, and coloured with blue, etc.

The method that i''m using is this:

            /// <summary>
            /// Exports a passed datagridview to an Excel worksheet.
            /// If captions is true, grid headers will appear in row 1.
            /// Data will start in row 2.
            /// </summary>
            /// <param name="datagridview"></param>
            /// <param name="captions"></param>
public static void Excel(DataGridView datagridview, bool captions = true)
            {
                object objApp_Late;
                object objBook_Late;
                object objBooks_Late;
                object objSheets_Late;
                object objSheet_Late;
                object objRange_Late;
                object[] Parameters;
                string[] headers = new string[datagridview.ColumnCount];
                string[] columns = new string[datagridview.ColumnCount];

                int i = 0;
                int c = 0;
                for (c = 0; c < datagridview.ColumnCount; c++)
                {
                    headers[c] = datagridview.Rows[0].Cells[c].OwningColumn.Name.ToString();
                    i = c + 65;
                    columns[c] = Convert.ToString((char)i);
                }

                try
                {
                    // Get the class type and instantiate Excel.
                    Type objClassType;
                    objClassType = Type.GetTypeFromProgID("Excel.Application");
                    objApp_Late = Activator.CreateInstance(objClassType);
                    //Get the workbooks collection.
                    objBooks_Late = objApp_Late.GetType().InvokeMember("Workbooks",
                    BindingFlags.GetProperty, null, objApp_Late, null);
                    //Add a new workbook.
                    objBook_Late = objBooks_Late.GetType().InvokeMember("Add",
                    BindingFlags.InvokeMethod, null, objBooks_Late, null);
                    //Get the worksheets collection.
                    objSheets_Late = objBook_Late.GetType().InvokeMember("Worksheets",
                    BindingFlags.GetProperty, null, objBook_Late, null);
                    //Get the first worksheet.
                    Parameters = new Object[1];
                    Parameters[0] = 1;
                    objSheet_Late = objSheets_Late.GetType().InvokeMember("Item",
                    BindingFlags.GetProperty, null, objSheets_Late, Parameters);

                    if (captions)
                    {
                        // Create the headers in the first row of the sheet
                        for (c = 0; c < datagridview.ColumnCount; c++)
                        {
                            //Get a range object that contains cell.
                            Parameters = new Object[2];
                            Parameters[0] = columns[c] + "1";
                            Parameters[1] = Missing.Value;
                            objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
                            BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                            //Write Headers in cell.
                            Parameters = new Object[1];
                            Parameters[0] = headers[c];
                            objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
                            null, objRange_Late, Parameters);
                        }
                    }

                    // Now add the data from the grid to the sheet starting in row 2
                    for (i = 0; i < datagridview.RowCount; i++)
                    {
                        for (c = 0; c < datagridview.ColumnCount; c++)
                        {
                            //Get a range object that contains cell.
                            Parameters = new Object[2];
                            Parameters[0] = columns[c] + Convert.ToString(i + 2);
                            Parameters[1] = Missing.Value;
                            objRange_Late = objSheet_Late.GetType().InvokeMember("Range",
                            BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                            //Write Headers in cell.
                            Parameters = new Object[1];
                            Parameters[0] = datagridview.Rows[i].Cells[headers[c]].Value.ToString();
                            objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty,
                            null, objRange_Late, Parameters);
                        }
                    }

                    //Return control of Excel to the user.
                    Parameters = new Object[1];
                    Parameters[0] = true;
                    objApp_Late.GetType().InvokeMember("Visible", BindingFlags.SetProperty,
                    null, objApp_Late, Parameters);
                    objApp_Late.GetType().InvokeMember("UserControl", BindingFlags.SetProperty,
                    null, objApp_Late, Parameters);
                }
                catch (Exception theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    String.Format("{0} {1} {2}", theException.Message, "Line:", theException.Source);

                    MessageBox.Show(errorMessage, "Error");
                }
            }

提前谢谢.
最好的问候.

Thanks in advance.
Best Regards.

推荐答案

似乎您可以使用

It looks like you can control the formatting by using the DisplayFormat[^] property of the Range object.

这篇关于如何使用C#格式化导出的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！