Java tutorial
/** * Copyright (C) 2007 Asterios Raptis * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package net.sourceforge.jaulp.export.excel.poi; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.net.URISyntaxException; import java.net.URL; import java.util.ArrayList; import java.util.List; import net.sourceforge.jaulp.lang.ClassUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRichTextString; 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 org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Workbook; /** * The Class ExportExcelUtils. */ public final class ExportExcelUtils { /** * 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 */ public static void main(String[] args) throws FileNotFoundException, IOException, URISyntaxException { String filename = null; // String filePath = null; // String relativeFilename = null; File currentPath = new File("."); System.out.println(currentPath.getAbsolutePath()); // filePath = "/jexcelFiles/xls/"; filename = "test.xls"; URL url = ClassUtils.getResource("test.xls"); new File(url.toURI()); // relativeFilename = filePath + filename; File excelSheet = new File(url.toURI()); new File(currentPath, filename); List<String[][]> sheetList = exportWorkbook(excelSheet); System.out.println(sheetList); List<List<List<String>>> excelSheetList = exportWorkbookAsStringList(excelSheet); System.out.println(excelSheetList); saveWorkbook(excelSheet); } /** * Privater Konstruktor damit keine Instanzen erzeugt werden knnen. */ private ExportExcelUtils() { } /** * 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(File excelSheet) throws IOException, FileNotFoundException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); HSSFWorkbook wb = new HSSFWorkbook(fs); int numberOfSheets = wb.getNumberOfSheets(); List<String[][]> sheetList = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); int rows = sheet.getLastRowNum(); int columns = sheet.getRow(0).getLastCellNum(); String[][] excelSheetInTDArray = null; excelSheetInTDArray = new String[rows][columns]; for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); if (null != row) { for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); if (null == cell) { excelSheetInTDArray[i][j] = ""; } else { 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); } 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(File excelSheet) throws IOException, FileNotFoundException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); HSSFWorkbook wb = new HSSFWorkbook(fs); int numberOfSheets = wb.getNumberOfSheets(); List<List<List<String>>> sl = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); int rows = sheet.getLastRowNum(); int columns = sheet.getRow(0).getLastCellNum(); List<List<String>> excelSheetList = new ArrayList<>(); for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); if (null != row) { List<String> reihe = new ArrayList<>(); for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); if (null == cell) { reihe.add(""); } else { 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); } return sl; } /** * 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(File excelSheet) throws IOException, FileNotFoundException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); HSSFWorkbook wb = new HSSFWorkbook(fs); int numberOfSheets = wb.getNumberOfSheets(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); int rows = sheet.getLastRowNum(); int columns = sheet.getRow(0).getLastCellNum(); for (int i = 0; i < rows; i++) { 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; } /** * Save workbook. * * @param excelSheet * the excel sheet * @throws IOException * Signals that an I/O exception has occurred. * @throws FileNotFoundException * the file not found exception */ public static void saveWorkbook(File excelSheet) throws IOException, FileNotFoundException { FileOutputStream out = new FileOutputStream(excelSheet); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); HSSFWorkbook wb = new HSSFWorkbook(fs); int numberOfSheets = wb.getNumberOfSheets(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); int rows = sheet.getLastRowNum(); int columns = sheet.getRow(0).getLastCellNum(); for (int i = 0; i < rows; i++) { 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.setCellValue(new HSSFRichTextString("")); } } } } } // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); out.close(); } /** * Creates a new CellStyle with the given date format. * * @param workbook * the workbook * @param dateFormat * the date format * @return the cell style */ public static CellStyle newDateCellStyle(Workbook workbook, String dateFormat) { CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat(dateFormat)); return dateCellStyle; } /** * Creates a new CellStyle from the given parameters. * * @param workbook * the workbook * @param fontName * the font name * @param boldweight * the boldweight * @param height * the height * @return the cell style */ public static CellStyle newCellStyle(Workbook workbook, String fontName, short boldweight, short height) { CellStyle boldFontCellStyle = workbook.createCellStyle(); boldFontCellStyle.setFont(newFont(workbook, fontName, boldweight, height)); return boldFontCellStyle; } /** * Creates a new font from the given parameters. * * @param workbook * the workbook * @param fontName * the font name * @param boldweight * the boldweight * @param height * the height * @return the font */ public static Font newFont(Workbook workbook, String fontName, short boldweight, short height) { Font font = workbook.createFont(); font.setFontName(fontName); font.setBoldweight(boldweight); font.setFontHeightInPoints(height); return font; } }