xml:
<select id="exportexcel" resultType="SystemManage.Pend.entity.Ppfoilsubstitutes" parameterType="java.util.Map">
select b.ppfoil_model as pdctno,a.* from ppfoilsubstitutes a
left join ppfoil b on a.ppfoil_id = b.ppfoil_id
<where>
<if test="fristdate != null and fristdate != ''">
<![CDATA[and a.ppfoilsubstitutes_indate >= #{fristdate}]]>
</if>
<if test="lastdate != null and lastdate != ''">
<![CDATA[and a.ppfoilsubstitutes_indate <= #{lastdate}]]>
</if>
</where>
order by a.ppfoilsubstitutes_id desc
</select>
dao:
public List<Pend> exportexcel(Map<String, Object> condition);
service:
public HSSFWorkbook exportexcel(Map<String, Object> condition){
List<Pend> list = pendDao.exportexcel(condition);
//定义表头
HSSFWorkbook wb = new HSSFWorkbook();
//生成一个工作表
HSSFSheet sheet = wb.createSheet("待料记录");
sheet.setColumnWidth(0, 2000);
sheet.setColumnWidth(1, 2000);
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 6000);
sheet.setColumnWidth(4, 3500);
sheet.setColumnWidth(5, 3500);
sheet.setColumnWidth(6, 4000);
sheet.setColumnWidth(7, 6000);
sheet.setColumnWidth(8, 6000);
sheet.setColumnWidth(9, 6000);
sheet.setColumnWidth(10, 3500);
sheet.setColumnWidth(11, 3000);
sheet.setColumnWidth(12, 4000);
sheet.setColumnWidth(13, 4000);
sheet.setColumnWidth(14, 12000);
sheet.setColumnWidth(15, 12000);
sheet.setColumnWidth(16, 4000);
sheet.setColumnWidth(17, 6000);
sheet.setColumnWidth(18, 4000);
sheet.setColumnWidth(19, 6000);
CellRangeAddress cra = new CellRangeAddress(0,0 , 0, 19);
sheet.addMergedRegion(cra);
//生成第一行
HSSFRow row = sheet.createRow(0);
//生成单元格的样式style
HSSFCellStyle style = wb.createCellStyle();
HSSFFont redFont = wb.createFont();
redFont.setFontHeightInPoints((short)20);
redFont.setBold(true);
style.setFont(redFont);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
row.setRowStyle(style);
row.setHeightInPoints(30);
HSSFCellStyle style1 = wb.createCellStyle();
HSSFFont redFont1 = wb.createFont();
redFont1.setBold(true);
redFont1.setFontHeightInPoints((short)11);
style1.setFont(redFont1);
style1.setAlignment(HorizontalAlignment.CENTER);
style1.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCellStyle style2 = wb.createCellStyle();
HSSFFont redFont2 = wb.createFont();
//字体大小
redFont2.setFontHeightInPoints((short)10);
style2.setFont(redFont2);
//自动换行
style2.setWrapText(true);
//水平
style2.setAlignment(HorizontalAlignment.CENTER);
//垂直
style2.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCellStyle style3 = wb.createCellStyle();
HSSFFont redFont3 = wb.createFont();
redFont3.setFontHeightInPoints((short)10);
style3.setFont(redFont3);
style3.setWrapText(true);
style3.setAlignment(HorizontalAlignment.LEFT);
style3.setVerticalAlignment(VerticalAlignment.TOP);
HSSFCellStyle style4 = wb.createCellStyle();
HSSFFont redFont4 = wb.createFont();
//字体大小
redFont4.setFontHeightInPoints((short)10);
style4.setFont(redFont4);
//自动换行
style4.setWrapText(true);
//水平
style4.setAlignment(HorizontalAlignment.CENTER);
//垂直
style4.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFDataFormat format= wb.createDataFormat();
style4.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
Cell cell = row.createCell(0);
cell.setCellValue("待料记录");
cell.setCellStyle(style);
String[] titles = {"序号","ID","添加地点","生产型号","板材型号","铜厚","板厚","交货时间","预计回厂时间","流程卡到采购部时间", "开料尺寸","数量","状态","是否替代料","录入备注","回厂备注","处理人","最后处理时间","录入人","录入时间"};
row = sheet.createRow(row.getRowNum() + 1);
for(int i=0;i<titles.length;i++){
Cell cell1 = row.createCell(i);
cell1.setCellValue(titles[i]);
cell1.setCellStyle(style1);
}
row.setHeightInPoints(20);
for (int i = 0; i < list.size(); i++) {
//得到当前行数的下一行(row.getRowNum():得到当前行数)
row = sheet.createRow(row.getRowNum() + 1);
Pend pend = list.get(i);
//赋值
row.setRowStyle(style2);
row.createCell(0).setCellValue(i+1);
row.getCell(0).setCellStyle(style2);
if(pend.getPend_id()!=null){
row.createCell(1).setCellValue(pend.getPend_id());
row.getCell(1).setCellStyle(style2);
}else{
row.createCell(1).setCellValue("");
row.getCell(1).setCellStyle(style2);
}
if(pend.getPend_type()!=null){
row.createCell(2).setCellValue(pend.getPend_type());
row.getCell(2).setCellStyle(style2);
}else{
row.createCell(2).setCellValue("仓库");
row.getCell(2).setCellStyle(style2);
}
if(pend.getPend_model()!=null){
row.createCell(3).setCellValue(pend.getPend_model());
row.getCell(3).setCellStyle(style2);
}else{
row.createCell(3).setCellValue("");
row.getCell(3).setCellStyle(style2);
}
if(pend.getPend_board()!=null){
row.createCell(4).setCellValue(pend.getPend_board());
row.getCell(4).setCellStyle(style2);
}else{
row.createCell(4).setCellValue("");
row.getCell(4).setCellStyle(style2);
}
if(pend.getPend_copper()!=null){
row.createCell(5).setCellValue(pend.getPend_copper());
row.getCell(5).setCellStyle(style2);
}else{
row.createCell(5).setCellValue("");
row.getCell(5).setCellStyle(style2);
}
if(pend.getPend_thick()!=null){
row.createCell(6).setCellValue(pend.getPend_thick());
row.getCell(6).setCellStyle(style2);
}else{
row.createCell(6).setCellValue("");
row.getCell(6).setCellStyle(style2);
}
if(pend.getPend_deliverytime()!=null){
row.createCell(7).setCellValue(pend.getPend_deliverytime());
row.getCell(7).setCellStyle(style4);
}else{
row.createCell(7).setCellValue("");
row.getCell(7).setCellStyle(style2);
}
if(pend.getPend_estimate()!=null){
row.createCell(8).setCellValue(pend.getPend_estimate());
row.getCell(8).setCellStyle(style4);
}else{
row.createCell(8).setCellValue("");
row.getCell(8).setCellStyle(style2);
}
if(pend.getPend_purchasetime()!=null){
row.createCell(9).setCellValue(pend.getPend_purchasetime());
row.getCell(9).setCellStyle(style4);
}else{
row.createCell(9).setCellValue("");
row.getCell(9).setCellStyle(style2);
}
if(pend.getPend_size()!=null){
row.createCell(10).setCellValue(pend.getPend_size());
row.getCell(10).setCellStyle(style2);
}else{
row.createCell(10).setCellValue("");
row.getCell(10).setCellStyle(style2);
}
if(pend.getPend_number()!=null){
row.createCell(11).setCellValue(pend.getPend_number());
row.getCell(11).setCellStyle(style2);
}else{
row.createCell(11).setCellValue("");
row.getCell(11).setCellStyle(style2);
}
if(pend.getPend_state()!=null){
if(pend.getPend_state()==1){
row.createCell(12).setCellValue("待提交");
}else if(pend.getPend_state()==2){
row.createCell(12).setCellValue("待接收");
}else if(pend.getPend_state()==3){
row.createCell(12).setCellValue("待完成");
}else if(pend.getPend_state()==4){
row.createCell(12).setCellValue("已完成");
}else if(pend.getPend_state()==5){
row.createCell(12).setCellValue("延期申请中");
}else if(pend.getPend_state()==6){
row.createCell(12).setCellValue("已延期");
}else if(pend.getPend_state()==7){
row.createCell(12).setCellValue("已结束");
}else {
row.createCell(12).setCellValue("已删除");
}
row.getCell(12).setCellStyle(style2);
}else{
row.createCell(12).setCellValue("");
row.getCell(12).setCellStyle(style2);
}
if(pend.getPend_substitutes()!=null){
if(pend.getPend_substitutes().equals("是")){
row.createCell(13).setCellValue("是");
}else {
row.createCell(13).setCellValue("否");
}
row.getCell(13).setCellStyle(style2);
}else{
row.createCell(13).setCellValue("否");
row.getCell(13).setCellStyle(style2);
}
if(pend.getPend_manualremarks()!=null){
row.createCell(14).setCellValue(pend.getPend_manualremarks());
row.getCell(14).setCellStyle(style3);
}else{
row.createCell(14).setCellValue("");
row.getCell(14).setCellStyle(style2);
}
if(pend.getPend_remarks()!=null){
row.createCell(15).setCellValue(pend.getPend_remarks());
row.getCell(15).setCellStyle(style3);
}else{
row.createCell(15).setCellValue("");
row.getCell(15).setCellStyle(style2);
}
if(pend.getPend_handler()!=null){
row.createCell(16).setCellValue(pend.getPend_handler());
row.getCell(16).setCellStyle(style2);
}else{
row.createCell(16).setCellValue("");
row.getCell(16).setCellStyle(style2);
}
if(pend.getPend_handlertime()!=null){
row.createCell(17).setCellValue(pend.getPend_handlertime());
row.getCell(17).setCellStyle(style4);
}else{
row.createCell(17).setCellValue("");
row.getCell(17).setCellStyle(style2);
}
if(pend.getPend_createname()!=null){
row.createCell(18).setCellValue(pend.getPend_createname());
row.getCell(18).setCellStyle(style2);
}else{
row.createCell(18).setCellValue("");
row.getCell(18).setCellStyle(style2);
}
if(pend.getPend_createtime()!=null){
row.createCell(19).setCellValue(pend.getPend_createtime());
row.getCell(19).setCellStyle(style4);
}else{
row.createCell(19).setCellValue("");
row.getCell(19).setCellStyle(style2);
}
}
return wb;
}
controller:
@RequestMapping("/exportexcel")
@ResponseBody
public void exportexcel(Pend pend, HttpServletResponse response, HttpSession session) throws IOException {
Map<String, Object> cond = new HashMap<String, Object>();
cond.put("pend","pend");
cond.put("pendname",(String)session.getAttribute("name"));
cond.put("pend_model",pend.getPend_model());
cond.put("pend_board",pend.getPend_board());
cond.put("pend_copper",pend.getPend_copper());
cond.put("pend_thick",pend.getPend_thick());
cond.put("pend_state",pend.getPend_state());
cond.put("deliverfristdate",pend.getDeliverfristdate());
cond.put("deliverlastdate",pend.getDeliverlastdate());
cond.put("pend_remarks",pend.getPend_remarks());
cond.put("fristdate",pend.getFristdate());
cond.put("lastdate",pend.getLastdate());
HSSFWorkbook wb = pendService.exportexcel(cond);
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
String name = "待料记录";
response.setHeader("Content-disposition", "attachment;filename=" + new String( name.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}