java 写入xlsx文件_JAVA创建并写入内容到xlsx文件

首先需要在web项目中导入jxl.jar 包

//action中代码

public String downloadReport(){

String path = System.getProperty("java.io.tmpdir") + "\\xlsx"+ (new java.util.Date().getTime());

List users=new ArrayList();//查询结果集

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 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 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 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;

}

}