关闭

C#操作Excel的几个函数

标签: excelc#exceptionstringnull
2381人阅读 评论(0) 收藏 举报
分类:

//获得某个文本的在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);

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:92072次
    • 积分:1573
    • 等级:
    • 排名:千里之外
    • 原创:65篇
    • 转载:6篇
    • 译文:0篇
    • 评论:8条
    文章分类
    最新评论