Java SXSSFWorkbook类代码示例

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
@RequestMapping(value = "/test2.xlsx", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
@ResponseBody
byte[] testDown() throws IOException, InvalidFormatException {
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int i = 0; i < 60000; i++) {
        Row newRow = sheet.createRow(i);
        for (int j = 0; j < 100; j++) {
            newRow.createCell(j).setCellValue("test" + Math.random());
        }
    }
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    workbook.write(os);
    byte[] bytes = os.toByteArray();
    return bytes;
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
private void buildSheet(SXSSFWorkbook wb,VariableCategory vc,XSSFCellStyle style){
	String name=vc.getName();
	Sheet sheet=wb.createSheet(name);
	Row row=sheet.createRow(0);
	List<Variable> variables=vc.getVariables();
	for(int i=0;i<variables.size();i++){
		sheet.setColumnWidth(i,4000);
		Cell cell=row.createCell(i);
		Variable var=variables.get(i);
		cell.setCellValue(var.getLabel());
		cell.setCellStyle(style);
	}
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
@Test
public void testExcel07() throws IOException{
	List<UserPlus> list = getData(0, 20000);
	Map<String, String> map = new HashMap<String,String>();
	map.put("msg", "用户信息导出报表");
	map.put("status", "导出成功");
	long start = System.currentTimeMillis();
	Workbook workbook = ExcelExportUtil.exportExcel07(UserPlus.class, list, map, true);
	long end = System.currentTimeMillis();
	System.out.println("耗时：" + (end - start ) + "毫秒");
	FileOutputStream outputStream = new FileOutputStream("D:/test/user.xlsx");
	workbook.write(outputStream);
	if(SXSSFWorkbook.class.equals(workbook.getClass())){
		SXSSFWorkbook wb = (SXSSFWorkbook)workbook;
		wb.dispose();
	}
	outputStream.flush();
	outputStream.close();
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
public static Workbook createWorkbook(Workbook workbook) throws IOException
{
  if (workbook instanceof XSSFWorkbook)
  {
    return new XSSFWorkbook();
  }
  else if (workbook instanceof SXSSFWorkbook)
  {
    return new SXSSFWorkbook();
  }
  else if (workbook instanceof HSSFWorkbook)
  {
    return new HSSFWorkbook();
  }

  throw new FileReadException("Unknown workbook type [" + workbook.getClass().getSimpleName() + "]", null);
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
protected OnLoadListener(final File outputFile, final ActionListener<Void> listener) {
    this.outputFile = outputFile;
    this.listener = listener;

    if (isExcel2007) {
        final SecurityManager sm = System.getSecurityManager();
        if (sm != null) {
            sm.checkPermission(new SpecialPermission());
        }
        workbook = AccessController
                .doPrivileged((PrivilegedAction<Workbook>) () -> new SXSSFWorkbook(-1));
        sheet = AccessController
                .doPrivileged((PrivilegedAction<Sheet>) () -> workbook.createSheet());
    } else {
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet();
    }
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
/**
 * could be interesting for spring batch
 *
 * @param inputResource source of the data read by lines
 * @param outputResource generated .xlsx resource
 */
public void process(Resource inputResource, Resource outputResource) {
    SXSSFWorkbook wb = new SXSSFWorkbook(flushSize);
    try {
        wb.setCompressTempFiles(true); // temp files will be gzipped
        final Sheet sh = wb.createSheet();

        InputStreamReader is = new InputStreamReader(inputResource.getInputStream());
        BufferedReader br = new BufferedReader(is);
        final CreationHelper createHelper = sh.getWorkbook().getCreationHelper();

        br.lines().forEachOrdered(string -> createRow(string, sh, createHelper));

        FileOutputStream out = new FileOutputStream(outputResource.getFile());

        wb.write(out);
        IOUtils.closeQuietly(out);
    } catch (IOException e) {
       logger.error("IOE", e);
    } finally {
        if (wb != null) {
            // dispose of temporary files backing this workbook on disk
            wb.dispose();
        }
    }
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
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.rowAccessWindowSize  = SXSSFWorkbook.DEFAULT_WINDOW_SIZE * 10;
    this.titlePageHeaderFirstWriteByRow           = 0;
    this.titlePageHeaderFirstWriteByRealDataCount = 0;
    this.titlePageHeaderRate                      = 0;
    this.setValueSign(":");
    this.setTitleUseOnePage(false);
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
public static void main(String[] args) throws Throwable {
        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        for (int rownum = 0; rownum < 1000; rownum++) {
            Row row = sh.createRow(rownum);
            Row row1 = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum++) {
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }

        }

        // Rows with rownum < 900 are flushed and not accessible
//        for (int rownum = 0; rownum < 103857; rownum++) {
//            Assert.assertNull(sh.getRow(rownum));
//        }
//
//        // ther last 100 rows are still in memory
//        for (int rownum = 103857; rownum < 104857; rownum++) {
//            Assert.assertNotNull(sh.getRow(rownum));
//        }

        File file = new File("C:\\Users\\FlyingHe\\Desktop", "datas.xlsx");
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();

        // dispose of temporary files backing this workbook on disk
        wb.dispose();
    }
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
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;
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
/**
 * 将workbook的对象写入到文件中
 * 
 * @author nan.li
 * @param filePath
 * @param dataName
 * @param workbook
 * @param header
 */
public static void writeDataToFile(String filePath, String dataName, SXSSFWorkbook workbook)
{
    File targetFile = new File(filePath);
    if (StringUtils.isEmpty(filePath))
    {
        logger.warn("filePath is empty! Quit writeDataToFile!");
        return;
    }
    FileOutputStream fo = null;
    try
    {
        targetFile.getParentFile().mkdirs();
        fo = new FileOutputStream(targetFile);
        workbook.write(fo);
        IOUtils.closeQuietly(fo);
        workbook.close();
    }
    catch (IOException e)
    {
        e.printStackTrace();
    }
    finally
    {
    }
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
@Test
    public void testAutoSizeColumn_ComprovaErroMetodoAutoSizeColumn_de_Sheet_QuandoUltimasLinhasSaoVazias() {
        final int tamanhoIncorretoColuna = 236;
        final int tamanhoCorretoColuna = 2048;

        Table t = new Table();
        //após escrever certo número de linhas com o conteúdo vazio,
        //a coluna não é ajustada para o tamanho equivalente a linha
        //com maior número de caracteres
        for (int i = 1; i < 130; i++) {
            t.addNewRow();
            if (i < 20) {
                t.add("Teste erro " + i);
            } else {
                t.add(" ");
            }
        }

        Workbook wb = t.toWorkBook(new SXSSFWorkbook());
        Sheet sheet = wb.getSheetAt(0);

//		alterado o modo de validação devido a diferneça de plataformas(Windows, Linux, Mac) esses valores podem mudar, porém devem respeitar o mínimo. Por isso usado assertTrue e não assertEquals
        Assert.assertTrue(sheet.getColumnWidth(0) >= tamanhoIncorretoColuna);
        Assert.assertTrue(sheet.getColumnWidth(1) >= tamanhoCorretoColuna);
    }
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
/**
 * Istanzia oggetto
 *
 * @param se segment engine
 */
public SegmentationExcelWriter(SegmentEngine se) {
    sheetCellIndex = new LinkedHashMap<>();
    wb = new SXSSFWorkbook();
    sheetResults = wb.createSheet("Results");
    headerResults = sheetResults.createRow(0);
    SXSSFCell fCell = headerResults.createCell(0);
    fCell.setCellValue("File Name");
    fCell = headerResults.createCell(1);
    fCell.setCellValue("Language");
    fCell = headerResults.createCell(2);
    fCell.setCellValue("Original Text");
    fCell = headerResults.createCell(3);
    fCell.setCellValue("KPI");
    int idxCell = 4;
    for (SegmentConfiguration sc : se.getPatternMatrix()) {
        idxCell = createExcelHeader(wb, headerResults, sheetCellIndex, idxCell, "", sc, sheetResults);
    }
    writeClassificationHeader(wb);
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
/**
 * Writes the example set into a excel file with XLSX format. If you want to write it in XLS
 * format use {@link #write(ExampleSet, Charset, OutputStream)}.
 *
 * @param exampleSet
 *            the exampleSet to write
 * @param sheetName
 *            name of the excel sheet which will be created.
 * @param dateFormat
 *            a string which describes the format used for dates.
 * @param numberFormat
 *            a string which describes the format used for numbers.
 * @param outputStream
 *            the stream to write the file to
 * @param op
 *            needed for checkForStop
 */
public static void writeXLSX(ExampleSet exampleSet, String sheetName, String dateFormat, String numberFormat,
		OutputStream outputStream, Operator op) throws WriteException, IOException, ProcessStoppedException {
	// .xlsx files can only store up to 16384 columns, so throw error in case of more
	if (exampleSet.getAttributes().allSize() > 16384) {
		throw new IllegalArgumentException(
				I18N.getMessage(I18N.getErrorBundle(), "export.excel.excel_xlsx_file_exceeds_column_limit"));
	}

	try (SXSSFWorkbook workbook = new SXSSFWorkbook(null, SXSSFWorkbook.DEFAULT_WINDOW_SIZE, false, true)) {
		Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetName));

		dateFormat = dateFormat == null ? DEFAULT_DATE_FORMAT : dateFormat;

		numberFormat = numberFormat == null ? "#.0" : numberFormat;
		writeXLSXDataSheet(workbook, sheet, dateFormat, numberFormat, exampleSet, op);
		workbook.write(outputStream);
	} finally {
		outputStream.flush();
		outputStream.close();
	}
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
public Excel(String sheetName, short xmlFormat, int rows){
	if(FORMAT_XSSF==xmlFormat) workbook=new XSSFWorkbook();
	else if(FORMAT_HSSF==xmlFormat) workbook=new HSSFWorkbook();
	else if(FORMAT_SXSSF==xmlFormat) workbook=new SXSSFWorkbook();
	this.xmlFormat=xmlFormat;
       Sheet sheet = workbook.createSheet();
       drawing = sheet.createDrawingPatriarch();
       workbook.setSheetName(0, sheetName);
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入依赖的package包/类
@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);
    }
}