java Excel导出，
分享于 点击 9360 次 点评：17
java Excel导出，
1、需求效果
2、大体数据结构
3、核心代码
function exportE(){
var schId = $.url().param('sid');
var yearIn = $(".filter_yearIn").find("option:selected").val(),
cls = $(".filter_cls").find("option:selected").val();
if( schId == null || yearIn == null || cls == null){
$('.parent_list').html('<tr><td>没有检索到数据.</td></tr>');
return false;
}
var url = "../sch/fm/export/"+schId+'/'+yearIn+'/'+cls;
url = encodeURI(url);
location.href = url;
} /**
*
* @Title: export
* @Description:导出学生家长信息
* @param schId
* @param clsId
* @param request
* @return 参数
* @return JSONObject
* @throws
*/
@RequestMapping(value="/fm/export/{schId}/{yearIn}/{clsId}",method=RequestMethod.GET)
@ResponseBody
public JSONObject exportE(
@PathVariable(value="schId") Long schId,
@PathVariable(value="yearIn") Integer yearIn,
@PathVariable(value="clsId") Long clsId,
HttpServletRequest request ,
HttpServletResponse response){
try{
JSONArray jarr = new JSONArray();
jarr = pubStu.getFalmily( schId,yearIn);
for( int i = 0; i < jarr.size(); i++){
Long rtime = jarr.getJSONObject(i).getLong("regTime");
if( rtime == null ) continue;
jarr.getJSONObject(i).put("regTime", Utils.long2Date(rtime ));
}
String sch = clsId+"";
String schNum = sch.substring(0, sch.length()-3);
schFamImpl schFam = new schFamImpl();
schFam.exportExcel(request, response, jarr, schNum);
return getSuccessResult( jarr );
}catch(Exception e){
e.printStackTrace();
return getErrorResult("家长数据获取失败，请稍后重试.");
}
}package com.cicada.cube.bo.union.impl;
import java.io.IOException;
import java.io.OutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
public class schFamImpl {
/**
*
* @Title: exportExcel
* @Description: 导出Excel
* @param param
* @return 参数
* @return void
* @throws
*/
public void exportExcel(HttpServletRequest request,
HttpServletResponse response, JSONArray jarr, String schNum) throws Exception {
try {
//第一步，创建一个workbook对应一个excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
//第二部，在workbook中创建一个sheet对应excel中的sheet
HSSFSheet sheet = workbook.createSheet(schNum+"级学生家长信息");
//第三部，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
HSSFRow row = sheet.createRow(0);
//第四步，创建单元格，设置表头
HSSFCell cell = row.createCell(0);
cell.setCellValue("班级");
cell = row.createCell(1);
cell.setCellValue("学号");
cell = row.createCell(2);
cell.setCellValue("学生");
cell = row.createCell(3);
cell.setCellValue("家长");
cell = row.createCell(4);
cell.setCellValue("手机");
cell = row.createCell(5);
cell.setCellValue("注册时间");
cell = row.createCell(6);
cell.setCellValue("注册状态");
//第五步，写入实体数据，实际应用中这些数据从数据库得到,对象封装数据，集合包对象。对象的属性值对应表的每行的值
int index = 1;
for (int i = 0; i < jarr.size(); i++) {
JSONObject famData = (JSONObject) jarr.get(i);
JSONArray child = (JSONArray) famData.get("child");
for(int j=0; j<child.size(); j++){
HSSFRow row1 = sheet.createRow(index++);
JSONObject childData = (JSONObject) child.get(j);
String classId = (childData.get("classId").toString());
String classNum = classId.substring(classId.length()-3, classId.length());
int num = Integer.parseInt(classNum);
if(num<10){
String Class = classId.substring(classId.length()-1, classId.length());
row1.createCell(0).setCellValue(Class);
}else if(num>=10 && num <100){
String Class = classId.substring(classId.length()-2, classId.length());
row1.createCell(0).setCellValue(Class);
}else if(num>=100){
row1.createCell(0).setCellValue(classNum);
}
row1.createCell(1).setCellValue(childData.get("stuCode").toString());
row1.createCell(2).setCellValue(childData.get("stuAlias").toString());
row1.createCell(3).setCellValue(famData.get("famName").toString());
row1.createCell(4).setCellValue(famData.get("phone").toString());
if(famData.get("regTime") != "" && famData.get("regTime") != null){
row1.createCell(5).setCellValue(famData.get("regTime").toString());
row1.createCell(6).setCellValue("已注册");
}else{
row1.createCell(5).setCellValue("");
row1.createCell(6).setCellValue("未注册");
}
}
}
//将文件保存到指定的位置
try {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="+ new String((schNum+"级学生家长信息" + ".xls").getBytes(), "iso-8859-1"));
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
} catch (Exception e) {
throw new Exception("Export Excel failed, beacause"
+ e.getMessage());
}
}
}/**
*
* @Title: getFalmily
* @Description: 获取年级级所有学生家长的信息
* @param schId
* @param clsId
* @return
* @throws Exception 参数
* @return JSONArray
* @throws
*/
public JSONArray getFalmily( Long schId, Integer yearIn) throws Exception; /**
*
* @Title: getFalmily
* @Description: 获取年级所有学生家长的信息
* @param schId
* @param clsId
* @return
* @throws Exception 参数
* @return JSONArray
* @throws
*/
public JSONArray getFalmily( Long schId, Integer yearIn) throws Exception{
logger.info( "Get student parent's information by student's id.");
try{
JSONArray schJarr = getStudents( schId);
JSONArray stuJarr = getStudents( schId, yearIn);
if(stuJarr.isEmpty() ) return new JSONArray();
Map<Long, String> stuMap = new HashMap<Long, String>();
List<Long> stuGids = new ArrayList<Long>();
for( int i = 0; i < stuJarr.size(); i++){
JSONObject jo = stuJarr.getJSONObject(i);
stuGids.add( jo.getLong("stuGid"));
}
//为了获取家长全部关联的孩子，限制在一个学校内的
for( int i = 0; i < schJarr.size(); i++){
JSONObject jo = schJarr.getJSONObject(i);
stuMap.put( jo.getLong("stuGid"), jo.getString( "stuAlias")+"|"+jo.getString( "classId")+"|"+jo.getString( "yearIn")+"|"+( jo.getString( "stuCode")==null?"-1": jo.getString( "stuCode") ) );
}
QryDomainFilter qd = new QryDomainFilter();
qd.setIndex( IndexBox.INDEX_BASE_BUSINESS_OBJECT );
qd.setTypes( BBOConst.BBO_FAMILY );
qd.setSelect( PubConst.EVE_ALL );
qd.setCondition( Utils.getQryIn( stuGids, "stuGids"));
//把关联学生信息带上
JSONArray jarr = statCustom.getStat( qd);
for( int i = 0; i < jarr.size(); i++){
JSONArray sJarr = new JSONArray();
Object[] stus = jarr.getJSONObject(i).getJSONArray("stuGids").toArray();
for( int j = 0; j < stus.length; j++){
if( stuMap.containsKey( stus[j])){
JSONObject sjo = new JSONObject();
sjo.put("stuGid", stus[j]);
sjo.put("stuAlias", stuMap.get( stus[j] ).split("\\|")[0] );
sjo.put("classId", stuMap.get( stus[j] ).split("\\|")[1] );
sjo.put("stuYearIn", stuMap.get( stus[j] ).split("\\|")[2] );
String stuCode = stuMap.get( stus[j] ).split("\\|")[3] ;
if( stuCode.equalsIgnoreCase("-1")) stuCode="";
sjo.put("stuCode", stuCode);
sJarr.add(sjo);
}
}
jarr.getJSONObject(i).put("child", sJarr);
}
return jarr;
}catch( Exception e){
e.printStackTrace();
throw new Exception( e.getCause());
}
}相关文章
- 暂无相关文章
用户点评