Example usage for org.apache.poi.hssf.usermodel HSSFCell setCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. setCellValue

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFCell setCellValue.

Prototype

@SuppressWarnings("fallthrough")
public void setCellValue(boolean value)

Source Link

Document

set a boolean value for the cell

Usage

From source file:com.claudesoft.service.CarTbDetailed.java

@Override
void setDataTableStyle(int index, ResultSet rs, HSSFCell cell) throws SQLException {
    if (index == 0) {
        cell.setCellValue(rs.getString(index));
        cell.setCellStyle(this.boldValueCellStyle);
    } else {//from ww w .ja  v  a  2s.c om
        cell.setCellValue(rs.getString(index));
        cell.setCellStyle(this.valueCellStyle);
    }
}

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

public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
    if (styleMap != null) {
        if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
            newCell.setCellStyle(oldCell.getCellStyle());
        } else {/*from  ww  w.ja  v a2  s.  c  o  m*/
            int stHashCode = oldCell.getCellStyle().hashCode();
            HSSFCellStyle newCellStyle = styleMap.get(stHashCode);
            if (newCellStyle == null) {
                newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
                newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                styleMap.put(stHashCode, newCellStyle);
            }
            newCell.setCellStyle(newCellStyle);
        }
    }
    switch (oldCell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }

}

From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java

License:Open Source License

public void postProcessXls(Object document) {
    logger.trace("postProcessXls start document " + document);
    if (document != null) {
        HSSFWorkbook workBook = (HSSFWorkbook) document;
        HSSFSheet sheet = workBook.getSheetAt(0);

        HSSFRow headerRow = sheet.getRow(0);

        for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
            sheet.setColumnWidth(i, 30 * 265); // width for 40 characters
        }/*  w  ww.j a v a 2s.  c o  m*/

        sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n
                                                      // by 1 to get space
                                                      // for header
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3"));

        HSSFFont headerFont = workBook.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle headerCellStyle = workBook.createCellStyle();
        headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCell headerCell = headerRow.createCell(0);
        headerCell.setCellStyle(headerCellStyle);
        headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date()));

        HSSFCellStyle metaDataCellStyle = workBook.createCellStyle();
        metaDataCellStyle.setFont(headerFont);

        HSSFRow metaDataRow = sheet.getRow(3);
        if (metaDataRow == null) {
            metaDataRow = sheet.createRow(3);
        }
        HSSFCell metaDataKey = metaDataRow.createCell(0);
        metaDataKey.setCellStyle(metaDataCellStyle);
        metaDataKey.setCellValue("CATS Instance");

        HSSFCell metaDataValue = metaDataRow.createCell(1);
        metaDataValue.setCellStyle(metaDataCellStyle);
        metaDataValue.setCellValue(AuthController.getHostAddress());

        HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle();
        datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        datatTableHeaderCellStyle.setFont(headerFont);

        HSSFRow actualDataTableHeaderRow = sheet.getRow(5);
        for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = actualDataTableHeaderRow.getCell(i);
            if (cell != null) {
                String cellValue = cell.getStringCellValue();
                cellValue = cellValue.replace("<br/> ", ""); // replace
                                                             // any line
                                                             // breaks
                cell.setCellValue(cellValue);
                cell.setCellStyle(datatTableHeaderCellStyle);
            }
        }

    }
    logger.trace("postProcessXls end");
}

From source file:com.commander4j.util.JExcel.java

License:Open Source License

public void exportToExcel(String filename, ResultSet rs) {
    try {/*from  w  w  w  .ja va 2  s. c  o m*/

        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int columnType = 0;
        String columnTypeName = "";
        int recordNumber = 0;
        int passwordCol = -1;

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        HSSFCellStyle cellStyle_varchar = workbook.createCellStyle();
        cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle();
        cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle();
        cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_title = workbook.createCellStyle();
        cellStyle_title.setAlignment(HorizontalAlignment.CENTER);

        HSSFCellStyle cellStyle_char = workbook.createCellStyle();
        cellStyle_char.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_date = workbook.createCellStyle();
        cellStyle_date.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle();
        cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_decimal = workbook.createCellStyle();
        cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT);

        HSSFFont font_title = workbook.createFont();
        font_title.setColor((short) 0xc);
        font_title.setBold(true);
        ;
        font_title.setItalic(true);
        font_title.setUnderline(HSSFFont.U_DOUBLE);
        cellStyle_title.setFont(font_title);

        HSSFCell cell;
        HSSFRow row;

        // rs.beforeFirst();

        while (rs.next()) {
            recordNumber++;

            if (recordNumber == 1) {
                row = sheet.createRow((int) 0);
                for (int column = 1; column <= numberOfColumns; column++) {
                    cell = row.createCell((int) (column - 1));
                    String columnName = rsmd.getColumnLabel(column);
                    columnName = columnName.replace("_", " ");
                    columnName = JUtility.capitalize(columnName);
                    cell.setCellStyle(cellStyle_title);
                    cell.setCellValue(columnName);
                    if (columnName.equals("Password")) {
                        passwordCol = column;
                    }
                }
            }

            row = sheet.createRow((int) recordNumber);

            for (int column = 1; column <= numberOfColumns; column++) {

                columnType = rsmd.getColumnType(column);
                columnTypeName = rsmd.getColumnTypeName(column);

                cell = row.createCell((int) (column - 1));

                try {
                    switch (columnType) {
                    case java.sql.Types.NVARCHAR:
                        HSSFRichTextString rtf_nvarchar;
                        if (column == passwordCol) {
                            rtf_nvarchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_nvarchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_nvarchar);
                        cell.setCellValue(rtf_nvarchar);
                        break;
                    case java.sql.Types.VARCHAR:
                        HSSFRichTextString rtf_varchar;
                        if (column == passwordCol) {
                            rtf_varchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_varchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_varchar);
                        cell.setCellValue(rtf_varchar);
                        break;
                    case java.sql.Types.CHAR:
                        HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column));
                        cell.setCellStyle(cellStyle_char);
                        cell.setCellValue(rtf_char);
                        break;
                    case java.sql.Types.DATE:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_date);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.TIMESTAMP:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_timestamp);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.DECIMAL:
                        HSSFRichTextString rtf_decimal = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimal);
                        break;
                    case java.sql.Types.NUMERIC:
                        HSSFRichTextString rtf_decimaln = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimaln);
                        break;
                    case java.sql.Types.BIGINT:
                        HSSFRichTextString rtf_bigint = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_bigint);
                        break;
                    case java.sql.Types.INTEGER:
                        HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_int);
                        break;
                    case java.sql.Types.FLOAT:
                        HSSFRichTextString rtf_float = new HSSFRichTextString(
                                String.valueOf(rs.getFloat(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_float);
                        break;
                    case java.sql.Types.DOUBLE:
                        HSSFRichTextString rtf_double = new HSSFRichTextString(
                                String.valueOf(rs.getDouble(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_double);
                        break;
                    default:
                        cell.setCellValue(new HSSFRichTextString(columnTypeName));
                        break;
                    }
                } catch (Exception ex) {
                    String errormessage = ex.getLocalizedMessage();
                    HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage);
                    cell.setCellStyle(cellStyle_varchar);
                    cell.setCellValue(rtf_exception);
                    break;
                }
            }

            if (recordNumber == 65535) {
                break;
            }
        }

        for (int column = 1; column <= numberOfColumns; column++) {
            sheet.autoSizeColumn((int) (column - 1));
        }

        if (recordNumber > 0) {
            try {
                FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase());
                workbook.write(fileOut);
                fileOut.close();
            } catch (Exception ex) {
                setErrorMessage(ex.getMessage());
            }
        }

        try {
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }
}

From source file:com.dayuan.action.BusFileAction.java

/**???Excel*/
@RequestMapping("/exportExcel")
public ModelAndView exportExcel(HttpServletRequest request, HttpServletResponse response) {
    Map<String, Object> context = getRootMap();
    SysUser user = SessionUtils.getUser(request);
    BusFileModel busFileModel = new BusFileModel();
    if (SuperAdmin.YES.key != user.getSuperAdmin() && user.getExcelAuth() == 0) {
        //busFileModel.setlUserName(user.getNickName());
        busFileModel.setlUId(user.getId().toString());
    }//from  w  ww .j av  a  2  s .  c  o m
    busFileModel.setRows(500);
    try {
        List<BusFiles> list = busFileService.queryByList(busFileModel);
        if (list != null && list.size() > 0) {
            // webbookExcel  
            HSSFWorkbook wb = new HSSFWorkbook();
            // webbooksheet,Excelsheet  
            HSSFSheet sheet = wb.createSheet("?");
            HSSFSheet sheetLoan = wb.createSheet("?");
            // sheet0,??poiExcel?short  
            HSSFRow row = sheet.createRow((int) 0);
            HSSFRow rowLoan = sheetLoan.createRow((int) 0);
            // ?   
            HSSFCellStyle style = wb.createCellStyle();
            // ?
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            /**sheet1 ?*/
            HSSFCell cell = row.createCell((short) 0);
            cell.setCellValue("??");
            cell.setCellStyle(style);
            cell = row.createCell((short) 1);
            cell.setCellValue("??");//busfiles.lUserName
            cell.setCellStyle(style);
            cell = row.createCell((short) 2);
            cell.setCellValue("??");//busLoanInfo.applicationName 
            cell.setCellStyle(style);
            cell = row.createCell((short) 3);
            cell.setCellValue("?"); //busfiles.lStatus
            cell.setCellStyle(style);
            cell = row.createCell((short) 4);
            cell.setCellValue(""); //busfiles.createTime
            cell.setCellStyle(style);
            cell = row.createCell((short) 5);
            cell.setCellValue("??"); //busLoanInfo.channel
            cell.setCellStyle(style);
            cell = row.createCell((short) 6);
            cell.setCellValue("?"); //buslending.loanAmount
            cell.setCellStyle(style);
            cell = row.createCell((short) 7);
            cell.setCellValue("?"); //buslending.openingQuota
            cell.setCellStyle(style);
            cell = row.createCell((short) 8);
            cell.setCellValue(""); //busBiling.creditEndDate
            cell.setCellStyle(style);
            cell = row.createCell((short) 9);
            cell.setCellValue("???"); //busBiling.loanAccount
            cell.setCellStyle(style);
            cell = row.createCell((short) 10);
            cell.setCellValue("?"); //legal.deliveryAddress
            cell.setCellStyle(style);

            /**sheet2 ?*/
            HSSFCell cellLoan = rowLoan.createCell((short) 0);
            cellLoan.setCellValue("??");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 1);
            cellLoan.setCellValue("??");//busLoanInfo.applicationName  
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 2);
            cellLoan.setCellValue("?"); //busLoanInfo.urgentCont 
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 3);
            cellLoan.setCellValue("");//busLoanInfo.relationship  
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 4);
            cellLoan.setCellValue("?");//busLoanInfo.urgentContPhone  
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 5);
            cellLoan.setCellValue("?");//busLoanInfo.urgentContAddress  
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 6);
            cellLoan.setCellValue("??"); //biling.loanCardNumber 
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 7);
            cellLoan.setCellValue("1");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 8);
            cellLoan.setCellValue("?1");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 9);
            cellLoan.setCellValue("???1");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 10);
            cellLoan.setCellValue("?1");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 11);
            cellLoan.setCellValue("2");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 12);
            cellLoan.setCellValue("?2");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 13);
            cellLoan.setCellValue("???2");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 14);
            cellLoan.setCellValue("?2");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 15);
            cellLoan.setCellValue("");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 16);
            cellLoan.setCellValue("??");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 17);
            cellLoan.setCellValue("????");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 18);
            cellLoan.setCellValue("??");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 19);
            cellLoan.setCellValue("??");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 20);
            cellLoan.setCellValue("?");
            cellLoan.setCellStyle(style);

            for (int i = 0; i < list.size(); i++) {
                BusFiles busFiles = list.get(i);
                Integer lId = busFiles.getId();
                BusLoanInfo busLoanInfo = busLoanInfoService.queryByLId(lId);
                BusLending busLending = busLendingService.queryByBId(lId);
                BusBiling busBiling = busBilingService.queryByBId(lId);
                BusLoanInfoLegal legal = busLoanInfoLegalService.getBusLoanInfoLegal(lId);
                List<BusLoanInfoShop> shopList = busLoanInfoShopService.queryListByBId(lId);

                /**rowsheet1*/
                row = sheet.createRow((int) 1 + i);
                row.createCell((short) 0).setCellValue(i + 1);
                row.createCell((short) 1).setCellValue(busFiles.getlUserName());
                row.createCell((short) 3).setCellValue(busFiles.getlStatus());
                row.createCell((short) 4).setCellValue(
                        DateUtil.getFormattedDateUtil((Date) busFiles.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));

                /**rowLoansheet2*/
                rowLoan = sheetLoan.createRow((int) 1 + i);
                rowLoan.createCell((short) 0).setCellValue(i + 1);

                if (busLoanInfo != null) {
                    row.createCell((short) 2).setCellValue(busLoanInfo.getApplicationName());
                    row.createCell((short) 5).setCellValue(busLoanInfo.getChannel());

                    rowLoan.createCell((short) 1).setCellValue(busLoanInfo.getApplicationName());
                    rowLoan.createCell((short) 2).setCellValue(busLoanInfo.getUrgentCont());
                    rowLoan.createCell((short) 3).setCellValue(busLoanInfo.getRelationship());
                    rowLoan.createCell((short) 4).setCellValue(busLoanInfo.getUrgentContPhone());
                    rowLoan.createCell((short) 5).setCellValue(busLoanInfo.getUrgentContAddress());

                } else {
                    row.createCell((short) 2).setCellValue("");
                    row.createCell((short) 5).setCellValue("");

                    rowLoan.createCell((short) 1).setCellValue("");
                    rowLoan.createCell((short) 2).setCellValue("");
                    rowLoan.createCell((short) 3).setCellValue("");
                    rowLoan.createCell((short) 4).setCellValue("");
                    rowLoan.createCell((short) 5).setCellValue("");
                }
                if (busLending != null) {
                    row.createCell((short) 6).setCellValue(busLending.getLoanAmount());
                    row.createCell((short) 7).setCellValue(busLending.getOpeningQuota());
                } else {
                    row.createCell((short) 6).setCellValue("");
                    row.createCell((short) 7).setCellValue("");
                }
                if (busBiling != null) {
                    row.createCell((short) 8).setCellValue(busBiling.getCreditEndDate());
                    row.createCell((short) 9).setCellValue(busBiling.getLoanAccount());

                    rowLoan.createCell((short) 6).setCellValue(busBiling.getLoanCardNumber());//??
                    rowLoan.createCell((short) 15).setCellValue(DateUtil
                            .getFormattedDateUtil((Date) busBiling.getCheckDate(), "yyyy-MM-dd HH:mm:ss"));//
                    rowLoan.createCell((short) 16).setCellValue(busBiling.getCreditorIfNormal());//??
                    rowLoan.createCell((short) 17).setCellValue(busBiling.getGuarantorIfNormal());//????
                    rowLoan.createCell((short) 18).setCellValue(busBiling.getCloudLoanIfWarning());//??
                    rowLoan.createCell((short) 19).setCellValue(busBiling.getShopOperation());//??
                    rowLoan.createCell((short) 20).setCellValue(busBiling.getOtherNeedToExplained());//?
                } else {
                    row.createCell((short) 8).setCellValue("");
                    row.createCell((short) 9).setCellValue("");

                    rowLoan.createCell((short) 6).setCellValue("");//??
                    rowLoan.createCell((short) 15).setCellValue("");//
                    rowLoan.createCell((short) 16).setCellValue("");//??
                    rowLoan.createCell((short) 17).setCellValue("");//????
                    rowLoan.createCell((short) 18).setCellValue("");//??
                    rowLoan.createCell((short) 19).setCellValue("");//??
                    rowLoan.createCell((short) 20).setCellValue("");//?

                }
                if (legal != null) {
                    row.createCell((short) 10).setCellValue(legal.getDeliveryAddress());
                } else {
                    row.createCell((short) 10).setCellValue("");
                }

                if (shopList != null && shopList.size() > 0) {
                    if (shopList.size() == 1) {
                        BusLoanInfoShop shop = shopList.get(0);
                        rowLoan.createCell((short) 7).setCellValue(shop.getShopName());//
                        rowLoan.createCell((short) 8).setCellValue(shop.getPlatformName());//?
                        rowLoan.createCell((short) 9).setCellValue(shop.getSubAccount());//???
                        rowLoan.createCell((short) 10).setCellValue(shop.getSbuPassword());//?
                        rowLoan.createCell((short) 11).setCellValue("");//
                        rowLoan.createCell((short) 12).setCellValue("");//?
                        rowLoan.createCell((short) 13).setCellValue("");//???
                        rowLoan.createCell((short) 14).setCellValue("");//?
                    } else if (shopList.size() == 2) {
                        BusLoanInfoShop shop = shopList.get(0);
                        rowLoan.createCell((short) 7).setCellValue(shop.getShopName());//
                        rowLoan.createCell((short) 8).setCellValue(shop.getPlatformName());//?
                        rowLoan.createCell((short) 9).setCellValue(shop.getSubAccount());//???
                        rowLoan.createCell((short) 10).setCellValue(shop.getSbuPassword());//?
                        BusLoanInfoShop shop1 = shopList.get(1);
                        rowLoan.createCell((short) 11).setCellValue(shop1.getShopName());//
                        rowLoan.createCell((short) 12).setCellValue(shop1.getPlatformName());//?
                        rowLoan.createCell((short) 13).setCellValue(shop1.getSubAccount());//???
                        rowLoan.createCell((short) 14).setCellValue(shop1.getSbuPassword());//?
                    }

                } else {
                    rowLoan.createCell((short) 7).setCellValue("");
                    rowLoan.createCell((short) 8).setCellValue("");
                    rowLoan.createCell((short) 9).setCellValue("");
                    rowLoan.createCell((short) 10).setCellValue("");
                    rowLoan.createCell((short) 11).setCellValue("");
                    rowLoan.createCell((short) 12).setCellValue("");
                    rowLoan.createCell((short) 13).setCellValue("");
                    rowLoan.createCell((short) 14).setCellValue("");
                }

            }
            String savePath = request.getSession().getServletContext().getRealPath(
                    File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,??
            File savePathFile = new File(savePath);
            if (!savePathFile.exists()) {
                savePathFile.mkdirs();
            }
            savePath = savePath + File.separator + UUID.randomUUID();//?
            File fileSavePath = new File(savePath);
            /**??*/
            if (fileSavePath.exists()) {
                if (fileSavePath.isDirectory()) {
                    File[] files = fileSavePath.listFiles();
                    for (File file : files) {
                        file.delete();
                    }
                    fileSavePath.delete();
                } else {
                    fileSavePath.delete();
                }
                fileSavePath.mkdirs();
            } else {
                fileSavePath.mkdirs();
            }
            String excel = "????" + DateUtil.getNowLongTime() + ".xls";//eccel??
            BufferedOutputStream fout = new BufferedOutputStream(
                    new FileOutputStream(savePath + File.separator + excel)); //excel
            wb.write(fout); //excel?
            fout.flush();
            fout.close();
            //MIME
            response.setContentType(request.getSession().getServletContext().getMimeType(excel));
            //Content-Disposition
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(excel, "UTF-8"));
            BufferedInputStream in = new BufferedInputStream(
                    new FileInputStream(savePath + File.separator + excel));//,io?
            OutputStream out = new BufferedOutputStream(response.getOutputStream());
            byte buffer[] = new byte[1024];
            int len = 0;
            while ((len = in.read(buffer)) > 0) {
                out.write(buffer, 0, len);//?response?
            }
            in.close();
            out.flush();
            out.close();
            /***/
            File file = new File(savePath + File.separator + excel);
            if (file != null) {
                if (file.exists()) {
                    file.delete();//
                }
                file = null;
            }
            /***/
            if (fileSavePath != null) {
                if (fileSavePath.exists()) {
                    fileSavePath.delete();
                }
                fileSavePath = null;
            }
            if (savePathFile != null) {
                savePathFile = null;
            }
            log.info("Excel?");
        }
    } catch (Exception e) {
        //e.printStackTrace();
        log.error("exportExcel" + e.getMessage());
        context.put("message", "??");
        return forword("message/message", context);
    }
    return null;
}

From source file:com.dayuan.action.BusinessLoanAction.java

/**
 * ?excel//w ww  .j av  a  2 s  .co m
 * */
@RequestMapping("/exportAllExcel")
public void exportAllExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
    SysUser user = SessionUtils.getUser(request);
    BusLoanInfoModel busLoanInfoModel = new BusLoanInfoModel();
    busLoanInfoModel.setuId(user.getId().toString());
    busLoanInfoModel.setuName(user.getNickName());
    List<BusLoanInfo> list = busLoanInfoService.queryList(busLoanInfoModel);
    if (list != null && list.size() > 0) {
        // webbookExcel  
        HSSFWorkbook wb = new HSSFWorkbook();
        // webbooksheet,Excelsheet  
        HSSFSheet sheet = wb.createSheet("???");

        // sheet0,??poiExcel?short  
        HSSFRow row = sheet.createRow((int) 0);
        // ?   
        HSSFCellStyle style = wb.createCellStyle();
        // ?
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("??");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("??"); //applicationName 
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("?");//urgentCont 
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue(""); // relationship
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("?"); //legalPhone
        cell.setCellStyle(style);
        cell = row.createCell((short) 5);
        cell.setCellValue("?"); //houseAddress
        cell.setCellStyle(style);
        cell = row.createCell((short) 6);
        cell.setCellValue("?"); //companyName
        cell.setCellStyle(style);
        cell = row.createCell((short) 7);
        cell.setCellValue("?");//  ?platformName
        cell.setCellStyle(style);
        cell = row.createCell((short) 8);
        cell.setCellValue(""); //?shopName
        cell.setCellStyle(style);
        cell = row.createCell((short) 9);
        cell.setCellValue("???"); //?,subAccount
        cell.setCellStyle(style);
        cell = row.createCell((short) 10);
        cell.setCellValue("?"); //?,sbuPassword
        cell.setCellStyle(style);

        for (int i = 0; i < list.size(); i++) {
            BusLoanInfo busLoanInfo = list.get(i);
            Integer bid = busLoanInfo.getId();
            BusLoanInfoLegal busLoanInfoLegal = this.busLoanInfoLegalService.getBusLoanInfoLegal(bid);
            BusLoanInfoShop busLoanInfoShop = this.busLoanInfoShopService.getBusLoanInfoShop(bid);

            row = sheet.createRow((int) 1 + i);
            row.createCell((short) 0).setCellValue(i + 1);
            row.createCell((short) 1).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getApplicationName()));
            row.createCell((short) 2).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getUrgentCont()));
            row.createCell((short) 3).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getRelationship()));
            if (busLoanInfoLegal != null) {
                row.createCell((short) 4)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getLegalPhone()));
                row.createCell((short) 5)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getHouseAddress()));
                row.createCell((short) 6)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getCompanyName()));
            } else {
                row.createCell((short) 4).setCellValue("");
                row.createCell((short) 5).setCellValue("");
                row.createCell((short) 6).setCellValue("");
            }
            if (busLoanInfoShop != null) {
                row.createCell((short) 7)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getPlatformName()));
                row.createCell((short) 8).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getShopName()));
                row.createCell((short) 9)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSubAccount()));
                row.createCell((short) 10)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSbuPassword()));
            } else {
                row.createCell((short) 7).setCellValue("");
                row.createCell((short) 8).setCellValue("");
                row.createCell((short) 9).setCellValue("");
                row.createCell((short) 10).setCellValue("");
            }
        }
        String savePath = request.getSession().getServletContext().getRealPath(
                File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,??
        savePath = savePath + File.separator + UUID.randomUUID();//?
        File fileSavePath = new File(savePath);
        /**??*/
        if (fileSavePath.exists()) {
            if (fileSavePath.isDirectory()) {
                File[] files = fileSavePath.listFiles();
                for (File file : files) {
                    file.delete();
                }
                fileSavePath.delete();
            } else {
                fileSavePath.delete();
            }
            fileSavePath.mkdirs();
        } else {
            fileSavePath.mkdirs();
        }
        String excel = "????" + DateUtil.getNowPlusTimeMill() + ".xls";//eccel??
        BufferedOutputStream fout = new BufferedOutputStream(
                new FileOutputStream(savePath + File.separator + excel)); //
        wb.write(fout); //excel?
        fout.flush();
        fout.close();
        //MIME
        response.setContentType(request.getSession().getServletContext().getMimeType(excel));
        //Content-Disposition
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excel, "UTF-8"));

        BufferedInputStream in = new BufferedInputStream(
                new FileInputStream(savePath + File.separator + excel));//,io?
        OutputStream out = new BufferedOutputStream(response.getOutputStream());
        byte buffer[] = new byte[1024];
        int len = 0;
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);//?response?
        }
        in.close();
        out.flush();
        out.close();
        /***/
        File file = new File(savePath + File.separator + excel);
        if (file != null) {
            if (file.exists()) {
                file.delete();//
            }
            file = null;
        }
        /***/
        if (fileSavePath != null) {
            if (fileSavePath.exists()) {
                fileSavePath.delete();
            }
            fileSavePath = null;
        }
        log.info("excel?");
    }
}

From source file:com.dayuan.action.BusinessLoanAction.java

/**
 * excel// w ww. ja  va2  s.c  om
 * 
 * */
@RequestMapping("/exportExcel")
public void exportExcel(Integer id, HttpServletRequest request, HttpServletResponse response) throws Exception {
    if (id == null || id.equals("")) {
        log.error("?");
        return;
    }
    BusLoanInfo busLoanInfo = this.busLoanInfoService.queryById(id);
    if (busLoanInfo == null) {
        log.error("busLoanInfo?");
        return;
    }
    Integer bid = busLoanInfo.getId();
    if (bid == null) {
        log.error("busLoanInfo?");
        return;
    }
    BusLoanInfoLegal busLoanInfoLegal = this.busLoanInfoLegalService.getBusLoanInfoLegal(bid);
    BusLoanInfoShop busLoanInfoShop = this.busLoanInfoShopService.getBusLoanInfoShop(bid);

    // webbookExcel  
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet  
    HSSFSheet sheet = wb.createSheet("???");

    // sheet0,??poiExcel?short  
    HSSFRow row = sheet.createRow((int) 0);
    // ?   
    HSSFCellStyle style = wb.createCellStyle();
    // ?
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell((short) 1);
    cell.setCellValue("??"); //applicationName 
    cell.setCellStyle(style);
    cell = row.createCell((short) 2);
    cell.setCellValue("?");//urgentCont 
    cell.setCellStyle(style);
    cell = row.createCell((short) 3);
    cell.setCellValue(""); // relationship
    cell.setCellStyle(style);
    cell = row.createCell((short) 4);
    cell.setCellValue("?"); //legalPhone
    cell.setCellStyle(style);
    cell = row.createCell((short) 5);
    cell.setCellValue("?"); //houseAddress
    cell.setCellStyle(style);
    cell = row.createCell((short) 6);
    cell.setCellValue("?"); //companyName
    cell.setCellStyle(style);
    cell = row.createCell((short) 7);
    cell.setCellValue("?");//  ?platformName
    cell.setCellStyle(style);
    cell = row.createCell((short) 8);
    cell.setCellValue(""); //?shopName
    cell.setCellStyle(style);
    cell = row.createCell((short) 9);
    cell.setCellValue("???"); //?,subAccount
    cell.setCellStyle(style);
    cell = row.createCell((short) 10);
    cell.setCellValue("?"); //?,sbuPassword
    cell.setCellStyle(style);

    // ? ??  
    try {

        row = sheet.createRow((int) 0 + 1);
        row.createCell((short) 0).setCellValue(1);
        row.createCell((short) 1).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getApplicationName()));
        row.createCell((short) 2).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getUrgentCont()));
        row.createCell((short) 3).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getRelationship()));
        if (busLoanInfoLegal != null) {
            row.createCell((short) 4).setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getLegalPhone()));
            row.createCell((short) 5)
                    .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getHouseAddress()));
            row.createCell((short) 6).setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getCompanyName()));
        } else {
            row.createCell((short) 4).setCellValue("");
            row.createCell((short) 5).setCellValue("");
            row.createCell((short) 6).setCellValue("");
        }
        if (busLoanInfoShop != null) {
            row.createCell((short) 7).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getPlatformName()));
            row.createCell((short) 8).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getShopName()));
            row.createCell((short) 9).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSubAccount()));
            row.createCell((short) 10).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSbuPassword()));
        } else {
            row.createCell((short) 7).setCellValue("");
            row.createCell((short) 8).setCellValue("");
            row.createCell((short) 9).setCellValue("");
            row.createCell((short) 10).setCellValue("");
        }
        // Student stu = (Student) list.get(i);  
        // ?  
        //  cell = row.createCell((short) 3);  
        //   cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getBirth()));  
        //             row.createCell((short) 10).setCellValue(busLoanInfoShop.getSbuPassword());?

        String savePath = request.getSession().getServletContext().getRealPath(
                File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,??
        savePath = savePath + File.separator + UUID.randomUUID();//?
        File fileSavePath = new File(savePath);
        /**??*/
        if (fileSavePath.exists()) {
            if (fileSavePath.isDirectory()) {
                File[] files = fileSavePath.listFiles();
                for (File file : files) {
                    file.delete();
                }
                fileSavePath.delete();
            } else {
                fileSavePath.delete();
            }
            fileSavePath.mkdirs();
        } else {
            fileSavePath.mkdirs();
        }
        String excel = "????" + DateUtil.getNowPlusTimeMill() + ".xls";//eccel??
        BufferedOutputStream fout = new BufferedOutputStream(
                new FileOutputStream(savePath + File.separator + excel)); //
        wb.write(fout); //excel?
        fout.flush();
        fout.close();
        //MIME
        response.setContentType(request.getSession().getServletContext().getMimeType(excel));
        //Content-Disposition
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excel, "UTF-8"));
        //,io?
        BufferedInputStream in = new BufferedInputStream(
                new FileInputStream(savePath + File.separator + excel));
        OutputStream out = new BufferedOutputStream(response.getOutputStream());
        byte buffer[] = new byte[1024];
        int len = 0;
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);//?response?
        }
        in.close();
        out.flush();
        out.close();
        /***/
        File file = new File(savePath + File.separator + excel);
        if (file != null) {
            if (file.exists()) {
                file.delete();//
            }
            file = null;
        }
        /***/
        if (fileSavePath != null) {
            if (fileSavePath.exists()) {
                fileSavePath.delete();
            }
            fileSavePath = null;
        }
        log.info("???excel?");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.demo.common.extreme.view.XlsView.java

License:Apache License

private void createHeader(TableModel model) {
    rownum = 0;//from   w  ww .  ja  va 2 s. c  om
    cellnum = 0;
    HSSFRow row = sheet.createRow(rownum);

    List columns = model.getColumnHandler().getHeaderColumns();
    for (Iterator iter = columns.iterator(); iter.hasNext();) {
        Column column = (Column) iter.next();
        String title = column.getCellDisplay();
        HSSFCell hssfCell = row.createCell(cellnum);

        setCellEncoding(hssfCell);

        hssfCell.setCellStyle((HSSFCellStyle) styles.get("titleStyle"));
        hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        hssfCell.setCellValue(title);
        int valWidth = (title + "").length() * WIDTH_MULT;
        sheet.setColumnWidth(hssfCell.getCellNum(), (short) valWidth);

        cellnum++;
    }
}

From source file:com.demo.common.extreme.view.XlsView.java

License:Apache License

private void fixWidthAndPopulate(HSSFCell cell, double numeric, String value) {
    int valWidth = 0;

    if (numeric != NON_NUMERIC) {
        cell.setCellValue(numeric);
        valWidth = (cell.getNumericCellValue() + "$,.").length() * WIDTH_MULT;
    } else {//  ww w  .ja va2 s  .c  om
        cell.setCellValue(value);
        valWidth = (cell.getStringCellValue() + "").length() * WIDTH_MULT;

        if (valWidth < (WIDTH_MULT * MIN_CHARS)) {
            valWidth = WIDTH_MULT * MIN_CHARS;
        }
    }

    if (valWidth > sheet.getColumnWidth(cell.getCellNum())) {
        sheet.setColumnWidth(cell.getCellNum(), (short) valWidth);
    }
}

From source file:com.demo.common.extreme.view.XlsView.java

License:Apache License

public void totals(TableModel model) {
    Column firstCalcColumn = model.getColumnHandler().getFirstCalcColumn();
    if (firstCalcColumn != null) {
        int rows = firstCalcColumn.getCalc().length;
        for (int i = 0; i < rows; i++) {
            rownum++;// w w w .  j av  a2  s  . c o m
            HSSFRow row = sheet.createRow(rownum);
            cellnum = 0;
            for (Iterator iter = model.getColumnHandler().getColumns().iterator(); iter.hasNext();) {
                Column column = (Column) iter.next();
                if (column.isFirstColumn()) {
                    String calcTitle = CalcUtils.getFirstCalcColumnTitleByPosition(model, i);
                    HSSFCell cell = row.createCell(cellnum);
                    setCellEncoding(cell);
                    if (column.isEscapeAutoFormat()) {
                        writeToCellAsText(cell, calcTitle, "_Totals");
                    } else {
                        writeToCellFormatted(cell, calcTitle, "_Totals");
                    }
                    cellnum++;
                    continue;
                }

                if (column.isCalculated()) {
                    CalcResult calcResult = CalcUtils.getCalcResultsByPosition(model, column, i);
                    Number value = calcResult.getValue();
                    HSSFCell cell = row.createCell(cellnum);
                    setCellEncoding(cell);
                    if (value != null)
                        if (column.isEscapeAutoFormat()) {
                            writeToCellAsText(cell, value.toString(), "_Totals");
                        } else {
                            writeToCellFormatted(cell,
                                    ExtremeUtils.formatNumber(column.getFormat(), value, model.getLocale()),
                                    "_Totals");
                        }
                    else {
                        cell.setCellStyle((HSSFCellStyle) styles.get("naStyle_Totals"));
                        cell.setCellValue("n/a");
                    }
                    cellnum++;
                } else {
                    HSSFCell cell = row.createCell(cellnum);
                    setCellEncoding(cell);
                    writeToCellFormatted(cell, "", "_Totals");
                    cellnum++;
                }
            }
        }
    }

}