一个简单的jxl文件上传功能
众说周知,java导出excel表格到EXCEL的方式有两种。其一是利用第三方插件JXL实现excel文件的生成,另一种方式则是不需要第三方的插件,直接通过jsp页面的设置和action层的response跳转完成excel文件的生成。综合来讲,采用第三方插件不仅能够满足功能性需求,而且还提供函数、字体、颜色及其他方面的接口,如果直接采用jsp跳转形式,则样式会略显低调,但是其实现形式很容易理解,无需了解更多的技术层面的东西。
那么我下面介绍的就是使用jxl实现文件的导出功能。
首先是从前端传值过来!前端的大多大同小异。我就不介绍了。
function exportExcel(){
var xmdw_input = KaTeX parse error: Expected 'EOF', got '#' at position 3: ('#̲xmdw_input').va…(’#start_input’).val();
var end_input=$(’#end_input’).val();
var params = “?start_input=”+start_input+
“&end_input=”+end_input+
“&xmdw_input=”+xmdw_input;
window.location.href="/ADXMGL/zhwy/zhwy-xmgl-manage!exportXmtj.action"+params;
}
下面就是后端的代码。
public String exportXmtj() throws Exception {
try {
String xmdw = getParameter(“xmdw_input”);
String start = getParameter(“start_input”);
String end = getParameter(“end_input”);
PropertyFilter dwPf = new PropertyFilter("xmdw:LIKE_S", xmdw);
PropertyFilter startPf = new PropertyFilter("RKSJ:GE_D", start);
PropertyFilter endPf = new PropertyFilter("RKSJ:LE_D", end);
List<PropertyFilter> pfList = new ArrayList<PropertyFilter>();
pfList.add(dwPf);
pfList.add(startPf);
pfList.add(endPf);
//List<PropertyFilter> pfList = initParam();
List<String> colList = new ArrayList<String>();
//colList.add("guId");
int startPage=this.page.getPageNo()*this.page.getPageSize()+1;
int endPage=(this.page.getPageNo()-1)*this.page.getPageSize();
colList.add("xmdw");
colList.add("xmsl");
colList.add("cbzj");
colList.add("dwzj");
colList.add("ml");
colList.add("mll");
List<Map<String, Object>> mapList = this.zhwyXmglManageService
.findMapLists(colList, pfList);
exportXmtj(mapList);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
然后是导出的时候,创建一个文件。将需要的东西传到这个表格里面去。
public List<PropertyFilter> initParam() {
String status = getParameter("status");
String create_start_date = getParameter("create_start_date");
String create_end_date = getParameter("create_end_date");
String flog = getParameter("flog");
UumUser currUser = (UumUser) this.getSessionAttribute(BasicConstant.CURRUSER);
List<PropertyFilter> pfList = new ArrayList<PropertyFilter>();
if (create_start_date != null) {
pfList.add(new PropertyFilter("rksj:GE_D", create_start_date));
}
if (create_end_date != null) {
pfList.add(new PropertyFilter("rksj:LE_D", create_end_date));
}
return pfList;
}
public String exportXmtj(List<Map<String, Object>> mapList) throws Exception {
WritableFont font1 = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
//设置背景颜色;
//cellFormat1.setBackground(Colour.BLUE_GREY);
//设置边框;
//cellFormat1.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
//设置自动换行;
cellFormat1.setWrap(true);
//设置文字居中对齐方式;
cellFormat1.setAlignment(Alignment.CENTRE);
//设置垂直居中;
cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
WritableFont font2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
cellFormat2.setAlignment(Alignment.CENTRE);
cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormat2.setWrap(true);
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");// 设置日期格式
//String name=df.format(new Date()) + ".xls";
String path = this.getRealRoot() + "template\\price-v3" + new Date().getTime() + ".xls";
WritableWorkbook book = null;
File template = new File(path);
book = Workbook.createWorkbook(template); // 设置表名
WritableSheet sheet = book.createSheet("表格", 0);
// WritableSheet sheet= new WritableSheet();
sheet.setColumnView(0, 20);
for (int i = 0; i < 6; i++) {
sheet.setColumnView(i, 20);
}
// 生成表格表头信息
Label labe0 = new Label(0, 0, "项目统计", cellFormat1);
Label labe1 = new Label(0, 1, "项目单位", cellFormat2);
Label labe2 = new Label(1, 1, "项目数量", cellFormat2);
Label labe3 = new Label(2, 1, "成本总价", cellFormat2);
Label labe4 = new Label(3, 1, "对外总价", cellFormat2);
Label labe5 = new Label(4, 1, "毛利", cellFormat2);
Label labe6 = new Label(5, 1, "毛利率", cellFormat2);
Label labe7 = new Label(0, mapList.size()+2,"-合计-", cellFormat2);
//遍历表头信息
sheet.mergeCells(0, 0, 5, 0);
sheet.addCell(labe0);
sheet.addCell(labe1);
sheet.addCell(labe2);
sheet.addCell(labe3);
sheet.addCell(labe4);
sheet.addCell(labe5);
sheet.addCell(labe6);
sheet.addCell(labe7);
int i = 2;
//求和
for (int j=0;j<mapList.size();j++) {
labe1 = new Label(0, i, (mapList.get(j).get("xmdw")!= null ?(mapList.get(j).get("xmdw").toString()): "") + "", cellFormat2);
labe2 = new Label(5, i, (mapList.get(j).get("mll")!= null ?(mapList.get(j).get("mll").toString().trim()): "") + "", cellFormat2);
sheet.addCell(labe1);
sheet.addCell(labe2);
if(mapList.get(j).get("xmsl")!=null)
sheet.addCell(setNumberCell(1, i,mapList.get(j).get("xmsl").toString(), cellFormat2));
if(mapList.get(j).get("cbzj")!=null)
sheet.addCell(setNumberCell(2, i,mapList.get(j).get("cbzj").toString(), cellFormat2));
if(mapList.get(j).get("dwzj")!=null)
sheet.addCell(setNumberCell(3, i,mapList.get(j).get("dwzj").toString(), cellFormat2));
if(mapList.get(j).get("ml")!=null)
sheet.addCell(setNumberCell(4, i,mapList.get(j).get("ml").toString(), cellFormat2));
i++;
}
//计算
sheet.addCell(new Formula(1,i,"SUM(b3:b"+(mapList.size()+2)+")",cellFormat2));
sheet.addCell(new Formula(2,i,"SUM(C3:C"+(mapList.size()+2)+")",cellFormat2));
sheet.addCell(new Formula(3,i,"SUM(d3:d"+(mapList.size()+2)+")",cellFormat2));
sheet.addCell(new Formula(4,i,"SUM(e3:e"+(mapList.size()+2)+")",cellFormat2));
DisplayFormat displayFormat = NumberFormats.PERCENT_FLOAT;
WritableCellFormat wcfF = new WritableCellFormat(displayFormat);
wcfF.setAlignment(Alignment.CENTRE);
wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
String formula = "e"+(mapList.size()+3)+"/"+"d"+((mapList.size()+3));
sheet.addCell(new Formula(5,i,formula, wcfF));
//当到达数据的最后一行时,新增一行数据
book.write();
book.close();
// path是指欲下载的文件的路径。
File file = new File(path);// 取得文件名。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();// 清空response
response.reset();// 设置response的Header
String fileName = "项目统计" + df.format(new Date());
fileName = URLEncoder.encode(fileName, "utf-8");
this.response.setHeader("Pragma", "public");
this.response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
response.addHeader("Content-Length", "" + file.length());
response.setContentType("application/octet-stream");
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
toClient.write(buffer);
toClient.flush();
toClient.close();
template.delete();
return null;
}
最后是接口:
public abstract List<Map<String, Object>> findMapLists(List columnNams, List paramList);
可以参照学习一下。
上一篇: php 一个上传文件的工具类
下一篇: layui修改、删除