導(dǎo)出excel是Java開發(fā)的必備技能，之前項(xiàng)目有這個功能，現(xiàn)在將其獨(dú)立出來，分享一下。

創(chuàng)新互聯(lián)建站專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站建設(shè)、成都網(wǎng)站制作、湖北網(wǎng)絡(luò)推廣、小程序開發(fā)、湖北網(wǎng)絡(luò)營銷、湖北企業(yè)策劃、湖北品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)建站為所有大學(xué)生創(chuàng)業(yè)者提供湖北建站搭建服務(wù)，24小時服務(wù)熱線：13518219792，官方網(wǎng)址：chinadenli.net

所用技術(shù)就是SpringBoot，然后是MVC架構(gòu)模式。

廢話不多說，直接上代碼了，源碼點(diǎn)末尾鏈接就可以下載。

(1)新建一個SpringBoot項(xiàng)目，然后導(dǎo)入eclipse)，項(xiàng)目結(jié)構(gòu)如下：

(2)修改pom文件，添加依賴;

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-test

test

org.apache.poi

poi

3.14

org.apache.poi

poi-ooxml

3.14

org.apache.poi

poi-contrib

3.6

org.apache.poi

poi-ooxml-schemas

3.17

org.apache.poi

poi-scratchpad

3.17

(3)新建一個實(shí)體類，命名為User.java;

package com.twf.springcloud.ExportExcel.po;

import java.io.Serializable;

public class User implements Serializable{

private static final long serialVersionUID = -9180229310895087286L;

private String name; // 姓名

private String sex; // 性別

private Integer age; // 年齡

private String phoneNo; // 手機(jī)號

private String address; // 地址

private String hobby; // 愛好

public User(String name, String sex, Integer age, String phoneNo, String address, String hobby) {

super();

this.name = name;

this.sex = sex;

this.age = age;

this.phoneNo = phoneNo;

this.address = address;

this.hobby = hobby;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getSex() {

return sex;

}

public void setSex(String sex) {

this.sex = sex;

}

public Integer getAge() {

return age;

}

public void setAge(Integer age) {

this.age = age;

}

public String getPhoneNo() {

return phoneNo;

}

public void setPhoneNo(String phoneNo) {

this.phoneNo = phoneNo;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public String getHobby() {

return hobby;

}

public void setHobby(String hobby) {

this.hobby = hobby;

}

@Override

public String toString() {

return "User [name=" + name + ", sex=" + sex + ", age=" + age + ", phoneNo=" + phoneNo + ", address=" + address

+ ", hobby=" + hobby + "]";

}

}

(4)新建一個excel樣式工具類;

package com.twf.springcloud.ExportExcel.utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.xssf.streaming.SXSSFCell;

import org.apache.poi.xssf.streaming.SXSSFRow;

import org.apache.poi.xssf.streaming.SXSSFSheet;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**

* excle樣式工具類

*/

public class ExcelFormatUtil {

/**

* 設(shè)置報表頭樣式

* @param workbook

* @return

*/

public static CellStyle headSytle(SXSSFWorkbook workbook){

// 設(shè)置style1的樣式，此樣式運(yùn)用在第二行

CellStyle style1 = workbook.createCellStyle();// cell樣式

// 設(shè)置單元格背景色，設(shè)置單元格背景色以下兩句必須同時設(shè)置

style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 設(shè)置填充樣式

style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 設(shè)置填充色

// 設(shè)置單元格上、下、左、右的邊框線

style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style1.setBorderRight(HSSFCellStyle.BORDER_THIN);

style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

Font font1 = workbook.createFont();// 創(chuàng)建一個字體對象

font1.setBoldweight((short) 10);// 設(shè)置字體的寬度

font1.setFontHeightInPoints((short) 10);// 設(shè)置字體的高度

font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗體顯示

style1.setFont(font1);// 設(shè)置style1的字體

style1.setWrapText(true);// 設(shè)置自動換行

style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 設(shè)置單元格字體顯示居中(左右方向)

style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 設(shè)置單元格字體顯示居中(上下方向)

return style1;

}

/**

* 設(shè)置報表體樣式

* @param wb

* @return

*/

public static CellStyle contentStyle(SXSSFWorkbook wb){

// 設(shè)置style1的樣式，此樣式運(yùn)用在第二行

CellStyle style1 = wb.createCellStyle();// cell樣式

// 設(shè)置單元格上、下、左、右的邊框線

style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style1.setBorderRight(HSSFCellStyle.BORDER_THIN);

style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

style1.setWrapText(true);// 設(shè)置自動換行

style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 設(shè)置單元格字體顯示居中(左右方向)

style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 設(shè)置單元格字體顯示居中(上下方向)

return style1;

}

/**

* 設(shè)置報表標(biāo)題樣式

* @param workbook

* @return

*/

public static HSSFCellStyle titleSytle(HSSFWorkbook workbook,short color,short fontSize){

// 設(shè)置style1的樣式，此樣式運(yùn)用在第二行

HSSFCellStyle style1 = workbook.createCellStyle();// cell樣式

// 設(shè)置單元格背景色，設(shè)置單元格背景色以下兩句必須同時設(shè)置

//style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 設(shè)置填充樣式

//short fcolor = color;

if(color != HSSFColor.WHITE.index){

style1.setFillForegroundColor(color);// 設(shè)置填充色

}

// 設(shè)置單元格上、下、左、右的邊框線

style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style1.setBorderRight(HSSFCellStyle.BORDER_THIN);

style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

HSSFFont font1 = workbook.createFont();// 創(chuàng)建一個字體對象

font1.setBoldweight(fontSize);// 設(shè)置字體的寬度

font1.setFontHeightInPoints(fontSize);// 設(shè)置字體的高度

font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗體顯示

style1.setFont(font1);// 設(shè)置style1的字體

style1.setWrapText(true);// 設(shè)置自動換行

style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 設(shè)置單元格字體顯示居中(左右方向)

style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 設(shè)置單元格字體顯示居中(上下方向)

return style1;

}

/**

*設(shè)置表頭

* @param sheet

*/

public static void initTitleEX(SXSSFSheet sheet, CellStyle header,String title[],int titleLength[]) {

SXSSFRow row0 = sheet.createRow(0);

row0.setHeight((short) 800);

for(int j = 0;j

SXSSFCell cell = row0.createCell(j);

//設(shè)置每一列的字段名

cell.setCellValue(title[j]);

cell.setCellStyle(header);

sheet.setColumnWidth(j, titleLength[j]);

}

}

}

(5)新建一個Service接口;

package com.twf.springcloud.ExportExcel.sevice;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.springframework.http.ResponseEntity;

public interface ExportService {

ResponseEntity exportExcel(HttpServletRequest request, HttpServletResponse response);

}

(6)新建一個Service接口實(shí)現(xiàn)類;

package com.twf.springcloud.ExportExcel.sevice.impl;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.xssf.streaming.SXSSFCell;

import org.apache.poi.xssf.streaming.SXSSFRow;

import org.apache.poi.xssf.streaming.SXSSFSheet;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Service;

import com.twf.springcloud.ExportExcel.controller.BaseFrontController;

import com.twf.springcloud.ExportExcel.po.User;

import com.twf.springcloud.ExportExcel.sevice.ExportService;

import com.twf.springcloud.ExportExcel.utils.ExcelFormatUtil;

@Service

public class ExportServiceImpl implements ExportService{

Logger logger = LoggerFactory.getLogger(ExportServiceImpl.class);

@Override

public ResponseEntity exportExcel(HttpServletRequest request, HttpServletResponse response) {

try {

logger.info(">>>>>>>>>>開始導(dǎo)出excel>>>>>>>>>>");

// 造幾條數(shù)據(jù)

List list = new ArrayList<>();

list.add(new User("唐三藏", "男", 30, "13411111111", "東土大唐", "取西經(jīng)"));

list.add(new User("孫悟空", "男", 29, "13411111112", "菩提院", "打妖怪"));

list.add(new User("豬八戒", "男", 28, "13411111113", "高老莊", "偷懶"));

list.add(new User("沙悟凈", "男", 27, "13411111114", "流沙河", "挑擔(dān)子"));

BaseFrontController baseFrontController = new BaseFrontController();

return baseFrontController.buildResponseEntity(export((List) list), "用戶表.xls");

} catch (Exception e) {

e.printStackTrace();

logger.error(">>>>>>>>>>導(dǎo)出excel 異常，原因?yàn)椋? + e.getMessage());

}

return null;

}

private InputStream export(List list) {

logger.info(">>>>>>>>>>>>>>>>>>>>開始進(jìn)入導(dǎo)出方法>>>>>>>>>>");

ByteArrayOutputStream output = null;

InputStream inputStream1 = null;

SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 保留1000條數(shù)據(jù)在內(nèi)存中

SXSSFSheet sheet = wb.createSheet();

// 設(shè)置報表頭樣式

CellStyle header = ExcelFormatUtil.headSytle(wb);// cell樣式

CellStyle content = ExcelFormatUtil.contentStyle(wb);// 報表體樣式

// 每一列字段名

String[] strs = new String[] { "姓名", "性別", "年齡", "手機(jī)號", "地址","愛好" };

// 字段名所在表格的寬度

int[] ints = new int[] { 5000, 5000, 5000, 5000, 5000, 5000 };

// 設(shè)置表頭樣式

ExcelFormatUtil.initTitleEX(sheet, header, strs, ints);

logger.info(">>>>>>>>>>>>>>>>>>>>表頭樣式設(shè)置完成>>>>>>>>>>");

if (list != null && list.size() > 0) {

logger.info(">>>>>>>>>>>>>>>>>>>>開始遍歷數(shù)據(jù)組裝單元格內(nèi)容>>>>>>>>>>");

for (int i = 0; i < list.size(); i++) {

User user = list.get(i);

SXSSFRow row = sheet.createRow(i + 1);

int j = 0;

SXSSFCell cell = row.createCell(j++);

cell.setCellValue(user.getName()); // 姓名

cell.setCellStyle(content);

cell = row.createCell(j++);

cell.setCellValue(user.getSex()); // 性別

cell.setCellStyle(content);

cell = row.createCell(j++);

cell.setCellValue(user.getAge()); // 年齡

cell.setCellStyle(content);

cell = row.createCell(j++);

cell.setCellValue(user.getPhoneNo()); // 手機(jī)號

cell.setCellStyle(content);

cell = row.createCell(j++);

cell.setCellValue(user.getAddress()); // 地址

cell.setCellStyle(content);

cell = row.createCell(j++);

cell.setCellValue(user.getHobby()); // 愛好

cell.setCellStyle(content);

}

logger.info(">>>>>>>>>>>>>>>>>>>>結(jié)束遍歷數(shù)據(jù)組裝單元格內(nèi)容>>>>>>>>>>");

}無錫婦科費(fèi)用 http://www.wxbhnkyy39.com/

try {

output = new ByteArrayOutputStream();

wb.write(output);

inputStream1 = new ByteArrayInputStream(output.toByteArray());

output.flush();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

if (output != null) {

output.close();

if (inputStream1 != null)

inputStream1.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

return inputStream1;

}

}

(7)新建一個下載文件的通用controller;

package com.twf.springcloud.ExportExcel.controller;

import java.io.InputStream;

import java.net.URLEncoder;

import java.util.HashMap;

import java.util.Map;

import org.apache.poi.util.IOUtils;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.http.HttpHeaders;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.util.StringUtils;

import org.springframework.validation.annotation.Validated;

@Validated

public class BaseFrontController {

/**

* slf4j 日志 logger

*/

protected final Logger logger = LoggerFactory.getLogger(this.getClass());

/**

* 下載文件,純SpringMVC的API來完成

*

* @param is 文件輸入流

* @param name 文件名稱,帶后綴名

*

* @throws Exception

*/

public ResponseEntity buildResponseEntity(InputStream is, String name) throws Exception {

logger.info(">>>>>>>>>>>>>>>>>>>>開始下載文件>>>>>>>>>>");

if (this.logger.isDebugEnabled())

this.logger.debug("download: " + name);

HttpHeaders header = new HttpHeaders();

String fileSuffix = name.substring(name.lastIndexOf('.') + 1);

fileSuffix = fileSuffix.toLowerCase();

Map arguments = new HashMap();

arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

arguments.put("xls", "application/vnd.ms-excel");

String contentType = arguments.get(fileSuffix);

header.add("Content-Type", (StringUtils.hasText(contentType) ? contentType : "application/x-download"));

if(is!=null && is.available()!=0){

header.add("Content-Length", String.valueOf(is.available()));

header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));

byte[] bs = IOUtils.toByteArray(is);

logger.info(">>>>>>>>>>>>>>>>>>>>結(jié)束下載文件-有記錄>>>>>>>>>>");

logger.info(">>>>>>>>>>結(jié)束導(dǎo)出excel>>>>>>>>>>");

return new ResponseEntity<>(bs, header, HttpStatus.OK);

}else{

String string="數(shù)據(jù)為空";

header.add("Content-Length", "0");

header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));

logger.info(">>>>>>>>>>>>>>>>>>>>結(jié)束下載文件-無記錄>>>>>>>>>>");

logger.info(">>>>>>>>>>結(jié)束導(dǎo)出excel>>>>>>>>>>");

return new ResponseEntity<>(string.getBytes(), header, HttpStatus.OK);

}

}

}

(8)新建一個controller，作為請求的入口;

package com.twf.springcloud.ExportExcel.controller;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.twf.springcloud.ExportExcel.sevice.ExportService;

@RestController

@RequestMapping("/exportExcel/")

public class ExportController {

@Autowired

private ExportService exportService;

// 導(dǎo)出excel

@RequestMapping("exportExcel")

public ResponseEntity exportExcel(HttpServletRequest request, HttpServletResponse response) {

return exportService.exportExcel(request,response);

}

}

(9)運(yùn)行ExportExcelApplication，可以下載excel，打開，結(jié)束

分享題目：Java導(dǎo)出Excel實(shí)例操作
鏈接URL：http://chinadenli.net/article38/pigjpp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供、企業(yè)建站、響應(yīng)式網(wǎng)站、做網(wǎng)站、Google、企業(yè)網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)