Question

c# 如何打开excel中的某个sheet

C# 如何打开excel中的某个sheet

Answer 1

第一步，打开Excel文档：

object filename="";

object MissingValue=Type.Missing;

string strKeyWord=""; //指定要搜索的文本，如果有多个，则声明string[]

Excel.Application ep=new Excel.ApplicationClass();

Excel.Workbook ew=ep.Workbooks.Open(filename.ToString(),MissingValue,

MissingValue,MissingValue,MissingValue,

MissingValue,MissingValue,MissingValue,

MissingValue,MissingValue,MissingValue,

MissingValue,MissingValue,MissingValue,

MissingValue);

然后准备遍历Excel工作表：

Excel.Worksheet ews;

int iEWSCnt=ew.Worksheets.Count;

int i=0,j=0;

Excel.Range oRange;

object oText=strKeyWord.Trim().ToUpper();

for(i=1;i<=iEWSCnt;i++)

{

ews=null;

ews=(Excel.Worksheet)ew.Worksheets[i];

oRange=null;

(Excel.Range)oRange=((Excel.Range)ews.UsedRange).Find(

oText,MissingValue,MissingValue,

MissingValue,MissingValue,Excel.XlSearchDirection.xlNext,

MissingValue,MissingValue,MissingValue);

if (oRange!=null && oRange.Cells.Rows.Count>=1 && oRange.Cells.Columns.Count>=1)

{

MessageBox.Show("文档中包含指定的关键字！","搜索结果",MessageBoxButtons.OK);

break;

}

}

这里要说两个值得注意的地方。一个是遍历工作表的索引，不是从0开始，而是从1开始
第一种方法实现了，再看看第二种方法。这种方法除了要遍历工作表，还要对工作表使用区域的行和列进行遍历。其它一样，只对遍历说明，代码如下：

bool blFlag=false;

int iRowCnt=0,iColCnt=0,iBgnRow,iBgnCol;

for(m=1;m<=iEWSCnt;m++)

{

ews=(Excel.Worksheet)ew.Worksheets[m];

iRowCnt=0+ews.UsedRange.Cells.Rows.Count;

iColCnt=0+ews.UsedRange.Cells.Columns.Count;

iBgnRow=(ews.UsedRange.Cells.Row>1)?

ews.UsedRange.Cells.Row-1:ews.UsedRange.Cells.Row;

iBgnCol=(ews.UsedRange.Cells.Column>1)?

ews.UsedRange.Cells.Column-1:ews.UsedRange.Cells.Column;

for(i=iBgnRow;i
{

for(j=iBgnCol;j
{

strText=((Excel.Range)ews.UsedRange.Cells[i,j]).Text.ToString();

if (strText.ToUpper().IndexOf(strKeyWord.ToUpper())>=0)

{

MessageBox.Show("文档中包含指定的关键字！","搜索结果",MessageBoxButtons.OK);

}

}

}

}

Answer 2

使用ActiveX控制Office比较方便的

private Missing miss=Missing.Value; //忽略的参数OLENULL
public static Missing MissValue=Missing.Value;
private Excel.Application m_objExcel;//Excel应用程序实例
private Excel.Workbooks m_objBooks;//工作表集合
private Excel.Workbook m_objBook;//当前操作的工作表
private Excel.Worksheet sheet;//当前操作的表格

public void OpenExcelFile(string filename)
{
m_objExcel = new Excel.Application();
UserControl(false);

m_objExcel.Workbooks.Open(
filename,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss);

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

m_objBook = m_objExcel.ActiveWorkbook;
sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
public void CreateExceFile()
{
m_objExcel = new Excel.Application();
UserControl(false);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
public void SaveAs(string FileName)
{
m_objBook.SaveAs(FileName, miss, miss, miss, miss,
miss, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges,
miss,miss, miss, miss);
//m_objBook.Close(false, miss, miss);
}
public void setValue(int x,int y,string text,System.Drawing.Font font,System.Drawing.Color color)
{
this.setValue(x,y,text);
Excel.Range range=sheet.get_Range(this.GetAix(x,y),miss);
range.Font.Size=font.Size;
range.Font.Bold=font.Bold;
range.Font.Color=color;
range.Font.Name=font.Name;
range.Font.Italic=font.Italic;
range.Font.Underline=font.Underline;
}
private string AList="ABCDEFGHIJKLMNOPQRSTUVWXYZ";

public string GetAix(int x,int y)
{
char [] AChars=AList.ToCharArray();
if(x>=26){return "";}
string s="";
s=s+AChars[x-1].ToString();
s=s+y.ToString();
return s;
}