|
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; }
Vs.net中集成了office组件,在添加引用中选择"COM组件"--"microsoft Excel 11.0 object library",Vs就会自动生成Excel应用所需的3个.dll文件,这样我们就可以对Excel表进
行写操作了:
///建立新的表格工作区,设定Sheet为Sheet1m_objExcel = new Excel.Application(); ///建立新的Excel应用m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; ///获取应用中的所有数据表m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); ///新建数据表m_objSheets = (Excel.Sheets)m_objBook.Worksheets; ///获取数据表中所有工作表m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); ///选取第一个工作表m_objSheet.Name="原始数据表" ///命名工作表为"原始数据表"
object[] objTitle = {"RVI", "NDVI", "PVI", "DVI", "RDVI", "PRI", "RSNDa", "VARI_green", "VARI_700"};
///定义一个object数组private object m_objOpt = System.Reflection.Missing.Value;
///定义一个Missing值,这样我们用不到的设定值就可以用Missing来代替了.
m_objRange = m_objSheet.get_Range("A1", m_objOpt); ///获得表格区域,区域从A1开始m_objRange = m_objRange.get_Resize(1,objTitle.Length); ///定义表格区域为1行,"objTitle的个数"列m_objRange.set_Value(m_objOpt,objTitle); ///将objTitle写入表格区域中,Excel会自动对齐的呵呵,放心
m_objFont = m_objRange.Font; ///获取区域中的字体设置m_objFont.Bold=true; ///设置字体为粗体
m_objBook.SaveAs(m_strSampleFolder + FileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); ///保存Excel文件,文件名为FileName,地址为m_strSampleFolder,呵呵.其他忽略
m_objBook.Close(false, m_objOpt, m_objOpt); ///关闭数据表
m_objExcel.Quit(); ///关闭Excel应用
我这也是从网上学的,在微软的客户服务区中有比较详细的解说,地址如下:
1.如何使用 Visual C# 2005 或 Visual C# .NET 向 Excel 工作簿传输数据
http://support.microsoft.com/kb/306023/zh-cn
2.如何在 Microsoft Visual C# .NET 中实现 Microsoft Excel 自动化
http://support.microsoft.com/kb/302084/zh-cn
3.如何使用 Visual C# 向 Microsoft Excel 2002 传输 XML 数据
http://support.microsoft.com/kb/307029/zh-cn
4.如何使用 ASP.NET、ADO.NET 和 Visual C# .NET 查询和显示 Excel 数据
http://support.microsoft.com/?scid=kb%3Bzh-cn%3B306572&x=6&y=10 |
