java中的jxl及poi实现读取、修改、另存excel

java实现读取excel并修改部分内容最终写入到新的文件中

pom.xml引入相关的jxl.jar与poi.jar

pom文件如下

<dependencies>

<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->

<dependency>

<groupId>net.sourceforge.jexcelapi</groupId>

<artifactId>jxl</artifactId>

<version>2.6.12</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>3.17</version>

</dependency>

</dependencies>

java实现具体代码如下：

package Util;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.FileReader;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFComment;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.util.CellAddress;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;

import jxl.write.WritableCell;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

public class ExcelHandler {

/**

* jxl读取文件

*workbook为只读文件 可以读取内容

* @throws BiffException

* @throws IOException

*/

public static void ExcelReader(){

File file=new File("C:\\Users\\Administrator\\Desktop\\excels\\example.xls");

Workbook workbook;

try {

workbook = Workbook.getWorkbook(file);

Sheet sheet=workbook.getSheet(0);

Cell cell=sheet.getCell(2, 1);

Cell cell1=sheet.getCell(2, 1);

Cell cell2=sheet.getCell(2, 1);

Cell cell3=sheet.getCell(2, 1);

System.out.println(cell.getContents());

} catch (BiffException | IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**‘

* jxl实现excel读取并输出

* 读取excel修改之后重新输出

*/

public static void ModifyExcelAndOutput() {

try {

File file=new File("C:\\Users\\Administrator\\Desktop\\excels\\example.xls");

//这里读出来的workbook作为模版

Workbook workbook=Workbook.getWorkbook(file);

//这里是将要输出的workbook

for(int i=0;i<2;i++){//这里模拟输出两个文件

//输出文件名

String outFileName="C:\\Users\\Administrator\\Desktop\\excels\\output"+i+".xls";

// jxl.Workbook 对象是只读的，所以如果要修改Excel，需要创建一个可读的副本，副本指向原Excel文件（即下面的new File(excelpath)）

//WritableWorkbook如果直接createWorkbook模版文件会覆盖原有的文件

WritableWorkbook writeBook=Workbook.createWorkbook(new File(outFileName),workbook);

//读取第一个sheet

WritableSheet sheet=writeBook.getSheet(0);

//读取将要修改的cell

WritableCell cell=sheet.getWritableCell(2, 1);

//获取上一部cell的格式

jxl.format.CellFormat cf=cell.getCellFormat();

Label lable=new Label(2, 1, "商户名称：修改后的商户名"+i);

//将修改后的单元格格式设置成和原来一样的

lable.setCellFormat(cf);

//将修改后的cell放回sheet中

sheet.addCell(lable);

writeBook.write();

writeBook.close();

}

workbook.close();

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* poi实现excel修改

* 读取excel并且修改部分内容并输出

*/

public static void ModifyAndExport() {

InputStream io;

try {

io = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\excels\\output0.xls"));

HSSFWorkbook workbook = new HSSFWorkbook(io);

HSSFSheet sheet=workbook.getSheetAt(0);

for(int i=2;i<5;i++){

HSSFRow row=sheet.getRow(4);

HSSFCell cell=row.getCell(0);

cell.setCellValue("联系人姓名：王"+i+"麻子");

HSSFCell cell1=row.getCell(3);

cell1.setCellValue("手机："+i+"10110");

String outputPath="C:\\Users\\Administrator\\Desktop\\excels\\output"+i+".xls";

FileOutputStream fo=new FileOutputStream(new File(outputPath));

workbook.write(fo);

}

workbook.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void main(String[] args) {

ModifyAndExport();

}