报表中的导出操作
程序代码：function opena(){
window.location.href= "<%=strContext%>/pretreament/exportAction.do?method=resourceExport";
}
<!-- 点击按钮进入opena()方法 ,触发action -->
<DIV class=r><font style="cursor:hand;" onClick="opena()">查看原始数据</font></DIV>
//standardindex.jsp页面跳转,导出数据action
public ActionForward resourceExport(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
try {
String med_type = (String) request.getSession(true).getAttribute("med_type");
String taskId = (String) request.getSession(true).getAttribute("taskId");
String department = (String) request.getSession(true).getAttribute("department");
System.out.println("in the resourceExp****"+med_type+"**"+taskId+"**"+department);
String filePath = request.getRealPath("/")
+ "/jsp/pretreament/datastandard/export/原始数据.xls";
//导出excel表
biz.resourceExport(filePath, taskId, med_type,department);
request.setAttribute("filePath", filePath);
request.setAttribute("fileName", "原始数据.xls");
} catch (Exception e) {
e.printStackTrace();
}
return new ActionForward("/exportAction.do?method=download");
}
//文件下载action
public ActionForward download(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
BufferedOutputStream bos = null;
String filePath = (String) request.getAttribute("filePath");
String filename = (String) request.getAttribute("fileName");
StringBuffer sb = new StringBuffer(50);
sb.append("attachment; filename=");
sb.append(filename);
try {
if (null != filePath && filename != null) {
response.setContentType("application/x-msdownload;charset=GBK");
response.setHeader("Content-Disposition", new String(sb
.toString().getBytes(), "ISO8859-1"));
FileInputStream fis = new FileInputStream(filePath);
bos = new BufferedOutputStream(response.getOutputStream());
byte[] buffer = new byte[2048];
while (fis.read(buffer) != -1) {
bos.write(buffer);
}
bos.write(buffer, 0, buffer.length);
fis.close();
bos.close();
}
File fs = new File(filePath);
if (fs.isFile() && fs.exists()) {
fs.delete();
System.out.println("删除单个文件" + filename + "成功！");
} else {
System.out.println("删除单个文件" + filename + "失败！");
}
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
public void resourceExport(String filePath, String taskId, String med_type,
String department) {
// TODO 自动生成的方法存根
try{
// 创建新的Excel 工作簿
Excel ex = new Excel();
HSSFWorkbook workbook = new HSSFWorkbook(); //workbook 工作簿 createSheet
// 在Excel工作簿中建一工作表，其名为缺省值 //sheet 工作表 createRow
HSSFSheet sheet = null; //row 一行 createCell
HSSFRow row = null; //cell 单元格 最小单位
HSSFCell cell = null;
IStandardDAO sDao = new StandardDAOImpl();
List list3 = new ArrayList();
String[] top_arraydis = null;
sheet = workbook.createSheet("药品批发购进和销售价");
//为导出的excel表提供数据
list3 = sDao.getPriceLinkData(taskId,3,med_type,department);
System.out.println("WholeSaleDatalist====>>>"+list3.size());
top_arraydis =ExcelColumns.PRE_WHOLESALE_TOP;
row = sheet.createRow(0);
for (int c = 0; c < top_arraydis.length; c++) {
cell = row.createCell(c);// 创建格 字段
cell.setCellValue(top_arraydis[c]);
}
int j =0;
int count3 =list3.size();
for (int i =0; i<count3;i++){
ResourceData d = (ResourceData) list3.get(i);
row = sheet.createRow(i+1);
cell =row.createCell(j);
cell.setCellValue(i+1); //创建格 字段 序号
cell =row.createCell(++j);
cell.setCellValue(d.getRep_medname());
cell =row.createCell(++j);
cell.setCellValue(d.getRepMed_type());
cell =row.createCell(++j);
cell.setCellValue(d.getRepMednum());
cell =row.createCell(++j);
cell.setCellValue(d.getRep_spec());
cell =row.createCell(++j);
cell.setCellValue(d.getRep_standard());
cell =row.createCell(++j);
cell.setCellValue(d.getRep_pro());
cell =row.createCell(++j);
cell.setCellValue(d.getRetailunit());
cell =row.createCell(++j);
cell.setCellValue(d.getSell_num());
cell =row.createCell(++j);
cell.setCellValue(d.getSell_price());
cell =row.createCell(++j);
cell.setCellValue(d.getPur_num());
cell =row.createCell(++j);
cell.setCellValue(d.getPur_price());
cell =row.createCell(++j);
cell.setCellValue(d.getSubmit_com());
cell =row.createCell(++j);
cell.setCellValue(d.getSurvey_year());
cell =row.createCell(++j);
cell.setCellValue(d.getData_region());
cell = row.createCell(++j);
cell.setCellValue(d.getMed_type());
cell =row.createCell(++j);
cell.setCellValue(d.getMed_num());
cell = row.createCell(++j);
cell.setCellValue(d.getMedname());
cell = row.createCell(++j);
cell.setCellValue(d.getSpec());
cell = row.createCell(++j);
cell.setCellValue(d.getContent());
cell = row.createCell(++j);
cell.setCellValue(d.getContentunit());
cell = row.createCell(++j);
cell.setCellValue(d.getDosage());
cell = row.createCell(++j);
cell.setCellValue(d.getDosageunit());
cell = row.createCell(++j);
cell.setCellValue(d.getScalar());
cell = row.createCell(++j);
cell.setCellValue(d.getScalarunit());
cell = row.createCell(++j);
cell.setCellValue(d.getProduce_com());
cell = row.createCell(++j);
cell.setCellValue(d.getCharact());
cell = row.createCell(++j);
cell.setCellValue(d.getWrapper());
cell = row.createCell(++j);
cell.setCellValue(d.getRest());
cell = row.createCell(++j);
cell.setCellValue(d.getProvince());
cell = row.createCell(++j);
cell.setCellValue(d.getDepartment());
cell = row.createCell(++j);
cell.setCellValue(d.getIs_yuanyan());
cell = row.createCell(++j);
cell.setCellValue(d.getIs_dddj());
cell = row.createCell(++j);
cell.setCellValue(d.getIs_tydj());
j=0;
}
public List getPriceLinkData(String taskId,int priceLink,String med_type,String department) throws Exception {
// TODO 自动生成的方法存根
List list =new ArrayList();
int Id =Integer.parseInt(taskId);
ResourceData data = null;
String sql ="select t1.RESOURCE_ID, t1.COLLTASK_ID, t1.COLLTASK_NAME, t1.DATA_YEAR, t1.DATA_REGION, t1.PRICE_LINK, t1.REP_MEDNAME, t1.REP_SPEC, " +
" t1.REP_STANDARD, t1.REP_PRO, t1.PRODUCE_COST, t1.TIME_COST," +
" t1.PUR_PRICE, t1.SELL_PRICE, t1.MED_NUM, t1.MED_TYPE, t1.SUBMIT_COM, t1.RETAILUNIT,t1.SURVEY_YEAR, t1.BIDDING_TIME, t1.SUBMIT_TIME," +
" t1.RECORD_PRICE, t1.RECORDPRICE_YEAR,t2.med_type,t2.MEDNUM,t2.MEDNAME,t2.SPEC,t2.content,t2.CONTENTUNIT, t2.DOSAGE, t2.DOSAGEUNIT, t2.SCALAR, t2.SCALARUNIT, t2.PRODUCE_COM, " +
" t2.CHARACT, t2.WRAPPER, t2.REST, t2.DEPARTMENT, t2.PROVINCE_NAME,t2.IS_YUANYAN, t2.IS_DDDJ,t2.IS_TYDJ " +
" from PRE_RESOURCE t1 left join PRE_STANDARDDATA t2 on t1.resource_id=t2.resource_id " +
" where t1.price_link="+priceLink+" and t2.STANDARDTASK_ID="+Id ;
//IS_YUANYAN, IS_DDDJ, IS_YZYJ, IS_TYDJ,
StringBuffer sb = new StringBuffer();
if(!med_type.equals("undefined")&&!department.equals("undefined")){
sb.append(" and t2.med_type='"+med_type+"' and t2.department='"+department+"' ");
}else if(!med_type.equals("undefined")&&department.equals("undefined")){
sb.append(" and t2.med_type='"+med_type+"' ");
}
String tempsql = sb.toString();
sql = sql + tempsql;
//DATA_YEAR,MED_TYPE, MEDNAME, SPEC, CONTENT, "+
// " CONTENTUNIT, DOSAGE, DOSAGEUNIT, SCALAR, SCALARUNIT, PRODUCE_COM "+
// " , CHARACT, WRAPPER, REST, DEPARTMENT, PROVINCE_NAME
System.out.println("PriceLinkDataSql=========>"+sql);
try {
db = db.getDBTool();
rs = db.querySql(dbSource, sql);
while (rs.next()) {
data = new ResourceData();
data.setColltask_name(rs.getString(3));
data.setData_year(rs.getString(4));
data.setData_region(rs.getString(5));
data.setPrice_link(rs.getString(6));
data.setRep_medname(rs.getString(7));
data.setRep_spec(rs.getString(8));
data.setRep_standard(rs.getString(9));
data.setRep_pro(rs.getString(10));
data.setProduce_cost(rs.getString(11));
data.setTime_cost(rs.getString(12));
data.setPur_price(rs.getString(13));
data.setSell_price(rs.getString(14));
data.setRepMednum(rs.getString(15));
data.setRepMed_type(rs.getString(16));
data.setSubmit_com(rs.getString(17));
data.setRetailunit(rs.getString(18));
data.setSurvey_year(rs.getString(19));
data.setBidding_time(rs.getString(20));
data.setSubmit_time(rs.getString(21));
data.setRecord_price(rs.getString(22));
data.setRecordprice_year(rs.getString(23));
data.setMed_type(rs.getString(24));
data.setMedname(rs.getString("MEDNAME"));
data.setSpec(rs.getString("SPEC"));
data.setProduce_com(rs.getString("PRODUCE_COM"));
data.setContent(rs.getString("CONTENT"));
data.setContentunit(rs.getString("CONTENTUNIT"));
data.setDosage(rs.getString("DOSAGE"));
data.setDosageunit(rs.getString("DOSAGEUNIT"));
data.setScalar(rs.getString("SCALAR"));
data.setScalarunit(rs.getString("SCALARUNIT"));
data.setCharact(rs.getString("CHARACT"));
data.setWrapper(rs.getString("WRAPPER"));
data.setRest(rs.getString("REST"));
data.setProvince(rs.getString("PROVINCE_NAME"));
data.setDepartment(rs.getString("DEPARTMENT"));
data.setMed_num(rs.getString("MEDNUM"));
data.setIs_yuanyan(rs.getString("IS_YUANYAN" ));
data.setIs_dddj(rs.getString("IS_DDDJ"));
data.setIs_tydj(rs.getString("IS_TYDJ"));
list.add(data);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}这个是导出操作，希望对大家有所帮助，O(∩_∩)O~ 有些不懂的可以问我，其中有些实体类我就没写上，
