一、jsp页面
1.导出按钮
<button type="button" id="export">导出</button>
2.搜索栏条件
<div class="input-group pull-right"> <div class="input-group"> <select id="deptId" name="deptId" class="input-sm form-control" style="width:50%;"> <option value>全部部门</option> <c:forEach items="${depts}" var="dept"> <option value="${dept.id}">${dept.name}</option> </c:forEach> </select> <input id="name" type="text" name="name" placeholder="姓名" class="input-sm form-control" style="width:50%;"> <span class="input-group-btn"> <button class="btn btn-sm btn-default action-refresh" type="button">搜索 </button> </span> </div>
二、js代码(将搜索栏的搜索条件通过js生成的表单提交给后台)
$('body').delegate("#export",'click',function () { var form = $('<form>'); form.hide(); form.attr('target','') form.attr("method",'post'); form.attr('action','wage/export'); form.append($('input[name= "name"]').clone()); form.append($("select[name = 'deptId']").clone()); $('body').append(form); form.submit(); form.remove(); });
三、后台controller
@RequestMapping("export") public void export(HttpServletRequest request, HttpServletResponse response ,Integer deptId ,String name){ String fileName = "月工资明细"; try { response.setContentType(request.getServletContext().getMimeType(fileName)); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls"); ExportWageToExcel.export(wageService.findByPageAndCondition(deptId,name,null,null), response.getOutputStream()); }catch (Exception e){ logger.error(e.getMessage(),e); } }说明:
wageService.findByPageAndCondition(deptId,name,null,null)返回的是要导出为Excel表的数据集合List<WageDTO> list;
四、ExportWageToExcel类
import com.jchvip.rch.dto.WageDTO; import com.jchvip.rch.exception.RCHException; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import java.io.OutputStream; import java.util.List; /** * @author Lix * @date 2018/1/24 15:20 */ public class ExportWageToExcel extends BaseExportToExcel { Logger logger = Logger.getLogger(ExportWageToExcel.class); static String[][] headers; @Override protected String[] getSheetNames() { return new String[]{"月工资明细"}; } @Override protected String[][] getHeaders() { headers = new String[][] {{" 所属部门 ","姓名","基薪","岗位","效益","职称津贴","奖金","补发工资","加班工资", "应发工资","扣失业保险","扣医疗保险","扣大额医疗","扣养老保险","扣住房公积金","扣企业年金(税前)", "扣个税","扣企业年金(税后)","实发工资"}}; return headers; } public static void export(List<WageDTO> list, OutputStream os) throws RCHException { ExportWageToExcel self = new ExportWageToExcel(); self.export();//画工作簿整体框架 HSSFSheet sheet = self.sheets[0];//获取工作表 int rows = 1;//工作表第0行是表头,所以内容的第一行的索引为1 //把list的内容画入工作表 for(WageDTO dto : list){ HSSFRow row = sheet.createRow(rows); addColumn(row,0,dto.getDeptNames()); addColumn(row,1,dto.getStaffName()); addColumn(row,2,dto.getBackPay()+""); addColumn(row,3,dto.getJob()+""); addColumn(row,4,dto.getMerit()+""); addColumn(row,5,dto.getPositional()+""); addColumn(row,6,dto.getBonus()+""); addColumn(row,7,dto.getBackPay()+""); addColumn(row,8,dto.getOvertime()+""); addColumn(row,9,dto.getTotal()+""); addColumn(row,10,dto.getUnemployment()+""); addColumn(row,11,dto.getMedical()+""); addColumn(row,12,dto.getLargeMedical()+""); addColumn(row,13,dto.getEndowment()+""); addColumn(row,14,dto.getHouse()+""); addColumn(row,15,dto.getAnnuityBeforTax()+""); addColumn(row,16,dto.getTax()+""); addColumn(row,17,dto.getAnnuityAfterTax()+""); addColumn(row,18,dto.getNetPay()+""); rows++; } for(int i = 0; i < headers[0].length; i++){ // sheet.autoSizeColumn(i,true); 自动列宽 //中文时自动列宽不起作用可用下面方法 headers[0][i]是列名 sheet.setColumnWidth(i, headers[0][i].getBytes().length*2*256); } try{ self.wb.write(os); os.close(); self.wb.close(); }catch (Exception e){ e.printStackTrace(); throw new RCHException("导出月工资明细失败!"); } } }
五、BaseExportToExcel类
import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.util.CellRangeAddress; public abstract class BaseExportToExcel { protected HSSFSheet[] sheets;//工作表数组 protected HSSFWorkbook wb;//工作簿 /* *画工作簿整体框架 * * */ protected void export() { wb = new HSSFWorkbook();//创建工作簿 String[] sheetNames = getSheetNames();//获得工作表表名称 String[][] headers = getHeaders();//获得每个工作表对应的列名 if (ValidateUtil.objectIsNull(sheets)) sheets = new HSSFSheet[sheetNames.length];//根据工作表名字的个数创建存放相应个数的工作表数组 for (int i = 0; i < sheetNames.length; i++) { HSSFSheet sheet = wb.createSheet(sheetNames[i]);//创建工作表 sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * 2));//设置行高 createHeader(sheet, headers[i]);//根据列名创建各个列 sheets[i] = sheet; } } protected abstract String[] getSheetNames(); protected abstract String[][] getHeaders(); /* * 根据列名数组创建各个列 * */ protected void createHeader(HSSFSheet sheet, String[] header) { HSSFRow row = sheet.createRow(0);//创建第一行 for (int i = 0; i < header.length; i++) { addColumn(row, i, header[i]);//根据列名创建列 } } /* * 合并单元格 * */ protected static void addRegion(HSSFSheet sheet, HSSFRow row, int rowFrom, int columnFrom, int rowTo, int columnTo, String value) { CellRangeAddress region = new CellRangeAddress(rowFrom, rowTo, columnFrom , columnTo); sheet.addMergedRegion(region); } protected static void addColumn(HSSFRow r, int column, String value) { HSSFCell cell = r.createCell(column);//创建单元格 HSSFCellStyle style = cell.getCellStyle();//创建单元格样式添加器 style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//设置垂直样式、垂直居中 cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格内容为字符串 cell.setCellValue(value);//设置单元格内容 } }