java用org.apache.poi包操作excel
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");