首先创建了一个实体对象,用来封装导出时需要传入的数据、设置信息等。
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* <p>Title: DownLoad</p>
* <p>Description: download实体</p>
*/
public class DownLoad
{
private String downType;// 导出格式
private String fname;// 导出文件名
private String checks;// 勾选项
private String title;// 标题
//integer:加入有两行或多行表头,填写1,2.. 表头 key:对象对应的字段名 value:表头名称
private Map<Integer, LinkedHashMap<String, String>> header;
//下拉框 String:第几列创建下拉框 ,list 下拉框内容
private Map<Integer,List<Object>> dropDownBox;
//合并单元格 外层list:所有要合并的单元格,内层list样式【m,n,p,q】
//作用是从(m,n)到(p,q)的单元格全部合并
private List<List<Integer>> mergeCells;
private Map<Integer, Integer> celColumn;//设置列宽,默认列宽10*250,如果有特殊的设置第几列,宽度
@SuppressWarnings("rawtypes")
private List<?> dataList = new ArrayList();//数据list
public String getDownType() {
return downType;
}
public void setDownType(String downType) {
this.downType = downType;
}
public String getFname() {
return fname;
}
public void setFname(String fname) {
this.fname = fname;
}
public String getChecks() {
return checks;
}
public void setChecks(String checks) {
this.checks = checks;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Map<Integer, LinkedHashMap<String, String>> getHeader() {
return header;
}
public void setHeader(Map<Integer, LinkedHashMap<String, String>> header) {
this.header = header;
}
public Map<Integer, List<Object>> getDropDownBox() {
return dropDownBox;
}
public void setDropDownBox(Map<Integer, List<Object>> dropDownBox) {
this.dropDownBox = dropDownBox;
}
public List<List<Integer>> getMergeCells() {
return mergeCells;
}
public void setMergeCells(List<List<Integer>> mergeCells) {
this.mergeCells = mergeCells;
}
public List<?> getDataList() {
return dataList;
}
public void setDataList(List<?> dataList) {
this.dataList = dataList;
}
public Map<Integer, Integer> getCelColumn() {
return celColumn;
}
public void setCelColumn(Map<Integer, Integer> celColumn) {
this.celColumn = celColumn;
}
}
具体导出的接口如下:
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
/**
* <p>Title: DownloadService</p>
* <p>Description: 导出数据 </p>
*/
@Service
public class DownloadService {
/**
* @category 导出功能
* @param download
* @param response
* @param request
*/
@SuppressWarnings("unused")
public static void downloading(DownLoad download,HttpServletResponse response, HttpServletRequest request){
OutputStream os = null;
final String userAgent = request.getHeader("USER-AGENT");
String fname = download.getFname();
if(fname==null){
return;
}
fname = fname.replaceAll(" ","_");
String filepath = request.getSession().getServletContext().getRealPath("") + "\\fonts\\";// 获得绝对路径
try{
if (StringUtils.contains(userAgent, "Mozilla")){
fname = new String(fname.getBytes(), "ISO8859-1");
} else{
fname = URLEncoder.encode(download.getFname(), "UTF-8");
}
} catch (UnsupportedEncodingException e){
e.printStackTrace();
}
response.reset();
// 生成xls文件
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="
+ fname + ".xls");
try
{
os = response.getOutputStream();
WritableWorkbook book = Workbook.createWorkbook(os);//创建Excel文件信息
/**
* 标题1样式
*/
jxl.write.WritableFont titleFontStyle = new jxl.write.WritableFont(
WritableFont.ARIAL, 20, WritableFont.BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
jxl.write.WritableCellFormat titleStyle = new jxl.write.WritableCellFormat(titleFontStyle);
titleStyle.setBackground(jxl.format.Colour.GRAY_25);
titleStyle.setBorder(Border.NONE, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.WHITE);
titleStyle.setAlignment(Alignment.CENTRE);//水平居中
titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
WritableCellFormat wcfF = new WritableCellFormat(
NumberFormats.TEXT); //定义一个单元格样式
CellView cv = new CellView(); //定义一个列显示样式
cv.setFormat(wcfF);//把定义的单元格格式初始化进去
cv.setSize(10*265);//设置列宽度(不设置的话是0,不会显示)
/**
* 表头样式
*/
WritableFont headFontStyle = new WritableFont(
WritableFont.ARIAL, 12, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
WritableCellFormat headStyle = new WritableCellFormat(headFontStyle);
headStyle.setBackground(Colour.LIGHT_GREEN);
headStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
headStyle.setAlignment(Alignment.CENTRE);//水平居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
/**
* 数据单元格样式
*/
jxl.write.WritableFont dataFontStyle = new jxl.write.WritableFont(
WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
jxl.write.WritableCellFormat dataStyle = new jxl.write.WritableCellFormat(
dataFontStyle);
dataStyle.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置单元格样式
dataStyle.setBackground(jxl.format.Colour.WHITE); // 设置单元格背景色
dataStyle.setWrap(true);//自动换行
dataStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
/**
* 尾部格式
*/
jxl.write.WritableFont footFontStyle = new jxl.write.WritableFont(
WritableFont.ARIAL, 16, WritableFont.BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
jxl.write.WritableCellFormat fontStyle = new jxl.write.WritableCellFormat(footFontStyle);
fontStyle.setBackground(jxl.format.Colour.WHITE);
fontStyle.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.WHITE);
fontStyle.setAlignment(Alignment.LEFT);//水平靠右
fontStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
int whichRow = 0;//目前到第几行
WritableSheet sheet = book.createSheet("sheet1",0);//创建工作簿
Label l = new Label(0, whichRow , download.getTitle() , titleStyle);//写入标题
sheet.addCell(l);
/**
* 写入表头
*/
Map<Integer, LinkedHashMap<String, String>> headMap = download.getHeader();
List<String> headOne = new ArrayList<String>();//表头1
List<String> headTwo = new ArrayList<String>();//表头2
List<String> headOneToValue = new ArrayList<String>();//字段名
if(headMap.containsKey(1)){
Map<String, String> map = headMap.get(1);
for(String key : map.keySet()){
headOneToValue.add(key);
headOne.add(map.get(key));
}
}
if(headMap.containsKey(2)){
Map<String, String> map = headMap.get(2);
for(String key : map.keySet()){
headTwo.add(key);
}
}
if(headOne!=null && headOne.size()>0){
sheet.mergeCells(0, 0, headOne.size()-1, 1);//合并标题两行
whichRow = whichRow+2;//标题加两行
for (int i = 0; i < headOne.size(); i++){
sheet.setColumnView(i, cv);//设置工作表中第n列的样式
l = new Label(i, whichRow , headOne.get(i), headStyle);
sheet.addCell(l);
}
whichRow = whichRow+1;//表头加一行
}
if(headTwo!=null && headTwo.size()>0){
for (int i = 0; i < headTwo.size(); i++){
l = new Label(i, whichRow , headTwo.get(i), headStyle);
sheet.addCell(l);
}
whichRow = whichRow+1;//第二行标题
}
sheet.getSettings().setVerticalFreeze(whichRow);//下载模板固定表头
/**
* 合并单元格
*/
List<List<Integer>> mergeCells = download.getMergeCells();
if(mergeCells!=null){
for (List<Integer> list : mergeCells) {
sheet.mergeCells(list.get(0), list.get(1), list.get(2), list.get(3));//合并单元格
}
}
/**
* 创建下拉框
*/
Map<Integer,List<Object>> dropDownBox = download.getDropDownBox();
if(dropDownBox!=null){
for(Integer key : dropDownBox.keySet()){
for (int td = whichRow; td < 50; td++) {
WritableCellFeatures wrcf = new WritableCellFeatures();
Label subLabel = new Label(key, td, "");
wrcf.setDataValidationList(dropDownBox.get(key));
subLabel.setCellFeatures(wrcf);
sheet.addCell(subLabel);
}
}
}
/**
* 设置列宽
*/
Map<Integer, Integer> celColumn = download.getCelColumn();
if(celColumn!=null){
for (Integer colum : celColumn.keySet()) {
sheet.setColumnView(colum, celColumn.get(colum));//设置工作表中第n列的样式
}
}
/**
* 插入数据
*/
List<?> dataList = download.getDataList();
if(dataList!=null){
for (int i = 0; i < dataList.size(); i++) {
Object obj = dataList.get(i);//将对象转换为map
Map<String, Object> map = new HashMap<String, Object>();
if(obj != null){
Field[] declaredFields = obj.getClass().getDeclaredFields();
for (Field field : declaredFields) {
field.setAccessible(true);
map.put(field.getName(), field.get(obj));
}
}
for (int j = 0; j < headOneToValue.size(); j++) {//根据字段名称插入数据
String cellValue = map.get(headOneToValue.get(j))==null?"":map.get(headOneToValue.get(j)).toString();
if(headOneToValue.get(j).equals("sex") && cellValue!=""){
if(cellValue.equals("1")){
cellValue = "男";
}else{
cellValue = "女";
}
}
l = new Label(j,whichRow,cellValue,dataStyle);
sheet.addCell(l);
}
whichRow = whichRow+1;//第几行插入数据
}
}
book.write();//输出到文件
book.close();
os.close();
} catch (Exception e)
{
e.printStackTrace();
}
}
}
这里边还有好多不完善的地方,等以后下一步完善!