java操作Excel之POI(1)

一、新建工作簿、sheet、单元格

 public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook(); //定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个sheet页");
wb.createSheet("第二个sheet页");
Row row = sheet.createRow(0); //创建第0行
Cell cell = row.createCell(0); //创建一个单元格,第0行,第1列
cell.setCellValue(1); //给单元格设置值
row.createCell(1).setCellValue(1.2); //第2列
row.createCell(2).setCellValue("这是一个字符串");
row.createCell(3).setCellValue(false);
FileOutputStream fileOut = new FileOutputStream("E:\\用POI搞出来的工作簿3.xls");
wb.write(fileOut);
fileOut.close();
}

效果图:

java操作Excel之POI(1)

二、创建一个时间格式的单元格:

 public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook(); //定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个sheet页");
wb.createSheet("第二个sheet页");
Row row = sheet.createRow(0); //创建第0行
Cell cell = row.createCell(0); //创建一个单元格,第0行,第1列
cell.setCellValue(new Date()); CreationHelper creationHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
//设置单元格时间格式
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
cell = row.createCell(1); //第2列
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle); cell = row.createCell(2); //第3列
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle); FileOutputStream fileOut = new FileOutputStream("E:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}

效果:

java操作Excel之POI(1)

三、创建不同格式的单元格:

/**
* 创建不同格式的单元格
*/
public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook(); //定义一个新的工作簿
Sheet sheet = wb.createSheet("第一个sheet页");
Row row = sheet.createRow(0); //第一行
row.createCell(0).setCellValue(new Date()); //第1列
row.createCell(1).setCellValue(1);
row.createCell(2).setCellValue("一个字符串");
row.createCell(3).setCellValue(true);
row.createCell(4).setCellValue(HSSFCell.CELL_TYPE_NUMERIC);
row.createCell(5).setCellValue(false); FileOutputStream fileOut = new FileOutputStream("E:\\工作簿2.xls");
wb.write(fileOut);
fileOut.close();
}

java操作Excel之POI(1)

三、遍历一个工作簿:

 /**
* 遍历一个工作簿
*/
public static void main(String[] args) throws Exception {
InputStream is = new FileInputStream("E:\\二货.xls");
POIFSFileSystem ps = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(ps);
HSSFSheet hssfSheet = wb.getSheetAt(0); //获取第一个sheet页
if(hssfSheet == null){
return;
}
//遍历row
for(int rowNum=0; rowNum<=hssfSheet.getLastRowNum(); rowNum++){
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow == null){
continue;
}
//遍历行
for(int cellNum=0; cellNum<=hssfRow.getLastCellNum(); cellNum++){
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if(hssfCell == null){
continue;
}
System.out.print(getValue(hssfCell) + " ");
} System.out.println();
} is.close();
} private static String getValue(HSSFCell hssfCell){
if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else{
return String.valueOf(hssfCell.getStringCellValue());
}
}

java操作Excel之POI(1)

四、提取文本:

 /**
* 提取文本
*/
public static void main(String[] args) throws Exception {
InputStream is = new FileInputStream("E:\\二货.xls");
POIFSFileSystem ps = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(ps); ExcelExtractor excelExtractor = new ExcelExtractor(wb);
excelExtractor.setIncludeSheetNames(false); //不需要sheet页的名字
System.out.println(excelExtractor.getText());
}

java操作Excel之POI(1)

上一篇:CameraManager与CameraDevice与ICameraService的相应关系


下一篇:N皇后问题 各种优化