C#操作excel的一些常识

range.NumberFormatLocal = "@";     //设置单元格格式为文本   
     
   range = (Range)worksheet.get_Range("A1", "E1");     //获取Excel多个单元格区域：本例做为Excel表头   
     
   range.Merge(0);     //单元格合并动作   
     
   worksheet.Cells[1, 1] = "Excel单元格赋值";     //Excel单元格赋值   
     
   range.Font.Size = 15;     //设置字体大小   
     
   range.Font.Underline=true;     //设置字体是否有下划线   
     
   range.Font.Name="黑体";       设置字体的种类   
     
   range.HorizontalAlignment=XlHAlign.xlHAlignCenter;     //设置字体在单元格内的对其方式   
     
   range.ColumnWidth=15;     //设置单元格的宽度   
     
   range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();     //设置单元格的背景色   
     
   range.Borders.LineStyle=1;     //设置单元格边框的粗细   
     
   range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());     //给单元格加边框   
     
   range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格上边框为无边框   
     
       range.EntireColumn.AutoFit();     //自动调整列宽   
     
   Range.HorizontalAlignment= xlCenter;     // 文本水平居中方式   
     
   Range.VerticalAlignment= xlCenter     //文本垂直居中方式   
     
   Range.WrapText=true;     //文本自动换行   
     
   Range.Interior.ColorIndex=39;     //填充颜色为淡紫色   
     
   Range.Font.Color=clBlue;     //字体颜色   
     
   xlsApp.DisplayAlerts=false;     //保存Excel的时候，不弹出是否保存的窗口直接进行保存
      / <summary>
        /// 常用颜色定义,对就Excel中颜色名
        /// </summary>
        public enum ColorIndex
        {
            无色 = -4142, 自动 = -4105, 黑色 = 1, 褐色 = 53, 橄榄 = 52, 深绿 = 51, 深青 = 49,
            深蓝 = 11, 靛蓝 = 55, 灰色80 = 56, 深红 = 9, 橙色 = 46, 深黄 = 12, 绿色 = 10,
            青色 = 14, 蓝色 = 5, 蓝灰 = 47, 灰色50 = 16, 红色 = 3, 浅橙色 = 45, 酸橙色 = 43,
            海绿 = 50, 水绿色 = 42, 浅蓝 = 41, 紫罗兰 = 13, 灰色40 = 48, 粉红 = 7,
            金色 = 44, 黄色 = 6, 鲜绿 = 4, 青绿 = 8, 天蓝 = 33, 梅红 = 54, 灰色25 = 15,
            玫瑰红 = 38, 茶色 = 40, 浅黄 = 36, 浅绿 = 35, 浅青绿 = 34, 淡蓝 = 37, 淡紫 = 39,
            白色 = 2
        }
      完整例子VS2008：
      using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Runtime.InteropServices; //for comexcelption
using Excel;
   using System.Reflection;
using System.IO;
using Microsoft.Office.Core;
   namespace OutputExcel
{
    class Program
    {
           public static SqlConnection getSqlConnection()
        {
            //第一种 ：SQL 登陆模式
            /*
            String sqlCon = "server=(local);database=Practice;uid=sa;pwd=p9pip";
            return new SqlConnection(sqlCon);
            */
            //第二种：windows 登陆模式
            String sqlCon = "server=(local);Integrated Security = SSPI;database=Practice";
            return new SqlConnection(sqlCon);
        }
           SqlCommand cmd;
           static void Main(string[] args)
        {
            SqlConnection con = Program.getSqlConnection();
            con.Open();
            //cmd = new SqlCommand("select * from dbo.Company11", con);
               String strSql = "select * from dbo.Company11";
               //第一种dataset(假如不知道列数)
            SqlDataAdapter myDateAdapter = new SqlDataAdapter(strSql,con);//创建数据适配器
            DataSet myDataSet = new DataSet();//建立dataset对象
            myDateAdapter.Fill(myDataSet, "Table_Name"); //填充数据
               int RowCount = myDataSet.Tables["Table_Name"].Rows.Count;
            int ColCount = myDataSet.Tables["Table_Name"].Columns.Count;
              //输出到黑屏
            for (int indexRow = 0; indexRow < RowCount; indexRow++)
            {
                for (int indexCol = 0; indexCol < ColCount; indexCol++)
                {
                    String Term = myDataSet.Tables["Table_Name"].Rows[indexRow][indexCol].ToString();
                    Console.Write("{0} ", Term);
                }
                Console.WriteLine();
            }
         
            //第二种 SqlDataReader (知道列数)
            /*
            cmd.Connection = con;
            cmd.CommandText = strSql;
            int RowCount = cmd.ExecuteNonQuery();//影响的行数
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
               // Console.WriteLine(sdr["CompanyID"] + "________" + sdr["CmpanyName"] + "________" + sdr["createdate"]);
                Console.WriteLine("{0} -- {1} -- {2}",sdr["CompanyID"],sdr["CmpanyName"],sdr["createdate"]);
            }
           
            //关闭reader
            if(sdr!=null)
                sdr.Close();
             */
   
               Excel.Application excelApp = new Excel.ApplicationClass();//创建excel对象
            Excel.Workbook workBook = excelApp.Workbooks.Add(true); //新建工作薄
            Excel.Worksheet workSheet = workBook.ActiveSheet as Excel.Worksheet; //新建工作表
               Excel.Range titleRange = workSheet.get_Range(    //设置标题
                                        workSheet.Cells[1, 1], workSheet.Cells[1, ColCount]);     //选取单元格
         
            titleRange.Merge(true); //合并单元格
            titleRange.Value2 = "Company表的信息";   //设置单元格内文本
               titleRange.Font.Name ="黑体"; //设置字体
            titleRange.Font.Size = 20; //设置字体大小    
            titleRange.Font.Bold = true; //加粗显示
            titleRange.Font.Underline = true; //设置字体是否有下划线
               //设置字体在单元格内的对其方式
            titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平居中
            titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;    //垂直居中
               //titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;   //设置边框
            titleRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;        //边框常规粗细
  
            titleRange.Borders.LineStyle = 1;//单元格边框线类型(线型,虚线型)
            titleRange.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
               //指定单元格下边框线粗细,和色彩
            titleRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;
            titleRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).ColorIndex = 3;
  
          
            titleRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); //设置单元格的背景色
          
            titleRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick,
                                    Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); // 给单元格加边框
               //设置表头
            for(int i = 0 ;i < ColCount ;i++)
            {
                Excel.Range headRange = workSheet.Cells[2, i + 1] as Excel.Range; //获取表头单元格
                //headRange.Value2 = dataGridViewX1.Columns[i].HeaderText;          //设置单元格文本 字段名字
                   headRange.Font.Name = "黑体";                                     //设置字体
                headRange.Font.Size = 14;                                         //字体大小
                headRange.Font.Bold = true;                                       //加粗显示
                   headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;    //水平居中
                headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;      //垂直居中
                   //headRange.ColumnWidth = dataGridViewX1.Columns[i].Width / 8;      //设置列宽
                headRange.ColumnWidth = 15; //设置单元格的宽度
                headRange.EntireColumn.AutoFit(); //自动调整列宽
                   headRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;     //设置边框
                headRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;         //边框常规粗细
            }
               //excelApp.ActiveWindow.FreezePanes = true;//冻结字段滚动条失效
               //填充数据
            for (int i = 0; i < RowCount; i++)
            {
                for (int j = 0; j < ColCount; j++)
                {
                    Excel.Range contentRange = workSheet.Cells[i + 3, j + 1] as Excel.Range; //获取单元格
                    //contentRange.Value2 = dataGridViewX1[j, i].Value;                        //设置单元格文本
                    contentRange.Value2 = myDataSet.Tables["Table_Name"].Rows[i][j].ToString(); //设置单元格文本
                    contentRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;         //设置边框
                    contentRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;             //边框常规粗细
                    contentRange.WrapText = true;                                            //自动换行
                  
                    contentRange.Interior.ColorIndex = 39; //填充颜色为淡紫色
                }
               }
               设置每列格式
            Excel.Range CompanyID = workSheet.get_Range(workSheet.Cells[3, 1], workSheet.Cells[RowCount + 3, 1]);
            CompanyID.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;   //对其方式
            CompanyID.NumberFormatLocal = "0";                             //格式化文本
               Excel.Range CmpanyName = workSheet.get_Range(workSheet.Cells[3, 2], workSheet.Cells[RowCount + 3, 3]);
            CmpanyName.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;   //对其方式
            CmpanyName.NumberFormatLocal = "0";
               Excel.Range createdate = workSheet.get_Range(workSheet.Cells[3, 3], workSheet.Cells[RowCount + 3, 3]);
            createdate.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //对其方式
            createdate.NumberFormatLocal = "yyyy-MM-dd";
               excelApp.Visible = true;//设置Excel可见
            //workSheet.PrintPreview(true);//显示打印预览
               //关闭连接
            if ( con!=null && con.State!=System.Data.ConnectionState.Closed )
               con.Close();
           //excelApp.Quit();//关闭excel对象
           //excelApp = null;
              Console.Read();
           }
    }
}