发表文章

[最新] 从后台获取数据,并导出EXCEL文件

weixin42517975 1月前 0

从后台获取数据,并导出EXCEL文件

Jsp页面:

<a href="javascript:;" id="exportExcel" class="easyui-linkbutton" data-options="iconCls:'icon-export-excel',size:'normal'" plain="true">

<span class="button-text">excel数据导出</span></a>

 

 

//导出excel

$("#exportExcel").click(function(){

//房帖历史记录excel

$.ajax({

"url":"<%=basePath%>/ftbdlsExcel.action",

"type":"post",

datatype:"json",

data:{

scgzksj: $("#SCGZKSJ").val(),

rybh: $("#STAFF_ID").val(),

dw:dwid,

xm: $("#NAME").val()

},

 success:function(filedownloadpath){

StandardPost('<%=basePath%>/fileDownload',{'path':filedownloadpath};

top.$.messager.alert("系统提示","导出成功!","info");

 }

});

});

Struts.Xml

<!--房帖历史记录 导出excel  -->

<action name="ftbdlsExcel" class="salaryManagementAction" method="ftbdlsExcel">

<result type="json">

<param name="root">filedownloadpath</param>

</result>

</action>

Action代码

/**

 * 房帖调整历史记录 信息导出excel

 */

public String ftbdlsExcel() {

String[] title = new String[9];

title[0] = "人员编号";

title[1] = "姓名";

title[2] = "单位";

title[3] = "校内房帖时间";

title[4] = "原房帖";

title[5] = "现房帖";

title[6] = "补发";

title[7] = "备注";

title[8] = "房帖调整时间";

try {

HSSFWorkbook wk = new HSSFWorkbook();

// 建立sheet

HSSFSheet sheet = wk.createSheet("房帖调整历史记录");

// 统一单元格高度

sheet.setDefaultRowHeight((short) 200);

// 设置列宽

sheet.setColumnWidth(0, 3500);

sheet.setColumnWidth(1, 3500);

sheet.setColumnWidth(2, 3500);

sheet.setColumnWidth(3, 3500);

sheet.setColumnWidth(4, 4500);

sheet.setColumnWidth(5, 3500);

sheet.setColumnWidth(6, 3500);

sheet.setColumnWidth(7, 25000);

sheet.setColumnWidth(8, 4500);

// 创建第一行

HSSFRow row0 = sheet.createRow(0);

// 设置行高

row0.setHeight((short) (550));

 

// 设置第一行的格式

HSSFCellStyle headstyle1 = wk.createCellStyle();

HorizontalAlignment align = null;

 

// 第一行 左右居中

headstyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 上下居中

headstyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// 字体

HSSFFont headfont1 = wk.createFont();

headfont1.setFontName("宋体");

// 字体大小

headfont1.setFontHeightInPoints((short) 11);

// 加粗

headfont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

headstyle1.setFont(headfont1);

 

// 其他行格式

HSSFCellStyle style = wk.createCellStyle();

// 左右居中

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 上下居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// 字体

HSSFFont font = wk.createFont();

font.setFontName("宋体");

// 字体大小

font.setFontHeightInPoints((short) 10);

style.setFont(font);

// 第一行内容设置

for (int i = 0; i < title.length; i++) {

HSSFCell cell = row0.createCell(i);

cell.setCellStyle(headstyle1);

cell.setCellValue(new HSSFRichTextString(title[i]));

}

// 创建第二行

List<GZ_FTTZ_2017> ftlslist = salaryManagementService.getftbdlsInfo(scgzksj, rybh, xm, dw);

for (int i = 0; i < ftlslist.size(); i++) {

HSSFRow row = sheet.createRow(i + 1);

row.setHeightInPoints(30);

for (int j = 0; j < 9; j++) {

HSSFCell cell = row.createCell(j);

cell.setCellStyle(style);

if (j == 0) {

cell.setCellValue(ftlslist.get(i).getSTAFF_ID());

} else if (j == 1) {

cell.setCellValue(ftlslist.get(i).getNAME());

} else if (j == 2) {

cell.setCellValue(ftlslist.get(i).getCOLLEGE_ID());

} else if (j == 3) {

cell.setCellValue(ftlslist.get(i).getXNFTSJ());

} else if (j == 4) {

cell.setCellValue(ftlslist.get(i).getJFT());

} else if (j == 5) {

cell.setCellValue(ftlslist.get(i).getXFT());

} else if (j == 6) {

cell.setCellValue(ftlslist.get(i).getBFFT());

} else if (j == 7) {

cell.setCellValue(ftlslist.get(i).getBZ());

} else if (j == 8) {

cell.setCellValue(ftlslist.get(i).getFTTZSJ());

}

}

}

 

Date now = new Date();

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");

String nowtime = sdf.format(now);

String filename = "房帖调整历史" + nowtime + ".xls";

String filenameandpath = "D:\\工资变动历史记录\\导出Excel\\";

File file = new File(filenameandpath);

if (!file.exists()) {

file.mkdirs();

}

filenameandpath += filename;

FileOutputStream fileoutputstream = new FileOutputStream(

filenameandpath);// 建立文件输出流

ByteArrayOutputStream os = new ByteArrayOutputStream();

wk.write(os);

byte tag_bytes[] = os.toByteArray();

fileoutputstream.write(tag_bytes);

fileoutputstream.close();

filedownloadpath = filenameandpath;

 

return "success";

} catch (Exception e) {

e.printStackTrace();

return "error";

}

}

 

 

Dao

 

/**

 * 房帖变动 历史信息查询 数据显示(不分页)

 */

public List<GZ_FTTZ_2017> getftbdlsInfo(String scgzksj, String rybh, String xm,

String dw) throws Exception {

List<GZ_FTTZ_2017> list = new ArrayList<GZ_FTTZ_2017>();

String sql = "";

List<DitZdb> ditzdblist = new ArrayList<DitZdb>();

try {

if(dw != null){

sql = "select cdmc from dit_zdb where zdid = '10026' and cdid = '"+dw+"'";

ditzdblist = getJdbcTemplate().query(

sql,

BeanPropertyRowMapper

.newInstance(DitZdb.class));

dw = ditzdblist.get(0).getCdmc();

}

sql = "select GZ.COLLEGE_ID,GZ.NAME,GZ.STAFF_ID,GZ.XNFTSJ,GZ.JFT,GZ.XFT,GZ.BFFT,"

+ "GZ.BZ,GZ.FTTZSJ "

+ "from GZ_FTTZ_2017 GZ";

sql += " where 1=1";

if (dw != null && !"".equals(dw)) {

sql += " and  GZ.COLLEGE_ID ='" + dw + "' ";

}

if (rybh != null && !"".equals(rybh)) {

sql += " and  GZ.STAFF_ID like '%" + rybh + "%'";

} else if (xm != null && !"".equals(xm)) {

sql += " and GZ.NAME like '%" + xm + "%'";

}

if (scgzksj != null && !"".equals(scgzksj)) {

sql += " and GZ.FTTZSJ = '" + scgzksj + "'";

}

list = getJdbcTemplate().query(sql,BeanPropertyRowMapper.newInstance(GZ_FTTZ_2017.class));

} catch (Exception e) {

e.printStackTrace();

}

return list;

}


相关推荐
最新评论 (0)
返回
发表文章
weixin42517975
文章数
10
评论数
0
注册排名
1322703