67,519
社区成员
发帖
与我相关
我的任务
分享
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();
}
}
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,"更改测试");
}
}
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();
}
}
}