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.Ent