java操作excel文件

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

采用POI操作excel

API:http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html

poi包：http://pan.baidu.com/s/1hmIQU

一.读取excel内容

1.excel内容的值如果不是string，则用getNumericCellValue()，得到double类型，再做相应转换，如果为string，则用getStringCellValue()

public static String getExcel(int index,int rowNum,int colNum) {//File file = new File("D:/BaiduYunDownload/excel/testdata.xls");
File file = new File("./POIexcel/testdata.xls");
String cellValue = null;
int rowN = rowNum-1;//将excel的行数-1Row row = null;
Cell cell= null;
HSSFCell hf = null;
// Cell cell_b = null; try {
FileInputStream in = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(in);
HSSFSheet sheet = wb.getSheetAt(index);//sheet页，index从0开始 //从哪行读取
// int firstRowNum = sheet.getFirstRowNum()+1;
// int lastRowNum = sheet.getLastRowNum(); row = sheet.getRow(rowN); //取得第几行
cell = row.getCell(colNum); //取得行的第3列,从0开始
if(cell!=null){
//((Object) hf).setEncoding(HSSFCell.ENCODING_UTF_16); //判断excel内容的数值类型
switch(cell.getCellType()) {
case Cell.CELL_TYPE_STRING://String
cellValue = cell.getStringCellValue().trim();
break;
case Cell.CELL_TYPE_NUMERIC://number
if(HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {//date
cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
cellValue = "";
}
}else {
cellValue = new DecimalFormat("###.###").format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值 if (!cell.getStringCellValue().equals("")) {
cellValue = cell.getStringCellValue();
} else {
cellValue = cell.getNumericCellValue() + "";
}
break; case HSSFCell.CELL_TYPE_BLANK:
break; case HSSFCell.CELL_TYPE_ERROR:
cellValue = "";
break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = (cell.getBooleanCellValue() == true ? "Y": "N");
break; default:
cellValue = "";
/*
if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC) {
double i = cell.getNumericCellValue();
cellValue = String.valueOf(i);
}else{
cellValue = cell.getStringCellValue().trim();
if(cellValue.equals("")){
System.out.println(rowNum+"行的值为空");
}
}
*/
}
}
}catch (Exception e) {
e.printStackTrace();
} return cellValue;
}

double转换为int：int i_yhfw= (int) Double.parseDouble(Demo.getExcel(index, 13));

二.设置excel内容

public static void setExcel(String path, int sheet, int row, int col,String value) { try {
File file = new File(path); FileInputStream in = new FileInputStream(file);
HSSFWorkbook hw = new HSSFWorkbook(in); HSSFSheet hsheet= hw.getSheetAt(sheet);//目标sheet的索引
HSSFRow hrow = hsheet.getRow(row-1);//目标行的索引
HSSFCell cell = hrow.createCell(col-1);//目标列的索引
HSSFRichTextString val = new HSSFRichTextString(value);
cell.setCellValue(val); OutputStream out = new FileOutputStream(file);//获取文件输出流
hw.write(out);//将内容写到excel
out.close();
in.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} }

以上的方法的是HSFF只能操作03的excel，通过官方api介绍，使用XSFF可以操作07的excel，故优化代码如下，自动识别传入的excel是03的还是07的。

package com.excel;import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;/**
* @author QiaoJiafei
* @version 创建时间：2015年11月12日上午10:35:03
* 类说明
*/
public class TestExcel037 {
public static void main(String args[]) {
System.out.println(getExcel("D:/03excel.xls",1,2,2));
System.out.println(getExcel("D:/07excel.xlsx",1,2,2)); } public static String getExcel(String path,int index,int rowNum,int colNum) {
File file = new File(path);
String cellValue = "";
Workbook wb = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
try {
FileInputStream in = new FileInputStream(file);
if(path.endsWith(".xls")) {
wb = new HSSFWorkbook(in);
sheet = wb.getSheetAt(index-1);
}else if (path.endsWith(".xlsx")) {
wb = new XSSFWorkbook(in);
sheet = wb.getSheetAt(index-1);
}
row = sheet.getRow(rowNum);
cell = row.getCell(colNum);
if(cell!=null){
switch(cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
cellValue = "";
}
}else {
cellValue = new DecimalFormat("###.###").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_FORMULA: if (!cell.getStringCellValue().equals("")) {
cellValue = cell.getStringCellValue();
} else {
cellValue = cell.getNumericCellValue() + "";
}
break; case Cell.CELL_TYPE_BLANK:
break; case Cell.CELL_TYPE_ERROR:
cellValue = "";
break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = (cell.getBooleanCellValue() == true ? "Y": "N");
break; default:
cellValue = "";
}
}
in.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} return cellValue; }}