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#调用api(FindFirstFile,FindNextFile)高效遍历目录文件

在c#下遍历目录,应用最多的应该就是 System.IO.DirectoryInfo.GetDirectories或GetFiles了,但是当目录特别大,文件特别多时,效率不尽人意,此时我们很容易想到...

C#操作Excel的函数

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

C#操作Excel(三)相关函数

设置Excel格式和WorkBook.SaveAs函数详解 设置Excel格式 Excel.Range contentRange = excel_conftable.Range[excel_conft...

C#导出Excel几个例子

#region 导出 Excel 文件 ////  /// 导出 Excel 文件 ///  /// 要导出的DataSet /// 要导出的文件名 private void Export...

聪明人喜欢挑战性,高难度excel操作技巧你会几个?

办公中我们使用的工具越来越多,我们的工作效率也是越来越高,在这个用网络代替笔纸的时代,我们好像一时没有适应过来,平时的时候我们灵活的运用笔纸进行勾画文字图案,可是到了电脑上或许我们就不会了,就连一个简...

delphi学习点滴——操作excel的几个控件使用说明

在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。 1) 打开Excel ExcelApplication1.Connect; 2...

excel操作技巧都在这,连大神都需要必备的技巧,你不会的有几个

我们在工作的时候会发现excel表格制作是绝对少不了的,我们在操作的时候使用一些excel的操作技巧是绝对能帮助我们提高工作效率和质量的,对于excel操作技巧来说,有太多需要我们学习的,就算是大神级...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:C#操作Excel的几个函数
举报原因:
原因补充:

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