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

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

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

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

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

/**
 * 构造函数
 * @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.");
}
 

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

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

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

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

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

	}
	
	
}
 

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) {

    }
}
 

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

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

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

/**
 * 获取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;
}
 

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

@SuppressWarnings("deprecation")
public void readXLSX(String path, int num) throws InvalidFormatException, IOException {
	File file = new File(path);
	@SuppressWarnings("resource")
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(num);

	int rowstart = xssfSheet.getFirstRowNum();
	int rowEnd = xssfSheet.getLastRowNum();
	for (int i = rowstart; i <= rowEnd; i++) {
		XSSFRow row = xssfSheet.getRow(i);
		if (null == row) {
			continue;
		}
		int cellStart = row.getFirstCellNum();
		int cellEnd = row.getLastCellNum();

		for (int k = cellStart; k <= cellEnd; k++) {
			XSSFCell cell = row.getCell(k);
			if (null == cell) {
				continue;
			}

			switch (cell.getCellTypeEnum()) {
				case NUMERIC: // 数字
					System.out.print(cell.getNumericCellValue() + "   ");
					break;
				case STRING: // 字符串
					System.out.print(cell.getStringCellValue() + "   ");
					break;
				case BOOLEAN: // Boolean
					System.out.println(cell.getBooleanCellValue() + "   ");
					break;
				case FORMULA: // 公式
					System.out.print(cell.getCellFormula() + "   ");
					break;
				case BLANK: // 空值
					System.out.println(" ");
					break;
				case ERROR: // 故障
					System.out.println(" ");
					break;
				default:
					System.out.print("未知类型   ");
					break;
			}

		}
		System.out.print("\n");
	}

}
 

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

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

/**
 * Add styled row to a sheet.
 * @throws IOException If fails
 */
@Test
public void addsRowWithStyleToSheet() throws IOException {
    try (final Workbook wbook = new XSSFWorkbook()) {
        final Row row =
            new XsRow()
                .with(new TextCells("a", "b", "c"))
                .with(
                    new TextCell("text")
                        .with(
                            new XsStyle(
                                new ForegroundColor(
                                    IndexedColors.GOLD.getIndex()
                                )
                            )
                        )
                )
                .with(
                    new XsStyle(
                        new ForegroundColor(
                            IndexedColors.GREY_50_PERCENT.getIndex()
                        )
                    )
                )
                .attachTo(
                    wbook.createSheet()
                );
        MatcherAssert.assertThat(
            row.getCell((int) row.getLastCellNum() - 2)
                .getCellStyle().getFillForegroundColor(),
            Matchers.equalTo(IndexedColors.GREY_50_PERCENT.getIndex())
        );
        MatcherAssert.assertThat(
            row.getCell((int) row.getLastCellNum() - 1)
                .getCellStyle().getFillForegroundColor(),
            Matchers.equalTo(IndexedColors.GOLD.getIndex())
        );
    }
}
 

@Test
public void addsCellContainingNumberInPosition() throws IOException {
    try (final Workbook workbook = new XSSFWorkbook()) {
        final int column = 2;
        final int expected = 1;
        final Double number = 5.0;
        final Cell cell = new NumberCell(column, number).attachTo(
            workbook.createSheet().createRow(0)
        );
        MatcherAssert.assertThat(
            cell.getColumnIndex(),
            Matchers.equalTo(expected)
        );
    }
}
 

public static void writeExcel(HttpServletResponse response,List<String> list) throws Exception {
	response.setContentType("application/vnd.ms-excel");//文件格式,此处设置为excel
	response.setHeader("Content-Disposition","attachment;filename=file.xls");//此处设置了下载文件的默认名称
	ServletOutputStream sos = response.getOutputStream();
    //创建一个新的excel
	XSSFWorkbook wb = new XSSFWorkbook();//XSSFWorkbook
	/**
	 * 采用现成Excel模板
	 * 用这种方式得先保证每个cell有值,不然会报空指针
	 * 有时我们用row.getCell(i)会得到null,那么此时就要用Iterator<Cell> it = row.cellIterator();
	 * XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File("D://a.xlsx")));
	 * XSSFSheet sheet = wb.getSheet("Sheet1");
	 * row[i] = sheet.getRow(i);
	 * headerCell[j] = row[i].getCell(j);
	 */
	//创建sheet页
	XSSFSheet sheet = wb.createSheet("sheet1");//sheet名
	//创建行数
	XSSFRow[] row = new XSSFRow[list.size()];
	//插入数据
	for (int i = 0; i < row.length; i++) {
		row[i] = sheet.createRow(i);
		sheet.setDefaultColumnWidth(30);//设置列的长度
		String info[] = list.get(i).split(",");
		XSSFCell[] headerCell = new XSSFCell[info.length];
		for (int j = 0; j < headerCell.length; j++) {
			headerCell[j] = row[i].createCell(j);
			headerCell[j].setCellValue(new XSSFRichTextString(info[j]));
			/**设置模板样式*/
			//headerCell[j].setCellStyle(setStyle(wb));
		}
	}
	wb.write(sos);
	wb.close();
    sos.flush();
    sos.close();
    response.flushBuffer();
}
 

@Test
public void test3556() throws IOException, InvalidFormatException {
    String file = "D://test/dataformat1.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    DataFormatter d = new DataFormatter(Locale.CHINA);

    for (int i = 0; i < xssfSheet.getLastRowNum(); i++) {
        Row row = xssfSheet.getRow(i);
        System.out.println(d.formatCellValue(row.getCell(0)));
    }

}
 

@Override
public boolean write(JSONObject object, OutputStream outputStream) {
    try (Workbook workbook = new XSSFWorkbook()) {
        JSONArray sheets = object.getJSONArray("sheets");
        for (int sheetIndex = 0, sheetSize = sheets.size(); sheetIndex < sheetSize; sheetIndex++) {
            JSONObject sheetJson = sheets.getJSONObject(sheetIndex);
            Sheet sheet = workbook.createSheet(sheetJson.containsKey("name") ? sheetJson.getString("name") : ("sheet " + sheetIndex));
            int firstRow = sheetJson.containsKey("first") ? sheetJson.getIntValue("first") : 0;
            JSONArray rows = sheetJson.getJSONArray("rows");
            for (int rowIndex = 0, rowSize = rows.size(); rowIndex < rowSize; rowIndex++) {
                JSONObject rowJson = rows.getJSONObject(rowIndex);
                int firstCol = rowJson.containsKey("first") ? rowJson.getIntValue("first") : 0;
                Row row = sheet.createRow(firstRow + rowIndex);
                JSONArray cells = rowJson.getJSONArray("cells");
                for (int cellIndex = 0, cellSize = cells.size(); cellIndex < cellSize; cellIndex++) {
                    JSONObject cellJson = cells.getJSONObject(cellIndex);
                    Cell cell = row.createCell(firstCol + cellIndex);
                    cell.setCellValue(cellJson.getString("value"));
                }
            }
        }
        workbook.write(outputStream);
        outputStream.close();

        return true;
    } catch (Throwable throwable) {
        logger.warn(throwable, "输出Excel时发生异常！");

        return false;
    }
}
 

public static void setCellPicture(XSSFWorkbook wb, XSSFSheet sh, byte[] iconBytes, int row, int col) throws Exception {
       int myPictureId = wb.addPicture(iconBytes, XSSFWorkbook.PICTURE_TYPE_PNG);
       
       XSSFDrawing drawing = sh.createDrawingPatriarch();
       XSSFClientAnchor myAnchor = new XSSFClientAnchor();
      
       myAnchor.setCol1(col);
       myAnchor.setRow1(row);
       
       XSSFPicture myPicture = drawing.createPicture(myAnchor, myPictureId);
       myPicture.resize();
}
 

@Test
public void lastRowNumXSSF() throws IOException {
    String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.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());
}
 

protected Workbook openWorkbook( File templateFile ) throws IOException {
	InputStream stream = new FileInputStream( templateFile );
	try {
		return new XSSFWorkbook( stream );
	} finally {
		stream.close();
	}
}
 

protected Workbook getTemplateSource(String url, HttpServletRequest request) throws Exception {
	LocalizedResourceHelper helper = new LocalizedResourceHelper(getApplicationContext());
	Locale userLocale = RequestContextUtils.getLocale(request);
	Resource inputFile = helper.findLocalizedResource(url, EXTENSION, userLocale);

	// Create the Excel document from the source.
	if (logger.isDebugEnabled()) {
		logger.debug("Loading Excel workbook from " + inputFile);
	}
	//POIFSFileSystem fs = new POIFSFileSystem(inputFile.getInputStream());
	return new XSSFWorkbook(inputFile.getInputStream());
}
 

@Test
public void testExcel2() throws IOException {
	String filepath = "E:\\ConvertTester\\excel\\abcd.xlsx";
	File originFile = new File(filepath);
	InputStream in = new FileInputStream(originFile);
	XSSFWorkbook workbook = new XSSFWorkbook(in);
	XSSFSheet sheet = workbook.createSheet("testSheet");

	XSSFDrawing drawing = sheet.createDrawingPatriarch();
	
	XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 2, 4, (short) 13, 26);

}
 

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