Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue May 10, 2022 9:58 am

Hello,

I have a problem with copy rows. I have a folder with excel files, and I will copy all files A6:T35 cell to one excel files. My problem is, the last CellRange copyed just.
Pleas, help my, how can i all files cell copy to one sheet.
My code:
Code: Select all
public class Masolo extends JFrame
{

   private JPanel contentPane;
   private int a = 6;
   private int b = 1;
   private int c = 35;
   private int d = 20;
   private int uja = 6;
   private int ujb = 1;
   private int ujc = 35;
   private int ujd = 20;
   private File mappa;
   private File[] fajlok;
   private Workbook workbook;

   /**
    * Launch the application.
    */
   public static void main(String[] args)
   {
      EventQueue.invokeLater(new Runnable()
      {
         public void run()
         {
            try
            {
               Masolo frame = new Masolo();
               frame.setVisible(true);
            }
            catch (Exception e)
            {
               e.printStackTrace();
            }
         }
      });
   }

   /**
    * Create the frame.
    */
   public Masolo()
   {
      setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      setBounds(100, 100, 450, 300);
      contentPane = new JPanel();
      contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
      setContentPane(contentPane);
      contentPane.setLayout(null);
      
      JButton masolgomb = new JButton("Másolás");
      masolgomb.setBounds(164, 89, 89, 23);
      masolgomb.addActionListener(new Masol());
      contentPane.add(masolgomb);
   }
   
   class Masol implements ActionListener
   {
      public void actionPerformed(ActionEvent e)
      {
         mappa = new File("c:\\Users\\user_name\\Desktop\\teszt mappa\\mappa\\");
         fajlok = mappa.listFiles();
         Worksheet sheet2;
         CellRange[] range2 = new CellRange[fajlok.length];
         
         for(int szamlalo = 0; szamlalo < fajlok.length; szamlalo++)
         {
            //Create a Worbook instance
            workbook = new Workbook();
            //Load an Excel file
            workbook.loadFromFile("c:\\Users\\user_name\\Desktop\\teszt mappa\\mappa\\" + fajlok[szamlalo].getName());            
   
            //Get the first worksheet
            Worksheet sheet1 = workbook.getWorksheets().get(0);
            
            sheet2 = workbook.getWorksheets().get(1);
   
            //Specify the source cell range
            CellRange range1 = sheet1.getCellRange(a,b,c,d);
            //Specify the destination cell range
            range2[szamlalo] = sheet2.getCellRange(uja,ujb,ujc,ujd);
            
            //Copy a cell range from sheet 1 to sheet 2
            //sheet1.copy(sheet1.getCellRange("A1:H1"),sheet2.getCellRange("A1:H1"),true);
   
            //Copy the source cell range to the destination cell range
            sheet1.copy(range1, range2[szamlalo]);
            /*
             for (int i = 0; i < 8; i++)
             {
               sheet2.autoFitColumn(i+1);
             }
            */
            //Save the result file
            
            uja += 30;
            ujc += 30;
            System.out.println("Kész");
         }
                        //Save the result file
         workbook.saveToFile("c:\\Users\\user_name\\Desktop\\teszt mappa\\masol_teszt.xlsx", FileFormat.Version2013);
         
      }
   }
}

Vizinyenyec
 
Posts: 15
Joined: Mon May 09, 2022 10:16 am

Wed May 11, 2022 10:47 am

Hi,

Thank you for your inquiry.
Please refer to the code below to resolve your problem.
Code: Select all
 
            mappa = new File("c:\\Users\\user_name\\Desktop\\teszt mappa\\mappa\\");
            fajlok = mappa.listFiles();
            CellRange[] range2 = new CellRange[fajlok.length];
            Workbook workbookAll=new Workbook();
            for(int szamlalo = 0; szamlalo < fajlok.length; szamlalo++)
            {
                //Create a Worbook instance
                workbook = new Workbook();
                //Load an Excel file
                workbook.loadFromFile("c:\\Users\\user_name\\Desktop\\teszt mappa\\mappa\\" + fajlok[szamlalo].getName());
                //Get the first worksheet
                Worksheet sheet1 = workbook.getWorksheets().get(0);
                CellRange range1 = sheet1.getRange().get("A6:T35");
                range2[szamlalo]=range1;
                Worksheet sheetAll=workbookAll.getWorksheets().get(0);
                int m=range2[szamlalo].getRowCount()*szamlalo;
                sheet1.copy(range2[szamlalo], sheetAll.getRange().get(1+m,1));
                workbookAll.saveToFile("masol_teszt.xlsx");
            }


Sincerely,
Kylie
E-iceblue support team
User avatar

kylie.tian
 
Posts: 412
Joined: Mon Mar 07, 2022 2:30 am

Wed May 11, 2022 11:36 am

Hi Kylie,

It's work! Perfect!
Thank you very much!

Vizinyenyec
 
Posts: 15
Joined: Mon May 09, 2022 10:16 am

Thu May 12, 2022 8:00 am

Hi,

You're welcome !
If you need assistance in the future, please don't hesitate to contact us.
Wish you all the best :D

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Mon May 16, 2022 9:36 am

Hi,

Thank you for this Great Support :D

Vizinyenyec

Vizinyenyec
 
Posts: 15
Joined: Mon May 09, 2022 10:16 am

Return to Spire.XLS