1、js页面:
$(function(){
$("#btnWater").click(function(){
var machineID=$("#txtMachine").val();
var proVersion=$("#txtVersion").val();
var jsonvalue={machineID:machineID,proVersion:proVersion};
if(machineID!="")
{
location.href='/FiledTranslate/ExcelOut.do?machineID='+machineID+"&proVersion="+proVersion;
}
else
{
alert('机型不能为空');
}
});
});
2、jsp页面:
<body>
机型ID:
<input type="text" id="txtMachine">
协议版本:
<input type="text" id="txtVersion">
<input type="button" id="btnWater" value="Excel导出" class="btn">
</body>
3、controller代码:
@RequestMapping("/ExcelOut.do")
private void ExcelOut(HttpServletRequest request,HttpServletResponse response)
{
String machine=request.getParameter("machineID");
String proVersion=request.getParameter("proVersion");
JSONObject json=translateService.getMachine_Name("machine_name",machine,proVersion);
ManageExcel(json, machine,response);
return ;
}
// 写excel
public void ManageExcel(JSONObject jsonObject,String machineID,HttpServletResponse response)
{
ExcelOperator eOperator=new ExcelOperator();
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
// 设置excel每列宽度,给工作表列定义列宽(实际应用自己更改列数)
for (int i = 0; i <=3; i++) {
sheet.setColumnWidth(i, 7000);
}
// 创建单元格样式
HSSFCellStyle style = wb.createCellStyle();
// 创建字体样式
HSSFFont font = wb.createFont();
// 创建Excel的sheet的一行
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 500);// 设定行的高度
// 创建一个Excel的单元格
HSSFCell cell = row.createCell(0);
// 合并单元格(startRow,endRow,startColumn,endColumn)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
// 给Excel的单元格设置样式和赋值
cell.setCellValue("运行数据信息");
cell.setCellStyle(eOperator.getCellStyle(style,font,"T"));
//设置列头
HSSFRow row2 = sheet.createRow(1);
cell= row2.createCell(0);
cell.setCellStyle(eOperator.getCellStyle(style,font,"H"));
cell.setCellValue(new HSSFRichTextString("中文名称"));
cell = row2.createCell(1);
cell.setCellStyle(eOperator.getCellStyle(style,font,"H"));
cell.setCellValue(new HSSFRichTextString("hive的字段类型"));
cell= row2.createCell(2);
cell.setCellStyle(eOperator.getCellStyle(style,font,"H"));
cell.setCellValue(new HSSFRichTextString("英文名称"));
cell= row2.createCell(3);
cell.setCellStyle(eOperator.getCellStyle(style,font,"H"));
cell.setCellValue(new HSSFRichTextString("mongodb的字段类型"));
int i=2;
Iterator<String> keys = jsonObject.keys();
while (keys.hasNext()){
String key = keys.next();
String value = jsonObject.get(key).toString();
if(key.equals("type_id")||key.equals("_id")||key.equals("prot_ver"))
{
}else{
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue(key);
cell = row.createCell(1);
String type=value.substring(0, 1);
cell.setCellValue(eOperator.rtnFieldType(type));
cell = row.createCell(2);
String str=translateService.getEnglishInfo(key);
cell.setCellValue(str);
cell = row.createCell(3);
cell.setCellValue(value);
i++;
}
}
//js调用是弹出现在窗口
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
String fileName = machineID;
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null)
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
if (bos != null)
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}