2019独角兽企业重金招聘Python工程师标准>>>
引用jxl.jar包
HttpServletResponse resp = (HttpServletResponse)ActionContext.getContext().get(ServletActionContext.HTTP_RESPONSE);
OutputStream os = resp.getOutputStream();// 取得输出流
resp.reset();// 清空输出流
resp.setHeader("Content-disposition", "attachment; filename="+new String((excelFileName+".xls").getBytes("gb2312"),"iso8859-1"));// 设定输出文件头
resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
String tmptitle = "综合分析"; // 标题
WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称
String[] title = new String[] {"地市名称","挂牌总套数","其中住宅挂牌总套数", "挂牌总面积","其中住宅挂牌总面积","挂牌均价","其中住宅挂牌均价", "交易总套数","其中住宅交易总套数","交易总面积", "其中住宅交易总面积","交易均价","其中住宅交易均价"};
jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat();
wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);
wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcfFC1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);;
wcfFC1.setWrap(true);
jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();
wcfFC2.setAlignment(jxl.format.Alignment.RIGHT);
wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcfFC2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);;
wcfFC2.setWrap(true);
//标题
wsheet.mergeCells(0, 0, title.length-1, 0);
wsheet.addCell(new Label(0, 0, excelFileName,wcfFC1));
// 开始写入第一行(即标题栏)
wsheet.mergeCells(0, 1, title.length-1, 1);
wsheet.addCell(new Label(0, 1,"单位:套、万平方米、元/平方米" ,wcfFC2));
/*wsheet.addCell(new Label(7, 0, ));*/
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat();
wcfFC.setBackground(jxl.format.Colour.GRAY_25);
wcfFC.setAlignment(jxl.format.Alignment.CENTRE);
wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);;
wcfFC.setWrap(true);
for (int i = 0; i < title.length; i++) {
wsheet.addCell(new Label(i, 2, title[i],wcfFC));// 在Label对象的构造中指明单元格位置(参数依次代表列数、行数、内容 )
}
//开始写入内容 并计算合计数值
BigDecimal totalgpts = new BigDecimal(0),totalzfgpts = new BigDecimal(0),totalgpmj = new BigDecimal(0),totalzfgpmj = new BigDecimal(0),
totalgpjg = new BigDecimal(0),totalzfgpjg = new BigDecimal(0),totaljyts = new BigDecimal(0),totalzfjyts = new BigDecimal(0),
totaljymj = new BigDecimal(0),totalzfjymj = new BigDecimal(0),totaljyjg = new BigDecimal(0),totalzfjyjg = new BigDecimal(0),
totalgpje = new BigDecimal(0),totalzfgpje = new BigDecimal(0),totaljyje = new BigDecimal(0),totalzfjyje = new BigDecimal(0);
int row = 0;
for (; row < list.size(); row++) {
ZhfxClfEntity cb = (ZhfxClfEntity) list.get(row);
wsheet.addCell(new Label(0, row + 3, cb.getMc()));
wsheet.addCell(new Label(1, row + 3, (null==cb.getClfgpts())?"0":cb.getClfgpts().toString()));
wsheet.addCell(new Label(2, row + 3, (null==cb.getClfzfgpts())?"0":cb.getClfzfgpts().toString()));
wsheet.addCell(new Label(3, row + 3, (null==cb.getClfgpmj())?"0":cb.getClfgpmj().toString()));
wsheet.addCell(new Label(4, row + 3, (null==cb.getClfzfgpmj())?"0":cb.getClfzfgpmj().toString()));
wsheet.addCell(new Label(5, row + 3, (null==cb.getClfgpjg())?"0":cb.getClfgpjg().toString()));
wsheet.addCell(new Label(6, row + 3, (null==cb.getClfzfgpjg())?"0":cb.getClfzfgpjg().toString()));
wsheet.addCell(new Label(7, row + 3, (null==cb.getClfjyts())?"0":cb.getClfjyts().toString()));
wsheet.addCell(new Label(8, row + 3, (null==cb.getClfzfjyts())?"0":cb.getClfzfjyts().toString()));
wsheet.addCell(new Label(9, row + 3, (null==cb.getClfjymj())?"0":cb.getClfjymj().toString()));
wsheet.addCell(new Label(10, row + 3,(null==cb.getClfzfjymj())?"0":cb.getClfzfjymj().toString()));
wsheet.addCell(new Label(11, row + 3, (null==cb.getClfjyjg())?"0":cb.getClfjyjg().toString()));
wsheet.addCell(new Label(12, row + 3, (null==cb.getClfzfjyjg())?"0":cb.getClfzfjyjg().toString()));
if(cb.getClfgpts()!=null){
totalgpts = totalgpts.add(cb.getClfgpts());
}
if(cb.getClfzfgpts()!=null){
totalzfgpts = totalzfgpts.add(cb.getClfzfgpts());
}
if(cb.getClfgpmj()!=null){
totalgpmj = totalgpmj.add(cb.getClfgpmj());
}
if(cb.getClfzfgpmj()!=null){
totalzfgpmj = totalzfgpmj.add(cb.getClfzfgpmj());
}
if(cb.getClfjyts()!=null){
totaljyts = totaljyts.add(cb.getClfjyts());
}
if(cb.getClfzfgpts()!=null){
totalzfjyts = totalzfjyts.add(cb.getClfzfjyts());
}
if(cb.getClfjymj()!=null){
totaljymj = totaljymj.add(cb.getClfjymj());
}
if(cb.getClfzfjymj()!=null){
totalzfjymj = totalzfjymj.add(cb.getClfzfjymj());
}
if(cb.getClfgpje()!=null){
totalgpje = totalgpje.add(cb.getClfgpje());
}
if(cb.getClfzfgpje()!=null){
totalzfgpje = totalzfgpje.add(cb.getClfzfgpje());
}
if(cb.getClfjyje()!=null){
totaljyje = totaljyje.add(cb.getClfjyje());
}
if(cb.getClfzfjyje()!=null){
totalzfjyje = totalzfjyje.add(cb.getClfzfjyje());
}
}
if(totalgpmj.equals(new BigDecimal(0)))
{
totalgpjg = new BigDecimal(0);
}else{
totalgpjg = totalgpje.divide(totalgpmj, 2 , RoundingMode.HALF_UP);
}
if(totalzfgpmj.equals(new BigDecimal(0)))
{
totalzfgpjg = new BigDecimal(0);
}else{
totalzfgpjg = totalzfgpje.divide(totalzfgpmj, 2 , RoundingMode.HALF_UP);
}
if(totaljymj.equals(new BigDecimal(0)))
{
totaljyjg = new BigDecimal(0);
}else{
totaljyjg = totaljyje.divide(totaljymj, 2 , RoundingMode.HALF_UP);
}
if(totalzfjymj.equals(new BigDecimal(0)))
{
totalzfjyjg = new BigDecimal(0);
}else{
totalzfjyjg = totalzfjyje.divide(totalzfjymj, 2 , RoundingMode.HALF_UP);
}
//合计部分
wsheet.addCell(new Label(0, row + 3, "合计"));
wsheet.addCell(new Label(1, row + 3, totalgpts.toString()));
wsheet.addCell(new Label(2, row + 3, totalzfgpts.toString()));
wsheet.addCell(new Label(3, row + 3, totalgpmj.toString()));
wsheet.addCell(new Label(4, row + 3, totalzfgpmj.toString()));
wsheet.addCell(new Label(5, row + 3, totalgpjg.toString()));
wsheet.addCell(new Label(6, row + 3, totalzfgpjg.toString()));
wsheet.addCell(new Label(7, row + 3, totaljyts.toString()));
wsheet.addCell(new Label(8, row + 3, totalzfjyts.toString()));
wsheet.addCell(new Label(9, row + 3, totaljymj.toString()));
wsheet.addCell(new Label(10, row + 3,totalzfjymj.toString()));
wsheet.addCell(new Label(11, row + 3, totaljyjg.toString()));
wsheet.addCell(new Label(12, row + 3, totalzfjyjg.toString()));
wbook.write(); // 写入文件
wbook.close();
os.close(); // 关闭流