概述

  Excel是我们平时工作中比较常用的用于存储二维表数据的,JAVA也可以直接对Excel进行操作,分别有jxl和poi,2种方式。

  HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
  从官方文档中了解到:POI提供的HSSF包用于操作 Excel '97(-2007)的.xls文件,而XSSF包则用于操作Excel2007之后的.xslx文件。

  本片文章主要参考poi官网:IT虾米网

代码

  •    要使用poi,必须引入poi的jar包,maven依赖如下:
复制代码
 1 <dependency> 
 2     <groupId>org.apache.poi</groupId> 
 3     <artifactId>poi</artifactId> 
 4     <version>3.14</version> 
 5 </dependency> 
 6  
 7 <dependency> 
 8     <groupId>org.apache.poi</groupId> 
 9     <artifactId>poi-ooxml</artifactId> 
10     <version>3.14</version> 
11 </dependency> 
12 <dependency> 
13     <groupId>org.apache.poi</groupId> 
14     <artifactId>poi-ooxml-schemas</artifactId> 
15     <version>3.14</version> 
16 </dependency>
复制代码
  •   使用poi创建execl文件
    复制代码
      1 package test.hd.poi; 
      2  
      3 import java.io.FileOutputStream; 
      4 import java.io.IOException; 
      5 import java.util.Date; 
      6  
      7 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
      8 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 
      9 import org.apache.poi.ss.usermodel.Cell; 
     10 import org.apache.poi.ss.usermodel.CellStyle; 
     11 import org.apache.poi.ss.usermodel.ClientAnchor; 
     12 import org.apache.poi.ss.usermodel.Comment; 
     13 import org.apache.poi.ss.usermodel.CreationHelper; 
     14 import org.apache.poi.ss.usermodel.DataFormat; 
     15 import org.apache.poi.ss.usermodel.Drawing; 
     16 import org.apache.poi.ss.usermodel.Font; 
     17 import org.apache.poi.ss.usermodel.RichTextString; 
     18 import org.apache.poi.ss.usermodel.Row; 
     19 import org.apache.poi.ss.usermodel.Sheet; 
     20 import org.apache.poi.ss.usermodel.Workbook; 
     21 import org.apache.poi.ss.util.CellRangeAddress; 
     22  
     23 public class CreateExcel { 
     24  
     25     public static void main(String[] args) throws IOException, InterruptedException { 
     26         Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }; 
     27         for (int i = 0; i < wbs.length; i++) { 
     28             Workbook workbook = wbs[i]; 
     29             // 得到一个POI的工具类 
     30             CreationHelper createHelper = workbook.getCreationHelper(); 
     31  
     32             // 在Excel工作簿中建一工作表,其名为缺省值, 也可以指定Sheet名称 
     33             Sheet sheet = workbook.createSheet(); 
     34             // Sheet sheet = workbook.createSheet("SheetName"); 
     35  
     36             // 用于格式化单元格的数据 
     37             DataFormat format = workbook.createDataFormat(); 
     38  
     39             // 设置字体 
     40             Font font = workbook.createFont(); 
     41             font.setFontHeightInPoints((short) 20); // 字体高度 
     42             font.setColor(Font.COLOR_RED); // 字体颜色 
     43             font.setFontName("黑体"); // 字体 
     44             font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度 
     45             font.setItalic(true); // 是否使用斜体 
     46             // font.setStrikeout(true); //是否使用划线 
     47  
     48             // 设置单元格类型 
     49             CellStyle cellStyle = workbook.createCellStyle(); 
     50             cellStyle.setFont(font); 
     51             cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平布局:居中 
     52             cellStyle.setWrapText(true); 
     53  
     54             CellStyle cellStyle2 = workbook.createCellStyle(); 
     55             cellStyle2.setDataFormat(format.getFormat("#, ## 0.0")); 
     56  
     57             CellStyle cellStyle3 = workbook.createCellStyle(); 
     58             cellStyle3.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss")); 
     59  
     60             // 添加单元格注释 
     61             // 创建Drawing对象,Drawing是所有注释的容器. 
     62             Drawing drawing = sheet.createDrawingPatriarch(); 
     63             // ClientAnchor是附属在WorkSheet上的一个对象, 其固定在一个单元格的左上角和右下角. 
     64             ClientAnchor anchor = createHelper.createClientAnchor(); 
     65             // 设置注释位子 
     66             anchor.setRow1(0); 
     67             anchor.setRow2(2); 
     68             anchor.setCol1(0); 
     69             anchor.setCol2(2); 
     70             // 定义注释的大小和位置,详见文档 
     71             Comment comment = drawing.createCellComment(anchor); 
     72             // 设置注释内容 
     73             RichTextString str = createHelper.createRichTextString("Hello, World!"); 
     74             comment.setString(str); 
     75             // 设置注释作者. 当鼠标移动到单元格上是可以在状态栏中看到该内容. 
     76             comment.setAuthor("H__D"); 
     77  
     78             // 定义几行 
     79             for (int rownum = 0; rownum < 30; rownum++) { 
     80                 // 创建行 
     81                 Row row = sheet.createRow(rownum); 
     82                 // 创建单元格 
     83                 Cell cell = row.createCell((short) 1); 
     84                 cell.setCellValue(createHelper.createRichTextString("Hello!" + rownum));// 设置单元格内容 
     85                 cell.setCellStyle(cellStyle);// 设置单元格样式 
     86                 cell.setCellType(Cell.CELL_TYPE_STRING);// 指定单元格格式:数值、公式或字符串 
     87                 cell.setCellComment(comment);// 添加注释 
     88  
     89                 // 格式化数据 
     90                 Cell cell2 = row.createCell((short) 2); 
     91                 cell2.setCellValue(11111.25); 
     92                 cell2.setCellStyle(cellStyle2); 
     93  
     94                 Cell cell3 = row.createCell((short) 3); 
     95                 cell3.setCellValue(new Date()); 
     96                 cell3.setCellStyle(cellStyle3); 
     97  
     98                 sheet.autoSizeColumn((short) 0); // 调整第一列宽度 
     99                 sheet.autoSizeColumn((short) 1); // 调整第二列宽度 
    100                 sheet.autoSizeColumn((short) 2); // 调整第三列宽度 
    101                 sheet.autoSizeColumn((short) 3); // 调整第四列宽度 
    102  
    103             } 
    104  
    105             // 合并单元格 
    106             sheet.addMergedRegion(new CellRangeAddress(1, // 第一行(0) 
    107                     2, // last row(0-based) 
    108                     1, // 第一列(基于0) 
    109                     2 // 最后一列(基于0) 
    110             )); 
    111  
    112             // 保存 
    113             String filename = "C:/Users/H__D/Desktop/workbook.xls"; 
    114             if (workbook instanceof XSSFWorkbook) { 
    115                 filename = filename + "x"; 
    116             } 
    117  
    118             FileOutputStream out = new FileOutputStream(filename); 
    119             workbook.write(out); 
    120             out.close(); 
    121         } 
    122     } 
    123  
    124 }
    复制代码
  • 使用poi修改execl文件
    复制代码
     1 package test.hd.poi; 
     2  
     3 import java.io.FileInputStream; 
     4 import java.io.FileOutputStream; 
     5 import java.io.IOException; 
     6 import java.io.InputStream; 
     7  
     8 import org.apache.poi.EncryptedDocumentException; 
     9 import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 
    10 import org.apache.poi.ss.usermodel.Cell; 
    11 import org.apache.poi.ss.usermodel.Row; 
    12 import org.apache.poi.ss.usermodel.Sheet; 
    13 import org.apache.poi.ss.usermodel.Workbook; 
    14 import org.apache.poi.ss.usermodel.WorkbookFactory; 
    15  
    16  
    17  
    18 public class UpdateExcel { 
    19      
    20     public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException { 
    21          
    22         InputStream inputStream = new FileInputStream("C:/Users/H__D/Desktop/workbook.xls"); 
    23         //InputStream inp = new FileInputStream("workbook.xlsx"); 
    24  
    25         Workbook workbook = WorkbookFactory.create(inputStream); 
    26         Sheet sheet = workbook.getSheetAt(0); 
    27         Row row = sheet.getRow(2); 
    28         Cell cell = row.getCell(3); 
    29         if (cell == null) 
    30             cell = row.createCell(3); 
    31         cell.setCellType(Cell.CELL_TYPE_STRING); 
    32         cell.setCellValue("a test"); 
    33  
    34         // Write the output to a file 
    35         FileOutputStream fileOut = new FileOutputStream("C:/Users/H__D/Desktop/workbook.xls"); 
    36         workbook.write(fileOut); 
    37         fileOut.close(); 
    38          
    39     } 
    40      
    41 }
    复制代码
  • 使用poi解析excel文件
    复制代码
     1 package test.hd.poi; 
     2  
     3 import java.io.FileInputStream; 
     4 import java.io.FileOutputStream; 
     5 import java.io.IOException; 
     6 import java.io.InputStream; 
     7  
     8 import org.apache.poi.EncryptedDocumentException; 
     9 import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 
    10 import org.apache.poi.ss.usermodel.Cell; 
    11 import org.apache.poi.ss.usermodel.DataFormatter; 
    12 import org.apache.poi.ss.usermodel.DateUtil; 
    13 import org.apache.poi.ss.usermodel.Row; 
    14 import org.apache.poi.ss.usermodel.Sheet; 
    15 import org.apache.poi.ss.usermodel.Workbook; 
    16 import org.apache.poi.ss.usermodel.WorkbookFactory; 
    17 import org.apache.poi.ss.util.CellReference; 
    18  
    19 import com.microsoft.schemas.office.visio.x2012.main.CellType; 
    20  
    21  
    22  
    23 public class ReadExcel { 
    24      
    25     public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException { 
    26          
    27         InputStream inputStream = new FileInputStream("C:/Users/H__D/Desktop/workbook.xls"); 
    28         //InputStream inp = new FileInputStream("C:/Users/H__D/Desktop/workbook.xls"); 
    29  
    30         Workbook workbook = WorkbookFactory.create(inputStream); 
    31         Sheet sheet = workbook.getSheetAt(0); 
    32  
    33         DataFormatter formatter = new DataFormatter(); 
    34         for (Row row : sheet) { 
    35             for (Cell cell : row) { 
    36                 CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); 
    37                 //单元格名称 
    38                 System.out.print(cellRef.formatAsString()); 
    39                 System.out.print(" - "); 
    40  
    41                 //通过获取单元格值并应用任何数据格式(Date,0.00,1.23e9,$ 1.23等),获取单元格中显示的文本 
    42                 String text = formatter.formatCellValue(cell); 
    43                 System.out.println(text); 
    44  
    45                  //获取值并自己格式化 
    46                 switch (cell.getCellType()) { 
    47                     case Cell.CELL_TYPE_STRING:// 字符串型 
    48                         System.out.println(cell.getRichStringCellValue().getString()); 
    49                         break; 
    50                     case Cell.CELL_TYPE_NUMERIC:// 数值型 
    51                         if (DateUtil.isCellDateFormatted(cell)) { // 如果是date类型则 ,获取该cell的date值 
    52                             System.out.println(cell.getDateCellValue()); 
    53                         } else {// 纯数字 
    54                             System.out.println(cell.getNumericCellValue()); 
    55                         } 
    56                         break; 
    57                     case Cell.CELL_TYPE_BOOLEAN:// 布尔 
    58                         System.out.println(cell.getBooleanCellValue()); 
    59                         break; 
    60                     case Cell.CELL_TYPE_FORMULA:// 公式型 
    61                         System.out.println(cell.getCellFormula()); 
    62                         break; 
    63                     case Cell.CELL_TYPE_BLANK:// 空值 
    64                         System.out.println(); 
    65                         break; 
    66                     case Cell.CELL_TYPE_ERROR: // 故障 
    67                         System.out.println(); 
    68                         break; 
    69                     default: 
    70                         System.out.println(); 
    71                 } 
    72             } 
    73         } 
    74          
    75     } 
    76      
    77 }
    复制代码

评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!