jxl的使用

一、生成Excel的类
package  test; 
import java.io.File;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public   class CreateExcel   {
    public   static   void main(String args[])   {
        try    {
            // 打开文件
            WritableWorkbook book = Workbook.createWorkbook( new File( " test.xls " ));
            // 生成名为“第一页”的工作表,参数0表示这是第一页
            WritableSheet sheet = book.createSheet( " 第一页 " , 0 );
            // 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
            // 以及单元格内容为test
            Label label =   new Label( 0 , 0 , " test " );
            // 将定义好的单元格添加到工作表中
            sheet.addCell(label);
/*
* 生成一个保存数字的单元格 必须使用Number的完整包路径,否则有语法歧义 单元格位置是第二列,第一行,值为789.123
*/
            jxl.write.Number number =   new jxl.write.Number( 1 , 0 , 555.12541 );
            sheet.addCell(number);// 写入数据并关闭文件
            book.write();
            book.close();
            
        }   catch (Exception e)   {
            System.out.println(e);
        }
}
}
编译执行后,会产生一个Excel文件。 
 
二、读取Excel
 
package test;
 // 读取Excel的类
 import java.io.File;
 import jxl.Cell;
 import jxl.Sheet;
 import jxl.Workbook;
 public   class ReadExcel   {
     public   static   void main(String args[])   {
         try    {
            Workbook book = Workbook.getWorkbook( new File( " test.xls " ));
             // 获得第一个工作表对象
             Sheet sheet = book.getSheet( 0 );
             // 得到第一列第一行的单元格
             Cell cell1 = sheet.getCell( 0 , 0 );
            String result = cell1.getContents();
            System.out.println(result);
            book.close();
        }   catch (Exception e)   {
            System.out.println(e);
        }
}
}
程序执行结果:test
 
三、修改Excel
 
package test;
 import java.io.File;
 import jxl.Workbook;
 import jxl.write.Label;
 import jxl.write.WritableSheet;
 import jxl.write.WritableWorkbook;
 public   class UpdateExcel   {
     public   static   void main(String args[])   {
         try    {
             // Excel获得文件
             Workbook wb = Workbook.getWorkbook( new File( " test.xls " ));
             // 打开一个文件的副本,并且指定数据写回到原文件
             WritableWorkbook book = Workbook.createWorkbook( new File( " test.xls " ), wb);
             // 添加一个工作表
             WritableSheet sheet = book.createSheet( " 第二页 " , 1 );
            sheet.addCell( new Label( 0 , 0 , " 第二页的测试数据 " ));
            book.write();
            book.close();
        }   catch (Exception e)   {
            System.out.println(e);
        }
}
}
 

四、这是一个使用流来导入的例子:

package cn.com.mc.bidmanager.excel;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.fileupload.FileItem;

import org.apache.commons.fileupload.FileUploadException;

import org.apache.commons.fileupload.disk.DiskFileItemFactory;

import org.apache.commons.fileupload.servlet.ServletFileUpload;

public class ExcelImport {

       public InputStream getStream(HttpServletRequest request){

              DiskFileItemFactory factory = new DiskFileItemFactory();

        //设置内存缓冲区,超过后写入临时文件

        factory.setSizeThreshold(10240000);

        //设置临时文件存储位置

        factory.setRepository(new File( request.getSession().getServletContext()                       .getRealPath("")+"/document"));

              ServletFileUpload upload = new ServletFileUpload(factory);

              // 设置单个文件的最大上传值

              upload.setFileSizeMax(102400000);

              // 设置整个request的最大值

              upload.setSizeMax(102400000);

              List items = null;

              try {

                     items = upload.parseRequest(request);

              } catch (FileUploadException e1) {

                     e1.printStackTrace();

              }

              FileItem item = (FileItem) items.get(0);

              InputStream stream = null;

              try {

                     stream = item.getInputStream();

              } catch (FileNotFoundException e) {

                     e.printStackTrace();

              } catch (IOException e){

                     e.printStackTrace();

              }

              return stream;

       }

}

 

Spring.xml:

<bean id="marksectDao" class="cn.com.mc.bidmanager.dao.impl.ComMarksectInfoDaoImpl" parent="daoTemplate"/>

<bean id="excelImportDao" class="cn.com.mc.bidmanager.excel.ExcelImport"/>

<bean id="bidBottomListDao" class="cn.com.mc.bidmanager.dao.impl.BidBottomListDaoImpl" parent="daoTemplate"/>

 

Service:

/**

* 功能:获得导入excel组件

*/

public ExcelImport getExcelImport() {

       return (ExcelImport) getWebApplicationContext().getBean( "excelImportDao");

}

/**

* 功能:导入标底清单

* @param sectId 标段ID

* @param stream 输入流

* @throws Exception

* @throws

*/

public void importBottomList(String sectId, InputStream stream) throws Exception  {

       // 删除标段下的所有标底清单

       bidBottomListDao.deleteAllBidBottomList(sectId);

       // 解析Excel文件

       Workbook w = Workbook.getWorkbook(stream);

       Sheet s = w.getSheet(0);

       int row = s.getRows();// 总行数

      ......

}

 

Action:

/**

* 功能:导入标底清单

* @param mapping

* @param form

* @param request

* @param response

* @return

* @throws Exception

*/

public ActionForward importBottomList(ActionMapping mapping, ActionForm form,      HttpServletRequest request, HttpServletResponse response) throws Exception{

       String sectId=request.getParameter("sectId");//标段ID

       InputStream stream=this.getExcelImport().getStream(request);

       ......

}

 

Jsp:

<script language="JavaScript" type="text/JavaScript">

function check(sectId)

{

    if(document.all.bottomList.file.value.trim().length==0)

       {

           alert("请选择要导入的Excel文件!");

           return;

       }

       document.all.bottomList.action="BidBottomListAction.do?forward=importBottomList&sectId="+sectId;

       document.all.bottomList.submit();

}

</script>

 

<input type="file" id="file" name="file" size="30" class="file"/>

<s:jspSecurity systemId="<%=(String)request.getSession().getAttribute("systemId")%>" resourceDesc="maintenance">

       <input type="button" value=" " onclick="check('<%=sectId %>');"

              class="btn_mouseout" onmouseover="this.className='btn_mouseover'"

                onmouseout="this.className='btn_mouseout'"

                onmousedown="this.className='btn_mousedown'"

                onmouseup="this.className='btn_mouseup'"/>

</s:jspSecurity>

 

Struts.xml:

<action path="/bidManager/list/BidBottomListAction"

      type="cn.com.mc.bidmanager.action.BidBottomListAction"

      parameter="forward">

      <forward name="bidBottomList" path="/bidManager/list/bidBottomList.jsp"/>

       <forward name="listCompare" path="/bidManager/list/listCompare.jsp"/>

       <forward name="bottomCell" path="/bidManager/list/bottomCellList.jsp"/>

</action>

阅读更多
个人分类: Java
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭