poi导出图片如图：
主要代码：
注：此为SSH项目 条件导出
配置依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
在前端写两个按钮
<button type="button" onclick="exportExcel2()" class="btn btn-info">
<span class="glyphicon glyphicon-circle-arrow-down"></span>导出Excel
</button>
<button type="button" onclick="importExcel()" class="btn btn-warning">
<span class="glyphicon glyphicon-circle-arrow-up"></span>导入Excel
</button>
定义方法 在条件查询表单中加个id，查询字段都加个name值以便传到后台进行查询
<script type="text/javascript">
// 页面加载函数
$(function(){
//初始化
importExcelHTML = $("#importExcelDiv").html();
})
// 导出Excel
function exportExcel2(){
// 获取条件查询的form表单
var searchForm = document.getElementById("searchForm");
// 给form表单设置提交路径
searchForm.action = "<%=request.getContextPath()%>/excelController/exportExcel2.do";
// 提交form表单
searchForm.submit();
}
// 导入Excel
function importExcel(){
$("#importExcelDiv").html(importExcelHTML);
initImportInput();
importDialog = bootbox.dialog({
title:"<h3>导入Excel</h3>",
message:$("#importExcelDiv form")
})
}
</script>
导入时的弹框
<!-- 导入页面内容 -->
<div id="importExcelDiv" style="display:none;">
<form id="importExcelForm" class="form-horizontal">
<div class="form-group">
<label for="firstname" class="col-sm-2 control-label">选择文件</label>
<div class="col-sm-8">
<input type="file" class="form-control" name="uploadExcel" id="excel"/>
</div>
</div>
</form>
</div>
后台Controller接值
package com.ff.controller;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.ff.model.Drug;
import com.ff.model.DrugArea;
import com.ff.model.Drugbrand;
import com.ff.service.DrugService;
import com.ff.util.ExcelUtil;
import com.ff.vo.DrugVo;
@Controller
@RequestMapping("excelController")
public class ExcelController {
@Autowired
private DrugService drugService;
//下载xlsx//导出
@RequestMapping("exportExcel2")
@ResponseBody
public void exportExcel(DrugVo vo, HttpServletRequest request, HttpServletResponse response) {
// 查询要到导出的数据
List<Drug> list = drugService.exportExcel(vo);
ExcelUtil.exportExcel(vo, request, response, list);
}
// 导入Excel
@RequestMapping("importExcel")
@ResponseBody
public Map<String, Object> importExcel(MultipartFile uploadExcel){
Map<String, Object> map = new HashMap<>();
try {
//1.将文件封装成工作薄
XSSFWorkbook workbook = new XSSFWorkbook(uploadExcel.getInputStream());
//2.获取工作表的数量
int numberOfSheets = workbook.getNumberOfSheets();
//3.循环所有的工作表，根据工作表下标获取对应的sheet
for (int i = 0; i < numberOfSheets; i++) {
XSSFSheet sheet = workbook.getSheetAt(i);
// 4.获取当前工作表中数据的开始位置
int firstRowNum = sheet.getFirstRowNum();
// 5.获取当前工作表中数据的结束位置
int lastRowNum = sheet.getLastRowNum();
// 6.将文件中的数据封装为一个list保存到数据库中
List<drug> list = new ArrayList<drug>();
for (int j = firstRowNum+1; j <= lastRowNum; j++) {
// 获取sheet中的每一行
XSSFRow row = sheet.getRow(j);
// 获取行中单元格的值
Drug drug = getExcelDate(row);
drugService.addDrug(drug);
}
}
map.put("state", true);
} catch (IOException e) {
e.printStackTrace();
map.put("state", false);
}
return map;
}
private Drug getExcelDate(XSSFRow row) {
String name = row.getCell(1).getStringCellValue();
double price = row.getCell(2).getNumericCellValue();
double drugSales = row.getCell(3).getNumericCellValue();
double drugStock = row.getCell(4).getNumericCellValue();
//地区
String area = row.getCell(5).getStringCellValue();
String brand = row.getCell(6).getStringCellValue();
String isOtc = row.getCell(7).getStringCellValue();
String person = row.getCell(8).getStringCellValue();
Date date = row.getCell(9).getDateCellValue();
//图片
String img = row.getCell(10).getStringCellValue();
DrugArea area2 = new DrugArea();
area2.setDrugAreaId(area.equals("山东")?1:area.equals("河南")?2:area.equals("北京")?3:null);
Drugbrand brand2=new Drugbrand();
brand2.setDrugBrandId(brand.equals("中华西药")?1:brand.equals("河南中药")?2:brand.equals("草药")?3:null);
Drug drug=new Drug(
name,
(int)price,
(int)drugSales,
(int)drugStock,
isOtc.equals("非处方药")?1:isOtc.equals("处方药")?2:null,
person.replace("幼年", "1").replace("少年", "2").replace("青年", "3").replace("中年", "4").replace("老年", "5").replace("孕妇", "6"),
date,
img,
area2,
brand2);
return drug;
}
}
ExcelUtil工具类
package com.ff.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
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;
import com.ff.model.Drug;
import com.ff.vo.DrugVo;
public class ExcelUtil {
// 下载路径
public static void excelDownload(XSSFWorkbook wirthExcelWB, HttpServletRequest request,
HttpServletResponse response, String fileName) {
OutputStream out = null;
try {
// 解决下载文件名中文乱码问题
if (request.getHeader("User-agent").toLowerCase().indexOf("firefox") != -1) {
fileName = new String(fileName.getBytes("utf-8"), "iso-8859-1");
} else {
fileName = URLEncoder.encode(fileName, "utf-8");
}
out = response.getOutputStream();
// 让浏览器识别是什么类型的文件
response.reset(); // 重点突出
response.setCharacterEncoding("UTF-8"); // 重点突出
response.setContentType("application/x-msdownload");// 不同类型的文件对应不同的MIME类型
// // 重点突出
// inline在浏览器中直接显示，不提示用户下载
// attachment弹出对话框，提示用户进行下载保存本地
// 默认为inline方式
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
wirthExcelWB.write(out);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != out) {
try {
out.close();
out = null;
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
//下载xlsx
public static void exportExcel(DrugVo vo, HttpServletRequest request, HttpServletResponse response,
//List<drug> list ============================================需要改
List<Drug> list) {
// 创建一个excel表格
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建sheet页
XSSFSheet sheet = workbook.createSheet();
// 创建表头行
XSSFRow headerRow = sheet.createRow(0);
// 创建一个内容剧中的的单元格样式
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置背景颜色
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 字体加粗
XSSFFont font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);
// 表头
//"序号", "电影名称", "电影价格", "电影评分", "上映地区", "上映时间", "适合人群", "是否上映", "余票数量" ============================================需要改
String[] headerNameArr = { "序号","药品名称","价格","销量","库存","产地","品牌","是否处方药","适合人群","生产日期","药品照片"};
Integer[] headerWidthArr = { 12 * 256, 20 * 256, 12 * 256, 12 * 256, 20 * 256, 20 * 256, 20 * 256, 20 * 256,12 * 256,12 * 256, 20 * 256 };
for (int i = 0; i < headerWidthArr.length; i++) {
XSSFCell cell = headerRow.createCell(i);
cell.setCellValue(headerNameArr[i]);
cell.setCellStyle(cellStyle);
// 给单元格设置宽度
sheet.setColumnWidth(i, headerWidthArr[i]);
sheet.setDefaultRowHeightInPoints(2000*2000);
}
// 设置日期类型的表格
XSSFCellStyle cellStyle2 = workbook.createCellStyle();
XSSFDataFormat format = workbook.createDataFormat();
cellStyle2.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
setCellWidth(cellStyle2);
// 设置字体居中
XSSFCellStyle cellStyle3 = workbook.createCellStyle();
cellStyle3.setAlignment(HorizontalAlignment.CENTER);
setCellWidth(cellStyle3);
// 往excel中放值
//drug drug = list.get(i);值 ============================================需要改
try {
// 往excel中放值
for (int i = 0; i < list.size(); i++) {
Drug drug = list.get(i);
XSSFRow row = sheet.createRow(i+1);
// 创建单元格并且给单元格赋值
XSSFCell cell = row.createCell(0);
cell.setCellValue(i+1);
cell.setCellStyle(cellStyle3);
//设置高度
row.setHeightInPoints(50);
// 插入 图片至 Excel
String path = request.getServletContext().getRealPath("");
String posterPath = drug.getFileName();
File file=new File(path+posterPath);
if(file.exists()) {
InputStream is = new FileInputStream(path+posterPath);
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
CreationHelper helper = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
// anchor主要用于设置图片的属性
ClientAnchor anchor = helper.createClientAnchor();
// 图片插入坐标
anchor.setCol1(10); //列
anchor.setRow1(i+1); //行
// 使用固定的长宽比例系数
double a = 1;
double b = 1;
// 插入图片
Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize(a,b);
}
XSSFCell cell1 = row.createCell(1);
cell1.setCellValue(drug.getDrugName());
cell1.setCellStyle(cellStyle3);
XSSFCell cell2 = row.createCell(2);
cell2.setCellValue(drug.getDrugPrice());
cell2.setCellStyle(cellStyle3);
XSSFCell cell3 = row.createCell(3);
cell3.setCellValue(drug.getDrugSales());
cell3.setCellStyle(cellStyle3);
XSSFCell cell4 = row.createCell(4);
cell4.setCellValue(drug.getDrugStock());
cell4.setCellStyle(cellStyle3);
XSSFCell cell5 = row.createCell(5);
cell5.setCellValue(drug.getDrugArea()==null?null:drug.getDrugArea().getDrugAreaName());
cell5.setCellStyle(cellStyle3);
XSSFCell cell6 = row.createCell(6);
cell6.setCellValue(drug.getDrugBrand()==null?null:drug.getDrugBrand().getDrugBrandName());
cell6.setCellStyle(cellStyle3);
XSSFCell cell7 = row.createCell(7);
cell7.setCellValue(drug.getIsOtc()==1?"非处方药":"处方药");
cell7.setCellStyle(cellStyle3);
XSSFCell cell8 = row.createCell(8);
cell8.setCellValue(drug.getPerson()==null?null:drug.getPerson().replace("1", "幼儿").replace("2", "少年").replace("3", "青年").replace("4", "中年").replace("5", "老年").replace("6", "孕妇"));
cell8.setCellStyle(cellStyle3);
XSSFCell cell9 = row.createCell(9);
cell9.setCellValue(drug.getProducedDate());
cell9.setCellStyle(cellStyle2);
XSSFCell cell0 = row.createCell(10);
cell0.setCellValue(drug.getFileName());
}
} catch (Exception e) {
e.printStackTrace();
}
ExcelUtil.excelDownload(workbook, request, response, "电影列表.xlsx");
}
private static void setCellWidth(XSSFCellStyle cellStyle) {
// 给单元格加边框
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
}
/**
* 文件下载
*
* @param file
* @param fileName
* @param response
*/
public static void downloadFile(File file, String fileName, HttpServletRequest request,
HttpServletResponse response) throws IOException {
OutputStream os = response.getOutputStream();
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
BufferedOutputStream bos = new BufferedOutputStream(os);
// 解决下载文件名中文乱码问题
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") != -1) {
fileName = new String(fileName.getBytes("utf-8"), "iso-8859-1");
} else {
fileName = URLEncoder.encode(fileName, "utf-8");
}
response.reset(); // 重点突出
response.setCharacterEncoding("UTF-8"); // 重点突出
response.setContentType("application/x-msdownload");// 不同类型的文件对应不同的MIME类型
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
// 定义一个长度为4096的字节数组
byte[] bytes = new byte[4096];
// 先读他个4096字节
int read = bis.read(bytes);
while (read > 0) {
bos.write(bytes, 0, read);
read = bis.read(bytes);
}
bos.close();
bis.close();
}
}
dao层执行
package com.ff.dao.impl;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import com.ff.dao.MovieDao;
import com.ff.model.movie.Movie;
import com.ff.model.movie.MovieVO;
import com.ff.util.DataTableUtil;
@Repository
public class MovieDaoImpl implements MovieDao {
@Autowired
private HibernateTemplate hibernateTemplate;
//查询数据
@Override
public void queryMovieList(DataTableUtil<Movie> page, MovieVO vo) {
Criteria criteria = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(Movie.class);
whereSearch(vo, criteria);
page.setRecordsTotal(criteria.list().size());
criteria.setFirstResult(page.getStart());
criteria.setMaxResults(page.getLength());
page.setData(criteria.list());
}
//条件导出
@Override
public List<Movie> queryMovieList(MovieVO vo) {
Criteria criteria = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(Movie.class);
whereSearch(vo, criteria);
return criteria.list();
}
//提取封装
private void whereSearch(MovieVO vo, Criteria criteria) {
if (vo != null) {
if (StringUtils.isNotBlank(vo.getMovieName())) {
criteria.add(Restrictions.like("movieName", "%"+vo.getMovieName()+"%"));
}
if (vo.getMinPrice() != null) {
criteria.add(Restrictions.gt("moviePrice", vo.getMinPrice()));
}
if (vo.getMaxPrice() != null) {
criteria.add(Restrictions.lt("moviePrice", vo.getMaxPrice()));
}
if (vo.getMinDate() != null) {
criteria.add(Restrictions.gt("pubDate", vo.getMinDate()));
}
if (vo.getMaxDate() != null) {
criteria.add(Restrictions.lt("pubDate", vo.getMaxDate()));
}
if (vo.getIsup() != null) {
criteria.add(Restrictions.eq("isup", vo.getIsup()));
}
if (StringUtils.isNotBlank(vo.getPerson())) {
String[] arr = vo.getPerson().split(",");
// Disjunction(抵死军可申)添加的是逻辑或（OR）的关系，另外还有个Conjunction，添加的是逻辑与（AND）的关系
// Restrictions.disjunction() 逻辑或
Disjunction disjunction = Restrictions.disjunction();
for (int i = 0; i < arr.length; i++) {
// MatchMode.ANYWHERE --> 字符串在中间匹配.相当于"like '%value%'"
disjunction.add(Restrictions.like("person", "%"+arr[i]+"%", MatchMode.ANYWHERE));
}
criteria.add(disjunction);
}
if(vo.getAreaId() != -1 && vo.getAreaId() != null){
criteria.add(Restrictions.eq("area.areaId", vo.getAreaId()));
}
}
}
}