Java export excel file


Export online / offline user information of each xmpp machine room to Excel table (timed task + web button), and provide download button on Web page for download.

Effect preview

                                            Export file effects

                                            Click download pop-up effect

Code results overview

/*"...." is the company's business code, which mostly obtains the export data from the cache or database, without affecting the export function. /
I. tool class: generate excel object wb

package com.onewaveinc.utils;

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 com.onewaveinc.mip.log.Logger;
import com.onewaveinc.user.entity.UserInfo;
 * Generate Excel file tool class
 * @author wxin
public class ExcelUtil {

    private static Logger logger = Logger.getInstance(ExcelUtil.class);
     * Export Excel
     * @param sheetName sheet Name
     * @param title Title
     * @param values content
     * @param wb HSSFWorkbook object
     * @return
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,List<UserInfo> valueList, HSSFWorkbook wb){

        // The first step is to create an HSSFWorkbook corresponding to an Excel file
        if(wb == null){
            wb = new HSSFWorkbook();

        // Step 2: add a sheet in the workbook, corresponding to the sheet in the Excel file
        HSSFSheet sheet = wb.createSheet(sheetName);

        // Step 3: add row 0 of the header in the sheet. Note that the old version of poi has restrictions on the number of rows and columns in Excel
        HSSFRow row = sheet.createRow(0);

        // Step 4: create cells and set the value header to set the header Center
        HSSFCellStyle style = wb.createCellStyle();
        // Create a centered format

        //Declare column objects
        HSSFCell cell = null;

        //Create title
        for(int i=0;i<title.length;i++){
            cell = row.createCell((short) i);

        //Create content
        if (null != valueList && valueList.size() > 0) {
            for(int i=0;i<valueList.size();i++){
                row = sheet.createRow(i + 1);
                UserInfo userInfo = valueList.get(i);
                String []userInfoArray = {userInfo.getLoginName(),userInfo.getStbMac(),userInfo.getLoginIp(),
                        userInfo.getServerDomain(), userInfo.getTerminalModel(),userInfo.getTerminalVersion(),
                        userInfo.getServerIp(),    userInfo.getUpdateTime(),userInfo.getLoginTime()};
                for(int j=0;j<userInfoArray.length;j++){
                    //Assign content to corresponding column objects in order
                    row.createCell((short) j).setCellValue(userInfoArray[j]);
        } else {
            logger.error("No data for user information");
        return wb;

II. Method of generating excel file

    public void run() throws InterruptedException, IOException {

     * Regularly export the information of online users in each machine room (a cluster) of XMPP
     * Export information: user account, mac address, login IP, login domain name, model and version of set-top box, and the IP of the node where the login is located,
     * Display the time of login and the time of login (the current time minus the time of login).
    public  String ExportExcel() {
        String result = "";
        try {
            result = ImportDataExcel(offlineUserInfoList, serverName, false);
  "**The offline result of this processing is:"+result);
        } catch (Exception e) {
            result = "failed";
        return result;
     * Export user information data to Excel table
     * @param userInfoList
     * @return msg "failed" or "success"
    public String ImportDataExcel(List<UserInfo> userInfoList, String serverName , boolean isOnline) {
        String msg = "";
        String fileName = "";
        String sheetName = "";
        String[] title = {"User account","mac address","Land IP","Login domain name","Set top box model", "Set top box version",
                "Log in to the IP", "Landing time", "Landing time"};
        //Format date
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        // new Date() to get the current system time, you can also use the current time stamp
        String date = df.format(new Date());
        if (isOnline) {
            fileName = serverName+"-online-usersInfo-"+date+".xls";
            sheetName = serverName+"Online user information form";
        } else {
            fileName = serverName+"-offline-usersInfo-"+date+".xls";
            sheetName = serverName+"Offline user information table";
        HSSFWorkbook wb = new HSSFWorkbook();
        wb = ExcelUtil.getHSSFWorkbook(sheetName, title, userInfoList, null);
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        catch (IOException e){
            msg = "failed";
        byte[] content = os.toByteArray();
        //The location where Excel files are stored after they are generated.
        File file = new File(path+"/"+fileName);
        OutputStream fos  = null;
            fos = new FileOutputStream(file);
            if ("".equals(msg)) {
                msg = "success";
  "Generate user information Excel Form success:"+ fileName);
        catch (Exception e){
            msg = "failed";
            logger.error("Generate user information Excel Form failure:"+ fileName);
        return msg;

III. spring MVC

public class UserChannelLoginService {
    public String ExportExcel() {
        String result = "";
        result = exportXMPPUserInfo.ExportExcel();
        return result;

IV. configuration file

#Export file path: export the online user information Excel table of each machine room of XMPP,

#Scheduled task time: export the online user information Excel table of each machine room of XMPP,
/spring/|xmpp.export.excel.time=0 44,45,46,47 20 11 * ?
<!-- Specify the target class and method to execute -->
    <bean id="autoSmsB2cJobDetail"  class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
             <!-- Specify task class -->
             <property name="targetObject" ref="contbiz.imoss.exportXMPPUserInfo" />
            <!--  Specify task method -->
             <property name="targetMethod" value="run" />
             <property name="concurrent" value="false" />
    <!-- Set the execution task and time -->
     <bean id="autoSmsB2cJobDetailCronTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean">
             <property name="jobDetail">
                    <ref bean="autoSmsB2cJobDetail" />
             <property name="cronExpression">
      <!-- Start timer -->
      <bean id="ssschedulerFactory" class="org.springframework.scheduling.quartz.SchedulerFactoryBean" lazy-init="false">
             <property name="triggers">
                           <!-- <ref bean="autoSmsB2cJobDetailSimpleTrigger" /> -->
                           <ref bean="autoSmsB2cJobDetailCronTrigger" />

Five, front-end

<input id="exportExcel" type="submit" value="export" />
    //Export excel
            if (result == "success") {
                W.alert("Export all online/Offline user succeeded");
            } else {
                W.alert("Export all online/Offline user failed");

Keywords: Java Excel Apache Spring Mac

Added by screamer141 on Sat, 30 Nov 2019 21:51:21 +0200