本文共 5379 字,大约阅读时间需要 17 分钟。
java之poi操作excel-基本的excel读写操作
接下来介绍一下采用java操作excel的第三方框架:poi,对于poi,我想搞过批量导入、导出数据的博友都应该熟悉(当然啦,批量导入导出excel数据还有jxl,这里我就不介绍了!)。poi是apache jakarta项目的子项目,主要的用途在于提供一组用于操作windows 文档的java api,如操作word,excel,powerpoint等,详细介绍诸位博友可以看看其他的网络介绍。
本篇博文将介绍一下采用poi读写excel,为后续介绍的批量导入、导出excel数据做铺垫!
工欲善其事,必先利其器。首先,需要poi的Jar包,就一个而已,可以到官网下,也可以来我这里: ,除此之外,就是需要对excel的基本一些知识需要有所了解,如sheet,row,cell等概念。最后需要明确一点:先有excel文件(工作簿),再有sheet,再有row,最后才有cell,明确了这一点,其实在操作excel上已经迈出了第一步!
废话不多说,直接上代码吧,代码中我已经有所注释,如果需要扩展功能,可以去查看poi的相关api,或者去网上下载一些pdf、word文档来瞧瞧。我找了一篇,没全看完,但是觉得还行,可以来我这里下载:
下面是我建立的jar project的总体图:这里,我只介绍ExcelRead和ExcelWrite,即excel读写操作。
![](https://img-blog.csdn.net/20161023113116567?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
下面是ExcelWrite:
- package com.poi.second;
-
- import java.io.FileOutputStream;
- 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;
- import org.apache.poi.ss.usermodel.CreationHelper;
-
-
-
-
-
-
- public class ExcelWrite {
- public static void main(String[] args) throws Exception{
-
-
- HSSFWorkbook wb=new HSSFWorkbook();
- HSSFSheet sheet=wb.createSheet("第一个sheet");
-
-
- HSSFRow row=sheet.createRow(0);
-
-
- row.createCell(0).setCellValue(true);
- row.createCell(1).setCellValue("钟林森");
- row.createCell(2).setCellValue(23);
-
-
- HSSFCell cell=row.createCell(3);
- cell.setCellValue(6000);
- HSSFCellStyle cellStyle = wb.createCellStyle();
- cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
- cell.setCellStyle(cellStyle);
-
-
- CreationHelper createHelper=wb.getCreationHelper();
- HSSFCellStyle style=wb.createCellStyle();
- style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
-
- cell=row.createCell(4);
- cell.setCellValue(new Date());
- cell.setCellStyle(style);
-
-
- FileOutputStream out=new FileOutputStream("E:\\java_BasicWeb\\someFiles\\excel写数据.xls");
- wb.write(out);
- out.close();
-
- System.out.println("写完了!");
- }
- }
我们看写回磁盘的那个 “excel写数据.xls”文件: ![](https://img-blog.csdn.net/20161023113606910?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
打开来看一下:成功写入
![](https://img-blog.csdn.net/20161023113722333?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
接下来,我们来读取excel文件,刚刚写入的那个磁盘的“excel读数据.xls”,打开先来看一下:
![](https://img-blog.csdn.net/20161023113819271?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
我们目的是读取这两行数据,放到控制台来瞧瞧(其实也可以写到数据库或者另外一个excel文件中: 然后你会发现其实这就是 批量导入、导出的雏形
)
上代码ExcelRead:
- package com.poi.second;
-
- import java.io.FileInputStream;
- import java.io.InputStream;
- 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;
- import org.apache.poi.poifs.filesystem.POIFSFileSystem;
-
-
-
-
-
-
- public class ExcelRead {
- public static void main(String[] args) throws Exception {
-
-
- InputStream stream = new FileInputStream("E:\\java_BasicWeb\\someFiles\\excel读数据.xls");
- POIFSFileSystem fs = new POIFSFileSystem(stream);
- HSSFWorkbook wb = new HSSFWorkbook(fs);
-
-
- HSSFSheet sheet = wb.getSheetAt(0);
- if (sheet == null) {
- return;
- }
-
-
- for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
- HSSFRow row = sheet.getRow(rowNum);
- if (row == null) {
- continue;
- }
-
-
- for(int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
- HSSFCell cell = row.getCell(cellNum);
- if (cell == null) {
- continue;
- }
-
-
- String strVal=readCellSecondMethod(cell);
- if (cellNum==2) {
- strVal=strVal.contains(".")?strVal.substring(0, strVal.indexOf(".")):strVal;
- }
- System.out.print(" " + strVal);
-
-
-
-
- }
- System.out.println();
- }
-
- stream.close();
- }
-
-
-
-
-
-
-
- public static String readCellFirstMethod(HSSFCell cell) {
- if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
- return String.valueOf(cell.getBooleanCellValue());
- } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
- return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
- }
- return String.valueOf(cell.getNumericCellValue());
- } else {
- return cell.getStringCellValue();
- }
- }
-
-
-
-
-
-
-
- public static String readCellSecondMethod(HSSFCell cell) {
-
- if (cell == null) {
- return "";
- }
- switch (cell.getCellType()) {
-
-
- case HSSFCell.CELL_TYPE_NUMERIC:
-
-
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
- return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
- }
-
- return String.valueOf(cell.getNumericCellValue());
-
-
-
- case HSSFCell.CELL_TYPE_STRING:
- return cell.getStringCellValue();
-
-
- case HSSFCell.CELL_TYPE_FORMULA:
- return cell.getCellFormula();
-
-
- case HSSFCell.CELL_TYPE_BLANK:
- return "";
-
-
- case HSSFCell.CELL_TYPE_BOOLEAN:
- return cell.getBooleanCellValue() + "";
-
-
- case HSSFCell.CELL_TYPE_ERROR:
- return cell.getErrorCellValue() + "";
- }
-
- return "";
- }
-
- }
看控制台:
![](https://img-blog.csdn.net/20161023114702447?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
好了,就介绍到这里吧,接下来,将写一个poi操作excel之批量导入、批量导出功能(包括模板文件的导入、导出功能)。