Java uses POI to export data in Excel

Java uses POI to export data in Excel
The Export and Import functions of data need to be used in recent projects. My colleagues and I are responsible for the export function, which is relatively simple. As for the comrades who want to see the import, I do not have it for the moment, so don't waste time on this article. However, the import function will be added later.

When I see some blogs that reference various util toolkit, I can simply use apache poi to directly import the poi package to the project. Of course, you can extract all the code into a tool class. Here, we will not extract it for ease of understanding.

1. First, prepare some jar packages for poi. (I will not talk about them here. You can search for them in my resources. I will share the full source code exported from the data later, it also contains a jar package );

 

2. After preparing the jar package, you can write the code. Next, I will write the code simply so that everyone can understand it. As for how awesome code you want to implement it later, you can. As long as you understand the principle, nothing else will happen, right.

First, I want to create an object class: Student. class

 

package testExport;/** *  * @author zsl * */public class Student {private Integer id;private String name;private String sex;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}}

This is not to mention. Next I have another class: ExportExcel. class

 

Package testExport; import java. io. fileNotFoundException; import java. io. fileOutputStream; import java. io. IOException; import java. util. arrayList; import java. util. list; import javax. swing. JOptionPane; // The following is the data export-related package 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; public class ExportExcel {public void Export () {// declare a work thin HSSFWorkbook wb = new HSSFWorkbook (); // declare a list and name it HSSFSheet sheet = wb. createSheet (student table); // give the list name a length sheet. setdefacolumcolumnwidth (short) 15); // generate a style HSSFCellStyle = wb. createCellStyle (); // create the first row (also known as the header) HSSFRow row = sheet. createRow (0); // The style font is Center style. setAlignment (HSSFCellStyle. ALIGN_CENTER); // create the cell HSSFCell cell = row for the first row of the header. createCell (short) 0); cell. setCellValue (student ID); cell. setCellStyle (style); cell = row. createCell (short) 1); cell. setCellValue (Student name); cell. setCellStyle (style); cell = row. createCell (short) 2); cell. setCellValue (Student gender); cell. setCellStyle (style); // Add some data. Write it to death first. You can replace it with your own set data List.
 
  
List = new ArrayList
  
   
(); List. add (new Student (111, James, male); list. add (new Student (111, Li Si, male); list. add (new Student (111, Wang Wu, female); // fill in the data in the cell for (short I = 0; I <list. size (); I ++) {row = sheet. createRow (I + 1); row. createCell (0 ). setCellValue (list. get (I ). getId (); row. createCell (1 ). setCellValue (list. get (I ). getName (); row. createCell (2 ). setCellValue (list. get (I ). getSex ();} try {// exported to the edisk by default. FileOutputStream out = new FileOut PutStream (E: // student table .xls); wb. write (out); out. close (); JOptionPane. showMessageDialog (null, exported successfully !);} Catch (FileNotFoundException e) {JOptionPane. showMessageDialog (null, export failed !); E. printStackTrace ();} catch (IOException e) {JOptionPane. showMessageDialog (null, export failed !); E. printStackTrace ();}}}
  
 

OK. The above is over. You can test it by changing the data marked with red in the font to your own data set.
Let's take a look:

The above is fine. Is it very simple? Yes, it is very simple. Since everyone knows the principle and how to implement it, what I want to say below is that if each column is not sure, what should I do?

Of course, not everyone needs to do this. Not every project uses this function, but I did. For example, what should I do if I want to select which columns to export on the front-end page?

Of course, the premise is that you must output the columns to be queried to the background at the front-end.

Suppose that I already know what columns need and have obtained them. When passing parameters, each column must be passed. If that column is not required, a null value is passed.

 

// Excel export function exportLabType () {// note: the null value means you do not want to export this column, we use this to identify // all the columns in my table below var typeId = typeId; var typeName = typeName; var buildingArea = null; var ability = null; var personTime = null; var identifier = identifier; var evaluate = evaluate; var establishNumber = establishNumber; var actualNumber = actualNumber; var d = {typeId: typeId, typeName: typeName, buildingArea: buildingArea, ability: ability, personTime: personTime, usingHours: usingHours, evaluate: evaluate, establishNumber: establishNumber, actualNumber: actualNumber}; $. ajax ({type: 'post', url: 'lab/exportlabtype', data: d, dataType: 'job', success: function () {}, error: function (wwb ){}});}

Next, I want to extract a common class from the code for exporting data as the util class. In the future, each table can be used for multiple classes regardless of its type or entity class, which is convenient and easy to use.

 

Package com. tspt. util; import java. io. fileNotFoundException; import java. io. fileOutputStream; import java. io. IOException; import java. lang. reflect. field; import java. lang. reflect. invocationTargetException; import java. lang. reflect. method; import java. util. collection; import java. util. hashMap; import java. util. iterator; import java. util. list; import java. util. map; import javax. swing. JOptionPane; 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;/*** use the open source component POI3.0.2 to dynamically export the EXCEL document ** @ author zsl * @ version v1.0 * @ param
 
  
* The application generic type represents any class that conforms to the javabean style * Note: For the sake of simplicity, the get method of the attribute xxx of the boolean type is getXxx (), instead of isXxx () * // T indicates that an uncertain object class is called the parameter object public class ExportExcel.
  
   
{/*** This is a common method that utilizes the JAVA reflection mechanism, you can output ** @ param title * Table title name * @ param headersName * Table attribute column name array * @ param headersId in EXCEL for data stored in a JAVA set with certain symbols * The data set to be displayed for the field * @ param dataset * corresponding to the table attribute column name, in the set, You must place the object * @ param out * of the class conforming to the javabean style and the stream object associated with the output device, you can export an EXCEL file to a local file or a Network */@ SuppressWarnings (unchecked) public void exportExcel (String title, String [] headersName, String [] headersId, List
   
    
DtoList) {// header Map
    
     
Map = new HashMap
     
      
(); Int key = 0; for (int I = 0; I 
        ZdMap = new HashMap
       
         (); Int value = 0; for (int I = 0; I 
          It = c. iterator (); // generate the table header short size = 0 based on the selected field; while (it. hasNext () {cell = row. createCell (size); cell. setCellValue (it. next (). toString (); cell. setCellStyle (style); size ++;} // field Collection zdC = zdMap. values (); Iterator
         
           LabIt = dtoList. iterator (); int zdRow = 0; while (labIt. hasNext () {int zdCell = 0; zdRow ++; row = sheet. createRow (zdRow); T l = (T) labIt. next (); // use reflection to dynamically call the getXxx () method based on the sequence of the javabean attribute to obtain the attribute value Field [] fields = l. getClass (). getDeclaredFields (); for (short I = 0; I <fields. length; I ++) {Field field = fields [I]; String fieldName = field. getName (); // System. out. println (fieldName); Iterator
          
            ZdIt = zdC. iterator (); while (zdIt. hasNext () {if (zdIt. next (). equals (fieldName) {String getMethodName = get + fieldName. substring (0, 1 ). toUpperCase () + fieldName. substring (1); Class tCls = l. getClass (); try {Method getMethod = tCls. getMethod (getMethodName, new Class [] {}); Object val = getMethod. invoke (l, new Object [] {}); // System. out. println (fields [I]. getName (); // System. out. println (val); String textVal = Null; if (val! = Null) {textVal = val. toString ();} else {textVal = null;} row. createCell (short) zdCell ). setCellValue (textVal); zdCell ++;} catch (SecurityException e) {e. printStackTrace ();} catch (IllegalArgumentException e) {e. printStackTrace ();} catch (NoSuchMethodException e) {e. printStackTrace ();} catch (IllegalAccessException e) {e. printStackTrace ();} catch (InvocationTargetException e) {e. printStackTrace ();}}}}} Try {FileOutputStream xls = new FileOutputStream (C: // Users // Administrator // Desktop // developer.xls); wb. write (xls); xls. close (); JOptionPane. showMessageDialog (null, exported successfully !);} Catch (FileNotFoundException e) {JOptionPane. showMessageDialog (null, export failed !); E. printStackTrace ();} catch (IOException e) {JOptionPane. showMessageDialog (null, export failed !); E. printStackTrace ();}}}
          
         
        
       
      
     
    
   
  
 

The above class is used as follows:

 

 

@ SuppressWarnings (unchecked) public void exportLabType () {// The following is the String typeId = request parameter obtained from the foreground. getParameter (typeId); String typeName = request. getParameter (typeName); String buildingArea = request. getParameter (buildingArea); String ability = request. getParameter (ability); String personTime = request. getParameter (personTime); String usingHours = request. getParameter (usingHours); String evaluate = request. getParameter (evaluate); String establishNumber = request. getParameter (establishNumber); String actualNumber = request. getParameter (actualNumber); // save them to an array format // This is the excel header String [] col = {typeId, typeName, buildingArea, ability, personTime, usingHours, evaluate, establishNumber, actualNumber}; String [] zd = col; // This is the field to be exported // The above class is used below, and the object parameter ExportExcel needs to be passed
 
  
Ee = new ExportExcel
  
   
(); // The last parameter is your data set. I will query it and fill it with ee directly. exportExcel (lab type, col, zd, this. laboratoryService. queryAllLab ());}