在C＃中读写Excel文件

利用DotNet 中自带的工具在命令提示符下执行tlbimp excel.exe.这样就不会因为你的Excel是xp或2000的不同要去找不同的*.olb文件，还有一点就是因为在2000以后的版本中没有了excel9.olb这个文件了。

通过执行tlbimp excel.exe后我们会得到excel.dll文件。

只要有了这个Excel.dll，现在我们就能使用Excel的各种操作函数了。

 

在C＃中读写Excel文件

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Reflection;
using System.IO;

namespace ExcelTest
{
    class Program
    {
        static void Main(string[] args)
        {
            创建Application对象
            Excel.Application xlsApp = new Excel.Application();
            if (xlsApp == null)
            {
                return;
            }

            xlsApp.Visible = true;

           
            //得到WorkBook对象, 可以用两种方式
            //之一: 打开已有的文件
            //Excel.Workbook xlsBook = xlsApp.Workbooks.Open(@"E:/Documents and Settings/daniel.chen/Desktop/test.xls",Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //之二：新建一个文件
            Excel.Workbook xlsBook = xlsApp.Workbooks.Add(Missing.Value);

 

            //指定要操作的Sheet，两种方式
            //之一：
            Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsBook.Sheets[1];
            //之二：
            //Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsApp.ActiveSheet;

 

            //指定单元格，读取数据，两种方法
            //之一：
            Excel.Range range1 = xlsSheet.get_Range("C2",Type.Missing);
            Console.WriteLine(range1.Value2);
            //之二：
            Excel.Range range2 = (Excel.Range)xlsSheet.Cells[2, 3];
            Console.WriteLine(range2.Value2);

            //在单元格中写入数据
            Excel.Range range3 = xlsSheet.get_Range("A1",Type.Missing);
            range3.Value2 = "Hello World!";
            range3.Borders.Color = Color.FromArgb(123, 231, 32).ToArgb();
            range3.Font.Color = Color.Red.ToArgb();
            range3.Font.Name = "Arial";
            range3.Font.Size = 9;
            //range3.Orientation = 90;  //vertical
            range3.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
            range3.VerticalAlignment = Excel.Constants.xlCenter;
            range3.Interior.Color = Color.FromArgb(192,192,192).ToArgb();
            range3.Columns.AutoFit();//adjust the column width automatically

            //在某个区域写入数据数组
            int matrixHeight = 20;
            int matrixWidth = 20;
            string[,] martix=new string[matrixHeight,matrixWidth];
            for (int i = 0; i < matrixHeight; i++)
                for (int j = 0; j < matrixWidth; j++)
                {
                    martix[i, j] = String.Format("{0}_{1}", i+1, j+1);
                }
            string startColName=GetColumnNameByIndex(0);
            string endColName=GetColumnNameByIndex(matrixWidth-1);
            //取得某个区域，两种方法
            //之一：
            Excel.Range range4 = xlsSheet.get_Range("A1",Type.Missing);
            range4 = range4.get_Resize(matrixHeight,matrixWidth);
            //之二：
            //Excel.Range range4 = xlsSheet.get_Range(String.Format("{0}{1}", startColName, 1), String.Format("{0}{1}", endColName, martixHeight));
            range4.Value2 = martix;
            range4.Font.Color = Color.Red.ToArgb();
            range4.Font.Name="Arial";
            range4.Font.Size = 9;
            range4.Columns.HorizontalAlignment = Excel.Constants.xlCenter;

            //设置column和row的宽度和颜色
            int columnIndex=3;
            int rowIndex=3;
            string colName = GetColumnNameByIndex(columnIndex);
            xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.ColumnWidth = 20;
            xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Rows.RowHeight = 40;
            xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.Interior.Color = Color.Blue.ToArgb();//单格颜色
            xlsSheet.get_Range(5 + ":" + 7, Type.Missing).Rows.Interior.Color = Color.Yellow.ToArgb();//第5行到第7行的颜色
            //xlsSheet.get_Range("G : G", Type.Missing).Columns.Interior.Color=Color.Pink.ToArgb();//第n列的颜色如何设置？？

            //保存，关闭
            if (File.Exists(@"E:/Documents and Settings/daniel.chen/Desktop/test1.xls"))
            {
                File.Delete(@"E:/Documents and Settings/daniel.chen/Desktop/test1.xls");
            }
            xlsBook.SaveAs(@"E:/Documents and Settings/daniel.chen/Desktop/test1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            xlsBook.Close(false, Type.Missing, Type.Missing);
            xlsApp.Quit();

            GC.Collect();

            Console.ReadKey();
        }

        //将column index转化为字母，至多两位
        public static string GetColumnNameByIndex(int index)
        {
            string[] alphabet = new string[] {"","A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ,"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
            string result = "";
            int temp = index / 26;
            int temp2 = index % 26 + 1;
            if (temp > 0)
            {
                result += alphabet[temp];
            }
            result += alphabet[temp2];
            return result;
        }
    }
}