POI導出excel

1.導入maven依賴

? ? ? ?<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.1</version></dependency>

2.工具類

ExcelEntity.java

/*** 代表要打印的Excel表格,用于存放要導出為Excel的相關數(shù)據(jù)** @param <T> 代表要打印的數(shù)據(jù)實體，如User等* @author zhrb@cec.jmu*/ public class ExcelEntity<T> {private String sheetName;//默認生成的sheet名稱private String header;//題頭private String footer;//腳注//底下是必須具備的屬性private String fileName;private String[] columnNames;//列名private String[] methodNames;//與列名對應的方法名private List<T> entities;//數(shù)據(jù)實體 ? ?public ExcelEntity(String fileName, String[] columnNames, String[] methodNames, List<T> entities) {this("sheet1", "", "", fileName, columnNames, methodNames, entities);} ?public ExcelEntity(String sheetName, String header, String footer, String fileName, String[] columnNames,String[] methodNames, List<T> entities) { ?this.sheetName = sheetName;this.header = header;this.footer = footer;this.fileName = fileName;this.columnNames = columnNames;this.methodNames = methodNames;this.entities = entities;} ?public String getHeader() {return header;} ?public void setHeader(String header) {this.header = header;} ?public String getSheetName() {return sheetName;} ?public void setSheetName(String sheetName) {this.sheetName = sheetName;} ?public List<T> getEntities() {return entities;} ?/*** @param entities 用于導出Excel的實體集合*/public void setEntities(List<T> entities) {this.entities = entities;} ?public String getFooter() {return footer;} ?public void setFooter(String footer) {this.footer = footer;} ?public String[] getColumnNames() {return columnNames;} ?public void setColumnNames(String[] columnNames) {this.columnNames = columnNames;} ?public String getFileName() {return fileName;} ?public void setFileName(String fileName) {this.fileName = fileName;} ?public String[] getMethodNames() {return methodNames;} ?public void setMethodNames(String[] methodNames) {this.methodNames = methodNames;} } ?

ExcelExporter.java

public class ExcelExporter {/*** 根據(jù)ExcelEntity等參數(shù)生成Workbook** @param entity* @return* @throws Exception*/public static <T> Workbook export2Excel(ExcelEntity<T> entity) throws Exception {Workbook workbook = export2Excel(entity.getHeader(), entity.getFooter(), entity.getSheetName(), entity.getColumnNames(), entity.getMethodNames(),entity.getEntities());return workbook;} ? ?/*** 根據(jù)給定參數(shù)導出Excel文檔** @param headerTitle 題頭* @param sheetName* @param columnNames 表頭名稱* @param methodNames* @param entities* @return* @throws Exception*/public static <T> Workbook export2Excel(String headerTitle, String footerTitle, String sheetName, String[] columnNames,String[] methodNames, List<T> entities) throws Exception {if (methodNames.length != columnNames.length) {throw new IllegalArgumentException("methodNames.length should be equal to columnNames.length:"+ columnNames.length + " " + methodNames.length);}Workbook newWorkBook2007 = new XSSFWorkbook();Sheet sheet = newWorkBook2007.createSheet(sheetName);//設置題頭Header header = sheet.getHeader();header.setCenter(headerTitle);//設置腳注Footer footer = sheet.getFooter();footer.setCenter(footerTitle); ?int[] columnWidths = new int[columnNames.length];// 創(chuàng)建表頭createTableHeader(sheet, 0, headerTitle, columnNames, columnWidths);// 填充表內容createTableContent(sheet, 1, methodNames, columnWidths, entities); ?return newWorkBook2007; ?} ?/*** 創(chuàng)建表頭** @param sheet* @param index ? ? ? 表頭開始的行數(shù)* @param headerTitle 題頭* @param columnNames* @param columnWidths*/private static void createTableHeader(Sheet sheet, int index, String headerTitle, String[] columnNames,int[] columnWidths) { ? ?Row headerRow = sheet.createRow(index); ?/* 格式設置 */// 設置字體Font font = sheet.getWorkbook().createFont();font.setBold(true);// 粗體顯示// 設置背景色CellStyle style = sheet.getWorkbook().createCellStyle();style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFont(font); ?for (int i = 0; i < columnNames.length; i++) {Cell headerCell = headerRow.createCell(i);headerCell.setCellStyle(style);headerCell.setCellValue(columnNames[i]);} ?for (int i = 0; i < columnNames.length; i++) {columnWidths[i] = (columnNames[i].getBytes().length + 2) * 256;sheet.setColumnWidth(i, columnWidths[i]);} ?} ?/*** 創(chuàng)建表格內容** @param sheet* @param rowIndexBegin 表內容開始的行數(shù)* @param methodNames ? T對象的方法名* @param columnWidths* @param entities* @throws Exception*/private static <T> void createTableContent(Sheet sheet, int rowIndexBegin, String[] methodNames, int[] columnWidths,List<T> entities) throws Exception {Class<? extends Object> clazz = null;if (entities.size() > 0) {clazz = entities.get(0).getClass();}String content;for (T t : entities) {Row row = sheet.createRow(rowIndexBegin++);for (int i = 0; i < methodNames.length; i++) {Cell cell = row.createCell(i);Method method = clazz.getMethod(methodNames[i], null);Object object = method.invoke(t, null);object = object == null ? "" : object;if (object.getClass().equals(Date.class)) {// 對日期格式進行特殊處理SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");content = sdf.format((Date) object);cell.setCellValue(content);} /*else if (object.getClass().equals(Timestamp.class)){//對時間戳進行特殊處理Date d = new Date();} */ else {content = object.toString();cell.setCellValue(content);}int columnWidth = (content.getBytes().length + 2) * 256;if (columnWidth > columnWidths[i]) {// 如果實際內容寬度大于對應的表頭寬度，則設置為實際內容寬度columnWidths[i] = columnWidth;sheet.setColumnWidth(i, columnWidths[i]);} ?}}} ? ?/*** 將workbook2007存為文件** @param workbook2007* @param dstFile*/public static void saveWorkBook2007(Workbook workbook2007, String dstFile) {File file = new File(dstFile);OutputStream os = null;try {os = new FileOutputStream(file);workbook2007.write(os);} catch (IOException e) {e.printStackTrace();} finally {if (os != null) {try {os.close();} catch (IOException e) {System.out.println(e);}}}} ? }

3.測試

創(chuàng)建測試實體類

Hero.java

public class Hero {private String name;private Integer age; ?public String getName() {return name;} ?public void setName(String name) {this.name = name;} ?public Integer getAge() {return age;} ?public void setAge(Integer age) {this.age = age;} }

Wind.java

public class Wind {private String location;private Integer speed;private Date Timestamp; ?public String getLocation() {return location;} ?public void setLocation(String location) {this.location = location;} ?public Integer getSpeed() {return speed;} ?public void setSpeed(Integer speed) {this.speed = speed;} ?public Date getTimestamp() {return Timestamp;} ?public void setTimestamp(Date timestamp) {Timestamp = timestamp;} } ?

測試導出excel文件

? ?public static void main(String[] args) throws Exception { ?// 準備數(shù)據(jù)List<Wind> winds = new ArrayList<>();// Wind有三個方法:getLocation、getSpeed、getTimestampfor (int i = 0; i < 10; i++) {Wind wind = new Wind();wind.setLocation(i + "");wind.setSpeed(i * 10);wind.setTimestamp(new Date());winds.add(wind);}String[] columnNames = {"地點", "速度", "時間"};String[] methodNames = {"getLocation", "getSpeed", "getTimestamp"}; // String fileName = "d:/temp/excel1.xlsx";String fileName = "/Users/apple/Documents/test.xlsx";// 生成ExcelEntity實體，包含4個必備參數(shù)ExcelEntity<Wind> excelEntity = new ExcelEntity<>(fileName, columnNames, methodNames, winds);//excelEntity.setHeader("題頭");//excelEntity.setFooter("腳注");Workbook excel = ExcelExporter.export2Excel(excelEntity);//ExcelExporter.export2Excel("題頭","腳注", "sheet1", columnNames, methodNames, winds);//也可以這樣調用,無需新建ExcelEntity對象//將Workbook存為文件ExcelExporter.saveWorkBook2007(excel, excelEntity.getFileName()); ?System.out.println("導出完成！");}

生成如下excel文件

?

總結

以上是生活随笔為你收集整理的java使用POI工具类导出excel的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。