Introduction to the method of exporting excel files in Java (code example)

This article brings you an introduction to the method of exporting excel files in Java (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. helped.

Requirements

Export the online/offline user information of each xmpp computer room to an Excel table (scheduled task web button) , and provide a download button on the web page for downloading.

Effect preview

Export file effect

Code Overview

/**"..." is the company's business code, which mostly obtains export data from cache or database, and does not affect the export function.

• The front-end writing method is the company framework, just understand the general meaning.

*/
1. 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;
/**
 * 生成Excel文件工具类
 * @author wxin
 *
 */
public class ExcelUtil {

    private static Logger logger = Logger.getInstance(ExcelUtil.class);
    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,List<userinfo> valueList, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length> 0) {
            for(int i=0;i<valuelist.size><p>2. Generate excel file method</p>
<pre class="brush:php;toolbar:false">    public void run() throws InterruptedException, IOException {
         ExportExcel();
   }

    /**
     * 定时导出XMPP每个机房（一个集群）的在线用户的信息
     * 导出信息：用户账号，mac地址，登陆的IP，登陆域名，机顶盒的型号，版本，和以及登陆所在节点的ip，
     * 显示 登陆的时间，登陆的时长（现在的时间减去登陆的时间）。
     */
    public  String ExportExcel() {
        String result = "";
        try {
            ...
            result = ImportDataExcel(offlineUserInfoList, serverName, false);
            logger.info("**此次处理离线结果为："+result);
            ...
            
        } catch (Exception e) {
            result = "failed";
            e.printStackTrace();
        }
        return result;
        
    }
    /**
     * 导出用户信息数据到Excel表格
     * @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 = {"用户账号","mac地址","登陆IP","登陆域名","机顶盒型号", "机顶盒版本",
                "登录所在节点的IP", "登陆时间", "登陆时长"};
        //设置日期格式
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        // new Date()为获取当前系统时间，也可使用当前时间戳
        String date = df.format(new Date());
        if (isOnline) {
            fileName = serverName+"-online-usersInfo-"+date+".xls";
            sheetName = serverName+"在线用户信息表";
        } else {
            fileName = serverName+"-offline-usersInfo-"+date+".xls";
            sheetName = serverName+"离线用户信息表";
        }
        HSSFWorkbook wb = new HSSFWorkbook();
        wb = ExcelUtil.getHSSFWorkbook(sheetName, title, userInfoList, null);
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try{
            wb.write(os);
        }
        catch (IOException e){
            msg = "failed";
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        //Excel文件生成后存储的位置。
        File file = new File(path+"/"+fileName);
        OutputStream fos  = null;
        try{
            fos = new FileOutputStream(file);
            fos.write(content);
            os.close();
            fos.close();
            if ("".equals(msg)) {
                msg = "success";
            }
            logger.info("生成用户信息Excel表格成功:"+ fileName);
        }
        catch (Exception e){
            msg = "failed";
            logger.error("生成用户信息Excel表格失败:"+ fileName);
            e.printStackTrace();
        }
        return msg;
    }</userinfo>

3. SpringMVC

@SuppressWarnings("deprecation")
@Resource("userLoginService")
@Bean("contbiz.imoss.userloginservice")
public class UserChannelLoginService {
...
    @Post
    @Path("exportExcel")
    public String ExportExcel() {
        String result = "";
        result = exportXMPPUserInfo.ExportExcel();
        return result;
    }
...
}

4. Configuration file

#导出文件路径：导出XMPP各个机房的在线用户信息Excel表，
#<require>
/spring/config.properties|xmpp.export.excel.path=D:\Doc\test111

#定时任务时间：导出XMPP各个机房的在线用户信息Excel表，
#<require>
/spring/config.properties|xmpp.export.excel.time=0 44,45,46,47 20 11 * ?</require></require>

<!-- 指定执行的目标类、方法 -->
    <bean>
             <!-- 指定任务类 -->
             <property></property>
            <!--  指定任务方法 -->
             <property></property>
             <property></property>
     </bean>
    <!-- 设置执行任务以及时间 -->
     <bean>
             <property>
                    <ref></ref>
             </property>
             <property>
                    <value>${xmpp.export.excel.time}</value>
             </property>
      </bean>
      <!-- 启动定时器 -->
      <bean>
             <property>
                    <list>
                           <!-- <ref bean="autoSmsB2cJobDetailSimpleTrigger" /> -->
                           <ref></ref>
                    </list>
             </property>
       </bean>

5. Front-end

/**前端写法为公司框架，理解大致意思就好。*/

...
<input>
...
<script>
    //导出excel
    W.$(&#39;exportExcel&#39;).on(&#39;click&#39;,function(e){    
        
        W.create(&#39;userLoginService/exportExcel&#39;).done(function(result){
            if (result == "success") {
                W.alert("导出所有在线/离线用户成功");
            } else {
                W.alert("导出所有在线/离线用户失败");
            }
            
        });
    });
</script>

##

The above is the detailed content of Introduction to the method of exporting excel files in Java (code example). For more information, please follow other related articles on the PHP Chinese website!