Java Code Examples for org.apache.poi.xssf.usermodel.XSSFWorkbook

The following examples show how to use org.apache.poi.xssf.usermodel.XSSFWorkbook. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
private MetaFile writeExcel(Map<String, List<String[]>> data) throws IOException {

    XSSFWorkbook workBook = new XSSFWorkbook();

    for (String model : data.keySet()) {
      XSSFSheet sheet = workBook.createSheet(model);
      int count = 0;
      for (String[] record : data.get(model)) {
        XSSFRow row = sheet.createRow(count);
        int cellCount = 0;
        for (String val : record) {
          XSSFCell cell = row.createCell(cellCount);
          cell.setCellValue(val);
          cellCount++;
        }
        count++;
      }
    }

    File excelFile = MetaFiles.createTempFile("Data", ".xls").toFile();
    FileOutputStream out = new FileOutputStream(excelFile);
    workBook.write(out);
    out.close();

    return metaFiles.upload(excelFile);
  }
 
Example #2
Source Project: birt   Author: eclipse   File: RaggedCrosstabReportTest.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testRunReport() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("RaggedCrosstab.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Ragged Crosstab Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals(9, firstNullRow(sheet));
	} finally {
		inputStream.close();
	}
}
 
Example #3
Source Project: mzmine3   Author: mzmine   File: XSSFExcelWriterReader.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * exports a data[rows][columns] array
 *
 * @param realFilePath
 * @param data
 * @param rowsFirst true: [rows][cols] false [cols][rows]
 */
public XSSFWorkbook exportDataArrayToFile(File file, String sheetname, Object[][] data,
    boolean rowsFirst) {
  // open wb
  XSSFWorkbook wb = new XSSFWorkbook();
  XSSFSheet sheet = getSheet(wb, sheetname);
  // write to wb
  for (int r = 0; r < data.length; r++) {
    // all columns
    for (int c = 0; c < data[r].length; c++) {
      if (data[r][c] != null) {
        if (rowsFirst)
          writeToCell(sheet, c, r, data[r][c]);
        else
          writeToCell(sheet, r, c, data[r][c]);
      }
    }
  }

  // save wb
  saveWbToFile(file, wb);
  return wb;
}
 
Example #4
Source Project: birt   Author: eclipse   File: Issue46RemoveBlankRowsUserProperties.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testWithoutOption() throws Exception {
	
	debug = false;
	InputStream inputStream = runAndRenderReport("BlankRows.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );

		assertEquals( 9, this.firstNullRow(workbook.getSheetAt(0)));			
	} finally {
		inputStream.close();
	}
}
 
Example #5
Source Project: excel-io   Author: Vatavuk   File: XsRowTest.java    License: MIT License 6 votes vote down vote up
/**
 * Add row to specific position in sheet.
 * @throws IOException If fails
 */
@Test
public void addsRowWithAbsolutePositionToSheet() throws IOException {
    try (final Workbook wbook = new XSSFWorkbook()) {
        final int position = 2;
        final int expected = 1;
        final Row row = new XsRow(
            position,
            new TextCell("textPos")
        ).attachTo(wbook.createSheet());
        MatcherAssert.assertThat(
            row.getRowNum(),
            Matchers.equalTo(expected)
        );
    }
}
 
Example #6
Source Project: birt   Author: eclipse   File: BasicReportTest.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testRunReportWithJpegXlsxGridlinesReport() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("SimpleWithJpegHideGridlines.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Simple Test Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( false, sheet.isDisplayFormulas() );
		assertEquals( false, sheet.isDisplayGridlines() );
		assertEquals( true, sheet.isDisplayRowColHeadings() );
		assertEquals( true, sheet.isDisplayZeros() );
		performSimpleWithJpegTests(sheet);
	} finally {
		inputStream.close();
	}
}
 
Example #7
Source Project: qconfig   Author: qunarcorp   File: TemplateExcelParseServiceImpl.java    License: MIT License 6 votes vote down vote up
@Override
public List<Map<String, String>> parse(final MultipartFile file) {
    try {
        final List<Map<String, String>> data = new ArrayList<>();

        final XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        final XSSFSheet sheet = workbook.getSheetAt(0);
        final List<Row> rows = Lists.newArrayList(sheet);
        final List<String> header = readHeader(rows.get(0));
        for (int rowIndex = 1; rowIndex < rows.size(); rowIndex++) {
            final Row row = rows.get(rowIndex);
            final Map<String, String> rowMap = new HashMap<>();
            for (int cellIndex = 0; cellIndex < header.size(); cellIndex++) {
                rowMap.put(header.get(cellIndex), readCellAsString(row.getCell(cellIndex)));
            }
            data.add(rowMap);
        }

        return data;
    } catch (IOException e) {
        LOG.error("parse excel failed. name: {}", file.getOriginalFilename(), e);
        throw new RuntimeException("parse excel failed");
    }
}
 
Example #8
Source Project: Knowage-Server   Author: KnowageLabs   File: Util.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
public static XSSFWorkbook merge(XSSFWorkbook source, HSSFSheet sheet) {
	XSSFWorkbook destinationWorkbook = source;
	XSSFSheet destinationSheet = destinationWorkbook.getSheetAt(0);

	List<CellStyle> styleMap = new ArrayList<CellStyle>();

	for (short i = 0; i < destinationWorkbook.getNumCellStyles(); i++) {
		styleMap.add(destinationWorkbook.getCellStyleAt(i));
	}

	copySheetSettings(destinationSheet, sheet);
	copySheet(styleMap, sheet, destinationSheet);
	copyPictures(destinationSheet, sheet);

	refreshFormula(destinationWorkbook);

	return destinationWorkbook;
}
 
Example #9
Source Project: Shop-for-JavaWeb   Author: EleTeam   File: ImportExcel.java    License: MIT License 6 votes vote down vote up
/**
 * 构造函数
 * @param path 导入文件对象
 * @param headerNum 标题行号,数据行号=标题行号+1
 * @param sheetIndex 工作表编号
 * @throws InvalidFormatException 
 * @throws IOException 
 */
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) 
		throws InvalidFormatException, IOException {
	if (StringUtils.isBlank(fileName)){
		throw new RuntimeException("导入文档为空!");
	}else if(fileName.toLowerCase().endsWith("xls")){    
		this.wb = new HSSFWorkbook(is);    
       }else if(fileName.toLowerCase().endsWith("xlsx")){  
       	this.wb = new XSSFWorkbook(is);
       }else{  
       	throw new RuntimeException("文档格式不正确!");
       }  
	if (this.wb.getNumberOfSheets()<sheetIndex){
		throw new RuntimeException("文档中没有工作表!");
	}
	this.sheet = this.wb.getSheetAt(sheetIndex);
	this.headerNum = headerNum;
	log.debug("Initialize success.");
}
 
Example #10
Source Project: MyBox   Author: Mararsh   File: GeographyCode.java    License: Apache License 2.0 6 votes vote down vote up
public static void writeExcel(File file, List<GeographyCode> codes) {
    try {
        if (file == null || codes == null || codes.isEmpty()) {
            return;
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        List<String> columns = writeExcelHeader(wb, sheet);
        for (int i = 0;
                i < codes.size();
                i++) {
            GeographyCode code = codes.get(i);
            writeExcel(sheet, i, code);
        }
        for (int i = 0;
                i < columns.size();
                i++) {
            sheet.autoSizeColumn(i);
        }
        try ( OutputStream fileOut = new FileOutputStream(file)) {
            wb.write(fileOut);
        }
    } catch (Exception e) {

    }
}
 
Example #11
Source Project: easypoi   Author: xiaolanglang   File: PoiPublicUtil.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 获取Excel2007图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) {
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
    for (POIXMLDocumentPart dr : sheet.getRelations()) {
        if (dr instanceof XSSFDrawing) {
            XSSFDrawing drawing = (XSSFDrawing) dr;
            List<XSSFShape> shapes = drawing.getShapes();
            for (XSSFShape shape : shapes) {
                XSSFPicture pic = (XSSFPicture) shape;
                XSSFClientAnchor anchor = pic.getPreferredSize();
                CTMarker ctMarker = anchor.getFrom();
                String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
                sheetIndexPicMap.put(picIndex, pic.getPictureData());
            }
        }
    }
    return sheetIndexPicMap;
}
 
Example #12
Source Project: birt   Author: eclipse   File: SingleSheetsReportTest.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testThreeTablesRenderPaginationBug() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReportDefaultTask("MultiSheets1.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {			
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Number Formats Test Report", workbook.getSheetAt(0).getSheetName());
		
		assertEquals(11, firstNullRow(workbook.getSheetAt(0)));
	} finally {
		inputStream.close();
	}
}
 
Example #13
Source Project: hadoopoffice   Author: ZuInnoTe   File: MSExcelWriter.java    License: Apache License 2.0 6 votes vote down vote up
private void finalizeWriteNotEncrypted() throws IOException {
	try {
		if ((this.signUtil!=null) && (this.currentWorkbook instanceof XSSFWorkbook)) {
			this.currentWorkbook.write(this.signUtil.getTempOutputStream()); // write to temporary file to sign it afterwards
		} else {
			this.currentWorkbook.write(this.oStream);
		}
	} finally {
		if ((this.oStream!=null) && (this.signUtil==null)) {
			this.oStream.close();
		}

	}
	
	
}
 
Example #14
Source Project: data-prep   Author: Talend   File: ExcelComparator.java    License: Apache License 2.0 6 votes vote down vote up
public static boolean compareTwoFile(XSSFWorkbook workbook1, XSSFWorkbook workbook2) {
    int nbSheet1 = workbook1.getNumberOfSheets();
    int nbSheet2 = workbook2.getNumberOfSheets();
    if (nbSheet1 != nbSheet2) {
        return false;
    }
    boolean equalFile = true;
    for (int i = 0; i <= nbSheet1 - 1; i++) {
        XSSFSheet sheet1 = workbook1.getSheetAt(i);
        XSSFSheet sheet2 = workbook2.getSheetAt(i);
        if (!compareTwoSheets(sheet1, sheet2)) {
            equalFile = false;
            break;
        }
    }

    return equalFile;
}
 
Example #15
Source Project: easyexcel   Author: alibaba   File: PoiTest.java    License: Apache License 2.0 6 votes vote down vote up
@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();
}
 
Example #16
Source Project: bamboobsc   Author: billchen198318   File: PerspectivesDashboardExcelCommand.java    License: Apache License 2.0 6 votes vote down vote up
private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putCharts(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "perspectives-dashboard.xlsx");
	file = null;
	return oid;
}
 
Example #17
Source Project: birt   Author: eclipse   File: AutoRowHeightsTest2.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testRunReportXlsx() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("AutoRowHeight2.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Auto RowHeight Report 2", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 1, this.firstNullRow(sheet));
		
		assertEquals( 2298, sheet.getRow(0).getHeight() );
	} finally {
		inputStream.close();
	}
}
 
Example #18
Source Project: birt   Author: eclipse   File: Issue94DisableRowSpanAutoHeight.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testIssue94DisableRowSpanAutoHeightAtRow() throws BirtException, IOException {

	debug = false;
	removeEmptyRows = false;
	spannedRowHeight = null;
	InputStream inputStream = runAndRenderReport("Issue94DisableRowSpanAutoHeightAtRow.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );

		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 9, this.lastRow(sheet));

		assertEquals( sheet.getRow(0).getHeight(), sheet.getRow(1).getHeight() );
		assertEquals( sheet.getRow(3).getHeight(), sheet.getRow(4).getHeight() );
		assertTrue( sheet.getRow(6).getHeight() < sheet.getRow(7).getHeight() );
		assertTrue( sheet.getRow(6).getHeight() > sheet.getRow(8).getHeight() );
		assertTrue( sheet.getRow(7).getHeight() > sheet.getRow(8).getHeight() );
	} finally {
		inputStream.close();
	}
}
 
Example #19
public MetaFile createMetaFile(XSSFWorkbook workBook) throws IOException, FileNotFoundException {

    Path path = MetaFiles.createTempFile("AccessConfigTemplate", ".xlsx");

    File file = path.toFile();
    FileOutputStream fout = new FileOutputStream(file);
    workBook.write(fout);
    fout.close();

    return metaFiles.upload(file);
  }
 
Example #20
Source Project: WhiteRabbit   Author: OHDSI   File: ReadXlsxFileWithHeader.java    License: Apache License 2.0 5 votes vote down vote up
public RowIterator() {
	try {
		XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
		XSSFSheet sheet = workbook.getSheetAt(0);
		iterator = sheet.iterator();
		fieldName2ColumnIndex = new HashMap<String, Integer>();
		for (Cell header : iterator.next())
			fieldName2ColumnIndex.put(header.toString(), fieldName2ColumnIndex.size());
	} catch (IOException e) {
		e.printStackTrace();
	}
}
 
Example #21
Source Project: poi   Author: T5750   File: TestExcelFormulaDemo.java    License: Apache License 2.0 5 votes vote down vote up
public static void readSheetWithFormula() {
	try {
		FileInputStream file = new FileInputStream(new File(
				TestUtil.DOC_PATH + File.separator + EXCEL_NAME
						+ Globals.SUFFIX_XLSX));
		// Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		FormulaEvaluator evaluator = workbook.getCreationHelper()
				.createFormulaEvaluator();
		// Get first/desired sheet from the workbook
		XSSFSheet sheet = workbook.getSheetAt(0);
		// Iterate through each rows one by one
		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			// For each row, iterate through all the columns
			Iterator<Cell> cellIterator = row.cellIterator();
			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				// If it is formula cell, it will be evaluated otherwise no
				// change will happen
				switch (evaluator.evaluateInCell(cell).getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print(cell.getStringCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_FORMULA:
					// Not again
					break;
				}
			}
			System.out.println("");
		}
		file.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 
Example #22
Source Project: easyexcel   Author: alibaba   File: Poi2Test.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void lastRowNumXSSF() throws IOException {
    String file = "D:\\test\\珠海.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    XSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 
Example #23
Source Project: JavaWeb   Author: tank2140896   File: PoiXSSFExcelUtil.java    License: Apache License 2.0 5 votes vote down vote up
public static void writeExcelObject(OutputStream outputStream,List<Object> data,String sheetName) throws Exception{
	XSSFWorkbook xssfWorkbook = writeSheetObject(data,sheetName);
	xssfWorkbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
	xssfWorkbook.close();
}
 
Example #24
Source Project: mzmine3   Author: mzmine   File: MenuExportToExcel.java    License: GNU General Public License v2.0 5 votes vote down vote up
public void exportDataToExcel(File f) {
  try {
    logger.info("retrieving data for export to excel");
    Object[][] data = chart.getDataArrayForExport();
    if (data != null) {
      f = FileAndPathUtil.getRealFilePath(f, "xlsx");
      logger.info("Exporting data to excel file: " + f.getAbsolutePath());
      XSSFWorkbook wb = excelWriter.exportDataArrayToFile(f, "xydata", data, false);
      excelWriter.closeWorkbook(wb);
    }
  } catch (InvalidFormatException | IOException e1) {
    logger.log(Level.WARNING, "Cannot export to excel", e1);
  }
}
 
Example #25
Source Project: java-master   Author: jufeng98   File: ExcelReader.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * @param excelType   excel文件类型
 * @param inputStream 文件流
 * @param beanClz     待转换的bean类型
 * @param headerNum   头部标题行数
 * @param footerNum   尾部非数据行数
 */
public ExcelReader(ExcelType excelType, InputStream inputStream, Class<T> beanClz, int headerNum, int footerNum) {
    try {
        if (ExcelType.XLS == excelType) {
            this.workbook = new HSSFWorkbook(inputStream);
        } else {
            this.workbook = new XSSFWorkbook(inputStream);
        }
        this.sheet = workbook.getSheetAt(0);
        this.headerNum = headerNum;
        this.footerNum = footerNum;
        this.beanClz = beanClz;
        init();
        context = new ExcelContext();
        context.setWorkbook(this.workbook);
        context.setSheet(this.sheet);
    } catch (Exception e) {
        throw new RuntimeException("construct workbook failed", e);
    } finally {
        if (this.workbook != null) {
            try {
                this.workbook.close();
            } catch (IOException ignored) {
            }
        }
    }
}
 
Example #26
Source Project: pentaho-reporting   Author: pentaho   File: RichTextRenderingIT.java    License: GNU Lesser General Public License v2.1 5 votes vote down vote up
@Test
public void testExcelRendering() throws Exception {
  URL resource = getClass().getResource( "rich-text-sample1.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  report.getReportConfiguration()
      .setConfigProperty( ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY, "true" );
  report.getReportHeader().getElement( 0 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.LTR );
  report.getReportHeader().getElement( 1 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.RTL );
  report.getReportHeader().removeElement( 0 );
  report.getReportHeader().getStyle().setStyleProperty( ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW );
  report.getReportFooter().clear();

  LogicalPageBox logicalPageBox = DebugReportRunner.layoutPage( report, 0 );

  RenderNode second = MatchFactory.findElementByName( logicalPageBox, "second" );
  assertTrue( second instanceof RenderBox );

  ExcelOutputProcessorMetaData metaData =
      new ExcelOutputProcessorMetaData( ExcelOutputProcessorMetaData.PAGINATION_FULL );
  metaData.initialize( report.getConfiguration() );

  XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
  ExcelColorProducer colorProducer = new StaticExcelColorSupport();
  ExcelFontFactory ff = new ExcelFontFactory( hssfWorkbook, colorProducer );
  CreationHelper ch = hssfWorkbook.getCreationHelper();
  ExcelTextExtractor te = new ExcelTextExtractor( metaData, colorProducer, ch, ff );

  Object compute = te.compute( (RenderBox) second );
  assertTrue( compute instanceof RichTextString );
  XSSFRichTextString rt = (XSSFRichTextString) compute;
  assertEquals( 4, rt.numFormattingRuns() );
}
 
Example #27
Source Project: wandora   Author: wandora-team   File: ExcelTopicTreeExtractor.java    License: GNU General Public License v3.0 5 votes vote down vote up
@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 
Example #28
Source Project: o2oa   Author: o2oa   File: AbstractExcel2007Writer.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( String fileName ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器,否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 
Example #29
Source Project: NoraUi   Author: NoraUi   File: Scenario.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * @param scenarioName
 *            name of scenario.
 * @param excelPath
 */
private void addXlsxFile(String scenarioName, String excelPath) {
    try (FileOutputStream outputStream = new FileOutputStream(excelPath); XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFCellStyle noraUiColumnStyle = workbook.createCellStyle();
        XSSFFont noraUiColumnFont = workbook.createFont();
        noraUiColumnFont.setColor(IndexedColors.BLACK.getIndex());
        noraUiColumnFont.setBold(true);
        noraUiColumnStyle.setFont(noraUiColumnFont);
        noraUiColumnStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 96, 88)));
        noraUiColumnStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFCellStyle noraUiResultColumnStyle = workbook.createCellStyle();
        XSSFFont noraUiResultColumnFont = workbook.createFont();
        noraUiResultColumnFont.setColor(IndexedColors.WHITE.getIndex());
        noraUiResultColumnFont.setBold(false);
        noraUiResultColumnStyle.setFont(noraUiResultColumnFont);
        noraUiResultColumnStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 128, 128)));
        noraUiResultColumnStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFSheet sheet = workbook.createSheet("NoraUi-" + scenarioName);
        Object[][] datas = { { "user", "password", "Result" }, { "user1", "password1" }, { "user2", "password2" } };
        int rowNum = 0;
        for (int i = 0; i < datas.length; i++) {
            Object[] data = datas[i];
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : data) {
                Cell cell = row.createCell(colNum++);
                if (i == 0) {
                    setHeaderStyleInXlsxFile(noraUiColumnStyle, noraUiResultColumnStyle, field, cell);
                }
                setRowValueInXlsxFile(field, cell);
            }
        }
        workbook.write(outputStream);
    } catch (IOException e) {
        log.error("IOException {}", e.getMessage(), e);
    }
}
 
Example #30
Source Project: hui-core-autoreport   Author: ithuhui   File: ConfigResolver.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 获取表格数据.
 *
 * @param chart     the chart
 * @param paramsMap the params map
 * @return the data map
 * @throws IOException the io exception
 * @since hui_project 1.0.0
 */
public List<ChartSeries> getDataMap(XSLFChart chart, Map<String, Object> paramsMap) throws IOException {
    POIXMLDocumentPart excelPart = chart.getRelations().get(0);
    InputStream excelInputStream = excelPart.getPackagePart().getInputStream();
    Workbook workbook = new XSSFWorkbook(excelInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    String config = sheet.getRow(25).getCell(25).getStringCellValue();
    excelInputStream.close();
    workbook.close();
    Map<String, String> configMap = getConfig(config);
    String sql = "";
    Map<String, String> colRowValueMap = new HashMap<>();
    for (String key : configMap.keySet()) {
        if (CHART_SQL.equals(key)) {
            sql = configMap.get(key);
        } else if (CHART_COL_ROW_VALUE.equals(key)) {
            String colRowValue = configMap.get(key);
            String colRowValueArray[] = colRowValue.split(":");
            for (String s : colRowValueArray) {
                String mapKey = s.substring(0, s.indexOf(","));
                String mapValue = s.substring(s.indexOf(",") + 1, s.length());
                colRowValueMap.put(mapKey, mapValue);
            }
        }

    }
    return analysisGraphData(sql, colRowValueMap, paramsMap);
}