C# Excel 操作个人小结

把整个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.Application appExcel = 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后的sheet
 Worksheet newSheet = (Worksheet)wbkTemplate.ActiveSheet;

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

行的copy（带格式）

//模板行的取得

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

//插入一个copy行

 //1 Select Template Row
TemplateRow.Select();
//2 Insert Blank Row
TemplateRow.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);
//3 Select NewRow
Range 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);