Author: echatellier Date: 2016-10-26 13:18:52 +0200 (Wed, 26 Oct 2016) New Revision: 1812 Url: http://forge.nuiton.org/projects/sandbox/repository/revisions/1812 Log: Add xls2CsvStream example Added: xls2csvstream/ xls2csvstream/pom.xml xls2csvstream/src/ xls2csvstream/src/main/ xls2csvstream/src/main/java/ xls2csvstream/src/main/java/Xls2CsvInputStream.java Index: xls2csvstream =================================================================== --- xls2csvstream 2016-02-16 14:55:13 UTC (rev 1811) +++ xls2csvstream 2016-10-26 11:18:52 UTC (rev 1812) Property changes on: xls2csvstream ___________________________________________________________________ Added: svn:ignore ## -0,0 +1 ## +.idea Added: xls2csvstream/pom.xml =================================================================== --- xls2csvstream/pom.xml (rev 0) +++ xls2csvstream/pom.xml 2016-10-26 11:18:52 UTC (rev 1812) @@ -0,0 +1,33 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> + <modelVersion>4.0.0</modelVersion> + + <groupId>org.nuiton</groupId> + <artifactId>xls2csvstream</artifactId> + <version>0-SNAPSHOT</version> + + <properties> + <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> + <maven.compiler.source>1.7</maven.compiler.source> + <maven.compiler.target>1.7</maven.compiler.target> + </properties> + + <dependencies> + <dependency> + <groupId>org.apache.poi</groupId> + <artifactId>poi</artifactId> + <version>3.15</version> + </dependency> + <dependency> + <groupId>org.apache.poi</groupId> + <artifactId>poi-ooxml</artifactId> + <version>3.15</version> + </dependency> + <dependency> + <groupId>net.sf.opencsv</groupId> + <artifactId>opencsv</artifactId> + <version>2.3</version> + </dependency> + </dependencies> +</project> Added: xls2csvstream/src/main/java/Xls2CsvInputStream.java =================================================================== --- xls2csvstream/src/main/java/Xls2CsvInputStream.java (rev 0) +++ xls2csvstream/src/main/java/Xls2CsvInputStream.java 2016-10-26 11:18:52 UTC (rev 1812) @@ -0,0 +1,108 @@ +/* + * #%L + * ToPIA :: Service Security + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2004 - 2014 CodeLutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Lesser General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Lesser Public License for more details. + * + * You should have received a copy of the GNU General Lesser Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/lgpl-3.0.html>. + * #L% + */ + +import au.com.bytecode.opencsv.CSVWriter; +import org.apache.poi.openxml4j.exceptions.InvalidFormatException; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.usermodel.WorkbookFactory; +import org.apache.poi.util.IOUtils; + +import java.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.IOException; +import java.io.InputStream; +import java.io.OutputStreamWriter; +import java.util.Arrays; + +/** + * Input stream qui convertit à la volée des fichiers xls en fichier csv. + */ +public class Xls2CsvInputStream extends InputStream { + + protected InputStream xlsStream; + protected InputStream csvStream; + + public Xls2CsvInputStream(InputStream xlsStream) throws IOException { + this.xlsStream = xlsStream; + try { + csvStream = convert(xlsStream); + xlsStream.close(); + } catch (InvalidFormatException e) { + throw new IOException(e); + } + } + + protected InputStream convert(InputStream xlsStream) throws IOException, InvalidFormatException { + Workbook wb = WorkbookFactory.create(xlsStream); + + ByteArrayOutputStream outStream = new ByteArrayOutputStream(); + CSVWriter writer = new CSVWriter(new OutputStreamWriter(outStream, "UTF-8"), ';'); + Sheet sheet = wb.getSheetAt(0); + + // pour la gestion des lignes de taille differente dans excel (si données absentes) + int columnCount = sheet.getRow(0).getLastCellNum(); + for (int i = 0; i <= sheet.getLastRowNum(); i++) { + Row row = sheet.getRow(i); + String[] rowData = new String[columnCount]; + Arrays.fill(rowData, ""); + boolean allEmpty = true; + for (int j = 0; j < row.getLastCellNum() && j < columnCount; j++) { + Cell cell = row.getCell(j); + if (cell != null) { + cell.setCellType(Cell.CELL_TYPE_STRING); + rowData[j] = cell.getStringCellValue(); + if (allEmpty) { + allEmpty = rowData[j] == null || rowData[j].trim().length() == 0; // isBlank + } + } + } + // pour la gestion des lignes vides en fin de fichier excel + if (!allEmpty) { + writer.writeNext(rowData); + } + } + writer.close(); + + return new ByteArrayInputStream(outStream.toByteArray()); + } + + @Override + public int read() throws IOException { + return this.csvStream.read(); + } + + @Override + public void close() throws IOException { + IOUtils.closeQuietly(xlsStream); + if (xlsStream != null) { + xlsStream.close(); + } + if (csvStream != null) { + csvStream.close(); + } + } +}