Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. createCell

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.luna.showcase.excel.service.ExcelDataService.java

License:Apache License

/**
 * ???/*from w ww. ja v  a2  s  .  c o m*/
 * excel 2007 ?sheet1048576
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2007(final User user, final String contextRootPath, final Searchable searchable) {

    int rowAccessWindowSize = 1000; //???
    int perSheetRows = 100000; //?sheet 10w?
    int totalRows = 0; //
    Long maxId = 0L;//??id 

    String fileName = generateFilename(user, contextRootPath, "xlsx");
    File file = new File(fileName);
    BufferedOutputStream out = null;
    SXSSFWorkbook wb = null;
    try {
        long beginTime = System.currentTimeMillis();

        wb = new SXSSFWorkbook(rowAccessWindowSize);
        wb.setCompressTempFiles(true);//?gzip

        while (true) {

            Sheet sheet = wb.createSheet();
            Row headerRow = sheet.createRow(0);
            Cell idHeaderCell = headerRow.createCell(0);
            idHeaderCell.setCellValue("?");
            Cell contentHeaderCell = headerRow.createCell(1);
            contentHeaderCell.setCellValue("");

            totalRows = 1;

            Page<ExcelData> page = null;

            do {
                searchable.setPage(0, pageSize);
                //
                if (!searchable.containsSearchKey("id_in")) {
                    searchable.addSearchFilter("id", SearchOperator.gt, maxId);
                }
                page = findAll(searchable);

                for (ExcelData data : page.getContent()) {
                    Row row = sheet.createRow(totalRows);
                    Cell idCell = row.createCell(0);
                    idCell.setCellValue(data.getId());
                    Cell contentCell = row.createCell(1);
                    contentCell.setCellValue(data.getContent());
                    maxId = Math.max(maxId, data.getId());
                    totalRows++;
                }
                //clear entity manager
                RepositoryHelper.clear();
            } while (page.hasNextPage() && totalRows <= perSheetRows);

            if (!page.hasNextPage()) {
                break;
            }
        }
        out = new BufferedOutputStream(new FileOutputStream(file));
        wb.write(out);

        IOUtils.closeQuietly(out);

        if (needCompress(file)) {
            fileName = compressAndDeleteOriginal(fileName);
        }

        long endTime = System.currentTimeMillis();

        Map<String, Object> context = Maps.newHashMap();
        context.put("seconds", (endTime - beginTime) / 1000);
        context.put("url", fileName.replace(contextRootPath, ""));
        notificationApi.notify(user.getId(), "excelExportSuccess", context);
    } catch (Exception e) {
        IOUtils.closeQuietly(out);
        log.error("excel export error", e);
        Map<String, Object> context = Maps.newHashMap();
        context.put("error", e.getMessage());
        notificationApi.notify(user.getId(), "excelExportError", context);
    } finally {
        // ?
        wb.dispose();
    }
}

From source file:com.lushapp.common.excel.ExportExcel.java

License:Apache License

private void exportExcelInUserModel(String title, Class<T> pojoClass, Collection<T> dataSet, OutputStream out) {
    try {/*from  w w  w . ja v a  2 s. c o m*/
        // ??
        if (dataSet == null || dataSet.size() == 0) {
            throw new Exception("??");
        }
        if (title == null || out == null || pojoClass == null) {
            throw new Exception("???");
        }
        // 
        Workbook workbook = new HSSFWorkbook();
        // ?
        Sheet sheet = workbook.createSheet(title);

        // 
        List<String> exportFieldTitle = new ArrayList<String>();
        List<Integer> exportFieldWidth = new ArrayList<Integer>();
        // ???get
        List<Method> methodObj = new ArrayList<Method>();
        Map<String, Method> convertMethod = new HashMap<String, Method>();
        // 
        Field fileds[] = pojoClass.getDeclaredFields();
        // ??filed
        for (int i = 0; i < fileds.length; i++) {
            Field field = fileds[i];
            Excel excel = field.getAnnotation(Excel.class);
            // annottion
            if (excel != null) {
                // 
                exportFieldTitle.add(excel.exportName());
                // 
                exportFieldWidth.add(excel.exportFieldWidth());
                // ?
                String fieldname = field.getName();
                // System.out.println(i+""+excel.exportName()+" "+excel.exportFieldWidth());
                StringBuffer getMethodName = new StringBuffer("get");
                getMethodName.append(fieldname.substring(0, 1).toUpperCase());
                getMethodName.append(fieldname.substring(1));

                Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[] {});

                methodObj.add(getMethod);
                if (excel.exportConvert() == true) {
                    StringBuffer getConvertMethodName = new StringBuffer("get");
                    getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
                    getConvertMethodName.append(fieldname.substring(1));
                    getConvertMethodName.append("Convert");
                    Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(),
                            new Class[] {});
                    convertMethod.put(getMethodName.toString(), getConvertMethod);
                }
            }
        }
        int index = 0;
        // 
        Row row = sheet.createRow(index);
        for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
            Cell cell = row.createCell(i);
            // cell.setCellStyle(style);
            RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
            cell.setCellValue(text);
        }

        // ?
        for (int i = 0; i < exportFieldWidth.size(); i++) {
            // 256=65280/255
            sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
        }
        Iterator its = dataSet.iterator();
        // ??
        while (its.hasNext()) {
            // 
            index++;
            row = sheet.createRow(index);
            Object t = its.next();
            for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {
                Cell cell = row.createCell(k);
                Method getMethod = methodObj.get(k);
                Object value = null;
                if (convertMethod.containsKey(getMethod.getName())) {
                    Method cm = convertMethod.get(getMethod.getName());
                    value = cm.invoke(t, new Object[] {});
                } else {
                    value = getMethod.invoke(t, new Object[] {});
                }
                cell.setCellValue(value == null ? "" : value.toString());
            }
        }

        workbook.write(out);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.lushapp.common.excel.ExportExcel.java

License:Apache License

private HSSFWorkbook exportExcelInUserModel2File(String title, Class<T> pojoClass, Collection<T> dataSet) {
    // //  w  w  w.ja va  2  s . c o m
    HSSFWorkbook workbook = null;
    try {
        // 
        workbook = new HSSFWorkbook();
        // ?
        Sheet sheet = workbook.createSheet(title);

        // 
        List<String> exportFieldTitle = new ArrayList<String>();
        List<Integer> exportFieldWidth = new ArrayList<Integer>();
        // ???get
        List<Method> methodObj = new ArrayList<Method>();
        Map<String, Method> convertMethod = new HashMap<String, Method>();
        Class superClazz = null;
        Field fileds[] = new Field[0];
        boolean flag = true;
        while (flag) {
            if (superClazz != null) {
                superClazz = superClazz.getSuperclass();
            } else {
                superClazz = pojoClass.getSuperclass();
            }
            if (superClazz.isInstance(Object.class)) {
                flag = false;
            } else {
                Field[] sf = superClazz.getDeclaredFields();
                if (sf != null && sf.length > 0) {
                    for (int m = 0; m < sf.length; m++) {
                        fileds = ArrayUtils.addAll(fileds, sf[m]);
                    }
                }
            }

        }
        // 
        Field cfileds[] = pojoClass.getDeclaredFields();
        if (cfileds != null && cfileds.length > 0) {
            for (int n = 0; n < cfileds.length; n++) {
                fileds = ArrayUtils.addAll(fileds, cfileds[n]);
            }
        }
        // ??filed
        for (int i = 0; i < fileds.length; i++) {
            Field field = fileds[i];
            Excel excel = field.getAnnotation(Excel.class);
            // annottion
            if (excel != null) {
                // 
                exportFieldTitle.add(excel.exportName());
                // 
                exportFieldWidth.add(excel.exportFieldWidth());
                // ?
                String fieldname = field.getName();
                // System.out.println(i+""+excel.exportName()+" "+excel.exportFieldWidth());
                StringBuffer getMethodName = new StringBuffer("get");
                getMethodName.append(fieldname.substring(0, 1).toUpperCase());
                getMethodName.append(fieldname.substring(1));

                Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[] {});

                methodObj.add(getMethod);
                if (excel.exportConvert() == true) {
                    //----------------------------------------------------------------
                    //update-begin--Author:Quainty  Date:20130524 for[8]excel
                    // get/setXxxxConvert??? ?Entity?
                    StringBuffer getConvertMethodName = new StringBuffer("convertGet");
                    getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
                    getConvertMethodName.append(fieldname.substring(1));
                    //getConvertMethodName.append("Convert");
                    //update-end--Author:Quainty  Date:20130524 for[8]excel
                    //----------------------------------------------------------------
                    // System.out.println("convert: "+getConvertMethodName.toString());
                    Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(),
                            new Class[] {});
                    convertMethod.put(getMethodName.toString(), getConvertMethod);
                }
            }
        }
        int index = 0;
        // 
        Row row = sheet.createRow(index);
        row.setHeight((short) 450);
        CellStyle titleStyle = getTitleStyle(workbook);
        for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
            Cell cell = row.createCell(i);
            // cell.setCellStyle(style);
            RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
            cell.setCellValue(text);
            cell.setCellStyle(titleStyle);
        }

        // ?
        for (int i = 0; i < exportFieldWidth.size(); i++) {
            // 256=65280/255
            sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
        }
        Iterator its = dataSet.iterator();
        // ??
        while (its.hasNext()) {
            // 
            index++;
            row = sheet.createRow(index);
            row.setHeight((short) 350);
            Object t = its.next();
            for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {
                Cell cell = row.createCell(k);
                Method getMethod = methodObj.get(k);
                Object value = null;
                if (convertMethod.containsKey(getMethod.getName())) {
                    Method cm = convertMethod.get(getMethod.getName());
                    value = cm.invoke(t, new Object[] {});
                } else {
                    value = getMethod.invoke(t, new Object[] {});
                }
                cell.setCellValue(value == null ? "" : value.toString());

                if (index % 2 == 0)
                    cell.setCellStyle(getTwoStyle(workbook));
                else
                    cell.setCellStyle(getOneStyle(workbook));
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return workbook;
}

From source file:com.lw.common.utils.ExcelUtil.java

public <T> Workbook batchImportFailList(String modelPath, List<T> objectList, Class<T> modelClass,
        Map<String, String> columnMap) throws Exception {
    //??excel/*from  ww  w  . j  a  v  a2 s. co  m*/
    File localfile = new File("D:\\lw7068\\Desktop\\ (4)\\??.xlsx");
    InputStream in = new FileInputStream(localfile);
    // ?
    Workbook book = new XSSFWorkbook(in);
    // ?sheet
    Sheet sheet = book.getSheetAt(0);
    //
    Row titleRow = sheet.getRow(0);
    //???index
    Map<String, Integer> rowIndex = getTitleRowIndex(titleRow);
    //???method
    Map<Integer, Method> methodsIndex = getRowIndexAndGetMethod(titleRow, rowIndex, modelClass, columnMap);
    for (int i = 0; i < objectList.size(); i++) {
        //excelindex   ??? 
        Map<Integer, String> dataMap = getRowIndexAndData(titleRow, objectList.get(i), methodsIndex);
        //
        Row row = sheet.createRow(i + 1);
        //
        for (Map.Entry<Integer, String> entry : dataMap.entrySet()) {
            Cell cell = row.createCell(entry.getKey());
            cell.setCellValue(entry.getValue());
        }
    }
    return book;
}

From source file:com.lw.common.utils.ExcelUtil.java

/**
 * excel//ww w .j a  v a 2 s  . c om
 * @param list ?
 * @param keys listmapkey?
 * @param columnNames excel??
 * */
public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {
    // excel
    Workbook wb = new HSSFWorkbook();
    // sheet??
    Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
    // ???n?
    for (int i = 0; i < keys.length; i++) {
        sheet.setColumnWidth((short) i, (short) (35.7 * 150));
    }

    // 
    Row row = sheet.createRow((short) 0);

    // ???
    CellStyle cs = wb.createCellStyle();
    CellStyle cs2 = wb.createCellStyle();

    // ?
    Font f = wb.createFont();
    Font f2 = wb.createFont();

    // ????
    f.setFontHeightInPoints((short) 10);
    f.setColor(IndexedColors.BLACK.getIndex());
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // ??
    f2.setFontHeightInPoints((short) 10);
    f2.setColor(IndexedColors.BLACK.getIndex());

    //        Font f3=wb.createFont();
    //        f3.setFontHeightInPoints((short) 10);
    //        f3.setColor(IndexedColors.RED.getIndex());

    // ?????
    cs.setFont(f);
    cs.setBorderLeft(CellStyle.BORDER_THIN);
    cs.setBorderRight(CellStyle.BORDER_THIN);
    cs.setBorderTop(CellStyle.BORDER_THIN);
    cs.setBorderBottom(CellStyle.BORDER_THIN);
    cs.setAlignment(CellStyle.ALIGN_CENTER);

    // ???
    cs2.setFont(f2);
    cs2.setBorderLeft(CellStyle.BORDER_THIN);
    cs2.setBorderRight(CellStyle.BORDER_THIN);
    cs2.setBorderTop(CellStyle.BORDER_THIN);
    cs2.setBorderBottom(CellStyle.BORDER_THIN);
    cs2.setAlignment(CellStyle.ALIGN_CENTER);
    //??
    for (int i = 0; i < columnNames.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(columnNames[i]);
        cell.setCellStyle(cs);
    }
    //??
    for (short i = 1; i < list.size(); i++) {
        // Row ,Cell  , Row  Cell 0
        // sheet
        Row row1 = sheet.createRow((short) i);
        // row
        for (short j = 0; j < keys.length; j++) {
            Cell cell = row1.createCell(j);
            cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
            cell.setCellStyle(cs2);
        }
    }
    return wb;
}

From source file:com.lwr.software.reporter.restservices.ReportExportService.java

License:Open Source License

public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) {
    Workbook wb = new XSSFWorkbook();

    Font boldFont = wb.createFont();
    boldFont.setBold(true);//  w  w w.  j  a va  2  s  .  c  o  m

    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setBorderBottom(BorderStyle.THIN);
    headerStyle.setBorderTop(BorderStyle.THIN);
    headerStyle.setBorderRight(BorderStyle.THIN);
    headerStyle.setBorderLeft(BorderStyle.THIN);
    headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
    headerStyle.setFont(boldFont);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);

    CellStyle titleStyle = wb.createCellStyle();
    titleStyle.setBorderBottom(BorderStyle.THIN);
    titleStyle.setBorderTop(BorderStyle.THIN);
    titleStyle.setBorderRight(BorderStyle.THIN);
    titleStyle.setBorderLeft(BorderStyle.THIN);

    List<RowElement> rows = toExport.getRows();
    int sheetIndex = 0;
    for (RowElement rowElement : rows) {
        List<Element> elements = rowElement.getElements();
        for (Element element : elements) {
            try {
                element.setParams(reportParams);
                element.init();
            } catch (Exception e) {
                logger.error("Unable to init '" + element.getTitle() + "' element of report '"
                        + toExport.getTitle() + "' Error " + e.getMessage(), e);
                return Response.serverError().entity("Unable to init '" + element.getTitle()
                        + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build();
            }
            String sheetName = element.getTitle().substring(0,
                    element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++);
            Sheet sheet = wb.createSheet(sheetName);

            Row reportTitleRow = sheet.createRow(0);
            Cell reportTitleHeader = reportTitleRow.createCell(0);
            reportTitleHeader.setCellStyle(headerStyle);
            reportTitleHeader.setCellValue("Report Title:");

            Cell reportTitleCell = reportTitleRow.createCell(1);
            reportTitleCell.setCellStyle(titleStyle);
            reportTitleCell.setCellValue(toExport.getTitle());

            Row elementTitleRow = sheet.createRow(1);
            Cell elementTitleHeader = elementTitleRow.createCell(0);
            elementTitleHeader.setCellStyle(headerStyle);
            elementTitleHeader.setCellValue("Element Title:");

            Cell elementTitleCell = elementTitleRow.createCell(1);
            elementTitleCell.setCellStyle(titleStyle);
            elementTitleCell.setCellValue(element.getTitle());

            List<List<Object>> dataToExport = element.getData();

            int rowIndex = 3;
            Row headerRow = sheet.createRow(rowIndex++);
            List<Object> unifiedHeaderRow = element.getHeader();
            for (int i = 0; i < unifiedHeaderRow.size(); i++) {
                Cell headerCell = headerRow.createCell(i);
                String headerCellValue = unifiedHeaderRow.get(i).toString();
                headerCell.setCellValue(headerCellValue);
                headerCell.setCellStyle(headerStyle);
            }
            for (int i = 0; i < dataToExport.size(); i++) {
                Row row = sheet.createRow(rowIndex++);
                List<Object> unifiedRow = dataToExport.get(i);
                int cellIndex = 0;
                for (Object cellValue : unifiedRow) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellStyle(cellStyle);
                    try {
                        double val = Double.parseDouble(cellValue.toString());
                        cell.setCellValue(val);
                    } catch (NumberFormatException e) {
                        cell.setCellValue(cellValue.toString());
                    }
                    cellIndex++;
                }
            }
        }
    }
    try {
        File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime());
        logger.info("Export CSV temp file path is " + file.getAbsoluteFile());
        wb.write(new FileOutputStream(file));
        wb.close();
        ResponseBuilder responseBuilder = Response.ok((Object) file);
        responseBuilder.header("Content-Type",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        responseBuilder.header("Content-Transfer-Encoding", "binary");
        responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName());
        responseBuilder.header("Content-Length", file.length());
        Response responseToSend = responseBuilder.build();
        file.deleteOnExit();
        return responseToSend;
    } catch (Exception e1) {
        return Response.serverError()
                .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build();
    }

}

From source file:com.macleod2486.magicbuilder.BlockConstructed.java

License:Open Source License

public void getBlock(int selection) {
    selection--;//from  w w w.j  a va  2  s  . co m

    try {
        int indexAdjust = 0;
        int rowIndex;
        int numberOfSets = Integer.parseInt(NumberOfBlocks.get(selection));
        int blockNameIndex = selection;
        selection = Integer.parseInt(IndexOfBlocks.get(selection));
        String tempItem;

        //Various elements
        Document page;
        Element setSection;
        Elements row;
        Elements td;

        //Worksheet the information will be written too
        HSSFWorkbook block = new HSSFWorkbook();
        HSSFSheet blockSet;
        Row infoRow;

        DateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy");
        Date date = new Date();

        while (numberOfSets > 0) {
            page = Jsoup.connect(tcgSite + BlockSets.get(selection + indexAdjust).toString()).get();
            setSection = page.select("table").get(2);
            row = setSection.select("tr");
            rowIndex = 1;

            blockSet = block.createSheet(BlockSets.get(selection + indexAdjust).toString()
                    .replaceAll("%20", " ").replaceAll("%27", " "));

            infoRow = blockSet.createRow(0);
            infoRow.createCell(0).setCellValue("Card Name");
            infoRow.createCell(1).setCellValue("High Price");
            infoRow.createCell(2).setCellValue("Medium Price");
            infoRow.createCell(3).setCellValue("Low Price");

            for (Element cardrow : row) {
                td = cardrow.select("td");
                tempItem = td.get(0).text().substring(1);

                if (!tempItem.contains("Forest") && !tempItem.contains("Mountain")
                        && !tempItem.contains("Swamp") && !tempItem.contains("Island")
                        && !tempItem.contains("Plains") && !tempItem.isEmpty()) {
                    if (td.get(5).text().length() > 2 && td.get(6).text().length() > 2
                            && td.get(7).text().length() > 2) {
                        infoRow = blockSet.createRow(rowIndex);
                        infoRow.createCell(0).setCellValue(td.get(0).text().substring(1));
                        infoRow.createCell(1).setCellValue(Double.parseDouble(
                                td.get(5).text().substring(1, td.get(5).text().length() - 1).replace(",", "")));
                        infoRow.createCell(2).setCellValue(Double.parseDouble(
                                td.get(6).text().substring(1, td.get(6).text().length() - 1).replace(",", "")));
                        infoRow.createCell(3).setCellValue(Double.parseDouble(
                                td.get(7).text().substring(1, td.get(7).text().length() - 1).replace(",", "")));

                        rowIndex++;
                    }

                }
            }

            blockSet.autoSizeColumn(0);
            blockSet.autoSizeColumn(1);
            blockSet.autoSizeColumn(2);

            indexAdjust++;
            numberOfSets--;

        }

        File blockFile = new File(
                BlockNames.get(blockNameIndex) + "-Block-" + dateFormat.format(date) + "-.xls");
        FileOutputStream blockOutput = new FileOutputStream(blockFile);
        block.write(blockOutput);
        blockOutput.close();

    }

    catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.macleod2486.magicbuilder.Constructed.java

License:Open Source License

public void getCards() {
    try {//from ww  w . j a  va  2s . c o m
        //Keeps track of the rows within the sheet as data is being written
        int rowNum;

        //Creates a excel file for the information to be stored
        HSSFWorkbook standard = new HSSFWorkbook();
        HSSFSheet setname;
        Row newRow;

        //Various values to screen the data
        String clean;
        double highprice = 0;
        double mediumPrice = 0;
        double lowPrice = 0;
        String temp;

        //Variables to take in information
        Document page;
        Element table;
        Elements row;
        Elements item;

        //Variables for extra information about the set
        double averageHighPrice = 0;
        double averageMediumPrice = 0;
        double averageLowPrice = 0;

        DecimalFormat format = new DecimalFormat("#.00");

        /*
         * Grabs the modified set values to then be used for the website url format
         * Not the most effecient for loop but will be modified as time goes on.
         */
        for (int limit = 0; limit < Sets.size(); limit++) {
            rowNum = 0;

            System.out.println("\nSet name: " + Names.get(limit).toString() + "\n");

            //Creates a new sheet per set after it filters out bad characters
            if (Names.get(limit).toString().contains(":")) {
                Names.set(limit, Names.get(limit).toString().replaceAll(":", "\\W"));
            } else if (Names.get(limit).toString().contains("/")) {
                Names.set(limit, Names.get(limit).replaceAll("/", "\\W"));
            }
            setname = standard.createSheet(Names.get(limit).toString());

            //Sets up the initial row in the sheet
            newRow = setname.createRow(0);
            newRow.createCell(0).setCellValue("Card Name");
            newRow.createCell(1).setCellValue("High Price");
            newRow.createCell(2).setCellValue("Medium Price");
            newRow.createCell(3).setCellValue("Low Price");

            /*Each modified string value is then put in the following url to then parse
              the information from it. */

            page = Jsoup.connect(
                    "http://magic.tcgplayer.com/db/price_guide.asp?setname=" + Sets.get(limit).toString())
                    .get();
            table = page.select("table").get(2);
            row = table.select("tr");

            //Grabs each card that was selected
            for (Element tableRow : row) {
                //Gets the first row 
                item = tableRow.select("td");
                clean = item.get(0).text();

                //Filters out land cards
                if (!clean.isEmpty()) {
                    if (item.get(5).text().length() > 2 && item.get(6).text().length() > 2
                            && item.get(7).text().length() > 2) {
                        //Creates new row in the sheet
                        newRow = setname.createRow(rowNum + 1);

                        //Gets the name of the card
                        clean = clean.substring(1);
                        newRow.createCell(0).setCellValue(clean);

                        //This gets the high price
                        temp = item.get(5).text();
                        highprice = removeCommas(temp.substring(1, temp.length() - 2));
                        newRow.createCell(1).setCellValue(highprice);
                        averageHighPrice += highprice;

                        //This gets the medium price
                        temp = item.get(6).text();
                        mediumPrice = removeCommas(temp.substring(1, temp.length() - 2));
                        newRow.createCell(2).setCellValue(mediumPrice);
                        averageMediumPrice += mediumPrice;

                        //This gets the low price
                        temp = item.get(7).text();
                        lowPrice = removeCommas(temp.substring(1, temp.length() - 2));
                        newRow.createCell(3).setCellValue(lowPrice);
                        averageLowPrice += lowPrice;

                        System.out.println(
                                clean + "  H:$" + highprice + " M:$" + mediumPrice + " L:$" + lowPrice);
                        rowNum++;

                    }
                }

            }

            if (Double.isNaN(averageHighPrice) && Double.isNaN(averageMediumPrice)
                    && Double.isNaN(averageLowPrice)) {
                //Finds the averages
                averageHighPrice /= rowNum;
                averageMediumPrice /= rowNum;
                averageLowPrice /= rowNum;

                //Formats them
                averageHighPrice = Double.parseDouble(format.format(averageHighPrice));
                averageMediumPrice = Double.parseDouble(format.format(averageMediumPrice));
                averageLowPrice = Double.parseDouble(format.format(averageLowPrice));

                //Inserts the values into the table
                newRow = setname.getRow(0);

                newRow.createCell(4).setCellValue("Average High Price");
                newRow.createCell(5).setCellValue("Average Medium Price");
                newRow.createCell(6).setCellValue("Average Low Price");

                newRow = setname.getRow(1);
                newRow.createCell(4).setCellValue(averageHighPrice);
                newRow.createCell(5).setCellValue(averageMediumPrice);
                newRow.createCell(6).setCellValue(averageLowPrice);

                System.out.println("Average Prices " + averageHighPrice + " " + averageMediumPrice + " "
                        + averageLowPrice);
            }

            //Zeroes them out
            averageHighPrice = averageMediumPrice = averageLowPrice = 0;

            //Sets the sheet to auto size columns
            for (int index = 0; index < 7; index++) {
                setname.autoSizeColumn(index);
            }

        }

        //Creates the date to be added in the output file name. 
        DateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy");
        Date date = new Date();

        if (this.selection == 0) {
            File standardFile = new File("Standard-" + dateFormat.format(date) + "-.xls");
            FileOutputStream standardOutput = new FileOutputStream(standardFile);
            standard.write(standardOutput);
            standardOutput.close();
        } else if (this.selection == 1) {
            File standardFile = new File("Modern-" + dateFormat.format(date) + "-.xls");
            FileOutputStream standardOutput = new FileOutputStream(standardFile);
            standard.write(standardOutput);
            standardOutput.close();
        } else {
            File standardFile = new File("All-" + dateFormat.format(date) + "-.xls");
            FileOutputStream standardOutput = new FileOutputStream(standardFile);
            standard.write(standardOutput);
            standardOutput.close();
        }

    }

    catch (Exception e) {
        e.printStackTrace();
        if (e.toString().contains("Status=400")) {
            System.out.println("That webpage does not exist!");
        } else if (e.toString().contains("SocketTimeout")) {
            System.out.println("Your connection timed out");
        }
    }

}

From source file:com.maogousoft.wuliu.controller.DriverController.java

public void exportExcel() throws IOException {
    StringBuffer from = new StringBuffer();
    from.append("from logistics_driver where status = 1 ");
    from.append(createOrder());// w w w  .j a va2  s  .c o  m
    Page<Record> page = Db.paginate(getPageIndex(), 100000, "select * ", from.toString());
    List<Record> list = page.getList();
    Dict.fillDictToRecords(page.getList());

    String headers = "?|?|??|??|?|??|||?|??||?|??|?|??|?";
    String attributes = "id|phone|name|recommender|plate_number|id_card|car_type_str|car_length|car_weight|gold|regist_time|car_phone|start_province_str|start_city_str|end_province_str|end_city_str";

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    Row headerRow = sheet.createRow(0);
    List<String> headerList = WuliuStringUtils.parseVertical(headers);
    for (int j = 0; j < headerList.size(); j++) {
        String attr = headerList.get(j);
        Cell cell = headerRow.createCell(j);
        cell.setCellValue(attr);
    }

    for (int i = 0; i < list.size(); i++) {
        Record record = list.get(i);
        Row row = sheet.createRow(i + 1);

        List<String> attrList = WuliuStringUtils.parseVertical(attributes);
        for (int j = 0; j < attrList.size(); j++) {
            String attr = attrList.get(j);
            Cell cell = row.createCell(j);
            Object value = getValue(record, attr);
            cell.setCellValue(value + "");
        }
    }

    HttpServletResponse resp = getResponse();
    String filename = TimeUtil.format(new Date(), "'?'yyyyMMdd_HHmmss'.xls'");
    resp.addHeader("Content-Disposition",
            "attachment;filename=" + new String(filename.getBytes("GBK"), "ISO-8859-1"));
    ServletOutputStream out = resp.getOutputStream();
    wb.write(out);
    out.close();
    renderNull();
}

From source file:com.maogousoft.wuliu.controller.DriverController.java

/**
 * /*from   w  w w .j  av a  2 s  .c  o  m*/
 * @description ? 
 * @author shevliu
 * @email shevliu@gmail.com
 * 201386 ?11:47:19
 * @throws IOException
 */
public void exportPendingAudit() throws IOException {
    StringBuffer from = new StringBuffer();
    from.append("from logistics_driver where status = 0 ");
    from.append(createOrder());
    Page<Record> page = Db.paginate(getPageIndex(), 100000, "select * ", from.toString());
    List<Record> list = page.getList();
    Dict.fillDictToRecords(page.getList());

    String headers = "?|?|??|??|?|??|||?|??||?|??|?|??|?";
    String attributes = "id|phone|name|recommender|plate_number|id_card|car_type_str|car_length|car_weight|gold|regist_time|car_phone|start_province_str|start_city_str|end_province_str|end_city_str";

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    Row headerRow = sheet.createRow(0);
    List<String> headerList = WuliuStringUtils.parseVertical(headers);
    for (int j = 0; j < headerList.size(); j++) {
        String attr = headerList.get(j);
        Cell cell = headerRow.createCell(j);
        cell.setCellValue(attr);
    }

    for (int i = 0; i < list.size(); i++) {
        Record record = list.get(i);
        Row row = sheet.createRow(i + 1);
        List<String> attrList = WuliuStringUtils.parseVertical(attributes);
        for (int j = 0; j < attrList.size(); j++) {
            String attr = attrList.get(j);
            Cell cell = row.createCell(j);
            Object value = getValue(record, attr);
            cell.setCellValue(value + "");
        }
    }

    HttpServletResponse resp = getResponse();
    String filename = TimeUtil.format(new Date(), "'?'yyyyMMdd_HHmmss'.xls'");
    resp.addHeader("Content-Disposition",
            "attachment;filename=" + new String(filename.getBytes("GBK"), "ISO-8859-1"));
    ServletOutputStream out = resp.getOutputStream();
    wb.write(out);
    out.close();
    renderNull();
}