将DataTable或二维数组导入到Excel

 最近一个项目牵扯到很多导出到Excel的功能，所以自己写了一个简单的类。

using System;
using System.Reflection;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.IO;


namespace Import
...{
    /**//// <summary>
    /// 说    明：Excel输出打印模块
    ///              
    ///    作    者：binheng.zhao
    ///    创建日期：2007－6－24
    /// </summary>
    public class ToExcel:IDisposable
    ...{
        构造函数#region 构造函数
        /**//// <summary>
        /// 新建工作薄
        /// </summary>
        public ToExcel()
        ...{
            xlApp = new Excel.ApplicationClass();
            xlApp.Visible = false;
            xlBooks = xlApp.Workbooks;
            xlBook = (Excel.Workbook)xlBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            xlSheets = (Excel.Sheets)xlBook.Worksheets;
            xlSheet = (Excel.Worksheet)xlSheets.get_Item(1);
        }

        /**//// <summary>
        /// 打开指定的Excel文件
        /// </summary>
        /// <param name="fileFullName">全路径文件名</param>
        public ToExcel(String fileFullName)
        ...{
            if (!File.Exists(fileFullName))
            ...{
                throw new Exception("指定的文件不存在!");
            }
            xlApp = new Excel.ApplicationClass();
            xlApp.Visible = false;
            xlBooks = xlApp.Workbooks;
            xlBook = xlBooks.Open(fileFullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            xlSheets = (Excel.Sheets)xlBook.Worksheets;
            xlSheet = (Excel.Worksheet)xlSheets.get_Item(1);

        }

        #endregion

        私有成员#region 私有成员

        private readonly Object missing = Missing.Value;
        private Excel.Application xlApp;
        private Excel.Workbooks xlBooks;
        private Excel.Workbook xlBook;
        private Excel.Sheets xlSheets;
        private Excel.Worksheet xlSheet;
        private Excel.Range xlRange;

        private enum DataType ...{ Numeric, String, DateTime, Guid, Object, Byte };
        private String fullFileName;
        private Boolean disposed = false;
        #endregion

        属性#region 属性
        public String FullFileName
        ...{
            get ...{ return fullFileName; }
            set ...{ fullFileName = value; }
        }


        #endregion

        数据导出方法#region 数据导出方法


        /**//// <summary>
        /// 将DataTable数据写入Excel文件
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="offsetTop">偏离上边offsetTop个单元格开始填充数据</param>
        /// <param name="offsetLeft">偏离左边offsetLeft个单元格开始填充数据</param>
        public void DataTableToExcel(DataTable table, Int32 offsetTop, Int32 offsetLeft)
        ...{
            try
            ...{
                Int32 rowCount = table.Rows.Count;
                Int32 colCount = table.Columns.Count;
                //填充列头
                String[,] colName = new String[1, table.Columns.Count];
                for (Int32 colIndex = 0; colIndex < table.Columns.Count; colIndex++)
                ...{
                    colName[0, colIndex] = table.Columns[colIndex].Caption;
                }

                xlRange = (Excel.Range)xlSheet.Cells[offsetTop, offsetLeft];
                xlRange = xlRange.get_Resize(1, table.Columns.Count);
                xlRange.NumberFormatLocal = "@";
                xlRange.Value2 = colName;
                //填充数据
                String[,] arr = new String[rowCount, colCount];
                for (Int32 rowIndex = 0; rowIndex < rowCount; rowIndex++)
                ...{
                    for (Int32 colIndex = 0; colIndex < colCount; colIndex++)
                    ...{
                        arr[rowIndex, colIndex] = table.Rows[rowIndex][colIndex].ToString();
                    }
                }

                foreach (DataColumn column in table.Columns)
                ...{
                    Object oFormat = GetRangeFormat(column);
                    xlRange = (Excel.Range)xlSheet.Cells[offsetTop + 1, column.Ordinal + offsetLeft];
                    xlRange = xlRange.get_Resize(rowCount, 1);
                    xlRange.NumberFormatLocal = oFormat;
                }

                xlRange = (Excel.Range)xlSheet.Cells[offsetTop + 1, offsetLeft];
                xlRange = xlRange.get_Resize(rowCount, colCount);
                xlRange.Value2 = arr;
            }
            catch (Exception e)
            ...{
                Dispose(false);
                throw e;
            }
        }


        /**//// <summary>
        /// 将DataTable数据写入Excel文件（自动分页）
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="rows">每页写入记录行数</param>
        /// <param name="offsetTop">偏离上边offsetTop个单元格开始填充数据</param>
        /// <param name="offsetLeft">偏离左边offsetLeft个单元格开始填充数据</param>
        public void DataTableToExcel(DataTable table, Int32 rows,Int32 offsetLeft,Int32 offsetTop)
        ...{
            try
            ...{
                Int32 rowCount = table.Rows.Count;  //DataTable总行数
                Int32 colCount = table.Columns.Count;   //DataTable总列数
                Int32 sheetCount = GetSheetCount(rowCount, rows);   //WorkSheet数量

                //增加工作表
                for (Int32 sheetIndex = 1; sheetIndex < sheetCount; sheetIndex++)
                ...{
                    //excelSheet=(Excel.Worksheet)excelSheets.get_Item(sheetIndex);
                    //excelSheet.Copy(missing, excelSheets[sheetIndex]);
                    xlSheet = (Excel.Worksheet)xlSheets.Add(missing, xlSheets[sheetIndex], 1, Excel.XlSheetType.xlWorksheet);
                }

                //初始化列头数据
                String[,] colName = new String[1, table.Columns.Count];
                for (Int32 colIndex = 0; colIndex < table.Columns.Count; colIndex++)
                ...{
                    colName[0, colIndex] = table.Columns[colIndex].Caption;
                }

                //向各工作表填充数据
                for (Int32 sheetIndex = 1; sheetIndex <= xlSheets.Count; sheetIndex++)
                ...{
                    Int32 startRow = (sheetIndex - 1) * rows;   //数据起始行索引
                    Int32 endRow = sheetIndex * rows;   //数据结束行索引

                    //当前工作表
                    xlSheet = (Excel.Worksheet)xlSheets.get_Item(sheetIndex);
                    
                    //填充列头数据
                    xlRange = (Excel.Range)xlSheet.Cells[offsetTop, offsetLeft];
                    xlRange = xlRange.get_Resize(1, table.Columns.Count);
                    xlRange.NumberFormatLocal = "@";
                    xlRange.Value2 = colName;

                    Int32 pageRowCount = endRow - startRow; //当前工作表记录数

                    String[,] arr = new String[pageRowCount, colCount];
                    for (Int32 rowIndex = 0; rowIndex < pageRowCount; rowIndex++)
                    ...{
                        for (Int32 colIndex = 0; colIndex < colCount; colIndex++)
                        ...{
                            arr[rowIndex, colIndex] = table.Rows[startRow + rowIndex][colIndex].ToString();
                        }
                    }

                    foreach (DataColumn column in table.Columns)
                    ...{
                        Object oFormat = GetRangeFormat(column);
                        xlRange = (Excel.Range)xlSheet.Cells[offsetTop + 1, column.Ordinal + offsetLeft];
                        xlRange = xlRange.get_Resize(rowCount, 1);
                        xlRange.NumberFormatLocal = oFormat;
                    }

                    xlRange = (Excel.Range)xlSheet.Cells[offsetLeft + 1, offsetTop];
                    xlRange = xlRange.get_Resize(pageRowCount, colCount);
                    xlRange.Value2 = arr;
                }
            }
            catch (Exception e)
            ...{
                Dispose(false);
                throw e;
            }
        }

        /**//// <summary>
        /// 将二维数组写入Excel
        /// </summary>
        /// <param name="arr">二维数组</param>
        /// <param name="offsetTop">偏离上边offsetTop个单元格开始填充数据</param>
        /// <param name="offsetLeft">偏离左边offsetLeft个单元格开始填充数据</param>
        public void ArrayToExcel(Object[,] arr, Int32 offsetTop, Int32 offsetLeft)
        ...{
            try
            ...{
                Int32 rowCount = arr.GetLength(0);
                Int32 colCount = arr.GetLength(1);

                String[,] arrValue = new String[rowCount, colCount];
                for (Int32 rowIndex = 0; rowIndex < arrValue.GetLength(0); rowIndex++)
                ...{
                    for (Int32 colIndex = 0; colIndex < arrValue.GetLength(1); colIndex++)
                    ...{
                        arrValue[rowIndex, colIndex] = arr[rowIndex, colIndex].ToString();
                    }
                }
                xlRange = (Excel.Range)xlSheet.Cells[offsetTop, offsetLeft];
                //excelRange = (Excel.Range)excelSheet.Cells[offsetTop, offsetLeft];
                xlRange = xlRange.get_Resize(rowCount, colCount);
                xlRange.NumberFormatLocal = "@";
                xlRange.Value2 = arrValue;
            }
            catch (Exception e)
            ...{
                Dispose(false);
                throw e;
            }
        }

        /**//// <summary>
        /// 将二维数组写入Excel（自动分页）
        /// </summary>
        /// <param name="arr">二维数组</param>
        /// <param name="rows">每页写入记录行数</param>
        /// <param name="offsetTop">偏离上边offsetTop个单元格开始填充数据</param>
        /// <param name="offsetLeft">偏离左边offsetLeft个单元格开始填充数据</param>
        public void ArrayToExcel(Object[,] arr,Int32 rows, Int32 offsetTop, Int32 offsetLeft)
        ...{
            Int32 rowCount = arr.GetLength(0);  //行数
            Int32 colCount = arr.GetLength(1);  //列数

            Int32 sheetCount = GetSheetCount(rowCount, rows);   //得到所需工作表数量

            //增加工作表
            for (Int32 sheetIndex = 1; sheetIndex < sheetCount; sheetIndex++)
            ...{
                xlSheet = (Excel.Worksheet)xlSheets.Add(missing, xlSheets[sheetIndex], 1, Excel.XlSheetType.xlWorksheet);
            }

            //填充各工作表
            for (Int32 sheetIndex = 1; sheetIndex <= xlSheets.Count; sheetIndex++)
            ...{

                Int32 startRow = (sheetIndex - 1) * rows;   //数据起始行索引
                Int32 endRow = sheetIndex * rows;   //数据结束行索引
                //当前工作表
                xlSheet = (Excel.Worksheet)xlSheets.get_Item(sheetIndex);

                Int32 pageRowCount = endRow - startRow; //当前工作表记录数

                String[,] arrValue = new String[pageRowCount, colCount];
                for (Int32 rowIndex = 0; rowIndex < arrValue.GetLength(0); rowIndex++)
                ...{
                    for (Int32 colIndex = 0; colIndex < arrValue.GetLength(1); colIndex++)
                    ...{
                        arrValue[rowIndex, colIndex] = arr[startRow + rowIndex,colIndex].ToString();
                    }
                }
                xlRange = (Excel.Range)xlSheet.Cells[offsetTop, offsetLeft];
                xlRange = xlRange.get_Resize(arrValue.GetLength(0), arrValue.GetLength(1));
                
                //设置工作表数据格式
                xlRange.NumberFormatLocal = "@";

                xlRange.Value2 = arrValue;
            }
        }

        #endregion

        public void Save(String fullFilePath) 
        ...{
            try
            ...{
                xlBook.SaveAs(fullFilePath, missing, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, true);
            }
            catch (Exception e)
            ...{   
                throw e;
            }
            finally
            ...{
                Dispose(false);
            }
        }

        public void Dispose()
        ...{
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        私有方法#region 私有方法

        /**//// <summary>
        /// 系统数据类别
        /// </summary>
        /// <param name="column">DataColumn</param>
        /// <returns>DataType枚举类型</returns>
        private DataType ColumnDataType(DataColumn column)
        ...{
            DataType columnType = DataType.String;
            switch (column.DataType.Name)
            ...{
                case "Double":
                case "Single":
                case "Decimal":
                case "Int16":
                case "Int32":
                case "Int64":
                case "UInt16":
                case "UInt32":
                case "UInt64":
                    columnType = DataType.Numeric;
                    break;
                case "Byte[]":
                    columnType = DataType.Byte;
                    break;
                case "String":
                    columnType = DataType.String;
                    break;
                case "GUID":
                    columnType = DataType.Guid;
                    break;
                case "DateTime":
                    columnType = DataType.DateTime;
                    break;
                case "Object":
                    columnType = DataType.Object;
                    break;
                default:
                    columnType = DataType.String;
                    break;
            }
            return columnType;
        }


        /**//// <summary>
        /// 得到Excel中显示格式
        /// </summary>
        /// <param name="column">DataColumn</param>
        /// <returns>Object 显示格式</returns>
        private Object GetRangeFormat(DataColumn column)
        ...{
            Object oFormat = missing;
            switch (ColumnDataType(column))
            ...{
                case DataType.Numeric:
                    oFormat = missing;
                    break;
                case DataType.String:
                    oFormat = "@";
                    break;
                case DataType.DateTime:
                    oFormat= "yyyy－M－dd";
                    break;
                //case DataType.Object:
                //    throw new Exception("不支持的数据类型:Object!");
                case DataType.Guid:
                    oFormat = missing;
                    break;
                //case DataType.Byte:
                //    throw new Exception("不支持的数据类型:Byte[]!");
                default:
                    oFormat = missing;
                    break;
            }
            return oFormat;
        }

        /**//// <summary>
        /// 计算分页填充所需WorkSheet数量
        /// </summary>
        /// <param name="rowCount">记录总行数</param>
        /// <param name="rows">每个工作表填充数据行数</param>
        /// <returns>WorkSheet数量</returns>
        private Int32 GetSheetCount(Int32 rowCount,Int32 rows)
        ...{
            int n = rowCount % rows;        //余数

            if (n == 0)
                return rowCount / rows;
            else
                return rowCount / rows + 1;
        }

        protected virtual void Dispose(Boolean disposing)
        ...{
            if (!this.disposed)
            ...{
                if (disposing)
                ...{
                    //释放托管资源
                }
                //释放非托管资源
                xlBook.Close(false, null, null);
                xlBooks.Close();
                xlApp.Quit();
                if (xlRange != null)
                ...{
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
                    xlRange = null;
                }
                if (xlSheet != null)
                ...{
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
                    xlSheet = null;
                }
                if (xlSheets != null)
                ...{
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets);
                    xlSheets = null;
                }
                if (xlBook != null)
                ...{
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
                    xlBook = null;
                }
                if (xlBooks != null)
                ...{
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks);
                    xlBooks = null;
                }

                if (xlApp != null)
                ...{
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
                GC.Collect();
            }
            disposed = true;
        }

        ~ToExcel()
        ...{
            Dispose(false);
        }

        #endregion
    }
}