Example usage for org.apache.poi.ss.usermodel Sheet createRow

List of usage examples for org.apache.poi.ss.usermodel Sheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. createRow

Introduction

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

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

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

/**
 * ????/*  w  w  w . java2  s. c  om*/
 * 
 * @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

/**
 * ????/*from  w w  w  .  java2  s .c o  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  www .  ja v  a2s  .c  om*/
 * 
 * @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

/**
 * ???/*  w  ww .jav  a2  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;
}

From source file:com.jfinal.ext.kit.PoiKit.java

License:Apache License

public Workbook export() {
    if (headers == null) {
        System.out.println("headers can not be null");
        return null;
    }//  ww w . j  av  a 2 s .c o  m
    if (columns == null) {
        System.out.println("columns can not be null");
        return null;
    }
    if (cellWidth < 0) {
        System.out.println("cellWidth < 0");
        return null;
    }
    Workbook wb;
    wb = new HSSFWorkbook();
    if (data.length > 1) {
        for (List<?> item : data) {
            if (item.size() >= MAX_ROWS) {
                System.out
                        .println("Invalid data size (" + item.size() + ") outside allowable range (0..65535)");
            }
        }
    } else if (data[0].size() > MAX_ROWS) {
        data = dice(data[0], MAX_ROWS).toArray(new List<?>[] {});
    }
    if (data.length == 0) {
        return wb;
    }
    for (int i = 0; i < data.length; i++) {
        Sheet sheet = wb.createSheet(sheetName + (i == 0 ? "" : (i + 1)));
        Row row;
        Cell cell;
        if (headers.length > 0) {
            row = sheet.createRow(0);
            if (headerRow <= 0) {
                headerRow = HEADER_ROW;
            }
            headerRow = Math.min(headerRow, MAX_ROWS);
            for (int h = 0, lenH = headers.length; h < lenH; h++) {
                if (cellWidth > 0) {
                    sheet.setColumnWidth(h, cellWidth);
                }
                cell = row.createCell(h);
                cell.setCellValue(headers[h]);
            }
        }

        for (int j = 0, len = data[i].size(); j < len; j++) {
            row = sheet.createRow(j + headerRow);
            Object obj = data[i].get(j);
            if (obj == null) {
                continue;
            }
            if (obj instanceof Map) {
                processAsMap(columns, row, obj);
            } else if (obj instanceof Model) {
                processAsModel(columns, row, obj);
            } else if (obj instanceof Record) {
                processAsRecord(columns, row, obj);
            }
        }
    }
    return wb;
}

From source file:com.jogo.dao.RepositorioDao.java

public void exportar(File arquivo, List<Usuario> Usuario, List<Perguntas> capAmerica,
        List<Perguntas> oSenhorAneis, List<Perguntas> batmam, List<Perguntas> veloFur,
        List<Perguntas> harryPotter, List<Perguntas> instrMortais, List<Perguntas> cronicNarnia,
        List<Perguntas> varios) {
    //CRIO MEU ARQUIVO DE EXCEL. INSTANCIOANDO DE XSSFWorkbook
    wb = new XSSFWorkbook();

    try {//w w  w .  j av  a2s . c o m

        //SALVAR A FOLHA 1 ONDE ESTO OS USUARIOS.    
        Sheet folha = wb.createSheet("USUARIO");
        int contador = 0;

        //CRIO UM FORACH DE ARRAY LIST DE USUARIO
        for (Usuario u : Usuario) {

            //CRIO A LINHA PASSANDO O CONTADOR(INDEX DA LINHA) E CAPTURO A LINHA
            Row fila = folha.createRow(contador);

            //ENCREMENTO O CONTADOR PARA IR PARA A PROXIMA LINHA.
            contador++;

            //AGORA USO UM FOR PARA PEGAR AS COLUNAS, QUE SO DUAS COLUNAS.
            for (int coluna = 0; coluna < 2; coluna++) {

                //TENDO A LINHA E COLUNA JA POSSO TER MINHA CELULA.
                Cell celula = fila.createCell(coluna);
                //SE A COLUNA FOR A 0 EU ADD O NOME NELA, SENAO ADD A PONTUAO.
                //FICARIA COLUNA 1 - NOME , COLUNA 2 - PONTUAO.
                if (coluna == 0) {
                    celula.setCellValue(u.getNome());
                } else {
                    celula.setCellValue(u.getPontuacao());
                }
                //SE O ARQUIVO NO EXISTIR ELE CRIAR E SE J? EXISTIR ELE SUBSTITUI.
                //NESSE CASO O ARQUIVO SEMPRE EXISTIRAR, PIS  NELE QUE EST? AS PERGUNTAS SALVAS.
                if (!arquivo.exists()) {
                    wb.write(new FileOutputStream(arquivo));
                } else {
                    wb.write(new FileOutputStream(arquivo));
                }

            }
        }
        //CHAMANDO O METODO E PASSANDO O INDEX PARA CADA FOLHA ONDE SERA SALVA.
        exportarPergunta(1, capAmerica);
        exportarPergunta(2, oSenhorAneis);
        exportarPergunta(3, batmam);
        exportarPergunta(4, veloFur);
        exportarPergunta(5, harryPotter);
        exportarPergunta(6, instrMortais);
        exportarPergunta(7, cronicNarnia);
        exportarPergunta(8, varios);
        wb.write(new FileOutputStream(arquivo));
    } catch (Exception e) {
    }
}

From source file:com.jogo.dao.RepositorioDao.java

public void exportarPergunta(int index, List<Perguntas> perguntas) {
    //NOMEAR AS FOLHAS NO EXCEL COM O NOME DA CATEGORIA.
    String nome = "";
    switch (index) {
    case 1://  w ww . j a va  2s. co  m
        nome = "CAPITO AMERICA";
        break;
    case 2:
        nome = "O SENHOR DOS ANIS";
        break;
    case 3:
        nome = "BATMAN";
        break;
    case 4:
        nome = "VELOZES E FURIOSOS";
        break;
    case 5:
        nome = "HARRY POTTER";
        break;
    case 6:
        nome = "OS INSTRUMENTOS MORTAIS";
        break;
    case 7:
        nome = "AS CRONICAS DE NARNIA";
        break;
    case 8:
        nome = "VARIOS FILMES";
        break;
    }

    //IGUAL AO PASSO DO USUARIO, AGORA SENDO COM AS PERGUNTAS.
    Sheet folha2 = wb.createSheet(nome);
    int contador2 = 0;
    for (Perguntas p : perguntas) {

        Row fila2 = folha2.createRow(contador2);
        contador2++;
        for (int coluna2 = 0; coluna2 < 6; coluna2++) {
            Cell celula2 = fila2.createCell(coluna2);

            switch (coluna2) {
            case 0:
                celula2.setCellValue(p.getPergunta());
                break;
            case 1:
                celula2.setCellValue(p.getAlt1());
                break;
            case 2:
                celula2.setCellValue(p.getAlt2());
                break;
            case 3:
                celula2.setCellValue(p.getAlt3());
                break;
            case 4:
                celula2.setCellValue(p.getAlt4());
                break;
            case 5:
                celula2.setCellValue(p.getResposta());
                break;
            default:
                break;
            }
        }
    }

}

From source file:com.jshuabo.reportcenter.server.service.automoblie.impl.DefaultAutoRecordServiceImpl.java

License:Open Source License

@Override
public void importToExcel(Map<String, Object> exprotMap, HttpServletResponse response,
        HttpServletRequest request, String[] title, String excelName) {

    Map<String, Object> pageData = autoRecordDataMapper.exprotPageData(exprotMap);

    SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows

    int flag = 1; // 
    int rowCount = 150000; // ?sheet 
    long count = (Long) pageData.get("COUNT");
    long pageSize = rowCount; // ??
    long page = 1;
    if (rowCount < count) {
        if (count % pageSize == 0) {
        } else {//from   ww  w .  j a  v  a  2s. c  om
            page = count / pageSize + 1;
        } // 
    }

    Sheet sh = workbook.createSheet(excelName + System.currentTimeMillis());

    Row row = sh.createRow((short) 0); // 
    Cell cell = null;
    CellStyle[] cs = POIUtils.cellStyle(workbook);

    POIUtils.createHeard(sh, row, cell, cs, title);

    for (int i = 1; i <= page; i++) {

        long offset = (i - 1) * pageSize;
        long rows = pageSize;
        exprotMap.put("offset", Long.valueOf(offset));
        exprotMap.put("rows", Long.valueOf(rows));

        // ??
        List<AutoRecordData> autoRecordDataList = autoRecordDataMapper.exprot2Excel(exprotMap);

        if (autoRecordDataList.isEmpty())
            continue;

        for (int rownum = 0; rownum < autoRecordDataList.size(); rownum++) {
            AutoRecordData autoRecord = autoRecordDataList.get(rownum);

            String subStation = SubStationConstant.getSubStationByKey(autoRecord.getSubStation());
            String licenseDate = DateFormatUtils.format(autoRecord.getLicenseDate(), DateFormatUtils.ymd);
            String inspectionDate = DateFormatUtils.format(autoRecord.getInspectionDate(), DateFormatUtils.ymd);
            String insuranceDate = DateFormatUtils.format(autoRecord.gettLInsuranceDate(), DateFormatUtils.ymd);
            String changeDate = DateFormatUtils.format(autoRecord.getChangeDate(), DateFormatUtils.ymd);
            String strongInsDate = DateFormatUtils.format(autoRecord.getStrongInsDate(), DateFormatUtils.ymd);

            // ? ?sheet 
            if (flag > pageSize) {
                flag = 1;
                sh = workbook.createSheet(excelName + System.currentTimeMillis());
                row = sh.createRow((short) 0);
                POIUtils.createHeard(sh, row, cell, cs, title);
            }
            row = sh.createRow(flag);
            for (int cellnum = 0; cellnum < title.length; cellnum++) {
                cell = row.createCell(0);
                cell.setCellValue(subStation);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(1);
                cell.setCellValue(autoRecord.getDeputyCard());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(2);
                cell.setCellValue(autoRecord.getCarKind());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(3);
                cell.setCellValue(autoRecord.getLicenseNo());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(4);
                cell.setCellValue(autoRecord.getLicense());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(5);
                cell.setCellValue(licenseDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(6);
                cell.setCellValue(autoRecord.getLicenseName());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(7);
                cell.setCellValue(inspectionDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(8);
                cell.setCellValue(autoRecord.getName());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(9);
                cell.setCellValue(autoRecord.getIdCard());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(10);
                cell.setCellValue(String.valueOf(autoRecord.getFtReceive()));
                cell.setCellStyle(cs[1]);

                cell = row.createCell(11);
                cell.setCellValue(changeDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(12);
                cell.setCellValue(autoRecord.getTelephone());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(13);
                cell.setCellValue(strongInsDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(14);
                cell.setCellValue(String.valueOf(autoRecord.gettLInsurance()));
                cell.setCellStyle(cs[1]);

                cell = row.createCell(15);
                cell.setCellValue(insuranceDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(16);
                cell.setCellValue(autoRecord.getPoliceProve());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(17);
                cell.setCellValue(autoRecord.getHouseholdCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(18);
                cell.setCellValue(autoRecord.getIdCardCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(19);
                cell.setCellValue(autoRecord.getLicenseCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(20);
                cell.setCellValue(autoRecord.getGuaranRespon());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(21);
                cell.setCellValue(autoRecord.getGuaranIncome());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(22);
                cell.setCellValue(autoRecord.getGuaranHouseCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(23);
                cell.setCellValue(autoRecord.getGuaranIDCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(24);
                cell.setCellValue(autoRecord.getDriLicenseCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(25);
                cell.setCellValue(autoRecord.getStrongInsCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(26);
                cell.setCellValue(autoRecord.getCommerInsuCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(27);
                cell.setCellValue(autoRecord.getCertificate());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(28);
                cell.setCellValue(String.valueOf(autoRecord.getAgreeDate()));
                cell.setCellStyle(cs[1]);

                cell = row.createCell(29);
                cell.setCellValue(autoRecord.getRentalAgreement());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(30);
                cell.setCellValue(autoRecord.getStrongInsPrompt());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(31);
                cell.setCellValue(autoRecord.gettLInsurancePrompt());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(32);
                cell.setCellValue(autoRecord.getInspectionPrompt());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(33);
                cell.setCellValue(autoRecord.getChangePrompt());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(34);
                cell.setCellValue(autoRecord.getStatus());
                cell.setCellStyle(cs[1]);

            }

            flag++;
        }
    }
    POIUtils.exprot(workbook, response, excelName);
}

From source file:com.jshuabo.reportcenter.server.service.automoblie.impl.DefaultBranchLineCarInfoServiceImpl.java

License:Open Source License

@Override
public void importToExcel(Map<String, Object> exprotMap, HttpServletResponse response,
        HttpServletRequest request, String[] title, String excelName) {
    User user = SecurityContextHolder.getCurrentUser();
    if (StringUtils.isEmpty(exprotMap.get("subStation"))) {
        exprotMap.put("subStation", user.getId());
    }/*from ww w  . j a  v a 2  s .  c om*/
    Map<String, Object> pageData = carInfoMapper.exprotPageData(exprotMap);

    SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows

    int flag = 1; // 
    int rowCount = 150000; // ?sheet 
    long count = (Long) pageData.get("COUNT");
    long pageSize = rowCount; // ??
    long page = 1;
    if (rowCount < count) {
        if (count % pageSize == 0) {
        } else {
            page = count / pageSize + 1;
        } // 
    }

    Sheet sh = workbook.createSheet(excelName + System.currentTimeMillis());

    Row row = sh.createRow((short) 0); // 
    Cell cell = null;
    CellStyle[] cs = POIUtils.cellStyle(workbook);

    POIUtils.createHeard(sh, row, cell, cs, title);

    for (int i = 1; i <= page; i++) {

        long offset = (i - 1) * pageSize;
        long rows = pageSize;
        exprotMap.put("offset", Long.valueOf(offset));
        exprotMap.put("rows", Long.valueOf(rows));
        // ??
        List<BranchLineCarInfo> autoRecordDataList = carInfoMapper.exprot2Excel(exprotMap);

        if (autoRecordDataList.isEmpty())
            continue;

        for (int rownum = 0; rownum < autoRecordDataList.size(); rownum++) {
            BranchLineCarInfo branchLineCarInfo = autoRecordDataList.get(rownum);

            String subStation = branchLineCarInfo.getExtendProp3();
            String licenseDate = DateFormatUtils.format(branchLineCarInfo.getLicenseDate(),
                    DateFormatUtils.ymd);
            String inspectionDate = DateFormatUtils.format(branchLineCarInfo.getInspectionDate(),
                    DateFormatUtils.ymd);
            String insuranceDate = DateFormatUtils.format(branchLineCarInfo.getTlInsuranceDate(),
                    DateFormatUtils.ymd);
            String changeDate = DateFormatUtils.format(branchLineCarInfo.getChangeDate(), DateFormatUtils.ymd);
            String strongInsDate = DateFormatUtils.format(branchLineCarInfo.getStrongInsdate(),
                    DateFormatUtils.ymd);
            String agreeDate = DateFormatUtils.format(branchLineCarInfo.getAgreeDate(), DateFormatUtils.ymd);

            // ? ?sheet 
            if (flag > pageSize) {
                flag = 1;
                sh = workbook.createSheet(excelName + System.currentTimeMillis());
                row = sh.createRow((short) 0);
                POIUtils.createHeard(sh, row, cell, cs, title);
            }
            row = sh.createRow(flag);
            for (int cellnum = 0; cellnum < title.length; cellnum++) {
                cell = row.createCell(0);
                cell.setCellValue(subStation);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(1);
                cell.setCellValue(branchLineCarInfo.getDeputyCard());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(2);
                cell.setCellValue(branchLineCarInfo.getCarKind());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(3);
                cell.setCellValue(branchLineCarInfo.getLicenseNo());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(4);
                cell.setCellValue(branchLineCarInfo.getLicense());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(5);
                cell.setCellValue(licenseDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(6);
                cell.setCellValue(branchLineCarInfo.getLicenseName());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(7);
                cell.setCellValue(inspectionDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(8);
                cell.setCellValue(branchLineCarInfo.getName());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(9);
                cell.setCellValue(branchLineCarInfo.getIdCard());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(10);
                cell.setCellValue(String.valueOf(branchLineCarInfo.getFtReceive()));
                cell.setCellStyle(cs[1]);

                cell = row.createCell(11);
                cell.setCellValue(changeDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(12);
                cell.setCellValue(branchLineCarInfo.getTelephone());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(13);
                cell.setCellValue(strongInsDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(14);
                cell.setCellValue(StringUtils.isEmpty(branchLineCarInfo.getTlInsurance()) ? 0D
                        : branchLineCarInfo.getTlInsurance());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(15);
                cell.setCellValue(insuranceDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(16);
                cell.setCellValue(branchLineCarInfo.getPoliceProve());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(17);
                cell.setCellValue(branchLineCarInfo.getHouseholdCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(18);
                cell.setCellValue(branchLineCarInfo.getIdcardCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(19);
                cell.setCellValue(branchLineCarInfo.getLicenseCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(20);
                cell.setCellValue(branchLineCarInfo.getGuaranRespon());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(21);
                cell.setCellValue(branchLineCarInfo.getGuaranIncome());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(22);
                cell.setCellValue(branchLineCarInfo.getGuaranhouseCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(23);
                cell.setCellValue(branchLineCarInfo.getGuaranidCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(24);
                cell.setCellValue(branchLineCarInfo.getDrilicenseCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(25);
                cell.setCellValue(branchLineCarInfo.getStronginsCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(26);
                cell.setCellValue(branchLineCarInfo.getCommerinsuCopy());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(27);
                cell.setCellValue(branchLineCarInfo.getCertificate());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(28);
                cell.setCellValue(agreeDate);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(29);
                cell.setCellValue(branchLineCarInfo.getRentalAgreement());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(30);
                cell.setCellValue(branchLineCarInfo.getStronginsPrompt());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(31);
                cell.setCellValue(branchLineCarInfo.getTlinsurancePrompt());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(32);
                cell.setCellValue(branchLineCarInfo.getInspectionPrompt());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(33);
                cell.setCellValue(branchLineCarInfo.getChangePrompt());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(34);
                cell.setCellValue(branchLineCarInfo.getStatus());
                cell.setCellStyle(cs[1]);

            }
            flag++;
        }
    }
    POIUtils.exprot(workbook, response, excelName);
}

From source file:com.jshuabo.reportcenter.server.service.automoblie.impl.DefaultBranchLineDrawoutRecordServiceImpl.java

@Override
public void importToExcel(Map<String, Object> exprotMap, HttpServletResponse response,
        HttpServletRequest request, String[] title, String excelName) {
    User user = SecurityContextHolder.getCurrentUser();
    if (StringUtils.isEmpty(exprotMap.get("subStation"))) {
        exprotMap.put("subStation", user.getId());
    }//from  w  w  w . ja  v a 2  s  . com
    Map<String, Object> pageData = drawoutRecordDataMapper.exprotPageData(exprotMap);

    SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows

    int flag = 1; // 
    int rowCount = 150000; // ?sheet 

    long count = (Long) pageData.get("COUNT");
    long pageSize = rowCount; // ??
    long page = 1;
    if (rowCount < count) {
        if (count % pageSize == 0) {
        } else {
            page = count / pageSize + 1;
        } // 
    }
    Sheet sh = workbook.createSheet(excelName + System.currentTimeMillis());
    Row row = sh.createRow((short) 0); // 
    Cell cell = null;
    CellStyle[] cs = POIUtils.cellStyle(workbook);
    POIUtils.createHeard(sh, row, cell, cs, title);

    for (int i = 1; i <= page; i++) {
        long offset = (i - 1) * pageSize;
        long rows = pageSize;
        exprotMap.put("offset", Long.valueOf(offset));
        exprotMap.put("rows", Long.valueOf(rows));
        // ??
        List<BranchLineDrawoutRecord> drawoutRecordDataList = drawoutRecordDataMapper.exprot2Excel(exprotMap);
        if (drawoutRecordDataList.isEmpty())
            continue;
        for (int rownum = 0; rownum < drawoutRecordDataList.size(); rownum++) {

            BranchLineDrawoutRecord bdr = drawoutRecordDataList.get(rownum);

            String data = DateFormatUtils.format(bdr.getDate(), DateFormatUtils.ymd);
            String subStation = bdr.getExtendProp3();
            // ? ?sheet 
            if (flag > pageSize) {
                flag = 1;
                sh = workbook.createSheet(excelName + System.currentTimeMillis());
                row = sh.createRow((short) 0);
                POIUtils.createHeard(sh, row, cell, cs, title);
            }
            row = sh.createRow(flag);
            for (int cellnum = 0; cellnum < title.length; cellnum++) {

                cell = row.createCell(0);
                cell.setCellValue(subStation);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(1);
                cell.setCellValue(data);
                cell.setCellStyle(cs[1]);

                cell = row.createCell(2);
                cell.setCellValue(bdr.getLicenseNo());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(3);
                cell.setCellValue(bdr.getDriver());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(4);
                cell.setCellValue(bdr.getLine());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(5);
                cell.setCellValue(bdr.getGpsNo());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(6);
                cell.setCellValue(bdr.getStartTime());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(7);
                cell.setCellValue(bdr.getStartMileage());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(8);
                cell.setCellValue(bdr.getStopTime());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(9);
                cell.setCellValue(bdr.getStopMileage());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(10);
                cell.setCellValue(bdr.getMileage());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(11);
                String temp = bdr.getExtendProp2();
                cell.setCellValue(StringUtils.isEmpty(temp) ? 0D : Double.valueOf(temp));
                cell.setCellStyle(cs[1]);

                cell = row.createCell(12);
                cell.setCellValue(bdr.getTicketQuantity());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(13);
                cell.setCellValue(bdr.getHallQuantity());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(14);
                cell.setCellValue(bdr.getPieQuantity());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(15);
                cell.setCellValue(bdr.getPcsQuantity());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(16);
                cell.setCellValue(bdr.getFuelPrice());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(17);
                cell.setCellValue(bdr.getFuelCosts());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(18);
                cell.setCellValue(bdr.getDay());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(19);
                cell.setCellValue(bdr.getRentalFee());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(20);
                cell.setCellValue(bdr.getParkingFee());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(21);
                cell.setCellValue(bdr.getAward());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(22);
                cell.setCellValue(bdr.getIsReim());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(23);
                cell.setCellValue(bdr.getNoDelivery());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(24);
                cell.setCellValue(bdr.getRefusal());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(25);
                cell.setCellValue(bdr.getVoteSign());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(26);
                cell.setCellValue(bdr.getRemarks());
                cell.setCellStyle(cs[1]);

                cell = row.createCell(27);
                cell.setCellValue(bdr.getExtendProp1());
                cell.setCellStyle(cs[1]);
            }
            flag++;
        }
    }
    POIUtils.exprot(workbook, response, excelName);

}