Service文件代码如下:
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;public EiInfo exportExcel(EiInfo eiInfo) {
WritableWorkbook wwb;
try {
OutputStream os = new FileOutputStream("D:\\test.xls");
wwb = Workbook.createWorkbook(os);
WritableSheet ws = wwb.createSheet("sheet1", 0); // 创建一个工作表// 设置单元格的文字格式
wwb.setColourRGB(Colour.GRAY_25,0xEA, 0xEA, 0xEA);
WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,
UnderlineStyle.NO_UNDERLINE,Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableFont wf_head = new WritableFont(WritableFont.ARIAL, 12,WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE,Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
WritableCellFormat whcf = new WritableCellFormat(wf_head); // 单元格定义
whcf.setBackground(jxl.format.Colour.GRAY_25); // 设置单元格的背景颜色
whcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框
whcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.RIGHT);
ws.setRowView(1, 500);
ws.setColumnView(0, 15); // 设置列的宽度
ws.setColumnView(1, 15); // 设置列的宽度
ws.setColumnView(2, 15); // 设置列的宽度
ws.setColumnView(3, 15); // 设置列的宽度
ws.setColumnView(4, 15); // 设置列的宽度
ws.setColumnView(5, 15); // 设置列的宽度
ws.setColumnView(6, 15); // 设置列的宽度
ws.setColumnView(7, 15); // 设置列的宽度
ws.setColumnView(8, 15); // 设置列的宽度
ws.setColumnView(9, 15); // 设置列的宽度
ws.setColumnView(10, 15); // 设置列的宽度
ws.setColumnView(11, 15); // 设置列的宽度
ws.setColumnView(12, 15); // 设置列的宽度
ws.setColumnView(13, 15); // 设置列的宽度
ws.setColumnView(14, 15); // 设置列的宽度
ws.setColumnView(15, 15); // 设置列的宽度
ws.setColumnView(16, 15); // 设置列的宽度
ws.setColumnView(17, 15); // 设置列的宽度
ws.setColumnView(18, 15); // 设置列的宽度
ws.setColumnView(19, 15); // 设置列的宽度
ws.setColumnView(20, 15); // 设置列的宽度
ws.setColumnView(21, 15); // 设置列的宽度
ws.setColumnView(22, 15); // 设置列的宽度
ws.setColumnView(23, 15); // 设置列的宽度
ws.setColumnView(24, 15); // 设置列的宽度
ws.setColumnView(25, 15); // 设置列的宽度
ws.setColumnView(26, 15); // 设置列的宽度
ws.setColumnView(27, 15); // 设置列的宽度Label title1=new Label(0,0,"单位名称",whcf);
Label title2=new Label(1,0,"上月累计",whcf);
Label title3=new Label(10,0,"本月发生",whcf);
Label title4=new Label(19,0,"本年累计",whcf);
Label title5=new Label(1,1,"收入",whcf);
Label title6=new Label(4,1,"成本",whcf);
Label title7=new Label(7,1,"利润",whcf);
Label title8=new Label(10,1,"收入",whcf);
Label title9=new Label(13,1,"成本",whcf);
Label title10=new Label(16,1,"利润",whcf);
Label title11=new Label(19,1,"收入",whcf);
Label title12=new Label(22,1,"成本",whcf);
Label title13=new Label(25,1,"利润",whcf);
Label title14=new Label(1,2,"材料",whcf);
Label title15=new Label(2,2,"设备",whcf);
Label title16=new Label(3,2,"小计",whcf);
Label title17=new Label(4,2,"材料",whcf);
Label title18=new Label(5,2,"设备",whcf);
Label title19=new Label(6,2,"小计",whcf);
Label title20=new Label(7,2,"材料",whcf);
Label title21=new Label(8,2,"设备",whcf);
Label title22=new Label(9,2,"小计",whcf);
Label title23=new Label(10,2,"材料",whcf);
Label title24=new Label(11,2,"设备",whcf);
Label title25=new Label(12,2,"小计",whcf);
Label title26=new Label(13,2,"材料",whcf);
Label title27=new Label(14,2,"成本",whcf);
Label title28=new Label(15,2,"小计",whcf);
Label title29=new Label(16,2,"材料",whcf);
Label title30=new Label(17,2,"设备",whcf);
Label title31=new Label(18,2,"小计",whcf);
Label title32=new Label(19,2,"材料",whcf);
Label title33=new Label(20,2,"设备",whcf);
Label title34=new Label(21,2,"小计",whcf);
Label title35=new Label(22,2,"材料",whcf);
Label title36=new Label(23,2,"成本",whcf);
Label title37=new Label(24,2,"小计",whcf);
Label title38=new Label(25,2,"材料",whcf);
Label title39=new Label(26,2,"设备",whcf);
Label title40=new Label(27,2,"小计",whcf);
ws.addCell(title1);
ws.addCell(title2);
ws.addCell(title3);
ws.addCell(title4);
ws.addCell(title5);
ws.addCell(title6);
ws.addCell(title7);
ws.addCell(title8);
ws.addCell(title9);
ws.addCell(title10);
ws.addCell(title11);
ws.addCell(title12);
ws.addCell(title13);
ws.addCell(title14);
ws.addCell(title15);
ws.addCell(title16);
ws.addCell(title17);
ws.addCell(title18);
ws.addCell(title19);
ws.addCell(title20);
ws.addCell(title21);
ws.addCell(title22);
ws.addCell(title23);
ws.addCell(title24);
ws.addCell(title25);
ws.addCell(title26);
ws.addCell(title27);
ws.addCell(title28);
ws.addCell(title29);
ws.addCell(title30);
ws.addCell(title31);
ws.addCell(title32);
ws.addCell(title33);
ws.addCell(title34);
ws.addCell(title35);
ws.addCell(title36);
ws.addCell(title37);
ws.addCell(title38);
ws.addCell(title39);
ws.addCell(title40);//合: 第1列第1行 到 第13列第1行
ws.mergeCells(0, 0, 0, 2);
ws.mergeCells(1, 0, 9, 0);
ws.mergeCells(10, 0, 18, 0);
ws.mergeCells(19, 0, 27, 0);
ws.mergeCells(1, 1, 3, 1);
ws.mergeCells(4, 1, 6, 1);
ws.mergeCells(7, 1, 9, 1);
ws.mergeCells(10, 1, 12, 1);
ws.mergeCells(13, 1, 15, 1);
ws.mergeCells(16, 1, 18, 1);
ws.mergeCells(19, 1, 21, 1);
ws.mergeCells(22, 1, 24, 1);
ws.mergeCells(25, 1, 27, 1);/*获取参数*/
String monthDate = eiInfo.get("monthDate").toString();
//向SQLMAP中穿入参数
Map<String, String> pageMap = new HashMap<String, String>();
pageMap.put("monthDate", monthDate);
List<Map> table = (List<Map>)dao.query("FKWZ0001.queryWZTable",pageMap);
for (int i=0;i<table.size();i++){
Map item = table.get(i);
ws.addCell(new Label(0, i+3, item.get("companyname").toString(), wcf));
ws.addCell(new Label(1, i+3, item.get("lmrevenuescllj").toString(), wcf));
ws.addCell(new Label(2, i+3, item.get("lmrevenuessblj").toString(), wcf));
ws.addCell(new Label(3, i+3, item.get("lmrevenuesclsblj").toString(), wcf));
ws.addCell(new Label(4, i+3, item.get("lmcostcllj").toString(), wcf));
ws.addCell(new Label(5, i+3, item.get("lmcostsblj").toString(), wcf));
ws.addCell(new Label(6, i+3, item.get("lmcostclsblj").toString(), wcf));
ws.addCell(new Label(7, i+3, item.get("lmprofitcllj").toString(), wcf));
ws.addCell(new Label(8, i+3, item.get("lmprofitsblj").toString(), wcf));
ws.addCell(new Label(9, i+3, item.get("lmprofitclsblj").toString(), wcf));
ws.addCell(new Label(10, i+3, item.get("tmrevenuescl").toString(), wcf));
ws.addCell(new Label(11, i+3, item.get("tmrevenuessb").toString(), wcf));
ws.addCell(new Label(12, i+3, item.get("tmrevenuesclsb").toString(), wcf));
ws.addCell(new Label(13, i+3, item.get("tmcostcl").toString(), wcf));
ws.addCell(new Label(14, i+3, item.get("tmcostsb").toString(), wcf));
ws.addCell(new Label(15, i+3, item.get("tmcostclsb").toString(), wcf));
ws.addCell(new Label(16, i+3, item.get("tmprofitcl").toString(), wcf));
ws.addCell(new Label(17, i+3, item.get("tmprofitsb").toString(), wcf));
ws.addCell(new Label(18, i+3, item.get("tmprofitclsb").toString(), wcf));
ws.addCell(new Label(19, i+3, item.get("tyrevenuescllj").toString(), wcf));
ws.addCell(new Label(20, i+3, item.get("tyrevenuessblj").toString(), wcf));
ws.addCell(new Label(21, i+3, item.get("tyrevenuesclsblj").toString(), wcf));
ws.addCell(new Label(22, i+3, item.get("tycostcllj").toString(), wcf));
ws.addCell(new Label(23, i+3, item.get("tycostsblj").toString(), wcf));
ws.addCell(new Label(24, i+3, item.get("tycostclsblj").toString(), wcf));
ws.addCell(new Label(25, i+3, item.get("typrofitcllj").toString(), wcf));
ws.addCell(new Label(26, i+3, item.get("typrofitsblj").toString(), wcf));
ws.addCell(new Label(27, i+3, item.get("typrofitclsblj").toString(), wcf));
}wwb.write();
wwb.close();} catch (IOException e){
} catch (RowsExceededException e){
} catch (WriteException e){}return eiInfo;
}
在JSP页面中设置隐藏表单:
<input type="hidden" id="monthDate" value="<%=monthDate%>">
然后在JS文件中获取monthDate,并调用Service中的exportExcel方法:
var downLoadExcel = function(){
var inInfo = new EiInfo();
inInfo.set("monthDate",$("#monthDate").val());
EiCommunicator.send("FKRY0001","exportExcel", inInfo,null);
window.open("./FK/common/download.jsp?docname=test.xls");
}
转载请注明:观测者 » Java导出Excel程序实例