com.jeans.iservlet.controller.impl.ExportController.java Source code

Java tutorial

Introduction

Here is the source code for com.jeans.iservlet.controller.impl.ExportController.java

Source

package com.jeans.iservlet.controller.impl;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.Collator;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import com.jeans.iservlet.controller.BaseController;
import com.jeans.iservlet.model.AssetConstants;
import com.jeans.iservlet.model.asset.Accessory;
import com.jeans.iservlet.model.asset.AccessoryDischarge;
import com.jeans.iservlet.model.asset.AccessoryDischargeInvoice;
import com.jeans.iservlet.model.asset.AccessoryEntry;
import com.jeans.iservlet.model.asset.AccessoryEntryInvoice;
import com.jeans.iservlet.model.asset.AccessoryInvoiceDetail;
import com.jeans.iservlet.model.asset.Asset;
import com.jeans.iservlet.model.asset.Hardware;
import com.jeans.iservlet.model.asset.Software;
import com.jeans.iservlet.model.hr.Company;
import com.jeans.iservlet.model.hr.Employee;
import com.jeans.iservlet.model.it.ITSystem;
import com.jeans.iservlet.model.it.SystemBranch;
import com.jeans.iservlet.service.asset.AccessoryReportService;
import com.jeans.iservlet.service.asset.AccessoryService;
import com.jeans.iservlet.service.asset.AssetService;
import com.jeans.iservlet.service.hr.HRService;
import com.jeans.iservlet.service.it.ITSystemService;

@Controller
@RequestMapping("/export")
public class ExportController extends BaseController {
    @Autowired
    private HttpServletRequest request;

    @Autowired
    private HttpServletResponse response;

    @Autowired
    private AssetService astService;

    @Autowired
    private AccessoryService acsService;

    @Autowired
    private AccessoryReportService arService;

    @Autowired
    private ITSystemService systService;

    @Autowired
    private HRService hrService;

    private boolean isIE() {
        String ua = request.getHeader("user-agent").toUpperCase();
        if ((ua != null) && (ua.indexOf("MSIE") != -1 || ua.indexOf("TRIDENT") != -1)) {
            return true;
        } else {
            return false;
        }
    }

    private void export(String type) throws IOException {
        StringBuilder fn = new StringBuilder(getCurrentCompany().getName());
        Workbook wb = new XSSFWorkbook();
        if ("_hard".equals(type)) {
            fn.append(" - ?(");
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.NETWORK_EQUIPMENT)),
                    AssetConstants.NETWORK_EQUIPMENT, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.SECURITY_EQUIPMENT)),
                    AssetConstants.SECURITY_EQUIPMENT, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.SERVER_EQUIPMENT)),
                    AssetConstants.SERVER_EQUIPMENT, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.STORAGE_EQUIPMENT)),
                    AssetConstants.STORAGE_EQUIPMENT, getCurrentCompany());
            generateSheet(
                    wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.INFRASTRUCTURE_EQUIPMENT)),
                    AssetConstants.INFRASTRUCTURE_EQUIPMENT, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.TERMINATOR_EQUIPMENT)),
                    AssetConstants.TERMINATOR_EQUIPMENT, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.MOBILE_EQUIPMENT)),
                    AssetConstants.MOBILE_EQUIPMENT, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.PRINTER_EQUIPMENT)),
                    AssetConstants.PRINTER_EQUIPMENT, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.OTHER_EQUIPMENT)),
                    AssetConstants.OTHER_EQUIPMENT, getCurrentCompany());
        } else if ("_soft".equals(type)) {
            fn.append(" - ?(");
            generateSheet(
                    wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.OPERATING_SYSTEM_SOFTWARE)),
                    AssetConstants.OPERATING_SYSTEM_SOFTWARE, getCurrentCompany());
            generateSheet(
                    wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.DATABASE_SYSTEM_SOFTWARE)),
                    AssetConstants.DATABASE_SYSTEM_SOFTWARE, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.MIDDLEWARE_SOFTWARE)),
                    AssetConstants.MIDDLEWARE_SOFTWARE, getCurrentCompany());
            generateSheet(
                    wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.STORAGE_SYSTEM_SOFTWARE)),
                    AssetConstants.STORAGE_SYSTEM_SOFTWARE, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.SECURITY_SOFTWARE)),
                    AssetConstants.SECURITY_SOFTWARE, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.OFFICE_SOFTWARE)),
                    AssetConstants.OFFICE_SOFTWARE, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.APPLICATION_SOFTWARE)),
                    AssetConstants.APPLICATION_SOFTWARE, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.OTHER_SOFTWARE)),
                    AssetConstants.OTHER_SOFTWARE, getCurrentCompany());
        } else if ("_all".equals(type)) {
            fn.append(" - IT?(");
            generateSheet(wb.createSheet(AssetConstants.getAssetTypeName(AssetConstants.HARDWARE_ASSET)),
                    AssetConstants.HARDWARE_ASSET, getCurrentCompany());
            generateSheet(wb.createSheet(AssetConstants.getAssetTypeName(AssetConstants.SOFTWARE_ASSET)),
                    AssetConstants.SOFTWARE_ASSET, getCurrentCompany());
        } else if ("_global".equals(type)) {
            fn.append(" - IT?(");
            for (Company comp : hrService.listCompaniesInRegion(getCurrentCompany().getId())) {
                generateSheet(
                        wb.createSheet(comp.getAlias() + " - "
                                + AssetConstants.getAssetTypeName(AssetConstants.HARDWARE_ASSET)),
                        AssetConstants.HARDWARE_ASSET, comp);
                generateSheet(
                        wb.createSheet(comp.getAlias() + " - "
                                + AssetConstants.getAssetTypeName(AssetConstants.SOFTWARE_ASSET)),
                        AssetConstants.SOFTWARE_ASSET, comp);
            }
        }
        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();
    }

    private void generateSheet(Sheet sheet, byte type, Company company) {
        // sheet
        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);
        }
    }

    private static final String[] HARDWARE_HEADERS = new String[] { "?", "?",
            "?", "", "??", "/?", "?", "?",
            "??", "??", "", "?", "", "", "??",
            "??", "?", "??", "", "" };
    private static final int[] HARDWARE_HEADERS_WIDTH = new int[] { 12, 12, 12, 12, 20, 12, 24, 60, 24, 60, 11, 5,
            13, 9, 9, 30, 16, 9, 9, 40 };
    private static final String[] SOFTWARE_HEADERS = new String[] { "?", "", "??",
            "", "", "?", "", "?", "",
            "", "", "??", "??", "" };
    private static final int[] SOFTWARE_HEADERS_WIDTH = new int[] { 12, 12, 20, 12, 24, 60, 11, 5, 13, 9, 14, 20,
            11, 40 };

    private void appendRow(Sheet sheet, Asset asset, int rowNumber, CellStyle cellStyleString,
            CellStyle cellStyleDate, CellStyle cellStyleQuantity, CellStyle cellStyleCost) {
        // 
        // 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());
        }
    }

    private void generateSheetHeader(Sheet sheet, boolean hardware) {
        // 
        // ?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);
            }
        }
    }

    /**
     * ???????
     * 
     * @param type
     *            "_all", "_hard", "_soft", "_global""_all"
     * @return
     * @throws IOException
     */
    @RequestMapping(method = RequestMethod.POST, value = "/assets")
    public ResponseEntity<byte[]> exportAssets(@RequestParam String type) throws IOException {
        if (StringUtils.isBlank(type)) {
            type = "_all";
        }
        if ("_global".equals(type) && getCurrentCompany().getLevel() != Company.CITY) {
            type = "_all";
        }
        export(type);
        return null;
    }

    private static final String[] ACS_HEADERS = new String[] { "", "??", "?", "?",
            "?", "???", "??" };
    private static final int[] ACS_HEADERS_WIDTH = new int[] { 12, 16, 12, 30, 8, 8, 50 };

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

    private static final String[] ENT_HEADERS = new String[] { "", "??", "?", "?",
            "???", "?", "??", "" };
    private static final int[] ENT_HEADERS_WIDTH = new int[] { 12, 16, 12, 30, 50, 10, 8, 10 };

    private static final String[] DIS_HEADERS = new String[] { "", "??", "?", "?",
            "???", "?", "??" };
    private static final int[] DIS_HEADERS_WIDTH = new int[] { 12, 16, 12, 30, 50, 10, 8 };

    private static final String[] REG_HEADERS = new String[] { "??", "", "??",
            "?", "?", "" };
    private static final int[] REG_HEADERS_WIDTH = new int[] { 8, 16, 16, 10, 10, 12 };

    /**
     * ?????
     * 
     * @param type
     *            ??0=?1=?2=
     * @param ids
     *            ?idid?
     * @return
     * @throws IOException
     */
    @RequestMapping(method = RequestMethod.POST, value = "/acs-report")
    public ResponseEntity<byte[]> exportAccessoryReport(@RequestParam int type, @RequestParam String ids)
            throws IOException {
        List<Long> idList = splitIdsToList(ids);
        if (!idList.isEmpty()) {
            switch (type) {
            case 0:
                exportAccessoryEntries(idList);
                break;
            case 1:
                exportAccessoryDischarges(idList);
                break;
            case 2:
                exportAccessoryRegistries(idList);
                break;
            default:
            }
        }
        return null;
    }

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

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

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

    private static final String[] ITSYSTEM_HEADERS = new String[] { "   ", "??", "",
            "/?", "", "?", "???", "?",
            "", "?", "", "", "", "?()",
            "?", "?", "?", "??()", "",
            "?()", "?", "?", "?" };
    private static final int[] ITSYSTEM_HEADERS_WIDTH = new int[] { 16, 20, 12, 24, 40, 10, 16, 40, 18, 12, 16, 16,
            12, 16, 12, 16, 16, 16, 10, 16, 12, 16, 16 };
    private static final String[] ITSYSTEM_sLevel = new String[] { "", "", "", "" };

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