jxl的使用_jxl 公式-CSDN博客

本文链接：https://blog.csdn.net/chxzly1981/article/details/1820923

一、生成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:

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:

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>

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

<input type="button" value="上传" οnclick="check('<%=sectId %>');"

class="btn_mouseout" οnmοuseοver="this.className='btn_mouseover'"

οnmοuseοut="this.className='btn_mouseout'"

οnmοusedοwn="this.className='btn_mousedown'"

οnmοuseup="this.className='btn_mouseup'"/>

</s:jspSecurity>

Struts.xml:

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

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

parameter="forward">

</action>