[color=red]通过POI统一读取Excel文件[/color]（兼容97-2003和2007+两种格式） [url]http://cgs1999.iteye.com/blog/1525665[/url]
java中[color=red]使用poi导出Excel[/color]详解 [url]http://gaochun091024.blog.51cto.com/6643038/1242195[/url]
Apache POI 读取、写入Excel文件教程 [url]http://05150212.iteye.com/blog/353428[/url]
POI格式化Cell样式2007/2010 [url]http://www.bug315.com/article/6.htm[/url]
[b][color=red]POI对Excel自定义日期格式的读取[/color][/b] [url]http://yl-fighting.iteye.com/blog/1726285[/url]
POI 设置单元格背景颜色 [url]http://xiaohewoai.iteye.com/blog/1300817[/url]
POI设置EXCEL[color=red]单元格格式[/color]为文本、小数、百分比、货币、日期、科学计数法和中文大写
读取数据:
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();
}
}创建:
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();
}
}更改数据:
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,"更改测试");
}
}
