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.linus.excel.poi.MergingCells.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");

    Row row = sheet.createRow((short) 1);
    Cell cell = row.createCell((short) 1);
    cell.setCellValue(new XSSFRichTextString("This is a test of merging"));

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("excel/merging_cells.xlsx");
    wb.write(fileOut);/*from   w w  w  .jav a  2 s  . c  o  m*/
    fileOut.close();
}

From source file:com.lufs.java.apache.poi.example.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (String arg : args) {
        if (arg.charAt(0) == '-') {
            xlsx = arg.equals("-xlsx");
        } else {/*from  w w  w  .  java 2  s. c om*/
            calendar.set(Calendar.YEAR, Integer.parseInt(arg));
        }
    }
    int year = calendar.get(Calendar.YEAR);

    try (Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook()) {

        Map<String, CellStyle> styles = createStyles(wb);

        for (int month = 0; month < 12; month++) {
            calendar.set(Calendar.MONTH, month);
            calendar.set(Calendar.DAY_OF_MONTH, 1);
            //create a sheet for each month
            Sheet sheet = wb.createSheet(months[month]);

            //turn off gridlines
            sheet.setDisplayGridlines(false);
            sheet.setPrintGridlines(false);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);
            PrintSetup printSetup = sheet.getPrintSetup();
            printSetup.setLandscape(true);

            //the following three statements are required only for HSSF
            sheet.setAutobreaks(true);
            printSetup.setFitHeight((short) 1);
            printSetup.setFitWidth((short) 1);

            //the header row: centered text in 48pt font
            Row headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(80);
            Cell titleCell = headerRow.createCell(0);
            titleCell.setCellValue(months[month] + " " + year);
            titleCell.setCellStyle(styles.get("title"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

            //header with month titles
            Row monthRow = sheet.createRow(1);
            for (int i = 0; i < days.length; i++) {
                //set column widths, the width is measured in units of 1/256th of a character width
                sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
                sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
                sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
                Cell monthCell = monthRow.createCell(i * 2);
                monthCell.setCellValue(days[i]);
                monthCell.setCellStyle(styles.get("month"));
            }

            int cnt = 1, day = 1;
            int rownum = 2;
            for (int j = 0; j < 6; j++) {
                Row row = sheet.createRow(rownum++);
                row.setHeightInPoints(100);
                for (int i = 0; i < days.length; i++) {
                    Cell dayCell_1 = row.createCell(i * 2);
                    Cell dayCell_2 = row.createCell(i * 2 + 1);

                    int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                    if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                        dayCell_1.setCellValue(day);
                        calendar.set(Calendar.DAY_OF_MONTH, ++day);

                        if (i == 0 || i == days.length - 1) {
                            dayCell_1.setCellStyle(styles.get("weekend_left"));
                            dayCell_2.setCellStyle(styles.get("weekend_right"));
                        } else {
                            dayCell_1.setCellStyle(styles.get("workday_left"));
                            dayCell_2.setCellStyle(styles.get("workday_right"));
                        }
                    } else {
                        dayCell_1.setCellStyle(styles.get("grey_left"));
                        dayCell_2.setCellStyle(styles.get("grey_right"));
                    }
                    cnt++;
                }
                if (calendar.get(Calendar.MONTH) > month)
                    break;
            }
        }

        // Write the output to a file
        String file = "calendar.xls";
        if (wb instanceof XSSFWorkbook)
            file += "x";

        try (FileOutputStream out = new FileOutputStream(file)) {
            wb.write(out);
        }
    }
}

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

License:Apache License

/**
 * workbook/*from   ww  w . j  a v a  2s  .  co  m*/
 * 1?vbs ?
 * 2?c#??
 * ? ????office 2007 ?
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2003WithOneSheetPerWorkBook(final User user, final String contextRootPath,
        final Searchable searchable) {
    int workbookCount = 0;
    List<String> workbookFileNames = new ArrayList<String>();
    int perSheetRows = 60000; //?sheet 6w?
    int totalRows = 0;
    String extension = "xls";

    int pageSize = 1000;
    Long maxId = 0L;

    BufferedOutputStream out = null;
    try {
        long beginTime = System.currentTimeMillis();

        while (true) {
            workbookCount++;
            String fileName = generateFilename(user, contextRootPath, workbookCount, extension);
            workbookFileNames.add(fileName);
            File file = new File(fileName);

            HSSFWorkbook wb = new HSSFWorkbook();
            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);

            out = new BufferedOutputStream(new FileOutputStream(file));
            wb.write(out);

            IOUtils.closeQuietly(out);

            if (!page.hasNextPage()) {
                break;
            }
        }

        String fileName = workbookFileNames.get(0);
        if (workbookCount > 1 || needCompress(new File(fileName))) {
            fileName = fileName.substring(0, fileName.lastIndexOf("_")) + ".zip";
            //
            compressAndDeleteOriginal(fileName, workbookFileNames.toArray(new String[0]));
        } else {
            String newFileName = fileName.substring(0, fileName.lastIndexOf("_")) + "." + extension;
            FileUtils.moveFile(new File(fileName), new File(newFileName));
            fileName = newFileName;
        }

        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) {
        e.printStackTrace();
        //
        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);
    }
}

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

License:Apache License

/**
 * excel 2003/*ww w .  ja v a 2  s .c  om*/
 * ????
 * ?sheet65536(usermodel? ?flush ????)
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2003WithUsermodel(final User user, final String contextRootPath,
        final Searchable searchable) {
    int perSheetRows = 60000; //?sheet 6w?
    int totalRows = 0;
    Long maxId = 0L;

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

        HSSFWorkbook wb = new HSSFWorkbook();
        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);
    }
}

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

License:Apache License

/**
 * ???// w  w w  .j  av a2 s.  c om
 * 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   ww w  . j av 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) {
    // //from  ww w  .  j a va 2  s .  co  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/*w  ww  .j  a  va  2 s . com*/
    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//from  w  w  w  .  j  av  a 2s.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);//from w w w  .ja 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();
    }

}