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;

}