Excel表的导入，导出

讲解jxl
//学会Excel得导入导出
首先用两个包：
jxl.jar ， 还有log4j-1.2.8日志包

 

 

讲解jxl
//学会excel得导入导出
首先用两个包：
jxl.jar ， 还有log4j-1.2.8日志包
import jxl.Workbook;
Workbook workbook=Workbook.getWorkbook(new File("作业.xls"));//对应文件
此类最后记得workbook.close();
Sheet sheet=workbook.getSheet(0);//获取工作表
Cell cell=sheet.getCell(2,3);//获取对应单元格值，从0开始，2表示第3列   3表示第4行
打印：cell.getContents();得到对应单元格得值
Cell[] cells=sheet.getColum(1);//获得第2列得所有值
CellType.EMPTY(单元类型，有空类型等)
if(cells[i].getType()!=CellType.EMPTY){...}
-----------------
上传excel表自动显示excel表中的数据   导入Excel表：
jsp 页面：
<form action="" method="Post"  enctype="multipart/form-data">
<td>上传Excel:</td>

<td><input type="file" name="excelFile"/></td>   //name的值与formbean的form类中的属性名相同，可以利用form配置得到

</form>

 

public ActionForward importproducts(ActionMapping mapping,ActionForm form ,HttpServletRequest request ,HttpServletResponse response) throws Exception{
  ExcelForm fileForm = (ExcelForm)form;
  FormFile formFile = fileForm.getFileInfo();
  
  System.out.println(formFile);
  System.out.println("Excel读取");
  

Workbook workbook = Workbook.getWorkbook(formFile.getInputStream());
  Sheet sheet = workbook.getSheet(0);//获取工作表
  productsDao dao=new productsImplDao();
  Cell[] cells = sheet.getColumn(0);//得到第一列的所有数据，放入cells数组
  for(int i = 1; i < cells.length; i++) {//根据第一列的数据个数得到行数
   String pid=sheet.getCell(0,i).getContents();//得到第0列，i行数据
   String pname=sheet.getCell(1,i).getContents();
   String pprice=sheet.getCell(2,i).getContents();
   String ptypename=sheet.getCell(3,i).getContents();
   String pappraise=sheet.getCell(4,i).getContents();
   dao.setImportExcel(pid,pname,pprice,ptypename,pappraise);
  }
  workbook.close();//关闭workbook
  return mapping.findForward("importexcelok");
 }

 

----------------------------------------------------------------------------------

导出Excel表

public ActionForward exportproducts(ActionMapping mapping,ActionForm form ,HttpServletRequest request ,HttpServletResponse response) throws Exception{
  
  System.out.println("Excel导出");
WritableWorkbook workbook=Workbook.createWorkbook(new FileOutputStream("d://allProducts.xls"));//导出表的路径

 

补充下：上面这句是错误的，倘若别人在客户端机器登陆网页下载，那么下载到的文件是在服务器上的，而不是在客户端机器上，故不可以这样写，正确的写法是：

String name="测试";
 response.setContentType("application/x-msdownload");
 response.setHeader("Content-disposition", "attachment;filename="+java.net.URLEncoder.encode(name,"UTF-8")+".xls"); //若是写成 "attachment;filename="+name+"xls";那么当你name为中文时，会出现乱码问题,所以需要转换，更详细的相关知识可以response.setheader()下载中文文件名乱码问题。地址：http://yaofeng911.javaeye.com/blog/473407

WritableWorkbook workbook=Workbook.createWorkbook(response.getOutputStream());
  

WritableSheet sheet = workbook.createSheet("工作表", 0);//
  Label label1 = new Label(0,0,"标号");
  Label label2 = new Label(1,0,"名称");//1列0行设置数据为"名称"
  Label label3= new Label(2,0,"价格");
  Label label4 = new Label(3,0,"类型");
  Label label5 = new Label(4,0,"评价");
  sheet.addCell(label1);//添加数据
  sheet.addCell(label2);
  sheet.addCell(label3);
  sheet.addCell(label4);
  sheet.addCell(label5);
  productsDao dao=new productsImplDao(); 
  List<Products> list=dao.query();
         int i=0;
  for (Products p : list) {
   i=i+1;
  sheet.addCell(new Label(0,i,p.getPid().toString()));
  sheet.addCell(new Label(1,i,p.getPname()));
  sheet.addCell(new Label(2,i,p.getPprice().toString()));
  sheet.addCell(new Label(3,i,p.getTypes().getTname()));
  sheet.addCell(new Label(4,i,p.getPappraise()));
   
   
  }
  workbook.write();
  workbook.close();
  return mapping.findForward("importexcelok");
 }

 

-----------------------以下为部分转载------------------------学以用之------------------------------

//添加数字
jxl.write.Number number = new jxl.write.Number(3, 4, 3.14159); //put the number 3.14159 in cell D5
sheet.addCell(number);

//添加带有字型Formatting的对象
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES,10,WritableFont.BOLD,true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelCF = new jxl.write.Label(4,4,"文本",wcfF);
sheet.addCell(labelCF);

//添加带有字体颜色,带背景颜色 Formatting的对象
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
wcfFC.setBackground(jxl.format.Colour.BLUE);
jxl.write.Label labelCFC = new jxl.write.Label(1,5,"带颜色",wcfFC);
sheet.addCell(labelCFC);

//添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1,1,3.1415926,wcfN);
sheet.addCell(labelNF);

//3.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0,2,false);
sheet.addCell(labelB);

//4.添加DateTime对象
jxl.write.DateTime labelDT = new jxl.write.DateTime(0,3,new java.util.Date());
sheet.addCell(labelDT);

//添加带有formatting的DateFormat对象
jxl.write.DateFormat df = new jxl.write.DateFormat("ddMMyyyyhh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1,3,new java.util.Date(),wcfDF);
sheet.addCell(labelDTF);

//和宾单元格
//sheet.mergeCells(int col1,int row1,int col2,int row2);//左上角到右下角
sheet.mergeCells(4,5,8,10);//左上角到右下角
wfc = new jxl.write.WritableFont(WritableFont.ARIAL,40,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
wchB.setAlignment(jxl.format.Alignment.CENTRE);
labelCFC = new jxl.write.Label(4,5,"单元合并",wchB);
sheet.addCell(labelCFC); //


//设置边框
jxl.write.WritableCellFormat wcsB = new jxl.write.WritableCellFormat();
wcsB.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THICK);
labelCFC = new jxl.write.Label(0,6,"边框设置",wcsB);
sheet.addCell(labelCFC);
workbook.write();
workbook.close();
}catch(Exception e)
{
e.printStackTrace();
}
System.out.println("end");
Runtime r=Runtime.getRuntime();
Process p=null;
//String cmd[]={"notepad","exec.java"};
String cmd[]={"C://Program Files//Microsoft Office//Office//EXCEL.EXE","out.xls"};
try{
p=r.exec(cmd);
}
catch(Exception e){
System.out.println("error executing: "+cmd[0]);
}


}
}