java操作poi怎么更改excel中的数据

[wspsky]

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

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的关闭操作，导致了数据的修改没有保存

[wspsky]

IO流关了 结果还是没有更改

[edotxp]

请查看 http://topic.csdn.net/u/20111010/16/d543e666-733f-469d-94d4-eec5773afec4.html?20889

[magong]

修改的只是内存中的副本，还有自己主动写文件的，如
FileOutputStream stream;
stream = new FileOutputStream(new File(fileToBeRead ));
workbook.write(stream);
stream.close();

[liuyuhua0066]

修改完cell的值 还需要保存一下excel

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

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,"更改测试");
	}
}

[wspsky]

解决了 是没写FileOutputStream
FileOutputStream fos = new FileOutputStream ("filepath");
wb.write(fos);
fos.cloes();