Java Code Examples for org.apache.poi.xssf.streaming.SXSSFWorkbook#write()

/**
 * 通过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 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();
}
 

@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();
}
 

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());
    }

}
 

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

@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);
}
 

@Test
public void write() 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(Long.toString(999999999999999L));
    SXSSFCell cell2 = row.createCell(1);
    cell2.setCellValue(Long.toString(1000000000000001L));
    sxxsFWorkbook.write(fileOutputStream);
}
 

public static void writeAndColse(SXSSFWorkbook sxssfWorkbook, OutputStream outputStream) throws Exception {
    try {
        if (outputStream != null) {
            sxssfWorkbook.write(outputStream);
            sxssfWorkbook.dispose();
            outputStream.flush();
            outputStream.close();
        }
    } catch (Exception e) {
        System.out.println(" Andyczy ExcelUtils Exception Message：Output stream is not empty !");
        e.getSuppressed();
    }
}
 

@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();
}
 

/**
 * 构建Excel服务器响应格式
 *
 * @param wb
 * @param response
 * @param filename
 * @throws IOException
 */
private void download(SXSSFWorkbook wb, HttpServletResponse response, String filename) throws IOException {
    OutputStream out = response.getOutputStream();
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-disposition",
            String.format("attachment; filename=%s", filename));
    if (null != out) {
        wb.write(out);
        out.flush();
    }
}
 

@Test
public void lastRowNum2332222() 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);
    cell.setCellValue("testssdf是士大夫否t");

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

@Test
public void lastRowNum23443() throws IOException {
    String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);

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

public void writeToResponse(SXSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
    String userAgent = request.getHeader("User-Agent");
    String fileName = "Excel-" + this.fileName + ".xls";
    String encodeFileName = URLEncoder.encode(fileName, "UTF8");
    // 如果没有userAgent，则默认使用IE的方式进行编码
    String rtn = "filename=\"" + encodeFileName + "\"";
    if (userAgent != null) {
        userAgent = userAgent.toLowerCase();
        if (userAgent.contains(IE)) {
            // IE浏览器，只能采用URLEncoder编码
            rtn = "filename=\"" + encodeFileName + "\"";
        } else if (userAgent.contains(OPERA)) {
            // Opera浏览器只能采用filename*
            rtn = "filename*=UTF-8''" + encodeFileName;
        } else if (userAgent.contains(SAFARI)) {
            // Safari浏览器，只能采用ISO编码的中文输出
            rtn = "filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "\"";
        }  else if (userAgent.contains(FIREFOX)) {
            // FireFox浏览器，可以使用MimeUtility或filename*或ISO编码的中文输出
            rtn = "filename*=UTF-8''" + encodeFileName;
        }
    }
    String headStr = "attachment;  " + rtn;
    response.setContentType("APPLICATION/ms-excel");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-Disposition", headStr);
    OutputStream out1 = response.getOutputStream();
    workbook.write(out1);
}
 

@SuppressWarnings("unchecked")
public void exportExcelTemplate(HttpServletRequest req, HttpServletResponse resp) throws Exception {
	List<VariableCategory> variableCategories=(List<VariableCategory>)httpSessionKnowledgeCache.get(req, VCS_KEY);
	if(variableCategories==null){
		KnowledgeBase knowledgeBase=buildKnowledgeBase(req);
		variableCategories=knowledgeBase.getResourceLibrary().getVariableCategories();
	}
	SXSSFWorkbook wb = new SXSSFWorkbook();
	XSSFCellStyle style=(XSSFCellStyle)wb.createCellStyle();
	Color c=new Color(147,208,15);
	XSSFColor xssfColor=new XSSFColor(c);
	style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	style.setFillForegroundColor(xssfColor);
	for(VariableCategory vc:variableCategories){
		buildSheet(wb, vc,style);
	}
	resp.setContentType("application/x-xls");
	resp.setHeader("Content-Disposition","attachment; filename=urule-batch-test-template.xlsx");
	OutputStream outputStream=resp.getOutputStream();
	wb.write(outputStream);;
	outputStream.flush();
	outputStream.close();
}
 

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 void exportExcelForLecturerProfit(HttpServletResponse response, Page<LecturerProfitVO> result) throws IOException {
	// 创建一个workbook 对应一个excel文件
	final SXSSFWorkbook workBook = new SXSSFWorkbook();
	SXSSFSheet sheet = workBook.createSheet("讲师分润报表");

	// 列名和列宽
	String[] names = { "讲师名称", "银行卡号", "银行名称", "银行开户名", "讲师分润（元）", "平台分润（元）", "时间" };// 表头
	Integer[] widths = { 25, 15, 15, 25, 25, 25, 25 };// 列宽

	// 创建第一行
	SXSSFRow row = sheet.createRow(0);

	// 设置第一行样式
	CellStyle headStyle = workBook.createCellStyle();
	headStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);// 水平居中
	headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

	// 设置第一行字体
	Font headFont = workBook.createFont();
	headFont.setBold(true);
	headStyle.setFont(headFont);

	// 设置第一行单元格内容、单元格样式
	for (int i = 0; i < names.length; i++) {
		SXSSFCell cell = row.createCell(i);
		cell.setCellValue(names[i]);
		cell.setCellStyle(headStyle);
		sheet.setColumnWidth(i, widths[i] * 256);
	}

	// 从第二行开始遍历出分润记录表的数据，再写入单元格
	SXSSFRow row1 = sheet.createRow(1);
	int r = 1;
	for (LecturerProfitVO bean : result.getList()) {
		row1 = sheet.createRow(r++);
		row1.createCell(0).setCellValue(bean.getLecturerVO().getLecturerName());
		row1.createCell(1).setCellValue(bean.getBankCardNo());
		row1.createCell(2).setCellValue(bean.getBankName());
		row1.createCell(3).setCellValue(bean.getBankUserName());
		row1.createCell(4).setCellValue(bean.getLecturerProfit().doubleValue());
		row1.createCell(5).setCellValue(bean.getPlatformProfit().doubleValue());
		row1.createCell(6).setCellValue(new SimpleDateFormat("yyyy/MM/dd").format(bean.getGmtCreate()));
	}
	try {
		workBook.write(response.getOutputStream());
		response.getOutputStream().flush();
	} catch (IOException e) {
		e.printStackTrace();
	} finally {
		if (response.getOutputStream() != null)
			response.getOutputStream().close();
		if (workBook != null)
			workBook.close();
	}
}