EXCEL获取单元格内容方法



在VS2012中调用COM Interop DLL操作Excel通过get_Range去获取Range时，会发生Object does not contain a definition for get_Range的错误。其原因和解决方案：

Misha‘s explanation is correct - when using No PIA, methods returning object are treated as if they return dynamic in order to simulate the VBA semantics of COM Variants. Because the return value of sh.Cells is Object, sh.get_Range is dispatched dynamically, and the dynamic COM binder does not support the get_Range syntax exposed in C# before indexed properties were supported. We‘ve tried to maintain backwards compatibility wherever possible when you turn on Embed Interop Types for a COM reference, but this is one place where some further tweaking is required.

The workaround you proposed will work - a cleaner workaround is the one Mike Rosenblum pointed out to use C# 4.0‘s new indexed properties syntax, which the dynamic COM binder does understand. You can then represent the operation with the following code:

Excel.Range r = sh.Range[sh.Cells[1, 1], sh.Cells[2, 2]];

See Also
get_Range method missing with embedded interop assembly

具体来讲^[2]

由于Framework版本不同，因此支持的也不一样

例如：
在 .NET Framework 3.5 語法

Excel.Range r = sh.Range(sh.Cells[1, 1], sh.Cells[2, 2]);
在 .NET Framework 4.0-4.5 改用
Excel.Range r = sh.Range[sh.Cells[1, 1], sh.Cells[2, 2]];

winform导出Excel代码：

使用方法：    ExportExcel("条形码数据一览", GetSearchData);//GetSearchData可以传datatable 或者datagridview下面代码是传递Datatable的。

     /// <summary>  
              /// 查询的数据导出为Excel  
              /// </summary>  
              /// <param name="fileName">导出文件名</param>  
              /// <param name="myDGV">导出的datatable数据</param>  
              private void ExportExcel(string fileName, MDataTable myDGV)  
              {  
                   string saveFileName = "";  
                   SaveFileDialog saveDialog = new SaveFileDialog();  
                   saveDialog.DefaultExt = "xls";  
                   saveDialog.Filter = "Excel文件|*.xls";  
                   saveDialog.FileName = fileName;  
                   saveDialog.ShowDialog();  
                   saveFileName = saveDialog.FileName;  
                   if (saveFileName.IndexOf(":") < 0) return; //被点了取消  
                   Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();  
                   if (xlApp == null)  
                   {  
                        MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");  
                        return;  
                   }  
      
                   Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;  
                   Microsoft.Office.Interop.Excel.Workbook workbook =  
     workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);  
                   Microsoft.Office.Interop.Excel.Worksheet worksheet =  
     (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1  
      
      
                   //写入标题  
                   for (int i = 0; i < myDGV.Columns.Count; i++)  
                   {  
                        worksheet.Cells[1, i + 1] = myDGV.Columns[i].ColumnName;  
                        //标题  
                        Microsoft.Office.Interop.Excel.Range titleRange = worksheet.Range[worksheet.Cells[1, 1],   
    worksheet.Cells[1, i + 1]];//选中标题  
                        titleRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //水平居中  
                   }  
                   //写入数值  
                   for (int r = 0; r < myDGV.Rows.Count; r++)  
                   {  
                        for (int i = 0; i < myDGV.Columns.Count; i++)  
                        {  
                             worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r][i].Value;  
                             //设置边框  
                             Microsoft.Office.Interop.Excel.Range allRange = worksheet.Range[worksheet.Cells[1, 1],   
    worksheet.Cells[r + 1, i + 1]];  
                             allRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
                        }  
                        System.Windows.Forms.Application.DoEvents();  
                   }  
                   //设置最后一行边框  
                   Microsoft.Office.Interop.Excel.Range endRange = worksheet.Range[worksheet.Cells[1, 1],   
    worksheet.Cells[myDGV.Rows.Count + 1,myDGV.Columns.Count]];  
                   endRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
                   worksheet.Columns.EntireColumn.AutoFit();//列宽自适应  
                   if (saveFileName != "")  
                   {  
                        try  
                        {  
                             workbook.Saved = true;  
                             workbook.SaveCopyAs(saveFileName);  
                        }  
                        catch (Exception ex)  
                        {  
                             MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);  
                        }  
      
                   }  
                   xlApp.Quit();  
                   GC.Collect();//强行销毁  
                   MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
              }  

 

其他DataGridview导出Excel的资料：

http://www.360doc.com/content/11/1211/17/8147167_171489298.shtml  winform应用使用DataGridView数据导出到Excel

http://ruantnt.blog.163.com/blog/static/190525452201110185199346/  winform(c#) DataGridView导出Excel 
http://hi.baidu.com/wenshangang/item/1227f415fab1a35a2a3e229f  Winform中dataGridView控件导出到excel表格中

http://blog.sina.com.cn/s/blog_62cd5a980101905a.html  WinForm中DataGridView导出为Excel(快速版)

http://www.cr173.com/html/7906_1.html  WinForm下DataGridView导出Excel的实现