把整个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);