写了一个小demo,把这样一个流程跑通,前端通过点击一个下载按钮,根据前端显示的数据,下载Excel文件到本地。
先分析一下整个流程
success之后返回OK,表示生成Excel成功,否则失败。
创建标签,标签有很多种类。以下创建一个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();
注意filename这里: response.addHeader("Content-Disposition","attachment;filename=" + new String(filename.getBytes("utf-8"), " ISO-8859-1"));
不然下载的文件名称会乱码。
先分析一下整个流程
- 前端通过ajax的post请求,传递json字符串到后台。
- 后台接收到json字符串,并拷贝一个模板Excel文件,假设命名为test1.xls。
- 后台解析json,通过jxl,Java可以很方便的操作微软的Excel文档。将数据填入指定位置。
- 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");
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"));
不然下载的文件名称会乱码。
这里在模板里面填充了部分数据