Java使用POI导出excel（下）——实例与小技巧
【更新】：thinkgem的导出工具类：
/** * Copyright © 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved. */ package com.thinkgem.jeesite.common.utils.excel; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.Collections; import java.util.Comparator; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; 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.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.google.common.collect.Lists; import com.thinkgem.jeesite.common.utils.Encodes; import com.thinkgem.jeesite.common.utils.Reflections; import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField; import com.thinkgem.jeesite.modules.sys.utils.DictUtils; /** * 导出Excel文件（导出“XLSX”格式，支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion） * @author ThinkGem * @version 2013-04-21 */ public class ExportExcel { private static Logger log = LoggerFactory.getLogger(ExportExcel.class); /** * 工作薄对象 */ private SXSSFWorkbook wb; /** * 工作表对象 */ private Sheet sheet; /** * 样式列表 */ private Map<String, CellStyle> styles; /** * 当前行号 */ private int rownum; /** * 注解列表（Object[]{ ExcelField, Field/Method }） */ List<Object[]> annotationList = Lists.newArrayList(); /** * 构造函数 * @param title 表格标题，传“空值”，表示无标题 * @param cls 实体对象，通过annotation.ExportField获取标题 */ public ExportExcel(String title, Class<?> cls){ this(title, cls, 1); } /** * 构造函数 * @param title 表格标题，传“空值”，表示无标题 * @param cls 实体对象，通过annotation.ExportField获取标题 * @param type 导出类型（1:导出数据；2：导出模板） * @param groups 导入分组 */ public ExportExcel(String title, Class<?> cls, int type, int... groups){ // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs){ ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type()==0 || ef.type()==type)){ if (groups!=null && groups.length>0){ boolean inGroup = false; for (int g : groups){ if (inGroup){ break; } for (int efg : ef.groups()){ if (g == efg){ inGroup = true; annotationList.add(new Object[]{ef, f}); break; } } } }else{ annotationList.add(new Object[]{ef, f}); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms){ ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type()==0 || ef.type()==type)){ if (groups!=null && groups.length>0){ boolean inGroup = false; for (int g : groups){ if (inGroup){ break; } for (int efg : ef.groups()){ if (g == efg){ inGroup = true; annotationList.add(new Object[]{ef, m}); break; } } } }else{ annotationList.add(new Object[]{ef, m}); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField)o1[0]).sort()).compareTo( new Integer(((ExcelField)o2[0]).sort())); }; }); // Initialize List<String> headerList = Lists.newArrayList(); for (Object[] os : annotationList){ String t = ((ExcelField)os[0]).title(); // 如果是导出，则去掉注释 if (type==1){ String[] ss = StringUtils.split(t, "**", 2); if (ss.length==2){ t = ss[0]; } } headerList.add(t); } initialize(title, headerList); } /** * 构造函数 * @param title 表格标题，传“空值”，表示无标题 * @param headers 表头数组 */ public ExportExcel(String title, String[] headers) { initialize(title, Lists.newArrayList(headers)); } /** * 构造函数 * @param title 表格标题，传“空值”，表示无标题 * @param headerList 表头列表 */ public ExportExcel(String title, List<String> headerList) { initialize(title, headerList); } /** * 初始化函数 * @param title 表格标题，传“空值”，表示无标题 * @param headerList 表头列表 */ private void initialize(String title, List<String> headerList) { this.wb = new SXSSFWorkbook(500); this.sheet = wb.createSheet("Export"); this.styles = createStyles(wb); // Create title if (StringUtils.isNotBlank(title)){ Row titleRow = sheet.createRow(rownum++); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1)); } // Create header if (headerList == null){ throw new RuntimeException("headerList not null!"); } Row headerRow = sheet.createRow(rownum++); headerRow.setHeightInPoints(16); for (int i = 0; i < headerList.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(styles.get("header")); String[] ss = StringUtils.split(headerList.get(i), "**", 2); if (ss.length==2){ cell.setCellValue(ss[0]); Comment comment = this.sheet.createDrawingPatriarch().createCellComment( new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); comment.setString(new XSSFRichTextString(ss[1])); cell.setCellComment(comment); }else{ cell.setCellValue(headerList.get(i)); } sheet.autoSizeColumn(i); } for (int i = 0; i < headerList.size(); i++) { int colWidth = sheet.getColumnWidth(i)*2; sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth); } log.debug("Initialize success."); } /** * 创建表格样式 * @param wb 工作薄对象 * @return 样式列表 */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); // style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; } /** * 添加一行 * @return 行对象 */ public Row addRow(){ return sheet.createRow(rownum++); } /** * 添加一个单元格 * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @return 单元格对象 */ public Cell addCell(Row row, int column, Object val){ return this.addCell(row, column, val, 0, Class.class); } /** * 添加一个单元格 * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @param align 对齐方式（1：靠左；2：居中；3：靠右） * @return 单元格对象 */ public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){ Cell cell = row.createCell(column); String cellFormatString = "@"; try { if(val == null){ cell.setCellValue(""); }else if(fieldType != Class.class){ cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val)); }else{ if(val instanceof String) { cell.setCellValue((String) val); }else if(val instanceof Integer) { cell.setCellValue((Integer) val); cellFormatString = "0"; }else if(val instanceof Long) { cell.setCellValue((Long) val); cellFormatString = "0"; }else if(val instanceof Double) { cell.setCellValue((Double) val); cellFormatString = "0.00"; }else if(val instanceof Float) { cell.setCellValue((Float) val); cellFormatString = "0.00"; }else if(val instanceof Date) { cell.setCellValue((Date) val); cellFormatString = "yyyy-MM-dd HH:mm"; }else { cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val)); } } if (val != null){ CellStyle style = styles.get("data_column_"+column); if (style == null){ style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:""))); style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString)); styles.put("data_column_" + column, style); } cell.setCellStyle(style); } } catch (Exception ex) { log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString()); cell.setCellValue(val.toString()); } return cell; } /** * 添加数据（通过annotation.ExportField添加数据） * @return list 数据列表 */ public <E> ExportExcel setDataList(List<E> list){ for (E e : list){ int colunm = 0; Row row = this.addRow(); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList){ ExcelField ef = (ExcelField)os[0]; Object val = null; // Get entity value try{ if (StringUtils.isNotBlank(ef.value())){ val = Reflections.invokeGetter(e, ef.value()); }else{ if (os[1] instanceof Field){ val = Reflections.invokeGetter(e, ((Field)os[1]).getName()); }else if (os[1] instanceof Method){ val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {}); } } // If is dict, get dict label if (StringUtils.isNotBlank(ef.dictType())){ val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), ""); } }catch(Exception ex) { // Failure to ignore log.info(ex.toString()); val = ""; } this.addCell(row, colunm++, val, ef.align(), ef.fieldType()); sb.append(val + ", "); } log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString()); } return this; } /** * 输出数据流 * @param os 输出数据流 */ public ExportExcel write(OutputStream os) throws IOException{ wb.write(os); return this; } /** * 输出到客户端 * @param fileName 输出文件名 */ public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{ response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName)); write(response.getOutputStream()); return this; } /** * 输出到文件 * @param fileName 输出文件名 */ public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{ FileOutputStream os = new FileOutputStream(name); this.write(os); return this; } /** * 清理临时文件 */ public ExportExcel dispose(){ wb.dispose(); return this; } // /** // * 导出测试 // */ // public static void main(String[] args) throws Throwable { // // List<String> headerList = Lists.newArrayList(); // for (int i = 1; i <= 10; i++) { // headerList.add("表头"+i); // } // // List<String> dataRowList = Lists.newArrayList(); // for (int i = 1; i <= headerList.size(); i++) { // dataRowList.add("数据"+i); // } // // List<List<String>> dataList = Lists.newArrayList(); // for (int i = 1; i <=1000000; i++) { // dataList.add(dataRowList); // } // // ExportExcel ee = new ExportExcel("表格标题", headerList); // // for (int i = 0; i < dataList.size(); i++) { // Row row = ee.addRow(); // for (int j = 0; j < dataList.get(i).size(); j++) { // ee.addCell(row, j, dataList.get(i).get(j)); // } // } // // ee.writeFile("target/export.xlsx"); // // ee.dispose(); // // log.debug("Export success."); // // } }
View Code
一、导出表格样式
前台使用easyui（此部分知识待补充）
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Basic DataGrid - jQuery EasyUI Demo</title> <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/default/easyui.css"> <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/icon.css"> <script type="text/javascript" src="jquery-easyui-1.3.3/jquery.min.js"></script> <script type="text/javascript" src="jquery-easyui-1.3.3/jquery.easyui.min.js"></script> <script type="text/javascript" src="jquery-easyui-1.3.3/locale/easyui-lang-zh_CN.js"></script> <script> var url; function deleteUser(){ var row=$(\'#dg\').datagrid(\'getSelected\'); if(row){ $.messager.confirm("系统提示","您确定要删除这条记录吗?",function(r){ if(r){ $.post(\'user!delete\',{delId:row.id},function(result){ if(result.success){ $.messager.alert("系统提示","已成功删除这条记录!"); $("#dg").datagrid("reload"); }else{ $.messager.alert("系统提示",result.errorMsg); } },\'json\'); } }); } } function newUser(){ $("#dlg").dialog(\'open\').dialog(\'setTitle\',\'添加用户\'); $(\'#fm\').form(\'clear\'); url=\'user!save\'; } function editUser(){ var row=$(\'#dg\').datagrid(\'getSelected\'); if(row){ $("#dlg").dialog(\'open\').dialog(\'setTitle\',\'编辑用户\'); $("#name").val(row.name); $("#phone").val(row.phone); $("#email").val(row.email); $("#qq").val(row.qq); url=\'user!save?id=\'+row.id; } } function saveUser(){ $(\'#fm\').form(\'submit\',{ url:url, onSubmit:function(){ return $(this).form(\'validate\'); }, success:function(result){ var result=eval(\'(\'+result+\')\'); if(result.errorMsg){ $.messager.alert("系统提示",result.errorMsg); return; }else{ $.messager.alert("系统提示","保存成功"); $(\'#dlg\').dialog(\'close\'); $("#dg").datagrid("reload"); } } }); } function exportUser(){ window.open(\'user!export\') } </script> </head> <body> <table id="dg" title="用户管理" class="easyui-datagrid" style="width:700px;height:365px" url="user!list" toolbar="#toolbar" pagination="true" rownumbers="true" fitColumns="true" singleSelect="true"> <thead> <tr> <th field="id" width="50" hidden="true">编号</th> <th field="name" width="50">姓名</th> <th field="phone" width="50">电话</th> <th field="email" width="50">Email</th> <th field="qq" width="50">QQ</th> </tr> </thead> </table> <div id="toolbar"> <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="newUser()">添加用户</a> <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true" onclick="editUser()">编辑用户</a> <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="deleteUser()">删除用户</a> <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser()">导出用户</a> </div> <div id="dlg" class="easyui-dialog" style="width:400px;height:250px;padding:10px 20px" closed="true" buttons="#dlg-buttons"> <form id="fm" method="post"> <table cellspacing="10px;"> <tr> <td>姓名：</td> <td><input id="name" name="user.name" class="easyui-validatebox" required="true" style="width: 200px;"></td> </tr> <tr> <td>联系电话：</td> <td><input id="phone" name="user.phone" class="easyui-validatebox" required="true" style="width: 200px;"></td> </tr> <tr> <td>Email：</td> <td><input id="email" name="user.email" class="easyui-validatebox" validType="email" required="true" style="width: 200px;"></td> </tr> <tr> <td>QQ：</td> <td><input id="qq" name="user.qq" class="easyui-validatebox" required="true" style="width: 200px;"></td> </tr> </table> </form> </div> <div id="dlg-buttons"> <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="saveUser()">保存</a> <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$(\'#dlg\').dialog(\'close\')">关闭</a> </div> </body> </html>
View Code
看导出部分：
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser()">导出用户</a>
function exportUser(){
window.open(\'user!export\')
}
后台采用的是struts2（稍旧）
public String export()throws Exception{ Connection con=null; try { con=dbUtil.getCon(); Workbook wb=new HSSFWorkbook(); String headers[]={"编号","姓名","电话","Email","QQ"}; ResultSet rs=userDao.userList(con, null); ExcelUtil.fillExcelData(rs, wb, headers); ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; }
这里我们采用值得借鉴的 util 的封装思想，封装两个 util ，编写相关的 静态方法！
ExcelUtil：
public class ExcelUtil {
public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
int rowIndex=0;
Sheet sheet=wb.createSheet();
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(headers[i]);
}
while(rs.next()){
row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
}
}
ResponseUtil:
public class ResponseUtil {
public static void write(HttpServletResponse response,Object o)throws Exception{
response.setContentType("text/html;charset=utf-8");
PrintWriter out=response.getWriter();
out.print(o.toString());
out.flush();
out.close();
}
public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
}
二、使用模板导出数据
首先在项目中放入一个模板（提前准备好的模板）
模板样式如下：
//大概原理就是读取模板，然后往里面塞数据（无需创建工作簿wb）
实际代码操作
前台页面还是采用上面的easyui添加模板导出按钮：
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser2()">用模版导出用户</a>
function exportUser2(){
window.open(\'user!export2\');
}
后台struts2不变
package com.java1234.action; import java.sql.Connection; import java.sql.ResultSet; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; import org.apache.struts2.ServletActionContext; import com.java1234.dao.UserDao; import com.java1234.model.PageBean; import com.java1234.model.User; import com.java1234.util.DbUtil; import com.java1234.util.ExcelUtil; import com.java1234.util.JsonUtil; import com.java1234.util.ResponseUtil; import com.java1234.util.StringUtil; import com.opensymphony.xwork2.ActionSupport; public class UserAction extends ActionSupport { /** * */ private static final long serialVersionUID = 1L; private String page; private String rows; private String id; private User user; private String delId; public String getPage() { return page; } public void setPage(String page) { this.page = page; } public String getRows() { return rows; } public void setRows(String rows) { this.rows = rows; } public String getDelId() { return delId; } public void setDelId(String delId) { this.delId = delId; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } public String getId() { return id; } public void setId(String id) { this.id = id; } DbUtil dbUtil=new DbUtil(); UserDao userDao=new UserDao(); public String list()throws Exception{ Connection con=null; PageBean pageBean=new PageBean(Integer.parseInt(page),Integer.parseInt(rows)); try{ con=dbUtil.getCon(); JSONObject result=new JSONObject(); JSONArray jsonArray=JsonUtil.formatRsToJsonArray(userDao.userList(con, pageBean)); int total=userDao.userCount(con); result.put("rows", jsonArray); result.put("total", total); ResponseUtil.write(ServletActionContext.getResponse(),result); }catch(Exception e){ e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String save()throws Exception{ if(StringUtil.isNotEmpty(id)){ user.setId(Integer.parseInt(id)); } Connection con=null; try{ con=dbUtil.getCon(); int saveNums=0; JSONObject result=new JSONObject(); if(StringUtil.isNotEmpty(id)){ saveNums=userDao.userModify(con, user); }else{ saveNums=userDao.userAdd(con, user); } if(saveNums>0){ result.put("success", "true"); }else{ result.put("success", "true"); result.put("errorMsg", "保存失败"); } ResponseUtil.write(ServletActionContext.getResponse(), result); }catch(Exception e){ e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String delete()throws Exception{ Connection con=null; try { con=dbUtil.getCon(); JSONObject result=new JSONObject(); int delNums=userDao.userDelete(con, delId); if(delNums==1){ result.put("success", "true"); }else{ result.put("errorMsg", "删除失败"); } ResponseUtil.write(ServletActionContext.getResponse(), result); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String export()throws Exception{ Connection con=null; try { con=dbUtil.getCon(); Workbook wb=new HSSFWorkbook(); String headers[]={"编号","姓名","电话","Email","QQ"}; ResultSet rs=userDao.userList(con, null); ExcelUtil.fillExcelData(rs, wb, headers); ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String export2()throws Exception{ Connection con=null; try { con=dbUtil.getCon(); ResultSet rs=userDao.userList(con, null); Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls"); ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } }
View Code
核心方法是：
public String export2()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
ResultSet rs=userDao.userList(con, null);
Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
模板稍有不同：
package com.java1234.util; import java.io.InputStream; import java.sql.ResultSet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class ExcelUtil { public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{ int rowIndex=0; Sheet sheet=wb.createSheet(); Row row=sheet.createRow(rowIndex++); for(int i=0;i<headers.length;i++){ row.createCell(i).setCellValue(headers[i]); } while(rs.next()){ row=sheet.createRow(rowIndex++); for(int i=0;i<headers.length;i++){ row.createCell(i).setCellValue(rs.getObject(i+1).toString()); } } } public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{ InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java1234/template/"+templateFileName); POIFSFileSystem fs=new POIFSFileSystem(inp); Workbook wb=new HSSFWorkbook(fs); Sheet sheet=wb.getSheetAt(0); // 获取列数 int cellNums=sheet.getRow(0).getLastCellNum(); int rowIndex=1; while(rs.next()){ Row row=sheet.createRow(rowIndex++); for(int i=0;i<cellNums;i++){ row.createCell(i).setCellValue(rs.getObject(i+1).toString()); } } return wb; } }
View Code
ExcelUtils:
public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java1234/template/"+templateFileName);
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
Sheet sheet=wb.getSheetAt(0);
// 获取列数
int cellNums=sheet.getRow(0).getLastCellNum();
int rowIndex=1;
while(rs.next()){
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<cellNums;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
return wb;
}
//注意流得读取形式
//注意是从第二列开始塞数据（第一列是表头）
ResponseUtils不变：
package com.java1234.util; import java.io.OutputStream; import java.io.PrintWriter; import javax.servlet.http.HttpServletResponse; import org.apache.poi.ss.usermodel.Workbook; public class ResponseUtil { public static void write(HttpServletResponse response,Object o)throws Exception{ response.setContentType("text/html;charset=utf-8"); PrintWriter out=response.getWriter(); out.print(o.toString()); out.flush(); out.close(); } public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{ response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1")); response.setContentType("application/ynd.ms-excel;charset=UTF-8"); OutputStream out=response.getOutputStream(); wb.write(out); out.flush(); out.close(); } }
View Code
三、导入用户
前端的对话框的代码：
<form id="uploadForm" action="user!upload" method="post" enctype="multipart/form-data">
<table>
<tr>
<td>下载模版：</td>
<td><a href="javascript:void(0)" class="easyui-linkbutton" onclick="downloadTemplate()">导入模版</a></td>
</tr>
<tr>
<td>上传文件：</td>
<td><input type="file" name="userUploadFile"></td>
</tr>
</table>
</form>
后台：
package com.java1234.action; import java.io.File; import java.io.FileInputStream; import java.sql.Connection; import java.sql.ResultSet; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Workbook; import org.apache.struts2.ServletActionContext; import com.java1234.dao.UserDao; import com.java1234.model.PageBean; import com.java1234.model.User; import com.java1234.util.DbUtil; import com.java1234.util.ExcelUtil; import com.java1234.util.JsonUtil; import com.java1234.util.ResponseUtil; import com.java1234.util.StringUtil; import com.opensymphony.xwork2.ActionSupport; public class UserAction extends ActionSupport { /** * */ private static final long serialVersionUID = 1L; private String page; private String rows; private String id; private User user; private String delId; private File userUploadFile; public String getPage() { return page; } public void setPage(String page) { this.page = page; } public String getRows() { return rows; } public void setRows(String rows) { this.rows = rows; } public String getDelId() { return delId; } public void setDelId(String delId) { this.delId = delId; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } public String getId() { return id; } public void setId(String id) { this.id = id; } public File getUserUploadFile() { return userUploadFile; } public void setUserUploadFile(File userUploadFile) { this.userUploadFile = userUploadFile; } DbUtil dbUtil=new DbUtil(); UserDao userDao=new UserDao(); public String list()throws Exception{ Connection con=null; PageBean pageBean=new PageBean(Integer.parseInt(page),Integer.parseInt(rows)); try{ con=dbUtil.getCon(); JSONObject result=new JSONObject(); JSONArray jsonArray=JsonUtil.formatRsToJsonArray(userDao.userList(con, pageBean)); int total=userDao.userCount(con); result.put("rows", jsonArray); result.put("total", total); ResponseUtil.write(ServletActionContext.getResponse(),result); }catch(Exception e){ e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String save()throws Exception{ if(StringUtil.isNotEmpty(id)){ user.setId(Integer.parseInt(id)); } Connection con=null; try{ con=dbUtil.getCon(); int saveNums=0; JSONObject result=new JSONObject(); if(StringUtil.isNotEmpty(id)){ saveNums=userDao.userModify(con, user); }else{ saveNums=userDao.userAdd(con, user); } if(saveNums>0){ result.put("success", "true"); }else{ result.put("success", "true"); result.put("errorMsg", "保存失败"); } ResponseUtil.write(ServletActionContext.getResponse(), result); }catch(Exception e){ e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String delete()throws Exception{ Connection con=null; try { con=dbUtil.getCon(); JSONObject result=new JSONObject(); int delNums=userDao.userDelete(con, delId); if(delNums==1){ result.put("success", "true"); }else{ result.put("errorMsg", "删除失败"); } ResponseUtil.write(ServletActionContext.getResponse(), result); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String export()throws Exception{ Connection con=null; try { con=dbUtil.getCon(); Workbook wb=new HSSFWorkbook(); String headers[]={"编号","姓名","电话","Email","QQ"}; ResultSet rs=userDao.userList(con, null); ExcelUtil.fillExcelData(rs, wb, headers); ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String export2()throws Exception{ Connection con=null; try { con=dbUtil.getCon(); ResultSet rs=userDao.userList(con, null); Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls"); ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { dbUtil.closeCon(con); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } public String upload()throws Exception{ POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(userUploadFile)); HSSFWorkbook wb=new HSSFWorkbook(fs); HSSFSheet hssfSheet=wb.getSheetAt(0); // 获取第一个Sheet页 if(hssfSheet!=null){ for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){ HSSFRow hssfRow=hssfSheet.getRow(rowNum); if(hssfRow==null){ continue; } User user=new User(); user.setName(ExcelUtil.formatCell(hssfRow.getCell(0))); user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1))); user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2))); user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3))); Connection con=null; try{ con=dbUtil.getCon(); userDao.userAdd(con, user); }catch(Exception e){ e.printStackTrace(); }finally{ dbUtil.closeCon(con); } } } JSONObject result=new JSONObject(); result.put("success", "true"); ResponseUtil.write(ServletActionContext.getResponse(), result); return null; } }
View Code
核心方法：
public String upload()throws Exception{
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(userUploadFile));
HSSFWorkbook wb=new HSSFWorkbook(fs);
HSSFSheet hssfSheet=wb.getSheetAt(0); // 获取第一个Sheet页
if(hssfSheet!=null){
for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
HSSFRow hssfRow=hssfSheet.getRow(rowNum);
if(hssfRow==null){
continue;
}
User user=new User();
user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
Connection con=null;
try{
con=dbUtil.getCon();
userDao.userAdd(con, user);
}catch(Exception e){
e.printStackTrace();
}finally{
dbUtil.closeCon(con);
}
}
}
JSONObject result=new JSONObject();
result.put("success", "true");
ResponseUtil.write(ServletActionContext.getResponse(), result);
return null;
}
其实核心呢就是获取到文件（上传的文件，进行遍历，加入数据库，再显示，注意去除表头，从第二行开始）
版权声明：本文为jiangbei原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。