把整个Excel当做数据库读入的方式

string ExcelConnection = "Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties=Excel 8.0";

string ExcelFileConnection = string.Format(ExcelConnection,FileName);

string queryString = string.Format("select * from [{0}$]", sheetName);

OleDbConnection oledbConn = new OleDbConnection(connString);

OleDbDataAdapter oledbAdap = new OleDbDataAdapter(queryString, oledbConn);

DataSet dsResult = new DataSet();

oledbAdap.Fill(dsResult, fileName);

把整个Excel当做Com对象读入的方式

打开Excel

Microsoft.Office.Interop.Excel.ApplicationappExcel = new Microsoft.Office.Interop.Excel.Application();

Workbook wbkExcel = appExcel.Workbooks.Open(fileName , Missing.Value , Missing.Value , 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

wbkExcel .Close(false, fileName, false);

另存为Excel

wbkExcel .SaveCopyAs(savePath.FileName);

取得sheet对象

Worksheet wstExcel = wbkExcel .Sheets[sheetName];

取得一个Cell

public static Range GetOneCell(Worksheet wst,int ColumnIndex, int RowIndex){ Range cell = (Range)wst.Cells[RowIndex, ColumnIndex]; return cell;}

取得一行

public static Range GetOneRowCells(Worksheet wst, int RowIndex){ Range row = wst.get_Range(wst.Cells[RowIndex, 1], wst.Cells[RowIndex, wst.Columns.Count]); return row;}

取得一列

public static Range GetOneColumnCells(Worksheet wst, int ColumnIndex){ Range column = wst.get_Range(wst.Cells[1, ColumnIndex], wst.Cells[wst.Rows.Count, ColumnIndex]); return column;}

同一本workbook的不同sheet的copy

//模板文件

Workbook wbkTemplate = T_EXECLE.GetWorkBook(this.TemplateFullName);

//被copy的sheet拷贝到自己的后面

wsData.Copy(wsData, Missing.Value);

//取得copy后的sheetWorksheet newSheet = (Worksheet)wbkTemplate.ActiveSheet;

//重命sheet名newSheet.Name = InstanceSheetNames[index];

行的copy（带格式）

//模板行的取得

Range TemplateRow = T_EXECLE.GetOneRowCells(classSheet, StartIndex + 1);

//插入一个copy行

//1 Select Template RowTemplateRow.Select();//2 Insert Blank RowTemplateRow.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);//3 Select NewRowRange newRange = TemplateRow.Application.ActiveCell;//4 Paste Data

TemplateRow.Copy(newRange);

Range的copy（带格式）

//要copy的数据

Range dataRange = dataSheet.get_Range(dataSheet.Cells[StartIndex, "B"], InstanceSheet.Cells[EndIndex, "K"]);

//copy到那个sheet的Range内Range copyWhere = TemplateSheet.get_Range(TemplateSheet.Cells[T_StartIndex, "B"], TemplateSheet.Cells[T_StartIndex + I_Count, "R"]);

//copy执行dataRange.Copy(copyWhere);