Java XSSFWorkbook類代碼示例

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的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 opProg
 *            increases the progress by the number of examples to provide a more detailed
 *            progress.
 */
public static void writeXLSX(ExampleSet exampleSet, String sheetName, String dateFormat, String numberFormat,
		OutputStream outputStream, OperatorProgress opProg) 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 {
		XSSFWorkbook workbook = new XSSFWorkbook();

		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, opProg);
		workbook.write(outputStream);
	} finally {
		outputStream.flush();
		outputStream.close();
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
public static void writeXLSXFile() throws IOException {

			// @SuppressWarnings("resource")
			XSSFWorkbook wbObj = new XSSFWorkbook();
			XSSFSheet sheet = wbObj.createSheet(sheetName);
			for (int row = 0; row < tableData.size(); row++) {
				XSSFRow rowObj = sheet.createRow(row);
				rowData = tableData.get(row);
				for (int col = 0; col < rowData.size(); col++) {
					XSSFCell cell = rowObj.createCell(col);
					cell.setCellValue(rowData.get(col));
					logger.info("Writing " + row + " " + col + "  " + rowData.get(col));
				}
			}
			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wbObj.write(fileOut);
			wbObj.close();
			fileOut.flush();
			fileOut.close();
		}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
public static ArrayList readXlsx(String path) throws IOException {
    XSSFWorkbook xwb = new XSSFWorkbook(path);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row;
    String[] cell = new String[sheet.getPhysicalNumberOfRows() + 1];
    ArrayList<String> arrayList = new ArrayList<>();
    for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        cell[i] = "";
        row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
            cell[i] += row.getCell(j).toString();
            cell[i] += " | ";
        }
        arrayList.add(cell[i]);
    }
    return arrayList;
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
/**
 * @param wb
 * @param color
 * @param foreGround
 * @return
 */
public static XSSFCellStyle createBackgroundColorXSSFCellStyle(XSSFWorkbook wb,XSSFColor color,short foreGround){
	String message="XSSFWorkbook must not be null!";
	Objects.requireNonNull(wb, () -> message);
	XSSFCellStyle cellStyle=wb.createCellStyle();
	cellStyle.setWrapText(true);
	cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
	cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
	cellStyle.setBorderBottom(BorderStyle.THIN);
	cellStyle.setBorderLeft(BorderStyle.THIN);
	cellStyle.setBorderTop(BorderStyle.THIN);
	cellStyle.setBorderRight(BorderStyle.THIN);
	cellStyle.setFillForegroundColor(color);
	cellStyle.setFillPattern(foreGround);
	return cellStyle;
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
@SuppressWarnings("resource")
private List<Map<String,Object>> parseExcel(InputStream stream) throws Exception {
	List<Map<String,Object>> mapList=new ArrayList<Map<String,Object>>();
	XSSFWorkbook wb = new XSSFWorkbook(stream);
	for (int i = 0; i < wb.getNumberOfSheets(); i++) {
		XSSFSheet sheet = wb.getSheetAt(i);
		if (sheet == null) {
			continue;
		}
		String name = sheet.getSheetName();
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("name",name);
		map.put("data", buildVariables(sheet));
		mapList.add(map);
	}
	return mapList;
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
public static void createWorkBook() throws IOException {
	// ����excel������
	final Workbook wb = new XSSFWorkbook();
	// ����sheet��ҳ��
	final Sheet sheet1 = wb.createSheet("Sheet_1");
	final Sheet sheet2 = wb.createSheet("Sheet_2");

	for (int i = 0; i < 20; i = i + 2) {
		final Row row1 = sheet1.createRow(i);
		final Row row2 = sheet2.createRow(i);

		for (int j = 0; j < 10; j++) {

			row1.createCell(j).setCellValue(j + "new");
			row2.createCell(j).setCellValue(j + "This is a string");

		}
	}
	// ����һ���ļ� ����Ϊworkbooks.xlsx
	final FileOutputStream fileOut = new FileOutputStream("d:\\workbooks.xlsx");
	// �����洴���Ĺ�����������ļ���
	wb.write(fileOut);
	// �ر������
	fileOut.close();
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
/**
 * @param wb
 * @param fontFamily
 * @param height
 * @param color
 * @param bold
 * @return
 */
public static XSSFFont createColorXSSFFont(XSSFWorkbook wb,String fontFamily,short height,XSSFColor color,boolean bold){
	String message="XSSFWorkbook must not be null!";
	Objects.requireNonNull(wb, () -> message);
	XSSFFont cellfont=wb.createFont();
	cellfont.setFontName(fontFamily);
	cellfont.setFontHeightInPoints(height);
	cellfont.setColor(color);
	if(bold){
		cellfont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
	}
	return cellfont;
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
public static void main(String[] args) {
        //Create Workbook instance holding reference to .xlsx file
        try (FileInputStream file = new FileInputStream(
                new File("Sample.xlsx"))) {
            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each rows one by one
//            Iterator<Row> rowIterator = sheet.iterator();
            for(Row row : sheet) {
                for (Cell cell : row) {
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            out.print(cell.getStringCellValue() + "\t");
                            break;
                    }
                }
                out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
@Test
public void iteratedCorrectlyScatteredSheet() throws IOException {
    MatcherAssert.assertThat(
        "Each rows are iterated correctly when scattered on multiple sheets",
        new IteratorIterable<>(
            new ExcelIterator(
                new XSSFWorkbook(
                    new ResourceAsStream("excel/test-scattered-sheets.xlsx").stream()
                )
            )
        ),
        Matchers.contains(
            Matchers.is("jed,24.0"),
            Matchers.is("aisyl,20.0"),
            Matchers.is("linux,23.0"),
            Matchers.is("juan,29.0")
        )
    );
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
public FeedbackWriter(OutputStream outputStream) throws IOException {
    this.outputStream = outputStream;
    this.workbook = new XSSFWorkbook();

    styleCentered = workbook.createCellStyle();
    styleCentered.setAlignment(HorizontalAlignment.CENTER);

    styleWordWrap = workbook.createCellStyle();
    styleWordWrap.setWrapText(true);
    styleWordWrap.setVerticalAlignment(VerticalAlignment.TOP);
    styleWordWrap.setAlignment(HorizontalAlignment.LEFT);

    prepareCommentSheet();
    preparePlantMetadata();
    prepareBedMetadataSheet();

}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
List<ClassifiableText> xlsxToClassifiableTexts(File xlsxFile, int sheetNumber) throws IOException, EmptySheetException {
  if (xlsxFile == null ||
      sheetNumber < 1) {
    throw new IllegalArgumentException();
  }

  try (XSSFWorkbook excelFile = new XSSFWorkbook(new FileInputStream(xlsxFile))) {
    XSSFSheet sheet = excelFile.getSheetAt(sheetNumber - 1);

    // at least two rows
    if (sheet.getLastRowNum() > 0) {
      return getClassifiableTexts(sheet);
    } else {
      throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is empty");
    }
  } catch (IllegalArgumentException e) {
    throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is not found");
  }
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
@Test
public void test1() throws Exception {
    File file = folder.newFile("temp.xlsx");
    try (Workbook wb = new XSSFWorkbook()) {
        Sheet sheet1 = wb.createSheet("Sheet1");
        for (int row = 10; row <= 110; row++) {
            ExcelUtils.writeCell(sheet1, row, 20, "ISSUE-" + row);
        }
        try (OutputStream out = new FileOutputStream(file)) {
            wb.write(out);
        }
    }

    Context context = new MockContext();

    Read re = new Read(file.getAbsolutePath(), "Sheet1", "U");
    TextList list = re.execute(context, None.getInstance());
    assertNotNull(list);

    List<Text> texts = list.remaining(Hint.none());
    assertNotNull(texts);
    assertEquals(101, texts.size());
    assertEquals("ISSUE-10", texts.get(0).getText());
    assertEquals("ISSUE-110", texts.get(100).getText());
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
public CommentWriter(OutputStream outputStream) throws IOException{
    this.outputStream = outputStream;

    this.workbook = new XSSFWorkbook();
    this.sheet = workbook.createSheet("Comments");

    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("#");

    cell = row.createCell(1);
    cell.setCellValue("comment");

    cell = row.createCell(2);
    cell.setCellValue("timestamp");

    rowCount = 1;
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
/**
 * 構造函數
 *
 * @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.");
}
 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; //導入依賴的package包/類
@Restrict({@Group("ADMIN")})
public Result reportAllExams(String from, String to) throws IOException {

    final DateTime start = DateTime.parse(from, DTF);
    final DateTime end = DateTime.parse(to, DTF);

    List<ExamParticipation> participations = Ebean.find(ExamParticipation.class)
            .fetch("exam")
            .where()
            .gt("started", start)
            .lt("ended", end)
            .disjunction()
            .eq("exam.state", Exam.State.GRADED)
            .eq("exam.state", Exam.State.GRADED_LOGGED)
            .eq("exam.state", Exam.State.ARCHIVED)
            .endJunction()
            .findList();

    Workbook wb = new XSSFWorkbook();
    generateParticipationSheet(wb, participations, true);
    response().setHeader("Content-Disposition", "attachment; filename=\"all_exams.xlsx\"");
    return ok(encode(wb));
}