Due to project requirements, the data needs to be exported into an Excel table, and the export item can be selected and downloaded. The Spring+Mybatis+SpringMVC framework used by the project is used to export Excel using Apache POI. Please use POI on Baidu. Without further ado, please add the code.
ExportExcelUtil code
package com.rixin.common.util;import java.io.OutputStream;import java.lang.reflect.Method;import java.net.URLEncoder;import java.util.Collection;import java.util.Iterator;import javax.servlet.http.HttpServletResponse;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.apache.poi.ss.usermodel.Font;/** * javaee export Excel tool class based on POI* * @author [email protected] * @see POI */public class ExportExcelUtil { /** * * @param response * Request * @param fileName * File name such as: "Student Table" * @param excelHeader * Excel table header array, storing the "name#name" format string, "name" is the excel header row, and "name" is the object field name* @param dataList * Data collection, it must be consistent with the field name in the table header array, and comply with the javabean specification* @return Return an HSSFWorkbook * @throws Exception */ public static <T> HSSFWorkbook export(HttpServletResponse response, String fileName, String[] excelHeader, Collection<T> dataList) throws Exception { // Set the request response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8")); // Create a Workbook, corresponding to an Excel file HSSFWorkbook wb = new HSSFWorkbook(); // Set the title style HSSFCellStyle titleStyle = wb.createCellStyle(); // Set cell border style titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// Top border thin edge line titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// bottom border thin edge line titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// left border thin edge line titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// right border thin edge line // Set cell alignment titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // Horizontal center titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Vertical center // Set font style Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 15); // Font height titleFont.setFontName("bold"); // Font style titleStyle.setFont(titleFont); // Add a sheet in the Workbook, corresponding to the sheet in the Excel file HSSFSheet sheet = wb.createSheet(fileName); // Title array String[] titleArray = new String[excelHeader.length]; // Field name array String[] fieldArray = new String[excelHeader.length]; for (int i = 0; i < excelHeader.length; i++) { String[] tempArray = excelHeader[i].split("#");// Temporary array split# titleArray[i] = tempArray[0]; fieldArray[i] = tempArray[1]; } // Add title row in sheet HSSFRow row = sheet.createRow((int) 0);// The number of rows starts at 0 HSSFCell sequenceCell = row.createCell(0);// The cell column starts at 0 and add sequence number sequenceCell.setCellValue("serial number"); sequenceCell.setCellStyle(titleStyle); sheet.autoSizeColumn(0);// Automatically set width// Assign value to the title row for (int i = 0; i < titleArray.length; i++) { HSSFCell titleCell = row.createCell(i + 1);// The 0 bit is occupied by the serial number, so +1 titleCell.setCellValue(titleArray[i]); titleCell.setCellStyle(titleStyle); sheet.autoSizeColumn(i + 1);// The 0 bit is occupied by the serial number, so +1 } // The data style needs to be set separately because of the different title and data styles, otherwise it will overwrite HSSFCellStyle dataStyle = wb.createCellStyle(); // Set data border dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // Set the center style dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // Horizontal center dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Vertical center // Set data font Font dataFont = wb.createFont(); dataFont.setFontHeightInPoints((short) 12); // Font height dataFont.setFontName("安"); // Font dataStyle.setFont(dataFont); // Traverse the collection data and generate data row Iterator<T> it = dataList.iterator(); int index = 0; while (it.hasNext()) { index++;// The 0 bit is occupied so +1 row = sheet.createRow(index); // Assign a value to the sequence number HSSFCell sequenceCellValue = row.createCell(0);// The sequence number value is always the 0th column sequenceCellValue.setCellValue(index); sequenceCellValue.setCellStyle(dataStyle); sheet.autoSizeColumn(0); T t = (T) it.next(); // Using reflection, dynamically call the corresponding getXxx() method based on the passed field name array, obtain the attribute value for (int i = 0; i < fieldArray.length; i++) { HSSFCell dataCell = row.createCell(i + 1); dataCell.setCellStyle(dataStyle); sheet.autoSizeColumn(i + 1); String fieldName = fieldArray[i]; String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);// Get the corresponding getXxx() method Class<? extends Object> tCls = t.getClass();// Generics are Object and subclasses of all Objects Method getMethod = tCls.getMethod(getMethodName, new Class[] {});// Get the corresponding method Object value = getMethod.invoke(t, new Object[] {});// Dynamic caller, get the attribute value if (value != null) { dataCell.setCellValue(value.toString());// Assign a value to the current column} } } OutputStream outputStream = response.getOutputStream();// Open stream wb.write(outputStream);// HSSFWorkbook write stream wb.close();// HSSFWorkbook close outputStream.flush();// Refresh stream outputStream.close();// Close stream return wb; } // XSSFCellStyle.ALIGN_CENTER Center Alignment// XSSFCellStyle.ALIGN_LEFT Left Alignment// XSSFCellStyle.ALIGN_RIGHT Right Alignment// XSSFCellStyle.VERTICAL_TOP Alignment// XSSFCellStyle.VERTICAL_CENTER in the middle // XSSFCellStyle.VERTICAL_BOTTOM in the right // CellStyle.BORDER_DOUBLE double-sided line // CellStyle.BORDER_THIN thin edge // CellStyle.BORDER_MEDIUM medium edge // CellStyle.BORDER_DASHED dotted edge // CellStyle.BORDER_HAIR small dot dotted edge // CellStyle.BORDER_THICK thick edge}
Controller code
@RequestMapping("/exportProject.do") public void exportProject(HttpServletResponse response, String export) { String[] excelHeader = export.split(","); List<Project> projectList = projectService.getProjects(null); try { ExportExcelUtil.export(response, "Project Table", excelHeader, projectList); } catch (Exception e) { e.printStackTrace(); } } Part of JSP code
...<div style="margin-top: 5px;"> <span><input type="checkbox" value="Project name#name" name="export" checked="checked"></span> <input type="text" value="Project name" disabled="disabled"></div>...
The picture below
The above is all about this article, I hope it will be helpful to everyone's learning.