Forums PostgreSQL.fr

import java.io.File;
import java.io.FileInputStream;
import java.text.NumberFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.opensymphony.xwork2.ActionSupport;

import trigo.Util.HibernateUtil;
import trigo.beans.TestTempLigneExtract;

@SuppressWarnings("serial")
public class OpenWorkBook extends ActionSupport{

	static XSSFRow row;
	Logger log = LogManager.getLogger(OpenWorkBook.class);
	private TestTempLigneExtract tmp = new TestTempLigneExtract();
	
	@Override
	public String execute() throws Exception {
		
		File file = new File("cheminFichierExcel.xlsx");
		FileInputStream fis = new FileInputStream(file);

		
		if(file.isFile() && file.exists()) {
			System.out.println("Fichier Excel ouvert");
		}else {
			System.out.println("Erreur sur l'ouverture du fichier Excel");
		}
		
		XSSFWorkbook wb = new XSSFWorkbook(fis);
		XSSFSheet sheet = wb.getSheetAt(0);
		Iterator<Row> rowIterator = sheet.iterator();
		int i = 0;
		while(rowIterator.hasNext()) {
			Session s = HibernateUtil.getSession();
			Transaction tx = s.beginTransaction();
			row = (XSSFRow)rowIterator.next();
			Iterator<Cell> cellIterator = row.cellIterator();
			tmp = new TestTempLigneExtract();
			i++;
			System.out.println(row.getRowNum());
			while(cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
			
				switch (cell.getColumnIndex()) {
					case 0:
						String avis;
						if(cell.getStringCellValue().isEmpty()) {
							avis = "";
						}else {
							avis = cell.getStringCellValue();
						}
						tmp.setAvis(avis);
						break;
					case 1:
						String art;
						if(cell.getStringCellValue().isEmpty()) {
							art = "";
						}else {
							art = cell.getStringCellValue();
						}
						tmp.setArticle(art);
						break;
					case 2:
						String des;
						if(cell.getStringCellValue().isEmpty()) {
							des = "";
						}else {
							des = cell.getStringCellValue();
						}
						tmp.setDesignationArticle(des);
						break;
					case 3:
						String sn;
						if(cell.getStringCellValue().isEmpty()) {
							sn = "";
						}else {
							sn = cell.getStringCellValue();
						}
						tmp.setSn(sn);
						break;
					case 4:
						String four;
						if(cell.getStringCellValue().isEmpty()) {
							four = "";
						}else {
							four = cell.getStringCellValue();
						}
						tmp.setFournisseur(four);
						break;
					case 5:
						String grp;
						if(cell.getStringCellValue().isEmpty()) {
							grp = "";
						}else {
							grp = cell.getStringCellValue();
						}
						tmp.setGroupeDeCode(grp);
						break;
					case 6:
						String descrip;
						if(cell.getStringCellValue().isEmpty()) {
							descrip = "";
						}else {
							descrip = cell.getStringCellValue();
						}
						tmp.setDescription(descrip);
						break;
					case 7:
						String statut;
						if(cell.getStringCellValue().isEmpty()) {
							statut = "";
						}else {
							statut = cell.getStringCellValue();
						}
						tmp.setStatut(statut);
						break;
					case 8:
						Date jour = null;
						if(cell.getDateCellValue() == null) {
							jour = null;
						}else {
							jour = cell.getDateCellValue();
						}
						tmp.setJourCreation(jour);
						break;
					case 9:
						String heure;
						if(cell.getStringCellValue().isEmpty()) {
							heure = "";
						}else {
							heure = cell.getStringCellValue();
						}
						tmp.setHeureCreation(heure);
						break;
					case 10:
						String gest;
						if(cell.getStringCellValue().isEmpty()) {
							gest = "";
						}else {
							gest = cell.getStringCellValue();
						}
						tmp.setGestionnaire(gest);
						break;
					case 11:
						String value;
						if(cell.getStringCellValue() == null) {
							value = "0.0";
						}else {
							value = cell.getStringCellValue();
						}
						NumberFormat nf = NumberFormat.getInstance();
						double valeur = nf.parse(value.replace(".", "")).doubleValue(); // il y a des . comme separateur des milliers dans L'Excel
						tmp.setValeur(valeur);
						break;
					case 12:
						String devise;
						if(cell.getStringCellValue().isEmpty()) {
							devise = "";
						}else {
							devise = cell.getStringCellValue();
						}
						tmp.setDevise(devise);
						break;
					case 13:
						if (cell.getNumericCellValue() == 0.0){
							tmp.setRic(false);
						}else if (cell.getNumericCellValue() == 1.0) {
							tmp.setRic(true);
						}else {
							tmp.setRic(null);
						}
						break;
					default:
						break;
				} // switch Index
			} // while Cell

			s.save(tmp);
			tx.commit();
			s.clear();
			s.close();
			System.out.println();
		
		} // while Row

		return SUCCESS;
	}
	
}

not-null="false"