Java tutorial
/** * The MIT License * * Copyright (C) 2007 Asterios Raptis * * Permission is hereby granted, free of charge, to any person obtaining * a copy of this software and associated documentation files (the * "Software"), to deal in the Software without restriction, including * without limitation the rights to use, copy, modify, merge, publish, * distribute, sublicense, and/or sell copies of the Software, and to * permit persons to whom the Software is furnished to do so, subject to * the following conditions: * * The above copyright notice and this permission notice shall be * included in all copies or substantial portions of the Software. * * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE * LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION * OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION * WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. */ package de.alpharogroup.export.excel.poi; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.net.URISyntaxException; import java.net.URL; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import de.alpharogroup.lang.ClassExtensions; /** * The Class ExportExcelExtensions. */ public final class ExportExcelExtensions { /** * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils * ein sheet in der excel-Datei. * * @param excelSheet * Die excel-Datei. * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei * zurck. * @throws IOException * Fals ein Fehler beim Lesen aufgetreten ist. * @throws FileNotFoundException * Fals die excel-Datei nicht gefunden wurde. */ public static List<String[][]> exportWorkbook(final File excelSheet) throws IOException, FileNotFoundException { final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); final List<String[][]> sheetList = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); final int rows = sheet.getLastRowNum(); final int columns = sheet.getRow(0).getLastCellNum(); String[][] excelSheetInTDArray = null; excelSheetInTDArray = new String[rows + 1][columns]; for (int i = 0; i <= rows; i++) { final HSSFRow row = sheet.getRow(i); if (null != row) { for (int j = 0; j < columns; j++) { final HSSFCell cell = row.getCell(j); if (null == cell) { excelSheetInTDArray[i][j] = ""; } else { final int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { excelSheetInTDArray[i][j] = ""; } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue()); } else if (cellType == Cell.CELL_TYPE_ERROR) { excelSheetInTDArray[i][j] = ""; } else if (cellType == Cell.CELL_TYPE_FORMULA) { excelSheetInTDArray[i][j] = cell.getCellFormula(); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue()); } else if (cellType == Cell.CELL_TYPE_STRING) { excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString(); } } } } } sheetList.add(excelSheetInTDArray); } wb.close(); return sheetList; } /** * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und * Listen von den Zeilen der sheets von der excel-Datei. * * @param excelSheet * Die excel-Datei. * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit * den sheets beinhalten weitere Listen mit String die jeweils eine Zeile * reprsentieren. * @throws IOException * Fals ein Fehler beim Lesen aufgetreten ist. * @throws FileNotFoundException * Fals die excel-Datei nicht gefunden wurde. */ public static List<List<List<String>>> exportWorkbookAsStringList(final File excelSheet) throws IOException, FileNotFoundException { final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); final List<List<List<String>>> sl = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); final int rows = sheet.getLastRowNum(); final int columns = sheet.getRow(0).getLastCellNum(); final List<List<String>> excelSheetList = new ArrayList<>(); for (int i = 0; i <= rows; i++) { final HSSFRow row = sheet.getRow(i); if (null != row) { final List<String> reihe = new ArrayList<>(); for (int j = 0; j < columns; j++) { final HSSFCell cell = row.getCell(j); if (null == cell) { reihe.add(""); } else { final int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { reihe.add(""); } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { reihe.add(Boolean.toString(cell.getBooleanCellValue())); } else if (cellType == Cell.CELL_TYPE_ERROR) { reihe.add(""); } else if (cellType == Cell.CELL_TYPE_FORMULA) { reihe.add(cell.getCellFormula()); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { reihe.add(Double.toString(cell.getNumericCellValue())); } else if (cellType == Cell.CELL_TYPE_STRING) { reihe.add(cell.getRichStringCellValue().getString()); } } } excelSheetList.add(reihe); } } sl.add(excelSheetList); } wb.close(); return sl; } /** * The main method. * * @param args * the arguments * @throws FileNotFoundException * the file not found exception * @throws IOException * Signals that an I/O exception has occurred. * @throws URISyntaxException * is thrown if a string could not be parsed as a URI reference. */ public static void main(final String[] args) throws FileNotFoundException, IOException, URISyntaxException { final String filename = "test.xls"; final URL url = ClassExtensions.getResource(filename); final File excelSheet = new File(url.toURI()); System.out.println(excelSheet.exists()); final List<String[][]> sheetList = exportWorkbook(excelSheet); System.out.println(sheetList); final List<List<List<String>>> excelSheetList = exportWorkbookAsStringList(excelSheet); System.out.println(excelSheetList); } /** * Replace null cells into empty cells. * * @param excelSheet * the excel sheet * @return the HSSF workbook * @throws IOException * Signals that an I/O exception has occurred. * @throws FileNotFoundException * the file not found exception */ public static HSSFWorkbook replaceNullCellsIntoEmptyCells(final File excelSheet) throws IOException, FileNotFoundException { final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); final int rows = sheet.getLastRowNum(); final int columns = sheet.getRow(0).getLastCellNum(); for (int i = 0; i <= rows; i++) { final HSSFRow row = sheet.getRow(i); if (null != row) { for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); if (cell == null) { cell = row.createCell(j, Cell.CELL_TYPE_BLANK); } } } } } return wb; } /** * Privater Konstruktor damit keine Instanzen erzeugt werden knnen. */ private ExportExcelExtensions() { } }