java之jxl输入excel文件

注：原创作品，分享以供交流，转载请注明出处。

本博文需要引入jxl.jar文件

/*
	 * 功能：生成excel文件 
	 * date:20140502
	 * @Author: chenchaoyang
	 */
	public ActionForward ZfSaleDataToExcel(ActionMapping mapping,
			ActionForm form, HttpServletRequest request,
			HttpServletResponse response) throws IOException, ServletException,
			Exception {
		HttpSession session = request.getSession(false);
		Authorities auth = SessionUtil.getUserAuth(request);
		int marketid = PublicUseDB.getMarketID(session);
		int userid = PublicUseDB.getUserName(session, auth);
		if (marketid == -1 || userid == -1 || auth == null) {
			return new ActionForward("/home.do", true);
		}
		String begindate = (String) request.getParameter("begindate"); // 开始日期
		String enddate = (String) request.getParameter("enddate"); // 结束日期
		ArrayList colnmoney = SaleDB.getAllZfSaleDan(begindate, enddate); // 从作废结算表中得到作废结算信息(ty_trademoney_zf);
		ExcelUtil excelUtil = new ExcelUtil(); // 把数据生成Excel表格
		WritableCellFormat wc = excelUtil.getWc(); // 得到Excel表格所需要的三种单元格格式
		WritableCellFormat wc1 = excelUtil.getWc1();
		WritableCellFormat wc2 = excelUtil.getWc2();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		String currentDate = sdf.format(new Date());
		String fileName = "作废单表格" + currentDate;
		fileName = fileName + ".xls";
		OutputStream os = response.getOutputStream(); // 获得响应客户的输出流：
		WritableWorkbook wwb = excelUtil.getWorkBook(os);
		WritableSheet sheet = wwb.createSheet("作废单表格", 1);
		excelUtil.chengpiMergeCells(sheet, 0, 6, 0, 1);
		sheet.mergeCells(7, 0, 9, 0);
		excelUtil.chengpiMergeCells(sheet, 10, 11, 0, 1);
		sheet.mergeCells(12, 0, 13, 0);
		for (int L = 1; L <= 9; L++) // 调整列宽
		{
			sheet.setColumnView(L, 12);
		}
		for (int L1 = 10; L1 <= 13; L1++) // 调整列宽
		{
			sheet.setColumnView(L1, 14);
		}
		sheet.addCell(excelUtil.getLabel(0, 0, "品种", wc));
		sheet.addCell(excelUtil.getLabel(1, 0, "序号", wc));
		sheet.addCell(excelUtil.getLabel(2, 0, "毛重", wc));
		sheet.addCell(excelUtil.getLabel(3, 0, "去皮", wc));
		sheet.addCell(excelUtil.getLabel(4, 0, "净重", wc));
		sheet.addCell(excelUtil.getLabel(5, 0, "单价", wc));
		sheet.addCell(excelUtil.getLabel(6, 0, "小计", wc));
		sheet.addCell(excelUtil.getLabel(7, 0, "交易服务费", wc));
		sheet.addCell(excelUtil.getLabel(7, 1, "买方", wc));
		sheet.addCell(excelUtil.getLabel(8, 1, "卖方", wc));
		sheet.addCell(excelUtil.getLabel(9, 1, "利润", wc));
		sheet.addCell(excelUtil.getLabel(10, 0, "买方", wc));
		sheet.addCell(excelUtil.getLabel(11, 0, "卖方", wc));
		sheet.addCell(excelUtil.getLabel(12, 0, "刷卡", wc));
		sheet.addCell(excelUtil.getLabel(12, 1, "买方", wc));
		sheet.addCell(excelUtil.getLabel(13, 1, "卖方", wc));

		int sum = 2; // 控制表格的行
		for (int i = 0; i < colnmoney.size(); i++) {
			Ty_trademoney_zf colnm = (Ty_trademoney_zf) colnmoney.get(i);
			String billNo = colnm.getT_billno();
			ArrayList temp = (ArrayList) SaleTools
					.getZfSaledatabyBillno(billNo);
			sheet.mergeCells(1, sum, 9, sum);
			sheet.mergeCells(7, sum + 1, 7, sum + temp.size());
			sheet.mergeCells(8, sum + 1, 8, sum + temp.size());
			sheet.mergeCells(9, sum + 1, 9, sum + temp.size());
			sheet.mergeCells(10, sum + 1, 10, sum + temp.size());
			sheet.mergeCells(11, sum + 1, 11, sum + temp.size());
			sheet.mergeCells(12, sum + 1, 12, sum + temp.size());
			sheet.mergeCells(13, sum + 1, 13, sum + temp.size());
			sheet.mergeCells(10, sum, 13, sum);
			String diyi = "交易号：" + colnm.getT_billno() + "  货物总金额："
					+ SaleTools.getFormattedNumeric(colnm.getT_totalmoney())
					+ "  买方卡号：" + colnm.getMbuyersn() + "司磅员："
					+ colnm.getUcode() + "交易时间：" + colnm.getT_sucessid();
			String dier = "作废时间：" + colnm.getZf_time() + "作废人："
					+ SeaFoodTools.getUserName(colnm.getZf_user());
			String xuhao = "序号";
			String xuhaozhi = "11";
			sheet.addCell(excelUtil.getLabel(0, sum, String.valueOf(i + 1), wc1));
			sheet.addCell(excelUtil.getLabel(1, sum, diyi, wc1));
			sheet.addCell(excelUtil.getLabel(10, sum, dier, wc1));
			sum = sum + 1; // 控制整个表格的行
			for (int j = 0; j < temp.size(); j++) {
				Ty_tradedatas data = (Ty_tradedatas) temp.get(j);
				int lie = 0;
				sheet.addCell(excelUtil.getLabel(lie, sum, String
						.valueOf(SeaFoodTools.getYtProduct(data.getPsn())), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						String.valueOf(j + 1), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						String.valueOf(data.getT_weightgross()), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						String.valueOf(data.getT_weighttare()), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						String.valueOf(data.getT_jweight()), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						String.valueOf(data.getT_price()), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						String.valueOf(data.getT_subtotal()), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						String.valueOf(colnm.getT_buyerfee()), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						String.valueOf(colnm.getT_sellerfee()), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(
						lie,
						sum,
						String.valueOf(colnm.getT_sellerfee()
								+ colnm.getT_buyerfee()), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum, String
						.valueOf(SaleDB.getCustomerName(colnm.getMbuyercode())
								+ "(" + colnm.getMbuyercode() + ")"), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum, String
						.valueOf(SaleDB.getCustomerName(colnm.getMsellercode())
								+ "(" + colnm.getMsellercode() + ")"), wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						SaleDB.getCustomerName(colnm.getMbuyerpushcode()) + "("
								+ colnm.getMbuyerpushcode() + ")", wc2));
				lie++;
				sheet.addCell(excelUtil.getLabel(lie, sum,
						SaleDB.getCustomerName(colnm.getMsellerpushcode())
								+ "(" + colnm.getMsellerpushcode() + ")", wc2));
				sum = sum + 1;
				lie++;
			}
		}
		excelUtil.print(response, wwb, fileName);
		return null;
}

工具类：

package bill.seafood.tools;
import java.io.OutputStream;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WritableCellFormat;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.WritableFont;
import jxl.format.VerticalAlignment;
import javax.servlet.http.HttpServletResponse;

public class ExcelUtil {

	 /*
	  * 功能：生成excel文件
	  */
	 public  WritableWorkbook getWorkBook(OutputStream os) throws Exception
	  {
		 WritableWorkbook wwb = Workbook.createWorkbook(os);
		  return wwb;
	  }
	 /*
	  * 生成excel文件的sheet表
	  */
	  public  void setSheetTitle(String[] sheetTitle,WritableSheet sheet) throws Exception
	  {
	   WritableCellFormat wc = new WritableCellFormat();   
     wc.setAlignment(Alignment.CENTRE);  // 设置居中     
     wc.setBorder(Border.ALL, BorderLineStyle.THIN);   // 设置边框线    
     wc.setBackground(jxl.format.Colour.GRAY_25);  // 设置单元格的背景颜色   
     wc.setVerticalAlignment(VerticalAlignment.CENTRE);
     jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("隶书"),14); // 设置字体  
     wc.setFont(wfont);
		  Label label;
		  for(int i=0;i<sheetTitle.length;i++){    
              label = new Label(i,0,sheetTitle[i],wc);    
              sheet.addCell(label);   
          }   
	  }
	  /*
	   * 创建表中的数字的显示形式
	   */
	  public  WritableCellFormat getNumberFormat(String format)
	  {
		jxl.write.NumberFormat nf = new jxl.write.NumberFormat(format);   
        jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf); 
        return wcf;
	  }
	  /*
	   * 创建带格式的字符型单元格
	   */
	  public Label getLabel(int l,int h,String name,WritableCellFormat wc)
	  {
		  if(wc != null)
		  {
			  return new Label(l,h,name,wc);
		  }
		  else
		  {
			  return new Label(l,h,name); 
		  }
	  }
	  /*
	   * 创建带四舍五入的数字型单元格
	   */
	  public jxl.write.Number getNumber(int l,int h,double number,String foramt)
	  {
		  jxl.write.Number number1 = new jxl.write.Number(l,h,number,getNumberFormat(foramt));
	      return number1;
	  }
	  /*
	   * 创建bool类型的单元格
	   */
	  public jxl.write.Boolean  getBool(int l,int h,boolean boole)
	  {
		  jxl.write.Boolean bool = new jxl.write.Boolean(l,h,boole);
		  return bool;
	  }

	 public void chengpiMergeCells(WritableSheet sheet,int l1,int l2,int h1,int h2)throws Exception
	  {
		    for(int i = l1 ; i <= l2; i ++)
		  {
			  sheet.mergeCells(i, h1, i, h2);
		  } 
	  }

	 public void print(HttpServletResponse response,WritableWorkbook wwb,String fileName) throws Exception
	 {
	 	 response.setContentType("application/x-xls;charset=gbk"); // 通知客户文件的MIME类型：
		 response.setHeader("Content-Disposition", "attachment; filename="
				+ new String(fileName.getBytes("gb2312"), "iso8859-1"));
		wwb.write();
		wwb.close();
	 }

	 public WritableCellFormat getWc() throws Exception
	 {
	 	jxl.write.WritableFont wfont = new jxl.write.WritableFont(
		WritableFont.createFont("隶书"), 14); // 设置字体
	 	WritableCellFormat wc = new WritableCellFormat();
	 			wc.setAlignment(Alignment.CENTRE); // 设置居中
		wc.setVerticalAlignment(VerticalAlignment.CENTRE);
		wc.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
		wc.setBackground(jxl.format.Colour.GRAY_25); // 设置单元格的背景颜色
		wc.setFont(wfont);
		return wc;
	 }
	  public WritableCellFormat getWc1() throws Exception
	 {
    WritableCellFormat wc1 = new WritableCellFormat();
		wc1.setAlignment(Alignment.CENTRE); // 设置居中
		wc1.setVerticalAlignment(VerticalAlignment.CENTRE);
		wc1.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
		wc1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色
		//wc1.setFont(wfont1);
		return wc1;
	 }
	  public WritableCellFormat getWc2() throws Exception
	 {
    WritableCellFormat wc2 = new WritableCellFormat();
    wc2.setAlignment(Alignment.CENTRE); // 设置居中
		wc2.setVerticalAlignment(VerticalAlignment.CENTRE);
		wc2.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
    return wc2;
	 }

}