Excel2010和以前的2003、2000、97都使用正常,但Excel2007好像问题多多,没时间研究,要求开发环境安装Excel2010吧。如果是布署在服务器上使用,则无妨;如果是发布给一般用户使用,那么最好要求用户安装2010,否则,要在代码中加入很多兼容代码,连Quit都会报错。
在Visual Studio .NET(我用的是VS2010)中建立一个C# WinForm工程,添加Microsoft Excel Object Library引用:右键单击Project或选择“项目”菜单 , 选“添加引用”,在COM 标签项,选中 locate Microsoft Excel Object Library 14(Excel2010是14,2007是12)。点确定按钮完成添加引用。 不知为何这里不像VB6一样用复选框来表示引用与否,这里只能添加,如果要去掉,则要到项目属性中去了。
添加两个按钮btnExport和btnRead,双击进代码。这里有个插曲,我画了一个按钮,再复制一个,双击第二个进去,竟然仍是响应第一个按钮。仔细看看也没有形成按钮数组。什么顺事呢?最终发现要在设计器(Form1.Desigher.cs)中修改这一句:
this.btnRead.Click += new System.EventHandler(this.btnRead_Click);
否则会仍然关联到btnExport_Click,——这应该是它的BUG吧?
代码最前端:
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
我还另外加了这两句,或许不需要:
using Office = Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
在类中声明两个全局的变量——注意别写到类外面去了,否则会报错:
Excel.Application objApp;
Excel._Workbook objBook;
其余的代码我就直接贴了:
namespace ExcelApp//(别怕,ExcelApp是我的工程名)
{
public partial class frmMain : Form
{
Excel.Application objApp;
Excel._Workbook objBook;
public frmMain()
{
InitializeComponent();
}
private void btnExport_Click(object sender, EventArgs e)
{
Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;
try
{
// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Get the range where the starting cell has the address
//m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range = objSheet.get_Range("A1", Missing.Value);
range = range.get_Resize(5, 5);
if (false) //true) 为了测试,可以换为true或false以执行不同代码
{
//Create an array.
double[,] saRet = new double[5, 5];
//Fill the array.
for (long iRow = 0; iRow < 5; iRow++)
{
for (long iCol = 0; iCol < 5; iCol++)
{
//Put a counter in the cell.
saRet[iRow, iCol] = iRow * iCol;
}
}
//Set the range value to the array.
range.set_Value(Missing.Value, saRet);
}
else
{
//Create an array.
string[,] saRet = new string[5, 5];
//Fill the array.
for (long iRow = 0; iRow < 5; iRow++)
{
for (long iCol = 0; iCol < 5; iCol++)
{
//Put the row and column address in the cell.
saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString();
}
}
//Set the range value to the array.
range.set_Value(Missing.Value, saRet);
}
//Return control of Excel to the user.
objApp.Visible = true;
objApp.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
private void btnRead_Click(object sender, EventArgs e)
{
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;
try
{
try
{
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Can't find the Excel workbook. Try clicking Button1 " +
"to create an Excel workbook with data before running Button2.";
MessageBox.Show(errorMessage, "Missing Workbook?");
//You can't automate Excel if you can't find the data you created, so
//leave the subroutine.
return;
}
//Get a range of data.
range = objSheet.get_Range("A1", "E5");
//Retrieve the data from the range.
Object[,] saRet;
saRet = (System.Object[,])range.get_Value(Missing.Value);
//Determine the dimensions of the array.
long iRows;
long iCols;
iRows = saRet.GetUpperBound(0);
iCols = saRet.GetUpperBound(1);
//Build a string that contains the data of the array.
String valueString;
valueString = "Array Data/n";
for (long rowCounter = 1; rowCounter <= iRows; rowCounter++)
{
for (long colCounter = 1; colCounter <= iCols; colCounter++)
{
//Write the next value into the string.
valueString = String.Concat(valueString,
saRet[rowCounter, colCounter].ToString() + ", ");
}
//Write in a new line.
valueString = String.Concat(valueString, "/n");
}
//Report the value of the array.
MessageBox.Show(valueString, "Array Values");
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
}
下面几句是显示指定文件z.xls中的A1单元格内容:
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objApp.Workbooks.Open("R:\\z.xls");
//objBook = objBooks.Add(Missing.Value); //这句是新建一个文件的意思
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
range = objSheet.get_Range("A1", Missing.Value);
String s=(String)range.Value2.ToString();
MessageBox.Show(s, "!");
示例代码陆续添加中。