java web导出excel表格,Java Web 基于POI导出Excel文件

1.ExportUtil 导出工具类

package com.wangzou.myweb.util;

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

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFFont;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportUtil {

private XSSFWorkbook wb = null;

private XSSFSheet sheet = null;

/**

* @param wb

* @param sheet

*/

public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet)

{

this.wb = wb;

this.sheet = sheet;

}

/**

* 合并单元格后给合并后的单元格加边框

*

* @param region

* @param cs

*/

public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {

int toprowNum = region.getFirstRow();

for (int i = toprowNum; i <= region.getLastRow(); i++) {

XSSFRow row = sheet.getRow(i);

for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {

XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,

// (short) j);

cell.setCellStyle(cs);

}

}

}

/**

* 设置表头的单元格样式

*

* @return

*/

public XSSFCellStyle getHeadStyle() {

// 创建单元格样式

XSSFCellStyle cellStyle = wb.createCellStyle();

// 设置单元格的背景颜色为淡蓝色

cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

// 设置单元格居中对齐

cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

// 设置单元格垂直居中对齐

cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

// 创建单元格内容显示不下时自动换行

cellStyle.setWrapText(true);

// 设置单元格字体样式

XSSFFont font = wb.createFont();

// 设置字体加粗

font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);

font.setFontName("宋体");

font.setFontHeight((short) 200);

cellStyle.setFont(font);

// 设置单元格边框为细线条

cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);

return cellStyle;

}

/**

* 设置表体的单元格样式

*

* @return

*/

public XSSFCellStyle getBodyStyle() {

// 创建单元格样式

XSSFCellStyle cellStyle = wb.createCellStyle();

// 设置单元格居中对齐

cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

// 设置单元格垂直居中对齐

cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

// 创建单元格内容显示不下时自动换行

cellStyle.setWrapText(true);

// 设置单元格字体样式

XSSFFont font = wb.createFont();

// 设置字体加粗

font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);

font.setFontName("宋体");

font.setFontHeight((short) 200);

cellStyle.setFont(font);

// 设置单元格边框为细线条

cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);

return cellStyle;

}

}

2. ExportExcelService 导出Excel文件的业务接口

package com.wangzou.myweb.biz;

import javax.servlet.ServletOutputStream;

public interface ExportExcelService {

public void exportExcel(String hql,String [] titles,

ServletOutputStream outputStream);

}

3.ExportExcelServiceImpl 导出Excel文件的业务实现类

package com.wangzou.myweb.biz.impl;

import java.io.IOException;

import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

import org.springframework.stereotype.Service;

import org.springframework.transaction.annotation.Transactional;

import com.wangzou.myweb.biz.ExportExcelService;

import com.wangzou.myweb.dao.ExportExcelDao;

import com.wangzou.myweb.entity.Product;

import com.wangzou.myweb.util.ExportUtil;

@Service

@Transactional

public class ExportExcelServiceImpl implements ExportExcelService {

@Autowired

private ExportExcelDao eed;

@Override

public void exportExcel(String hql, String[] titles, ServletOutputStream outputStream) {

List list = eed.exportExcel(hql);

// 创建一个workbook 对应一个excel应用文件

XSSFWorkbook workBook = new XSSFWorkbook();

// 在workbook中添加一个sheet,对应Excel文件中的sheet

XSSFSheet sheet = workBook.createSheet("sheet1");

ExportUtil exportUtil = new ExportUtil(workBook, sheet);

XSSFCellStyle headStyle = exportUtil.getHeadStyle();

XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();

// 构建表头

XSSFRow headRow = sheet.createRow(0);

XSSFCell cell = null;

for (int i = 0; i < titles.length; i++) {

cell = headRow.createCell(i);

cell.setCellStyle(headStyle);

cell.setCellValue(titles[i]);

}

// 构建表体数据

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

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

XSSFRow bodyRow = sheet.createRow(j + 1);

Product product = list.get(j);

cell = bodyRow.createCell(0);

cell.setCellStyle(bodyStyle);

cell.setCellValue(product.getName());

cell = bodyRow.createCell(1);

cell.setCellStyle(bodyStyle);

cell.setCellValue(product.getAmount());

cell = bodyRow.createCell(2);

cell.setCellStyle(bodyStyle);

cell.setCellValue(product.getPrice());

}

}

try {

workBook.write(outputStream);

outputStream.flush();

outputStream.close();

} catch (IOException e) {

e.printStackTrace();

} finally {

try {

outputStream.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

}

4.ExportExcelDao 导出Excel文件的持久层接口

package com.wangzou.myweb.dao;

import java.util.List;

import com.wangzou.myweb.entity.Product;

public interface ExportExcelDao {

public List exportExcel(String hql);

}

5.ExportExcelDaoImpl导出Excel文件的持久层实现类

package com.wangzou.myweb.dao.impl;

import java.util.List;

import org.hibernate.SessionFactory;

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

import org.springframework.stereotype.Repository;

import com.wangzou.myweb.dao.ExportExcelDao;

import com.wangzou.myweb.entity.Product;

@Repository

public class ExportExcelDaoImpl implements ExportExcelDao {

@Autowired

private SessionFactory session;

@Override

public List exportExcel(String hql) {

@SuppressWarnings("unchecked")

List list = session.getCurrentSession().createQuery(hql).list();

return list;

}

}

6.ExportExcelController导出Excel文件的控制类

package com.wangzou.myweb.controller;

import java.io.IOException;

import java.io.InputStream;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

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 org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

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

import org.springframework.web.multipart.MultipartFile;

import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.wangzou.myweb.biz.ExportExcelService;

@Controller

public class ExportExcelController {

@Autowired

private ExportExcelService service;

@RequestMapping("/export")

public String exportExcel(HttpServletResponse response) {

response.setContentType("application/binary;charset=utf-8");

try {

ServletOutputStream outputStream = response.getOutputStream();

String fileName = new String(("product").getBytes(), "utf-8");

response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");// 组装附件名称和格式

String hql = "from Product";

String[] titles = { "商品名", "商品总量", "商品单价" };

service.exportExcel(hql, titles, outputStream);

} catch (IOException e) {

e.printStackTrace();

}

return null;

}

@RequestMapping("/load")

public String upload(HttpServletRequest request, HttpServletResponse response) {

MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;

MultipartFile file = mulRequest.getFile("excel");

String filename = file.getOriginalFilename();

if (filename == null || "".equals(filename)) {

return null;

}

try {

InputStream input = file.getInputStream();

@SuppressWarnings("resource")

XSSFWorkbook workBook = new XSSFWorkbook(input);

XSSFSheet sheet = workBook.getSheetAt(0);

if (sheet != null) {

for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {

XSSFRow row = sheet.getRow(i);

for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {

XSSFCell cell = row.getCell(j);

String cellStr = cell.toString();

System.out.print("【" + cellStr + "】 ");

}

System.out.println();

}

}

} catch (Exception e) {

e.printStackTrace();

}

return "/test/uploadExcel.jsp";

}

}

7.Maven依赖的架包

org.apache.poi

poi-ooxml

3.14

以上的详细代码就是通过数据库查询到数据并把查询到的数据以Excel表格形式显示出来!