Java实现Excel导出(一)
不同于excel导入，下面例子的excel导出是在后台java代码中实现，在页面上点击导出按钮，所有的工作都在后台进行，下面是使用POI的方式导出excel。
由于时间有限，所以就没写前端页面，下面的例子是通过在浏览器中输入导出方法的地址来弹出窗口
主要实现将下面数据库表导出至excel
Java文件
entity实体 StudentGrade.java
package com.env.entity;
import javax.persistence.Entity;
import javax.persistence.Table;
import com.env.common.entity.BaseBO;
@Entity
@Table(name="s_grade")
public class StudentGrade extends BaseBO {
private static final long serialVersionUID = 1L;
//成绩id
private String gid;
//学生姓名
private String studentname;
//课程java
private String java;
//课程Oracle
private String oracle;
//课程数据结构
private String struct;
//总分
private String total;
public String getGid() {
return gid;
}
public void setGid(String gid) {
this.gid = gid;
}
public String getStudentname() {
return studentname;
}
public void setStudentname(String studentname) {
this.studentname = studentname;
}
public String getJava() {
return java;
}
public void setJava(String java) {
this.java = java;
}
public String getOracle() {
return oracle;
}
public void setOracle(String oracle) {
this.oracle = oracle;
}
public String getStruct() {
return struct;
}
public void setStruct(String struct) {
this.struct = struct;
}
public String getTotal() {
return total;
}
public void setTotal(String total) {
this.total = total;
}
}
dao层 SgradeMapper.java
package com.env.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.env.entity.StudentGrade;
public interface SgradeMapper {
List<StudentGrade> getStudentGradeList(@Param("studentname")String studentname);
}
mapper文件 SgradeMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.env.dao.SgradeMapper">
<select id="getStudentGradeList" resultType="com.env.entity.StudentGrade">
select
gid,studentname,struct,java,oracle,total
from s_grade
<if test="studentname!=null and studentname!=''">
where studentname = #{studentname}
</if>
</select>
</mapper>
Controller层 SgradeController.java
package com.env.controller;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import com.env.result.Result;
import com.env.service.SgradeService;
import com.env.utils.LoggerUtil;
@RestController
@RequestMapping(value="/sGradeController")
public class SgradeController {
@Autowired
private SgradeService sgradeService;
/*
* 导出学生成绩列表至excel
*/
//导出的方法是用GET请求方式访问，后面的参数{json}用来传学生姓名，可根据学生姓名查询该学生成绩，仅导出该学生的成绩到excel，若参数为空，则导出所有学生的成绩到
//工具类Result方法
/*public static Result success(Object data){
Result result=new Result(1,"success",data);
return result;
}
public static Result error(String msg){
Result result=new Result(0,msg,null);
return result;
}*/
@RequestMapping(method = RequestMethod.GET, value="/exportSgrade2Excel/{json}")
public Result exportSgrade2Excel(
@PathVariable String json,HttpServletRequest request, HttpServletResponse response) {
try {
json = "{"+json+"}";
String result = sgradeService.exportSgrade2Excel(json,
request, response);
return Result.success(result);
} catch (Exception e) {
e.printStackTrace();
return Result.error("异常，导出学生成绩到Excel失败");
}
}
}
Service层 SgradeService.java
package com.env.service;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public interface SgradeService {
/*
* 导出学生成绩至excel
*/
String exportSgrade2Excel(String json, HttpServletRequest request, HttpServletResponse response) throws Exception;
}
ServiceImpl SgradeServiceImpl.java
package com.env.serviceImpl;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.DynaBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.env.common.entity.PoiDataBean;
import com.env.common.entity.PoiTitleBean;
import com.env.common.utils.DynaBeanPluginsUtil;
import com.env.common.utils.ExcelPoiPluginsUtil;
import com.env.common.utils.FileToolsUtil;
import com.env.common.utils.JsonPluginsUtil;
import com.env.common.utils.Util;
import com.env.dao.SgradeMapper;
import com.env.entity.StudentGrade;
import com.env.service.SgradeService;
@Service
public class SgradeServiceImpl implements SgradeService {
@Autowired
private SgradeMapper sgradeMapper;
/*
* 导出学生成绩数据至excel
*/
@Override
public String exportSgrade2Excel(String json, HttpServletRequest request,
HttpServletResponse response) throws Exception {
//json字符串转换为bean对象,也可用其他工具类转换，下面的工具类中不会给出
DynaBean bean = JsonPluginsUtil.json2bean(json);
// 参数,可为空，为空则导出所有学生的成绩，Util.toString()是转字符串的工具类方法，下面的工具类中不会给出
String studentname = Util.toString(bean.get("studentname"));
//查询学生成绩
List<StudentGrade> list = sgradeMapper.getStudentGradeList(studentname);
// Excel模板,导出的excel文件以此为模板
String frompath = FileToolsUtil.ROOT + "resource/excel/studentGrade.xlsx";
// 导出的文件名称
String filename = "学生成绩表";
//在此处定义excel列名，须与上面所说的excel模板的一一对应
String columnname[] = {
"ordernum","studentname","java","oracle","数据结构","总分"
};
//导出到excel的所有数据保存在该list中
List<DynaBean> list1 = new ArrayList<DynaBean>();
//每一个DynaBean相当于一行数据
DynaBean bean1 = null;
//从第四行开始插入数据
int row = 3;
for(int i=0;i<=list.size()-1;i++){
String studentname1 = list.get(i).getStudentname();
String java = list.get(i).getJava();
String oracle = list.get(i).getOracle();
String struct = list.get(i).getStruct();
String total = list.get(i).getTotal();
bean1 = DynaBeanPluginsUtil.invoke();
bean1.set("ordernum", i+1);
bean1.set("studentname", studentname1);
bean1.set("java", java);
bean1.set("oracle", oracle);
bean1.set("数据结构", struct);
bean1.set("总分", total);
list1.add(bean1);
row++;
}
//在导出的excel表中显示查询条件(导出的是哪个学生的数据)
String studentname2 = null;
if (!Util.isNull(studentname) && !"-1".equals(studentname)) {
studentname2 = studentname;
}else{
studentname2 = "全部";
}
//0和3分别表示从第一列第四行开始插入数据，list1是要插入表格的数据集合，columnname为上面定义的表的列名，为String类型数组
PoiDataBean dataBean = new PoiDataBean(0, 3, list1, columnname);
// 导出Excel的头部信息
List<PoiTitleBean> titleBean = new ArrayList<PoiTitleBean>();
String title = "学生：" + studentname2;
PoiTitleBean selectBean = new PoiTitleBean(1, Util.toString(title));
titleBean.add(selectBean);
//导出Excel工具类
//frompath为导出excel的模板文件地址
//filename为将导出excel文件的名称
//titleBean为导出Excel文件的头部信息
ExcelPoiPluginsUtil.wirte(frompath, filename, dataBean, titleBean, request, response);
return null;
}
}
涉及到的主要工具类
DynaBeanPluginsUtil.java
package com.env.common.utils;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.DynaProperty;
public class DynaBeanPluginsUtil {
/**
* 创建 DynaBean
* @return
*/
public static DynaBean invoke(){
return new CommonDynaBean();
}
/**
* 判断是否有此属性
* @param _dynaBean 对象
* @param _property 属性名称
* @return 【true 有】【 false 无】
*/
public static boolean IsProperty(DynaBean _dynaBean,String _property){
return getPropertyStr(_dynaBean).indexOf(_property)!=-1?true:false;
}
/**
* 获取此对象中所含有的属性
* @param _dynaBean
* @param _propertys
* @return
*/
public static String[] havePropertys(DynaBean _dynaBean,String[] _propertys){
String _tempStr = getPropertyStr(_dynaBean);
List<String> _list = new ArrayList<String>();
for(String _property : _propertys){
if(_tempStr.indexOf(_property)!=-1){
_list.add(_property);
}
}
return _list.toArray(new String[]{});
}
/**
* 复制生成新的bean
*
* @param _bean 复制的对象
* @param copyPropertys 复制某些属性
* @param copy2Propertys复制为对应属性
* @return
*/
public static DynaBean copyBean(DynaBean _bean,String[] copyPropertys,String[] copy2Propertys){
DynaBean _tempBean = invoke();
String _tempStr = getPropertyStr(_bean);
if(copy2Propertys!=null&©Propertys.length!=copy2Propertys.length){
System.out.println("复制属性与复制到的属性不匹配！");
}else{
String _property = null;
if(copy2Propertys==null){
for(int _i=0;_i<copyPropertys.length;_i++){
_property = copyPropertys[_i];
if(_tempStr.indexOf(_property)!=-1){
_tempBean.set(_property, _bean.get(_property));
}else{
_tempBean.set(_property, null);
}
}
}else{
for(int _i=0;_i<copyPropertys.length;_i++){
_property = copyPropertys[_i];
if(_tempStr.indexOf(_property)!=-1){
_tempBean.set(copy2Propertys[_i], _bean.get(_property));
}else{
_tempBean.set(copy2Propertys[_i], null);
}
}
}
}
return _tempBean;
}
/**
* 将_bean的属性复制到的2bean 如果有数据则覆盖 但类型必须保持一致
*
* @param _bean 复制的对象
* @param copyPropertys 复制某些属性
* @param copy2Propertys复制为对应属性
* @return
*/
public static DynaBean copy2Bean(DynaBean _bean,DynaBean _tempBean ,String[] copyPropertys,String[] copy2Propertys){
String _tempStr = getPropertyStr(_bean);
if(copy2Propertys!=null&©Propertys.length!=copy2Propertys.length){
System.out.println("复制属性与复制到的属性不匹配！");
}else{
String _property = null;
if(copy2Propertys==null){
for(int _i=0;_i<copyPropertys.length;_i++){
_property = copyPropertys[_i];
if(_tempStr.indexOf(_property)!=-1){
_tempBean.set(_property, _bean.get(_property));
}else{
_tempBean.set(_property, null);
}
}
}else{
for(int _i=0;_i<copyPropertys.length;_i++){
_property = copyPropertys[_i];
if(_tempStr.indexOf(_property)!=-1){
_tempBean.set(copy2Propertys[_i], _bean.get(_property));
}else{
_tempBean.set(copy2Propertys[_i], null);
}
}
}
}
return _tempBean;
}
/**
* 复制生成新的bean
*
* @param _bean 复制的对象
* @param copyPropertys 复制某些属性
* @param copy2Propertys复制为对应属性
* @return
*/
public static DynaBean copy2Bean(DynaBean _bean,DynaBean _temp2Bean ,String[] copyPropertys){
return copy2Bean( _bean, _temp2Bean , copyPropertys, null);
}
/**
* 复制生成新的bean
*
* @param _bean 复制的对象
* @param copyPropertys 复制某些属性
* @return
*/
public static DynaBean copyBean(DynaBean _bean,String[] copyPropertys){
return copyBean(_bean, copyPropertys,null);
}
private static String getPropertyStr(DynaBean _dynaBean){
DynaProperty[] _dynaPropertys = _dynaBean.getDynaClass().getDynaProperties();
StringBuffer _buffer = new StringBuffer(",");
for(DynaProperty _dynaProperty: _dynaPropertys){
_buffer.append(_dynaProperty.getName()+",");
}
return _buffer.toString();
}
}
PoiDataBean.java
package com.env.common.entity;
import java.util.List;
import org.apache.commons.beanutils.DynaBean;
public class PoiDataBean {
public PoiDataBean(int columnstart, int rowstart, List<DynaBean> data,
String[] columnname) {
super();
this.columnstart = columnstart;
this.rowstart = rowstart;
this.data = data;
this.columnname = columnname;
}
private int columnstart;//开始列0
private int rowstart;//开始行0
private List<DynaBean> data;//数据集合
private String[] columnname;//数据项
private boolean tieleWirte;//是否已经写入
public int getColumnstart() {
return columnstart;
}
public void setColumnstart(int columnstart) {
this.columnstart = columnstart;
}
public int getRowstart() {
return rowstart;
}
public void setRowstart(int rowstart) {
this.rowstart = rowstart;
}
public List<DynaBean> getData() {
return data;
}
public void setData(List<DynaBean> data) {
this.data = data;
}
public String[] getColumnname() {
return columnname;
}
public void setColumnname(String[] columnname) {
this.columnname = columnname;
}
public boolean isTieleWirte() {
return tieleWirte;
}
public void setTieleWirte(boolean tieleWirte) {
this.tieleWirte = tieleWirte;
}
}
PoiTitleBean.java
package com.env.common.entity;
/**
* 导出excel的头部信息
*/
public class PoiTitleBean {
private int row;//行数
private String title;//标题
private int call = 0;//列数
public int getRow() {
return row;
}
public PoiTitleBean(int row, String title) {
super();
this.row = row;
this.title = title;
}
public void setRow(int row) {
this.row = row;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getCall() {
return call;
}
public void setCall(int call) {
this.call = call;
}
}
ExcelPoiPluginsUtil.java
package com.env.common.utils;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Value;
import com.env.common.entity.ExcelPicBean;
import com.env.common.entity.PoiDataBean;
import com.env.common.entity.PoiTitleBean;
/**
* Excel 解析、读写工具类
*
* 需要
* poi-3.9-20121203.jar
* poi-ooxml-3.9-20121203.jar
* poi-ooxml-schemas-3.9-20121203.jar
* xmlbeans-2.3.0.jar
* 支持
* @author
*
*/
public class ExcelPoiPluginsUtil {
private static final int EXCELMAXCOUNT = 5000;
private static final String EXCEL_ROOT = "resource\\excel\\";
private static final String EXCEL_SUB = "excel";
public static final int HSSFCell_CELL_TYPE_STRING = HSSFCell.CELL_TYPE_STRING;
public static List<PoiTitleBean> inikPoiTitleBean(int _start, String[] _titles){
List<PoiTitleBean> _poiTitleBeanList = new ArrayList<PoiTitleBean>();
PoiTitleBean _poiTitleBean = null;
for(int _i=0;_i<_titles.length;_i++){
_poiTitleBean = new PoiTitleBean(_start+_i, _titles[_i]);
_poiTitleBeanList.add(_poiTitleBean);
}
return _poiTitleBeanList;
}
/**
*
* @param data 数据
* @param frompath 模板路径
* @param filename 文件名称
* @param columnstart 开始列0
* @param rowstart 开始行0
* @return
* @throws IOException
* @throws RowsExceededException
* @throws WriteException
*/
public static DynaBean wirte(List<List<String>> data,String frompath ,String filename , int columnstart, int rowstart) throws IOException
{
DynaBean _dynaBean = DynaBeanPluginsUtil.invoke();
int fileCount = getFileCount(data.size());
String downFile = null;
if(fileCount==1){
downFile = EXCEL_ROOT + UUIDToolsUtil.getUUID(EXCEL_SUB) + frompath.substring(frompath.lastIndexOf("."),frompath.length());
String toPath = FileToolsUtil.ROOT + downFile;
wirte( data, frompath, toPath, columnstart, rowstart, 0, data.size());
}else{
String felie = UUIDToolsUtil.getUUID(EXCEL_SUB);
String sourceDir = EXCEL_ROOT + felie;
for(int _h=1;_h<=fileCount;_h++){
String filePath = sourceDir +"\\" + filename + "_" + _h +frompath.substring(frompath.lastIndexOf("."),frompath.length());
String toPath = FileToolsUtil.ROOT + filePath;
wirte( data, frompath, toPath, columnstart, rowstart, (_h-1)*EXCELMAXCOUNT, _h==fileCount?(data.size()-(_h-1)*EXCELMAXCOUNT):EXCELMAXCOUNT);
}
downFile = sourceDir + ".zip";
String zipFile = FileToolsUtil.ROOT + downFile;
ZipPluginsUtil.zip(FileToolsUtil.ROOT + sourceDir + "\\", zipFile);
}
_dynaBean.set("fileCount", fileCount);
_dynaBean.set("downFile", downFile);
_dynaBean.set("filename", filename);
return _dynaBean;
}
public static DynaBean wirte(List<List<String>> data,String frompath , int columnstart, int rowstart) throws IOException
{
return wirte(data, frompath , UUIDToolsUtil.getUUID(EXCEL_SUB) , columnstart, rowstart);
}
private static void wirte(List<List<String>> data,String frompath,String topath, int columnstart, int rowStart, int dataStart, int dataCount) throws IOException
{
FileToolsUtil.copyFile(frompath, topath);
// 初始化
Workbook wb = null;
InputStream inp = new FileInputStream(topath);
//
int rowCount = 0;
rowCount = rowStart + dataCount;
int i = dataStart;
try {
wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
for(int _i = rowStart; _i <rowCount; _i++) {
Row _row = sheet.getRow(_i);
List<String> _i_data=data.get(i);
if(_row==null){
_row= sheet.createRow(_i);
}
_row.setHeight((short) 500);
int columnCount = _i_data.size()+columnstart;//需要加上开始的列数
int j = 0;//需要额外的计数
for(int _j = columnstart; _j <columnCount; _j++) {
Cell cell = _row.getCell(_j);
if(cell==null){
cell = _row.createCell(_j);
}
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(_i_data.get(j));
j++;
}
i++;
}
FileOutputStream out=new FileOutputStream(topath);
wb.write(out);
out.flush();
out.close();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static int getFileCount(int dataSize){
int fileCount = (dataSize%EXCELMAXCOUNT==0?dataSize:(dataSize-dataSize%EXCELMAXCOUNT+EXCELMAXCOUNT))/EXCELMAXCOUNT;
return fileCount;
}
/**
* @descri 使用该方法进行excel导出
* @param frompath 模板路径
* @param filename 文件名称
* @param _poiDataBean 数据
* @param _poiTitleBean 标题
* @return
* @throws IOException
* @throws RowsExceededException
* @throws WriteException
*/
public static void wirte(String frompath ,String filename, PoiDataBean _poiDataBean, List<PoiTitleBean> _poiTitleBean,
HttpServletRequest request, HttpServletResponse response) throws IOException {
String toPath = FileToolsUtil.TMPPATH + UUIDToolsUtil.getUUID(EXCEL_SUB) +
frompath.substring(frompath.lastIndexOf("."),frompath.length());
wirte(frompath, toPath, filename, 0, _poiDataBean.getData().size(), _poiDataBean, _poiTitleBean, request, response);
}
private static void wirte(String frompath, String topath, String filename, int dataStart, int dataCount, PoiDataBean _poiDataBean, List<PoiTitleBean> _poiTitleBean,
HttpServletRequest request, HttpServletResponse response) throws IOException {
// response输出流
ServletOutputStream out = response.getOutputStream();
// 文件名外加当前时间
DateFormat format = new SimpleDateFormat("yyyyMMdd");
String timeFileName = format.format(new Date());
timeFileName =encodingFileName(filename + "_" + timeFileName);
// 设置response必要参数
response.reset();
response.setContentType("application/octet-stream; charset=iso-8859-1");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename=" + timeFileName +".xlsx" );
List<DynaBean> data = _poiDataBean.getData();
int columnstart = _poiDataBean.getColumnstart();
int rowStart = _poiDataBean.getRowstart();
String[] columnname = _poiDataBean.getColumnname();
if(FileToolsUtil.copyFile(frompath, topath)){
// 初始化
Workbook wb = null;
InputStream inp = new FileInputStream(topath);
int rowCount = 0;
rowCount = rowStart + dataCount;
int i = dataStart;
try {
wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
Row _row = sheet.getRow(_poiDataBean.getRowstart());
int columnCount = columnname.length+columnstart;//需要加上开始的列数
Cell _cell = null;
Map<Integer,CellStyle > _callTypeMap0 = new HashMap<Integer,CellStyle >();
Cell cell = _row.getCell(_poiDataBean.getColumnstart());
for(int _j = columnstart; _j <columnCount; _j++){
_cell = _row.getCell(_j);
if(_cell!=null){
_callTypeMap0.put(_j, _cell.getCellStyle());
}else{
_callTypeMap0.put(_j, cell.getCellStyle());
}
}
_row = sheet.getRow(_poiDataBean.getRowstart()+1);
Map<Integer,CellStyle > _callTypeMap1 = new HashMap<Integer,CellStyle >();
cell = _row.getCell(_poiDataBean.getColumnstart());
for(int _j = columnstart; _j <columnCount; _j++){
_cell = _row.getCell(_j);
if(_cell!=null){
_callTypeMap1.put(_j, _cell.getCellStyle());
}else{
_callTypeMap1.put(_j, cell.getCellStyle());
}
}
short _rowHeight = _row.getHeight();
if(!_poiDataBean.isTieleWirte()){
if(_poiTitleBean != null && _poiTitleBean.size()>0){
for(PoiTitleBean _title : _poiTitleBean){
_row = sheet.getRow(_title.getRow());
cell = _row.getCell(_title.getCall());
cell.setCellValue(_title.getTitle());
}
}
}
for(int _i = rowStart; _i <rowCount; _i++) {
_row = sheet.getRow(_i);
DynaBean _i_data=data.get(i);
if(_row==null){
_row= sheet.createRow(_i);
}
_row.setHeight(_rowHeight);
Map<Integer,CellStyle > _callTypeMap = null;
if(_i%2!=0){
_callTypeMap = _callTypeMap0;
}else{
_callTypeMap = _callTypeMap1;
}
int j = 0;//需要额外的计数
for(int _j = columnstart; _j <columnCount; _j++) {
cell = _row.getCell(_j);
if(cell==null){
cell = _row.createCell(_j);
}
cell.setCellStyle(_callTypeMap.get(_j));
dynaBeanVal2CellVal(cell,_i_data.get(columnname[_j-columnstart]));
j++;
}
i++;
}
wb.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}
}
public static String encodingFileName(String fileName) {
String returnFileName = "";
try {
returnFileName = new String(fileName.getBytes("GB2312"), "ISO8859-1");
returnFileName = StringUtils.replace(returnFileName, "+", "%20");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return returnFileName;
}
private static void dynaBeanVal2CellVal(Cell cell,Object _dynaBeanVal){
if(_dynaBeanVal instanceof String){
cell.setCellValue(Util.toString(_dynaBeanVal));
}else if(_dynaBeanVal instanceof Date){
cell.setCellValue((Date)_dynaBeanVal);
}else if(_dynaBeanVal instanceof Boolean){
cell.setCellValue((Boolean)_dynaBeanVal);
}else if(_dynaBeanVal instanceof Integer ){
cell.setCellValue((Integer)_dynaBeanVal);
}else if(_dynaBeanVal instanceof Float ){
cell.setCellValue((Float)_dynaBeanVal);
}else if(_dynaBeanVal instanceof Double ){
cell.setCellValue((Double)_dynaBeanVal);
}else {
cell.setCellValue(Util.toString(_dynaBeanVal));
}
}
/**
*
* @param _excelPath Excel文件路径
* @param _picPath 图片路径
* @param columnstart 从第几列开始
* @param rowstart 从第几行开始
* @param columnCount 占用几列
* @param rowCount 占用几行
*
* rowstart<0 则会自动追加在最后 |rowstart|行
*
* @return
* @throws IOException
* @throws RowsExceededException
* @throws WriteException
*/
public static boolean wirtePic(String _excelPath , ExcelPicBean _excelPicBean) throws IOException{
String _picPath = _excelPicBean.getPicPath();
int columnstart = _excelPicBean.getColumnstart();
int rowstart = _excelPicBean.getRowstart();
int columnCount = _excelPicBean.getColumnCount();
int rowCount = _excelPicBean.getRowCount();
Workbook wb = null;
InputStream inp = new FileInputStream(_excelPath);
ByteArrayOutputStream byteArrayOut = null;
FileOutputStream out = null;
int PICTURE_TYPE = 0;
try {
wb = WorkbookFactory.create(inp);
File pic = new File(_picPath);
String filename = pic.getName();
String sux = filename.substring(filename.lastIndexOf(".")+1, filename.length()).toLowerCase();
if("png".equals(sux)){
PICTURE_TYPE = Workbook.PICTURE_TYPE_PNG;
}else if("jpg".equals(sux)){
PICTURE_TYPE = Workbook.PICTURE_TYPE_JPEG;
}
//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File(_picPath));
ImageIO.write(bufferImg,"png",byteArrayOut);
//创建工作薄
Sheet sheet = wb.getSheetAt(0);
if(rowstart<0){
rowstart = sheet.getLastRowNum()-rowstart;
}
Drawing patriarch = sheet.createDrawingPatriarch();
ClientAnchor anchor = patriarch.createAnchor(
10,10,10,10,
columnstart , rowstart,
columnstart+columnCount,rowstart+rowCount);
patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),PICTURE_TYPE));
// 参数说明：
// row 起始行 cell 起始单元格 row1 终止行 cell2 终止单元格
if(inp!=null){
inp.close();
}
out = new FileOutputStream(_excelPath);
wb.write(out);
out.flush();
out.close();
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
if(inp!=null){
inp.close();
}
if(out!=null){
out.close();
}
if(byteArrayOut!=null){
byteArrayOut.close();
}
}
return true;
}
}
Excel模板文件 studentGrade.xlsx
实现效果
studentname参数为空的情况
浏览器地址栏
回车
导出的数据
studentname参数不为空的情况
浏览器地址栏
导出的数据
至此，导出规则的excel就搞定了