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

}