Java 类org.apache.poi.xssf.usermodel.XSSFWorkbook 实例源码

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

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

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

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

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

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

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

}

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

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

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

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

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

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

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

@Ignore
@Test
public void iteratedCorrectlyBetweenBlankSheet() throws IOException {
    MatcherAssert.assertThat(
        "Each rows are iterated correctly, between blank sheets",
        new IteratorIterable<>(
            new ExcelIterator(
                new XSSFWorkbook(
                    new ResourceAsStream("excel/test-between-blank-sheet.xlsx").stream()
                )
            )
        ),
        Matchers.contains(
            Matchers.is("jed,24.0"),
            Matchers.is("aisyl,20.0"),
            Matchers.is("linux,23.0"),
            Matchers.is("juan,29.0")
        )
    );
}

public static XSSFWorkbook create(List<RaceResult> raceResults) {
    XSSFWorkbook workbook = new XSSFWorkbook();

    helper = workbook.getCreationHelper();

    dateFormat = workbook.createCellStyle();
    dateFormat.setDataFormat(helper.createDataFormat().getFormat("m/d/yy"));

    twoDigitFormat = workbook.createCellStyle();
    twoDigitFormat.setDataFormat(helper.createDataFormat().getFormat("0.00"));

    threeDigitFormat = workbook.createCellStyle();
    threeDigitFormat.setDataFormat(helper.createDataFormat().getFormat("0.000"));

    commaNumberFormat = workbook.createCellStyle();
    commaNumberFormat.setDataFormat(helper.createDataFormat().getFormat("#,##0"));

    twoDigitCommaFormat = workbook.createCellStyle();
    twoDigitCommaFormat.setDataFormat(helper.createDataFormat().getFormat("#,##0.00"));

    createResultsSheets(raceResults, workbook);
    createBreedingSheet(raceResults, workbook);
    createWageringSheet(raceResults, workbook);

    return workbook;
}

public XSSFCellStyleLib(XSSFWorkbook wb){
        this.wb=wb;
        ICellStyleConvert convert=new CellStyleConvert();
        //添加cell樣式 
        convert.convert(CellStyle.NONE, new DefaultCellStyleImpl(wb),this);
        convert.convert(CellStyle.DEFAULT, new DefaultCellStyleImpl(wb),this);
        convert.convert(CellStyle.STANDARD, new TextCellStyleImpl(wb),this);
<<<<<<< HEAD
        convert.convert(CellStyle.TITLE, new TitleCellStyleImpl(wb),this);
=======
>>>>>>> fe2012a7f8558d8df36b789847bdc41c788d6eaf
    }

@Override
public DataSourceRowDefinition transform(final T parseContext) throws IOException {

    notNull(parseContext, "Mandatory argument 'parseContext' is missing.");
    try (final XSSFWorkbook workbook = new XSSFWorkbook(parseContext.getFile())) {
        return getRowDefinition(workbook.getSheetAt(parseContext.getSheetIndex())
                .getRow(parseContext.getHeaderRowIndex()),
                workbook.getSheetAt(parseContext.getSheetIndex())
        .getRow(parseContext.getDataRowIndex()));
    } catch (InvalidFormatException e) {
        throw new IOException("Error processing input file:"+parseContext.getFile().getName(), e);
    }
}

@Test
public void test02() throws Exception {
    File file = new File("C:\\Users\\he\\Downloads\\2016-05-10.xls");
    try (FileInputStream fis = new FileInputStream(file)) {
        XSSFWorkbook workbook = new XSSFWorkbook (fis);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        print(spreadsheet);
    }
}

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

private void testGenerator(ScriptGenerator generator) throws IOException {
    try (InputStream is = getClass().getResourceAsStream("/Users.xlsx");
            Workbook workbook = new XSSFWorkbook(is)) {
        Sheet sheet = workbook.getSheetAt(0);
        List<String> lines = generator.generate(sheet);
        assertThat(lines.size()).isEqualTo(44);
        assertThat(lines.get(0)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('t.wilson', 2237);");
        assertThat(lines.get(15)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('p.romero', 3657);");
        assertThat(lines.get(22)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('b.walton', 4352);");
        assertThat(lines.get(43)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('e.nash', 5565);");
    }
}

private void testGenerator(ScriptGenerator generator) throws IOException {
    try (InputStream is = getClass().getResourceAsStream("/Users.xlsx");
            Workbook workbook = new XSSFWorkbook(is)) {
        Sheet sheet = workbook.getSheetAt(0);
        List<String> lines = generator.generate(sheet);
        assertThat(lines.size()).isEqualTo(44);
        assertThat(lines.get(0)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('t.wilson', 2237);");
        assertThat(lines.get(15)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('p.romero', 3657);");
        assertThat(lines.get(22)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('b.walton', 4352);");
        assertThat(lines.get(43)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('e.nash', 5565);");
    }
}

@Override
void setSheet() {
    XSSFWorkbook wb = Objects.requireNonNull(this.getWb(),
            "XSSFWorkbook must not be null!");
    XSSFSheet sheet = wb.getSheetAt(0);
    this.setSheet(sheet);
    this.setSheetName(this.getSheetName());
}

/**
 * 抽象出图片生成业务代码
 * 
 * @throws IOException
 */
private void extractPicturePortion(String svgString, XSSFWorkbook wb,
        XSSFSheet sheet, int startCol, int endCol, int startRow, int endRow)
        throws IOException {
    // 图片
    if (org.apache.commons.lang3.StringUtils.isNotBlank(svgString)) {
        byte[] safeDataBytes = new BASE64Decoder().decodeBuffer(svgString);
        int pictureIdx = wb.addPicture(safeDataBytes,
                Workbook.PICTURE_TYPE_JPEG);
        CreationHelper helper = wb.getCreationHelper();
        // Create the drawing patriarch. This is the top level container for
        // all shapes.
        Drawing drawing = sheet.createDrawingPatriarch();
        // add a picture shape
        ClientAnchor anchor = helper.createClientAnchor();
        // set top-left corner of the picture,
        // subsequent call of Picture#resize() will operate relative to it
        anchor.setCol1(startCol);
        anchor.setCol2(endCol);
        anchor.setRow1(startRow);
        anchor.setRow2(endRow);
        anchor.setDx1(0);
        anchor.setDy1(0);
        anchor.setDx2(0);
        anchor.setDy2(0);
        anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        pict.resize(1);
    }
}

public static XSSFCellStyle createCenterXSSFCellStyle(XSSFWorkbook wb){
    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);
    return cellStyle;
}

/**
 * @param wb
 * @return {code XSSFFont}
 */
public static XSSFFont createXSSFFont(XSSFWorkbook wb){
    String message="XSSFWorkbook must not be null!";
    Objects.requireNonNull(wb, () -> message);
    XSSFFont cellfont=wb.createFont();
    cellfont.setFontName("新宋体");
    cellfont.setFontHeightInPoints((short) 12);
    cellfont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    cellfont.setColor(new XSSFColor(new Color(50,73,38)));

    return cellfont;
}

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

/**
 * @param sheet 
 * @param pos 位置【row,col】
 * @param style
 * @param v
 */
public static void setCell(final XSSFWorkbook wb,final XSSFSheet sheet,int[] pos,CellStyle style,FontStyle font,String v){
    XSSFRow row = sheet.getRow(pos[0]);
    XSSFCell cell = CellValueUtil.createCellIfNotPresent(row, pos[1]);
    XSSFCellStyle _style = Objects
            .requireNonNull(new XSSFCellStyleLib(wb).get(style),
                    "specified style not defined!");
    XSSFFont _font = Objects
            .requireNonNull(new XSSFFontStyleLib(wb).get(font),
                    "specified font not defined!");
    _style.setFont(_font);
    setCellProperties(cell, v, _style);
}

public static XSSFCellStyle createXSSFCellStyle(XSSFWorkbook wb){
    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);
    return cellStyle;
}

public static XSSFCellStyle createCenterXSSFCellStyle(XSSFWorkbook wb){
    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);
    return cellStyle;
}

/**
 * 返回excel内容字体样式
 * @param wb
 * @return
 */
public static XSSFCellStyle getcontentCellStyle(XSSFWorkbook wb){
    String message="XSSFWorkbook must not be null!";
    Objects.requireNonNull(wb, () -> message);
    XSSFCellStyle contentStyle=createCenterXSSFCellStyle(wb);
    //fill enlarged font
    XSSFFont enlargedFont=XSSFontUtil.createColorXSSFFont(wb, "新宋体", (short) 16, new XSSFColor(Color.WHITE), true);
    contentStyle.setFont(enlargedFont);
    return contentStyle;
}

public static XSSFCellStyle createXSSFCellStyle(XSSFWorkbook wb){
    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);
    return cellStyle;
}

/**
 * 设置单行全部内容
 * @param sheet
 * @param wb
 * @param rowNum
 * @param kVal
 */
public static void setRowMutipleColContent(XSSFSheet sheet,XSSFWorkbook wb,int rowNum,final Map<Integer,String> kVal){
    String message="XSSFSheet must not be null!";
    Objects.requireNonNull(sheet, () -> message);
    XSSFRow row=sheet.getRow(rowNum);
    if(!kVal.isEmpty()){
        kVal.forEach((k,v)->{
            setRowContents(row,k,v,wb);
        });
    }

}

public XSSFCellStyleLib(XSSFWorkbook wb){
    this.wb=wb;
    ICellStyleConvert convert=new CellStyleConvert();
    //添加cell樣式 
    convert.convert(CellStyle.NONE, new DefaultCellStyleImpl(wb),this);
    convert.convert(CellStyle.DEFAULT, new DefaultCellStyleImpl(wb),this);
    convert.convert(CellStyle.STANDARD, new TextCellStyleImpl(wb),this);
}

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

/**
 * @throws IOException
 */
@SneakyThrows
public static ExportExcelConfigBuilder fromWorkbook(FileSource fileSource) {
    FileType fileType = FileTypeGuesser.guess(fileSource);
    if (fileType == FileType.ZIP) {
        return fromWorkbook(new XSSFWorkbook(fileSource.toInputStream()));
    }
    if (fileType == FileType.CFBF) {
        return fromWorkbook(new HSSFWorkbook(fileSource.toInputStream()));
    }
    throw new IllegalArgumentException("The source file should be either a XLSX or a XLS file");
}

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

@Override
public ArrayList<Ciudadano> leerCiudadanos(ArrayList<Ciudadano> ciudadanos, String ruta) {
    try {
        FileInputStream file = new FileInputStream(new File(ruta));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            ArrayList<Object> aux = new ArrayList<Object>();
            for (int i = 0; i < 7; i++) {
                aux.add(row.getCell(i) != null ? row.getCell(i).toString() : null);
            }

            String nombre = row.getCell(0) != null ? row.getCell(0).toString() : null;
            if (nombre != null && nombre.equals("Nombre"))
                continue;
            String fecha = row.getCell(3) != null ? row.getCell(3).toString() : null;

            Date date = new SimpleDateFormat("dd-MMM-yyyy").parse(fecha);
            java.sql.Date nacimiento = new java.sql.Date(date.getTime());

            Ciudadano ciudadano = new Ciudadano(aux.get(0).toString(), aux.get(1).toString(), aux.get(2).toString(),
                    aux.get(4).toString(), aux.get(5).toString(), aux.get(6).toString(), nacimiento);

            ciudadanos.add(ciudadano);
        }

        file.close();
        workbook.close();
    } catch (Exception e) {
        System.err.println("Error al leer del excel xlsx");
    }
    return ciudadanos;
}

/**
    Uses Apache POI to extract information from xlsx file into a 2D array.
    Here is where we got our starter code: http://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/
    Look at example number 4, Apache POI library – Reading an Excel file.

    This file originally just printed data, that is why there are several commented out lines in the code.
    We have repurposed this method to put all data into a 2D String array and return it.
 */
public String[][] extractFromXLSX(InputStream excelFile) throws NotOfficeXmlFileException {
    try {
        Workbook workbook = new XSSFWorkbook(excelFile);
        Sheet datatypeSheet = workbook.getSheetAt(0);

        String[][] cellValues = new String[datatypeSheet.getLastRowNum() + 1]
                [max(max(datatypeSheet.getRow(1).getLastCellNum(), datatypeSheet.getRow(2).getLastCellNum()),
                datatypeSheet.getRow(3).getLastCellNum())];

        for (Row currentRow : datatypeSheet) {
            //cellValues[currentRow.getRowNum()] = new String[currentRow.getLastCellNum()];

            for (Cell currentCell : currentRow) {

                //getCellTypeEnum shown as deprecated for version 3.15
                //getCellTypeEnum ill be renamed to getCellType starting from version 4.0
                if (currentCell.getCellTypeEnum() == CellType.STRING) {
                    cellValues[currentCell.getRowIndex()][currentCell.getColumnIndex()] = currentCell.getStringCellValue();
                } else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
                    cellValues[currentCell.getRowIndex()][currentCell.getColumnIndex()] =
                            Integer.toString((int) Math.round(currentCell.getNumericCellValue()));
                }

            }

        }
        return cellValues;
    } catch (IOException e) {
        System.out.println("EVERYTHING BLEW UP STOP STOP STOP");
        e.printStackTrace();
        return null; //TODO: this should not return null. This should continue.
    }

}

public static Workbook createWookBook() {
    // Time.getTime();
    // String currrentPath = path + "/" + Time.year + "/" + Time.month;
    // FileOperation.createDir(currrentPath);
    // ����excel������
    final Workbook wb = new XSSFWorkbook();

    // ����sheet��ҳ��
    final Sheet sheet1 = wb.createSheet("Data");
    return wb;
}