java操作poi如何更改excel中的数据

java操作poi怎么更改excel中的数据
如题 现在要更改excel表中的数据 在网上找到个方法 按照他的写法数据没有修改成功

Java code

public static void test() {        String fileToBeRead = "C:\\project_TMP\\testPrj\\新規.xls"; // excel位置        int coloum = 3; // 比如你要获取第三列        try {            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(                    fileToBeRead));            HSSFSheet sheet = workbook.getSheet("Sheet1");            for (int i = 0; i <= sheet.getLastRowNum(); i++) {                HSSFRow row = sheet.getRow((short) i);                if (null == row) {                    continue;                } else {                        HSSFCell cell = row.getCell((short) 3);                        if (null == cell) {                            continue;                        } else {                            System.out.println(cell.getStringCellValue());                            int temp = Integer.parseInt(cell.getStringCellValue());                            cell.setCellValue(temp + 1);                        }                }            }        } catch (FileNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }

这个代码是网上的 我按照他的鞋 但是操作后 数据还是原来的 没有更改 请大神教教 谢谢

[解决办法]
修改文件最后还需要通过IO流操作来保存更改，这其实是很关键的一步，你代码里面没有IO的关闭操作，导致了数据的修改没有保存
[解决办法]
修改的只是内存中的副本，还有自己主动写文件的，如
FileOutputStream stream;
stream = new FileOutputStream(new File(fileToBeRead ));
workbook.write(stream);
stream.close();

[解决办法]
修改完cell的值 还需要保存一下excel

Java code

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class ChangeCell {    @SuppressWarnings("deprecation")    public static void main(String[] args) {        String fileToBeRead = "C:\\exp.xls"; // excel位置        int coloum = 1; // 比如你要获取第1列        try {            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(                    fileToBeRead));            HSSFSheet sheet = workbook.getSheet("Sheet1");            for (int i = 0; i <= sheet.getLastRowNum(); i++) {                HSSFRow row = sheet.getRow((short) i);                if (null == row) {                    continue;                } else {                    HSSFCell cell = row.getCell((short) coloum);                    if (null == cell) {                        continue;                    } else {                        System.out.println(cell.getNumericCellValue());                        int temp = (int) cell.getNumericCellValue();                        cell.setCellValue(temp + 1);                    }                }            }            FileOutputStream out = null;            try {                out = new FileOutputStream(fileToBeRead);                workbook.write(out);            } catch (IOException e) {                e.printStackTrace();            } finally {                try {                    out.close();                } catch (IOException e) {                    e.printStackTrace();                }            }        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }    }} 
 
[解决办法]
给你贴一个具体的更改实例，实例修改的是第一个sheet 第一行第一列的值 ，你自己改一下测试的文件路径就OK
Java code
package poi.excel;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFCell;import java.io.*;import java.util.Date;import java.sql.Timestamp;import java.text.DecimalFormat;/** * Created by IntelliJ IDEA. * User: admin * Date: 2011-10-10 * Time: 16:10:29 * To change this template use File | Settings | File Templates. */public class UpdateExcel2003 {    /**     * 只是一个demo，这里假设修改的值是String类型     * @param exlFile     * @param sheetIndex     * @param col     * @param row     * @param value     * @throws Exception     */    public static void updateExcel(File exlFile,int sheetIndex,int col,int row,String value)throws Exception{        FileInputStream fis=new FileInputStream(exlFile);        HSSFWorkbook workbook=new HSSFWorkbook(fis);//        workbook.        HSSFSheet sheet=workbook.getSheetAt(sheetIndex);        HSSFRow r=sheet.getRow(row);        HSSFCell cell=r.getCell(col);//        int type=cell.getCellType();        String str1=cell.getStringCellValue();        //这里假设对应单元格原来的类型也是String类型        cell.setCellValue(value);        System.out.println("单元格原来值为"+str1);        System.out.println("单元格值被更新为"+value);        fis.close();//关闭文件输入流        FileOutputStream fos=new FileOutputStream(exlFile);        workbook.write(fos);        fos.close();//关闭文件输出流    }    private String getCellValue(HSSFCell cell) {        String cellValue = "";        DecimalFormat df = new DecimalFormat("#");        switch (cell.getCellType()) {            case XSSFCell.CELL_TYPE_STRING:                cellValue = cell.getRichStringCellValue().getString().trim();                break;            case XSSFCell.CELL_TYPE_NUMERIC:                cellValue = df.format(cell.getNumericCellValue()).toString();                break;            case XSSFCell.CELL_TYPE_BOOLEAN:                cellValue = String.valueOf(cell.getBooleanCellValue()).trim();                break;            case XSSFCell.CELL_TYPE_FORMULA:                cellValue = cell.getCellFormula();                break;            default:                cellValue = "";        }        return cellValue;    }    /**     * @param args     */    public static void main(String[] args) throws Exception{        // TODO Auto-generated method stub//            下面改成你自己的xls文件进行测试，2003格式的，不能2007            File file=new File("resources/excel/stuInfo.xls");         //下面尝试更改第一行第一列的单元格的值          UpdateExcel2003.updateExcel(file,0,0,0,"更改测试");    }} 

 

查看更多 下一篇