Java操作excel-POI
添加依赖项
这里直接给出Maven配置文件
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
读取excel
static void Write(String path) throws InvalidFormatException, IOException
{
FileInputStream file = new FileInputStream(new File(path));
Workbook workbook = WorkbookFactory.create(file);//读取excel文件
Sheet sheet = workbook.getSheetAt(0);//获取第一个sheet页
//遍历行
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
//遍历列
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t\t");//输出bool类型值
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");//输出Int类型值
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t\t");//输出String类型值
break;
}
}
System.out.println("");
}
file.close();
FileOutputStream outFile =new FileOutputStream(new File(path));
workbook.write(outFile);
outFile.close();
}
更新excel
static void Update(String path) throws InvalidFormatException, IOException
{
FileInputStream file = new FileInputStream(new File(path));
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
Cell cell = sheet.getRow(1).getCell(2);//读取第2行第3个
cell.setCellValue(12554);//将值改成12554
file.close();
FileOutputStream outFile =new FileOutputStream(new File(path));
workbook.write(outFile);
outFile.close();
}
创建一个新的excel
static void Create(String path)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("伊泽rl");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("伊泽rl");
try {
FileOutputStream out =new FileOutputStream(new File(path));
workbook.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
添加公式
cell.setCellFormula("A1*B2*C3");
