Laboratoire ICAR » Plateforme TXM

//

package org.txm.searchengine.cqp.corpus;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

import org.txm.core.preferences.TXMPreferences;

import org.txm.searchengine.cqp.AbstractCqiClient;

import org.txm.searchengine.cqp.clientExceptions.CqiClientException;

import org.txm.searchengine.cqp.corpus.query.CQLQuery;

import org.txm.utils.logger.Log;

/**

		return null;

	}

}

		return CorpusManager.getCorpusManager().getCqiClient().cpos2Id(this.getQualifiedName(), positions);

	}

	public String[] cpos2Str(int[] positions) throws UnexpectedAnswerException, IOException, CqiServerError {

		return CorpusManager.getCorpusManager().getCqiClient().cpos2Str(this.getQualifiedName(), positions);

	}

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.HashMap;

import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {

	Workbook wb = null;

	Sheet ws = null;

	String[] header = null;

	HashMap<String, String> record = null;

	int iRow = 0;

	int nRows;

	private File tableFile;

	public ReadExcel(File tableFile, String sheetName) throws EncryptedDocumentException, InvalidFormatException, IOException {

		this.tableFile = tableFile;

		if (tableFile.exists() && tableFile.length() > 0) {

			wb = WorkbookFactory.create(tableFile);

			if (sheetName == null || sheetName.length() == 0) {

					ws = wb.getSheetAt(0);

				}

			}

			nRows = ws.getPhysicalNumberOfRows();

		}

		else {

			ws = wb.createSheet();

		}

	}

	/**

	 * one rule (regex test) per column

	 * all lines are processed

		Row headers = ws.getRow(0);

		if (headers == null) return false;

		int colMax = headers.getLastCellNum();

		ArrayList<Integer> columnIdxToTest = new ArrayList<>();

		ArrayList<String> columnsTest = new ArrayList<>();

		for (int colIndex = 0; colIndex < colMax; colIndex++) {

				}

			}

		}

		if (columnIdxToTest.size() == 0) {

			System.out.println("Error: no column found with name=" + rules.keySet());

			return false;

		}

		ArrayList<Integer> removed = new ArrayList<>();

		for (int rowIndex = 0; rowIndex < nRows; rowIndex++) {

			Row row = ws.getRow(rowIndex);

			if (row == null) continue;

			for (int i = 0; i < columnIdxToTest.size(); i++) {

				int colIndex = columnIdxToTest.get(i);

				Cell cell = row.getCell(colIndex);

				if (cell != null) {

					String value = cellToString(cell).trim();

				}

			}

		}

		// TODO very slow when there is a lot of lines

		for (int rowIndex : removed) {

			ws.shiftRows(rowIndex + 1, nRows, -1);

			nRows--;

		}

		return true;

	}

	/**

	 * search and replace lines utils

	 * 

		if (!extractTo(ws2, lineRules, columnsSelection)) {

			return false;

		}

		return true;

	}

	/**

	 * extract lines and columns following rules.

	 * 

	public boolean copyColumns(HashMap<String, String[]> columnsToCopy) throws EncryptedDocumentException, InvalidFormatException, IOException {

		Row headers = ws.getRow(0);

		if (headers == null) return false;

		int colMax = headers.getLastCellNum();

		int nRows = ws.getPhysicalNumberOfRows();

		HashMap<String, Integer> searchColumns = new HashMap<>();

		for (int colIndex = 0; colIndex < colMax; colIndex++) {

			Cell cell = headers.getCell(colIndex);

				}

			}

		}

		if (searchColumns.keySet().size() != columnsToCopy.keySet().size()) {

			System.out.println("Error: not all columns found=" + searchColumns.keySet() + " of " + columnsToCopy.keySet());

			ArrayList<String> notFound = new ArrayList<>(columnsToCopy.keySet());

			System.out.println("NOT FOUND: " + notFound);

			return false;

		}

		System.out.println("N Rows to update: " + nRows);

		int nRowWritten = 0;

		for (int rowIndex = 0; rowIndex < nRows; rowIndex++) { // update all rows, starting from the second row (first row is the header)

			Row row = ws.getRow(rowIndex);

			if (row == null) continue;

			int icol = 0;

			for (String col : columnsToCopy.keySet()) {

				for (String newcol : columnsToCopy.get(col)) {

					int colIndex = searchColumns.get(col);

					Cell cell = row.getCell(colIndex);

					if (cell != null) {

						Cell newCell = row.createCell(colMax + icol);

						if (rowIndex == 0) {

							newCell.setCellValue(newcol); // first column must be renamed

					icol++;

				}

			}

			nRowWritten++;

		}

		System.out.println("" + nRowWritten + " rows updated.");

		return nRowWritten > 0;

	}

	/**

	 * extract lines and columns following rules.

	 * 

	public boolean searchAndReplaceInLines(HashMap<String, String[]> searchAndReplaceRules) throws EncryptedDocumentException, InvalidFormatException, IOException {

		Row headers = ws.getRow(0);

		if (headers == null) return false;

		int colMax = headers.getLastCellNum();

		int nRows = ws.getPhysicalNumberOfRows();

		HashMap<String, Integer> searchColumns = new HashMap<>();

		for (int colIndex = 0; colIndex < colMax; colIndex++) {

			Cell cell = headers.getCell(colIndex);

				}

			}

		}

		if (searchColumns.keySet().size() != searchAndReplaceRules.keySet().size()) {

			System.out.println("Error: not all columns found=" + searchColumns.keySet() + " of " + searchAndReplaceRules.keySet());

			ArrayList<String> notFound = new ArrayList<>(searchAndReplaceRules.keySet());

			System.out.println("NOT FOUND: " + notFound);

			return false;

		}

		System.out.println("N Rows to update: " + nRows);

		int nRowWritten = 0;

		for (int rowIndex = 0; rowIndex < nRows; rowIndex++) { // update all rows, starting from the second row (first row is the header)

			Row row = ws.getRow(rowIndex);

			if (row == null) continue;

			boolean change = false;

			for (String col : searchAndReplaceRules.keySet()) {

				int colIndex = searchColumns.get(col);

				Cell cell = row.getCell(colIndex);

				if (cell != null) {

					String value = cellToString(cell).trim();

					}

				}

			}

			if (change) {

				nRowWritten++;

			}

		}

		System.out.println("" + nRowWritten + " rows updated.");

		return true;

	}

	/**

	 * one rule (regex test) per column

	 * all lines are processed

	public boolean extractTo(Sheet ws2, HashMap<String, String> lineRules, List<String> columnsSelection) throws EncryptedDocumentException, InvalidFormatException, IOException {

		Row headers = ws.getRow(0);

		if (headers == null) return false;

		int colMax = headers.getLastCellNum();

		ArrayList<Integer> columnIdxToTest = new ArrayList<>();

		ArrayList<Integer> columnIdxToWrite = new ArrayList<>();

		ArrayList<String> columns = new ArrayList<>();

					columnIdxToTest.add(colIndex);

					columnsTest.add(lineRules.get(value));

				}

				if (columnsSelection.contains(value)) {

					columnIdxToWrite.add(colIndex);

				}

			}

		}

		if (columnIdxToTest.size() == 0) {

			System.out.println("Error: no column found with name=" + lineRules.keySet());

			return false;

		}

		if (columnIdxToWrite.size() != columnsSelection.size()) {

			System.out.println("Error: not all columns found=" + columnIdxToWrite + " of " + columnsSelection);

			ArrayList<String> notFound = new ArrayList<>(columnsSelection);

			System.out.println("NOT FOUND: " + notFound);

			return false;

		}

		Row row2 = ws2.createRow(0);

		int nCell = 0;

		for (int iCol : columnIdxToWrite) {

			cell2.setCellValue(cellToString(headers.getCell(iCol)).trim());

			nCell++;

		}

		int nRowWritten = 1;

		for (int rowIndex = 0; rowIndex < nRows; rowIndex++) {

			Row row = ws.getRow(rowIndex);

			if (row == null) continue;

			for (int i = 0; i < columnIdxToTest.size(); i++) {

				int colIndex = columnIdxToTest.get(i);

				Cell cell = row.getCell(colIndex);

				if (cell != null) {

					String value = cellToString(cell).trim();

					if (value.matches(columnsTest.get(i))) {

						// write new line in Sheet ws2

						row2 = ws2.createRow(nRowWritten);

						nCell = 0;

							nCell++;

						}

						nRowWritten++;

						break; // next row

					}

				}

			}

		}

		return true;

	}

	/**

	 * 

	 * @param rules String:old column name -> String:new column name

	private boolean renameColumns(HashMap<String, String> rules) {

		Row headers = ws.getRow(0);

		if (headers == null) return false;

		int colMax = headers.getLastCellNum();

		for (int colIndex = 0; colIndex < colMax; colIndex++) {

			Cell cell = headers.getCell(colIndex);

		}

		return true;

	}

	public boolean save() throws IOException {

		return saveAs(tableFile);

	}

	public boolean saveAs(File newTableFile) throws IOException {

		BufferedOutputStream writer = new BufferedOutputStream(new FileOutputStream(newTableFile, false));

		wb.write(writer);

		writer.close();

		return true;

	}

	public boolean readHeaders() {

		if (nRows == 0) {

			header = new String[0];

			return header != null;

		}

		Row firstRow = ws.getRow(0);

		int colMax = firstRow.getLastCellNum();

		header = new String[colMax];

		for (int it = 0; it < colMax; it++) {

			header[it] = firstRow.getCell(it).getStringCellValue();

		}

		if (iRow == 0) iRow = 1; // skip first line

		return true;

	}

	public boolean readRecord() {

		if (iRow < nRows) {

			_getRecord();

			return false;

		}

	}

	public String[] getHeaders() {

		return header;

	}

	public HashMap<String, String> getRecord() {

		return record;

	}

	public String get(String h) {

		if (record == null) return null;

		return record.get(h);

	}

	protected void _getRecord() {

		record = new HashMap<>();

		Row row = ws.getRow(iRow);

		for (int colIndex = 0; colIndex < header.length; colIndex++) {

			String col = header[colIndex];

			Cell cell = row.getCell(colIndex);

				record.put(col, "");

			}

		}

		for (String k : record.keySet())

			if (record.get(k) == null) System.out.println("ERROR null value with " + k);

	}

	public void close() throws IOException {

		if (wb != null) wb.close();

	}

	/**

	 * 

	 * @param inputFile

	 * @return list of lines (line = list of cells) with values converted to String - the file is completely read

	 */

	public static ArrayList<ArrayList<String>> toTable(File inputFile, String sheetName) {

		ArrayList<ArrayList<String>> data = new ArrayList<>();

		if (!inputFile.canRead()) {

			System.out.println("** Excel2XML: '" + inputFile.getName() + "' file not readable. Aborting.");

			return data;

		}

		try {

			Workbook wb = WorkbookFactory.create(inputFile);

			Sheet ws;

					ws = wb.getSheetAt(0);

				}

			}

			if (ws == null) {

				System.out.println("** Excel2XML: no sheet found. Aborting.");

				return data;

			}

			int nRows = ws.getPhysicalNumberOfRows();

			if (nRows == 0) return null;

			Row firstRow = ws.getRow(0);

			int colMax = firstRow.getLastCellNum();

			ArrayList<String> headers = new ArrayList<>();

			for (int it = 0; it < colMax; it++) {

				headers.add(firstRow.getCell(it).getStringCellValue());

			}

			for (int rowIndex = 0; rowIndex < nRows; rowIndex++) {

				Row row = ws.getRow(rowIndex);

				ArrayList<String> dataLine = new ArrayList<>();

			e.printStackTrace();

			return null;

		}

		return data;

	}

	/**

	 * 

	 * @param cell. If null returns ""

	 */

	public static String cellToString(Cell cell) {

		if (cell == null) return "";

		switch (cell.getCellTypeEnum()) {

		}

	}

	public static void main(String[] args) throws Exception {

		File tableFile = new File("/home/mdecorde/Téléchargements/Requêtes_SVO-SOV.xlsx");

		// ReadExcel excel = new ReadExcel(tableFile, null);

			System.out.println(line);

		}

	}

	public static void mainAF(String[] args) throws Exception {

		// ArrayList<ArrayList<String>> data = toTable(new File("/home/mdecorde/xml/ruscorpora1m-test/metadata.xlsx"), null);

		// if (data.size() == 0) {

		// } else {

		// System.out.println(data);

		// }

		File tableFile = new File("/home/mdecorde/TEMP/ANTRACT/AF/all.xlsx");

		File table2File = null;

		System.out.println("open...");

		ReadExcel excel = new ReadExcel(tableFile, null);

		HashMap<String, String> lineRules = new HashMap<>(); // line tests to select line to keep

		List<String> columnsSelection; // list of columns to keep

		HashMap<String, String> columnsToCopy = new HashMap<>();

		// columnsToCopy.put("Titre propre", "title"); // not working yet

		// columnsToCopy.put("Date de diffusion", "textorder"); // not working yet

		// searchAndReplaceRules.put("textorder", new String[] {"../../....", "$3$2$1"}); // not working yet

		// sujets

		table2File = new File("/home/mdecorde/TEMP/ANTRACT/AF/sujets.xlsx");

		columnsSelection = Arrays.asList(

				"Nom fichier segmenté (info)", "antract_video", "antract_debut", "antract_fin", "antract_duree", "antract_tc_type", "antract_tc_date",

				"Résumé", "Séquences", "Descripteurs (Aff. Lig.)", "Générique (Aff. Lig.)");

		lineRules.put("Type de notice", "Notice sujet");

		table2File.delete();

		ReadExcel excel2 = new ReadExcel(table2File, null);

		if (!excel.extractTo(excel2, lineRules, columnsSelection)) {

			System.out.println("FAIL");

			return;

		}

		System.out.println("copying column: " + columnsToCopy.size());

		// excel2.copyColumns(columnsToCopy);

		System.out.println("search&replace column: " + searchAndReplaceRules.size());

		// excel2.searchAndReplaceInLines(searchAndReplaceRules);

		System.out.println("renaming column: " + columnsToRenameRules.size());

		excel2.renameColumns(columnsToRenameRules);

		System.out.println("saving&closing...");

		excel2.save();

		excel2.close();