C#读写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;

         }

     }

}

C#读写EXCEL方法二

通过数据库方式

   OleDbDataAdapter    ada    =    new    OleDbDataAdapter("SELECT    *    FROM    [Sheet1$]",    "Provider=Microsoft.Jet.OLEDB.4.0;Data    Source="    +    openFileDialog1.FileName    +    ";Extended    Properties=Excel    8.0;");   

   DataTable    dt    =    new    DataTable();   

   try   

   {   

   ada.Fill(dt);   

   }   

   catch(Exception    ex)   

   {   

   MessageBox.Show(ex.ToString());   

   }   

   dataGrid1.DataSource    =    dt;   

   }