C# Excel导出数据

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Collections;

namespace ConSystemComponent.DataAccess
{
  public  class ExcelOperator
    {
        /// <summary>    
        ///将DataTable数据导出到Excel文件  
        /// </summary>    
        /// <remarks>   
        /// add com "Microsoft Excel 11.0 Object Library"   
        /// using Excel=Microsoft.Office.Interop.Excel;   
        /// using System.Reflection;   
        /// </remarks>   
        /// <param name="dt">将要导出的DataTable</param>   
        /// <param name="savePath">生成Excel文件的保存路径(末尾不要加\\)</param>
        /// <returns>返回生成Excel文件的完整路径</returns>
        public string DataTableToExcel(DataTable dt, string savePath)
        {
            DateTime now = DateTime.Now;
            string FileName = now.Year.ToString().PadLeft(2) + now.Month.ToString().PadLeft(2, '0') + now.Day.ToString().PadLeft(2, '0') + "-" + now.Hour.ToString().PadLeft(2, '0') + now.Minute.ToString().PadLeft(2, '0') + now.Second.ToString().PadLeft(2, '0');
            //验证strFileName是否为空或值无效    
            if (FileName.Trim() == "")
            { return "保存路径不正确!"; }
            //if (!Directory.Exists(savePath))
            //{
            //    return "保存路径不正确!";
            //}
            FileName = FileName + ".xls";
            //定义表格内数据的行数和列数    
            int rowscount = dt.Rows.Count;
            int colscount = dt.Columns.Count;
            //行数必须大于0    
            if (rowscount <= 0)
            {
                return "没有数据可供保存！";
            }
            //列数必须大于0    
            if (colscount <= 0)
            {
                return "没有数据可供保存！";
            }
            //行数不可以大于65536    
            if (rowscount > 65536)
            {
                return "数据记录数太多(最多不能超过65536条)，不能保存！";
            }
            //列数不可以大于255    
            if (colscount > 255)
            {
                return "数据记录行数太多，不能保存 ";
            }

            //验证以fileName命名的文件是否存在，如果存在删除它    
            FileInfo file = new FileInfo(FileName);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch
                {
                    return "试图删除现有文件" + FileName + "失败！";
                }
            }

            Excel.Application objExcel = null;
            Excel.Workbook objWorkbook = null;
            Excel.Worksheet objsheet = null;
            try
            {
                //申明对象    
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
                //设置EXCEL不可见    
                objExcel.Visible = false;

                //向Excel中写入表格的表头    
                int displayColumnsCount = 1;
                for (int i = 0; i <= dt.Columns.Count - 1; i++)
                {
                    //设置单元格格式
                    objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[1, displayColumnsCount]).Font.Bold = true;
                    objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[1, displayColumnsCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    
                    objExcel.Cells[1, displayColumnsCount] = Arr[i];
                    displayColumnsCount++;
                }

                //向Excel中逐行逐列写入表格中的数据    
                for (int row = 0; row <= dt.Rows.Count - 1; row++)
                {
                    displayColumnsCount = 1;
                    for (int col = 0; col < colscount; col++)
                    {
                        objExcel.Cells[row + 2, displayColumnsCount] = dt.Rows[row][col].ToString().Trim();
                        if (dt.Columns[col].ColumnName == "Attribute11" || dt.Columns[col].ColumnName == "Attribute12")
                        {
                            if (dt.Rows[row][col].ToString().Trim() == "1")
                            {
                                objExcel.Cells[row + 2, displayColumnsCount] = "发送";
                            }
                            else
                            {
                                objExcel.Cells[row + 2, displayColumnsCount] = "不发送";
                            }
                        }
                        displayColumnsCount++;
                    }
                }

                //设置单元格格式
                Excel.Range excelRange = objsheet.get_Range(objsheet.Cells[1, 1], objsheet.Cells[rowscount+1, colscount]);
                excelRange.Borders.LineStyle = 1;
                //保存文件    
                //objWorkbook.SaveAs(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                //        Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                //        Missing.Value, Missing.Value);
                objExcel.Save(FileName);
            }
            catch (Exception error)
            {
                return "生成Excel文件出错：" + error.Message;
            }
            finally
            {
                //关闭Excel应用    
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();
                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
            return FileName;
        }

        /// <summary>
        /// 获取表头信息
        /// </summary>
        public ArrayList Arr
        {
            get
            {
                ArrayList arr=new ArrayList();
                arr.Add("公司");
                arr.Add("name");
                arr.Add("age");
                arr.Add("地址");
                arr.Add("公司电话");
                arr.Add("手机号码");
                arr.Add("邮箱地址A");
                arr.Add("邮箱地址B");
                arr.Add("状态1");
                arr.Add("状态2");
                arr.Add("编号");
                return arr;
            }
        }
    }
}

　　

转载于:https://www.cnblogs.com/huangtu/archive/2013/05/27/3102034.html