java用poi操作excel，2003,2007,2010

原文: http://happyqing.iteye.com/blog/1965570
通过POI统一读取Excel文件（兼容97-2003和2007+两种格式） http://cgs1999.iteye.com/blog/1525665
java中使用poi导出Excel详解 http://gaochun091024.blog.51cto.com/6643038/1242195
Apache POI 读取、写入Excel文件教程 http://05150212.iteye.com/blog/353428
java操作poi怎么更改excel中的数据 http://bbs.csdn.net/topics/370237960
POI 对 Excel 单元格颜色操作实例 http://www.oschina.net/code/snippet_12_16825
POI做导出Excel2003设置单元格中字体大小颜色，合并行列 http://blog.csdn.net/kunkun378263/article/details/9040639
POI格式化Cell样式2007/2010 http://www.bug315.com/article/6.htm
POI对Excel自定义日期格式的读取 http://yl-fighting.iteye.com/blog/1726285
POI读取Excel常见问题 http://blog.csdn.net/ghsau/article/details/10163043
使用Apache POI写的一个生成/解析 Excel的工具类 http://my.oschina.net/simpleton/blog/487510
POI中设置Excel单元格格式样式（居中，字体，边框等） http://blog.csdn.net/hoking_in/article/details/7919368
POI 设置单元格背景颜色 http://xiaohewoai.iteye.com/blog/1300817
POI设置EXCEL单元格格式为文本、小数、百分比、货币、日期、科学计数法和中文大写

使用POI修改Excel后进行保存 http://my.oschina.net/Early20/blog/515081

读取数据:

Java代码

package com.urt.module.excel;

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelPoi {

public static void main(String[] args) {

String fileToBeRead = "D:\\test.xlsx";

Workbook workbook;

try {

if (fileToBeRead.indexOf(".xlsx") > -1) {

workbook = new XSSFWorkbook(new FileInputStream(fileToBeRead));

} else {

workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));

}

//HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead)); //2003 创建对Excel工作簿文件的引用

//XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(fileToBeRead)); //2007,2010 创建对Excel工作簿文件的引用

Sheet sheet = workbook.getSheet("Sheet1"); // 创建对工作表的引用

int rows = sheet.getPhysicalNumberOfRows();// 获取表格的

int cells = 0;

for (int r = 0; r < rows; r++) { // 循环遍历表格的行

if (r == 0) {

//在第一行标题行计算出列宽度,因为数据行中可能会有空值

cells = sheet.getRow(r).getLastCellNum();

continue;

}

String value = "";

Row row = sheet.getRow(r); // 获取单元格中指定的行对象

if (row != null) {

//int cells = row.getPhysicalNumberOfCells();// 获取一行中的单元格数

//int cells = row.getLastCellNum();// 获取一行中最后单元格的编号（从1开始 for (short c = 0; c < cells; c++) { // 循环遍历单元格中的列

for (short c = 0; c < cells; c++) {

Cell cell = row.getCell((short) c); // 获取指定单元格中的列

if (cell != null) {

if (cell.getCellType() == Cell.CELL_TYPE_STRING) { // 判断单元格的值是否为字符串类型

value += cell.getStringCellValue() + ",";

} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // 判断单元格的值是否为数字类型

//if(DateUtil.isCellDateFormatted(cell)){

// cell.getDateCellValue();

//日期型 

//}

value += cell.getNumericCellValue() + ",";

} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { // 判断单元格的值是否为布尔类型

value += cell.getStringCellValue() + ",";

}

String[] str = value.split(",");

System.out.println(value);

}

} catch (Exception e) {

e.printStackTrace();

}

创建:

Java代码

import org.apache.poi.hssf.usermodel.*;

import java.io.FileOutputStream;

import java.io.IOException;

publicclass CreateCells

{

publicstaticvoid main(String[] args)

throws IOException

{

HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象

HSSFSheet sheet = wb.createSheet("new sheet");//建立新的sheet对象

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short)0);//建立新行

// Create a cell and put a value in it.

HSSFCell cell = row.createCell((short)0);//建立新cell

cell.setCellValue(1);//设置cell的整数类型的值

// Or do it on one line.

row.createCell((short)1).setCellValue(1.2);//设置cell浮点类型的值

row.createCell((short)2).setCellValue("test");//设置cell字符类型的值

row.createCell((short)3).setCellValue(true);//设置cell布尔类型的值

HSSFCellStyle cellStyle = wb.createCellStyle();//建立新的cell样式

cellStyle.setDataFormat(HSSFDataFormat.getFormat("m/d/yy h:mm"));//设置cell样式为定制的日期格式

HSSFCell dCell =row.createCell((short)4);

dCell.setCellValue(new Date());//设置cell为日期类型的值

dCell.setCellStyle(cellStyle); //设置该cell日期的显示格式

HSSFCell csCell =row.createCell((short)5);

csCell.setEncoding(HSSFCell.ENCODING_UTF_16);//设置cell编码解决中文高位字节截断

csCell.setCellValue("中文测试_Chinese Words Test");//设置中西文结合字符串

row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR);//建立错误cell

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

}

更改数据:

Java代码

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

}

不静之心

java用poi操作excel，2003,2007,2010

发表评论取消回复

访问信息

功能

近期评论

不静之心

java用poi操作excel，2003,2007,2010

发表评论 取消回复

访问信息

功能

近期评论

发表评论取消回复