Example usage for org.apache.poi.ss.usermodel CellStyle setDataFormat

List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setDataFormat

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel CellStyle setDataFormat.

Prototype

void setDataFormat(short fmt);

Source Link

Document

set the data format (must be a valid format).

Usage

From source file:com.ideaspymes.proyecttemplate.stock.web.ProductoConsultaBean.java

@Override
public Workbook getWorkBook() {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("My Sample Excel");
    List<CatalogoProductos> lista = (List<CatalogoProductos>) getDetalles();

    sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * new Short("6")));

    org.apache.poi.ss.usermodel.Font fontTitulo = wb.createFont();
    fontTitulo.setFontHeightInPoints((short) 12);
    fontTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);

    org.apache.poi.ss.usermodel.Font fontTituloPricipal = wb.createFont();
    fontTituloPricipal.setFontHeightInPoints((short) 22);
    fontTituloPricipal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);

    DataFormat format = wb.createDataFormat();

    CellStyle styleTituloPrincipal = wb.createCellStyle();
    styleTituloPrincipal.setFont(fontTituloPricipal);
    styleTituloPrincipal.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleTituloPrincipal.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle styleTitulo = wb.createCellStyle();
    styleTitulo.setFont(fontTitulo);// w w w  .ja  va  2s  .c om
    styleTitulo.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleTitulo.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
    styleTitulo.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleTitulo.setWrapText(true);

    CellStyle styleNumero = wb.createCellStyle();
    styleNumero.setDataFormat(format.getFormat("#,##0"));
    styleNumero.setWrapText(true);
    styleNumero.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleNumero.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle styleFecha = wb.createCellStyle();
    styleFecha.setDataFormat(format.getFormat("dd/MM/yyyy"));
    styleFecha.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleFecha.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true);

    CellStyle styleCenter = wb.createCellStyle();
    styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleCenter.setAlignment(CellStyle.ALIGN_CENTER);
    styleCenter.setWrapText(true);

    Row rowTitle = sheet.createRow(0);
    Cell cellTitle = rowTitle.createCell(1);
    cellTitle.setCellStyle(styleTituloPrincipal);

    sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based)
            1, //last row  (0-based)
            1, //first column (0-based)
            11 //last column  (0-based)
    ));

    cellTitle.setCellValue("Listado de Activos");

    int i = 2;

    Row row0 = sheet.createRow(i);
    row0.setHeight((short) 500);

    Cell cell1 = row0.createCell(1);
    cell1.setCellValue("Foto");
    cell1.setCellStyle(styleTitulo);

    Cell cellFecha = row0.createCell(3);
    cellFecha.setCellValue("Fecha Ingreso");
    cellFecha.setCellStyle(styleTitulo);

    Cell cellFechaCarga = row0.createCell(4);
    cellFechaCarga.setCellValue("Fecha Carga");
    cellFechaCarga.setCellStyle(styleTitulo);

    Cell cell3 = row0.createCell(5);
    cell3.setCellValue("Nombre");
    cell3.setCellStyle(styleTitulo);

    Cell cell4 = row0.createCell(6);
    cell4.setCellValue("Cdigo");
    cell4.setCellStyle(styleTitulo);

    Cell cell5 = row0.createCell(7);
    cell5.setCellValue("Descripcin");
    cell5.setCellStyle(styleTitulo);

    Cell cell6 = row0.createCell(8);
    cell6.setCellValue("Es Regalo?");
    cell6.setCellStyle(styleTitulo);

    Cell cell7 = row0.createCell(9);
    cell7.setCellValue("Familia");
    cell7.setCellStyle(styleTitulo);

    Cell cell8 = row0.createCell(10);
    cell8.setCellValue("Ubicaciones");
    cell8.setCellStyle(styleTitulo);

    Cell cell9 = row0.createCell(11);
    cell9.setCellValue("Stock");
    cell9.setCellStyle(styleTitulo);

    for (CatalogoProductos cp : lista) {

        int indexFila = i + 1;
        if (cp.getImagen() != null) {
            int pictureIdx = wb.addPicture(cp.getImagen(), Workbook.PICTURE_TYPE_PNG);
            CreationHelper helper = wb.getCreationHelper();

            //Creates the top-level drawing patriarch.
            Drawing drawing = sheet.createDrawingPatriarch();

            //Create an anchor that is attached to the worksheet
            ClientAnchor anchor = helper.createClientAnchor();
            //set top-left corner for the image
            anchor.setCol1(1);
            anchor.setRow1(indexFila);

            //Creates a picture
            Picture pict = drawing.createPicture(anchor, pictureIdx);
            //Reset the image to the original size
            pict.resize(0.4);
        }
        Row row1 = sheet.createRow(indexFila);
        row1.setHeightInPoints(80f);

        Cell cellColFecha = row1.createCell(3);

        if (cp.getFecha() != null) {
            cellColFecha.setCellValue(cp.getFecha());
            cellColFecha.setCellStyle(styleFecha);

        } else {
            cellColFecha.setCellValue("");
            cellColFecha.setCellStyle(styleFecha);
        }

        Cell cellColFechaCarga = row1.createCell(4);

        if (cp.getFechaCarga() != null) {
            cellColFechaCarga.setCellValue(cp.getFechaCarga());
            cellColFechaCarga.setCellStyle(styleFecha);

        } else {
            cellColFechaCarga.setCellValue("");
            cellColFechaCarga.setCellStyle(styleFecha);
        }

        Cell cellCol1 = row1.createCell(5);
        cellCol1.setCellValue(cp.getProducto());
        cellCol1.setCellStyle(style);

        Cell cellCol2 = row1.createCell(6);
        cellCol2.setCellValue(cp.getCodigo());
        cellCol2.setCellStyle(styleNumero);

        Cell cellCol3 = row1.createCell(7);
        cellCol3.setCellValue(cp.getDescripcion());
        cellCol3.setCellStyle(style);

        Cell cellCol4 = row1.createCell(8);
        cellCol4.setCellValue(cp.isEsRegalo() ? "SI" : "NO");
        cellCol4.setCellStyle(styleCenter);

        Cell cellCol5 = row1.createCell(9);
        cellCol5.setCellValue(cp.getFamilia());
        cellCol5.setCellStyle(style);

        Cell cellCol6 = row1.createCell(10);
        cellCol6.setCellValue(cp.getUbicaciones());
        cellCol6.setCellStyle(style);

        Cell cellCol7 = row1.createCell(11);
        cellCol7.setCellValue(cp.getStock());
        cellCol7.setCellStyle(styleNumero);

        i++;

    }

    sheet.setColumnWidth(1, 4000);
    sheet.setColumnWidth(2, 0);
    sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(4, 4000);
    sheet.setColumnWidth(5, 10000);
    sheet.setColumnWidth(6, 3000);
    sheet.setColumnWidth(7, 10000);
    sheet.setColumnWidth(8, 3500);
    sheet.setColumnWidth(9, 6000);
    sheet.setColumnWidth(10, 10000);
    sheet.setColumnWidth(11, 2000);

    return wb;
}

From source file:com.ipcglobal.fredimport.xls.BaseXls.java

License:Apache License

/**
 * Find cell style.//from   w ww.  ja  va  2  s  .  com
 *
 * @param fontName the font name
 * @param fontColor the font color
 * @param fontHeight the font height
 * @param fontWeight the font weight
 * @param alignHorz the align horz
 * @param alignVert the align vert
 * @param bgColor the bg color
 * @param cellBorder the cell border
 * @param dataFormat the data format
 * @return the cell style
 * @throws Exception the exception
 */
public CellStyle findCellStyle(String fontName, short fontColor, short fontHeight, short fontWeight,
        short alignHorz, short alignVert, short bgColor, CellBorder cellBorder, short dataFormat)
        throws Exception {
    String keyStyle = new StringBuffer().append(fontName).append("|").append(fontColor).append("|")
            .append(fontHeight).append("|").append(fontWeight).append("|").append(alignHorz).append("|")
            .append(alignVert).append("|").append(bgColor).append("|").append(cellBorder).append("|")
            .append(dataFormat).append("|").toString();
    CellStyle cellStyle = cellStyles.get(keyStyle);
    if (cellStyle == null) {
        String keyFont = new StringBuffer().append(fontName).append("|").append(fontColor).append("|")
                .append(fontHeight).append("|").append(fontWeight).append("|").toString();
        Font font = fonts.get(keyFont);
        if (font == null) {
            font = wb.createFont();
            fonts.put(keyFont, font);
            font.setFontName(fontName);
            font.setFontHeightInPoints(fontHeight);
            font.setBoldweight(fontWeight);
            font.setColor(fontColor);
        }
        cellStyle = wb.createCellStyle();
        cellStyles.put(keyStyle, cellStyle);
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        if (bgColor != BG_COLOR_NONE) {
            cellStyle.setFillForegroundColor(bgColor);
            cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        if (alignHorz != -1)
            cellStyle.setAlignment(alignHorz);
        if (alignVert != -1)
            cellStyle.setVerticalAlignment(alignVert);
        if (dataFormat != -1) {
            cellStyle.setDataFormat(dataFormat);
        }
        if (cellBorder != null)
            addBorderToStyle(cellStyle, cellBorder);
    }

    return cellStyle;
}

From source file:com.jeans.iservlet.action.asset.AssetExportAction.java

private void appendRow(Sheet sheet, Asset asset, int rowNumber) {
    // // w  w  w . j  a  v  a 2  s  . c  om
    DataFormat df = sheet.getWorkbook().createDataFormat();
    // ?10?
    Font font = sheet.getWorkbook().createFont();
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ?1???????
    CellStyle cellStyleString = sheet.getWorkbook().createCellStyle();
    cellStyleString.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleString.setFont(font);
    cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyleString.setWrapText(false);
    // ?2????(yyyyMM)???
    CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle();
    cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleDate.setFont(font);
    cellStyleDate.setDataFormat(df.getFormat("yyyyMM"));
    cellStyleDate.setWrapText(false);
    // ?3??????(#)???
    CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle();
    cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleQuantity.setFont(font);
    cellStyleQuantity.setDataFormat(df.getFormat("#"));
    cellStyleQuantity.setWrapText(false);
    // ?4?????(#,##0.00_ )???
    CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle();
    cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleCost.setFont(font);
    cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ "));
    cellStyleCost.setWrapText(false);
    // 20
    Row row = sheet.createRow(rowNumber);
    row.setHeightInPoints(20);
    Cell cell = null;
    if (asset instanceof Hardware) {
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getCode());

        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getFinancialCode());

        cell = row.createCell(2, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        Company company = asset.getCompany();
        if (null != company) {
            cell.setCellValue(company.getAlias());
        }

        cell = row.createCell(3, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog()));

        cell = row.createCell(4, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getName());

        cell = row.createCell(5, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getVendor());

        cell = row.createCell(6, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getModelOrVersion());

        cell = row.createCell(7, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getAssetUsage());

        cell = row.createCell(8, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getSn());

        cell = row.createCell(9, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getConfiguration());

        cell = row.createCell(10, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleDate);
        Date pt = asset.getPurchaseTime();
        if (null != pt) {
            cell.setCellValue(pt);
        }

        cell = row.createCell(11, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleQuantity);
        cell.setCellValue(asset.getQuantity());

        cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleCost);
        cell.setCellValue(asset.getCost().doubleValue());

        cell = row.createCell(13, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getAssetStateName(asset.getState()));

        cell = row.createCell(14, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getHardwareWarrantyName(((Hardware) asset).getWarranty()));

        cell = row.createCell(15, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getLocation());

        cell = row.createCell(16, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getIp());

        cell = row.createCell(17, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getHardwareImportanceName(((Hardware) asset).getImportance()));

        cell = row.createCell(18, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        Employee owner = ((Hardware) asset).getOwner();
        if (null != owner) {
            cell.setCellValue(owner.getName());
        }

        cell = row.createCell(19, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getComment());
    } else if (asset instanceof Software) {
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        Company company = asset.getCompany();
        if (null != company) {
            cell.setCellValue(company.getAlias());
        }

        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog()));

        cell = row.createCell(2, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getName());

        cell = row.createCell(3, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getVendor());

        cell = row.createCell(4, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getModelOrVersion());

        cell = row.createCell(5, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getAssetUsage());

        cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleDate);
        Date pt = asset.getPurchaseTime();
        if (null != pt) {
            cell.setCellValue(pt);
        }

        cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleQuantity);
        cell.setCellValue(asset.getQuantity());

        cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleCost);
        cell.setCellValue(asset.getCost().doubleValue());

        cell = row.createCell(9, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getAssetStateName(asset.getState()));

        cell = row.createCell(10, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getSoftwareTypeName(((Software) asset).getSoftwareType()));

        cell = row.createCell(11, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Software) asset).getLicense());

        cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleDate);
        Date et = ((Software) asset).getExpiredTime();
        if (null != et) {
            cell.setCellValue(et);
        }

        cell = row.createCell(13, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getComment());
    }
}

From source file:com.jeans.iservlet.action.asset.AssetExportAction.java

private void generateSheetHeader(Sheet sheet, boolean hardware) {
    // //w w  w.  jav a 2s .c o  m
    // ?10??
    Font font = sheet.getWorkbook().createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ?????????
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(font);
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyle.setWrapText(false);
    // 20
    Row row = sheet.createRow(0);
    row.setHeightInPoints(20);
    Cell cell = null;
    if (hardware) {
        for (int i = 0; i < 20; i++) {
            cell = row.createCell(i, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(HARDWARE_HEADERS[i]);
            sheet.setColumnWidth(i, HARDWARE_HEADERS_WIDTH[i] * 256);
        }
    } else {
        for (int i = 0; i < 14; i++) {
            cell = row.createCell(i, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(SOFTWARE_HEADERS[i]);
            sheet.setColumnWidth(i, SOFTWARE_HEADERS_WIDTH[i] * 256);
        }
    }
}

From source file:com.jeans.iservlet.controller.impl.ExportController.java

private void generateSheet(Sheet sheet, byte type, Company company) {
    // sheet/*  w w w  .  j  av a  2s  .  com*/
    if (type == AssetConstants.HARDWARE_ASSET) {
        // 
        generateSheetHeader(sheet, true);
    } else if (type == AssetConstants.SOFTWARE_ASSET) {
        // 
        generateSheetHeader(sheet, false);
    } else if (type >= AssetConstants.NETWORK_EQUIPMENT && type <= AssetConstants.OTHER_EQUIPMENT) {
        // ?
        generateSheetHeader(sheet, true);
    } else if (type >= AssetConstants.OPERATING_SYSTEM_SOFTWARE && type <= AssetConstants.OTHER_SOFTWARE) {
        // ?
        generateSheetHeader(sheet, false);
    }
    List<Asset> assets = astService.listAssets(company, type);
    DataFormat df = sheet.getWorkbook().createDataFormat();
    // ?10?
    Font font = sheet.getWorkbook().createFont();
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ?1???????
    CellStyle cellStyleString = sheet.getWorkbook().createCellStyle();
    cellStyleString.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleString.setFont(font);
    cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyleString.setWrapText(false);
    // ?2????(yyyyMM)???
    CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle();
    cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleDate.setFont(font);
    cellStyleDate.setDataFormat(df.getFormat("yyyyMM"));
    cellStyleDate.setWrapText(false);
    // ?3??????(#)???
    CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle();
    cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleQuantity.setFont(font);
    cellStyleQuantity.setDataFormat(df.getFormat("#"));
    cellStyleQuantity.setWrapText(false);
    // ?4?????(#,##0.00_ )???
    CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle();
    cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleCost.setFont(font);
    cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ "));
    cellStyleCost.setWrapText(false);
    int rowNumber = 1;
    for (Asset asset : assets) {
        appendRow(sheet, asset, rowNumber++, cellStyleString, cellStyleDate, cellStyleQuantity, cellStyleCost);
    }
}

From source file:com.jeans.iservlet.controller.impl.ExportController.java

/**
 * ?????/*  w w w  .  ja  v  a 2s.  com*/
 * 
 * @param storedOnly
 *            ???
 * @return
 * @throws IOException
 */
@RequestMapping(method = RequestMethod.POST, value = "/accessories")
public ResponseEntity<byte[]> exportAccessories(@RequestParam boolean storedOnly) throws IOException {
    StringBuilder fn = new StringBuilder(getCurrentCompany().getName());
    Date n = new Date();
    String today = (new SimpleDateFormat("yyyyMMdd")).format(n);
    String now = (new SimpleDateFormat("yyyy-MM-dd HHmmss")).format(n);
    Workbook wb = new XSSFWorkbook();
    fn.append(" - ???(").append(today).append(").xlsx");
    Sheet sheet = wb.createSheet(now);
    // 
    // ?10??
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ?????????
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(font);
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyle.setWrapText(false);
    // 20
    Row row = sheet.createRow(0);
    row.setHeightInPoints(20);
    Cell cell = null;
    for (int i = 0; i < 7; i++) {
        cell = row.createCell(i, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(ACS_HEADERS[i]);
        sheet.setColumnWidth(i, ACS_HEADERS_WIDTH[i] * 256);
    }
    List<Accessory> acs = acsService.listAccessories(getCurrentCompany(), storedOnly);
    Collections.sort(acs, new Comparator<Accessory>() {

        @Override
        public int compare(Accessory o1, Accessory o2) {
            int ret = o1.getType().compareTo(o2.getType());
            if (ret == 0) {
                ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName());
                if (ret == 0) {
                    ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getBrand(), o2.getBrand());
                    if (ret == 0) {
                        ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getModel(),
                                o2.getModel());
                    }
                }
            }
            return ret;
        }

    });
    // 
    DataFormat df = wb.createDataFormat();
    // ?10?
    Font dFont = wb.createFont();
    dFont.setFontName("");
    dFont.setFontHeightInPoints((short) 10);
    // ?1???????
    CellStyle cellStyleString = wb.createCellStyle();
    cellStyleString.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleString.setFont(dFont);
    cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyleString.setWrapText(false);
    // ?2??????(#)???
    CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle();
    cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleQuantity.setFont(dFont);
    cellStyleQuantity.setDataFormat(df.getFormat("#"));
    cellStyleQuantity.setWrapText(false);
    int rowNumber = 1;
    for (Accessory ac : acs) {
        // 20
        Row dRow = sheet.createRow(rowNumber);
        dRow.setHeightInPoints(20);
        Cell dCell = null;
        dCell = dRow.createCell(0, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getType().getTitle());

        dCell = dRow.createCell(1, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getName());

        dCell = dRow.createCell(2, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getBrand());

        dCell = dRow.createCell(3, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getModel());

        dCell = dRow.createCell(4, Cell.CELL_TYPE_NUMERIC);
        dCell.setCellStyle(cellStyleQuantity);
        dCell.setCellValue(null == ac.getStorage() ? 0 : ac.getStorage().getQuantity());

        dCell = dRow.createCell(5, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getUnit());

        dCell = dRow.createCell(6, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getDescription());

        rowNumber++;
    }
    String filename = null;
    if (isIE()) {
        filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20");
    } else {
        filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1");
    }
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-disposition", "attachment; filename=" + filename);
    BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096);
    wb.write(out);
    wb.close();
    out.close();

    return null;
}

From source file:com.jeans.iservlet.controller.impl.ExportController.java

/**
 * ????//from www.ja  v a 2  s .co  m
 * 
 * @param ids
 *            ???(AccessoryEntryInvoice.id)?
 * @return
 * @throws IOException
 */
private void exportAccessoryEntries(List<Long> idList) throws IOException {
    StringBuilder fn = new StringBuilder(getCurrentCompany().getName());
    fn.append(" - ????.xlsx");
    Workbook wb = new XSSFWorkbook();
    DataFormat df = wb.createDataFormat();
    // ?10??
    Font bFont = wb.createFont();
    bFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bFont.setFontName("");
    bFont.setFontHeightInPoints((short) 10);
    // ?10?
    Font nFont = wb.createFont();
    nFont.setFontName("");
    nFont.setFontHeightInPoints((short) 10);
    // ??1???????
    CellStyle csLeftTextBold = wb.createCellStyle();
    csLeftTextBold.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftTextBold.setFont(bFont);
    csLeftTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csLeftTextBold.setWrapText(false);
    // ??2???????
    CellStyle csLeftText = wb.createCellStyle();
    csLeftText.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftText.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftText.setFont(nFont);
    csLeftText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csLeftText.setWrapText(false);
    // ??3???????
    CellStyle csLeftTime = wb.createCellStyle();
    csLeftTime.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftTime.setFont(nFont);
    csLeftTime.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm"));
    csLeftTime.setWrapText(false);
    // ??4???????
    CellStyle csCenterTextBold = wb.createCellStyle();
    csCenterTextBold.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterTextBold.setFont(bFont);
    csCenterTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csCenterTextBold.setWrapText(false);
    // ??5???????
    CellStyle csCenterText = wb.createCellStyle();
    csCenterText.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterText.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterText.setFont(nFont);
    csCenterText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csCenterText.setWrapText(false);
    // ??6???????
    CellStyle csCenterNumber = wb.createCellStyle();
    csCenterNumber.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterNumber.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterNumber.setFont(nFont);
    csCenterNumber.setDataFormat(df.getFormat("#"));
    csCenterNumber.setWrapText(false);
    // ??7????8????
    CellStyle csLeftId = wb.createCellStyle();
    csLeftId.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftId.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftId.setFont(nFont);
    csLeftId.setDataFormat(df.getFormat("#00000000"));
    csLeftId.setWrapText(false);
    // ??8???????
    CellStyle csCenterNumberBold = wb.createCellStyle();
    csCenterNumberBold.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterNumberBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterNumberBold.setFont(bFont);
    csCenterNumberBold.setDataFormat(df.getFormat("#"));
    csCenterNumberBold.setWrapText(false);
    // ???sheet
    for (long id : idList) {
        AccessoryEntryInvoice invoice = arService.loadEntryInvoice(id);
        if (null == invoice) {
            continue;
        }
        Sheet sheet = wb.createSheet("?#" + id);
        // ??xxxxxx
        Row row = sheet.createRow(0);
        row.setHeightInPoints(20);
        Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftTextBold);
        cell.setCellValue("??");
        cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csLeftId);
        cell.setCellValue(id);
        // yyyy-MM-dd HH:mm
        row = sheet.createRow(1);
        row.setHeightInPoints(20);
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftTextBold);
        cell.setCellValue("");
        cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csLeftTime);
        cell.setCellValue(invoice.getTime());
        // xxx
        row = sheet.createRow(2);
        row.setHeightInPoints(20);
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftTextBold);
        cell.setCellValue("");
        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftText);
        cell.setCellValue(invoice.getOperator().getName());
        // 
        row = sheet.createRow(3);
        row.setHeightInPoints(20);
        for (int i = 0; i < 8; i++) {
            cell = row.createCell(i, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterTextBold);
            cell.setCellValue(ENT_HEADERS[i]);
            sheet.setColumnWidth(i, ENT_HEADERS_WIDTH[i] * 256);
        }
        // 
        List<AccessoryEntry> entries = invoice.getEntries();
        int rowNumber = 4;
        int totalQuantity = 0;
        int totalRemained = 0;
        for (AccessoryEntry entry : entries) {
            Accessory acs = entry.getAccessory();
            row = sheet.createRow(rowNumber);
            row.setHeightInPoints(20);
            cell = row.createCell(0, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getType().getTitle());
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getName());
            cell = row.createCell(2, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getBrand());
            cell = row.createCell(3, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getModel());
            cell = row.createCell(4, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getDescription());
            cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(csCenterNumber);
            cell.setCellValue(entry.getQuantity());
            totalQuantity += entry.getQuantity();
            cell = row.createCell(6, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getUnit());
            cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(csCenterNumber);
            cell.setCellValue(entry.getRemained());
            totalRemained += entry.getRemained();
            rowNumber++;
        }
        // ?
        row = sheet.createRow(rowNumber);
        row.setHeightInPoints(20);
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csCenterTextBold);
        cell.setCellValue("?");
        cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterNumberBold);
        cell.setCellValue(entries.size());
        cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterNumberBold);
        cell.setCellValue(totalQuantity);
        cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterNumberBold);
        cell.setCellValue(totalRemained);
    }

    String filename = null;
    if (isIE()) {
        filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20");
    } else {
        filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1");
    }
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-disposition", "attachment; filename=" + filename);
    BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096);
    wb.write(out);
    wb.close();
    out.close();
}

From source file:com.jeans.iservlet.controller.impl.ExportController.java

/**
 * ????/* w  w  w  .  jav a 2  s .co  m*/
 * 
 * @param ids
 *            ???(AccessoryDischargeInvoice.id)?
 * @return
 * @throws IOException
 */
private void exportAccessoryDischarges(List<Long> idList) throws IOException {
    StringBuilder fn = new StringBuilder(getCurrentCompany().getName());
    fn.append(" - ????.xlsx");
    Workbook wb = new XSSFWorkbook();
    DataFormat df = wb.createDataFormat();
    // ?10??
    Font bFont = wb.createFont();
    bFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bFont.setFontName("");
    bFont.setFontHeightInPoints((short) 10);
    // ?10?
    Font nFont = wb.createFont();
    nFont.setFontName("");
    nFont.setFontHeightInPoints((short) 10);
    // ??1???????
    CellStyle csLeftTextBold = wb.createCellStyle();
    csLeftTextBold.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftTextBold.setFont(bFont);
    csLeftTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csLeftTextBold.setWrapText(false);
    // ??2???????
    CellStyle csLeftText = wb.createCellStyle();
    csLeftText.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftText.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftText.setFont(nFont);
    csLeftText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csLeftText.setWrapText(false);
    // ??3???????
    CellStyle csLeftTime = wb.createCellStyle();
    csLeftTime.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftTime.setFont(nFont);
    csLeftTime.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm"));
    csLeftTime.setWrapText(false);
    // ??4???????
    CellStyle csCenterTextBold = wb.createCellStyle();
    csCenterTextBold.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterTextBold.setFont(bFont);
    csCenterTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csCenterTextBold.setWrapText(false);
    // ??5???????
    CellStyle csCenterText = wb.createCellStyle();
    csCenterText.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterText.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterText.setFont(nFont);
    csCenterText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csCenterText.setWrapText(false);
    // ??6???????
    CellStyle csCenterNumber = wb.createCellStyle();
    csCenterNumber.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterNumber.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterNumber.setFont(nFont);
    csCenterNumber.setDataFormat(df.getFormat("#"));
    csCenterNumber.setWrapText(false);
    // ??7????8????
    CellStyle csLeftId = wb.createCellStyle();
    csLeftId.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftId.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftId.setFont(nFont);
    csLeftId.setDataFormat(df.getFormat("#00000000"));
    csLeftId.setWrapText(false);
    // ??8???????
    CellStyle csCenterNumberBold = wb.createCellStyle();
    csCenterNumberBold.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterNumberBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterNumberBold.setFont(bFont);
    csCenterNumberBold.setDataFormat(df.getFormat("#"));
    csCenterNumberBold.setWrapText(false);
    // ???sheet
    for (long id : idList) {
        AccessoryDischargeInvoice invoice = arService.loadDischargeInvoice(id);
        if (null == invoice) {
            continue;
        }
        Sheet sheet = wb.createSheet("?#" + id);
        // ??xxxxxx
        Row row = sheet.createRow(0);
        row.setHeightInPoints(20);
        Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftTextBold);
        cell.setCellValue("??");
        cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csLeftId);
        cell.setCellValue(id);
        // yyyy-MM-dd HH:mm
        row = sheet.createRow(1);
        row.setHeightInPoints(20);
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftTextBold);
        cell.setCellValue("");
        cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csLeftTime);
        cell.setCellValue(invoice.getTime());
        // xxx
        row = sheet.createRow(2);
        row.setHeightInPoints(20);
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftTextBold);
        cell.setCellValue("");
        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftText);
        cell.setCellValue(invoice.getApplicant().getName());
        // xxx
        row = sheet.createRow(3);
        row.setHeightInPoints(20);
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftTextBold);
        cell.setCellValue("");
        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csLeftText);
        cell.setCellValue(invoice.getOperator().getName());
        // 
        row = sheet.createRow(4);
        row.setHeightInPoints(20);
        for (int i = 0; i < 7; i++) {
            cell = row.createCell(i, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterTextBold);
            cell.setCellValue(DIS_HEADERS[i]);
            sheet.setColumnWidth(i, DIS_HEADERS_WIDTH[i] * 256);
        }
        // 
        List<AccessoryDischarge> discharges = invoice.getDischarges();
        int rowNumber = 5;
        int totalQuantity = 0;
        for (AccessoryDischarge discharge : discharges) {
            Accessory acs = discharge.getAccessory();
            row = sheet.createRow(rowNumber);
            row.setHeightInPoints(20);
            cell = row.createCell(0, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getType().getTitle());
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getName());
            cell = row.createCell(2, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getBrand());
            cell = row.createCell(3, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getModel());
            cell = row.createCell(4, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getDescription());
            cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(csCenterNumber);
            cell.setCellValue(discharge.getQuantity());
            totalQuantity += discharge.getQuantity();
            cell = row.createCell(6, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csCenterText);
            cell.setCellValue(acs.getUnit());
            rowNumber++;
        }
        // ?
        row = sheet.createRow(rowNumber);
        row.setHeightInPoints(20);
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csCenterTextBold);
        cell.setCellValue("?");
        cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterNumberBold);
        cell.setCellValue(discharges.size());
        cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterNumberBold);
        cell.setCellValue(totalQuantity);
    }

    String filename = null;
    if (isIE()) {
        filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20");
    } else {
        filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1");
    }
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-disposition", "attachment; filename=" + filename);
    BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096);
    wb.write(out);
    wb.close();
    out.close();
}

From source file:com.jeans.iservlet.controller.impl.ExportController.java

/**
 * ?????//from w  w w  .j ava  2s  .c o m
 * 
 * @param ids
 *            ??(AccessoryInvoiceDetail.id)?
 * @return
 * @throws IOException
 */
private void exportAccessoryRegistries(List<Long> idList) throws IOException {
    StringBuilder fn = new StringBuilder(getCurrentCompany().getName());
    fn.append(" - ???.xlsx");
    Workbook wb = new XSSFWorkbook();
    DataFormat df = wb.createDataFormat();
    // ?10??
    Font bFont = wb.createFont();
    bFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bFont.setFontName("");
    bFont.setFontHeightInPoints((short) 10);
    // ?10?
    Font nFont = wb.createFont();
    nFont.setFontName("");
    nFont.setFontHeightInPoints((short) 10);
    // ??1???????
    CellStyle csLeftTextBold = wb.createCellStyle();
    csLeftTextBold.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftTextBold.setFont(bFont);
    csLeftTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csLeftTextBold.setWrapText(false);
    // ??2???????
    CellStyle csLeftText = wb.createCellStyle();
    csLeftText.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftText.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftText.setFont(nFont);
    csLeftText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csLeftText.setWrapText(false);
    // ??3???????
    CellStyle csCenterTime = wb.createCellStyle();
    csCenterTime.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterTime.setFont(nFont);
    csCenterTime.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm"));
    csCenterTime.setWrapText(false);
    // ??4???????
    CellStyle csCenterTextBold = wb.createCellStyle();
    csCenterTextBold.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterTextBold.setFont(bFont);
    csCenterTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csCenterTextBold.setWrapText(false);
    // ??5???????
    CellStyle csCenterText = wb.createCellStyle();
    csCenterText.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterText.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterText.setFont(nFont);
    csCenterText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    csCenterText.setWrapText(false);
    // ??6???????
    CellStyle csCenterNumber = wb.createCellStyle();
    csCenterNumber.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterNumber.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterNumber.setFont(nFont);
    csCenterNumber.setDataFormat(df.getFormat("#"));
    csCenterNumber.setWrapText(false);
    // ??7????8????
    CellStyle csLeftId = wb.createCellStyle();
    csLeftId.setAlignment(CellStyle.ALIGN_LEFT);
    csLeftId.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csLeftId.setFont(nFont);
    csLeftId.setDataFormat(df.getFormat("#00000000"));
    csLeftId.setWrapText(false);
    // ??8????8????
    CellStyle csCenterId = wb.createCellStyle();
    csCenterId.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterId.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterId.setFont(nFont);
    csCenterId.setDataFormat(df.getFormat("#00000000"));
    csCenterId.setWrapText(false);
    // ??9???????
    CellStyle csCenterNumberBold = wb.createCellStyle();
    csCenterNumberBold.setAlignment(CellStyle.ALIGN_CENTER);
    csCenterNumberBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    csCenterNumberBold.setFont(bFont);
    csCenterNumberBold.setDataFormat(df.getFormat("#"));
    csCenterNumberBold.setWrapText(false);
    // ????sheet
    long currAcsId = 0;
    Sheet sheet = null;
    int rowNumber = 0, totalIn = 0, totalOut = 0;
    for (long id : idList) {
        AccessoryInvoiceDetail registry = arService.loadRegistry(id);
        if (null == registry) {
            continue;
        }
        Accessory acs = registry.getAccessory();
        if (acs.getId() != currAcsId) {
            // ???sheet??
            if (null != sheet) {
                Row row = sheet.createRow(rowNumber);
                row.setHeightInPoints(20);
                Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING);
                cell.setCellStyle(csCenterTextBold);
                cell.setCellValue("?");
                cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(csCenterNumberBold);
                cell.setCellValue(totalIn);
                cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(csCenterNumberBold);
                cell.setCellValue(totalOut);
            }
            // ?sheet?
            currAcsId = acs.getId();
            sheet = wb.createSheet("???#" + currAcsId);
            // ??xxxxxx
            Row row = sheet.createRow(0);
            row.setHeightInPoints(20);
            Cell cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftTextBold);
            cell.setCellValue("??");
            cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(csLeftId);
            cell.setCellValue(currAcsId);
            // xxxxxx
            row = sheet.createRow(1);
            row.setHeightInPoints(20);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftTextBold);
            cell.setCellValue("");
            cell = row.createCell(2, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftText);
            cell.setCellValue(acs.getType().getTitle());
            // ??xxxxxx
            row = sheet.createRow(2);
            row.setHeightInPoints(20);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftTextBold);
            cell.setCellValue("??");
            cell = row.createCell(2, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftText);
            cell.setCellValue(acs.getName());
            // ?xxxxxx
            row = sheet.createRow(3);
            row.setHeightInPoints(20);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftTextBold);
            cell.setCellValue("?");
            cell = row.createCell(2, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftText);
            cell.setCellValue(acs.getBrand());
            // ?xxxxxx
            row = sheet.createRow(4);
            row.setHeightInPoints(20);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftTextBold);
            cell.setCellValue("?");
            cell = row.createCell(2, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftText);
            cell.setCellValue(acs.getModel());
            // ??xxxxxx
            row = sheet.createRow(5);
            row.setHeightInPoints(20);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftTextBold);
            cell.setCellValue("???");
            cell = row.createCell(2, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftText);
            cell.setCellValue(acs.getDescription());
            // ???xxxxxx
            row = sheet.createRow(6);
            row.setHeightInPoints(20);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftTextBold);
            cell.setCellValue("???");
            cell = row.createCell(2, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(csLeftText);
            cell.setCellValue(acs.getUnit());
            // 
            row = sheet.createRow(7);
            row.setHeightInPoints(20);
            for (int i = 0; i < 6; i++) {
                cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                cell.setCellStyle(csCenterTextBold);
                cell.setCellValue(REG_HEADERS[i]);
                sheet.setColumnWidth(i, REG_HEADERS_WIDTH[i] * 256);
            }
            rowNumber = 8;
            totalIn = 0;
            totalOut = 0;
        }
        // ?
        Row row = sheet.createRow(rowNumber);
        row.setHeightInPoints(20);
        Cell cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterNumber);
        cell.setCellValue(rowNumber - 7);
        cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterTime);
        cell.setCellValue(registry.getInvoice().getTime());
        cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterId);
        cell.setCellValue(registry.getInvoice().getId());
        cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterNumber);
        cell.setCellValue(id > 0 ? registry.getQuantity() : 0);
        cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(csCenterNumber);
        cell.setCellValue(id < 0 ? registry.getQuantity() : 0);
        cell = row.createCell(5, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(csCenterText);
        cell.setCellValue(
                id < 0 ? ((AccessoryDischargeInvoice) registry.getInvoice()).getApplicant().getName() : "");
        if (id > 0) {
            totalIn += registry.getQuantity();
        } else {
            totalOut += registry.getQuantity();
        }
        rowNumber++;
    }

    String filename = null;
    if (isIE()) {
        filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20");
    } else {
        filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1");
    }
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-disposition", "attachment; filename=" + filename);
    BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096);
    wb.write(out);
    wb.close();
    out.close();
}

From source file:com.jeans.iservlet.controller.impl.ExportController.java

/**
 * ???//from w ww  .  ja  v  a 2 s.  co m
 * 
 * @return
 * @throws IOException
 */
@RequestMapping(method = RequestMethod.POST, value = "/systems")
public ResponseEntity<byte[]> exportITSystems() throws IOException {
    StringBuilder fn = new StringBuilder(getCurrentCompany().getName());
    fn.append(" - ??(");
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("?");
    // ?
    // 
    // ?10??
    Font tFont = sheet.getWorkbook().createFont();
    tFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    tFont.setFontName("");
    tFont.setFontHeightInPoints((short) 10);
    // ?????????
    CellStyle cellStyleTitle = sheet.getWorkbook().createCellStyle();
    cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleTitle.setFont(tFont);
    cellStyleTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyleTitle.setWrapText(false);
    // 20
    Row row = sheet.createRow(0);
    row.setHeightInPoints(20);
    Cell cell = null;
    for (int i = 0; i < ITSYSTEM_HEADERS.length; i++) {
        cell = row.createCell(i, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleTitle);
        cell.setCellValue(ITSYSTEM_HEADERS[i]);
        sheet.setColumnWidth(i, ITSYSTEM_HEADERS_WIDTH[i] * 256);
    }
    // ?????->?->????
    List<ITSystem> systems = new ArrayList<ITSystem>(systService.listSystems(getCurrentCompany(), null, null));
    Collections.sort(systems, new Comparator<ITSystem>() {

        @Override
        public int compare(ITSystem o1, ITSystem o2) {
            int ret = o1.getType().ordinal() - o2.getType().ordinal();
            if (ret == 0) {
                ret = Long.compare(o1.getOwner().getId(), o2.getOwner().getId());
                if (ret == 0) {
                    ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName());
                }
            }
            return ret;
        }

    });
    // ??
    DataFormat df = sheet.getWorkbook().createDataFormat();
    // ?10?
    Font font = sheet.getWorkbook().createFont();
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ?1???????
    CellStyle cellStyleString = sheet.getWorkbook().createCellStyle();
    cellStyleString.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleString.setFont(font);
    cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyleString.setWrapText(false);
    // ?2????(yyyyMMdd)???
    CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle();
    cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleDate.setFont(font);
    cellStyleDate.setDataFormat(df.getFormat("yyyyMMdd"));
    cellStyleDate.setWrapText(false);
    // ?3??????(#)???
    CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle();
    cellStyleQuantity.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleQuantity.setFont(font);
    cellStyleQuantity.setDataFormat(df.getFormat("0"));
    cellStyleQuantity.setWrapText(false);
    // ?4?????(#,##0.00_ )???
    CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle();
    cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleCost.setFont(font);
    cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ "));
    cellStyleCost.setWrapText(false);
    // sheet
    int rowNumber = 1;
    for (ITSystem system : systems) {
        // 20
        row = sheet.createRow(rowNumber);
        row.setHeightInPoints(20);
        // ?
        Set<SystemBranch> branches = system.getBranches();
        SystemBranch localBranch = null; // ??
        long localId = 0;
        if (getCurrentCompany().getLevel() == Company.BRANCH) {
            localId = getCurrentCompany().getSuperior().getId();
        } else {
            localId = getCurrentCompany().getId();
        }
        BigDecimal cost = new BigDecimal("0.0"); // 
        for (SystemBranch branch : branches) {
            cost.add(branch.getCost());
            if (branch.getCompany().getId() == localId) {
                localBranch = branch;
            }
        }
        boolean branched = (localBranch != null); // ?
        boolean owned = system.getOwner().getId() == getCurrentCompany().getId(); // ?????
        // 
        // 
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getType().getTitle());
        // ??
        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getName());
        // 
        cell = row.createCell(2, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getAlias());
        // /?
        cell = row.createCell(3, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getModelOrVersion());
        // 
        cell = row.createCell(4, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getBrief());
        // ?
        cell = row.createCell(5, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(ITSYSTEM_sLevel[system.getSecurityLevel()]);
        // ???
        cell = row.createCell(6, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getSecurityCode());
        // ?
        cell = row.createCell(7, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getUsersBrief());
        // 
        cell = row.createCell(8, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(null == system.getProvider() ? "" : system.getProvider().getAlias());
        // ?
        cell = row.createCell(9, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getOwner().getAlias());
        // 
        cell = row.createCell(10, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getScope().getTitle(system.getOwner().getLevel()));
        // 
        cell = row.createCell(11, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(system.getDeploy().getTitle());
        // 
        cell = row.createCell(12, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(branched ? "" : "");
        if (branched) {
            // ?()
            cell = row.createCell(13, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(cellStyleCost);
            cell.setCellValue(localBranch.getCost().doubleValue());
            // ?
            cell = row.createCell(14, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyleString);
            cell.setCellValue(localBranch.getStage().getTitle());
            // ?
            cell = row.createCell(15, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(cellStyleDate);
            Date ct = localBranch.getConstructedTime();
            if (null != ct) {
                cell.setCellValue(ct);
            }
            // ?
            cell = row.createCell(16, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(cellStyleDate);
            Date at = localBranch.getAbandonedTime();
            if (null != at) {
                cell.setCellValue(at);
            }
        }
        // ??
        cell = row.createCell(17, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleQuantity);
        cell.setCellValue(system.getFreeMaintainMonths());
        if (owned) {
            // 
            cell = row.createCell(18, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(cellStyleQuantity);
            cell.setCellValue(branches.size());
            // ?)
            cell = row.createCell(19, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(cellStyleCost);
            cell.setCellValue(cost.doubleValue());
            // ??
            cell = row.createCell(20, Cell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyleString);
            cell.setCellValue(system.getStage().getTitle());
            // ?
            cell = row.createCell(21, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(cellStyleDate);
            Date ct = system.getConstructedTime();
            if (null != ct) {
                cell.setCellValue(ct);
            }
            // ?
            cell = row.createCell(22, Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(cellStyleDate);
            Date at = system.getAbandonedTime();
            if (null != at) {
                cell.setCellValue(at);
            }
        }
        rowNumber++;
    }

    fn.append((new SimpleDateFormat("yyyyMMdd")).format(new Date())).append(").xlsx");
    String filename = null;
    if (isIE()) {
        filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20");
    } else {
        filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1");
    }
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-disposition", "attachment; filename=" + filename);
    BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096);
    wb.write(out);
    wb.close();
    out.close();
    return null;
}