org.apache.poi.xssf.streaming.SXSSFWorkbook Java Examples

@Test
public void testread() throws IOException {
    String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";

    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    Sheet xssfSheet = xssfWorkbook.getXSSFWorkbook().getSheetAt(0);
    //
    // Cell cell = xssfSheet.getRow(0).createCell(9);

    String file1 = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";

    SXSSFWorkbook xssfWorkbook1 = new SXSSFWorkbook(new XSSFWorkbook(file1));
    Sheet xssfSheet1 = xssfWorkbook1.getXSSFWorkbook().getSheetAt(0);

    // Cell cell1 = xssfSheet1.getRow(0).createCell(9);

    xssfWorkbook.close();
    xssfWorkbook1.close();
}
 

/**
 * 导出-导入模板
 *
 * @param data
 * @throws Exception
 */
public void exportTemplate() {
    SXSSFWorkbook sxssfWorkbook = null;
    try {
        try {
            verifyResponse();
            verifyParams();
            ExcelEntity excelMapping = ExcelMappingFactory.loadExportExcelClass(excelClass, fileName);
            ExcelWriter excelWriter = new ExcelWriter(excelMapping, pageSize, rowAccessWindowSize, recordCountPerSheet, openAutoColumWidth);
            sxssfWorkbook = excelWriter.generateTemplateWorkbook();
            download(sxssfWorkbook, httpServletResponse, URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        } finally {
            if (sxssfWorkbook != null) {
                sxssfWorkbook.close();
            }
            if (httpServletResponse != null && httpServletResponse.getOutputStream() != null) {
                httpServletResponse.getOutputStream().close();
            }
        }
    } catch (Exception e) {
        throw new ExcelBootException(e);
    }
}
 

/**
 * response 响应
 *
 * @param sxssfWorkbook
 * @param outputStream
 * @param fileName
 * @param sheetName
 * @param response
 * @throws Exception
 */
private static void setIo(SXSSFWorkbook sxssfWorkbook, OutputStream outputStream, String fileName, String[] sheetName, HttpServletResponse response) throws Exception {
    try {
        if (response != null) {
            response.setHeader("Charset", "UTF-8");
            response.setHeader("Content-Type", "application/force-download");
            response.setHeader("Content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName == null ? sheetName[0] : fileName, "utf8") + ".xlsx");
            response.flushBuffer();
            outputStream = response.getOutputStream();
        }
        writeAndColse(sxssfWorkbook, outputStream);
    } catch (Exception e) {
        e.getSuppressed();
    }
}
 

/**
 * Excel导出：无样式（行、列、单元格样式）、自适应列宽
 * web 响应（response）
 *
 * @return
 */
public Boolean exportForExcelsNoStyle() {
    long startTime = System.currentTimeMillis();
    log.info("=== ===  === :Excel tool class export start run!");
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000);
    OutputStream outputStream = null;
    SXSSFRow sxssfRow = null;
    try {
        setDataListNoStyle(sxssfWorkbook, sxssfRow, dataLists, regionMap, mapColumnWidth, paneMap, sheetName, labelName, dropDownMap,defaultColumnWidth,fontSize);
        setIo(sxssfWorkbook, outputStream, fileName, sheetName, response);
    } catch (Exception e) {
        e.printStackTrace();
    }
    log.info("=== ===  === :Excel tool class export run time:" + (System.currentTimeMillis() - startTime) + " ms!");
    return true;
}
 

@Test
public void lastRowNum2333() throws IOException, InvalidFormatException {
    String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new File(file));
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    Cell cell = xssfSheet.getRow(0).createCell(9);
    cell.setCellValue("testssdf是士大夫否t");

    FileOutputStream fileout = new FileOutputStream("d://test/r2" + System.currentTimeMillis() + ".xlsx");
    sxssfWorkbook.write(fileout);
    sxssfWorkbook.dispose();
    sxssfWorkbook.close();

    xssfWorkbook.close();
}
 

/**
 * 用于浏览器导出
 *
 * @param param
 * @param exportFunction
 * @param ExportFunction
 * @param <R>
 * @param <T>
 */
public <R, T> void exportResponse(R param, ExportFunction<R, T> exportFunction) {
    SXSSFWorkbook sxssfWorkbook = null;
    try {
        try {
            verifyResponse();
            sxssfWorkbook = commonSingleSheet(param, exportFunction);
            download(sxssfWorkbook, httpServletResponse, URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        } finally {
            if (sxssfWorkbook != null) {
                sxssfWorkbook.close();
            }
            if (httpServletResponse != null && httpServletResponse.getOutputStream() != null) {
                httpServletResponse.getOutputStream().close();
            }
        }
    } catch (Exception e) {
        throw new ExcelBootException(e);
    }
}
 

/**
 * 用于浏览器分sheet导出
 *
 * @param param
 * @param exportFunction
 * @param ExportFunction
 * @param <R>
 * @param <T>
 */
public <R, T> void exportMultiSheetResponse(R param, ExportFunction<R, T> exportFunction) {
    SXSSFWorkbook sxssfWorkbook = null;
    try {
        try {
            verifyResponse();
            sxssfWorkbook = commonMultiSheet(param, exportFunction);
            download(sxssfWorkbook, httpServletResponse, URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        } finally {
            if (sxssfWorkbook != null) {
                sxssfWorkbook.close();
            }
        }
    } catch (Exception e) {
        throw new ExcelBootException(e);
    }
}
 

/**
 * 通过OutputStream分sheet导出excel文件,一般用于异步导出大Excel文件到ftp服务器
 *
 * @param param
 * @param exportFunction
 * @param ExportFunction
 * @param <R>
 * @param <T>
 * @return
 */
public <R, T> OutputStream generateMultiSheetStream(R param, ExportFunction<R, T> exportFunction) throws IOException {
    SXSSFWorkbook sxssfWorkbook = null;
    try {
        verifyStream();
        sxssfWorkbook = commonMultiSheet(param, exportFunction);
        sxssfWorkbook.write(outputStream);
        return outputStream;
    } catch (Exception e) {
        log.error("分Sheet生成Excel发生异常! 异常信息:", e);
        if (sxssfWorkbook != null) {
            sxssfWorkbook.close();
        }
        throw new ExcelBootException(e);
    }
}
 

@Override
public org.uberfire.backend.vfs.Path exportDataSetExcel(DataSet dataSet) {
    try {
        SXSSFWorkbook wb = dataSetToWorkbook(dataSet);

        // Write workbook to Path
        String tempXlsFile = uuidGenerator.newUuid() + ".xlsx";
        Path tempXlsPath = gitStorage.createTempFile(tempXlsFile);
        try (OutputStream os = Files.newOutputStream(tempXlsPath)) {
            wb.write(os);
            os.flush();
        }

        // Dispose of temporary files backing this workbook on disk
        if (!wb.dispose()) {
            log.warn("Could not dispose of temporary file associated to data export!");
        }
        return Paths.convert(tempXlsPath);
    } catch (Exception e) {
        throw exceptionManager.handleException(e);
    }
}
 

@Test
public void lastRowNum233() throws IOException {
    String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";
    Workbook xx = new XSSFWorkbook(file);
    System.out.println(new File(file).exists());

    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook();
    Sheet xssfSheet = xssfWorkbook.getXSSFWorkbook().getSheetAt(0);

    Cell cell = xssfSheet.getRow(0).createCell(9);
    cell.setCellValue("testssdf是士大夫否t");

    FileOutputStream fileout = new FileOutputStream("d://test/r2" + System.currentTimeMillis() + ".xlsx");
    xssfWorkbook.write(fileout);
    xssfWorkbook.close();
}
 

SerialWriteWorkbook() {
  this.swb = new SXSSFWorkbook(100);
  this.sh = this.swb.createSheet();

  this.rowIndex = 0;

  this.headerStyle = (XSSFCellStyle)swb.createCellStyle();
  this.headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
  //solid fill
  this.headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  Font headerFont = swb.createFont();
  headerFont.setFontHeightInPoints((short)14);
  headerFont.setBold(true);
  headerFont.setColor(IndexedColors.WHITE.getIndex());
  this.headerStyle.setFont(headerFont);
}
 

static void writeByLocalOrBrowser(HttpServletResponse response, String fileName, SXSSFWorkbook wb, OutputStream out) {
    try {
        ZipSecureFile.setMinInflateRatio(0L);
        if (response != null) {
            // response对象不为空,响应到浏览器下载
            response.setContentType(FebsConstant.XLSX_CONTENT_TYPE);
            response.setHeader("Content-disposition", "attachment; filename="
                    + URLEncoder.encode(String.format("%s%s", fileName, FebsConstant.XLSX_SUFFIX), "UTF-8"));
            if (out == null) {
                out = response.getOutputStream();
            }
        }
        wb.write(out);
        out.flush();
        out.close();
    } catch (Exception e) {
        log.error(e.getMessage());
    }

}
 

protected Sheet createSheet(SXSSFWorkbook wb,Paper paper,String name){
	Sheet sheet = null;
	if(name==null){
		sheet=wb.createSheet();
	}else{			
		sheet=wb.createSheet(name);
	}
	PaperType paperType=paper.getPaperType();
	XSSFPrintSetup printSetup=(XSSFPrintSetup)sheet.getPrintSetup();
	Orientation orientation=paper.getOrientation();
	if(orientation.equals(Orientation.landscape)){
		printSetup.setOrientation(PrintOrientation.LANDSCAPE);			
	}
	setupPaper(paperType, printSetup);
	int leftMargin=paper.getLeftMargin();
	int rightMargin=paper.getRightMargin();
	int topMargin=paper.getTopMargin();
	int bottomMargin=paper.getBottomMargin();
	sheet.setMargin(Sheet.LeftMargin, UnitUtils.pointToInche(leftMargin));
	sheet.setMargin(Sheet.RightMargin, UnitUtils.pointToInche(rightMargin));
	sheet.setMargin(Sheet.TopMargin, UnitUtils.pointToInche(topMargin));
	sheet.setMargin(Sheet.BottomMargin, UnitUtils.pointToInche(bottomMargin));
	return sheet;
}
 

public RTemplate()
{
    this.sheetIndex           = 0;
    this.direction            = 0;
    this.templateSheet        = null;
    this.excelVersion         = null;
    this.valueMethods         = new LinkedHashMap<String ,RCellGroup>();
    this.valueNames           = new Hashtable<String ,String>();
    this.autoHeights          = new Hashtable<String ,Object>();
    this.valueListeners       = new Hashtable<String ,ValueListener>();
    this.sheetListeners       = new ArrayList<SheetListener>();
    this.isIntegerShowDecimal = false;
    this.isExcelFilter        = false;
    this.cells                = new HashMap<String ,RCell>();
    this.isSafe               = false;
    this.isBig                = true;
    this.isCheck              = true;
    this.pageBreakMode        = "auto";
    this.rowAccessWindowSize  = SXSSFWorkbook.DEFAULT_WINDOW_SIZE * 10;
    this.titlePageHeaderFirstWriteByRow           = 0;
    this.titlePageHeaderFirstWriteByRealDataCount = 0;
    this.titlePageHeaderRate                      = 0;
    this.setValueSign(":");
    this.setTitleUseOnePage(false);
}
 

@Test
public void lastRowNum255() throws IOException, InvalidFormatException {
    String file = "D:\\test\\complex.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new File(file));
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    xssfSheet.shiftRows(1, 4, 10, true, true);

    FileOutputStream fileout = new FileOutputStream("d://test/r2" + System.currentTimeMillis() + ".xlsx");
    sxssfWorkbook.write(fileout);
    sxssfWorkbook.dispose();
    sxssfWorkbook.close();

    xssfWorkbook.close();
}
 

@Test
public void lastRowNum2() throws IOException {
    String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    Sheet xssfSheet = xssfWorkbook.getXSSFWorkbook().getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getPhysicalNumberOfRows());
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    LOGGER.info("一共行数:{}", xssfSheet.getFirstRowNum());

}
 

/**
 * Description 创建表格
 *
 * @return org.apache.poi.hssf.usermodel.HSSFWorkbook
 * @author fxb
 * @date 2018/8/23
 */
public SXSSFWorkbook createExcel() throws Exception {
    try {
        //只在内存中保留五百条记录，五百条之前的会写到磁盘上，后面无法再操作
        SXSSFWorkbook workbook = new SXSSFWorkbook(500);
        //遍历headers创建表格
        for (String key : headers.keySet()) {
            this.createSheet(workbook, key, headers.get(key), this.data.get(key));
        }
        return workbook;
    } catch (Exception e) {
        log.error("创建表格失败:{}", e.getMessage());
        throw e;
    }
}
 

@Test
public void lastRowNum() throws IOException {
    String file = "D:\\test\\原文件.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    SXSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

/**
 * 创建一个工作薄
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-16
 * @version     v1.0
 *
 * @return
 */
public final static RWorkbook createWorkbook(RTemplate i_RTemplate)
{
    RWorkbook v_Ret = null;
    
    if ( "xls".equalsIgnoreCase(i_RTemplate.getExcelVersion()) )
    {
        v_Ret = new RWorkbook(new HSSFWorkbook());
    }
    else if ( "xlsx".equalsIgnoreCase(i_RTemplate.getExcelVersion()) )
    {
        if ( i_RTemplate.getIsBig() )
        {
            v_Ret = new RWorkbook(new SXSSFWorkbook(i_RTemplate.getRowAccessWindowSize()));
        }
        else
        {
            v_Ret = new RWorkbook(new XSSFWorkbook());
        }
    }
    else
    {
        return v_Ret;
    }
    
    ExcelHelp.copyWorkbook(i_RTemplate.getTemplateSheet().getWorkbook() ,v_Ret.getWorkbook());
    
    return v_Ret;
}
 

/**
 * This implementation disposes of the {@link SXSSFWorkbook} when done with rendering.
 * @see org.apache.poi.xssf.streaming.SXSSFWorkbook#dispose()
 */
@Override
protected void renderWorkbook(Workbook workbook, HttpServletResponse response) throws IOException {
	super.renderWorkbook(workbook, response);

	// Dispose of temporary files in case of streaming variant...
	((SXSSFWorkbook) workbook).dispose();
}
 

public void outZip() {
	try {
		wb.write(out);
		out.close();
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	((SXSSFWorkbook) wb).dispose();
}
 

/**
 * 
 */
public Excel2007Writer(File file, int cache_size) {
	wb = new SXSSFWorkbook(cache_size);
	try {
		out = new FileOutputStream(file);
	} catch (FileNotFoundException e) {
		LOGGER.error("", e);
	}
	createHelper = wb.getCreationHelper();
	sh = wb.createSheet();

	rowHeader = sh.createRow(0);
}
 

/**
 * 创建单元格样式
 *
 * @param workbook 工作簿
 * @param fontSize 字体大小
 * @return 单元格样式
 */
private static CellStyle createCellStyle(SXSSFWorkbook workbook, short fontSize, short aligment, Boolean bold) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(aligment);
    //垂直居中
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    org.apache.poi.ss.usermodel.Font font = workbook.createFont();
    if (bold) {
        //加粗字体
        font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
    }
    font.setFontHeightInPoints(fontSize);
    cellStyle.setFont(font);
    return cellStyle;
}
 

private void generateReport(String filename) {
	StringUtilities.outputWithTime("Generating scan report");
	removeEmptyTables();

	workbook = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk

	int i = 0;
	indexedTableNameLookup = new HashMap<>();
	for (Table table : tableToFieldInfos.keySet()) {
		String tableNameIndexed = Table.indexTableNameForSheet(table.getName(), i);
		indexedTableNameLookup.put(table.getName(), tableNameIndexed);
		i++;
	}

	createFieldOverviewSheet();
	createTableOverviewSheet();

	if (scanValues) {
		createValueSheet();
	}

	createMetaSheet();

	try (FileOutputStream out = new FileOutputStream(new File(filename))) {
		workbook.write(out);
		out.close();
		StringUtilities.outputWithTime("Scan report generated: " + filename);
	} catch (IOException ex) {
		throw new RuntimeException(ex.getMessage());
	}
}
 

public static <T> SXSSFWorkbook generateExcel(List<T> list, Class<T> clazz, String[] fieldsName, String[] fields, String sheetName) {
    //1、创建工作簿
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    if (list != null && !list.isEmpty()) {
        //1.3、列标题样式
        CellStyle style2 = createCellStyle(workbook, (short) 13, CellStyle.ALIGN_LEFT, true);
        //1.4、强制换行
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);
        //2、创建工作表
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        //设置默认列宽
        sheet.setDefaultColumnWidth(13);
        SXSSFRow row2 = sheet.createRow(0);
        row2.setHeight((short) 260);
        for (int j = 0; j < list.size(); j++) {
            SXSSFRow row = sheet.createRow(j + 1);
            row.setHeight((short) 260);
            for (int i = 0; i < fieldsName.length; i++) {
                //3.3设置列标题
                SXSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(style2);
                cell2.setCellValue(fieldsName[i]);
                //4、操作单元格；将数据写入excel
                handleWriteCell(row, i, j, list, cellStyle, fields, clazz);
            }
        }
    }
    return workbook;
}
 

/**
 * 本地输出
 * Excel导出：有样式（行、列、单元格样式）、自适应列宽
 *
 * @return
 */
public Boolean localNoResponse() {
    long startTime = System.currentTimeMillis();
    log.info("=== ===  === :Excel tool class export start run!");
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000);
    SXSSFRow sxssfRow = null;
    try {
        setDataList(sxssfWorkbook, sxssfRow, dataLists, regionMap, mapColumnWidth, styles, paneMap, sheetName, labelName, rowStyles, columnStyles, dropDownMap, defaultColumnWidth,fontSize);
        setIo(sxssfWorkbook, filePath);
    } catch (Exception e) {
        e.printStackTrace();
    }
    log.info("=== ===  === :Excel tool class export run time:" + (System.currentTimeMillis() - startTime) + " ms!");
    return true;
}
 

/**
 * 本地输出
 * Excel导出：无样式（行、列、单元格样式）、自适应列宽
 *
 * @return
 */
public Boolean localNoStyleNoResponse() {
    long startTime = System.currentTimeMillis();
    log.info("=== ===  === :Excel tool class export start run!");
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000);
    SXSSFRow sxssfRow = null;
    try {
        setDataListNoStyle(sxssfWorkbook, sxssfRow, dataLists, regionMap, mapColumnWidth, paneMap, sheetName, labelName, dropDownMap, defaultColumnWidth,fontSize);
        setIo(sxssfWorkbook, filePath);
    } catch (Exception e) {
        e.printStackTrace();
    }
    log.info("=== ===  === :Excel tool class export run time:" + (System.currentTimeMillis() - startTime) + " ms!");
    return true;
}
 

private static void clear(Workbook workbook) {
    if (workbook instanceof SXSSFWorkbook) {
        ((SXSSFWorkbook) workbook).dispose();
    }
    try {
        workbook.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}
 

public ExcelWorkbook(boolean streaming) {
    if (streaming) {
        sxssfWorkbook = new SXSSFWorkbook(100);
        sxssfWorkbook.setCompressTempFiles(true);

    } else {
        xssfWorkbook = new XSSFWorkbook();
    }
}
 

@Test
public void write0() throws IOException {
    FileOutputStream fileOutputStream =
        new FileOutputStream("D://test//tt132" + System.currentTimeMillis() + ".xlsx");
    SXSSFWorkbook sxxsFWorkbook = new SXSSFWorkbook();
    SXSSFSheet sheet = sxxsFWorkbook.createSheet("t1");
    SXSSFRow row = sheet.createRow(0);
    SXSSFCell cell1 = row.createCell(0);
    cell1.setCellValue(999999999999999L);
    SXSSFCell cell2 = row.createCell(1);
    cell2.setCellValue(1000000000000001L);
    SXSSFCell cell32 = row.createCell(2);
    cell32.setCellValue(300.35f);
    sxxsFWorkbook.write(fileOutputStream);
}