使用 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);
}