Java SXSSFWorkbook.createSheet方法代码示例

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包/类
/**
 * 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 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包/类
/**
 * 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包/类
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包/类
/**
 * 
 */
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);
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入方法依赖的package包/类
protected SXSSFWorkbook creaWorkBook()
{
	_workBook = new SXSSFWorkbook(_bufferSize);
	initStili();
	
	for(int i=0; i < _sheets.size(); i++)
	{
		Sheet sheet = _workBook.createSheet();
		_workBook.setSheetName(i, _sheets.get(i).getNomeSheet() );
		createSheet(i, sheet, _sheets.get(i) );
	}
	
	return _workBook;
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入方法依赖的package包/类
public static SXSSFSheet newSXSSFSheet(SXSSFWorkbook wb,String sheetName) {
	return (SXSSFSheet) wb.createSheet(sheetName);
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入方法依赖的package包/类
private static void writeClassificationHeader(SXSSFWorkbook workbook) {
    SXSSFSheet sheet = workbook.createSheet("Bayes");
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("File");
    header.createCell(1).setCellValue("Text");
    header.createCell(2).setCellValue("1st Level1");
    header.createCell(3).setCellValue("1st Score1");
    header.createCell(4).setCellValue("1st Level2");
    header.createCell(5).setCellValue("1st Score2");
    header.createCell(6).setCellValue("1st Level3");
    header.createCell(7).setCellValue("1st Score3");
    header.createCell(8).setCellValue("1st Level4");
    header.createCell(9).setCellValue("1st Score4");
    header.createCell(10).setCellValue("1st Level5");
    header.createCell(11).setCellValue("1st Score5");
    header.createCell(12).setCellValue("1st Level6");
    header.createCell(13).setCellValue("1st Score7");
    header.createCell(14).setCellValue("2nd Level1");
    header.createCell(15).setCellValue("2nd Score1");
    header.createCell(16).setCellValue("2nd Level2");
    header.createCell(17).setCellValue("2nd Score2");
    header.createCell(18).setCellValue("2nd Level3");
    header.createCell(19).setCellValue("2nd Score3");
    header.createCell(20).setCellValue("2nd Level4");
    header.createCell(21).setCellValue("2nd Score4");
    header.createCell(22).setCellValue("2nd Level5");
    header.createCell(23).setCellValue("2nd Score5");
    header.createCell(24).setCellValue("2nd Level6");
    header.createCell(25).setCellValue("2nd Score6");
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入方法依赖的package包/类
/**
 * Esporta il contenuto di un indice su Excel
 *
 * @since 1.2
 * @param fileToExport file su cui esportare
 * @param semGui frame
 * @param documentsTable tabella dei documenti
 */
public static void exportExcelFile(String fileToExport, SemGui semGui, JTable documentsTable) {
    Thread t = new Thread(new Runnable() {
        @Override
        public void run() {
            String path = fileToExport;
            if (!path.endsWith(".xlsx")) {
                path = path + ".xlsx";
            }
            try {
                HashMap<String, String> c1 = new HashMap<>();
                HashMap<String, String> c2 = new HashMap<>();
                if (documentsTable != null) {
                    GuiUtils.filterTable(documentsTable, null, 0);
                    int rc = documentsTable.getModel().getRowCount();
                    for (int i = 0; i < rc; i++) {
                        String key = (String) documentsTable.getValueAt(i, 0);
                        String c1v = (String) documentsTable.getValueAt(i, 9);
                        String c2v = (String) documentsTable.getValueAt(i, 10);
                        if (key != null) {
                            if (c1v != null) {
                                c1.put(key, c1v);
                            }
                            if (c2v != null) {
                                c2.put(key, c2v);
                            }

                        }

                    }
                }
                FileOutputStream fos = new FileOutputStream(path);
                SXSSFWorkbook wb = new SXSSFWorkbook();
                SXSSFSheet sheetResults = wb.createSheet("Index");
                SXSSFRow headerResults = sheetResults.createRow(0);
                headerResults.createCell(0).setCellValue("Level1");
                headerResults.createCell(1).setCellValue("Level2");
                headerResults.createCell(2).setCellValue("Level3");
                headerResults.createCell(3).setCellValue("Level4");
                headerResults.createCell(4).setCellValue("Level5");
                headerResults.createCell(5).setCellValue("Level6");
                headerResults.createCell(6).setCellValue("Text");
                headerResults.createCell(7).setCellValue("Tokens");
                headerResults.createCell(8).setCellValue("Class1");
                headerResults.createCell(9).setCellValue("Class2");
                semGui.getME().getDocumentsExcel((String) semGui.getLinguaAnalizzatoreIstruzione().getSelectedItem(), sheetResults, c1, c2);
                wb.write(fos);
                fos.close();
            } catch (Exception e) {
                LogGui.printException(e);
            }
            semGui.getFilesInfoLabel().setText("Esportazione terminata");
        }
    });
    t.start();
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入方法依赖的package包/类
public static void convert(String input, String output) throws Exception {
    if(input != null && output != null) {
        Path inputPath = FileSystems.getDefault().getPath(input);
        Path outputPath = FileSystems.getDefault().getPath(output);
        String contentType = Files.probeContentType(inputPath);

        Workbook workbook = null;

        if(contentType.equals("application/vnd.ms-excel")) { // excel 2003
            //NPOIFSFileSystem poifs = new NPOIFSFileSystem(new FileInputStream(inputPath.toFile()));
            //EncryptionInfo decryptionInfo = new EncryptionInfo(poifs);
            //Decryptor decryptor = Decryptor.getInstance(decryptionInfo);
            //decryptor.verifyPassword("myhouse");
            //workbook = new HSSFWorkbook(decryptor.getDataStream(poifs));
            workbook = new HSSFWorkbook(new FileInputStream(inputPath.toFile()));
        } else if(contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) { // excel 2007+
            //POIFSFileSystem poifs = new POIFSFileSystem(new FileInputStream(inputPath.toFile()));
            //EncryptionInfo decryptionInfo = new EncryptionInfo(poifs);
            //Decryptor decryptor = Decryptor.getInstance(decryptionInfo);
            //decryptor.verifyPassword("myhouse");
            //workbook = new XSSFWorkbook(decryptor.getDataStream(poifs));
            workbook = new XSSFWorkbook(new FileInputStream(inputPath.toFile()));
        }

        if(workbook != null) {
            SXSSFWorkbook outputWorkbook = new SXSSFWorkbook();
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Sheet outputSheet = outputWorkbook.createSheet();
                for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                    Row row = sheet.getRow(j);
                    if(row != null) {
                        Row outputRow = outputSheet.createRow(j);
                        for (int k = 0; k <= row.getLastCellNum(); k++) {
                            Cell cell = row.getCell(k);
                            if(cell != null) {
                                Cell outputCell = outputRow.createCell(k, cell.getCellType());
                                if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
                                    // convert to unicode and copy
                                    if(cell.getStringCellValue() != null) {
                                        outputCell.setCellValue(MongolianLanguageUtil.toUnicode(cell.getStringCellValue()));
                                    }
                                } else {
                                    // just copy cell
                                    switch(cell.getCellType()) {
                                        case Cell.CELL_TYPE_BOOLEAN:
                                            outputCell.setCellValue(cell.getBooleanCellValue());
                                            break;
                                        case Cell.CELL_TYPE_FORMULA:
                                            outputCell.setCellValue(cell.getCellFormula());
                                            break;
                                        case Cell.CELL_TYPE_NUMERIC:
                                            outputCell.setCellValue(cell.getNumericCellValue());
                                            break;
                                        case Cell.CELL_TYPE_ERROR:
                                            outputCell.setCellValue(cell.getErrorCellValue());
                                            break;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            outputWorkbook.write(new FileOutputStream(outputPath.toFile()));
        }
    }
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入方法依赖的package包/类
/**
 * [Flow #-1] 엑셀 파일 생성 : 새 엑셀 파일을 생성함
 */
@Test
public void testWriteExcelFile() throws Exception {
    
    try {
    	log.debug("testWriteExcelFile start....");

    	String sheetName1 = "first sheet";
    	String sheetName2 = "second sheet";
    	StringBuffer sb = new StringBuffer();
    	sb.append(fileLocation).append("/").append("testWriteExcelFile.xlsx");

    	// delete file
    	if (EgovFileUtil.isExistsFile(sb.toString())) {
    		EgovFileUtil.delete(new File(sb.toString()));
    		log.debug("Delete file...." + sb.toString());
    	}
    	
    	SXSSFWorkbook wb = new SXSSFWorkbook();
    	
    	wb.createSheet(sheetName1);
        wb.createSheet(sheetName2);
    	wb.createSheet();

    	// 엑셀 파일 생성
    	SXSSFWorkbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString());
    	
    	// 파일 존재 확인
    	assertTrue(EgovFileUtil.isExistsFile(sb.toString()));
    	
    	// 저장된 Sheet명 일치 점검
    	assertEquals(sheetName1, tmp.getSheetName(0));
    	assertEquals(sheetName2, tmp.getSheetName(1));

    } catch (Exception e) {
    	log.error(e.toString());
    	throw new Exception(e);
    } finally {
    	log.debug("testWriteExcelFile end....");
    }
}
 

import org.apache.poi.xssf.streaming.SXSSFWorkbook; //导入方法依赖的package包/类
/**
 * [Flow #-3] 엑셀 파일 속성 수정 : 엑셀 파일의 속성(셀의 크기, Border의 속성, 셀의 색상, 정렬 등)을 수정함
 */
@Test
public void testWriteExcelFileAttribute() throws Exception {
    
    try {
    	log.debug("testWriteExcelFileAttribute start....");

    	short rowheight = 40;
    	int columnwidth = 30;
    	
    	StringBuffer sb = new StringBuffer();
    	sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx");        	

    	// delete file
    	if (EgovFileUtil.isExistsFile(sb.toString())) {
    		EgovFileUtil.delete(new File(sb.toString()));
    	
    		log.debug("Delete file...." + sb.toString());
    	}
    	
    	SXSSFWorkbook wb = new SXSSFWorkbook();
    	
    	Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");
        
        // 셀의 크기
    	sheet1.setDefaultRowHeight(rowheight);
    	sheet1.setDefaultColumnWidth(columnwidth);
    	
    	Font f2 = wb.createFont();
    	CellStyle cs = wb.createCellStyle();
    	cs = wb.createCellStyle();

        cs.setFont( f2 );
        cs.setWrapText( true );
     
        // 정렬
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        cs.setFillPattern(HSSFCellStyle.DIAMONDS); // 무늬 스타일
        
        // 셀의 색상
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // 무늬 색
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex());  // 배경색
        
    	sheet1.setDefaultColumnStyle((short) 0, cs);
    	
        Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());
        
        CellStyle cs1 = tmp.getCellStyleAt((short)(tmp.getNumCellStyles() - 1));

        log.debug("getAlignment : " + cs1.getAlignment());
    	assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());
    	
    	log.debug("getFillPattern : " + cs1.getFillPattern());
    	assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern());
    	
    	log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor());
    	log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor());
    	assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
    	assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());        	


    } catch (Exception e) {
    	log.error(e.toString());
    	throw new Exception(e);
    } finally {
    	log.debug("testWriteExcelFileAttribute end....");
    }
}