JAVA export EXCEL table POI

Today, I will bring you a sample of how to use poi to export Excel for Java Web projects, which is suitable for novices in the first contact. There are two steps to export Excel: 1. Generate an excel and put it in the working directory; 2. Export it to the local.

Please download the required jar package by yourself, as shown in the figure:

Then introduce the jar package
Next, put the code. The code looks complex, in fact, a little analysis, so easy!!!

jsp page

//The first step is to generate excel and put it in a directory
//Add a click event to a button on the page
$('#exportBtn').bind('click', function() {
    $.ajax({
        async : true,
        cache : false,
        type : 'post',
        dataType : 'json',
        data : {},
        //url needs to be modified by yourself
        url : '${ctx}/convert/createExcel',

        //Step 2: export the files in the directory to the local
        success : function(result) {
            if($('#download').length > 0) {
                //The url here also needs to be modified by yourself
                $('#download').attr('src', '${ctx}/convert/down?fileNm=' + result.fileNm);
            } else {
                $('body').append($('<iframe id="download" style="display : none" />'));
                $('#download').attr('src', '${ctx}/convert/down?fileNm=' + result.fileNm);
            }
        }
    });
});

java code (generate excel):

@RequestMapping("/createExcel")
@ResponseBody
public Map<String, Object> createExcel() throws IOException {

    //Make some data first
    //Suppose there are three columns: number, name and age
    Map<String, Object> titles = new LinkedHashMap<String, Object>();
    titles.put("id", "number");
    titles.put("name", "Full name");
    titles.put("age", "Age");

    //Make 3 lines of data by yourself
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    Map<String, Object> map1 = new HashMap<String, Object>();
    Map<String, Object> map2 = new HashMap<String, Object>();
    Map<String, Object> map3 = new HashMap<String, Object>();

    map1.put("id", "001");
    map1.put("name", "Zhang San");
    map1.put("age", "29");
    list.add(map1);

    map2.put("id", "002");
    map2.put("name", "Li Si");
    map2.put("age", "26");
    list.add(map2);

    map3.put("id", "003");
    map3.put("name", "Wang Wu");
    map3.put("age", "25");
    list.add(map3);

    //Generate excel officially
    //Export the most basic object of excel
    HSSFWorkbook book = new HSSFWorkbook();
    //sheet page object
    HSSFSheet sheet = (HSSFSheet)book.createSheet("sheet Page name");
    //Cell format object
    HSSFCellStyle style = book.createCellStyle();

    //Format it up, down, left, right
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);

    int rowNum = 0;//Line sequence number, starting at line 0
    int colNum = 0;//Column ordinal, starting at line 0

    //Line object, referring to a line
    Row row = sheet.createRow(rowNum);
    //Cell object
    Cell cell = null;

    for(String key : titles.keySet()) {
        //Create a cell
        cell = row.createCell(colNum);
        //Write data to cells
        cell.setCellValue(titles.get(key).toString());
        //Set the style
        cell.setCellStyle(style);
        //Set row height
        row.setHeightInPoints(20);
        //Change to next cell (next column in same row)
        colNum++;
    }
    //Line feed
    rowNum++;
    //Back to column 0
    colNum = 0;

    //Write data, same as title, same as above
    for(int i = 0;i < list.size();i++) {
        Map<String, Object> data = list.get(i);
        row = sheet.createRow(rowNum);
        for(String key : titles.keySet()) {
            cell = row.createCell(colNum);
            cell.setCellValue(data.get(key).toString());
            cell.setCellStyle(style);
            row.setHeightInPoints(20);
            //Set the following width
            sheet.setColumnWidth(colNum,  data.get(key).toString().getBytes().length * 2 * 256);
            colNum++;
        }
        rowNum++;
        colNum=0;
    }

    //Generate file
    Map<String, Object> result = new HashMap<String, Object>();

    //Note the escape, or use File.separator
    //You must find a path under a web, or an error will be reported
    String fileNm = "C:\\study\\eclipse\\workspace\\spring_poi\\WebContent\\1.xlsx";
    result.put("fileNm", fileNm);
    OutputStream out =  new FileOutputStream(fileNm);
    book.write(out);
    return result;
}

java code (download excel):

//For simple file download, readers can also write a
@RequestMapping("/down")
public void down(HttpServletResponse response, String fileNm) throws IOException {
    File file = new File(fileNm);
    String finalNm = "final.xlsx";
    response.setContentType("text/html;charset=UTF-8");
    BufferedInputStream bis = null;
    BufferedOutputStream bos = null;
    long fileLength = file.length();
    response.setContentType("application/octet-stream");
    response.setHeader("Content-disposition", "attachment; filename="
            + new String(finalNm.getBytes("gbk"), "ISO8859-1") );
    response.setHeader("Content-Length", String.valueOf(fileLength));
    bis = new BufferedInputStream(new FileInputStream(file));
    bos = new BufferedOutputStream(response.getOutputStream());
    byte[] buff = new byte[2048];
    int bytesRead;
    while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
        bos.write(buff, 0, bytesRead);
    }
    bis.close();
    bos.close();
}

And then the test results

(good soil)

Click export...
You can download excel.

It's still very earthy...

Tags: Excel Java JSP JSON

Posted on Sun, 03 May 2020 00:14:34 -0700 by abhic