C#操作Excel的几个函数

原创 2011年01月19日 17:21:00

//获得某个文本的在excel的sheet中的位置

public Position GetPosition(Worksheet mySheet,string strText)
        {
            Position positon = new Position();
            try
            {
                Range currentFind = mySheet.Cells.Find(strText, Type.Missing,
                            Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
                            Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false,
                            Type.Missing, Type.Missing);
                if (currentFind!=null)
                {
                    positon.Row = currentFind.Row;
                    positon.Column = currentFind.Column;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message + " please contact the administrator!");
            }
            return positon;
        }

 

 

 

 //获得位置,给定区域内获取

        public Position GetPosition(Worksheet mySheet, string strText,int MaxRow,int MinRow,int Column,decimal Amount)
        {
            Position positon = new Position();
            bool bFind = false;
            bool bComplete = false;
            try
            {
                if (strText=="")
                {
                    return positon;
                }
                Range currentFind = mySheet.Cells.Find(strText, Type.Missing,
                            Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
                            Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false,
                            Type.Missing, Type.Missing);

                if (currentFind != null)
                {
                    if ((currentFind.Row >= (MinRow + 1)) && (currentFind.Row <= MaxRow) && (currentFind.Column == (Column + 1)))
                    {
                        Range range = (Range)mySheet.Cells[currentFind.Row, currentFind.Column + 2];
                        string strResult = "";
                        if (range.Value2 != null)
                        {
                            strResult = range.Value2.ToString();
                        }
                        decimal currentAmount = decimal.Parse(strResult);
                        if (currentAmount == Amount)
                        {
                            bFind = true;
                            bComplete = true;
                            positon.Row = currentFind.Row;
                            positon.Column = currentFind.Column;
                        }
                    }
                }
                else
                {
                    bComplete = true;
                }
                //continue to find
                Range rangeFind = currentFind;
                 while (!bComplete)
                 {
                     if (!bFind)
                     {
                         Range nextFind = mySheet.Cells.FindNext(rangeFind);
                         if (nextFind != null)
                         {
                             if (!((nextFind.Row == currentFind.Row) && (nextFind.Column == currentFind.Column)))
                             {
                                 rangeFind = nextFind;
                                 if ((nextFind.Row >= (MinRow + 1)) && (nextFind.Row < MaxRow) && (nextFind.Column == (Column + 1)))
                                 {
                                     Range range = (Range)mySheet.Cells[nextFind.Row, nextFind.Column + 2];
                                     string strResult = "";
                                     if (range.Value2 != null)
                                     {
                                         strResult = range.Value2.ToString();
                                     }
                                     decimal currentAmount = decimal.Parse(strResult);
                                     if (currentAmount == Amount)
                                     {
                                         bFind = true;
                                         bComplete = true;
                                         positon.Row = nextFind.Row;
                                         positon.Column = nextFind.Column;
                                     }
                                 }
                             }
                             else
                             {
                                 bComplete = true;
                             }
                          
                         }
                         else
                         {
                             bComplete = true;
                         }
                       
                     }
                 }
            }
            catch (Exception e)
            {
                MessageBox.Show("Error:" + e.Message + " please contact the administrator!");
            }
            return positon;
        }

 

 

 //插入行

 

                if (iRowNeedInsert > 0)
                {
                    //insert blank rows
                    Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mySheet1.Rows[pSDTotal1.Row - 1, missing];
                    for (int i = 0; i < iRowNeedInsert; i++)
                    {
                        range.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, missing);
                    }
                }
 //复制和粘贴

                rCopy1 = "G" + (pSD2.Row + 1).ToString() + " ";
                rCopy2 = "J" + (pSDLast2.Row).ToString() + " ";
                rPaste = "G" + (pSDLast1.Row + 1).ToString() + " ";
                mySheet2.get_Range(rCopy1, rCopy2).Copy(System.Type.Missing);
                mySheet1.get_Range(rPaste, System.Type.Missing).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, System.Type.Missing, System.Type.Missing);

 

 //置为空

 mySheet1.get_Range(mySheet1.Cells[iCountLC, pLC1.Column], mySheet1.Cells[iCountLC, pLC1.Column + 4]).Value2 = "";

 

 //排序

   Microsoft.Office.Interop.Excel.Range rngLC = mySheet1.get_Range(mySheet1.Cells[pLC1.Row + 1, pLC1.Column], mySheet1.Cells[pLCLast1.Row, pLC1.Column + 4]);
                rngLC.Sort(rngLC, Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending,
                                    missing, missing, Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending,
                                    missing, Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending,
                                    Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, missing, missing,
                                    Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns,
                                    Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin,
                                    Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal,
                                    Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal,
                                    Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal);

C#操作Excel的函数

对于Excel的数据处理功能,大家都已经了解。 我们经常需要将数据导入到Excel,或直接打开Excel文档,读写文件操作,这需要用到ExcelHelper类,有了这个类,这些操作大大的减少我们工作...
  • cndrip
  • cndrip
  • 2012年09月09日 22:16
  • 7744

操作EXCEL代码(c#完全版)

using System;     using System.Collections;     using Excel=Microsoft.Office.Interop.Excel;    ...
  • my98800
  • my98800
  • 2016年11月08日 09:26
  • 1142

C#操作Excel类,功能比较全

原文出处: http://www.cnblogs.com/wangchuang/p/5546345.html /// 常用工具类——Excel操作类 ///  --------...
  • wuyuander
  • wuyuander
  • 2017年06月06日 11:13
  • 507

使用C#对Excel文档的相关操作

  1.首先需要引入Excel的Com组件,Microsoft Excel 11.0 Object Library 1.5(该组件版本为office2003)添加该引用后在解决方案的引用文件夹里会有E...
  • wwwinking
  • wwwinking
  • 2009年12月29日 17:51
  • 3263

自己用的c#操作excel类。

下面的程序,是自己经常用到的excel操作类,本程序与别的对excel操作唯一改进的地方是对excel数据的填充方式上。网上经常看到的填充方式有:1:按单元格2:按区域(range)本程序,也还是按区...
  • yangang0201
  • yangang0201
  • 2007年07月13日 09:56
  • 4111

【关于MFC操作Excel表格的一些经验】

关于MFC操作excel的方法
  • bigtree_mfc
  • bigtree_mfc
  • 2014年10月30日 19:43
  • 1979

C#操作excel(多种方法比较)

我们在做excel资料的时候,通常有以下方法。 一.导入导出excel常用方法: 1.用查询表的方式查询并show在数据集控件上。 public static string st...
  • u012543266
  • u012543266
  • 2014年03月26日 21:30
  • 1607

C#对Excel的读写操作

读取Excel,首先要添加Microsoft.Office.Interop.Excel 12.0 动态库  读取工作薄中的sheetspublic ArrayList loadExcelSheets(...
  • dyyaries
  • dyyaries
  • 2011年06月29日 18:05
  • 1835

C#对Excel的基本操作

Office2007尚未普及,Office2000~2003仍然是主流。本文介绍一些C#处理Excel得方法,都是自己封装的~1. 添加COM组件的Reference2. 引入名字空间using Mi...
  • fallriver
  • fallriver
  • 2008年04月24日 14:53
  • 1659

C#中操作Excel(3)—— Excel中操作文本、图片和批注

一 引言        本文主要介绍向Excel中插入文本和图片的方法。相信大家对Excel的模型对象都有了一定的了解,和Word相似,Excel中插入文本和图片也需要依靠Range对象。但是与Wor...
  • lzhui1987
  • lzhui1987
  • 2016年11月17日 11:35
  • 1688
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:C#操作Excel的几个函数
举报原因:
原因补充:

(最多只允许输入30个字)