1.Jakarta POI 是apache的子项目,目标是处理ol2对象。它提供了一组Windows文档操作的Java API。
2.EXCEL 结构
HSSFWorkbook excell 文档对象介绍
HSSFSheet excell的表单
HSSFRow excell的行
HSSFCell excell的格子单元
HSSFFont excell字体
HSSFName 名称
HSSFDataFormat 日期格式
在poi1.7中才有以下2项:
HSSFHeader sheet头
HSSFFooter sheet尾
和这个样式
HSSFCellStyle cell样式
辅助操作包括
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
3.简单的用法
创建Excel
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class NewWorkbook {
public static String outputFile = "C:/test1.xls";
public static void main(String[] args) {
try {
HSSFWorkbook wb = new HSSFWorkbook();//create new HSSFWorkbook object
FileOutputStream fileOut = new FileOutputStream(outputFile);
wb.write(fileOut);//Workbook-->test1.xls
fileOut.flush();
fileOut.close();
System.out.println("The file has been created.");
} catch (Exception e) {
e.printStackTrace();
}
}
}
简单的Excel写操作
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class CreateCells {
public static String fileTobewrite = "C:/test1.xls";
public static void main(String[] args) throws IOException {
try {
HSSFWorkbook wb = new HSSFWorkbook();//create new HSSFWorkbook object
HSSFSheet sheet = wb.createSheet("new sheet");// create new sheet object
//Create a row and put some cells in it. Rows are 0.
HSSFRow row = sheet.createRow(0);//create new row
//Create a cell and put a value in it.
HSSFCell cell = row.createCell(0);//create new cell
cell.setCellValue(1);//setting the cell value
//do it on one line
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue("test");
row.createCell(3).setCellValue(true);
HSSFCellStyle cellStyle = wb.createCellStyle();//new cell style
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));// set date style
HSSFCell dcell = row.createCell(4);//create new cell
dcell.setCellValue(new Date());
dcell.setCellStyle(cellStyle);
HSSFCell csCell = row.createCell(5);
csCell.setCellType(HSSFCell.ENCODING_UTF_16);
csCell.setCellValue("中文测试_Chinese Words Test");//set cell code
row.createCell(6).setCellType(HSSFCell.CELL_TYPE_ERROR);
//write the output to a file
FileOutputStream fileOut = new FileOutputStream(fileTobewrite);
wb.write(fileOut);
fileOut.flush();
fileOut.close();
System.out.println("The cells have been added.");
} catch (Exception e) {
e.printStackTrace();
}
}
}
简单的Excel读操作
import java.io.FileInputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ReadExcel {
public static String fileTobeRead = "C:/test1.xls";
public static String getCellValue(HSSFCell cell){
String value = null;
if (cell != null)
{
//get the type of the cell
int cellType = cell.getCellType();
switch (cellType)
{
//""
case HSSFCell.CELL_TYPE_BLANK :
value = "";
break;
//Boolean
case HSSFCell.CELL_TYPE_BOOLEAN :
value = cell.getBooleanCellValue() ? "TRUE" : "FALSE";
break;
//Error
case HSSFCell.CELL_TYPE_ERROR :
value = "ERR-" + cell.getErrorCellValue();
break;
//Formula
case HSSFCell.CELL_TYPE_FORMULA :
value = cell.getCellFormula();
break;
//Numeric
case HSSFCell.CELL_TYPE_NUMERIC :
//Date
if (HSSFDateUtil.isCellDateFormatted(cell))
{
//change to "yyyy-MM-dd"
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
value = sdf.format(cell.getDateCellValue());
}
//Number
else
{
value = cell.getNumericCellValue() + "";
}
break;
//String
case HSSFCell.CELL_TYPE_STRING :
value = cell.getStringCellValue();
break;
//Other
default :
value = "Unknown Cell Type:" + cell.getCellType();
}
}
return value;
}
public static void main(String[] args) {
try {
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(fileTobeRead));
HSSFSheet sheet = wb.getSheet("new sheet");
//getSheetAt(int index) first sheet index is 0.
int rowNum = sheet.getPhysicalNumberOfRows();
int cellNum;
System.out.println("Row number is " + rowNum);
HSSFRow row;
HSSFCell cell;
for(int i=0;i<sheet.getPhysicalNumberOfRows();i++){
row = sheet.getRow(i);
cellNum = row.getPhysicalNumberOfCells();
System.out.println("cell number is " + cellNum);
for(int j = 0; j < cellNum; j++){
cell=row.getCell(j);
System.out.println("row " + i + "cell "+ j + ":" + getCellValue(cell));
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
4.设置单元格格式
//set font style red and bold
HSSFFont font = wb.createFont();
font.setColor(HSSFFont.COLOR_RED);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//create style
HSSFCellStyle cellStyle1 = wb.createCellStyle();
cellStyle1.setFont(font);
//use this style
HSSFCell cell1 = row.createCell(1);
cell.setCellStyle(cellStyle1);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("Title");