Creating Excel Document with Apache POI


    public static byte[] convertCSVToExcell(String csv) {
        byte[] result = null;
        try {
            // Convert csv to lists of strings
            CSVReader reader = new CSVReader(new StringReader(csv));
            List arList = reader.readAll();
            
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet("Main");
            
            HSSFFont font = hwb.createFont();  
            font.setFontName("Lucida Sans");  
            
            int maxColumns = 0;
            for(int k = 0; k  maxColumns) {
                    maxColumns = ardata.length;
                }
                for (int p = 0; p < ardata.length; p++) {
                    HSSFCell cell = row.createCell((short) p);
                    cell.getCellStyle().setFont(font);
                    String data = ardata[p].toString();
                    if (data.startsWith("=")) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        data = data.replaceAll("\"", "");
                        data = data.replaceAll("=", "");
                        cell.setCellValue(data);
                    } else if (data.startsWith("\"")) {
                        data = data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data);
                    } else {
                        data = data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(data);
                    }
                }
            }
            
            // requires: java.awt.headless=true jvm option
            for(int i=0; i < maxColumns; i++) {
                sheet.autoSizeColumn(i);
            }
            
            ByteArrayOutputStream output = new ByteArrayOutputStream();
            hwb.write(output);
            output.flush();
            output.close();
            result = output.toByteArray();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s