首先是需要导入poi包
1.poi创建Sheet页
public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook();
wb.createSheet("第一个Sheet页");
wb.createSheet("第二个Sheet页");
FileOutputStream fileOut = new FileOutputStream("c:\\E\\用poi搞出来的Sheet页.xls");
wb.write(fileOut);
fileOut.close();
}
执行后会在c盘下的文件夹E下创建出一个 用poi搞出来的Sheet页.xls (Excle文件)
2.poi创建单元格和设置值
public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("第一个Sheet页");
Row row = sheet.createRow(0);//创建第一行
Cell cell = row.createCell(0);//创建第一行的第一个的单元格
cell.setCellValue(1);//给单元格设置值
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue("这是一个字符串");
FileOutputStream fileOut = new FileOutputStream("c:\\E\\用poi搞出来的Cell.xls");
wb.write(fileOut);
fileOut.close();
}
3.设置时间值
public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook();//定义工作簿
Sheet sheet = wb.createSheet("第一个sheet页");
Row row = sheet.createRow(0);//创建一个行
Cell cell = row.createCell(0);//创建一个单元格,第一列
cell.setCellValue(new Date());//给单元格设置值
CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();//单元格样式
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
cell = row.createCell(1);//创建单元格,第二列
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
cell = row.createCell(2);//第三列
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
FileOutputStream fileOut = new FileOutputStream("c:\\E\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
4.各种类型的值设置
public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook();//定义工作簿
Sheet sheet = wb.createSheet("第一个sheet页");
Row row = sheet.createRow(0);//创建一个行
Cell cell = row.createCell(0);//创建一个单元格,第一列
cell.setCellValue(new Date());//给单元格设置值
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("c:\\E\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
5.遍历
public static void main(String[] args) throws Exception {
InputStream is = new FileInputStream("c:\\E\\二货名单.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
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;
}
//遍历列Cell
for(int cellNum=0;cellNum<=hssfRow.getLastCellNum();cellNum++) {
HSSFCell hssfCell = hssfRow.getCell(cellNum);//获取单元格
if(hssfCell==null) {
continue;
}
System.out.println(getValue(hssfCell));
}
System.out.println();
}
}
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());
}
}
6.抽取文本
public class Demo7 {
public static void main(String[] args) throws Exception {
InputStream is = new FileInputStream("c:\\E\\二货名单.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
ExcelExtractor excelExtractor = new ExcelExtractor(wb);
System.out.println(excelExtractor.getText());
}
}
<