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

来源：思诚科技更新时间：2018-4-25

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

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

pom文件如下

[html] view plain copy
<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实现具体代码如下：

[java] view plain copy
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();  
    }  
}  