JExcelOperator.java
导出导入的类
public class JExcelOperator {
/*定义默认的列宽*/
private static final int DEFAULT_COLUMN_WIDTH = 10;
/*定义默认的工作薄名称*/
private static final String DEFAULT_WORK_TITLE = "教育";
/*定义默认的工作表标题样式*/
private static final ITitleFormat DEFAULT_TITLE_FORMAT = new DefaultTitleFormat();
/*定义默认的工作表表头样式*/
private static final IColumnFormat DEFAULT_COLUMN_FORMAT = new DefaultColumnFormat();
/*定义默认的工作表数据样式*/
private static final IDataCellFormat DEFAULT_DATACELL_FORMAT = new DefaultDataCellFormat();
/*指定工作表标题的 样式*/
private ITitleFormat titleFormat ;
/*指定表头样式*/
private IColumnFormat columnFormat ;
/*指定数据行 样式*/
private IDataCellFormat dataFormat ;
/**
* 导出Excel .成功导出,返回true;否则返回false
* @param request
* HTTP请求对象
* @param response
* HTTP响应对象
* @param workTitle
* 工作薄名称
* @param title
* 工作表标题
* @param columns
* 列名数组
* @param widths
* 列宽度数组
* @param dataList
* 要导出的对象集合
* @param objectMapper
* 对象解析器,将一个对象解析成一行数据
*/
@SuppressWarnings("unchecked")
public boolean exportExcel(HttpServletRequest request, HttpServletResponse response, String workTitle,String title,
String[] columns, int[] widths,List dataList, IObjectMapper objectMapper) {
OutputStream out = null;
WritableWorkbook wbook = null;
try {
//断言 objectMapper 不能为空
Assert.notNull(objectMapper, "对象解析器不能为空");
if(workTitle == null || workTitle.trim().equals("")){
workTitle = DEFAULT_WORK_TITLE;
}
out = response.getOutputStream();
response.setContentType("application/msexcel");// 定义输出类型
String fileName = workTitle;
//获取浏览器类型
String user_agent = request.getHeader("User-Agent").toLowerCase();
//为不同的浏览器,对文件名进行不同的编码转换
if(user_agent.indexOf("firefox") > 0){
fileName = new String(workTitle.getBytes("UTF-8"), "iso8859-1");
}else{
fileName = URLEncoder.encode(workTitle, "UTF-8");
}
response.setHeader("Content-disposition", "attachment; filename="
+ fileName + ".xls"); // 设定输出文件头
wbook = Workbook.createWorkbook(out); // 建立excel文件
WritableSheet wsheet;
if(workTitle != null && !workTitle.equals("")){
wsheet = wbook.createSheet(workTitle, 0); // 创建一个工作薄
}else{
wsheet = wbook.createSheet("", 0);
}
int headerIndex = 0; //记录表头所在的行
if(title != null && !title.equals("")){
headerIndex = 1;
// 设置excel标题
wsheet.addCell(new Label(0, 0, title, this.getTitleFormat().getTitleCellFormat()));
}
// 在一新行中, 为表格添加表头
if(columns != null){
for (int i = 0; i < columns.length; i++) {
if(widths != null && widths.length > 0){
if((i + 1) <= widths.length ){
wsheet.setColumnView(i, widths[i]);
}else{
wsheet.setColumnView(i, DEFAULT_COLUMN_WIDTH);
}
}else{
wsheet.setColumnView(i, DEFAULT_COLUMN_WIDTH);
}
wsheet.addCell(new Label(i, headerIndex, columns[i],this.getColumnFormat().getTitleCellFormat()));
}
}
//将集合中的数据添加到excel的工作表中
if(dataList != null){
for (int i = 0; i < dataList.size(); i++) {
Object obj = dataList.get(i);
objectMapper.toExcelRow(wsheet,this.dataFormat, i + headerIndex + 1, obj);
}
}
// 主体内容生成结束
wbook.write(); // 写入文件
return true;
} catch (Exception ex) {
ex.printStackTrace();
return false;
} finally {
if (wbook != null) {
try {
wbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
/**
* 导入Excel,将Excel 中的数据导入到List 中
* @param input
* 指定输入流
* @param index
* 工作博的序号 (从0开始)
* @param startIndex
* 指定从第几行开始读入 (第一行对应的index为0)
* @param rowMapper
* 行包装器(负责将一行数据转变成一个数据对象)
*/
@SuppressWarnings("unchecked")
public List getDataFromExcel(InputStream input, int index, int startIndex,
IRowMapper rowMapper) {
// TODO Auto-generated method stub
Workbook rwb = null;
try {
List list = new ArrayList();
rwb = Workbook.getWorkbook(input);
Assert.isTrue(index < rwb.getSheets().length && index >= 0, "为index指定的值 " + index + " 无效 ");
Sheet st = rwb.getSheet(index);
startIndex = (startIndex < 0)?0:startIndex;
Assert.isTrue(startIndex < st.getRows(), "为startIndex指定的值 " + startIndex + " 无效");
Assert.notNull(rowMapper, "行包装器没有指定");
for (int i = startIndex; i < st.getRows(); i++) {
Object obj = rowMapper.toObject(st, i);
if(obj != null){
list.add(obj);
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
}finally{
if(rwb != null){
rwb.close();
}
}
}
/**
* 导入Excel,将Excel中的数据导入到List 中
* @param input
* 指定输入流
* @param title
* 工作博的名称
* @param startIndex
* 指定从第几行开始读入(第一行对应的index为0)
* @param rowMapper
* 行包装器(负责将一行数据转变成一个数据对象)
*/
@SuppressWarnings("unchecked")
public List getDataFromExcel(InputStream input, String title, int startIndex,
IRowMapper rowMapper) {
Workbook rwb = null;
try {
List list = new ArrayList();
rwb = Workbook.getWorkbook(input);
Assert.notNull(title,"title参数不能为空");
Sheet st = rwb.getSheet(title);
Assert.notNull(st,"为title参数指定的值:" + title + "无效");
startIndex = (startIndex < 0)?0:startIndex;
Assert.isTrue(startIndex < st.getRows(), "为startIndex指定的值 " + startIndex + " 无效");
Assert.notNull(rowMapper, "行包装器没有指定");
for (int i = startIndex; i < st.getRows(); i++) {
Object obj = rowMapper.toObject(st, i);
if(obj != null){
list.add(obj);
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
}finally{
if(rwb != null){
rwb.close();
}
}
}
@SuppressWarnings("unchecked")
public void setTitleFormat(ITitleFormat titleFormat) {
this.titleFormat = titleFormat;
}
@SuppressWarnings("unchecked")
public void setColumnFormat(IColumnFormat columnFormat) {
this.columnFormat = columnFormat;
}
@SuppressWarnings("unchecked")
public void setDataFormat(IDataCellFormat dataFormat) {
this.dataFormat = dataFormat;
}
@SuppressWarnings("unchecked")
public ITitleFormat getTitleFormat() {
//如果没有设置工作表标题样式,就给它一个默认样式
if(this.titleFormat == null || this.titleFormat.getTitleCellFormat() == null){
this.titleFormat = DEFAULT_TITLE_FORMAT;
}
return titleFormat;
}
@SuppressWarnings("unchecked")
public IColumnFormat getColumnFormat() {
//如果没有设置工作表标题样式,就给它一个默认样式
if(this.columnFormat == null || this.columnFormat.getTitleCellFormat() == null){
this.columnFormat = DEFAULT_COLUMN_FORMAT;
}
return columnFormat;
}
@SuppressWarnings("unchecked")
public IDataCellFormat getDataFormat() {
if(this.dataFormat == null){
this.dataFormat = DEFAULT_DATACELL_FORMAT;
}
return dataFormat;
}
}
/*
* 该类负责将一个对象转变成Excel 中的一行数据
*/
public interface IObjectMapper {
/*
* 将obj 对象 转变成sheet工作表 中的第 rowNum行数据
* sheet 操作的工作表
* dataFormat 指定数据行 样式
* rowNum 表示 行号
* obj 要转换的对象
*/
public void toExcelRow(WritableSheet sheet,IDataCellFormat dataFormat, int rowNum,Object obj) throws RowsExceededException, WriteException;
}
/*
* Excel 行包装器, 负责将excel 中一行数据转变成一个对象
*/
public interface IRowMapper {
/*
* 定义如何将一个excel 中的一行 转变成一个对象,返回装有excel数据的对象
* sheet 操作的工作表
* rowNum 表示 行号
*/
public Object toObject(Sheet sheet,int rowNum) throws RowsExceededException, WriteException;
}
接下来是定义默认样式的类
//描述工作博单元格样式的根接口
public interface ICellFormat {
public WritableCellFormat getTitleCellFormat();
}
//该接口为 标识接口,所有描述工作博标题样式的类都要实现此接口
public interface ITitleFormat extends ICellFormat{
public WritableCellFormat getTitleCellFormat();
}
//该接口为 标识接口,所有描述工作博列名样式的类都要实现此接口
public interface IColumnFormat extends ICellFormat{
}
/*
* 该接口定义了如何为不同的数据类型指定不同的样式的方法
*/
public interface IDataCellFormat {
public WritableCellFormat getDataCellFormat(CellType type);
}
/*
* 定义一个工作博标题样式的默认实现
*/
@Component
public class DefaultTitleFormat implements ITitleFormat{
public WritableCellFormat getTitleCellFormat() {
// TODO Auto-generated method stub
WritableCellFormat wcf = null;
try {
//字体样式
WritableFont wf = new WritableFont(WritableFont.TIMES,20, WritableFont.BOLD,true);//最后一个为是否italic
wf.setColour(Colour.RED);
wcf = new WritableCellFormat(wf);
//对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框
wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
//背景色
wcf.setBackground(Colour.GREY_25_PERCENT);
} catch (WriteException e) {
e.printStackTrace();
}
return wcf;
}
}
/*
* 定义一个工作博表头样式的默认实现
*/
@Component
public class DefaultColumnFormat implements IColumnFormat{
@Override
public WritableCellFormat getTitleCellFormat() {
WritableCellFormat wcf = null;
try {
//字体样式
WritableFont wf = new WritableFont(WritableFont.TIMES,15, WritableFont.NO_BOLD,false);//最后一个为是否italic
wf.setColour(Colour.BLUE);
wcf = new WritableCellFormat(wf);
//对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框
wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
//背景色
wcf.setBackground(Colour.GREY_25_PERCENT);
} catch (WriteException e) {
e.printStackTrace();
}
return wcf;
}
}
/*
* 定义一个工作博数据行样式的默认实现
*/
@Component
public class DefaultDataCellFormat implements IDataCellFormat{
@Override
public WritableCellFormat getDataCellFormat(CellType type) {
// TODO Auto-generated method stub
WritableCellFormat wcf = null;
try {
//字体样式
if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//数字
NumberFormat nf = new NumberFormat("#.00");
wcf = new WritableCellFormat(nf);
}else if(type == CellType.DATE || type == CellType.DATE_FORMULA){//日期
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm");
wcf = new jxl.write.WritableCellFormat(df);
}else{
WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一个为是否italic
wcf = new WritableCellFormat(wf);
}
//对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框
wcf.setBorder(Border.LEFT,BorderLineStyle.THIN);
wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN);
wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN);
//背景色
wcf.setBackground(Colour.WHITE);
wcf.setWrap(true);//自动换行
} catch (WriteException e) {
e.printStackTrace();
}
return wcf;
}
}
用到第三方jar包:
spring3,jxl.jar
在spring mvc action里面如何使用?
@Controller
@RequestMapping(value = "/example/excel")
public class ExcelExampleAction {
private IBaseService<SysStudycentre> sysStudycentreService; // 定义学习中心服务引用
private JExcelOperator excelOperator;
// 注入名为sysStudycentreService 的IBaseService<SysStudycentre> 对象
@Resource(name = "sysStudycentreService")
public void setSysStudycentreService(IBaseService<SysStudycentre> sysStudycentreService) {
this.sysStudycentreService = sysStudycentreService;
}
// 注入名为 excelOperator 的JExcelOperator 对象
@Resource(name = "excelOperator")
public void setExcelOperator(JExcelOperator excelOperator) {
this.excelOperator = excelOperator;
}
// 导出excel
@RequestMapping(value = "getexcel")
@ResponseBody
@SuppressWarnings("unchecked")
public void exportToExcel(Model model,HttpServletRequest request,HttpServletResponse response) {
List<SysStudycentre> sysStudyCenters = this.sysStudycentreService.findAll();
//导出到excel,并将导出文件发往客户端
excelOperator.exportExcel(request,response, "学习中心",null, new String[]{"名称","联系人","联系方式","更新时间"},new int[]{10,20,30,30}, sysStudyCenters, new IObjectMapper(){
@Override
public void toExcelRow(WritableSheet sheet,
IDataCellFormat dataFormat, int rowNum, Object obj)
throws RowsExceededException, WriteException {
// TODO Auto-generated method stub
if(dataFormat != null){
//dataFormat.getDataCellFormat(CellType.STRING_FORMULA) 指定单元格 数据使用的格式
sheet.addCell(new Label(0, rowNum, ((SysStudycentre)obj).getFname(),dataFormat.getDataCellFormat(CellType.STRING_FORMULA))); // 学习中心名称
sheet.addCell(new Label(1, rowNum,((SysStudycentre)obj).getFcontactor(),dataFormat.getDataCellFormat(CellType.STRING_FORMULA) )); // 联系人
sheet.addCell(new Label(2, rowNum,((SysStudycentre)obj).getFmobilephone(),dataFormat.getDataCellFormat(CellType.NUMBER_FORMULA) )); // 联系方式
sheet.addCell(new Label(3, rowNum,((SysStudycentre)obj).getFupdatetime().toString(),dataFormat.getDataCellFormat(CellType.DATE_FORMULA) )); // 更新时间
}else{
sheet.addCell(new Label(0, rowNum, ((SysStudycentre)obj).getFname())); // 学习中心名称
sheet.addCell(new Label(1, rowNum,((SysStudycentre)obj).getFcontactor() )); // 联系人
sheet.addCell(new Label(2, rowNum,((SysStudycentre)obj).getFmobilephone() )); // 联系方式
sheet.addCell(new Label(3, rowNum,((SysStudycentre)obj).getFupdatetime().toString() )); // 更新时间
}
}
});
}
// 从excel 中导入数据
@RequestMapping(value = "exportToList")
@SuppressWarnings("unchecked")
public String exportToList(Model model,HttpServletRequest request,HttpServletResponse response) {
String filePath = "";
try{
//取得文件路径
filePath = request.getSession().getServletContext().getRealPath("") + "\\WEB-INF\\classes\\data\\studyCenter2.xls";
FileInputStream in = new FileInputStream(filePath);
List list = new JExcelOperator().getDataFromExcel(in, 0, 3, new IRowMapper(){
@Override
public Object toObject(Sheet sheet, int rowNum)
throws RowsExceededException, WriteException {
// TODO Auto-generated method stub
SysStudycentre studyCentre = new SysStudycentre();
studyCentre.setFname(sheet.getCell(0, rowNum).getContents());
studyCentre.setFcontactor(sheet.getCell(1, rowNum).getContents());
studyCentre.setFmobilephone(sheet.getCell(2, rowNum).getContents());
return studyCentre;
}
});
for(Object obj:list){
System.out.println(((SysStudycentre)obj).getFname());
}
//将集合存入model 中
model.addAttribute("studyCenterList",list);
return "example/exceldata";
} catch (FileNotFoundException e) {
request.setAttribute("message", filePath + "文件没找到");
return "example/error";
}
}
}