Java POI Export Excel

Many times, a software application needs to generate a report in Microsoft Excel file format. Sometimes, an application might even want to use an Excel file as input data. For example, an application developed by a company will need all the output from the Finance department to generate its own Excel.

Any Java programmer willing to use the output of MS Office files can be done using pre-defined and read-only APIs.

What is Apache POI?

Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. This is an open source library developed by the Apache Software Foundation that uses Java distributed design or modifies Microsoft Office files. It contains classes and methods to decode user input data or files into MS Office documents.

Apache POI Component

Apache POI contains classes and methods to compound all of the MS Office OLE 2 documents. The list of this API component is as follows.

• poifs (poor obfuscation technology for file systems): This component is a fundamental factor for all other POI components. It is used to explicitly read different files.

• HSSF (horrible spreadsheet format): It is used to read and write the XLS format of the Ms-excel file.

• XSSF (XML format): It is used for ms-excel in the xlsx file format.

• HPSF (horrible property formatting): It is used to extract Ms-office file property settings.

• HWPF (terrible word processor format): It is a file used to read and write Ms-word's document extension.

• XWPF (XML word Processor format): It is a file used to read and write Ms-word docx extensions.

• HSLF (Scary Slide layout format): It is used to read, create, and edit PowerPoint presentations.

• HDGF (Scary Chart format): It contains classes and methods for Ms-visio binary files.

• HPBF (horrible publisher format): It is used to read and write Ms-publisher files.

What I do use Java POI to export Excel files. Therefore, only the HSSF component is limited.

Exportutil Tool Class:

Package Net.dagongsoft.framework.util.poi;import Java.text.simpledateformat;import Java.util.date;import Org.apache.commons.lang.stringutils;import Org.apache.poi.hssf.usermodel.hssfcell;import Org.apache.poi.hssf.usermodel.hssfcellstyle;import Org.apache.poi.hssf.usermodel.hssfrow;import Org.apache.poi.hssf.usermodel.hssfsheet;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Com.alibaba.fastjson.jsonarray;import com.alibaba.fastjson.jsonobject;/** * @description use Java POI to export Excel common class * @            Author LDP * @time June 8, 2017 pm 2:17:08 * */public class Exportutil {/** * export Excel (. xls) format, small amount of data * * @param title * Sheet name * @param headstring * Column name * @param jsonarray * Data set */public static Hssfworkbook export Excel (string title, String headstring, Jsonarray Jsonarray) {//Declare a workbook Hssfworkbook workbook = new Hssfworkbook ();//Header style/ /Hssfcellstyle TitleStyle = Workbook.createcellstyle ();//Titlestyle.setalignment (Hssfcellstyle.align_center);// Hssffont Titlefont = Workbook.createfont ();//titlefont.setfontheightinpoints (short);//Titlefont.setboldweight ((short) 700);// Titlestyle.setfont (Titlefont);//column header style Hssfcellstyle HeaderStyle = Workbook.createcellstyle ();// Headerstyle.setfillpattern (Hssfcellstyle.solid_foreground);//Headerstyle.setborderbottom (HSSFCellStyle.BORDER_ THIN);//Headerstyle.setborderleft (Hssfcellstyle.border_thin);//Headerstyle.setborderright (HSSFCellStyle.BORDER _thin);//Headerstyle.setbordertop (Hssfcellstyle.border_thin);//Headerstyle.setalignment (HSSFCellStyle.ALIGN_ CENTER);//Hssffont HeaderFont = Workbook.createfont ();//Headerfont.setfontheightinpoints ((short) 12);// Headerfont.setboldweight (hssffont.boldweight_bold);//Headerstyle.setfont (HeaderFont);//Cell style Hssfcellstyle CellStyle = Workbook.createcellstyle ();//Cellstyle.setfillpattern (Hssfcellstyle.solid_foreground);// Cellstyle.setborderbottom (Hssfcellstyle.border_thin);//Cellstyle.setborderleft (Hssfcellstyle.border_thin);// Cellstyle.setborderright (HssfcellStyle.border_thin);//Cellstyle.setbordertop (Hssfcellstyle.border_thin);//Cellstyle.setalignment ( Hssfcellstyle.align_center);//Cellstyle.setverticalalignment (Hssfcellstyle.vertical_center);//HSSFFont CellFont = Workbook.createfont ();//Cellfont.setboldweight (Hssffont.boldweight_normal);//Cellstyle.setfont (CellFont);// Generate a table Hssfsheet sheet = null;if (Stringutils.isnotblank (title)) {sheet = Workbook.createsheet (title);} else {sheet = Work Book.createsheet ();} string[] headers = Headstring.split (",");//Iterate through the collection data, resulting in a data row int rowIndex = 0;int count = 0;if (Jsonarray! = null) {for (int n = 0; N < jsonarray.size (); n++) {if (RowIndex = = 65535 | | rowIndex = = 0) {if (RowIndex! = 0) {count++;//If the data exceeds, the second page shows if (Stringutils.isnotblank (Ti TLE) {sheet = Workbook.createsheet (title + count);} else {sheet = Workbook.createsheet ();}} Hssfrow headerrow = sheet.createrow (0); Column header rowIndex =0for (int i = 0; i < headers.length; i++) {Headerrow.createcell (i). Setcellvalue (Headers[i]); headerrow.g Etcell (i). Setcellstyle (HeaderStyle);} RowIndex = 1;//data content starting from rowindex=1}jsonobject Jo = Jsonarray.getjsonobject (n); Hssfrow DataRow = Sheet.createrow (RowIndex); for (int i = 0; i < headers.length; i++) {Hssfcell Newcell = datarow.create Cell (i); Object o = Jo.get (headers[i]); String cellvalue = ""; if (o = = null) {Cellvalue = "";} else if (o instanceof Date) {cellvalue = new SimpleDateFormat ("Yyyy/mm/dd 00:00:00"). Format (o);} else {cellvalue = O.tostr ing ();} Newcell.setcellvalue (Cellvalue); Newcell.setcellstyle (CellStyle);} rowindex++;}} AutoFit width for (int i = 0; i < headers.length; i++) {sheet.autosizecolumn (i);} return workbook;}}

Control class:

Package Net.dagongsoft.platform.poi.controller;import Java.io.outputstream;import Java.util.arraylist;import Java.util.list;import Javax.servlet.http.httpservletrequest;import Javax.servlet.http.httpservletresponse;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.springframework.beans.factory.annotation.autowired;import Org.springframework.stereotype.controller;import Org.springframework.web.bind.annotation.requestmapping;import Com.alibaba.fastjson.jsonarray;import Net.dagongsoft.platform.poi.entity.student;import Net.dagongsoft.platform.poi.service.studentexportservice;import Net.dagongsoft.platform.poi.util.ExportUtilTest ; @Controller @requestmapping ("/studentexportcontroller") public class Studentexportcontroller {@Autowiredprivate Studentexportservice Studentexportservice; @RequestMapping ("/export.form") public void Exportexcel ( HttpServletRequest request, HttpServletResponse response) throws Exception {list<student> List = new arraylist< Student> (); List.add (new S)Tudent ("Zhangsan", "a")), List.add (New Student (1001, "Lisi", "Max")), List.add (New Student (1002, "Wangwu", "25")); Hssfworkbook WB = studentexportservice.export (list), Response.setcontenttype ("application/vnd.ms-excel"); Response.setheader ("Content-disposition", "Attachment;filename=student.xls"); OutputStream OuputStream = Response.getoutputstream (); Wb.write (Ouputstream); Ouputstream.flush (); Ouputstream.close ();}}

Serivice Interface declaration:

Package Net.dagongsoft.platform.poi.service;import Java.util.list;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Net.dagongsoft.platform.poi.entity.student;public Interface Studentexportservice {Hssfworkbook export (list<student> List);}

Service Implementation class:

Package Net.dagongsoft.platform.poi.service.impl;import Java.util.list;import Org.apache.poi.hssf.usermodel.hssfcell;import Org.apache.poi.hssf.usermodel.hssfcellstyle;import Org.apache.poi.hssf.usermodel.hssfrow;import Org.apache.poi.hssf.usermodel.hssfsheet;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.springframework.stereotype.service;import Net.dagongsoft.platform.poi.entity.student;import net.dagongsoft.platform.poi.service.studentexportservice;@ Servicepublic class Studentexportserviceimpl implements Studentexportservice {string[] Excelheader = {"Id", "Name", "age "}; @Overridepublic Hssfworkbook export (list<student> List) {Hssfworkbook wb = new Hssfworkbook (); Hssfsheet sheet = wb.createsheet ("Student"); Hssfrow row = Sheet.createrow ((int) 0); Hssfcellstyle style = Wb.createcellstyle (); for (int i = 0; i < excelheader.length; i++) {Hssfcell cell = Row.createcell (i); Cell.setcellvalue (Excelheader[i]); Cell.setcellstyle (style); Sheet.autosizecolumn (i);//sheet. SetColumnWidth (i, 100 * 256);} for (int i = 0; i < list.size (); i++) {row = Sheet.createrow (i + 1); Student Student = List.get (i), Row.createcell (0). Setcellvalue (Student.getid ()); Row.createcell (1). Setcellvalue ( Student.getname ()); Row.createcell (2). Setcellvalue (Student.getage ());} return WB;}}

　　

Java POI Export Excel