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

我不是女神ヾ 2021-11-01 07:24 365阅读 0赞

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

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

pom文件如下

<dependencies>  
  
<dependency>  
<groupId>net.sourceforge.jexcelapi</groupId>  
<artifactId>jxl</artifactId>  
<version>2.6.12</version>  
</dependency>  
  
<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();  
\}

转载于:https://www.cnblogs.com/ffaiss/p/11241990.html