使用jxl操作excle文件

 

     有个需求

1.要把用户从数据库查询到的明细，可以支持一xls方式的导出。

2.用户可以讲xls格式的数据导入到数据库

 

    一开始就想到了使用jxl,先热热身，看看jxl.jar的基本使用方法

 

package cn.com.xinli.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Date;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


public class TestJXL
{
	public static void main(String[] args) throws Exception
	{   
		
		
		TestJXL.saveToEXcle();
		
		
    }  
	public static void saveToEXcle()
	{
		  // 准备设置excel工作表的标题    
        String[] title = {"编号","产品名称","产品价格","产品数量","生产日期","产地","是否出口"};    
       try {    
           // 获得开始时间    
           long start = System.currentTimeMillis();    
           // 输出的excel的路径    
            String filePath = "c:\\test.xls";    
           // 创建Excel工作薄    
            WritableWorkbook wwb;    
           // 新建立一个jxl文件,即在C盘下生成test.xls    
            OutputStream os = new FileOutputStream(filePath);    
            wwb=Workbook.createWorkbook(os);    
           // 添加第一个工作表并设置第一个Sheet的名字    
            WritableSheet sheet = wwb.createSheet("产品清单", 0);    
            Label label;    
           for(int i=0;i<title.length;i++){    
               // Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y    
               // 在Label对象的子对象中指明单元格的位置和内容    
                label = new Label(i,0,title[i]);    
               // 将定义好的单元格添加到工作表中    
                sheet.addCell(label);    
            }    
           // 下面是填充数据    
           /* 
             * 保存数字到单元格，需要使用jxl.write.Number 
             * 必须使用其完整路径，否则会出现错误 
             * */   
           // 填充产品编号    
           jxl.write.Number number = new jxl.write.Number(0,1,20071001);    
            sheet.addCell(number);    
           // 填充产品名称    
            label = new Label(1,1,"金鸽瓜子");    
            sheet.addCell(label);    
           /* 
             * 定义对于显示金额的公共格式 
             * jxl会自动实现四舍五入 
             * 例如 2.456会被格式化为2.46,2.454会被格式化为2.45 
             * */   
           jxl.write.NumberFormat nf = new jxl.write.NumberFormat("0.00");   //#.## 
           jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf);    
           // 填充产品价格    
           jxl.write.Number nb = new jxl.write.Number(2,1,0.1,wcf);    
            sheet.addCell(nb);    
           // 填充产品数量    
           jxl.write.Number numb = new jxl.write.Number(3,1,200);    
            sheet.addCell(numb);    
           /* 
             * 定义显示日期的公共格式 
             * 如:yyyy-MM-dd hh:mm 
             * */   
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");    
            String newdate = sdf.format(new Date());    
           // 填充出产日期    
            label = new Label(4,1,newdate);    
            sheet.addCell(label);    
           // 填充产地    
            label = new Label(5,1,"陕西西安");    
            sheet.addCell(label);    
           /* 
             * 显示布尔值 
             * */   
           jxl.write.Boolean bool = new jxl.write.Boolean(6,1,true);    
            sheet.addCell(bool);    
           /* 
             * 合并单元格 
             * 通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的 
             * 表示将从第x+1列，y+1行到m+1列，n+1行合并 
             * 
             * */   
            sheet.mergeCells(0,3,2,3);    
            label = new Label(0,3,"合并了三个单元格");    
            sheet.addCell(label);    
           /* 
             * 
             * 定义公共字体格式 
             * 通过获取一个字体的样式来作为模板 
             * 首先通过web.getSheet(0)获得第一个sheet 
             * 然后取得第一个sheet的第二列，第一行也就是"产品名称"的字体 
             * */   
            CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat();    
            WritableCellFormat wc = new WritableCellFormat();    
           // 设置居中    
            wc.setAlignment(Alignment.CENTRE);    
           // 设置边框线    
            wc.setBorder(Border.ALL, BorderLineStyle.THIN);    
           // 设置单元格的背景颜色    
            wc.setBackground(jxl.format.Colour.RED);    
            label = new Label(1,5,"字体",wc);    
            sheet.addCell(label);    
  
           // 设置字体    
           jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("隶书"),20);    
            WritableCellFormat font = new WritableCellFormat(wfont);    
            label = new Label(2,6,"隶书",font);    
            sheet.addCell(label);   
            
            /*设置列宽*/
	    	  sheet.setColumnView(0,12);
	    	  sheet.setColumnView(1,10);
	    	  sheet.setColumnView(2,8);
	    	  sheet.setColumnView(3,20);
	    	  sheet.setColumnView(4,8);
	    	  sheet.setColumnView(5,15);
	    	  sheet.setColumnView(6,12);
	    	  sheet.setColumnView(7,15);  
           // 写入数据    
            wwb.write();    
           // 关闭文件    
            wwb.close();    
           long end = System.currentTimeMillis();    
            System.out.println("----完成该操作共用的时间是:"+(end-start)/1000);    
        } catch (Exception e) {    
            System.out.println("---出现异常---");    
            e.printStackTrace();    
        }    
	}

}

 

 

下面完成需求1，导出

先决条件：

 a.mysql环境+struts

 b.建立测试使用的person表

CREATE TABLE `person` (                                
          `id` int(11) NOT NULL AUTO_INCREMENT,                
          `name` varchar(10) NOT NULL,                         
          PRIMARY KEY (`id`)                                   
        ) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=gbk  

 

 

分析：当用户点击了导出按钮，会弹出一个对话框，打开或者保存，这个是使用

 

response.setContentType("application/vnd.ms-excel;charset=GBK");//设置输出类型
response.addHeader("Content-Disposition","attachment;filename="+fileName);

 

然后使用输出流构造 WritableWorkbook 工作薄对象，吧从数据库查到的结果集，一行一行的写到xls中

 

 

需求2

 

要将xls中的数据读取出来，然后插到数据库中，关键是 拿到输入流，根据输入流构造WritableWorkbook 工作薄对象，然后使用

Sheet sheet = book.getSheet( 0 );
	         // 得到第一列第一行的单元格 
	         int columnum = sheet.getColumns(); // 得到列数 
	         int rownum = sheet.getRows(); // 得到行数 

 

 

得到行数和列数，写个双循环，拿到单元格里面的内容，构造sql语句，然后将它们插入到数据库中，关于根据单元格的内容批量执行sql，这里有一个效率问题，网上看到一个blog，受益很大（MYSQL）

 

 对于大数据量的插入，建议使用insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,的方式

 

 这样只执行一个sql 就将数据插入到数据库中了，使用循环 来insert，或者使用 pstmt.addBatch()的放似乎都没有这样效率高，效率在这样写sql效率比 循环 来insert，或者使用 pstmt.addBatch() 能提高10倍左右的时间，不过sql的长度是有限制的，我们可以自己设置每1000条记录提交一次来解决

 

下面贴一下Action中的关键代码

 

package cn.com.xinli.action.user;
import java.io.DataInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import cn.com.xinli.action.BaseAction;
import cn.com.xinli.bean.User;
import cn.com.xinli.util.DBUtil;

public class ExcleOperateAction extends BaseAction
{

	Logger log=Logger.getLogger(ExcleOperateAction.class);
	@Override
	public ActionForward doIt(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception
	{
		
		String operType = request.getParameter("operType");
		log.info("operType:"+operType);
		
		if (operType != null) 
		{
			if (operType.equals("upload")) 
			{
				return upload(mapping, form, request, response);
			}
			else if(operType.equals("download"))
			{
				return download(mapping, form, request, response);
			}
		}
		return null;
		
	}
	
	
	private ActionForward download(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) throws Exception
	{
		log.info("开始执行导出文件操作");
		
		String fileName = new String("记录明细".getBytes("GB2312"), "ISO_8859_1");
		response.setContentType("application/vnd.ms-excel;charset=GBK");//设置输出类型
		response.addHeader("Content-Disposition","attachment;filename="+fileName);
		try
		{
			 // 创建Excel工作薄   
		      WritableWorkbook wwb;   
		      wwb = Workbook.createWorkbook(response.getOutputStream());  
		      
		      WritableCellFormat wc = new WritableCellFormat(); 
			  /*居中*/
			  wc.setAlignment(Alignment.CENTRE); 
		      // 添加第一个工作表并设置第一个Sheet的名字   
		      WritableSheet sheet = wwb.createSheet("网银明细", 0);  
		      String[] title = { "ID", "Name"};  
		      Label label;   
		      for (int i = 0; i < title.length; i++) 
		      {   
		        // Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是z   
		        // 在Label对象的子对象中指明单元格的位置和内容   
		        label = new Label(i, 0, title[i],wc);
		        
		        // 将定义好的单元格添加到工作表中   
		        sheet.addCell(label);   
		      }  
		
     
      List<User> list=new ArrayList<User>();
      Connection conn=null;
      ResultSet rs=null;
      PreparedStatement pstmt;
      conn=DBUtil.getConnection();
      String sql="select * from person";
      pstmt=conn.prepareStatement(sql.toString());
      rs =pstmt.executeQuery();
      while(rs.next())
      {
    	 User user=new User();
    	 user.setId(rs.getString("id"));
    	 user.setName(rs.getString("name"));
    	 list.add(user);
      }
      if(list.size()>0)
      {
    	  int cols=0;
    	  int rows=1;
    	 
    	  for(User user : list)
    	  {
    		  Label label1=new Label(cols++,rows,user.getId(),wc);
    		  Label label2=new Label(cols++,rows,user.getName(),wc);
    		
    	      sheet.addCell(label1);
    	      sheet.addCell(label2);
    	      rows++;
    	      cols=0;
    	  }
    	  /*设置列宽*/
    	  sheet.setColumnView(0,12);
    	  sheet.setColumnView(1,10);
      
    	 
      }
      wwb.write();   
      // 关闭文件   
      wwb.close();
	}
		catch(Exception e)
		{
			
			e.printStackTrace();
		}
		return null;
	}


	private ActionForward upload(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) throws Exception
	{
		log.info("开始执行导入文件操作");
		try
		 {
		/*读取文件流*/
		DataInputStream in  = new DataInputStream(request.getInputStream());
		
		/*过滤HTTPHeader,否则jxl会报无法识别文件结尾的错误*/
		 for(int i = 0; i < 4; i++)
		 {
	        	in.readLine();
	     }
		 
			 Workbook book = Workbook.getWorkbook(in);
		     Sheet sheet = book.getSheet( 0 );
	         // 得到第一列第一行的单元格 
	         int columnum = sheet.getColumns(); // 得到列数 
	         int rownum = sheet.getRows(); // 得到行数 
	         System.out.println("列数："+columnum);
	         System.out.println("行数 ："+rownum);
	         Connection conn=null;
	         PreparedStatement pstmt;
	         conn=DBUtil.getConnection();
	         StringBuffer sql = new StringBuffer("INSERT INTO person (id,name) values ");
		        
		         for ( int i =0; i < rownum; i ++) // 循环进行读写,这里从1开始是不读取第一行
		           {
		        	 	if(i!=0)
		        	 	{
		        	 		sql.append(",");
		        	 	}
		        	 	sql.append("(");
			            for ( int j = 0 ; j < columnum; j ++ )   
			            {
				             String c=sheet.getCell(j, i).getContents(); // 得到第j列第i行的单元格的内容
				             System.out.println("("+j+","+i+"):"+c);
				             sql.append("'"+c+"'");
				             if(j<columnum-1)
				             {
				            	 sql.append(",");
				             }
	
			            } 
			            sql.append(")");
		            } 
		         	pstmt=conn.prepareStatement(sql.toString());
		         	
					pstmt.execute();
					request.setAttribute("message", "上传文件成功..");
					return mapping.findForward("result");
		 }
		 catch(Exception e)
		 {
			 request.setAttribute("message", "上传文件失败.."+e.getMessage());
			 return mapping.findForward("result");
		 }
		 
				
	}


	
	}

 

 

 

关于附件：

 

1.下载附件直接可以导入到eclipse

2.mysql+person 表 可以直接 使这个web项目跑起来

 

 

参考资料：

 

http://www.blogjava.net/hankchen/archive/2009/09/01/293430.html
http://www.pcstu.com/databases/MSsql/jq/20080929/63477.html

 http://fuhao9611.iteye.com/blog/72485