Java jxl在excel模板中动态加入数据，及前端下载excel的例子

写了一个小demo，把这样一个流程跑通，前端通过点击一个下载按钮，根据前端显示的数据，下载Excel文件到本地。


先分析一下整个流程

1. 前端通过ajax的post请求，传递json字符串到后台。
2. 后台接收到json字符串，并拷贝一个模板Excel文件，假设命名为test1.xls。
3. 后台解析json,通过jxl，Java可以很方便的操作微软的Excel文档。将数据填入指定位置。
4. excel生成成功后，前端使用一个display:none的a标签download 属性规定被下载的超链接目标。将test1.xls下载到本地。

JS 

$('#downExcel').click(function(){ showData={"da1":"ghfghf","da2":"566","da3":"测试数据测试数据测试数据","da4":"速度","da5":"1.35","da6":"70"};         var strData= JSON.stringify(showData); $.ajax({        url:'${ctx}/Down/createExcel',        type: "POST",         data:{data:strData},        success: function (data) {        if(data=="ok")        {        //download            var a = document.getElementById("downPdf");          a.href='${ctx}/Down/excel';          a.download="test";        a.click();          console.log('over!');        }        else        {console.log('创建Excel失败！');}       }         });});

success之后返回OK，表示生成Excel成功，否则失败。

如果模板时固定的话，不需要每次重新创建Excel表格，设置各种单元格合并和格式。以下是在excel模板中在指定单元格中加入数据的基本过程。

---

创建一个工作薄，不可写
Workbook wb = Workbook.getWorkbook(inStream);


创建一个可写的工作薄的，并指向原工作薄
jxl.write.WritableWorkbook wbe = Workbook.createWorkbook(new File(userPath), wb);


获取第一个工作表sheet
WritableSheet sheet = wbe.getSheet(0); 

获取目标单元格，在通过工作表获取单元格的时候，getWritableCell获取目标单元格。它的重载方式中可以通过数字的行号和列好指定，也可以通过类似
A1，D2，AK4这种String字符串定位。由于单元格存在合并单元格和其他不对齐情况。所以用参数为String重载方式要更方便一点。
WritableCell cell = sheet.getWritableCell("D3");

创建标签，标签有很多种类。以下创建一个number类型的标签。设置其合适和目标单元格匹配。
jxl.write.Number lbl = new jxl.write.Number(cell.getColumn(), cell.getRow(),Double.parseDouble(str), cell.getCellFormat());

将标签加入到工作表
sheet.addCell(lbl);

//将修改保存到workbook,一定要保存
wbe.write();

//最后关闭和释放内存
wbe.close();
wb.close();

Java

//created by cc on 2017/11/26import java.io.BufferedInputStream;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.math.BigDecimal;import jxl.*;import jxl.read.biff.BiffException;import jxl.write.*;import jxl.write.biff.RowsExceededException;import net.sf.json.JSONArray;import net.sf.json.JSONObject;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;@Controller@RequestMapping("/Down")public class Download {public String tempPath = "D:\\test.xls";@RequestMapping("/excel")public void dwnExcel(HttpServletRequest request, HttpServletResponse response) {try {// path是指欲下载的文件的路径。String userPath = "D:\\test1.xls";File file = new File(userPath);// 取得文件名。String filename = file.getName();// 以流的形式下载文件。InputStream fis = new BufferedInputStream(new FileInputStream(file));byte[] buffer = new byte[fis.available()];fis.read(buffer);fis.close();// 清空responseresponse.reset();// 设置response的Headerresponse.addHeader("Content-Disposition","attachment;filename=" + new String(filename.getBytes("utf-8"), "ISO-8859-1"));response.addHeader("Content-Length", "" + file.length());OutputStream toClient = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/octet-stream");toClient.write(buffer);toClient.flush();toClient.close();} catch (IOException ex) {ex.printStackTrace();}}// create excel from template and then fill data@ResponseBody@RequestMapping("/createExcel")public String createExcel(HttpServletRequest request, HttpServletResponse response)throws BiffException, IOException, RowsExceededException, WriteException {try {String data = request.getParameter("data");JSONObject inputJsonObj = JSONObject.fromObject(data);// to make sure copy a new template;String userPath = "D:\\test1.xls";File file = new File(userPath);if (file.exists()) {file.delete();}copyFile(tempPath, userPath);// fill data to excelInputStream inStream = new FileInputStream(userPath);Workbook wb = Workbook.getWorkbook(inStream);// writeablejxl.write.WritableWorkbook wbe = Workbook.createWorkbook(new File(userPath), wb);// 创建workbook的副本WritableSheet sheet = wbe.getSheet(0); // 获取第一个sheetfillCell(sheet, "B3", inputJsonObj,"da1");fillCell(sheet, "D3", inputJsonObj,"da2");fillCell(sheet, "K3", inputJsonObj,"da3");fillCell(sheet, "AG3", inputJsonObj,"da4");fillCell(sheet, "AK4", inputJsonObj,"da5");fillCell(sheet, "D4", inputJsonObj,"da6");// 将修改保存到workbook,一定要保存wbe.write();// release memorywbe.close();wb.close();inStream.close();response.setCharacterEncoding("utf-8");return "ok";} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}response.setCharacterEncoding("utf-8");return "error";}//填充一个单元格,判断字符串是否可以转为Number类型,由此使用不同的标签public void fillCell(WritableSheet sheet, String flag, JSONObject obj, String str) {try {if (obj.containsKey(str)) {str = obj.getString(str);if (isNum(str)) {// for numberWritableCell cell = sheet.getWritableCell(flag);jxl.write.Number lbl = new jxl.write.Number(cell.getColumn(), cell.getRow(),Double.parseDouble(str), cell.getCellFormat());sheet.addCell(lbl);} else {// for stringWritableCell cell = sheet.getWritableCell(flag);jxl.write.Label lbl = new jxl.write.Label(cell.getColumn(), cell.getRow(), str,cell.getCellFormat());sheet.addCell(lbl);}}} catch (Exception e) {System.out.println(e);}}//判断字符串是否可以转为Number类型public static boolean isNum(String str) {try {new BigDecimal(str);return true;} catch (Exception e) {return false;}} //复制文件到指定目录public void copyFile(String oldPath, String newPath) {try {int byteread = 0;File oldfile = new File(oldPath);if (oldfile.exists()) { // 文件存在时InputStream inStream = new FileInputStream(oldPath); // 读入原文件FileOutputStream fs = new FileOutputStream(newPath);byte[] buffer = new byte[1444];while ((byteread = inStream.read(buffer)) != -1) {fs.write(buffer, 0, byteread);}                                fs.close();inStream.close();}} catch (Exception e) {System.out.println("复制单个文件操作出错");e.printStackTrace();}}}

注意filename这里： response.addHeader("Content-Disposition","attachment;filename=" + new String(filename.getBytes("utf-8"), "ISO-8859-1"));
不然下载的文件名称会乱码。

这里在模板里面填充了部分数据