Examples with XSSFWorkbook org.apache.poi.xssf.usermodel.XSSFWorkbook used on opensource projects

Example 91 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project hale by halestudio.

the class XLSLookupTableWriter method execute.

/**
 * @see eu.esdihumboldt.hale.common.core.io.impl.AbstractIOProvider#execute(eu.esdihumboldt.hale.common.core.io.ProgressIndicator,
 *      eu.esdihumboldt.hale.common.core.io.report.IOReporter)
 */
@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException {
    Workbook workbook;
    // write xls file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
        workbook = new HSSFWorkbook();
    } else // write xlsx file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
        workbook = new XSSFWorkbook();
    } else {
        reporter.error(new IOMessageImpl("Content type is invalid!", null));
        reporter.setSuccess(false);
        return reporter;
    }
    Sheet sheet = workbook.createSheet();
    workbook.setSheetName(0, "Lookup table");
    Row row = null;
    Cell cell = null;
    DataFormat df = workbook.createDataFormat();
    // create cell style of the header
    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    // use bold font
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // set a medium border
    headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    // set cell data format to text
    headerStyle.setDataFormat(df.getFormat("@"));
    // create cell style
    CellStyle rowStyle = workbook.createCellStyle();
    // set thin border around the cell
    rowStyle.setBorderBottom(CellStyle.BORDER_THIN);
    rowStyle.setBorderLeft(CellStyle.BORDER_THIN);
    rowStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    rowStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    rowStyle.setWrapText(true);
    Map<Value, Value> table = getLookupTable().getTable().asMap();
    int rownum = 0;
    // write header
    row = sheet.createRow(rownum++);
    cell = row.createCell(0);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_SOURCE_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);
    cell = row.createCell(1);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_TARGET_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);
    for (Value key : table.keySet()) {
        // create a row
        row = sheet.createRow(rownum);
        cell = row.createCell(0);
        cell.setCellValue(key.as(String.class));
        cell.setCellStyle(rowStyle);
        Value entry = table.get(key);
        cell = row.createCell(1);
        cell.setCellValue(entry.as(String.class));
        cell.setCellStyle(rowStyle);
        rownum++;
    }
    // write file
    FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath());
    workbook.write(out);
    out.close();
    reporter.setSuccess(true);
    return reporter;
}

Example 92 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project TeachingInSimulation by ScOrPiOzzy.

the class ExcelUtil method readExcelSheet.

public static Object[][] readExcelSheet(String fiPath, String sheetName, int columnNum) {
    Workbook wb = null;
    FileInputStream fo = null;
    try {
        int rowNum = 1;
        fo = new FileInputStream(fiPath);
        try {
            wb = new HSSFWorkbook(fo);
        } catch (Exception e) {
            try {
                fo = new FileInputStream(fiPath);
                wb = new XSSFWorkbook(fo);
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet == null) {
            System.out.println("工作簿为空！");
            return null;
        }
        while (sheet.getRow(rowNum) != null) {
            rowNum++;
        }
        return readExcelSheet(fiPath, sheetName, rowNum, columnNum);
    // -------------------------------------------------------------
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        return null;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    } finally {
        try {
            if (fo != null) {
                fo.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            fo = null;
        }
    }
}

Example 93 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project TeachingInSimulation by ScOrPiOzzy.

the class ExcelUtil method newSheet.

/**
 * 在指定Excel中创建Sheet,文件必须存在
 * @param version Excel文件版本
 * @param filePath 目标文件路径（含文件名）
 * @param sheetNames 希望作成的sheetName可是是String数组
 * @param overWriteFlag 是否覆盖
 * @return RETURN_SUCCESS:创建成功 1:文件已经存在
 * @throws IOException
 */
public static int newSheet(String version, String filePath, boolean overWriteFlag, String... sheetNames) {
    // 检查sheet名是否已经存在，根据overWirteFlag进行处理
    FileInputStream fi = null;
    FileOutputStream foDel = null;
    Workbook wbIn = null;
    try {
        fi = new FileInputStream(filePath);
        if (version.equals(EXCEL_TYPE_2007)) {
            wbIn = new XSSFWorkbook(fi);
        } else {
            wbIn = new HSSFWorkbook(fi);
        }
        for (int i = 0; i < sheetNames.length; i++) {
            int sheetIndex = wbIn.getSheetIndex(sheetNames[i]);
            if (sheetIndex != -1) {
                System.out.println("工作簿已经存在！位于第" + (sheetIndex + 1) + "张");
                if (overWriteFlag) {
                    wbIn.removeSheetAt(sheetIndex);
                    System.out.println("删除成功!");
                } else {
                    return RETURN_FAILE;
                }
            }
        }
        foDel = new FileOutputStream(filePath);
        wbIn.write(foDel);
        foDel.flush();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (foDel != null) {
                foDel.close();
            }
            if (wbIn != null) {
                wbIn.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    // 插入新的sheet
    FileOutputStream fo = null;
    try {
        fo = new FileOutputStream(filePath);
        for (int i = 0; i < sheetNames.length; i++) {
            wbIn.createSheet(WorkbookUtil.createSafeSheetName(sheetNames[i]));
        // wbOut.setSheetName(totalSheetsNum,
        // WorkbookUtil.createSafeSheetName(sheetNames[i]));
        }
        wbIn.write(fo);
        fo.flush();
    } catch (Exception e) {
        e.printStackTrace();
        return RETURN_ERROR;
    } finally {
        try {
            if (fo != null) {
                fo.close();
            }
            if (fi != null) {
                fi.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    return RETURN_SUCCESS;
}

Example 94 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project pmph by BCSquad.

the class OrgUserServiceImpl method importExcel.

@SuppressWarnings("resource")
@Override
public List<OrgVO> importExcel(MultipartFile file) throws CheckedServiceException {
    String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
    Workbook workbook = null;
    InputStream in = null;
    try {
        in = file.getInputStream();
    } catch (FileNotFoundException e) {
        throw new CheckedServiceException(CheckedExceptionBusiness.EXCEL, CheckedExceptionResult.NULL_PARAM, "获取上传的文件失败");
    } catch (IOException e) {
        throw new CheckedServiceException(CheckedExceptionBusiness.EXCEL, CheckedExceptionResult.ILLEGAL_PARAM, "读取文件失败");
    }
    try {
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(in);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(in);
        } else {
            throw new CheckedServiceException(CheckedExceptionBusiness.EXCEL, CheckedExceptionResult.ILLEGAL_PARAM, "读取的不是Excel文件");
        }
    } catch (IOException e) {
        throw new CheckedServiceException(CheckedExceptionBusiness.EXCEL, CheckedExceptionResult.ILLEGAL_PARAM, "文件读取失败");
    } catch (OfficeXmlFileException e) {
        throw new CheckedServiceException(CheckedExceptionBusiness.EXCEL, CheckedExceptionResult.ILLEGAL_PARAM, "此文档不是对应的.xls或.xlsx的Excel文档，请修改为正确的后缀名再进行上传");
    }
    PageParameter<OrgAndOrgUserVO> pageParameter = new PageParameter<>();
    pageParameter.setParameter(new OrgAndOrgUserVO());
    pageParameter.setStart(null);
    List<OrgAndOrgUserVO> orgUsers = orgUserDao.getListAllOrgUser(pageParameter);
    List<OrgVO> list = new ArrayList<>();
    for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
        Sheet sheet = workbook.getSheetAt(numSheet);
        if (null == sheet) {
            continue;
        }
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            OrgVO orgVO = new OrgVO();
            Row row = sheet.getRow(rowNum);
            if (null == row) {
                break;
            }
            Cell second = row.getCell(1);
            String orgName = StringUtil.getCellValue(second);
            if (StringUtil.isEmpty(orgName)) {
                throw new CheckedServiceException(CheckedExceptionBusiness.EXCEL, CheckedExceptionResult.NULL_PARAM, "Excel文件里序号为" + rowNum + "的机构名称为空");
            }
            orgVO.setOrgName(orgName);
            if (null == orgUsers || orgUsers.isEmpty()) {
                list.add(orgVO);
                continue;
            }
            for (OrgAndOrgUserVO orgAndOrgUserVO : orgUsers) {
                if (orgName.equals(orgAndOrgUserVO.getOrgName())) {
                    orgVO.setUsername(orgAndOrgUserVO.getUsername());
                    orgVO.setOrgTypeName(orgAndOrgUserVO.getOrgTypeName());
                    orgVO.setRealname(orgAndOrgUserVO.getRealname());
                    break;
                }
            }
            list.add(orgVO);
        }
    }
    return list;
}

Example 95 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project jgnash by ccavanaugh.

the class BudgetResultsExport method exportBudgetResultsModel.

/**
     * Exports a {@code BudgetResultsModel} to a spreadsheet.
     * 
     * @param file File to save to
     * @param model Results model to export
     * @return Error message
     */
public static String exportBudgetResultsModel(final Path file, final BudgetResultsModel model) {
    String message = null;
    final ResourceBundle rb = ResourceUtils.getBundle();
    final String extension = FileUtils.getFileExtension(file.toString());
    try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) {
        final CreationHelper createHelper = wb.getCreationHelper();
        // create a new sheet
        final Sheet s = wb.createSheet(model.getBudget().getName());
        // create header cell styles
        final CellStyle headerStyle = wb.createCellStyle();
        // create 2 fonts objects
        final Font amountFont = wb.createFont();
        final Font headerFont = wb.createFont();
        amountFont.setFontHeightInPoints((short) 10);
        amountFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setFontHeightInPoints((short) 11);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // Set the other cell style and formatting
        headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headerStyle.setBorderTop(CellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headerStyle.setBorderRight(CellStyle.BORDER_THIN);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        DataFormat df_header = wb.createDataFormat();
        headerStyle.setDataFormat(df_header.getFormat("text"));
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        int row = 0;
        Row r = s.createRow(row);
        // create period headers
        for (int i = 0; i < model.getDescriptorList().size(); i++) {
            Cell c = r.createCell(i * 3 + 1);
            c.setCellValue(createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription()));
            c.setCellStyle(headerStyle);
            s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3));
        }
        {
            int col = model.getDescriptorList().size() * 3 + 1;
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary")));
            c.setCellStyle(headerStyle);
            s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2));
        }
        // create results header columns
        row++;
        r = s.createRow(row);
        {
            Cell c = r.createCell(0);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account")));
            c.setCellStyle(headerStyle);
            for (int i = 0; i <= model.getDescriptorList().size(); i++) {
                c = r.createCell(i * 3 + 1);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted")));
                c.setCellStyle(headerStyle);
                c = r.createCell(i * 3 + 2);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Actual")));
                c.setCellStyle(headerStyle);
                c = r.createCell(i * 3 + 3);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining")));
                c.setCellStyle(headerStyle);
            }
        }
        // must sort the accounts, otherwise child structure is not correct
        List<Account> accounts = new ArrayList<>(model.getAccounts());
        accounts.sort(Comparators.getAccountByTreePosition(Comparators.getAccountByCode()));
        // create account rows
        for (final Account account : accounts) {
            final DataFormat df = wb.createDataFormat();
            final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
            final String pattern = format.toLocalizedPattern().replace("¤", account.getCurrencyNode().getPrefix());
            final CellStyle amountStyle = wb.createCellStyle();
            amountStyle.setFont(amountFont);
            amountStyle.setDataFormat(df.getFormat(pattern));
            // Sets cell indentation, only impacts display if users changes the cell formatting to be left aligned.
            amountStyle.setIndention((short) (model.getDepth(account) * 2));
            row++;
            int col = 0;
            r = s.createRow(row);
            CellStyle cs = wb.createCellStyle();
            cs.cloneStyleFrom(headerStyle);
            cs.setAlignment(CellStyle.ALIGN_LEFT);
            cs.setIndention((short) (model.getDepth(account) * 2));
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(account.getName()));
            c.setCellStyle(cs);
            List<CellReference> budgetedRefList = new ArrayList<>();
            List<CellReference> changeRefList = new ArrayList<>();
            List<CellReference> remainingRefList = new ArrayList<>();
            for (int i = 0; i < model.getDescriptorList().size(); i++) {
                BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getBudgeted().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference budgetedRef = new CellReference(row, col);
                budgetedRefList.add(budgetedRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getChange().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference changeRef = new CellReference(row, col);
                changeRefList.add(changeRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_FORMULA);
                c.setCellStyle(amountStyle);
                c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());
                CellReference remainingRef = new CellReference(row, col);
                remainingRefList.add(remainingRef);
            }
            // add summary columns                               
            addSummaryCell(r, ++col, budgetedRefList, amountStyle);
            addSummaryCell(r, ++col, changeRefList, amountStyle);
            addSummaryCell(r, ++col, remainingRefList, amountStyle);
        }
        // add group summary rows
        for (final AccountGroup group : model.getAccountGroupList()) {
            final DataFormat df = wb.createDataFormat();
            final CellStyle amountStyle = wb.createCellStyle();
            amountStyle.setFont(amountFont);
            amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            amountStyle.setBorderBottom(CellStyle.BORDER_THIN);
            amountStyle.setBorderTop(CellStyle.BORDER_THIN);
            amountStyle.setBorderLeft(CellStyle.BORDER_THIN);
            amountStyle.setBorderRight(CellStyle.BORDER_THIN);
            final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
            final String pattern = format.toLocalizedPattern().replace("¤", model.getBaseCurrency().getPrefix());
            amountStyle.setDataFormat(df.getFormat(pattern));
            row++;
            int col = 0;
            r = s.createRow(row);
            CellStyle cs = wb.createCellStyle();
            cs.cloneStyleFrom(headerStyle);
            cs.setAlignment(CellStyle.ALIGN_LEFT);
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(group.toString()));
            c.setCellStyle(cs);
            List<CellReference> budgetedRefList = new ArrayList<>();
            List<CellReference> changeRefList = new ArrayList<>();
            List<CellReference> remainingRefList = new ArrayList<>();
            for (int i = 0; i < model.getDescriptorList().size(); i++) {
                BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getBudgeted().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference budgetedRef = new CellReference(row, col);
                budgetedRefList.add(budgetedRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getChange().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference changeRef = new CellReference(row, col);
                changeRefList.add(changeRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_FORMULA);
                c.setCellStyle(amountStyle);
                c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());
                CellReference remainingRef = new CellReference(row, col);
                remainingRefList.add(remainingRef);
            }
            // add summary columns                               
            addSummaryCell(r, ++col, budgetedRefList, amountStyle);
            addSummaryCell(r, ++col, changeRefList, amountStyle);
            addSummaryCell(r, ++col, remainingRefList, amountStyle);
        }
        // force evaluation
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();
        short columnCount = s.getRow(1).getLastCellNum();
        // autosize all of the columns + 10 pixels
        for (int i = 0; i <= columnCount; i++) {
            s.autoSizeColumn(i);
            s.setColumnWidth(i, s.getColumnWidth(i) + 10);
        }
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles());
        // Save
        String filename = file.toString();
        if (wb instanceof XSSFWorkbook) {
            filename = FileUtils.stripFileExtension(filename) + ".xlsx";
        } else {
            filename = FileUtils.stripFileExtension(filename) + ".xls";
        }
        try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) {
            wb.write(out);
        } catch (final Exception e) {
            Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
            message = e.getLocalizedMessage();
        }
    } catch (IOException e) {
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
    return message;
}