Java tutorial
/* * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU 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 Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ /* * ExcelStreamingSpreadSheetWriter.java * Copyright (C) 2013-2017 University of Waikato, Hamilton, New Zealand */ package adams.data.io.output; import adams.core.Constants; import adams.core.ExcelHelper; import adams.core.Utils; import adams.data.io.input.ExcelStreamingSpreadSheetReader; import adams.data.io.input.SpreadSheetReader; import adams.data.spreadsheet.SpreadSheet; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import java.io.OutputStream; import java.util.HashSet; import java.util.logging.Level; /** <!-- globalinfo-start --> * Writes OOXML MS Excel files in streaming mode (more memory efficient). * <br><br> <!-- globalinfo-end --> * <!-- options-start --> * Valid options are: <br><br> * * <pre>-D <int> (property: debugLevel) * The greater the number the more additional info the scheme may output to * the console (0 = off). * default: 0 * minimum: 0 * </pre> * * <pre>-sheet-prefix <java.lang.String> (property: sheetPrefix) * The prefix for sheet names. * default: Sheet * </pre> * * <pre>-missing <java.lang.String> (property: missingValue) * The placeholder for missing values. * default: * </pre> * * <pre>-output-as-displayed (property: outputAsDisplayed) * If enabled, cells are output as displayed, ie, results of formulas instead * of the formulas. * </pre> * * <pre>-max-rows <int> (property: maxRows) * The maximum number of rows to keep in memory. * default: 100 * minimum: 1 * </pre> * <!-- options-end --> * * @author fracpete (fracpete at waikato dot ac dot nz) * @version $Revision$ */ public class ExcelStreamingSpreadSheetWriter extends AbstractMultiSheetSpreadSheetWriterWithMissingValueSupport implements SpreadSheetWriterWithFormulaSupport { /** for serialization. */ private static final long serialVersionUID = -3549185519778801930L; /** the OOXML file extension. */ public static String FILE_EXTENSION = ".xlsx"; /** whether to output the cells as displayed (disable to output formulas). */ protected boolean m_OutputAsDisplayed; /** the number of rows to keep in memory. */ protected int m_MaxRows; /** * Returns a string describing the object. * * @return a description suitable for displaying in the gui */ @Override public String globalInfo() { return "Writes OOXML MS Excel files in streaming mode (more memory efficient)."; } /** * Adds options to the internal list of options. */ @Override public void defineOptions() { super.defineOptions(); m_OptionManager.add("output-as-displayed", "outputAsDisplayed", false); m_OptionManager.add("max-rows", "maxRows", 100, 1, null); } /** * Returns a string describing the format (used in the file chooser). * * @return a description suitable for displaying in the * file chooser */ @Override public String getFormatDescription() { return "MS Excel spreadsheets (large XML)"; } /** * Returns the extension(s) of the format. * * @return the extension (without the dot!) */ @Override public String[] getFormatExtensions() { return new String[] { ".xslx" }; } /** * Returns, if available, the corresponding reader. * * @return the reader, null if none available */ public SpreadSheetReader getCorrespondingReader() { return new ExcelStreamingSpreadSheetReader(); } /** * Returns how the data is written. * * @return the type */ @Override protected OutputType getOutputType() { return OutputType.STREAM; } /** * Sets whether to output the cell content as displayed, ie, no formulas * but the result of formulas. * * @param value true if to output as displayed */ @Override public void setOutputAsDisplayed(boolean value) { m_OutputAsDisplayed = value; reset(); } /** * Returns whether to output the cell content as displayed, ie, no formulas * but the result of formulas. * * @return true if to output as displayed */ @Override public boolean getOutputAsDisplayed() { return m_OutputAsDisplayed; } /** * Returns the tip text for this property. * * @return tip text for this property suitable for * displaying in the explorer/experimenter gui */ @Override public String outputAsDisplayedTipText() { return "If enabled, cells are output as displayed, ie, results of formulas instead of the formulas."; } /** * Sets the maximum number of rows to keep in memory. * * @param value the number of rows */ public void setMaxRows(int value) { if (value > 0) { m_MaxRows = value; reset(); } else { getLogger().severe("At least 1 rows needs to be kept in memory, provided: " + value); } } /** * Returns the maximum number of rows to keep in memory. * * @return the number of rows */ public int getMaxRows() { return m_MaxRows; } /** * Returns the tip text for this property. * * @return tip text for this property suitable for * displaying in the explorer/experimenter gui */ public String maxRowsTipText() { return "The maximum number of rows to keep in memory."; } /** * Performs the actual writing. The caller must ensure that the writer gets * closed. * * @param content the spreadsheet to write * @param out the writer to write the spreadsheet to * @return true if successfully written */ @Override protected boolean doWrite(SpreadSheet[] content, OutputStream out) { boolean result; SXSSFWorkbook workbook; Sheet sheet; Row row; adams.data.spreadsheet.Row spRow; adams.data.spreadsheet.Cell spCell; Cell cell; int i; int n; int count; CellStyle styleDate; CellStyle styleDateTime; CellStyle styleTime; HashSet<String> names; String name; result = true; try { workbook = new SXSSFWorkbook(m_MaxRows); styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT); styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT); styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT); count = 0; names = new HashSet<>(); for (SpreadSheet cont : content) { if (m_Stopped) return false; sheet = workbook.createSheet(); if (cont.getName() != null) { name = cont.getName().replace("'", ""); if (names.contains(name)) name += (count + 1); } else { name = m_SheetPrefix + (count + 1); } names.add(name); workbook.setSheetName(count, name); // header row = sheet.createRow(0); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); cell.setCellValue(cont.getHeaderRow().getCell(i).getContent()); } // data for (n = 0; n < cont.getRowCount(); n++) { if (m_Stopped) return false; row = sheet.createRow(n + 1); spRow = cont.getRow(n); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); spCell = spRow.getCell(i); if ((spCell == null) || spCell.isMissing()) { if (m_MissingValue.length() > 0) cell.setCellValue(m_MissingValue); else cell.setCellType(Cell.CELL_TYPE_BLANK); continue; } if (spCell.isFormula() && !m_OutputAsDisplayed) { cell.setCellFormula(spCell.getFormula().substring(1)); } else { if (spCell.isDate()) { cell.setCellValue(spCell.toDate()); cell.setCellStyle(styleDate); } else if (spCell.isTime()) { cell.setCellValue(spCell.toTime()); cell.setCellStyle(styleTime); } else if (spCell.isDateTime()) { cell.setCellValue(spCell.toDateTime()); cell.setCellStyle(styleDateTime); } else if (spCell.isNumeric()) { cell.setCellValue(Utils.toDouble(spCell.getContent())); } else { cell.setCellValue(spCell.getContent()); } } } } // next sheet count++; } // save workbook.write(out); } catch (Exception e) { result = false; getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e); } return result; } }