Laboratoire ICAR » Plateforme TXM

 org.txm.libs.itext;bundle-version="2.1.5";visibility:=reexport,

 org.txm.libs.msoffice;bundle-version="1.0.0";visibility:=reexport,

 org.txm.libs.office;bundle-version="0.8.2";visibility:=reexport

											- dataBuffer.ColumnStart

											+ columnBuffer.Position > 100000) {

												+ NumberFormat

												.getIntegerInstance()

												.format(

														columnsCount)

												+ " in record " //$NON-NLS-1$

												+ NumberFormat

												.getIntegerInstance()

												.format(

														currentRecord)

												+ ". Set the SafetySwitch property to false" //$NON-NLS-1$

												+ " if you're expecting column lengths greater than 100,000 characters to" //$NON-NLS-1$

												+ " avoid this error."); //$NON-NLS-1$

											- dataBuffer.ColumnStart

											+ columnBuffer.Position > 100000) {

												+ NumberFormat

												.getIntegerInstance()

												.format(

														columnsCount)

												+ " in record " //$NON-NLS-1$

												+ NumberFormat

												.getIntegerInstance()

												.format(

														currentRecord)

												+ ". Set the SafetySwitch property to false" //$NON-NLS-1$

												+ " if you're expecting column lengths greater than 100,000 characters to" //$NON-NLS-1$

												+ " avoid this error."); //$NON-NLS-1$

							- dataBuffer.LineStart - 1);

									- dataBuffer.LineStart - 1);

					- dataBuffer.LineStart);

							- dataBuffer.ColumnStart + 1);

					+ NumberFormat.getIntegerInstance().format(

							currentRecord)

					+ ". Set the SafetySwitch property to false" //$NON-NLS-1$

					+ " if you're expecting more than 100,000 columns per record to" //$NON-NLS-1$

					+ " avoid this error."); //$NON-NLS-1$

			                  * StaticSettings.INITIAL_COLUMN_COUNT];

	protected HashMap<String, String> getRecord() throws IOException {

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

		for (String h : getHeaders()) {

			record.put(h, get(h));

		}

		return record;

	}

package org.txm.utils;

import java.io.File;

import java.io.IOException;

import java.nio.charset.Charset;

import java.util.Arrays;

import java.util.HashMap;

import org.txm.libs.msoffice.ReadExcel;

import org.txm.libs.office.ReadODS;

/**

 * Read a table from a TSV, XLSX or ODS file

 * 

 * @author mdecorde

 *

 */

public class TableReader {

	File tableFile;

	String mode = "tsv";

	HashMap<String, String> record;

	CsvReader tsvReader = null;

	ReadExcel excelReader = null;

	ReadODS odsReader;

	public TableReader(File tableFile) throws Exception {

		this.tableFile = tableFile;

		if (tableFile.getName().toLowerCase().endsWith(".xlsx")) {

			mode = "excel";

			excelReader = new ReadExcel(tableFile, null);

		} else if (tableFile.getName().toLowerCase().endsWith(".ods")) {

			mode = "ods";

			odsReader = new ReadODS(tableFile, null);

		} else if (tableFile.getName().toLowerCase().endsWith(".tsv")) {

			tsvReader = new CsvReader(tableFile.getAbsolutePath(), "\t".charAt(0), Charset.forName("UTF-8"));

			mode = "tsv";

		}

	}

	public String getMode() {

		return mode;

	}

	public boolean readHeaders() throws IOException {

		if (tsvReader != null) {

			return tsvReader.readHeaders();

		} else if (excelReader != null) {

			return excelReader.readHeaders();

		}

		return false;

	}

	/**

	 * read the next record

	 * @return true if there is another record

	 * @throws IOException

	 */

	public boolean readRecord() throws IOException {

		if (tsvReader != null) {

			if (tsvReader.readRecord()) {

				record = tsvReader.getRecord();

				return true;

			} else {

				return false;

			}

		} else if (excelReader != null) {

			if (excelReader.readRecord()) {

				record = excelReader.getRecord();

				return true;

			} else {

				return false;

			}

		}

		return false;

	}

	public HashMap<String, String> getRecord() {

		return record;

	}

	public String[] getHeaders() throws IOException {

		if (tsvReader != null) {

			return tsvReader.getHeaders();

		} else if (excelReader != null) {

			return excelReader.getHeaders();

		}

		return null;

	}

	String EMPTY = "";

	public String get(String h) throws IOException {

		String s = getRecord().get(h);

		if (s == null) return EMPTY;

		return s;

	}

	public void close() throws IOException {

		if (tsvReader != null) {

			tsvReader.close();

		} else if (excelReader != null) {

			excelReader.close();

		}

	}

	public static void main(String[] args) {

		try {

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

			TableReader reader = new TableReader(new File("/home/mdecorde/TEMP/ANTRACT/AF/ANTRACT_AF_Notices_3oct19.xlsx"));

			System.out.println("Table loaded");

			System.out.println("Reading headers...");

			reader.readHeaders();

			System.out.println(Arrays.toString(reader.getHeaders()));

			System.out.println("Reading record...");

			reader.readRecord();

			System.out.println(reader.getRecord());

			System.out.println("Reading record...");

			reader.readRecord();

			System.out.println(reader.getRecord());

		} catch (Exception e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		}

	}

}

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.util.HashMap;

import org.apache.poi.EncryptedDocumentException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.DateUtil;

	Workbook wb = null;

	Sheet ws = null;

	String[] header = null;

	HashMap<String, String> record = null;

	int iRow = 0;

	int nRows;

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

		wb = WorkbookFactory.create(tableFile);

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

			ws = wb.getSheetAt(0);

		} else {

			ws = wb.getSheet(sheetName);

			if (ws == null) {

				ws = wb.getSheetAt(0);

			}

		}

		nRows = ws.getPhysicalNumberOfRows();

	}

	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();

			iRow++; // for next 

			return true;

		} else { // end of file

			record = null;

			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<String, String>();

		Row row = ws.getRow(iRow);

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

			String col = header[colIndex];

			Cell cell = row.getCell(colIndex);

			if (cell != null) {

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

				if (value == null) {

					record.put(header[colIndex], "");

				} else {

					record.put(header[colIndex], value);

				}

			} else {

				record.put(header[colIndex], "");

			}

		}

		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();

	}

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

			if (DateUtil.isCellDateFormatted(cell)) {

                SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");

                return dateFormat.format(cell.getDateCellValue());

            } else {

                Double value = cell.getNumericCellValue();

                return value.toString();

            }

	SpreadsheetDocument spreadsheet = null;

	Table table = null;

	String[] headers;

	public ReadODS(File tableFile, String sheetname) throws Exception {

		spreadsheet = SpreadsheetDocument.loadDocument(tableFile);

		// the first access to the document causes a very long process

		int sheetCount = spreadsheet.getSheetCount();

		if (sheetCount < 1) {

			System.out.println("** ReadODS: no sheet found in file. Aborting.");

			return;

		}

		Table table = null;

		if (sheetname != null) {

			table = spreadsheet.getSheetByName(sheetname);

		}

		if (table == null) {

			table = spreadsheet.getSheetByIndex(0);

		}

		//table.getRowByIndex(0);

	}

	public boolean readHeaders() {

		Row row = table.getRowByIndex(0);

		int ncolumns = row.getCellCount();

		headers = new String[ncolumns];

		for (int j = 0 ; j < ncolumns ; j++) {

			String cell = row.getCellByIndex(j).getDisplayText();

			if (cell == null) {

				headers[j] = "";

			} else {

				headers[j] = cell;

			}

		}

		return headers.length > 0;

	}

	public String[] getHeaders() {

		return headers;

	}

	public boolean readRecord() {

		return false;

	}

	public HashMap<String, String> getRecord() {

		return null;

	}