customizing excel file from java

Question

I have a jsp page populated with tables from a data base. I have to throw the data of this table in excel file. Also I have to customize the excel file like border colors, fonts merging cell cell colors etc along with the data. How can I do it? Is there any java script for this work? or are there any open source tools for this task?

I have found one like dataTables jquery package but it did not customize the excel file only throws data to excel. or do I have to use java libraries??

@nischal You could do it with JavaScript, but only works in Internet Explorer. — Paul Vargas, Jun 02 '12 at 04:31

score 3 · Accepted Answer · edited Jun 01 '12 at 12:37

You can use Apache POI for that.

For Reference -

public class xyzExcelExportView extends AbstractExcelView {

    private static final xyzService xyzService = ApplicationContextProvider.getxyzServiceImpl();

    @SuppressWarnings({ "unchecked" })
    protected void buildExcelDocument(Map model, HSSFWorkbook workbook,
            HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        CurrencyService currencyService = ApplicationContextProvider.getCurrencyService();
        Currency currency = null;
        //set Response
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "Attachment;Filename=\""+"ExcelExport"+"."+"xls" + "\"");
        // Create Sheet.
        HSSFSheet sheet = workbook.createSheet("Software Contract Database");
        // Create Style for sheet.
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        HSSFCellStyle dateStyle = workbook.createCellStyle();       
        HSSFCellStyle datePatternStyle = workbook.createCellStyle();
        HSSFDataFormat format1 = workbook.createDataFormat();
        HSSFCellStyle dataStyle = workbook.createCellStyle();
        HSSFCellStyle numberStyle = workbook.createCellStyle();     
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        dataStyle.setWrapText(true);
        datePatternStyle.setDataFormat(HSSFDataFormat
                .getBuiltinFormat("d-mmm-yy"));
        numberStyle.setDataFormat(format1.getFormat("#,##0"));
        // Font setting for sheet.
        HSSFFont font = workbook.createFont();
        HSSFPalette palette = workbook.getCustomPalette();
        palette.setColorAtIndex(HSSFColor.LAVENDER.index,
                (byte) 204,  
                (byte) 204,    
                (byte) 255);
        font.setBoldweight((short) 700);
        sheet.setDefaultColumnWidth((short) 30);
        List<Type> dataList = // YOur Data list     
        int currentRow = 0;
        // WRITE ROW FOR HEADER
        HSSFCell header = null;
        for (short i = 0; i < getHeader().size(); i++) {
            header = getCell(sheet, currentRow, i);
            headerStyle.setFillForegroundColor(HSSFColor.LAVENDER.index);
            headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            headerStyle.setFont(font);
            header.setCellStyle(headerStyle);
            setText(header, getxyzHeader().get(i));
        }       

        HSSFRow row = null;
        HSSFCell cellNumber = null;
        HSSFCell cellString = null;
        HSSFCell cellNumberWitnComma = null;
        HSSFCell cellDate = null;

        for(Type xyz : dataList){
                currentRow++;
                short i = 0;
                row = sheet.createRow(currentRow);
                cellNumber = row.createCell(i);
                cellNumber = fillxyzExcelExportCell(xyz.getId() == null ? 0 : xyz.getId(), 1 , cellNumber);
                cellNumber.setCellStyle(dataStyle);

                cellString = row.createCell(++i);
                cellString = fillxyzExcelExportCell(xyz.getStatus() == null ? "" : xyz.getStatus() , 3 , cellString);   
                cellString.setCellStyle(dataStyle); 

                cellString = row.createCell(++i);
                cellString = fillxyzExcelExportCell(xyz.getxyzStatus() , 3 , cellString);   
                cellString.setCellStyle(dataStyle);

                cellString = row.createCell(++i);
                cellString = fillxyzExcelExportCell(xyz.getName() == null ? "" : xyz.getName(), 3 , cellString);
                cellString.setCellStyle(dataStyle);     
            }
        }   
        workbook.write(response.getOutputStream());
    }

score 2 · Answer 2 · answered Jun 01 '12 at 12:11

2

Apache POI would be well suited to this task - it's a Java library and has very good excel support. It's free and open source.

answered Jun 01 '12 at 12:11

Michael Berry

70,193
21
157
216

score 1 · Answer 3 · answered Jun 01 '12 at 12:25

1

JExcelApi is the open source library that allows you to read ,write and customize the excel sheets. It might accomplish your requirement.

answered Jun 01 '12 at 12:25

RAVITEJA SATYAVADA

2,503
23
56
88

score 0 · Answer 4 · edited May 23 '17 at 10:34

The following answers may be helpful:

How to export html table to excel using javascript This works only in IE.
Excel XML Spreadsheet example You need specify the Content-Type header application/vnd.ms-excel and optionally the header Content-Disposition For reference about XML format, see http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx
Generating xls file from corresponding xml

You can also export an HTML file to Excel by changing the Content-Type and Content-Disposition in your JSP. Since Excel 2003, you can open HTML files. My favorite way is Apache POI for XLS or XLSX.

customizing excel file from java

4 Answers4