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/26

import 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();
			// 清空response
			response.reset();
			// 设置response的Header
			response.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 excel
			InputStream inStream = new FileInputStream(userPath);
			Workbook wb = Workbook.getWorkbook(inStream);
			// writeable
			jxl.write.WritableWorkbook wbe = Workbook.createWorkbook(new File(userPath), wb);// 创建workbook的副本
			WritableSheet sheet = wbe.getSheet(0); // 获取第一个sheet

			fillCell(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 memory
			wbe.close();
			wb.close();
			inStream.close();
			response.setCharacterEncoding("utf-8");
			return "ok";

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.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 number
					WritableCell 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 string
					WritableCell 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"));
不然下载的文件名称会乱码。

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