How to read an Excel 2013 worksheet and write to a PostgreSQL database?

Question

How to read an Excel 2013 worksheet and write to a PostgreSQL database?

Navigation

#1 by (1 votes)

1

I'm having trouble writing information to the database.

This information I extract from an Excel file.

For example, I have 3 fields and I do not know how to get the 1st cell and save it to the bank, then get the 2nd and save and finally get the 3rd and save.

Remembering that there is a lot of information that each column has.

My code looks like this:

public void readingSheet(String path) {

    try {  

       File file = new File(path);  

       String name = file.toString();  

       int pos = name.lastIndexOf('.');  

       String ext = name.substring(pos + 1);  

       FileInputStream fileIn = new FileInputStream(file);  

       Workbook obj = null;  

       if (ext.equals("xlsx")) {

           try {  
               //Metodo aceita o path do arquivo  
               obj = new XSSFWorkbook(fileIn);  
           } 

           catch (IOException ex) {  
               throw new RuntimeException(ex);  
           }  
       } 

       else if (ext.equals("xls")) {  

           try {  
               //Metodo nao aceita string do path do arquivo  
               obj = new HSSFWorkbook(fileIn);  
           } 
           catch (IOException e) {  
               throw new RuntimeException(e);  
           }  
       }  

       else {  
           throw new IllegalArgumentException("Arquivo recebido não é uma extensão do Excel");  
       }  

       int o = 0;  

       Sheet worksheet = obj.getSheet("Plan1");  

       Row row;  

       Cell cell;  

       for(int i = 1; i<= worksheet.getLastRowNum(); i++){  

           row = worksheet.getRow(i);  

           String linha = "";  

           for(int j = 0; j < 3; j++) {  
               cell = row.getCell(j);  

               if(cell.getCellType()==1)   {
                   linha += " | " + cell.getStringCellValue();
               }

               else {  
                   double aux = 0;  
                   int aux2 = 0;  
                   aux = cell.getNumericCellValue();  
                   aux2 = (int) aux;  
                   linha += " | " + aux2;
              }  
          }  
           System.out.println("");
           System.out.println("Linha: " + linha);  
           System.out.println("");
       }    

   } 

    catch (FileNotFoundException e) {  
      e.printStackTrace();
   }  

}

Thank you all.

java

asked by anonymous 17.04.2015 / 21:49

1 answer

Save value geojson Condition within foreach before saving template

score 1 · Answer 1

I recommend using the jxl library to manipulate excel spreadsheets. Just did not test with excel 2013. Here's an example of manipulation

The sample code is this

import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

public void readingSheet(String caminho_arquivo_xls) {
    //objeto relativo ao arquivo excel
    Workbook workbook = null;

    try {
        //Carrega planilha
        WorkbookSettings config = new WorkbookSettings();
        config.setEncoding("Cp1252");//configura acentuação
        workbook = Workbook.getWorkbook(new File(caminho_arquivo_xls),config);//recupera arquivo desejado
        //recupera pagina/planilha/aba do arquivo
        Sheet sheet = workbook.getSheet(0);
        //recupera numero de linhas
        int linhas = sheet.getRows();
        //percorre todas as linhas da planilha
        for (int row = 0; row < linhas; row++) {
            //cria meu objeto que recebe as linhas
            MeuObjeto objeto = new MeuObjeto();
            //verifica se coluna 0 (A) e linha row não é vazia
            if(!sheet.getCell(0,row).getContents().isEmpty()){
                //recupera informação da coluna A linha row.
                objeto.setDadoA(sheet.getCell(0, row).getContents().toString());
            }
            if(!sheet.getCell(1,row).getContents().isEmpty()){
                //recupera informação da coluna B linha row.
                objeto.setDadoB(sheet.getCell(1, row).getContents().toString());
            }
            //chama meu objeto que faz o serviço de salvar no banco de dados
            bancodados.salvarDado(objeto);
        }

    } catch (IOException e) {
        print_erro(e);
    } catch (BiffException e) {
        print_erro(e);
    } catch (NumberFormatException e) {
        print_erro(e);
    } catch(Exception e){
        print_erro(e);
    } finally {
        //fechar
        if (workbook != null)
            workbook.close();
    }
}