Click here to Skip to main content
15,886,422 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
this code is searching and getting data from excelfileSource and save it in a second excelfileDestination ;; I want to convert this code from using spire.xls to normal Excel.App method .

this is my code using Spire.xls :

  public void myCodeSpire ()
{
           //excel File Destination
           workbook newBook = new Workbook();    
           newBook.LoadFromFile("ExcelFileDestination.xls", ExcelVersion.Version97to2003);
           Worksheet newSheet = newBook.Worksheets[0];
           //excel File Source
           Workbook workbook = new Workbook();   
           workbook.LoadFromFile(ExcelFileSource.xls, ExcelVersion.Version97to2003);
           Worksheet sheet = workbook.Worksheets[2];


        foreach (CellRange range in sheet.Columns[0])
              {
                if (range.Text == KeySearch)
                  {
          CellRange sourceRange = sheet.Range[range.Row, 5, range.Row + 2, 7];
          CellRange destRange = newSheet.Range[34, 3, 36, 5];
          sheet.Copy(sourceRange, destRange,true);


          sourceRange = sheet.Range[range.Row, 9, range.Row + 2, 11];
          destRange = newSheet.Range[34, 6, 36, 8];
          sheet.Copy(sourceRange, destRange, true);
                   

           sourceRange = sheet.Range[range.Row, 13, range.Row + 2, 13];
           destRange = newSheet.Range[34, 9, 36, 9];
           sheet.Copy(sourceRange, destRange, true);

                 
            sourceRange = sheet.Range[range.Row, 15, range.Row + 2, 15];
            destRange = newSheet.Range[34, 10, 36, 10];
             sheet.Copy(sourceRange, destRange, true);

                  
                   }
              }

      newSheet.Range["C34:J36"].NumberFormat = "0.000";
      newSheet.Range["C34:J36"].Borders.LineStyle = LineStyleType.Thin;
      newSheet.Range["C34:J36"].Borders.Color =System.Drawing.Color.Black; 

      newSheet.Range["C34:J36"].Border[BordersLineType.DiagonalDown].LineStyle= LineStyleType.None;

      newSheet.Range["C34:J36"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
      

       
 newBook.SaveToFile("ExcelFileDestination.xls", ExcelVersion.Version97to2003);
}



Thank you for any help .

What I have tried:

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[3];               
     foreach (CellRange range in sheet.Columns[0])
          {
              if (range.Text == KEYsearch)
              {
         
            //getdata search code from source and copy it in excel file destination


         }


newBook.save(excelFileDestinationPath);
Posted
Updated 3-Dec-17 22:50pm
v5

1 solution

Well, since you really don't know how to do the simplest of stuff in Excel you might wanna start here:
Walkthrough: Creating Your First VSTO Add-in for Excel[^]

Then use Improve question when you are stuck on a specific problem.
 
Share this answer
 
Comments
EM_Y 4-Dec-17 4:46am    
I guess you didn't even get what I'm trying to explain !
I want to copy the line where my Key search exist in column [0]filesource +the 2 line next to my new file destination . I used to code with spire .xls and my code work very fine but I want to convert it to nrmal excel.App method .
It'll be sure simple to copy a specific cell data to another .
Thank you for your help.
Kenneth Haugland 4-Dec-17 5:01am    
xls files are xls files, so what is your problem then? Why not simply use IO file.copy?
EM_Y 4-Dec-17 5:16am    
it's not the same property code , there is differences between using : using spire.xls and using Excel = Microsoft.Office.Interop.Excel namespaces !
I'm still trying to find it .. very thankful .
Kenneth Haugland 4-Dec-17 5:44am    
I really dont think you understand this:
"This Professional .NET Excel component is a standalone Excel .NET managed assembly and does not depend on Microsoft Office Excel. Spire.XLS for .NET offers support both for the old Excel 97-2003 format (.xls) and for the new Excel 2007,Excel 2010, Excel 2013 and Excel 2016 (.xlsx, .xlsb, .xlsm), along with Open Office(.ods) format. It features fast and reliably compared to developing your own spreadsheet manipulation solution or using Microsoft Automation." Meaning it creates an Excel file, and to be able to use Excel.Interop you need to have Excel installed on your computer. If you are wondering about how to do stuff in Excel, just use record macro and look at the excel code.
EM_Y 4-Dec-17 6:22am    
okey I'm understanding this , Thank you for your time I'm trying my best .

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900