Java之使用poi导出excel文件及文件格式设置为文本

使用 SXSSFWorkbook 进行Excel导出下载

注意：该方法是将Excel中指定列的单元格格式设置为文本格式

1.excel模版导出工具类

package com.qzlink.util.excel;

import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Record;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.base.Joiner;

public class ExcelUtil {

    private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
    
    public static void exportExcel(Controller controller, String[] headers, String[] columns, String fileName, List<Record> dataList) {

        HttpServletResponse response = controller.getResponse();
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
        response.setContentType("application/msexcel;charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        OutputStream out = null;
        try{
            out = response.getOutputStream();
            // 创建工作簿
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
            CellStyle cellStyle = sxssfWorkbook.createCellStyle();
            DataFormat dataFormat = sxssfWorkbook.createDataFormat();
            cellStyle.setDataFormat(dataFormat.getFormat("@")); // 设置为文本格式

            // 创建工作表
            SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet1");
            // 判断header是否为空
            if (null == headers || headers.length <= 0) {
                // 写入文件
                sxssfWorkbook.write(out);
                return;
            }
            // 判断columns是否为空
            if (null == columns || columns.length <= 0 || null == dataList || dataList.size() <= 0) {
                // 设置head
                SXSSFRow sheetRow = sheet.createRow(0);
                for (int column=0; column<headers.length; column++) {
                    sheetRow.createCell(column).setCellValue(headers[column]);
                    sheet.setDefaultColumnStyle(column,cellStyle); // 设置为文本格式，只对设置列有用
                }
                // 写入文件
                sxssfWorkbook.write(out);
                return;
            }
            // 设置数据
            for (int row=0; row<=dataList.size(); row++) {

                SXSSFRow sheetRow = sheet.createRow(row);
                if (row == 0) { // 设置head
                    for (int column=0; column<headers.length; column++) {
                        sheetRow.createCell(column).setCellValue(headers[column]);
                        sheet.setDefaultColumnStyle(column,cellStyle); // 设置为文本格式，只对设置列有用
                    }
                } else { // 设置数据
                    for (int column=0; column<headers.length; column++) {
                        sheetRow.createCell(column).setCellValue(
                                dataList.get(row-1).getStr(columns[column]) == null ? "" : dataList.get(row-1).getStr(columns[column]));
                        sheet.setDefaultColumnStyle(column,cellStyle); // 设置为文本格式，只对设置列有用
                    }
                }
            }
            // 写入文件
            sxssfWorkbook.write(out);
        } catch (Exception e) {
            log.error("ExcelUtil exportExcel exception catch : " + e);
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
                controller.renderNull();
            } catch (IOException e) {
                log.error("ExcelUtil exportExcel exception finally catch : " + e);
            }
        }

    }
}

2.Controller 类调用方法

/**
 * 导出模板文件
 */
public void downloadTemplateFile() {
    String taskId = getPara("taskId", "0");
    Record taskRecord = voiceTaskSer.queryEntityById(Integer.parseInt(taskId));
    List<VarInfoSettingVo> varInfoSettings = taskContactSer.getTableHead(taskRecord);

    List<String> headers = new ArrayList<>();
    headers.add("电话号码");
    headers.add("联系人");
    if (null != varInfoSettings && varInfoSettings.size() > 0) {
        for (VarInfoSettingVo vo : varInfoSettings) {
            headers.add(vo.getCustomizeName());
        }
    }
    String fileName = "template_" + DateUtil.getCurrDate(DateUtil.FORMAT_THREE) + ".xlsx";
    ExcelUtil.exportExcel(this, headers.toArray(new String[headers.size()]), null, fileName, null);
}