C#/.NET 4.0新特性生成Excel文档

一时失言乱红尘 2021-11-11 02:46 190阅读 0赞

直接上代码：

using MSExcel = Microsoft.Office.Interop.Excel;
    public class ExcelHelper
    {
        /// <summary>
        /// Creates the excel file by column.
        /// </summary>
        /// <param name="filename">The filename.</param>
        /// <param name="columns">The columns.</param>
        public static void CreateExcelFileByColumn (string filename, IEnumerable<ColumnData> columns)
        {
            createExcelFile (filename, excelApp =>
            {
                //Write data into the workbook by column.
                int columnIndex = 1;
                foreach (var column in columns)
                {
                    //Write the header.
                    excelApp.Cells[1, columnIndex].Value = column.Header;
    
                    //Write the following lines in this column.
                    int rowIndex = 2;
                    foreach (var cell in column.Data)
                    {
                        excelApp.Cells[rowIndex++, columnIndex].Value = cell;
                    }
                    columnIndex++;
                }
            });
        }
    
        /// <summary>
        /// Creates the excel file by row.
        /// </summary>
        /// <param name="filename">The filename.</param>
        /// <param name="rows">The rows.</param>
        public static void CreateExcelFileByRow (string filename, IEnumerable<IEnumerable> rows)
        {
            createExcelFile (filename, excelApp =>
            {
                //Write data into the workbook by row.
                int rowIndex = 1;
                foreach (var row in rows)
                {
                    int columnIndex = 1;
                    foreach (var cell in row)
                    {
                        excelApp.Cells[rowIndex, columnIndex++].Value = cell;
                    }
                    rowIndex++;
                }
            });
        }
    
        /// <summary>
        /// Creates the excel file and perform the specified action.
        /// </summary>
        /// <param name="filename">The filename.</param>
        /// <param name="action">The action.</param>
        private static void createExcelFile (string filename, Action<MSExcel.Application> action)
        {
            //Create the excel application and set it to run in background.
            var excelApp = new MSExcel.Application ();
            excelApp.Visible = false;
    
            //Add a new workbook.
            excelApp.Workbooks.Add ();
    
            //Perform the action.
            action (excelApp);
    
            //Save the workbook then close the file.
            excelApp.ActiveWorkbook.SaveAs (Filename: filename, 
                FileFormat: MSExcel.XlFileFormat.xlWorkbookNormal);
            excelApp.ActiveWorkbook.Close ();
    
            //Exit the excel application.
            excelApp.Quit ();
        }
    }

这个类提供了两个方法：按列数据创建excel文件和按行数据创建excel文件。

注意此时excelApp.Cells\[row,column\]的返回值已经是.NET 4.0中引入的dynamic类型，  
而不再是原来的object类型，所以可以直接在上面调用.Value()方法，由DLR动态解析；  
而在以前，必须首先强转其返回值为Range，写成：

((MSExcel.Range)excelApp.Cells[row, column]).Value2 = value;

此外，由于C\# 4.0中命名参数和可选参数的引入，也使得对COM对象的方法调用变得相当  
简洁，仅看最后的SaveAs方法，以前必须传递一堆Type.Missing对象以表示不传递此参  
数或使用默认值，如：

excelApp.ActiveWorkbook.SaveAs (filename,
        MSExcel.XlFileFormat.xlWorkbookNormal,
        Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, MSExcel.XlSaveAsAccessMode.xlNoChange,
        Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing);

非常头痛，现在只需用命名参数指定要给出的参数，其他的由于是可选参数，都会自动  
给默认值：

excelApp.ActiveWorkbook.SaveAs (Filename: filename,
        FileFormat: MSExcel.XlFileFormat.xlWorkbookNormal);

意思非常清晰。

最后补上那个简单类ColumnData的定义：

/// <summary>
    /// Represents the header and data of a column.
    /// </summary>
    public class ColumnData
    {
        /// <summary>
        /// Gets or sets the header.
        /// </summary>
        /// <value>The header.</value>
        public string Header { get; set; }
    
        /// <summary>
        /// Gets or sets the data.
        /// </summary>
        /// <value>The data.</value>
        public IEnumerable Data { get; set; }
    }

引用Microsoft.Office.Excel 12.0简单测试通过。

转载于:https://www.cnblogs.com/Gildor/archive/2009/12/05/1617389.html