自己做了个导出成excel文件工具望指导

[qq_36894609]

---------------XSSFWorkbook工具。 方法为public是考虑到能被继承进而重写------------
import com.alibaba.fastjson.JSONArray;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.Base64;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSONObject;

import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

public class XSSWorkbookUtil {

/**
* 下一行数 。 当前行数 = row-1
*/
int row = 0;

/**
* 文件名称
*/
String fileName = "导出数据.xlsx";

XSSFWorkbook workbook = null;

public XSSWorkbookUtil(String fileName) {
this.fileName = fileName;
workbook = new XSSFWorkbook();
}

public XSSWorkbookUtil() {
workbook = new XSSFWorkbook();
}

/**
* 通用样式
*
*/
public XSSFCellStyle createBorderStyle() {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
return cellStyle;
}

/**
* 标题样式
*
*/
public XSSFCellStyle createTitleStyle() {
XSSFCellStyle cellStyle = createBorderStyle();
XSSFFont font = workbook.createFont();
font.setBold(true);
font.setFontName("宋体");
font.setFontHeight((short) 300);
cellStyle.setFont(font);
return cellStyle;
}

/**
* 创建自增长行
*
* @param sheet - 页
*/
public XSSFRow createrow(XSSFSheet sheet) {
XSSFRow res = sheet.createRow(row);
row++;
return res;
}

/**
* 创建指定样式的列
*
* @param res - 第几行
* @param cellStyle - 样式
* @param columnIndex - 第几列
*/
public XSSFCell createCel(XSSFRow res, CellStyle cellStyle, int columnIndex) {
XSSFCell cel = res.createCell(columnIndex);
cel.setCellStyle(cellStyle);
return cel;
}

/**
* 创建合并样式标题
*
* @param sheet - 页
* @param cellStyle - 标题样式
* @param columnNum - 合并行数
*/
public XSSFCell createTitleRow(XSSFSheet sheet, CellStyle cellStyle, int columnNum) {
// 合并单元格 参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
XSSFRow res = createrow(sheet);
sheet.addMergedRegion(new CellRangeAddress(row - 1, row - 1, 0, columnNum));
XSSFCell cel = null;
for (int i = 0; i < columnNum + 1; i++) {
XSSFCell tempcel = res.createCell(i);
tempcel.setCellStyle(cellStyle);
if (i == 0)
cel = tempcel;
}
return cel;
}

/**
* 创建工作表
*
* @param list 工作表数据
* @param sheetName 工作表名称
* @return
*/
public Boolean setSheetData(JSONArray list, String sheetName) {
if (null == list || list.size() == 0)
return false;
row = 0;
// 样式
XSSFCellStyle cellBorderStyle = createBorderStyle();
XSSFSheet sheet = null;
if (sheetName == null || sheetName.isEmpty()) {
sheet = workbook.createSheet();
} else {
sheet = workbook.createSheet(sheetName);
}
XSSFRow temprow = createrow(sheet);
Object[] cols = null;
for (int i = 0; i < list.size(); i++) {
JSONObject checkSchool = (JSONObject) list.get(i);
if (i == 0) {
cols = checkSchool.keySet().toArray();
createCel(temprow, cellBorderStyle, 0).setCellValue("序号");
for (int j = 0; j < cols.length; j++) {
createCel(temprow, cellBorderStyle, j + 1).setCellValue(cols[j].toString());
}
}
temprow = createrow(sheet);
createCel(temprow, cellBorderStyle, 0).setCellValue(i + 1);
for (int j = 0; j < cols.length; j++) {
Object colData = checkSchool.get(cols[j].toString());
createCel(temprow, cellBorderStyle, j + 1).setCellValue(colData == null ? "" : colData.toString());
}
}
return true;
}

public Boolean exportData() {
ServletOutputStream outputStream = null;
try {
// 导出
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes())
.getRequest();
String agent = request.getHeader("User-Agent");
if (agent != null && agent.contains("Firefox")) {
fileName = "=?utf-8?B?" + Base64.getEncoder().encodeToString(fileName.getBytes("utf-8")) + "?=";
} else {
fileName = URLEncoder.encode(fileName, "UTF-8");
}
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes())
.getResponse();
response.setCharacterEncoding("utf-8");
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
outputStream = response.getOutputStream();
workbook.write(outputStream);
return true;
} catch (IOException e) {
e.printStackTrace();
return false;
} finally {
// 关闭
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

-----------------分割线 调用示例------------------
JSONArray jsonArray = new JSONArray();
JSONObject obj = new JSONObject();
obj.put("columnName", “columnValue”);
....
obj.put("columnNameN", “columnValuN”);
jsonArray.add(obj);
XSSWorkbookUtil xssWorkbookUtil = new XSSWorkbookUtil(fileName);
xssWorkbookUtil.setSheetData(jsonArray , sheetName);
xssWorkbookUtil.exportData();

-----------------------------------------------------
发这个贴是因为想有人能指出我的不足。或者是这个工具能提供给有需要的人

[biankobe24]

没啥大问题，但是Excel这个轮子太多了，而且性能都挺不错
xls response.setContentType("application/vnd.ms-excel");
xlsx response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");