C#操作EXCEL类(最新版)
最近经常要用到操作EXCEL,上网查了一些方法.
网上讨论的大多是在一个类里封装的,使用起来很不方便,如设置一个单元格的值就写如下方法
public void setValue(Excel.Worksheet sheet,int row,int column,string text)
{
sheet.Cells[row,column]=text;
}
这样用起来不直观,我重新写了一个,请大家看一下有什么不足之处;
用法(和EXCEL中使用方法一样直接明了):
myWorkbook workbook = new myWorkbook();
workbook.createNewWorkbook();
mySheet sheet = workbook.sheets[1];
sheet.activate();
for (int i = 1; i < 150; i++)
{
sheet.cells[i, 1].select();
sheet.cells[i, 1].backColorIndex = 38;
sheet.cells[i, 1].text = "abcde " + i.ToString(); ;
}
类一: 我的Workbook 类
class myWorkbook
{
Excel.Application myexcel = null;
Excel.Workbook myworkbook = null;
public mySheet sheets = null;
public myWorkbook()
{
myexcel = new Application();
myexcel.Visible = true;
}
public void createNewWorkbook()
{
myworkbook = myexcel.Workbooks.Add(true);
sheets = new mySheet(myworkbook);
}
public void open(string excelFile)
{
myworkbook = myexcel.Workbooks.Open(excelFile,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
sheets = new mySheet(myworkbook);
}
public void add(string sheetName)
{
Excel.Worksheet ws=(Excel.Worksheet)myworkbook.Sheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing);
ws.Name = sheetName;
}
public void del(int sheetIndex)
{
((Excel.Worksheet)myworkbook.Sheets[sheetIndex]).Delete();
}
public void quit()
{
myworkbook.Close(Type.Missing, Type.Missing, Type.Missing);
myexcel.Quit();
myworkbook = null;
myexcel = null;
//GC.Collect();
}
}
类二: 我的Worksheet 类
class mySheet
{
Excel.Workbook myworkbook = null;
Excel.Worksheet myworksheet = null;
public myRange cells = null;
public mySheet(Excel.Workbook _myworkbook)
{
myworkbook = _myworkbook;
}
public mySheet this[int index]
{
get
{
if (index > 0 && index <= myworkbook.Sheets.Count)
{
myworksheet = (Excel.Worksheet)myworkbook.Sheets[index];
cells = new myRange(myworksheet);
return this;
}
else
{
return null;
}
}
}
public void activate()
{
myworksheet.Activate();
}
}
类三: 我的Range 类
class myRange
{
Excel.Worksheet mysheet = null;
Excel.Range myrange = null;
public myRange(Excel.Worksheet _mysheet)
{
mysheet = _mysheet;
}
public myRange this[int row, int column]
{
get
{
if (row > 0 && column > 0)
{
myrange = (Excel.Range)mysheet.Cells[row, column];
return this;
}
else
{
return null;
}
}
}
public void select()
{
myrange.Select();
}
/// <summary>
///取得和设置表格中的数据
/// </summary>
public string text
{
get { return myrange.Text.ToString(); }
set { myrange.Value2 = value; }
}
public int backColorIndex
{
get { return int.Parse(myrange.Interior.ColorIndex.ToString()); }
set{myrange.Interior.ColorIndex=value;}
}
}
