前言:
如今兄弟们对“java在excel动态生成图表”大体比较珍视,小伙伴们都想要学习一些“java在excel动态生成图表”的相关知识。那么小编在网上汇集了一些关于“java在excel动态生成图表””的相关内容,希望姐妹们能喜欢,朋友们一起来学习一下吧!POI简介
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
开发工具及环境
开发工具:Idea
JAVA版本:jdk1.8.0_241
IDE插件:lombok
Maven 依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version></dependency>准备工作1.创建ExcelHeader类,该类的作用是可以自定义excel列头名称
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructor@Builderpublic class ExcelHeader { private String fieldName; private String columnName;}2.创建Excel工具类
import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.servlet.http.HttpServletResponse;import java.beans.PropertyDescriptor;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Method;import java.util.Arrays;import java.util.Iterator;import java.util.List;import java.util.stream.Collectors;public class ReflectExcelWriter<T> { private List<ExcelHeader> headers; private Class<T> clazz; public ReflectExcelWriter(List<ExcelHeader> headers, Class clazz) { this.headers = headers; this.clazz = clazz; } public ReflectExcelWriter(Class clazz) { this.clazz = clazz; this.initHeaders(); } private ReflectExcelWriter() { } private void initHeaders() { this.headers = Arrays.asList(this.clazz.getDeclaredFields()).stream().map(field -> ExcelHeader.builder().fieldName(field.getName()).columnName(field.getName()).build()).collect(Collectors.toList()); } public void writeFile(String fileName, List<T> dataList) { FileOutputStream fileOut = null; Workbook workbook = exportData(dataList); try { File file = new File(fileName); if (!file.exists()) { file.createNewFile(); } fileOut = new FileOutputStream(file); workbook.write(fileOut); } catch (Exception e) { e.printStackTrace(); } finally { try { if (null != fileOut) fileOut.close(); if (null != workbook) workbook.close(); } catch (Exception e) { e.printStackTrace(); } } } public void downFile(List<T> dataList, HttpServletResponse response) throws IOException { response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.addHeader("Content-Disposition", "attachment;fileName=response.xlsx");// 设置文件名 OutputStream out = response.getOutputStream(); Workbook workbook = exportData(dataList); try { workbook.write(out); out.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (null != workbook) workbook.close(); } catch (Exception e) { e.printStackTrace(); } } } /** * 生成Excel并写入数据信息 * * @param dataList 数据列表 * @return 写入数据后的工作簿对象 */ public Workbook exportData(List<T> dataList) { // 生成xlsx的Excel Workbook workbook = new SXSSFWorkbook(); // 生成Sheet表,写入第一行的列头 Sheet sheet = buildDataSheet(workbook); //构建每行的数据内容 int rowNum = 1; for (Iterator<T> it = dataList.iterator(); it.hasNext(); ) { T data = it.next(); if (data == null) { continue; } //输出行数据 Row row = sheet.createRow(rowNum++); try { convertDataToRow(data, row); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } } return workbook; } /** * 生成sheet表,并写入第一行数据(列头) * * @param workbook 工作簿对象 * @return 已经写入列头的Sheet */ private Sheet buildDataSheet(Workbook workbook) { Sheet sheet = workbook.createSheet(); // 设置列头宽度 for (int i = 0; i < headers.size(); i++) { sheet.setColumnWidth(i, 4000); } // 设置默认行高 sheet.setDefaultRowHeight((short) 400); // 写入第一行各列的数据 Row head = sheet.createRow(0); for (int i = 0; i < headers.size(); i++) { Cell cell = head.createCell(i); cell.setCellValue(headers.get(i).getColumnName()); } return sheet; } /** * 将数据转换成行 * * @param t 源数据 * @param row 行对象 * @return */ private void convertDataToRow(T t, Row row) throws IllegalAccessException, InstantiationException { int index = 0; try { for (ExcelHeader header : headers) { Cell cell = row.createCell(index++); PropertyDescriptor descriptor = new PropertyDescriptor(header.getFieldName(), clazz); Method getMethod = descriptor.getReadMethod(); if (getMethod != null) { Object result = getMethod.invoke(t); cell.setCellValue(null == result ? "" : result.toString()); } } } catch (Exception e) { e.printStackTrace(); } }}3.创建实体类
@Data @AllArgsConstructor @NoArgsConstructor public static class User{ private String name; private String phone; private String birthdate; private String email; }4.测试类默认列头
public class Test { public static void main(String[] args) { List<User> users = Arrays.asList( new User("Eric", "13900000000", "1999-02-10", "Eric@163.com"), new User("Alice", "18600000000", "2002-03-21", "Alice@163.com"), new User("Jack", "13800000000", "1972-12-11", "Jack@163.com"), new User("Bob", "15500000000", "1986-06-15", "Bob@163.com")); String output = "learning/data/toutiao/user.xlsx"; ReflectExcelWriter<User> writer = new ReflectExcelWriter<>(User.class); writer.writeFile(output, users); }}
效果图
自定义列头
public class Test { public static void main(String[] args) { List<User> users = Arrays.asList( new User("Eric", "13900000000", "1999-02-10", "Eric@163.com"), new User("Alice", "18600000000", "2002-03-21", "Alice@163.com"), new User("Jack", "13800000000", "1972-12-11", "Jack@163.com"), new User("Bob", "15500000000", "1986-06-15", "Bob@163.com")); List<ExcelHeader> headers = Arrays.asList( ExcelHeader.builder().fieldName("name").columnName("名字").build(), ExcelHeader.builder().fieldName("phone").columnName("联系电话").build(), ExcelHeader.builder().fieldName("birthdate").columnName("生日").build(), ExcelHeader.builder().fieldName("email").columnName("邮箱").build() ); String output = "learning/data/toutiao/user.xlsx" ReflectExcelWriter<User> writer = new ReflectExcelWriter<>(headers, User.class); writer.writeFile(output, users); }}
效果图
版权声明:
本站文章均来自互联网搜集,如有侵犯您的权益,请联系我们删除,谢谢。
标签: #java在excel动态生成图表