apache.poi导出excel功能——不知道类对象的通用导出方法。
目录
工具类 toExcel.java 和main excel导出操作
前言:上一篇用了easypoi的excel通用导出方法,但是boss不同意,白白弄了,要改成apache.poi,因为同事之前已经用了这个jar包,只好重写了。
-
准备
-
JAR
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.json/json -->
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20160810</version>
</dependency>
-
读取resource x.json文件
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Charsets;
import com.google.common.io.Resources;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.net.URL;
import java.util.Enumeration;
import java.util.LinkedHashMap;
import java.util.Map;
/**
* 属性文件获取工具类(仅json)
*
* @author WangZhenkun on 18-8-1
*/
public class PropertiesUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(PropertiesUtils.class);
/*
* 根据json文件名称获取json配置文件数据
*
* @param fileName json文件名称前缀,如果在resource下直接写文件名,如果有路径,请在前面添加路径如:"com/xxx/abc"
*/
public static JSONObject getJsonResource(String fileName) {
fileName += ".json";
ClassLoader classLoader = getClassLoader();
Enumeration<URL> resources;
JSONObject jsonObject = new JSONObject();
try {
resources = classLoader.getResources(fileName);
} catch (IOException e) {
LOGGER.warn("getJsonResource fail {}", fileName, e);
return jsonObject;
}
while (resources.hasMoreElements()) {
URL url = resources.nextElement();
try {
String json = Resources.toString(url, Charsets.UTF_8);
jsonObject.putAll(JSON.parseObject(json)); // 有多个的时候,后面的覆盖前面的
} catch (IOException e) {
LOGGER.warn("addJsonFile fail url:{}", url, e);
}
}
return jsonObject;
}
private static ClassLoader getClassLoader() {
ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
if (classLoader != null) {
return classLoader;
}
return PropertiesUtils.class.getClassLoader();
}
/**
* 私有构造方法,防止工具类被new
*/
private PropertiesUtils() {
throw new IllegalAccessError();
}
public static void main(String[] args) {
Map<String, String> fileList = new LinkedHashMap<>();
JSONObject obj = PropertiesUtils.getJsonResource("excel");
obj = obj.getJSONObject("data");
JSONArray ajaxResultsList = obj.getJSONArray("results");
JSONArray ajaxFieldList = obj.getJSONArray("fieldList");
ajaxFieldList.forEach(o -> {
/*属性*/
String fileName = ((JSONObject) o).get("fieldName").toString();
/*中文名*/
String excelName = ((JSONObject) o).get("name").toString();
fileList.put(excelName, fileName);
});
System.out.println(ajaxResultsList);
System.out.println(fileList);
}
}
-
工具类 toExcel.java 和main excel导出操作
这个是我从****扒下来的,但是修改了一点,我们的json数据不一定都是string形式的,可能是数字等。关于excel的title字体和颜色和背景颜色可自定义
import cn.afterturn.easypoi.xuhy.util.PropertiesUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class toExcel {
/**
* @param workbook
* @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetTitle (sheet的名称)
* @param headers (表格的标题)
* @param result (表格的数据)
* @param out (输出流)
* @throws Exception
* @Title: exportExcel
* @Description: 导出Excel的方法
* @author: evan @ 2014-01-09
*/
public void exportExcel(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, ArrayList<String> headers, List<List<String>> result,
OutputStream out) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式 标题背景颜色自定义
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.RED.index);//excel title 颜色自定义
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.size(); i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers.get(i));
cell.setCellValue(text.toString());
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (List<String> m : result) {
row = sheet.createRow(index);
int cellIndex = 0;
for (Object str : m) {
HSSFCell cell = row.createCell((short) cellIndex);
cell.setCellValue(str + "");
cellIndex++;
}
index++;
}
}
}
public static void main(String[] args) {
ArrayList<String> fileList = new ArrayList<>();
Map<String, Object> fileMap = new LinkedHashMap<>();
/*===================================JSON解析部分===================================*/
JSONObject obj = PropertiesUtils.getJsonResource("excel");
obj = obj.getJSONObject("data");
JSONArray ajaxResultsList = obj.getJSONArray("results");
JSONArray ajaxFieldList = obj.getJSONArray("fieldList");
ajaxFieldList.forEach(o -> {
String isHide = ((JSONObject) o).get("isHide").toString();
if (isHide.equals("0")) {
/*属性*/
String fileName = ((JSONObject) o).get("fieldName").toString();
/*中文名*/
String excelName = ((JSONObject) o).get("name").toString();
/*Json解析按照key value获取对应值*/
fileMap.put(excelName, fileName);
/*excel title 数组值设置*/
fileList.add(excelName);
}
});
/*===================================JSON解析部分===================================*/
try {
OutputStream out = new FileOutputStream("D:\\test" + System.currentTimeMillis() + ".xls");
List<List<String>> data = new ArrayList<List<String>>();
for (int i = 1; i < ajaxResultsList.size(); i++) {
List rowData = new ArrayList();
for (String title : fileMap.keySet()) {
Object column = fileMap.get(title);
Object jsonVal = ((JSONObject) ajaxResultsList.get(i)).get(column);
/*不支持为空或者null*/
jsonVal = (jsonVal == null || jsonVal == "" ? "空" : jsonVal);
rowData.add(jsonVal);
}
data.add(rowData);
}
/*EXCEL TITLE*/
toExcel eeu = new toExcel();
HSSFWorkbook workbook = new HSSFWorkbook();
/*sheet name*/
eeu.exportExcel(workbook, 0, "sheetNameX", fileList, data, out);
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
-
excel导出效果图