C # personal summary of Excel operations

Reading the entire Excel file as a database

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 );

 

Reading the entire Excel file as a Com Object

Open 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 );

Close Excel

WbkExcel. Close (false, fileName, false );

Save as Excel

WbkExcel. SaveCopyAs (savePath. FileName );

Get sheet object

Worksheet wstExcel = wbkExcel. Sheets [sheetName];

Obtain a Cell

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

Get a row

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;
}

Retrieve a column

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;
}

 

Copy of different sheets of the same workbook

// Template File

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

// Copy the copy sheet to your end

WsData. Copy (wsData, Missing. Value );

// Obtain the copy sheet.
Worksheet newSheet = (Worksheet) wbkTemplate. ActiveSheet;

// Rename sheet
NewSheet. Name = InstanceSheetNames [index];

 

Copy of rows (with format)

// Obtain the template row

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

// Insert a copy row

// 1 Select Template Row
TemplateRow. Select ();
// 2 Insert Blank Row
TemplateRow. Ent