Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. XSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook.

Prototype

public XSSFWorkbook()

Source Link

Document

Create a new SpreadsheetML workbook.

Usage

From source file:com.b510.excel.client.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (int i = 0; i < args.length; i++) {
        if (args[i].charAt(0) == '-') {
            xlsx = args[i].equals("-xlsx");
        } else {//ww w.  j  a v  a 2s . c o  m
            calendar.set(Calendar.YEAR, Integer.parseInt(args[i]));
        }
    }
    int year = calendar.get(Calendar.YEAR);

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        Sheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        Cell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        Row monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //set column widths, the width is measured in units of 1/256th of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            Cell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            Row row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                Cell dayCell_1 = row.createCell(i * 2);
                Cell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file
    String file = "calendar.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.b510.excel.client.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//w  w w. jav  a  2s . c o m

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "loan-calculator.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.b510.excel.client.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from w  ww. ja va  2s . c om

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.bc.util.XlsxExporter.java

public static void WriteInvoiceToFile(File output, CustomerOrder order, ArrayList<CustomerOrderItem> items) {
    try {/*from   w w  w.  j  av  a  2  s  .  c o  m*/
        if (output.exists()) {
            log.info(output.getName() + " exists. Deleting");
            output.delete();
            log.info("Deleted " + output.getName());
        }

        log.info("Creating xlsx file...");

        FileOutputStream fos = new FileOutputStream(output);
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet("Order");
        CellStyle style = workBook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.ALIGN_FILL);
        Font font = workBook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);

        String[] columnHeaders = { "Invoice", "Salesman", "Customer Name", "Customer Code", "PO", "Ship Date",
                "Post Date", "ISBN", "ISBN13", "Title", "List Price", "Price", "Quantity", "Shipped",
                "Discount", "Extended Price" };

        log.info("Creating header row & columns");

        Row row = sheet.createRow(0);

        for (int i = 0; i < columnHeaders.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnHeaders[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 4500);
        }

        sheet.setColumnWidth(9, 13500);

        log.info("Writing " + items.size() + " records");

        XSSFDataFormat decimalFormat = workBook.createDataFormat();
        CellStyle dstyle = workBook.createCellStyle();
        dstyle.setDataFormat(decimalFormat.getFormat("0.00"));

        int i = 1;
        for (CustomerOrderItem orderItem : items) {
            Row drow = sheet.createRow(i++);
            Hibernate.initialize(order.getCustomerOrderItems());

            String strValue;
            Float floatValue;
            Integer intVal;

            Cell cInvoice = drow.createCell(0);
            strValue = order.getInvoiceNumber();
            if (strValue == null)
                strValue = "";
            cInvoice.setCellValue(order.getInvoiceNumber());

            Cell cSalesman = drow.createCell(1);
            strValue = order.getSalesman();
            if (strValue == null)
                strValue = "";
            cSalesman.setCellValue(strValue);

            Cell cCustomerName = drow.createCell(2);
            strValue = order.getCustomer().getCompanyName();
            if (strValue == null)
                strValue = "";
            cCustomerName.setCellValue(strValue);

            Cell cCustomerCode = drow.createCell(3);
            strValue = order.getCustomerCode();
            if (strValue == null)
                strValue = "";
            cCustomerCode.setCellValue(strValue);

            Cell cPo = drow.createCell(4);
            strValue = order.getPoNumber();
            if (strValue == null)
                strValue = "";
            cPo.setCellValue(strValue);

            Cell cShipDate = drow.createCell(5);
            Date d = order.getShipDate();
            if (d == null)
                cShipDate.setCellValue("");
            else
                cShipDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));

            Cell cPostDate = drow.createCell(6);
            d = order.getPostDate();
            if (d == null)
                cPostDate.setCellValue("");
            else
                cPostDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));
            Hibernate.initialize(orderItem.getInventoryItem());
            InventoryItem item = orderItem.getInventoryItem(); //orderItem.getInventoryItem();
            if (item != null) {
                Cell cIsbn = drow.createCell(7);
                strValue = item.getIsbn();
                if (strValue == null)
                    strValue = "";
                cIsbn.setCellValue(strValue);

                Cell cIsbn13 = drow.createCell(8);
                strValue = item.getIsbn13();
                if (strValue == null)
                    strValue = "";
                cIsbn13.setCellValue(strValue);

                Cell cTitle = drow.createCell(9);
                strValue = item.getTitle();
                if (strValue == null)
                    strValue = "";
                cTitle.setCellValue(strValue);

                Cell cListPrice = drow.createCell(10);
                floatValue = item.getListPrice();
                cListPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cListPrice.setCellValue(floatValue);

                Cell cPrice = drow.createCell(11);
                floatValue = item.getSellingPrice();
                cPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cPrice.setCellValue(floatValue);
            }
            Cell cQuantity = drow.createCell(12);
            intVal = orderItem.getQuantity();
            log.info("Quantity : " + intVal);
            if (intVal == null)
                intVal = 0;
            cQuantity.setCellValue(intVal);

            Cell cShipped = drow.createCell(13);
            intVal = orderItem.getFilled();
            log.info("Shipped QTY : " + intVal);
            if (intVal == null)
                intVal = 0;
            cShipped.setCellValue(intVal);

            Cell cDiscount = drow.createCell(14);
            cDiscount.setCellStyle(dstyle);
            floatValue = orderItem.getDiscount();
            if (floatValue == null)
                floatValue = 0.0f;
            cDiscount.setCellValue(floatValue);

            Cell cExtendedPrice = drow.createCell(15);
            cExtendedPrice.setCellStyle(dstyle);
            BigDecimal dValue = orderItem.getTotalPrice();
            if (dValue == null)
                dValue = BigDecimal.ZERO;
            cExtendedPrice.setCellValue(dValue.doubleValue());

        }

        workBook.write(fos);
        log.info("Finished writing data, closing...");

        fos.close();
        log.info("Completed exporting data to " + output.getAbsolutePath());
    } catch (Exception ex) {
        Logger.getLogger(XlsxExporter.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.bfa.controller.ExcelWriter.java

public void writeTimeTable() {
    FileOutputStream fOutStream = null;
    try {/*from   www .ja v  a  2s.com*/
        File testFile = new File("TimeTable.xlsx");
        XSSFWorkbook myWorkBook = new XSSFWorkbook();
        String[] days = { "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun" };
        String[] times = { "8:55-9:50", "9:50-10:45", "11:15-12:10", "12:10-13:05", "14:00-14:55",
                "14:55-15:50" };
        Iterator timeTableIterator = timeTableDetails.iterator();
        int rowNum = 1, i = 0, cellNum = 0;
        while (timeTableIterator.hasNext()) {
            TimeTableBean currTimeTable = (TimeTableBean) timeTableIterator.next();
            XSSFSheet mySheet = myWorkBook.createSheet(currTimeTable.getClassName());
            Row row = mySheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue(currTimeTable.getClassName());
            int j = 0;
            TimeTableSlot[][] currSlot = currTimeTable.getTimeTable();
            for (TimeTableSlot[] a : currSlot) {
                row = mySheet.createRow(rowNum++);
                cell = row.createCell(0);
                cell.setCellValue(days[j++]);
                //System.out.println(days[j-1]);
                Row secondRow = mySheet.createRow(rowNum++);
                row = mySheet.createRow(rowNum++);
                for (TimeTableSlot p : a) {
                    if (p != null) {
                        int timeSlot = 0;
                        cell = row.createCell(cellNum);
                        Cell secondCell = secondRow.createCell(cellNum++);
                        secondCell.setCellValue(times[timeSlot++]);
                        cell.setCellValue(p.getSubject());
                    }
                    //System.out.println(p.subjectName+"----"+p.teacherName);
                }
                cellNum = 0;
                //System.out.println("\n");
            }
            rowNum = 1;
        }
        fOutStream = new FileOutputStream(testFile);
        myWorkBook.write(fOutStream);
        System.out.println("Success");
    } catch (Exception ex) {
        Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fOutStream.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

private Workbook getWorkbook() {
    switch (xlsFormat) {

    case XLSX_FORMAT:
        return new XSSFWorkbook();

    case XLS_FORMAT:
        return new HSSFWorkbook();

    default:/*  ww w.  j  a  v a  2 s  .co  m*/
        return new XSSFWorkbook();
    }
}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataSetWriterExcel.java

License:Apache License

@Override
public void write(DataTable dataSet) throws Exception {
    log.info("Generating workbook");
    RecordDef recordDef = dataSet.getRecordDef();

    workbook = new XSSFWorkbook();
    Sheet sheet = null;/*from ww  w  . ja va2 s  .  c  o  m*/
    int rowIndex = EXCEL_MAX_ROWS; // Force creation of a new sheet (make
    // the "current sheet" look full)

    for (Record record : dataSet) {
        if (rowIndex >= (EXCEL_MAX_ROWS - 1)) {
            sheet = initNewSheet(recordDef);
            rowIndex = 1; // account for header that initNewSheet() wrote
        }
        addDataRow(sheet, recordDef, record, rowIndex++);
    }

    if (outputStream != null) {
        log.info("Writing workbook");
        workbook.write(outputStream);
        outputStream.close();
    }
}

From source file:com.centurylink.mdw.common.service.JsonExport.java

License:Apache License

public Workbook exportXlsx(String name) throws JSONException {

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet(name == null ? jsonable.getClass().getSimpleName() : name);

    if (jsonable instanceof JsonArray) {
        JSONArray jsonArray = ((JsonArray) jsonable).getArray();
        for (int i = 0; i < jsonArray.length(); i++) {
            JSONObject jsonObj = jsonArray.getJSONObject(i);
            addNames(jsonObj);//w w w .  j a  v  a 2s . c  o  m
            setRowValues(sheet, i + 1, jsonObj);
        }
        setColumnLabels(sheet);
    } else if (jsonable instanceof InstanceList) {
        InstanceList<?> instanceList = (InstanceList<?>) jsonable;
        List<? extends Jsonable> items = instanceList.getItems();
        for (int i = 0; i < items.size(); i++) {
            JSONObject jsonObj = items.get(i).getJson();
            addNames(jsonObj);
            setRowValues(sheet, i + 1, jsonObj);
        }
        setColumnLabels(sheet);
    } else if (jsonable instanceof JsonListMap) {
        JsonListMap<?> listMap = (JsonListMap<?>) jsonable;
        int row = 1;
        List<String> keys = new ArrayList<String>();
        keys.addAll(listMap.getJsonables().keySet());
        Collections.sort(keys);
        for (String key : keys) {
            List<? extends Jsonable> jsonableList = listMap.getJsonables().get(key);
            for (Jsonable jsonable : jsonableList) {
                addNames(jsonable.getJson());
            }
            if (!"".equals(names.get(0))) {
                names.add(0, ""); // key column
            }
            for (Jsonable jsonable : jsonableList) {
                Row valuesRow = setRowValues(sheet, row, jsonable.getJson(), 1);
                Cell cell = valuesRow.createCell(0);
                cell.setCellValue(key);
                row++;
            }
        }
        setColumnLabels(sheet);
    } else {
        throw new UnsupportedOperationException("Unsupported JSON type: " + jsonable);
    }

    return workbook;
}

From source file:com.cisco.ca.cstg.pdi.utils.Util.java

public static void json2Excel(OutputStream output, String json, String[] properties, String[] columnsNames)
        throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Row header = sheet.createRow(0);//from   ww w. ja v  a  2  s.  c  o m

    for (int i = 0; i < columnsNames.length; i++) {
        String string = columnsNames[i];
        Cell cell = header.createCell(i);
        RichTextString text = new XSSFRichTextString(string);
        cell.setCellValue(text);
    }

    LOGGER.info("Writing on workbook.");
    try {
        ObjectMapper mapper = new ObjectMapper();
        JsonNode jsonNode = mapper.readTree(json);

        int i = 0;
        for (JsonNode jsonNode2 : jsonNode) {
            Row row = sheet.createRow(++i);

            for (int j = 0; j < properties.length; j++) {
                String string = properties[j];
                Cell cell = row.createCell(j);
                RichTextString text = new XSSFRichTextString(jsonNode2.get(string).getTextValue());
                cell.setCellValue(text);
            }
        }
    } catch (JsonProcessingException e) {
        LOGGER.error(e.getMessage(), e);
    }

    workbook.write(output);
    workbook.close();
    LOGGER.info("Written on workbook.");
}

From source file:com.cms.utils.DataUtil.java

License:Open Source License

/**
 *
 * @param file// w  w w.j av  a2 s .  c om
 * @param iSheet
 * @param iBeginRow
 * @param iFromCol
 * @param iToCol
 * @param rowBack
 * @param lstValidateCells
 * @return
 */
public static List isValidExcells(String mimeType, File file, int iSheet, int iBeginRow, int iFromCol,
        int iToCol, int rowBack, List<ValidateCells> lstValidateCells) {
    String fileName = file.getName();
    fileName = removeDotInteger(fileName);
    boolean isCopySheet = true;
    File fileError = null;
    Map<String, String> mapsNameError = new HashMap<>();
    List lst = null;
    try {
        if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx
            lst = ExcelReaderXLSX.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack);
        } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls
            lst = ExcelReader.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack);
        }
        //If lst null return
        if (lst == null) {
            return lst;
        }
        String error = "";
        ValidateCells validateCells = null;
        int index = 0;
        if (iBeginRow == 0) {
            index = 1;
        } else {
            index = 0;
        }
        int rowErr = 0;
        Object[] temp;
        List<String> lstReturn = Lists.newArrayList();
        for (int i = index, size = lst.size(); i < size; i++) {
            temp = (Object[]) lst.get(i);
            if (checkObjectNull(temp)) {
                //                    lst.remove(temp);
                //                    i--;
                //                    continue;
                if (i == 0) {
                    lst = Lists.newArrayList();
                }
                break;
            }
            error = "";
            for (int j = 0; j < lstValidateCells.size(); j++) {
                validateCells = lstValidateCells.get(j);
                if (validateCells.getPattern() != null) {
                    error += DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(),
                            validateCells.isIsNotNull(), validateCells.getPattern());
                } else {
                    lstReturn = DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(),
                            validateCells.isIsNotNull(), validateCells.getLength());
                    error += lstReturn.get(0);
                    temp[j] = lstReturn.get(1);
                }
            }
            if (!isStringNullOrEmpty(error)) {
                rowErr = i + iBeginRow;
                mapsNameError.put(rowErr + "", error);
            }
        }

        if (!mapsNameError.isEmpty()) {
            //
            FileInputStream flieInput = new FileInputStream(file);
            XSSFWorkbook workbookIp = null;
            String fileCreate = fileName + "_Error.xlsx";
            FileOutputStream fileOut = new FileOutputStream(fileCreate);
            XSSFWorkbook workbookEp = new XSSFWorkbook();
            XSSFSheet worksheetEp = workbookEp.createSheet("Thong_Tin_Loi");
            XSSFCellStyle cellStyle = null;
            //
            if (isCopySheet) {
                if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx
                    workbookIp = new XSSFWorkbook(flieInput);
                } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls
                    HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(flieInput);
                    workbookIp = ExcelReaderXLSX.convertWorkbookHSSFToXSSF(hSSFWorkbook);
                }
                XSSFSheet worksheetIp = workbookIp.getSheetAt(iSheet);
                ExcelReaderXLSX.copySheets(worksheetEp, worksheetIp, iToCol);
                //
                cellStyle = CommonUtils.styleCell(workbookEp);
                isCopySheet = false;
            }
            for (Map.Entry<String, String> entrySet : mapsNameError.entrySet()) {
                String key = entrySet.getKey();
                String value = entrySet.getValue();
                int row = Integer.valueOf(key);
                XSSFRow row5 = worksheetEp.getRow(row);
                if (row5 != null) {
                    XSSFCell cellB1 = row5.createCell(iToCol + 1);
                    cellB1.setCellValue(value);
                    cellB1.setCellStyle(cellStyle);
                }
            }
            workbookEp.write(fileOut);
            fileOut.flush();
            fileOut.close();
            fileError = new File(fileCreate);
            Resource resource = new FileResource(fileError);
            Page.getCurrent().open(resource, null, false);
            lst = null;
            fileError.deleteOnExit();
        }

    } catch (Exception e) {
        e.printStackTrace();
        ;
        lst = null;
    }
    return lst;
}