利用jxl进行Excel的导出操作 博客分类: web框架 jxlwebExcelwcf
程序员文章站
2024-03-07 23:39:33
...
web工程 利用jxl(JExcelApi)进行Excel的导出功能
jxl.jar版本 2.6.6 详见附件
public void exportYCItemPaid(HttpServletRequest request, HttpServletResponse response) throws Exception { response.setContentType("application/vnd.ms-excel"); String fileName = "赔付标的信息("+1+")"; fileName = new String(fileName.getBytes("GBK"),"ISO8859-1"); response.addHeader("Content-Disposition","attachment;filename="+fileName+".xls"); OutputStream os = response.getOutputStream(); WritableWorkbook book = Workbook.createWorkbook(os); WritableSheet sheet = book.createSheet("test", 0); //生成详细数据 WritableFont wf = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false); WritableCellFormat textFormat = new WritableCellFormat(NumberFormats.TEXT); //定义一个单元格样式 textFormat.setAlignment(Alignment.CENTRE); textFormat.setBorder(Border.ALL, BorderLineStyle.THIN); WritableCellFormat doubleFormat = new WritableCellFormat(NumberFormats.FLOAT); //定义一个单元格样式 doubleFormat.setAlignment(Alignment.CENTRE); doubleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); WritableCellFormat wcf = new WritableCellFormat(wf); wcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 WritableCellFeatures txcf0 = new WritableCellFeatures(); txcf0.setComment("合同号请与承保系统中数据相符"); WritableCellFeatures txcf1 = new WritableCellFeatures(); txcf1.setComment("农户姓名请与承保系统中数据相符"); WritableCellFeatures txcf2 = new WritableCellFeatures(); txcf2.setComment("归属烟叶站名称请与承保系统中数据相符"); WritableCellFeatures txcf3 = new WritableCellFeatures(); txcf3.setComment("烟草生长期(1——团棵期、2——现蕾期、3——采烤期)"); WritableCellFeatures txcf4 = new WritableCellFeatures(); txcf4.setComment("当期单株有效叶片数(请录入数字 介于0.1到9999999.99)"); WritableCellFeatures txcf5 = new WritableCellFeatures(); txcf5.setComment("灾害类型(请录入字符)"); WritableCellFeatures txcf6 = new WritableCellFeatures(); txcf6.setComment("受灾地点(请录入字符)"); WritableCellFeatures txcf7 = new WritableCellFeatures(); txcf7.setComment("受损面积(请录入字符 介于0.10.1到9999999.99)"); WritableCellFeatures txcf8 = new WritableCellFeatures(); txcf8.setComment("是否绝收(0——不绝收,1——绝收;)"); WritableCellFeatures txcf9 = new WritableCellFeatures(); txcf9.setComment("受损程度(请录入数字介于 1到100)"); WritableCellFeatures txcf10 = new WritableCellFeatures(); txcf10.setComment("赔付金额(请录入数字)"); WritableCellFeatures txcf11 = new WritableCellFeatures(); txcf11.setComment("开户行账号(请录入字符)"); WritableCellFeatures txcf12 = new WritableCellFeatures(); txcf12.setComment("账户姓名(请录入字符)"); WritableCellFeatures txcf13 = new WritableCellFeatures(); txcf13.setComment("开户银行(请录入字符)"); CellView cv = new CellView(); //定义一个列显示样式 cv.setFormat(wcf);//把定义的单元格格式初始化进去 cv.setSize(10*265);//设置列宽度(不设置的话是0,不会显示) String orgCode = "1"; String orgName = "12"; int columnIndex = 0; sheet.setColumnView(columnIndex, 25); // 设置列的宽度 第一列 sheet.setColumnView(++columnIndex, 15); // 设置列的宽度 第二列 sheet.setColumnView(++columnIndex, 35); // 设置列的宽度 第三列 sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第四列 sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第五列 sheet.setColumnView(++columnIndex, 20); // 设置行的高度 第六列 sheet.setColumnView(++columnIndex, 25); // 设置行的高度 第七列 sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第八列 sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第九列 sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第十列 sheet.setColumnView(++columnIndex, 25); // 设置行的高度 十一列 sheet.setColumnView(++columnIndex, 30); // 设置行的高度 十二列 sheet.setColumnView(++columnIndex, 20); // 设置行的高度 十三列 sheet.setColumnView(++columnIndex, 20); // 设置行的高度 十四列 String[] titles = { "合同号","农户姓名","归属烟叶站名称","烟草生长期", "当期单株有效叶片数","灾害类型","受灾地点","受损面积", "是否绝收","受损程度","赔付金额","开户行账号","账户姓名","开户银行"}; //生成Title Label titleLabel = null; for (int i = 0; i < titles.length; i++) { titleLabel = new Label(i, 0, titles[i],textFormat); switch (i) { case 0://合同号 titleLabel.setCellFeatures(txcf0); break; case 1://农户姓名 titleLabel.setCellFeatures(txcf1); break; case 2://归属烟叶站名称 titleLabel.setCellFeatures(txcf2); break; case 3://烟草生长期 titleLabel.setCellFeatures(txcf3); break; case 4://当期单株有效叶片数 titleLabel.setCellFeatures(txcf4); break; case 5://灾害类型 titleLabel.setCellFeatures(txcf5); break; case 6://受灾地点 titleLabel.setCellFeatures(txcf6); break; case 7://受损面积 titleLabel.setCellFeatures(txcf7); break; case 8://是否绝收 titleLabel.setCellFeatures(txcf8); break; case 9://受损程度 titleLabel.setCellFeatures(txcf9); break; case 10://赔付金额 titleLabel.setCellFeatures(txcf10); break; case 11://开户行账号 titleLabel.setCellFeatures(txcf11); break; case 12://账户姓名 titleLabel.setCellFeatures(txcf12); break; case 13://开户银行 titleLabel.setCellFeatures(txcf13); break; default: break; } sheet.addCell(titleLabel); } List<String> jsList = new ArrayList<String>(); jsList.add("1"); jsList.add("0"); //生长期下拉列表 List<String> seasonList = new ArrayList<String>(); seasonList.add("1"); seasonList.add("2"); seasonList.add("3"); // for (int i = 0; i < lossInfoList.size(); i++) { jxl.write.Number titleNumber = null; int cellIndex = 0; int i=0; //合同号 titleLabel = new Label(cellIndex++ , i+1, "1121212",textFormat); sheet.addCell(titleLabel); //农户姓名 titleLabel = new Label(cellIndex++ , i+1, "1121212",textFormat); sheet.addCell(titleLabel); //归属烟叶站名称 titleLabel = new Label(cellIndex++ , i+1, orgName ,textFormat);//归属烟叶站名称 sheet.addCell(titleLabel); //烟草生长期(1——团棵期、2——现蕾期、3——采烤期) titleNumber = new jxl.write.Number(cellIndex++ , i+1,1 , textFormat); WritableCellFeatures cf1 = new WritableCellFeatures(); cf1.setDataValidationList(seasonList); titleNumber.setCellFeatures(cf1); sheet.addCell(titleNumber); //当期单株有效叶片数 titleNumber = new jxl.write.Number(cellIndex++ , i+1, 1, doubleFormat); WritableCellFeatures cf2 = new WritableCellFeatures(); cf2.setNumberValidation(0.1 ,9999999.99, BaseCellFeatures.BETWEEN); titleNumber.setCellFeatures(cf2); sheet.addCell(titleNumber); //灾害类型 titleLabel = new Label(cellIndex++ , i+1, "1121212" ,textFormat); sheet.addCell(titleLabel); //受灾地点 titleLabel = new Label(cellIndex++ , i+1, "1121212" ,textFormat); sheet.addCell(titleLabel); //受损面积 titleNumber = new jxl.write.Number(cellIndex++ , i+1,1, doubleFormat); WritableCellFeatures cf3 = new WritableCellFeatures(); cf3.setNumberValidation(0.1 ,9999999.99, BaseCellFeatures.BETWEEN); titleNumber.setCellFeatures(cf3); sheet.addCell(titleNumber); //是否绝收 设置下拉列表取值范围 0,1 titleNumber = new jxl.write.Number(cellIndex++ , i+1, Double.parseDouble("12") ,textFormat); WritableCellFeatures cf6 = new WritableCellFeatures(); cf6.setDataValidationList(jsList); titleNumber.setCellFeatures(cf6); sheet.addCell(titleNumber); //受损程度 titleNumber = new jxl.write.Number (cellIndex++ , i+1,12 ,doubleFormat); WritableCellFeatures cf4 = new WritableCellFeatures(); cf4.setNumberValidation(0.01 ,100, BaseCellFeatures.BETWEEN); titleNumber.setCellFeatures(cf4); sheet.addCell(titleNumber); //赔付金额 titleNumber = new jxl.write.Number(cellIndex++ , i+1, 0, doubleFormat); WritableCellFeatures cf5 = new WritableCellFeatures(); cf5.setNumberValidation(0.01 ,9999999.99, BaseCellFeatures.BETWEEN); titleNumber.setCellFeatures(cf5); sheet.addCell(titleNumber); // } // //设置下拉列表取值范围 // List angerlist = new ArrayList(); // angerlist.add("是"); // angerlist.add("否"); // Label norFormat = new Label(1, 0, "请选择"); // WritableCellFeatures ws = new WritableCellFeatures(); // ws.setDataValidationList(angerlist); // norFormat.setCellFeatures(ws); // sheet.addCell(norFormat); // // // // //设置数字有效性 取值是100, 200区间, 100和200是有效的 // jxl.write.Number number = new jxl.write.Number(3,3,100); // WritableCellFeatures cf = new WritableCellFeatures(); // cf.setNumberValidation(100, 200,BaseCellFeatures.BETWEEN); // number.setCellFeatures(cf); // sheet.addCell(number); // // //设置数字有效性 大等于100 // jxl.write.Number number1 = new jxl.write.Number(3,4,0); // WritableCellFeatures cf1 = new WritableCellFeatures(); // cf1.setNumberValidation(100 , BaseCellFeatures.GREATER_THAN); // number1.setCellFeatures(cf1); // sheet.addCell(number1); // // //设置数字有效性 小于100 // jxl.write.Number number2 = new jxl.write.Number(3,5,0); // WritableCellFeatures cf2 = new WritableCellFeatures(); // cf2.setNumberValidation(100 , BaseCellFeatures.LESS_THAN); // number2.setCellFeatures(cf2); // sheet.addCell(number2); // // //设置数字有效性 不在100,200区间的数字 // jxl.write.Number number3 = new jxl.write.Number(3,6,0); // WritableCellFeatures cf3 = new WritableCellFeatures(); // cf3.setNumberValidation(100,200 , BaseCellFeatures.NOT_BETWEEN); // number3.setCellFeatures(cf3); // sheet.addCell(number3); // // //设置数字有效性 必须等于 100 // jxl.write.Number number4 = new jxl.write.Number(3,7,0); // WritableCellFeatures cf4 = new WritableCellFeatures(); // cf4.setNumberValidation(100 , BaseCellFeatures.EQUAL); // number4.setCellFeatures(cf4); // sheet.addCell(number4); // // //设置数字有效性 不等于 100 // jxl.write.Number number5 = new jxl.write.Number(3,8,0); // WritableCellFeatures cf5 = new WritableCellFeatures(); // cf5.setNumberValidation(100 , BaseCellFeatures.NOT_EQUAL); // number5.setCellFeatures(cf5); // sheet.addCell(number5); // // //设置数字有效性 大于等于100 // jxl.write.Number number6 = new jxl.write.Number(3,9,0); // WritableCellFeatures cf6 = new WritableCellFeatures(); // cf6.setNumberValidation(100 , BaseCellFeatures.GREATER_EQUAL); // number6.setCellFeatures(cf6); // sheet.addCell(number6); // // //设置数字有效性 小于等于100 // jxl.write.Number number7 = new jxl.write.Number(3,10,0); // WritableCellFeatures cf7 = new WritableCellFeatures(); // cf7.setNumberValidation(100, BaseCellFeatures.LESS_EQUAL); // number7.setCellFeatures(cf7); // sheet.addCell(number7); // book.write(); book.close(); os.close(); response.flushBuffer(); }