How can I use this data search code in excel file with out using spire.xls

Question

1.00/5 (1 vote)

See more:

My code is about Retrieving data and extract to the first worksheet of the new excel workbook ,
I'm using here wpf ,and the spire.xls method :

I tried to find something seemlier but i couldn't debug my code .
Ps: cellRAnge is available only when i use spire.xls

I want to replace spire.xls use with Excel.APP

using Spire.Xls;

        public void codespire()
        {
            Workbook newBook = new Workbook();
            Worksheet newSheet = newBook.Worksheets[0];
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Information.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            int i = 1;
            int columnCount = sheet.Columns.Count();
            foreach (CellRange range in sheet.Columns[0])
            {
                if (range.Text == "teacher")
                {
                    CellRange sourceRange = sheet.Range[range.Row, 1, range.Row, columnCount];
                    CellRange destRange = newSheet.Range[i, 1, i, columnCount];
                    sheet.Copy(sourceRange, destRange, true);
                    i++;
                }
            }
            newBook.SaveToFile("NewForm.xlsx", ExcelVersion.Version2010);

        }
    }
}

What I have tried:

I want to replace spire.xls with with Excel.APPlication methode:

using Excel = Microsoft.Office.Interop.Excel;



public void myCodeExcel.Appli()
{

 Excel.Application APP = new Microsoft.Office.Interop.Excel.Application();
        //Destination
 Microsoft.Office.Interop.Excel.Workbook newBook = APP.Workbooks.Open(excelFileDestinationPAth);
 
       Microsoft.Office.Interop.Excel.Worksheet newSheet = (Microsoft.Office.Interop.Excel.Worksheet) newBook.Worksheets[1];        
        
// source
    Microsoft.Office.Interop.Excel.Workbook workBook = APP.Workbooks.Open(excelFileSourcePAth);
 
       Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet) workbook.Worksheets[1];

int i =1 ;
 int columnCount = sheet.Columns.Count();
 foreach (Excel.Range r in (Excel.Range) sheet.Columns["A", Type.Missing])
            {
             
        Excel.Range   rang = r.Find(numSérie,sheet.Cells[i, 1],Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows,  Excel.XlSearchDirection.xlNext,
  false,false,false);
                string cellAdress = rang.get_Address(false, false, Excel.XlReferenceStyle.xlA1, false, false);
                string rangeCellSearch = sheet.Range[cellAdress].Value;
            

 if (rangeCellSearch == Teacher)        
{
//how can I copy range filter results to the second excel file ..

}

Posted 4-Dec-17 21:39pm

EM_Y

Updated 5-Dec-17 3:48am

v3

Add a Solution

Comments

Afzaal Ahmad Zeeshan 5-Dec-17 4:15am

By rewriting the code Spire did. If the function is made available from a library, then how do you expect to find that function anywhere else (let alone in these COM classes).

What is the question?

EM_Y 5-Dec-17 4:33am

I've just update my code , I hope it's clear now ! very thankful for your attention and I wish your help !
I want to copy all rows result in one simple code !

Kenneth Haugland 5-Dec-17 6:13am

Don't repost your question, use Improve question if you have more details instead.

ZurdoDev 5-Dec-17 9:26am

You can learn a lot of code by simply recording macros and then looking at the code.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

EM_Y · Accepted Answer · 2017-12-05T03:48:00

using Excel = Microsoft.Office.Interop.Excel;

public void my code () 

{
Excel.Application APP = new Microsoft.Office.Interop.Excel.Application();
        //Destination
 Microsoft.Office.Interop.Excel.Workbook newBook = APP.Workbooks.Open(excelFileDestinationPAth);
 
       Microsoft.Office.Interop.Excel.Worksheet newSheet = (Microsoft.Office.Interop.Excel.Worksheet) newBook.Worksheets[1];        
        
// source
    Microsoft.Office.Interop.Excel.Workbook workBook = APP.Workbooks.Open(excelFileSourcePAth);
 
       Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet) workbook.Worksheets[1];


///column where I'm gonna search my keysearch
 Excel.Range columSearchkey = (Excel.Range)sheet.Columns["A", Type.Missing];
   
 // my range result
  Excel.Range rangresult = columSearchkey.Find(numSérie,sheet.Cells[i, 1],Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows,  Excel.XlSearchDirection.xlNext,false,false,false);

// cell where I find my keysearch
  string cellAdress = rangresult.get_Address(false, false, Excel.XlReferenceStyle.xlA1, false, false);
//cell number
 string cellPositionnumber = cellAdress.Substring(1);
          
// data cell value result  
    string datacellvalueresult = sheet.Range[cellAdress].Value;
     
                 if (datacellvalueresult == searchkey)
                 {

//I copy data cell by cell because for luck in my case  the number range result is fix for three rows 

//first cell
 newSheet.get_Range("C34:E34").Value = sheet.get_Range(string.Concat("E", cellPositionnumber, ":", "G", cellPositionnumber)).Value2;
        .
        .
        .
//rest cells ...

}

}