---------------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();
-----------------------------------------------------
發這個貼是因為想有人能指出我的不足。或者是這個工具能提供給有需要的人
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/276837.html
標籤：Java相關