java 对excel操作 读取、写入、修改数据；导出数据库数据到excel
============前提加入jar包jxl.jar=========================
// 从数据库导出数据到excel
public List<Xskh> outPutExcel() {
String url= request.getSession().getServletContext()
.getRealPath("/")
+ "upload/"; // 创建文件
File dirPath = new File(ctxPath);
if (!dirPath.exists()) {
dirPath.mkdir();// 创建文件夹
}
List<Xskh> list = excelDao.getInfo(" select x from Xskh ");// 查询数据库导出资料
try {
WritableSheet sheet = null;
// 创建excel文件
WritableWorkbook book = Workbook.createWorkbook(new File(url+"data.xls"));
// 生成名为“第一页”的工作表，参数0表示这是第一页
sheet = book.createSheet(" 第一页 ", 0);
// 设置行宽、高
sheet.getSettings().setDefaultColumnWidth(20);
sheet.getSettings().setDefaultRowHeight(480);
// 设置字体
WritableFont font = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.RED);
WritableCellFormat wcfF = new WritableCellFormat(font);
// 字体居中
wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
// 自动换行
wcfF.setWrap(true);
// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
// 以及单元格内容为test
Label label = new Label(0, 0, "简称 ", wcfF);
// 将定义好的单元格添加到工作表中
sheet.addCell(label);
Label labelcomplay = new Label(1, 0, "公司", wcfF);
sheet.addCell(labelcomplay);
Label labeltel = new Label(2, 0, "固定电话", wcfF);
sheet.addCell(labeltel);
Label labelmobile = new Label(3, 0, "移动电话", wcfF);
sheet.addCell(labelmobile);
Label labelfax = new Label(4, 0, "传真", wcfF);
sheet.addCell(labelfax);
Label labeladdr = new Label(5, 0, "地址", wcfF);
sheet.addCell(labeladdr);
Label labelsite = new Label(6, 0, "网址", wcfF);
sheet.addCell(labelsite);
Label labelmail = new Label(7, 0, "邮箱", wcfF);
sheet.addCell(labelmail);
Label qq = new Label(8, 0, "qq", wcfF);
sheet.addCell(qq);
Label people = new Label(9, 0, "联系人", wcfF);
sheet.addCell(people);
Label linkbm = new Label(10, 0, "联系人部门", wcfF);
sheet.addCell(linkbm);
Label linkejob = new Label(11, 0, "联系人职务", wcfF);
sheet.addCell(linkejob);
Label linkphone = new Label(12, 0, "联系人电话", wcfF);
sheet.addCell(linkphone);
// 所有资料
for (int i = 0; i < list.size(); i++) {
Label labelad = new Label(0, i + 1, list.get(i).getName());
sheet.addCell(labelad);
Label labelmail2 = new Label(1, i + 1, list.get(i).getCompany());
sheet.addCell(labelmail2);
Label tel = new Label(2, i + 1, list.get(i).getTelephone());
sheet.addCell(tel);
Label mobile = new Label(3, i + 1, list.get(i).getMobile());
sheet.addCell(mobile);
Label fax = new Label(4, i + 1, list.get(i).getFax());
sheet.addCell(fax);
Label addr = new Label(5, i + 1, list.get(i).getAddress());
sheet.addCell(addr);
Label site = new Label(6, i + 1, list.get(i).getWww());
sheet.addCell(site);
Label mail = new Label(7, i + 1, list.get(i).getEmail());
sheet.addCell(mail);
Label q = new Label(8, i + 1, list.get(i).getQq());
sheet.addCell(q);
Label peo = new Label(9, i + 1, list.get(i).getLikeme());
sheet.addCell(peo);
Label bm = new Label(10, i + 1, list.get(i).getKfPeoBm());
sheet.addCell(bm);
Label zw = new Label(11, i + 1, list.get(i).getKfPeoZhiwei());
sheet.addCell(zw);
Label linktel = new Label(12, i + 1, list.get(i).getKfPeoTel());
sheet.addCell(linktel);//创建excel
}
// 写入数据并关闭文件
book.write();
book.close();
String result = download(request, response, url+ "/data.xls", "data.xls");//下载的方法
if ("unexists".equals(result)) {
request.setAttribute("msg", "抱歉，文件不存在，请重新导入文件！");
} else if ("error".equals(result)) {
request.setAttribute("msg", "抱歉，当前服务器用户过多，请稍后再试！");
} else {
request.setAttribute("msg", "数据导出成功！");
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
return list;
}
---------------------下载的方法------------------
public String download(HttpServletRequest request,
HttpServletResponse response, String urlandfile, String fileName)
throws Exception {
String msg = null;
try {
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html");
javax.servlet.ServletOutputStream ou = response.getOutputStream();
// 路径
java.io.File file = new java.io.File(urlandfile);
if (!file.exists()) {
System.out.println(file.getAbsolutePath() + " 文件不能存在!");
msg = "unexists";
return msg;
}
// 读取文件流
java.io.FileInputStream fileInputStream = new java.io.FileInputStream(
file);
// 下载文件
// 设置响应头和下载保存的文件名
response.setContentType("application/x-msdownload");// 弹出下载的框
response.setContentLength((int) file.length());// 下载统计文件大小的进度
response.setHeader("Content-Disposition", "attachment; filename="
+ fileName);
// 下载框的信息
if (fileInputStream != null) {
int filelen = fileInputStream.available();
// 文件太大时内存不能一次读出,要循环
byte a[] = new byte[filelen];
fileInputStream.read(a);
ou.write(a);
}
fileInputStream.close();
ou.close();
msg = "success";
} catch (Exception e) {
e.printStackTrace();
msg = "error";
}
// 解决完成后使用一切正常,但是总抛出java.lang.IllegalStateException异常主要是流还存在
return msg;
}
