Example usage for org.apache.poi.xssf.usermodel XSSFDataFormat getFormat

List of usage examples for org.apache.poi.xssf.usermodel XSSFDataFormat getFormat

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

Introduction

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

Prototype

@Override
public String getFormat(short index)

Source Link

Document

get the format string that matches the given format index

Usage

From source file:br.com.sose.utils.BigGridDemo_temp.java

License:Apache License

/**
 * Create a library of cell styles.//  www  .j a va  2  s .  c  o m
 */
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat fmt = wb.createDataFormat();

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style1.setDataFormat(fmt.getFormat("0.0%"));
    styles.put("percent", style1);

    XSSFCellStyle style2 = wb.createCellStyle();
    style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    style2.setDataFormat(fmt.getFormat("0.0X"));
    styles.put("coeff", style2);

    XSSFCellStyle style3 = wb.createCellStyle();
    style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style3.setDataFormat(fmt.getFormat("$#,##0.00"));
    styles.put("currency", style3);

    XSSFCellStyle style4 = wb.createCellStyle();
    style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style4.setDataFormat(fmt.getFormat("mmm dd"));
    styles.put("date", style4);

    XSSFCellStyle style5 = wb.createCellStyle();
    XSSFFont headerFont = wb.createFont();
    headerFont.setBold(true);
    style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    style5.setFont(headerFont);
    styles.put("header", style5);

    return styles;
}

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  a v a 2s.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.github.igor_kudryashov.utils.excel.ExcelWriter.java

License:Apache License

/**
 * Returns a style of cell/*from  www.  j  a  v a  2  s.com*/
 *
 * @param rownum
 *            the number of row for count odd/even rows
 * @param entry
 *            value of cell
 * @param header
 *            <code>true</code> if this row is the header, otherwise
 *            <code>false</code>
 * @return the cell style
 */
private XSSFCellStyle getCellStyle(int rownum, Object entry, boolean header) {
    XSSFCellStyle style;
    String name = entry.getClass().getName();
    if (header) {
        name += "_header";
    } else if ((rownum % 2) == 0) {
        name += "_even";
    }
    if (styles.containsKey(name)) {
        // if we already have a style for this class, return it
        style = styles.get(name);
    } else {
        // create new style
        style = (XSSFCellStyle) workbook.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.TOP);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, colorBorder);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, colorBorder);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, colorBorder);
        // format data
        XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat();
        short format = 0;
        if (name.contains("Date")) {
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe));
            style.setAlignment(CellStyle.ALIGN_LEFT);
        } else if (name.contains("Double")) {
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(2));
            style.setAlignment(CellStyle.ALIGN_RIGHT);
        } else if (name.contains("Integer")) {
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(1));
            style.setAlignment(CellStyle.ALIGN_RIGHT);
        } else {
            style.setAlignment(CellStyle.ALIGN_LEFT);
            if (!header) {
                style.setWrapText(true);
            }
        }

        if (header) {
            // for header
            style.setFillForegroundColor(colorHeaderBackground);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        } else if (name.contains("_even")) {
            // for even rows
            style.setFillForegroundColor(colorEvenCellBackground);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        style.setDataFormat(format);
        // keep the style for reuse
        styles.put(name, style);
    }
    return style;
}

From source file:com.philips.his.pixiu.cdr.poi.BigGridDemo.java

License:Apache License

/**
 * Create a library of cell styles./*from   ww w  .  j a  va  2s  . c om*/
 */
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat fmt = wb.createDataFormat();

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setAlignment(HorizontalAlignment.RIGHT);
    style1.setDataFormat(fmt.getFormat("0.0%"));
    styles.put("percent", style1);

    XSSFCellStyle style2 = wb.createCellStyle();
    style2.setAlignment(HorizontalAlignment.CENTER);
    style2.setDataFormat(fmt.getFormat("0.0X"));
    styles.put("coeff", style2);

    XSSFCellStyle style3 = wb.createCellStyle();
    style3.setAlignment(HorizontalAlignment.RIGHT);
    style3.setDataFormat(fmt.getFormat("$#,##0.00"));
    styles.put("currency", style3);

    XSSFCellStyle style4 = wb.createCellStyle();
    style4.setAlignment(HorizontalAlignment.RIGHT);
    style4.setDataFormat(fmt.getFormat("mmm dd"));
    styles.put("date", style4);

    XSSFCellStyle style5 = wb.createCellStyle();
    XSSFFont headerFont = wb.createFont();
    headerFont.setBold(true);
    style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style5.setFont(headerFont);
    styles.put("header", style5);

    return styles;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoHojalateriaReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();/*from  www  .  j ava  2  s  . com*/
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Cell cell;
    Row row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn);
    cell.setCellValue("Hojalteria y pintura");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < 3; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 2 //last column  (0-based)
    ));

    //detalle
    cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    Cell inicio = null;
    Cell fin = null;
    for (RegistroCostoRC x : datos.getRegistroHojalateriaPintura()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        //tipo
        cell = row.createCell(initialColumn);
        cell.setCellValue(x.getTipo());
        //descripcion
        cell = row.createCell(initialColumn + 1);
        cell.setCellValue(x.getDescripcion());
        //costo
        cell = row.createCell(initialColumn + 2);
        cell.setCellValue(x.getCosto());
        cell.setCellStyle(cellStyle);
        if (inicio == null) {
            inicio = cell;
        }
    }
    if (inicio != null) {
        fin = cell;
    }
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn + 1);
    cell.setCellValue("Total");
    cellStyle = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell = row.createCell(initialColumn + 2);
    cell.setCellStyle(cellStyle);
    if (inicio != null) {
        String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")";
        cell.setCellFormula(formula);
        contexto.put("totalHojalateria", cell);
    } else {
        cell.setCellValue(0.0);
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 2);
    BordeSeccion sinTotal = new BordeSeccion(r);
    sinTotal.setLowerRow(r.getLowerRow() - 1);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoMecanicaReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();// w  w  w .java2 s .c  o m
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Cell cell;
    Row row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn);
    cell.setCellValue("Mecanica");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < 3; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 2 //last column  (0-based)
    ));

    //detalle
    cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    Cell inicio = null;
    Cell fin = null;
    for (RegistroCostoRC x : datos.getRegistroMecanica()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        //tipo
        cell = row.createCell(initialColumn);
        cell.setCellValue(x.getTipo());
        //descripcion
        cell = row.createCell(initialColumn + 1);
        cell.setCellValue(x.getDescripcion());
        //costo
        cell = row.createCell(initialColumn + 2);
        cell.setCellValue(x.getCosto());
        cell.setCellStyle(cellStyle);
        if (inicio == null) {
            inicio = cell;
        }
    }
    if (inicio != null) {
        fin = cell;
    }
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn + 1);
    cell.setCellValue("Total");
    cellStyle = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell = row.createCell(initialColumn + 2);
    cell.setCellStyle(cellStyle);
    if (inicio != null) {
        String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")";
        cell.setCellFormula(formula);
        contexto.put("totalMecanica", cell);
    } else {
        cell.setCellValue(0.0);
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 2);
    BordeSeccion sinTotal = new BordeSeccion(r);
    sinTotal.setLowerRow(r.getLowerRow() - 1);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.TotalServicioReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();//from  w  w w .  j a v a  2 s  . c o m
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Row row = getRow(sheet, initialRow);
    row.createCell(initialColumn).setCellValue("Total del servicio:");
    //calculo del total
    Cell totalMecanica = (Cell) contexto.get("totalMecanica");
    Cell totalHojalateria = (Cell) contexto.get("totalHojalateria");
    Cell cell = row.createCell(initialColumn + 1);
    if (totalMecanica != null && totalHojalateria != null) {
        String formula = getSimpleReference(totalMecanica) + "+" + getSimpleReference(totalHojalateria);
        cell.setCellFormula(formula);
    } else {
        if (totalMecanica != null) {
            String formula = getSimpleReference(totalMecanica);
            cell.setCellFormula(formula);
        }
        if (totalHojalateria != null) {
            String formula = getSimpleReference(totalHojalateria);
            cell.setCellFormula(formula);
        }
    }
    if (totalMecanica == null && totalHojalateria == null) {
        cell.setCellValue(0d);
    }
    XSSFCellStyle cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell.setCellStyle(cellStyle);

    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 1);
    return r;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?//from  w  ww.  j  a  va 2  s .c  o m
 * @param FileName ???????excel
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (FileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // jsonkey
        String[] cellNames;
        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);
        // ???
        cellNames = new String[curCellNum];
        // ????JSONkey
        for (int m = 0; m < curCellNum; m++) {
            Cell cell = fisrtRow.getCell(m);
            // ?
            cell.setCellStyle(cellStyle);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            // ?
            cellNames[m] = getCellValue(cell);
        }
        for (String s : cellNames) {
            System.out.print("" + i + " sheet " + s + ",");
        }
        System.out.println();

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);

                cell.setCellStyle(cellStyle);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // ???
                rowMap.put(cellNames[k], getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?/*from   www . j av  a 2  s  . co  m*/
 * @param FileName ???????excel
 * @param headers list,String-->Arrays.asList();
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (fileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);
                // ???
                rowMap.put(headers.get(k), getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:controller.DAORequest.java

public void saveRequest() {

    XSSFSheet sheet = workbook.getSheetAt(0);
    sheet.getRow(0).createCell(10).setCellValue("Id solicitante");
    sheet.getRow(0).createCell(11).setCellValue("Nombre solicitante");
    sheet.getRow(0).createCell(12).setCellValue("Estado de solicitud");
    sheet.getRow(0).createCell(13).setCellValue("Num de Resolucion");
    int rowI = 1;
    for (Object o : School.getInstance().selectAllRequests()) {

        Request r = (Request) o;
        Row row = sheet.createRow(rowI);
        ///* w  w  w . j a v a 2  s. com*/
        Cell cellDate = row.createCell(0);
        cellDate.setCellValue(r.getDate());
        CellStyle styleCreationDate = workbook.createCellStyle();
        XSSFDataFormat dfCreationDate = workbook.createDataFormat();
        styleCreationDate.setDataFormat(dfCreationDate.getFormat("d/m/yy"));
        cellDate.setCellStyle(styleCreationDate);
        //
        Cell cellCarnet = row.createCell(1);
        cellCarnet.setCellValue(r.getAffected().getId());
        CellStyle styleCreationInt = workbook.createCellStyle();
        XSSFDataFormat dfCreationInt = workbook.createDataFormat();

        Cell cellName = row.createCell(2);
        cellName.setCellValue(r.getAffected().getName());
        Cell cellEmail = row.createCell(3);
        cellEmail.setCellValue(r.getAffected().getEmail());
        Cell cellPhone = row.createCell(4);
        cellPhone.setCellValue(r.getAffected().getPhone());
        Cell cellPeriod = row.createCell(5);
        cellPeriod.setCellValue(r.getGroup().getPeriod());
        Cell cellCourse = row.createCell(6);
        cellCourse.setCellValue(r.getGroup().getCourse().getCode());
        Cell cellNumGroup = row.createCell(7);
        cellNumGroup.setCellType(Cell.CELL_TYPE_NUMERIC);
        cellNumGroup.setCellValue(r.getGroup().getNumber());
        Cell cellInc = row.createCell(8);
        cellInc.setCellValue(transformInconsistencieToSpanish(r.getInconsistencie()));
        Cell cellDescription = row.createCell(9);
        cellDescription.setCellValue(r.getDescription());
        Cell cellIdReq = row.createCell(10);
        cellIdReq.setCellValue(r.getRequester().getId());
        Cell cellNameReq = row.createCell(11);
        cellNameReq.setCellValue(r.getRequester().getName());
        Cell cellReqState = row.createCell(12);
        cellReqState.setCellValue(transformReqStatetoSpanish(r.getRequestState()));
        if (r.getRequestState() == ERequestState.PROCESSED) {
            Cell cellNumReso = row.createCell(13);
            cellNumReso.setCellValue(Integer.toString(r.getResolution().getId()));
        }

        rowI++;

    }

    // Save to excel file 
    try {

        FileOutputStream out = new FileOutputStream(new File("src//files//DatosFormulario.xlsx"));

        workbook.write(out);

        workbook.close();
        out.close();
        saveResolution();

    } catch (FileNotFoundException ex) {

        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {

        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    }

}