简单实现POI操作Excel生成数据透视图
需求
财务部门需要做一自动导出数据报表的功能,其中要有指定格式的数据透视图(柱状图、饼状图等等)
思考
正常来讲,工作三年多以来,POI导出导入数据表用的写的确实不少了(合并单元格、改背景色我还是会的),但是要用POI导出数据透视图(不是跟财务打过交道我连透视图是啥都搞不清楚)需求还是第一次遇见。遇事不决问百度,慢慢来不急。
尝试
知己知彼。想用POI导出数据透视图,先试着用excel自己做个数据透视图试试。耗时半小时,加上度娘的谆谆诱导,终于明白了一些做透视图的诀窍,行列区分啥的(又学新东西了,舒服)。
之后就是百度用POI实现了,网上大神确实写的不少
其中 这位大佬的博客实在是良心
链接: 使用POI在Excel中动态生成图表工具类(支持柱状、组合、环状图、折线图、等常用图).
直接cv代码执行,嘿,柱状图、饼状图、折线图还真老老实实出来了!
接下来就是扒代码了(半小时,我放弃了,发现平时对这一块了解少,看不太懂。。。)
继续逛gai ,之后也是这位大佬的另一篇链接: poi在Word中生成各种图表(支持柱状、组合、环状图、折线图、等常用图). 通过在word模板文档中添加占位符,读取模板替换占位符的方式实现word文档中导入图表,想着也可以用这种方案试着写excel模板替换。
实现
1、先做一个excel生成数据透视图的模板,先模拟数据生成一次
看下是否达到预期效果(调整图表名啥的)
2、替换数据对应的单元格为特殊标识,保证不要重复就行
3、代码测试
// An highlighted block
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Set;
/**
* @ClassName MyTest
* 描述 :
* @Date 2020/6/11 17:32
*/
public class MyTest {
public static void main(String args[]) throws Exception{
String s = "{\n" +
"\t\"#a1\": \"月份\",\n" +
"\t\"#a2\": \"1月\",\n" +
"\t\"#a3\": \"2月\",\n" +
"\t\"#a4\": \"3月\",\n" +
"\t\"#a5\": \"4月\",\n" +
"\t\"#a6\": \"5月\",\n" +
"\t\"#b1\": \"销售数据\",\n" +
"\t\"#b2\": \"210\",\n" +
"\t\"#b3\": \"220\",\n" +
"\t\"#b4\": \"230\",\n" +
"\t\"#b5\": \"240\",\n" +
"\t\"#b6\": \"250\",\n" +
"\t\"#c1\": \"销售金额\",\n" +
"\t\"#c2\": \"2312\",\n" +
"\t\"#c3\": \"2145\",\n" +
"\t\"#c4\": \"5431\",\n" +
"\t\"#c5\": \"2234\",\n" +
"\t\"#c6\": \"1111\",\n" +
"\t\"#d1\": \"投入人员\",\n" +
"\t\"#d2\": \"4\",\n" +
"\t\"#d3\": \"7\",\n" +
"\t\"#d4\": \"9\",\n" +
"\t\"#d5\": \"10\",\n" +
"\t\"#d6\": \"22\",\n" +
"}";
JSONObject jsonObject = JSONObject.parseObject(s);
Set<String> kays = jsonObject.keySet();
//读取excel提前制定的excel模板
// File file = new File("D:\\excel\\ccc.xlsx");
FileInputStream is = new FileInputStream("D:\\excel\\ccc.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(is);
FileOutputStream out = new FileOutputStream("D:\\excel\\ddd.xlsx");
try {
XSSFSheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
for(int i = 0;i<=lastRowNum;i++){
XSSFRow row = sheet.getRow(i);
for(int j = 0;j<4;j++){
XSSFCell cell = row.getCell(j);
String z = cell.getStringCellValue();
if(null != z ){
if(kays.contains(z)){
if(z.contains("1") || z.contains("a")){
String d = jsonObject.getString(z);
cell.setCellValue(d);
}else{
Integer d = jsonObject.getInteger(z);
cell.setCellValue(d);
}
}else{
row.removeCell(cell);
}
}
}
}
workbook.write(out);
}catch (Exception e){
e.printStackTrace();
}finally {
out.flush();
is.close();
out.close();
}
}
}
4、生成的效果还好,基本上跟自己想的差不多。数据内容要确认好内容格式,行列标题String格式,数据内容数值格式,不然数据透视表无法生成