使用Range属性
this . Range[ "A3:F6, B1:C5" ] . Select ( ) ;
使用Cells属性
for ( int icell= 1 ; icell<= 100 ; icell++ )
{
this . Application. Worksheets[ 2 ] . cells[ icell, 1 ] . value = icell;
}
使用快捷记号
#N/A
使用Offset属性
this . Range[ "A1:A3" ] . Offset[ 3 , 3 ] . Select ( ) ;
使用Resize属性
this . Range[ "A1" ] . Resize[ 3 , 3 ] . Select ( ) ;
使用Union属性
this . Application. Union ( this . Range[ "A1:D4" ] , this . Range[ "E5:H8" ] ) . Select ( ) ;
使用UsedRange属性
this . UsedRange. Select ( ) ;
使用CurrentRegion属性
this . Range[ "A5" ] . CurrentRegion. Select ( ) ;
使用Select方法
this . Application. Worksheets[ 3 ] . Activate ( ) ;
this . Application. Worksheets[ 3 ] . Range[ "A1:B10" ] . Select ( ) ;
使用Activate方法
this . Application. Worksheets[ 3 ] . Activate ( ) ;
this . Application. Worksheets[ 3 ] . Range[ "A1:B10" ] . Activate ( ) ;
使用Goto方法
this . Application. Goto ( this . Application. Worksheets[ 3 ] . Range[ "A1:B10" ] , true ) ;
获得指定行,列中的最后一个非空单元格
Excel. Range rng = this . Range[ "A65535" ] . End[ Excel. XlDirection. xlUp ] ;
MessageBox. Show ( "A列中最后一个非空单元格是" + rng. Address[ 0 , 0 ] + ",行号" + rng. Row. ToString ( ) + ",数值" + rng. Text) ;
定位单元格
Excel. Range rng = this . UsedRange. SpecialCells ( Excel. XlCellType. xlCellTypeFormulas) ;
rng. Select ( ) ;
MessageBox. Show ( "工作表中有公式的单元格为:" + rng. Address) ;
查找单元格
Excel. Range rng, Rng;
Rng = this . Range[ "A:A" ] ;
string strFind = textBox1. Text;
if ( strFind. Trim ( ) != string . Empty)
{
rng = Rng. Find ( strFind, Rng. Cells[ Rng. Cells. Count ] , Excel. XlFindLookIn. xlValues, Excel. XlLookAt. xlWhole, Excel. XlSearchOrder. xlByRows, Excel. XlSearchDirection. xlNext, false ) ;
if ( rng != null )
{
this . Application. Goto ( rng, true ) ;
}
else
{
MessageBox. Show ( "没有找到单元格!" ) ;
}
}
查找单元格重复数据
Excel. Range rng, Rng;
string FindAddress = string . Empty;
Rng = this . Range[ "A:A" ] ;
string strFind = textBox1. Text;
if ( strFind. Trim ( ) != string . Empty)
{
rng = Rng. Find ( strFind, Rng. Cells[ Rng. Cells. Count ] , Excel. XlFindLookIn. xlValues, Excel. XlLookAt. xlWhole, Excel. XlSearchOrder. xlByRows, Excel. XlSearchDirection. xlNext, false ) ;
if ( rng != null )
{
FindAddress = rng. Address;
do
{
rng. Interior. ColorIndex= 6 ;
rng= Rng. FindNext ( rng) ;
} while ( rng != null && rng. Address != FindAddress) ;
}
}
使用Like运算符
替换单元格内字符串
this . Range[ "A1:A5" ] . Replace ( "通州" , "南通" ) ;
复制单元格区域
this . Application. DisplayAlerts = false ;
this . Range[ "A1" ] . CurrentRegion. Copy ( this . Application. Worksheets[ 2 ] . Range[ "A1" ] ) ;
this . Application. DisplayAlerts = true ;
复制单元格区域时带列宽大小
this . Range[ "A1" ] . CurrentRegion. Copy ( ) ;
Excel. Range rng = this . Application. Worksheets[ 3 ] . Range[ "A1" ] ;
rng. PasteSpecial ( Excel. XlPasteType. xlPasteColumnWidths) ;
rng. PasteSpecial ( Excel. XlPasteType. xlPasteAll) ;
this . Application. CutCopyMode = Excel. XlCutCopyMode. xlCut;
使用选择性粘贴
this . Range[ "A1" ] . CurrentRegion. Copy ( ) ;
Excel. Range rng = this . Application. Worksheets[ 3 ] . Range[ "A1" ] ;
rng. PasteSpecial ( Excel. XlPasteType. xlPasteValues) ;
this . Application. CutCopyMode = Excel. XlCutCopyMode. xlCut;
直接赋值的方法
Excel. Range rng = this . Application. Worksheets[ 3 ] . Range[ "A1" ] ;
Excel. Range Rng = this . Range[ "A1" ] . CurrentRegion;
rng. Resize[ Rng. Rows. Count , Rng. Columns. Count] . Value = Rng. Value;
单元格自动进入编辑状态
先在”VSTO 设计器生成的代码”内加入
this . SelectionChange + = new Excel. DocEvents_SelectionChangeEventHandler ( 工作表1 _SelectionChange) ;
然后在事件代码中输入
if ( Target. Column == 3 && Target. Count == 1 )
{
if ( Target. Text == string . Empty)
{
this . Application. SendKeys ( "{F2}" ) ;
}
}
禁用单元格拖放功能
if ( this . Application. Intersect ( Target, this . Range[ "A1:A15" ] ) != null )
{
this . Application. CellDragAndDrop = false ;
}
else
{
this . Application. CellDragAndDrop = true ;
}
单元格字体格式设置
Excel. Font rng = this . Range[ "A1" ] . Font;
rng. Name = "宋体" ;
rng. FontStyle = "Bold" ;
rng. Size = 18 ;
rng. ColorIndex = 3 ;
rng. Underline = 2 ;
设置单元格内部格式
Excel. Interior rng = this . Range[ "A1" ] . Interior;
rng. ColorIndex = 3 ;
rng. Pattern = Excel. XlPattern. xlPatternCrissCross;
rng. PatternColorIndex = 6 ;
为单元格区域添加边框
Excel. Borders rng = this . Range[ "B4:G10" ] . Borders;
rng. LineStyle = Excel. XlLineStyle. xlContinuous;
rng. Weight = Excel. XlBorderWeight. xlThin;
rng. ColorIndex = 5 ;
Excel. XlColorIndex col = ( Excel. XlColorIndex) 5 ;
this . Range[ "B4:G10" ] . BorderAround2 ( Excel. XlLineStyle. xlContinuous, Excel. XlBorderWeight. xlMedium, col) ;
为单元格区域应用多种边框格式
Excel. XlColorIndex col = ( Excel. XlColorIndex) 5 ;
Excel. Border rng = this . Range[ "B4:G10" ] . Borders[ Excel. XlBordersIndex. xlInsideHorizontal ] ;
Excel. Border Rng = this . Range[ "B4:G10" ] . Borders[ Excel. XlBordersIndex. xlInsideVertical ] ;
rng. LineStyle = Excel. XlLineStyle. xlDot;
rng. Weight = Excel. XlBorderWeight. xlThin;
rng. ColorIndex = col;
Rng. LineStyle = Excel. XlLineStyle. xlContinuous;
Rng. Weight = Excel. XlBorderWeight. xlThin;
Rng. ColorIndex = col;
this . Range[ "B4:G10" ] . BorderAround2 ( Excel. XlLineStyle. xlContinuous, Excel. XlBorderWeight. xlMedium, col) ;
灵活设计单元格的行高列宽
Excel. Range rng1 = this . Range[ "A1" ] ;
Excel. Range rng2 = this . Range[ "B1" ] ;
rng1. RowHeight = this . Application. CentimetersToPoints ( 2 ) ;
rng1. ColumnWidth = this . Application. CentimetersToPoints ( 1.5 ) ;
rng2. RowHeight = this . Application. CentimetersToPoints ( 1.2 ) ;
rng2. ColumnWidth = this . Application. CentimetersToPoints ( 0.3 ) ;
单元格中建立数据有效性
Excel. Range rng = this . Range[ "A1:A10" ] ;
rng. Validation. Delete ( ) ;
rng. Validation. Add (
Excel. XlDVType. xlValidateList,
Excel. XlDVAlertStyle. xlValidAlertStop,
Excel. XlFormatConditionOperator. xlBetween,
"1,2,3,4,5,6,7,8" ) ;
判断单元格是否存在数据有效性
try
{
if ( this . Range[ "A12" ] . Validation. Type >= 0 )
{
MessageBox. Show ( "单元格中有数据有效性!" ) ;
}
}
catch
{
MessageBox. Show ( "单元格中沒有数据有效性!" ) ;
}
动态的数据有效性
void 工作表1_SelectionChange ( Excel. Range Target)
{
if ( Target. Column == 1 && Target. Count == 1 && Target. Row > 1 )
{
Target. Validation. Delete ( ) ;
Target. Validation. Add (
Excel. XlDVType. xlValidateList,
Excel. XlDVAlertStyle. xlValidAlertStop,
Excel. XlFormatConditionOperator. xlBetween,
"主機,显示器" ) ;
}
}
自动展开数据有效性下拉列表
this . Application. SendKeys ( "%{down}" ) ;
在单元格中写入公式
this . Range[ "C1:C10" ] . Formula= "=sum(A1,B1)" ;
写入单元格区域数组公式
this . Range[ "C1" ] . FormulaArray = "=A1:A2*B1:B2" ;
检查单元格是否含有公式
Excel. Range rng = this . Application. Selection;
if ( Convert. IsDBNull ( rng. HasFormula) )
{
MessageBox. Show ( "公式区域為:" + rng. SpecialCells ( Excel. XlCellType. xlCellTypeFormulas, 23 ) . Address[ 0 , 0 ] ) ;
}
else if ( rng. HasFormula)
{
MessageBox. Show ( "全部单元格為公式!" ) ;
}
else
{
MessageBox. Show ( "全部单元格不為公式!" ) ;
}
判断单元格公式是否存在错误
未研究出来,如何调用用VBA.IsError,用了Excel自带函数来处理的.
Excel. Range rng = this . Range[ "A1" ] . Offset[ 0 , 1 ] ;
rng. Formula = "=iserror(A1)" ;
if ( rng. Value)
{
MessageBox. Show ( "A1单元格錯誤類型為:" + this . Range[ "A1" ] . Text) ;
}
else
{
MessageBox. Show ( "A1单元格結果為:" + this . Range[ "A1" ] . Text) ;
}
取得单元格中公式的引用单元格
Excel. Range rng = this . Range[ "C1" ] . Precedents;
MessageBox. Show ( "公式所引用的单元格有:" + rng. Address) ;
將单元格中的公式转换为数值
Excel. Range rng = this . Range[ "C1:C10" ] ;
rng. Formula = "=sum(A1:B1)" ;
rng. Value = rng. Value;
判断单元格是否存在批注
if ( this . Range[ "A1" ] . Comment == null )
{
MessageBox. Show ( "A1单元格中沒有批注" ) ;
}
else
{
MessageBox. Show ( "A1单元格中批注內容为:" + "\n" + this . Range[ "A1" ] . Comment. Text ( ) ) ;
}
为单元格添加批注
Excel. Range rng = this . Range[ "A1" ] ;
if ( rng. Comment == null )
{
rng. AddComment ( rng. Text) ;
rng. Comment. Visible = true ;
}
删除单元格中的批注
Excel. Range rng = this . Range[ "A1" ] ;
if ( rng. Comment != null )
{
rng. Comment. Delete ( ) ;
}
判断单元格区域是否存在合并单元格
Excel. Range rng = this . Application. Selection;
if ( Convert. IsDBNull ( rng. MergeCells) )
{
MessageBox. Show ( "区域中包含合并单元格!" ) ;
}
else if ( rng. MergeCells)
{
MessageBox. Show ( "区域中全部為合并单元格!" ) ;
}
else
{
MessageBox. Show ( "区域中沒有合并单元格!" ) ;
}
合并单元格时连接每个单元格的文本
Excel. Range rng = this . Application. Selection;
string s = string . Empty;
foreach ( Excel. Range Rng in rng)
{
s = s + Rng. Text;
}
this . Application. DisplayAlerts = false ;
rng. Merge ( ) ;
rng. Value = s;
this . Application. DisplayAlerts = true ;
合并內容相同的连续单元格
int rEnd = this . Range[ "A65535" ] . End[ Excel. XlDirection. xlUp ] . Row;
this . Application. DisplayAlerts = false ;
for ( int i = rEnd; i >= 2 ; i-- )
{
Excel. Range rng = this . Cells[ i, 1 ] ;
if ( rng. Value == rng. Offset[ - 1 , 0 ] . Value)
{
this . Application. Union ( rng, rng. Offset[ - 1 , 0 ] ) . Merge ( ) ;
}
}
取消合并单元格时在每个单元格中保留內容
int rEnd = this . Range[ "A65535" ] . End[ Excel. XlDirection. xlUp ] . Row;
int m = this . Cells[ rEnd, 1 ] . MergeArea. Count- 1 ;
this . Range[ Cells [ 1 , 1 ] , Cells[ rEnd, 1 ] ] . UnMerge ( ) ;
this . Application. DisplayAlerts = false ;
for ( int i = 1 ; i < rEnd+ m; i++ )
{
Excel. Range rng = this . Cells[ i, 1 ] ;
if ( rng. Offset[ 1 , 0 ] . Text == string . Empty)
{
rng. Offset[ 1 , 0 ] . Value = rng. Value;
}
}
高亮显示单元格区域
Excel. Range rng = this . Application. Selection;
Cells. Interior. ColorIndex = Excel. XlColorIndex. xlColorIndexNone;
rng. Interior. ColorIndex = 8 ;
双击被保护单元格时不显示提示消息框
if ( Target. Locked)
{
MessageBox. Show ( "此单元格已保护,不能編輯" ) ;
Cancel = true ;
}
重新计算工作表指定区域
Excel. XlCalculation oldCalcultion = this . Application. Calculation;
this . Application. Calculation = Excel. XlCalculation. xlCalculationManual;
this . Range[ "A1:D10" ] . Calculate ( ) ;
this . Application. Calculation = oldCalcultion;
录入数据后单元格自动保护
if ( this . ProtectContents)
{
this . Unprotect ( "123456" ) ;
}
if ( Target. Text != string . Empty)
{
Target. Locked = true ;
this . Protect ( "123456" ) ;
}
使用单元格的Address属性
if ( Target. Address[ 0 , 0 ] == "A1" )
{
MessageBox. Show ( "你选择了A1单元格" ) ;
}
使用Column属性和Row属性
int i= 0 ;
if ( Target. Column == 1 && Target. Row < 11 && int . TryParse ( Target. Text, out i) )
{
Target. Offset[ 0 , 1 ] . Value = i * 3 ;
}
使用Intersect方法
Excel. Range rng = this . Application. Intersect ( Target, this . Application. Union ( this . Range[ "A1:A10" ] , this . Range[ "C1:C10" ] ) ) ;
if ( rng != null )
{
MessageBox. Show ( "你选择了" + Target. Address[ 0 , 0 ] + "单元格" ) ;
使用工作表的名称
this . Application. Worksheets[ "工作表2" ] . Activate ( ) ;
使用工作的索引号
this . Application. Worksheets[ 2 ] . Activate ( ) ;
使用工作表的代码名称
MessageBox. Show ( this . Application. ActiveSheet. CodeName) ;
用ActiveSheet属性引用活动工作表
this . Application. Worksheets[ 2 ] . Select ( ) ;
MessageBox. Show ( this . Application. ActiveSheet. Name) ;
选择工作表的方法
this . Application. Worksheets[ 2 ] . Select ( ) ;
this . Application. Worksheets[ 2 ] . Activate ( ) ;
使用For遍历工作表
int wkCount = this . Application. Worksheets. Count;
string s = string . Empty;
for ( int i = 1 ; i <= wkCount; i++ )
{
s = s + this . Application. Worksheets[ i] . Name + "\n" ;
}
MessageBox. Show ( "工作簿中含有以下工作表:" + "\n" + s) ;
使用ForEach语句
string s = string . Empty;
foreach ( Excel. Worksheet wk in this . Application. Worksheets)
{
s = s + wk. Name + "\n" ;
}
MessageBox. Show ( "工作簿中含有以下工作表:" + "\n" + s) ;
在工作表中向下翻页
Excel. Sheets shs= Globals. ThisWorkbook. Worksheets;
Excel. Worksheet wkThis = shs. Application. ActiveSheet;
Excel. Worksheet wkNext;
int wkIndex = wkThis. Index;
int wkCount = shs. Count;
if ( wkIndex < wkCount)
{
wkNext = ( Excel. Worksheet) wkThis. Next;
wkNext. Select ( ) ;
}
工作表的添加与删除
Excel. Sheets wksThis = this . Application. Worksheets;
Excel. Worksheet wsAdd = this . Application. Worksheets. Add ( System. Type. Missing, wksThis[ wksThis. Count] ) ;
wsAdd. Name = "数据" ;
批量添加工作表
Excel. Sheets wksThis = this . Application. Worksheets;
Excel. Worksheet wksNew = null ;
if ( wksThis. Count <= 3 )
{
for ( int i = 1 ; i <= 10 ; i++ )
{
wksNew = wksThis. Add ( System. Type. Missing, wksThis[ wksThis. Count] ) ;
wksNew. Name = "第" + i. ToString ( ) + "个工作表" ;
}
}
禁止删除指定工作表
Office. CommandBarControl cmdCtl = this . Application. CommandBars[ 41 ] . Controls[ 2 ] ;
自动建立工作表目录
int i = this . Application. Worksheets. Count;
for ( int n = 1 ; n <= i; n++ )
{
this . Cells[ n+ 1 , 1 ] . Value = this . Application. Worksheets[ n] . Name;
}
建立工作表链接
int m = this . Application. Worksheets. Count;
if ( Target. Count == 1 )
{
if ( Target. Column== 1 )
{
if ( Target. Row> 1 && Target. Row<= ( m+ 1 ) )
{
this . Application. Sheets[ Target. Value ] . Select ( ) ;
}
}
}
工作表的深度隐藏
this . Application. Sheets[ 2 ] . Visible = Excel. XlSheetVisibility. xlSheetVeryHidden;
防止更改工作表的名称
void ThisWorkbook_BeforeClose ( ref bool Cancel)
{
if ( this . Sheets[ 1 ] . Name != "Excel Home" )
{
this . Sheets[ 1 ] . Name = "Excel Home" ;
}
this . Save ( ) ;
}
工作表中一次插入多行
Excel. Range rng = this . Rows[ 3 ] ;
rng. Resize[ 3 ] . Insert ( ) ;
删除工作表中的空行
Excel. Range rng = this . UsedRange;
int rngEnd = this . Cells[ rng. Rows. Count, rng. Columns. Count] . End[ Excel. XlDirection. xlUp ] . Row;
for ( int i = rngEnd; i >= 1 ; i++ )
{
if ( this . Application. WorksheetFunction. CountA ( this . Rows[ i] ) == 0 )
{
this . Rows[ i] . Delete ( ) ;
}
}
删除工作表的重复行
int rngEnd = this . Range[ "A65535" ] . End[ Excel. XlDirection. xlUp ] . Row;
for ( int i = rngEnd; i>= 1 ; i-- )
{
if ( this . Application. WorksheetFunction. CountIf ( this . Columns[ 1 ] , this . Cells[ i, 1 ] ) > 1 )
{
this . Rows[ i] . Delete ( ) ;
}
}
定位删除特定内容所在的行(删除A列中包含”Excel”字符的行
this . Application. DisplayAlerts = false ;
int rngEnd = this . Range[ "A65535" ] . End[ Excel. XlDirection. xlUp ] . Row;
string str = "Excel.*" ;
for ( int i = rngEnd; i >= 1 ; i-- )
{
Excel. Range rng = this . Cells[ i, 1 ] ;
if ( Regex. IsMatch ( rng. Text, str) )
{
this . Rows[ i] . Delete ( ) ;
}
}
注: 需引用using System. Text. RegularExpressions;
判断是否选中整行
int i = this . Columns. Count;
Excel. Range rng = this . Application. Selection;
if ( rng. Columns. Count == i)
{
MessageBox. Show ( "你选中了一整行" ) ;
}
else
{
MessageBox. Show ( "你没有选中了一整行" ) ;
}
限制工作表的滚动区域
this . ScrollArea = "B4:H12" ;
复制自动筛选后的数据区域
this . Application. Worksheets[ 2 ] . Cells. Clear ( ) ;
if ( this . FilterMode)
{
this . AutoFilter. Range. SpecialCells ( Excel. XlCellType. xlCellTypeVisible) . Copy (
this . Application. Worksheets[ 2 ] . Cells[ 1 , 1 ] ) ;
}
使用高级筛选获得不重复记录
Excel. Range rngSheet2 = this . Application. Worksheets[ 2 ] . Cells;
rngSheet2. Clear ( ) ;
this . Range[ "A1" ] . CurrentRegion. AdvancedFilter (
Excel. XlFilterAction. xlFilterCopy,
System. Type. Missing,
this . Application. Worksheets[ 2 ] . Cells[ 1 , 1 ] ,
true ) ;
工作表的保护与解除保护
this . Unprotect ( "12345" ) ;
this . Cells[ 1 , 1 ] . Value= 100 ;
this . Protect ( "12345" ) ;
奇偶页打印
int pg = this . PageSetup. Pages. Count;
for ( int i = 1 ; i <= pg; i= i+ 2 )
{
this . PrintOutEx ( 1 , i) ;
}
使用工作簿的名称
string str = this . Application. Workbooks[ "工作簿的引用方法.xlsx" ] . Path;
MessageBox. Show ( str) ;
使用ThisWorkbook
this . Application. ThisWorkbook. Close ( false ) ;
使用ActiveWorkbook
MessageBox. Show ( this . Application. ActiveWorkbook. Name) ;
新建工作簿
Excel. Workbook Nowbook;
string [ ] shName = new string [ 4 ] { "余额" , "单价" , "数量" , "金额" } ;
string [ ] arr = new string [ 12 ] { "01月" , "02月" , "03月" , "04月" , "05月" , "06月" , "07月" , "08月" , "09月" , "10月" , "11月" , "12月" } ;
this . Application. SheetsInNewWorkbook = 4 ;
Nowbook = this . Application. Workbooks. Add ( ) ;
for ( int i = 1 ; i <= 4 ; i++ )
{
Nowbook. Sheets[ i] . Name = shName[ i - 1 ] ;
Nowbook. Sheets[ i] . Range[ "B1" ] . Resize[ 1 , arr. Length] = arr;
Nowbook. Sheets[ i] . Range[ "B2" ] = "品名" ;
}
Nowbook. SaveAs ( "C:\\" + "存货明细.xlsx" ) ;
Nowbook. Close ( true ) ;
打开指定的工作簿
int wkCount = this . Application. Workbooks. Count;
for ( int i = 1 ; i <= wkCount; i++ )
{
if ( this . Application. Workbooks[ i] . Name == "123.xlsx" )
{
MessageBox. Show ( "123工作簿已经打开" ) ;
}
}
this . Application. Workbooks. Open ( "C:\\" + "123.xlsx" ) ;
使用公式不打开工作簿取得其他工作簿数据
if ( File. Exists ( "C:\\11.xlsx" ) )
{
string Temp = string . Empty;
Temp = "\'C:\\[11.xlsx]工作表1\'!" ;
Excel. Range rng = this . Range[ "A1:F22" ] ;
rng. FormulaR1C1 = "=" + Temp + "RC" ;
}
使用GetObject函数
隱藏Application對象
Excel. Application myApp = new Excel. Application ( ) ;
Excel. Worksheet sh;
string Temp = "C:\\11.xlsx" ;
myApp. Visible = false ;
sh = myApp. Workbooks. Open ( Temp) . Sheets[ 1 ] ;
Excel. Range rng = sh. Range[ "A1" ] . CurrentRegion;
this . Range[ "A1" ] . Resize[ rng. Rows. Count, rng. Columns. Count] . Value = rng. Value;
使用ExecuteExcel4Macro方法
private void button1_Click ( object sender, EventArgs e)
{
object Rcout, Ccout;
String temp, temp1, temp2, temp3;
object [ , ] arr;
temp = "\'C:\\[11.xlsx]工作表1\'!" ;
temp1= temp + this . Rows[ 1 ] . Address[
System. Type. Missing,
System. Type. Missing,
Excel. XlReferenceStyle. xlR1C1] ;
temp1 = "Counta(" + temp1 + ")" ;
Ccout = this . Application. ExecuteExcel4Macro ( temp1) ;
temp2 = temp + this . Columns[ "A" ] . Address[
System. Type. Missing,
System. Type. Missing,
Excel. XlReferenceStyle. xlR1C1] ;
temp2 = "Counta(" + temp2 + ")" ;
Rcout = this . Application. ExecuteExcel4Macro ( temp2) ;
int R = int . Parse ( Rcout. ToString ( ) ) ;
int C = int . Parse ( Ccout. ToString ( ) ) ;
arr = new object [ R , C] ;
for ( int m= 0 ; m< R; m++ )
{
for ( int n = 0 ; n < C; n++ )
{
temp3 = temp + this . Cells[ m+ 1 , n+ 1 ] . Address[
System. Type. Missing,
System. Type. Missing,
Excel. XlReferenceStyle. xlR1C1] ;
arr[ m, n] = this . Application. ExecuteExcel4Macro ( temp3) ;
}
}
this . Range[ "A1" ] . Resize[ R , C] . Value = arr;
}
使用SQL連接
private void button1_Click ( object sender, EventArgs e)
{
DataSet DS = ExcelOpen ( "C:\\11.xlsx" ) ;
DataTable DT = new DataTable ( ) ;
DT = DS. Tables[ 0 ] ;
if ( DT != null )
{
for ( int m = 0 ; m < DT. Rows. Count; m++ )
{
for ( int n = 0 ; n < DT. Columns. Count; n++ )
{
this . Cells[ m + 1 , n + 1 ] . Value = DT. Rows[ m] [ n] . ToString ( ) ;
}
}
}
}
private DataSet ExcelOpen ( string str)
{
string ConnStr;
ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + str + " ;Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"" ;
OleDbConnection ExcelConn = new OleDbConnection ( ConnStr) ;
try
{
ExcelConn. Open ( ) ;
if ( ExcelConn. State == ConnectionState. Open)
{
OleDbDataAdapter ExcelDA = new OleDbDataAdapter ( "select * from [工作表1$]" , ExcelConn) ;
DataSet ExcelDS = new DataSet ( ) ;
ExcelDA. Fill ( ExcelDS) ;
ExcelConn. Close ( ) ;
return ExcelDS;
}
else
{
return null ;
}
}
catch ( Exception ex)
{
MessageBox. Show ( "提示信息:" + ex. ToString ( ) ) ;
return null ;
}
}
返回窗口的可視區域地址
string str;
str = this . Application. ActiveWindow. VisibleRange. Address[ 0 , 0 ] ;
MessageBox. Show ( "窗品的可視區域為:" + str) ;
在工作表中添加图形
Excel. Shape myShape;
try
{
this . Shapes. Item ( "myShape" ) . Delete ( ) ;
}
catch
{
}
myShape = this . Shapes. AddShape (
Office. MsoAutoShapeType. msoShapeRectangle,
40 , 120 , 280 , 30 ) ;
myShape. Name = "myShape" ;
Excel. Characters myCharacters = myShape. TextFrame. Characters ( ) ;
myCharacters. Text = "單擊將選擇工作表2!" ;
myCharacters. Font. Name = "新細明體" ;
myCharacters. Font. Size = 22 ;
myCharacters. Font. ColorIndex = 7 ;
myShape. TextFrame. HorizontalAlignment = Excel. XlHAlign. xlHAlignCenter;
myShape. TextFrame. VerticalAlignment = Excel. XlVAlign. xlVAlignCenter;
myShape. Placement = Excel. XlPlacement. xlFreeFloating;
myShape. Select ( ) ;
Excel. ShapeRange myShapeRng = this . Application. Selection. ShapeRange;
myShapeRng. Line. Weight = 1 ;
myShapeRng. Line. DashStyle = Office. MsoLineDashStyle. msoLineSolid;
myShapeRng. Line. Style = Office. MsoLineStyle. msoLineSingle;
myShapeRng. Line. Transparency = 0 ;
myShapeRng. Line. Visible = Office. MsoTriState. msoTrue;
myShapeRng. Line. ForeColor. SchemeColor = 40 ;
myShapeRng. Line. BackColor. RGB = 65536 * 255 + 256 * 255 + 255 ;
myShapeRng. Fill. Transparency = 0 ;
myShapeRng. Fill. Visible = Office. MsoTriState. msoTrue;
myShapeRng. Fill. ForeColor. SchemeColor = 41 ;
myShapeRng. Fill. OneColorGradient (
Office. MsoGradientStyle. msoGradientHorizontal,
4 , 0.23F ) ;
this . Range[ "A1" ] . Select ( ) ;
this . Hyperlinks. Add ( myShape, "" , "工作表2!A1" , "選擇工作表2!" ) ;
导出出工作表中的图片
string FileName = string . Empty;
Excel. ChartObjects chartObjs = ( Excel. ChartObjects) this . ChartObjects ( missing) ;
foreach ( Excel. Shape myShape in this . Shapes)
{
FileName = "C:\\" + myShape. Name + ".gif" ;
myShape. Copy ( ) ;
Excel. Chart myChart = chartObjs. Add ( 0 , 0 , myShape. Width + 28 , myShape. Height + 30 ) . Chart;
myChart. Paste ( ) ;
myChart. Export ( FileName, "gif" ) ;
myChart. Parent. Delete ( ) ;
}
在工作表中添加艺术字
Excel. Shape myShape;
try
{
this . Shapes. Item ( "myShape" ) . Delete ( ) ;
}
catch
{
}
myShape = this . Shapes. AddTextEffect (
Office. MsoPresetTextEffect. msoTextEffect15,
"我愛Excel Home" ,
"新細明體" ,
36 ,
Office. MsoTriState. msoFalse,
Office. MsoTriState. msoFalse,
100 , 100 ) ;
myShape. Name = "myShape" ;
myShape. Fill. Solid ( ) ;
myShape. Fill. ForeColor. SchemeColor = 55 ;
myShape. Fill. Transparency = 0 ;
myShape. Line. Weight = 1.5F ;
myShape. Line. DashStyle = Office. MsoLineDashStyle. msoLineSolid;
myShape. Line. Style = Office. MsoLineStyle. msoLineSingle;
myShape. Line. Transparency = 0 ;
myShape. Line. ForeColor. SchemeColor = 12 ;
myShape. Line. BackColor. RGB = 65536 * 255 + 256 * 255 + 255 ;
}
遍历工作表中的图片
int i = this . Shapes. Count;
for ( int m = 1 ; m <= i; m++ )
{
this . Cells[ m, 1 ] . Value = this . Shapes. Item ( m) . Name;
}
移动、旋转图片
Excel. Shape myShape = this . Shapes. Item ( 2 ) ;
for ( int m = 1 ; m <= 300 ; m = m + 5 )
{
myShape. Top = ( float ) System. Math. Sin ( m * ( 3.1416 / 180 ) ) * 100 + 100 ;
myShape. Left = ( float ) System. Math. Cos ( m * ( 3.1416 / 180 ) ) * 100 + 100 ;
myShape. Fill. ForeColor. RGB = m * 100 ;
for ( int n = 1 ; n <= 10 ; n++ )
{
myShape. IncrementRotation ( - 2 ) ;
}
}
工作表中自动插入图片
string FilePath;
Excel. Range rng;
string temp = string . Empty;
for ( int i = 3 ; i <= this . Range[ "A65535" ] . End[ Excel. XlDirection. xlUp ] . Row; i++ )
{
FilePath = "C:\\Users\\Public\\Pictures\\Sample Pictures\\" + this . Cells[ i, 1 ] . Text + ".jpg" ;
if ( File. Exists ( FilePath) )
{
rng = this . Cells[ i, 3 ] ;
this . Shapes. AddPicture ( FilePath,
Office. MsoTriState. msoTrue,
Office. MsoTriState. msoTrue,
rng. Left + 1 ,
rng. Top + 1 ,
rng. Width + 1 ,
rng. Height + 1 ) ;
}
else
{
temp = temp + "\n" + this . Cells[ i, 1 ] . Text ;
}
}
if ( temp != string . Empty)
{
MessageBox. Show ( temp + "\n" + "沒有照片" ) ;
}
特别鸣谢:Excelhome论坛的a5658805朋友辛劳整理及测试。
资料来源地址:http://club.excelhome.net/thread-1200539-1-1.html