Java 导出excel（二）

import java.io.File;

import java.io.IOException;

import java.util.List;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import jxl.Workbook;

import jxl.format.Alignment;

import jxl.format.Border;

import jxl.format.BorderLineStyle;

import jxl.format.Colour;

import jxl.format.UnderlineStyle;

import jxl.format.VerticalAlignment;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

import com.hbkjsf.susheguanli.fygl.FyglEntity;

import com.hbkjsf.susheguanli.log.Log;

import com.hbkjsf.susheguanli.wjjlgl.WjjlglEntity;

import com.hbkjsf.susheguanli.xsjbxxgl.XsjbxxglEntity;

/**

* 模块描述：宿舍卫生管理

* 

* @author wuyu

* 

*/

public class SswsglDeal {

/**

* 功能描述：生成EXCEL

* @param request

* @param ssid

* @param wsrq

*/

public static void createExcel(HttpServletRequest request, String ssid,

    String wsrq) {

   SswsglEntity entity = new SswsglEntity();

   List list = entity.findAll(ssid, wsrq);

   String fileName = "";

   String filePath = "/excel/";

   File file = new File(String.valueOf(request.getRealPath("/"))

     + filePath);

   if (!file.exists()) {

    file.mkdirs();

   }

   fileName = "宿舍卫生信息一览表" + ".xls";

   // getRealPath()方法取得根目录路径

   filePath = String.valueOf(request.getRealPath("/")) + filePath

     + fileName;

   // 开始创建EXCEL文件

   try {

    // 定制字体样式 20:代表字体大小 arial:字体 bold:是否加粗

    WritableFont font = new WritableFont(WritableFont.ARIAL, 10,

      WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,

      Colour.BLACK);

    WritableFont font1 = new WritableFont(WritableFont.ARIAL, 15,

      WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,

      Colour.BLACK);

    WritableFont font2 = new WritableFont(WritableFont.ARIAL, 17,

      WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,

      Colour.BLACK);

    // 定制EXCEL表格样式

    WritableCellFormat style = new WritableCellFormat(font1);

    style = new WritableCellFormat(font1);

    style.setAlignment(Alignment.CENTRE);

    style.setVerticalAlignment(VerticalAlignment.CENTRE);

    style.setBorder(Border.ALL, BorderLineStyle.THIN);

    style.setWrap(true);

    WritableCellFormat style1 = new WritableCellFormat(font);

    style1 = new WritableCellFormat(font);

    style1.setAlignment(Alignment.CENTRE);

    style1.setVerticalAlignment(VerticalAlignment.CENTRE);

    style1.setBorder(Border.ALL, BorderLineStyle.THIN);

    style1.setWrap(true);

    WritableCellFormat style2 = new WritableCellFormat(font2);

    style2 = new WritableCellFormat(font);

    style2.setAlignment(Alignment.LEFT);

    style2.setVerticalAlignment(VerticalAlignment.BOTTOM);

    style2.setBorder(Border.ALL, BorderLineStyle.THIN);

    style2.setWrap(true);

    WritableWorkbook book;

    // 创建一个可写入的EXCEL文件对象

    book = Workbook.createWorkbook(new File(filePath));

    // 创建一张工作表 sheet1:代表工作表的名称 ０：代表第一张工作表

    WritableSheet sheet = book.createSheet("sheet1", 0);

    // 设定工作表列宽 0:代表第一列 10:代表列的宽度

    sheet.setColumnView(0, 10);

    sheet.setColumnView(1, 15);

    sheet.setColumnView(2, 15);

    sheet.setColumnView(3, 15);

    sheet.setColumnView(4, 15);

    // sheet.setColumnView(4, 40);

    // 创建表头

    sheet.addCell(new Label(0, 0, "序号", style));

    sheet.addCell(new Label(1, 0, "宿舍编号", style));

    sheet.addCell(new Label(2, 0, "检查日期", style));

    sheet.addCell(new Label(3, 0, "评比成绩", style));

    sheet.addCell(new Label(4, 0, "备注", style));

    // sheet.addCell(new Label(4, 0, "备注", style));

    // 把数据库中的数据导入到EXCEL文件中去

    if (list != null && list.size() > 0) {

     Map map = null;

     String excelssid = "";

     String pbcj = "";

     String excelwsrq = "";

     String wsbz = "";

     int i;

     for (i = 0; i < list.size(); i++) {

      map = (Map) list.get(i);

      String xuhao = String.valueOf(i + 1);

      excelssid = String.valueOf(

        map.get("ssid") != null ? map.get("ssid") : "").trim();

      pbcj = String.valueOf(

        map.get("pbcjname") != null ? map.get("pbcjname") : "")

        .trim();

      excelwsrq = String.valueOf(

        map.get("wsrq") != null ? map.get("wsrq") : "")

        .trim();

      wsbz = String.valueOf(

        map.get("wsbz") != null ? map.get("wsbz")

          : "").trim();

      sheet.addCell(new Label(0, 1 + i, xuhao, style1));

      sheet.addCell(new Label(1, 1 + i, excelssid, style1));

      sheet.addCell(new Label(2, 1 + i, excelwsrq, style1));

      sheet.addCell(new Label(3, 1 + i, pbcj, style1));

      sheet.addCell(new Label(4, 1 + i, wsbz, style1));

     }

    } else {

    }

    WritableCellFormat stylefont = null;

    font = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD,

      false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

    stylefont = new WritableCellFormat(font);

    stylefont.setAlignment(jxl.format.Alignment.CENTRE);

    stylefont.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

    // 插入两行

    sheet.insertRow(0);

    sheet.addCell(new Label(0, 0, "宿舍卫生信息一览表", stylefont));

    // 合并第一行

    sheet.mergeCells(0, 0, 4, 0);

    book.write();

    book.close();

   } catch (IOException e) {

    e.printStackTrace();

   } catch (RowsExceededException e) {

    e.printStackTrace();

   } catch (WriteException e) {

    Log.add_info("生成EXCEL文件失败");

   }

   filePath = request.getRealPath("file://excel//") + "//";

   request.setAttribute("filePath", filePath);

   request.setAttribute("fileName", fileName);

}

/**

* 功能描述：修改一条卫生记录

* @param ssid

* @param wsrq

* @param pbcj

* @param wsbz

*/

public static void modifyone(String ssid, String wsrq, String pbcj, String wsbz) {

   SswsglEntity entity = new SswsglEntity();

   entity.modifyone(ssid, wsrq, pbcj, wsbz);

}

/**

* 功能描述：查询卫生记录

* 

* @param wsid

* @param request

*/

public static void findone(String wsid, HttpServletRequest request) {

   SswsglEntity entity = new SswsglEntity();

   String ssid = "";

   String wsrq = "";

   if (wsid != null) {

    String[] sswsid = wsid.split("=");

    ssid = sswsid[0];

    wsrq = sswsid[1];

   }

   Map map = entity.findone(ssid, wsrq);

   request.setAttribute("map", map);

}

/**

* 功能描述：添加一条卫生信息

* 

* @param ssid

* @param wsrq

* @param pbcj

* @param wsbz

*/

public static void addWS(String ssid, String wsrq, String pbcj, String wsbz) {

   SswsglEntity entity = new SswsglEntity();

   if (!ssid.equals("") && !wsrq.equals("")) {

    entity.insert(ssid, wsrq, pbcj, wsbz);

   } else {

   }

}

/**

* 功能描述：删除选中的卫生信息

* 

* @param stmp

*/

public static void deleteselect(String[] stmp) {

   SswsglEntity entity = new SswsglEntity();

   String wsid = "";

   String[] sswsid;

   String ssid = "";

   String wsrq = "";

   if (stmp != null) {

    for (int i = 0; i < stmp.length; i++) {

     wsid = stmp[i];

     sswsid = wsid.split("=");

     ssid = sswsid[0];

     wsrq = sswsid[1];

     entity.deleteselect(ssid, wsrq);

    }

   }

}

/**

* 功能描述：分页显示宿舍卫生信息

* 

* @param ssid

* @param request

* @param wsrq

*/

public static void search(String ssid, HttpServletRequest request,

    String wsrq) {

   SswsglEntity entity = new SswsglEntity();

   // 查询所有数据

   List totalList = entity.findAll(ssid, wsrq);

   System.out.println(totalList + "777777777777777777777777777777");

   // 定义总共有多少条数据

   int totalRecords;

   try {

    totalRecords = totalList.size();

   } catch (Exception e) {

    totalRecords = 1;

   }

   // 定义每页显示数据的条数

   int pageSize = 6;

   // 定义当前页

   int pageNO;

   String pageNumber = String.valueOf(

     request.getParameter("pageNO") != null ? request

       .getParameter("pageNO") : "").trim();

   if (pageNumber == null || pageNumber.equals("")) {

    pageNO = 1;

   } else {

    try {

     pageNO = Integer.parseInt(pageNumber);

    } catch (NumberFormatException e) {

     pageNO = 1;

    }

    if (pageNO <= 0) {

     pageNO = 1;

    }

   }

   // 定义总共有多少页

   int totalPages = totalRecords % pageSize == 0 ? totalRecords / pageSize

     : totalRecords / pageSize + 1;

   // 当前页大于总页数的时候

   if (pageNO > totalPages) {

    pageNO = totalPages;

   }

   // 定义当前页起始位置 注意：当前页的起始位置只能在当前页确定之后才能定义

   int startPos = (pageNO - 1) * pageSize;

   List infoList = entity.search(ssid, wsrq, startPos, pageSize);

   request.setAttribute("pageNO", pageNO);

   request.setAttribute("totalPages", totalPages);

   request.setAttribute("totalRecords", totalRecords);

   request.setAttribute("pageSize", pageSize);

   request.setAttribute("infoList", infoList);

}

}