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

补充：软件开发 , C# ,