Extract data from excel to mysql in java

Question

I want to extract data from excel to mysql but at the beginning i want just to read the file so i use the code below :

@RestController
public class ExtractDataController {

     protected String[] celluls;

    @RequestMapping(value = "/readExcel", method = RequestMethod.GET)
    public String processExcel()        
    {

        try {

            InputStream ExcelFileToRead = new FileInputStream("Test.xlsx");
            //définir l'objet workbook
            XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);  
            //Itérer sur le nombre de sheets
            for(int i=0;i<wb.getNumberOfSheets();i++)
            {
            XSSFSheet sheet = wb.getSheetAt(i);
            //Itérer sur les lignes
            for(int k=0;k<sheet.getLastRowNum();k++)
            {
            XSSFRow ligne = sheet.getRow(k);
            celluls = new String[ligne.getLastCellNum()];
            for(int j=0;j<ligne.getLastCellNum();j++){
                if(ligne.getCell(j).getCellType() == 0)

                    celluls[j] = ""+ligne.getCell(j).getNumericCellValue();
                else if(ligne.getCell(j).getStringCellValue().equals("/0"))
                    celluls[j] = ""+0;
                else if(!ligne.getCell(j).getStringCellValue().equals("NIL") && !ligne.getCell(j).getStringCellValue().equals("NULL") && !ligne.getCell(j).getStringCellValue().equals("/0"))
                    {celluls[j] = ligne.getCell(j).getStringCellValue();
                    System.out.println(celluls[j]);
                    }
                else celluls[j] = null;
                System.out.println(celluls[j]);
            }

            }

        }} catch (Exception e) {
            System.err.println("Erreur");
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "data extracted successfully";
    }}

Now , the file Test.xsls has just 2 rows and 1 sheet just to test the code but when i run the application i have the first row duplicated and it doesn't read the seconde one .

Here's the data of my file

Country cc  ndc RS
France  36  123 roaming international
Maroc   12  145 roaming international

And here's what i get

Country
Country
cc
cc
ndc
ndc
RS
RS
France
France
36.0
123.0
roaming international
roaming international
Country
Country
cc
cc
ndc
ndc
RS
RS
France
France
36.0
123.0
roaming international
roaming international

Any ideas please?

Why do it in Java and why all from scratch? There is an Excel version of Mysql or you can connect to MySQL using ODBC. — Stefan
– Stefan, Commented Jun 16, 2015 at 11:28
You get duplicate values because you have two System.out.println? — m4ktub
– m4ktub, Commented Jun 16, 2015 at 11:32
Stefan i have to do it in java because in my project oi have an interface with button (extract data) and i have to call this method when clicking on the button. — Hajar Ch
– Hajar Ch, Commented Jun 16, 2015 at 11:33
M4Ktub, it's just an error , no even if i delete the second system.out.println , it doesn't read the second row and the first is duplicated — Hajar Ch
– Hajar Ch, Commented Jun 16, 2015 at 11:36

ultimate · Accepted Answer · 2015-06-16 11:55:05Z

1

I assume it's the usage of < sheet.getLastRowNum() for your first for loop:

getLastRowNum() --> last row contained n this sheet (0-based)

(see https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#getLastRowNum())

Hence this is NOT the number of rows, but the index of the last row!

So, if you have 2 rows, this will return 1, since this is the last row containing data. Hence your for-loop has to go up to <= sheet.getLastRowNum()

answered Jun 16, 2015 at 11:55

ultimate

7435 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract data from excel to mysql in java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related