Java操作Excel（使用JXL）
一、本地操作
1、读取
1 package com.ljf.mb; 2 3 import java.io.FileInputStream; 4 import java.io.InputStream; 5 6 import jxl.Cell; 7 import jxl.Sheet; 8 import jxl.Workbook; 9 10 public class ReadExcel 11 { 12 public static void main(String[] args) throws Exception 13 { 14 { 15 String filePath = "d:/.xls"; //文件在电脑中的位置 16 InputStream is = new FileInputStream(filePath); 17 Workbook rwb = Workbook.getWorkbook(is); 18 19 Sheet rs = rwb.getSheet(0);// 获取第一张Sheet表 20 int hangNum = rs.getRows();//得到总行数 21 22 for (int i = 1; i < hangNum; i++) 23 { 24 String name = getCallStr(rs, i, 5).trim(); //得到第i行，第5列数据，行列均是从0开始 25 String gg = getCallStr(rs, i, 6).trim(); 26 System.out.println(name + " " + gg); //组织自己想要的输出 27 } 28 } 29 } 30 31 /** 32 * 功能描述：得到工作表中某个单元格的数据 33 *</br>日期：2013-3-29 34 *</br>@author ： 劲风 35 *</br>@param rs 工作表对象 36 *</br>@param hang 行数，从0开始 37 *</br>@param lie 列数，从0开始 38 *</br>@return 单元格的内容 39 */ 40 public static String getCallStr(Sheet rs, int hang, int lie) 41 { 42 Cell cell = rs.getCell(lie, hang); 43 String value = cell.getContents(); 44 if (value != null) 45 { 46 return value.trim(); 47 } 48 return ""; 49 } 50 }
2、生成
1 import java.io.File; 2 import jxl.Workbook; 3 import jxl.write.Label; 4 import jxl.write.WritableSheet; 5 import jxl.write.WritableWorkbook; 6 7 public class WriteExcel 8 { 9 10 public static void main(String[] args) throws Exception 11 { 12 WritableWorkbook wwb = null; 13 WritableSheet ws = null; 14 try 15 { 16 //首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象 17 wwb = Workbook.createWorkbook(new File("d:/tables/one.xls")); 18 if(wwb!=null) 19 { 20 //创建一个可写入的工作表 21 ws = wwb.createSheet("sheet1", 0); //第一个是工作表的名称，第二个是工作表在工作薄中的位置 22 23 //下面开始添加单元格 24 Label lab00 = creatLabel(0, 0, "姓名"); //行，列，内容 25 Label lab01 = creatLabel(0, 1, "年龄"); //行，列，内容 26 Label lab10 = creatLabel(1, 0, "张三"); //行，列，内容 27 Label lab11 = creatLabel(1, 1, "20"); //行，列，内容 28 29 ws.addCell(lab00); 30 ws.addCell(lab01); 31 ws.addCell(lab10); 32 ws.addCell(lab11); 33 34 wwb.write(); //从内存中写入文件中 35 wwb.close(); //关闭资源，释放内存 36 } 37 } 38 catch (Exception e) 39 { 40 e.printStackTrace(); 41 } 42 43 44 45 } 46 47 public static Label creatLabel(int hang,int lie,String con) //行，列，内容 48 { 49 return new Label(lie, hang, con); 50 } 51 }
二、Spring MVC 生成下载Excel
1、页面请求
<a href="javascript:void(0)" class="easyui-linkbutton exportBtn" iconCls="glyphicon-export" plain="true" onclick="exportCsv()">导出</a> ...... <script type="text/javascript"> //导出（未实现） function exportCsv() { var option = datagrid.datagrid("options"); var param = option.queryParams; param.sortName = option.sortName; param.sortOrder = option.sortOrder; window.location.href = "exportExcel?" + $.param(param); } </script>
2、后台逻辑
1 /** 2 * 导出Excel 3 * @throws Exception 4 */ 5 @RequestMapping("/exportExcel") 6 public void exportExcel(HttpServletResponse response,OutBillVo vo) throws Exception { 7 8 List<OutBill> ls=service.exportExcelQuery(vo); //取得导出数据 9 10 File file = new File("aaa.xls"); 11 jxl.write.WritableWorkbook book = Workbook.createWorkbook(file); //工作薄 12 jxl.write.WritableSheet sheet = book.createSheet("Sheet1", 0); //工作表 13 14 //设置行高 15 sheet.getSettings().setDefaultRowHeight(350); //设置所有行默认行高 16 sheet.setRowView(0, 700);//第1行 高度 17 sheet.setRowView(1, 400);//第2行 高度 18 19 //设置个别列宽 20 sheet.setColumnView(1, 16);//宽度设置：登记时间 21 sheet.setColumnView(8, 16);//宽度设置：出发时间 22 sheet.setColumnView(9, 16);//宽度设置：归队时间 23 sheet.setColumnView(6, 12);//宽度设置：用车部门 24 sheet.setColumnView(7, 16);//宽度设置：目的地 25 sheet.setColumnView(18, 12);//宽度设置：车辆状况 26 sheet.setColumnView(19, 16);//宽度设置：备注 27 28 //定义格式 29 //主标题格式 30 WritableCellFormat formatterTitle= new WritableCellFormat(); //标题格式 31 formatterTitle.setAlignment(Alignment.CENTRE);//水平对齐 32 formatterTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐 33 //WritableFont font = new WritableFont(WritableFont.createFont("ＭＳ Ｐゴシック"), 18, WritableFont.BOLD, false); //黑体 34 WritableFont font = new WritableFont(WritableFont.TAHOMA, 18, WritableFont.BOLD, false); 35 formatterTitle.setFont(font); 36 37 //列标题格式 38 WritableCellFormat formatterColumnTitle= new WritableCellFormat(); //列标题格式 39 formatterColumnTitle.setAlignment(Alignment.CENTRE);//水平对齐 40 formatterColumnTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐 41 formatterColumnTitle .setBackground(Colour.GRAY_25);//背景色 42 formatterColumnTitle.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线 所有边框 细线 43 44 //普通内容格式 45 WritableCellFormat formatterCon= new WritableCellFormat(); //内容格式 46 formatterCon.setAlignment(Alignment.CENTRE);//水平对齐 47 formatterCon.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐 48 formatterCon.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线 所有边框 细线 49 50 //合计行格式 51 WritableCellFormat formatterSum= new WritableCellFormat(); //合计格式 52 formatterSum.setAlignment(Alignment.CENTRE);//水平对齐 53 formatterSum.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐 54 formatterSum.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线 所有边框 细线 55 WritableFont font_sum = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.BOLD, false); 56 formatterSum.setFont(font_sum); 57 58 //输出标题 59 sheet.mergeCells(0, 0, 19, 0); //合并单元格（标题） 60 sheet.addCell(creatLabel(0,0,"车辆外出登记表",formatterTitle)); 61 62 //输出列标题 63 int rowNum=1; //行号 64 int columnNum=0; //列号 65 sheet.addCell(creatLabel(rowNum,columnNum++,"序号",formatterColumnTitle)); 66 sheet.addCell(creatLabel(rowNum,columnNum++,"登记时间",formatterColumnTitle)); 67 sheet.addCell(creatLabel(rowNum,columnNum++,"车辆",formatterColumnTitle)); 68 sheet.addCell(creatLabel(rowNum,columnNum++,"驾驶员",formatterColumnTitle)); 69 sheet.addCell(creatLabel(rowNum,columnNum++,"用车人",formatterColumnTitle)); 70 sheet.addCell(creatLabel(rowNum,columnNum++,"审批人",formatterColumnTitle)); 71 sheet.addCell(creatLabel(rowNum,columnNum++,"用车部门",formatterColumnTitle)); 72 sheet.addCell(creatLabel(rowNum,columnNum++,"目的地",formatterColumnTitle)); 73 sheet.addCell(creatLabel(rowNum,columnNum++,"出发时间",formatterColumnTitle)); 74 sheet.addCell(creatLabel(rowNum,columnNum++,"归队时间",formatterColumnTitle)); 75 sheet.addCell(creatLabel(rowNum,columnNum++,"总时长",formatterColumnTitle)); 76 sheet.addCell(creatLabel(rowNum,columnNum++,"等待时长",formatterColumnTitle)); 77 sheet.addCell(creatLabel(rowNum,columnNum++,"出发里程",formatterColumnTitle)); 78 sheet.addCell(creatLabel(rowNum,columnNum++,"归队里程",formatterColumnTitle)); 79 sheet.addCell(creatLabel(rowNum,columnNum++,"使用里程",formatterColumnTitle)); 80 sheet.addCell(creatLabel(rowNum,columnNum++,"加油金额",formatterColumnTitle)); 81 sheet.addCell(creatLabel(rowNum,columnNum++,"过/停费",formatterColumnTitle)); 82 sheet.addCell(creatLabel(rowNum,columnNum++,"总费用",formatterColumnTitle)); 83 sheet.addCell(creatLabel(rowNum,columnNum++,"车辆状况",formatterColumnTitle)); 84 sheet.addCell(creatLabel(rowNum,columnNum++,"备注",formatterColumnTitle)); 85 86 //合计项 87 int useNum = 0; // 使用里程 88 double useTime = 0; // 使用时长(小时，保留一位小数) 89 double waitTime = 0; // 等待时间(小时，保留一位小数) 90 double fuelNum = 0; // 加油费 91 double otherFee = 0; // 其他费用（过路费、停车费） 92 double allFee = 0; // 全部费用 93 94 if(ls != null && ls.size() > 0) 95 { 96 SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm"); //日期格式化 97 for(int i = 0; i < ls.size(); i++) 98 { 99 OutBill bo = ls.get(i); 100 rowNum++; 101 columnNum=0; 102 103 //非必填项Null值处理 104 Double fuelNumDouble = bo.getFuelNum(); //加油费 105 Double otherFeeDouble = bo.getOtherFee(); //其他费用（过路费、停车费） 106 String remark = bo.getRemark(); //备注 107 String carStatus = bo.getCarStatus(); //车辆状况 108 109 String fuelNumStr = ""; // 110 if(fuelNumDouble != null){fuelNumStr = fuelNumDouble.toString();} 111 String otherFeeStr = ""; // 112 if(otherFeeDouble != null){otherFeeStr = otherFeeDouble.toString();} 113 if(remark == null){remark="";} 114 if(carStatus == null){remark="";} 115 116 //输出一行数据 117 sheet.addCell(creatLabel(rowNum,columnNum++,(i+1)+"",formatterCon)); //序号 118 sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getAddTime()) ,formatterCon)); //登记时间 119 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getCar().getName() ,formatterCon)); //车辆 120 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getDriver() ,formatterCon)); //驾驶员 121 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUser() ,formatterCon)); //用车人 122 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getApprover(),formatterCon)); //审批人 123 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getOrgan().getName() ,formatterCon)); //用车部门 124 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getDestination() ,formatterCon)); //目的地 125 sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getBeginTime()) ,formatterCon)); //出发时间 126 sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getEndTime()) ,formatterCon)); //归队时间 127 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUseTime()+"" ,formatterCon)); //总时长 128 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getWaitTime()+"" ,formatterCon)); //等待时长 129 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getBeginNum()+"" ,formatterCon)); //出发里程 130 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getEndNum()+"" ,formatterCon)); //归队里程 131 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUseNum()+"" ,formatterCon)); //使用里程 132 sheet.addCell(creatLabel(rowNum,columnNum++,fuelNumStr ,formatterCon)); //加油金额 133 sheet.addCell(creatLabel(rowNum,columnNum++,otherFeeStr ,formatterCon)); //过 / 停费 134 sheet.addCell(creatLabel(rowNum,columnNum++,bo.getAllFee()+"" ,formatterCon)); //总费用 135 sheet.addCell(creatLabel(rowNum,columnNum++,carStatus ,formatterCon)); //车辆状况 136 sheet.addCell(creatLabel(rowNum,columnNum++,remark,formatterCon)); //备注 137 138 //合计项累加 139 if(bo.getUseNum() != null) 140 { 141 useNum += bo.getUseNum(); 142 } 143 if(bo.getUseTime() != null) 144 { 145 useTime += bo.getUseTime(); 146 } 147 if(bo.getWaitTime() != null) 148 { 149 waitTime += bo.getWaitTime(); 150 } 151 if(bo.getFuelNum() != null) 152 { 153 fuelNum += bo.getFuelNum(); 154 } 155 if(bo.getOtherFee() != null) 156 { 157 otherFee += bo.getOtherFee(); 158 } 159 if(bo.getAllFee() != null) 160 { 161 allFee += bo.getAllFee(); 162 } 163 } 164 165 //表格增加合计项 166 rowNum++; 167 columnNum = 0; 168 sheet.setRowView(rowNum, 400);//合计行 高度 169 170 sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //序号 171 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //登记时间 172 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //车辆 173 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //驾驶员 174 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //用车人 175 sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //审批人 176 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //用车部门 177 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //目的地 178 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //出发时间 179 sheet.addCell(creatLabel(rowNum,columnNum++,"合计",formatterSum)); //归队时间 180 sheet.addCell(creatLabel(rowNum,columnNum++,useTime+"" ,formatterSum)); //总时长 181 sheet.addCell(creatLabel(rowNum,columnNum++,waitTime+"" ,formatterSum)); //等待时长 182 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //出发里程 183 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //归队里程 184 sheet.addCell(creatLabel(rowNum,columnNum++,useNum+"" ,formatterSum)); //使用里程 185 sheet.addCell(creatLabel(rowNum,columnNum++,fuelNum+"" ,formatterSum)); //加油金额 186 sheet.addCell(creatLabel(rowNum,columnNum++,otherFee+"" ,formatterSum)); //过 / 停费 187 sheet.addCell(creatLabel(rowNum,columnNum++,allFee+"" ,formatterSum)); //总费用 188 sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //车辆状况 189 sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //备注 190 191 } 192 193 //------------------------------- 194 195 book.write(); 196 book.close(); 197 198 FileInputStream f = new FileInputStream(file); 199 byte[] fb = new byte[f.available()]; 200 f.read(fb); 201 response.setHeader("Content-disposition", "attachment; filename=" + new String("出车记录.xls".getBytes("gb2312"), "iso8859-1")); 202 ByteArrayInputStream bais = new ByteArrayInputStream(fb); 203 int b; 204 while ((b = bais.read()) != -1) 205 { 206 response.getOutputStream().write(b); 207 } 208 response.getOutputStream().flush(); 209 f.close(); 210 211 } 212 213 private Label creatLabel(int rowNum,int columnNum,String con,WritableCellFormat formatter) //行，列，内容 ，格式化 214 { 215 return new Label(columnNum, rowNum, con,formatter); 216 }
