JAVA创建并写入内容到xlsx文件
首先需要在web项目中导入jxl.jar 包
//action中代码
public String downloadReport(){
String path = System.getProperty("java.io.tmpdir") + "\\xlsx"+ (new java.util.Date().getTime());
List<User> users=new ArrayList<User>();//查询结果集
users.add(new User(1, "屌丝1", 18, "男"));
users.add(new User(2, "妹子2", 19, "女"));
users.add(new User(3, "屌丝3", 20, "男"));
users.add(new User(4, "妹子4", 21, "女"));
users.add(new User(5, "屌丝5", 22, "男"));
try {
createExcel(path,List<User> users);//方法代码在下面
//下载文件
response().setContentType("application/x-msdownload;charset=UTF-8"); // 设置响应类型为下载
response().setCharacterEncoding("UTF-8");//页面乱码问题
String docName = java.net.URLEncoder.encode("report_case.xls", "UTF-8");//文件名称设置
response().setHeader("Content-Disposition", "attachment; filename="+ new String(docName.getBytes("UTF-8"), "UTF-8"));
BufferedInputStream br = new BufferedInputStream(new FileInputStream(path));
byte[] buf = new byte[1024];
int len = 0;
OutputStream out = response().getOutputStream();
while ((len = br.read(buf)) > 0)
out.write(buf, 0, len);//把下载的文档写入本地电脑中去
} catch (IOException e) {
e.printStackTrace();
}finally{
out.close();
br.close();
}
return null;
}
//下载调用方法
/**
* 设置表格标题和风格
* @param path
* @param users
*/
public static void createExcel(String path,List<User> users){
try {
//创建工作薄
WritableWorkbook workbook = Workbook.createWorkbook(new File(path));
//创建工作表
WritableSheet sheet=workbook.createSheet("测试表格", 0);
//置页眉、页脚
sheet.setHeader("页眉", "", "第 &P 页，共 &N 页"); // 设置页眉
sheet.setFooter("", "", "&D &T"); // 设置页脚
//设置第几列的的列宽
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 20);
//指定几种单元格的字体样式
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
WritableFont BoldFont12 = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD);//加粗12号字体
WritableFont BoldFont20 = new WritableFont(WritableFont.ARIAL, 20,WritableFont.BOLD);//加粗18号字体
//用于台头
WritableCellFormat wcf_head=new WritableCellFormat(BoldFont20);
wcf_head.setBorder(Border.ALL, BorderLineStyle.THIN);//线条
wcf_head.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
wcf_head.setAlignment(Alignment.CENTRE);//水平对齐
wcf_head.setWrap(false);//是否换行
// 用于标题
WritableCellFormat wcf_title = new WritableCellFormat(BoldFont12);
wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_title.setAlignment(Alignment.CENTRE); // 水平对齐
wcf_title.setWrap(false); // 是否换行
// 用于正文
WritableCellFormat wcf_text = new WritableCellFormat(NormalFont);
wcf_text.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_text.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_text.setAlignment(Alignment.CENTRE); // 水平对齐
wcf_text.setWrap(false); // 是否换行
// 用于跨行
WritableCellFormat wcf_merge = new WritableCellFormat(NormalFont);
wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); // 垂直对齐
wcf_merge.setAlignment(Alignment.LEFT);
wcf_merge.setWrap(true); // 是否换行
sheet.setRowView(0, 800);//设置行高
sheet.setRowView(1, 500);
sheet.addCell(new Label(0, 0, "保险信息", wcf_head));
sheet.mergeCells(0,0,4,0);//合并第一行的第1列到第一行的第4列
sheet.addCell(new Label(0, 1, "编号", wcf_title));
sheet.addCell(new Label(1, 1, "姓名", wcf_title));
sheet.addCell(new Label(2, 1, "年龄",wcf_title));
sheet.addCell(new Label(3, 1, "性别", wcf_title));
//把上面缓存中内容写到文件中去 2表示从第几行开始标题除外
addCellFreezeOrder(2,users,sheet,wcf_text);
workbook.write();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 把缓存中的数据写到文件当中去
* @param i
* @param users
* @param sheet
* @param wcf_text
*/
private static void addCellFreezeOrder(int i, List<User> users,
WritableSheet sheet, WritableCellFormat wcf_text) {
WritableFont wcf_font=(WritableFont) wcf_text.getFont();//用于正文、
//设置文字格式类型按照文本格式输出
WritableCellFormat wcf_numberformat=new WritableCellFormat(wcf_font,NumberFormats.TEXT);
try {
wcf_numberformat.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf_numberformat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_numberformat.setAlignment(Alignment.LEFT); // 水平对齐
wcf_numberformat.setWrap(false); // 是否换行
for(User s:users){
sheet.addCell(new Label(0,i,s.getSequen()+"",wcf_text));
sheet.addCell(new Label(1,i,s.getName(),wcf_text));
sheet.addCell(new Label(2,i,s.getAge()+"",wcf_text));
sheet.addCell(new Label(3,i,s.getSex(),wcf_text));
i++;
}
} catch (Exception e) {
e.printStackTrace();
}
}
//实体类
class User{
private int sequen;
private String name;
private int age;
private String sex;
public User(int sequen, String name, int age, String sex) {
super();
this.sequen = sequen;
this.name = name;
this.age = age;
this.sex = sex;
}
public int getSequen() {
return sequen;
}
public void setSequen(int sequen) {
this.sequen = sequen;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
