Java操作Excel之POI简单例子

Z时代
2024-01-10
分类：综合

java

 21 /**
 22  * 利用POI操作Excel表单
 23  * 
 24  * 需要jar包：
 25  *     HSSF针对03及以前版本，即.xls后缀
 26  *         |---poi-3.16.jar
 27  *     XSSF针对07及以后版本，即xlsx后缀
 28  *         |---poi-3.16.jar
 29  *         |---poi-ooxml.3.16.jar
 30  *         |---poi-ooxml-schemas-3.16.jar
 31  *         |---xmlbeans-2.6.0.jar
 32  *         |---commons-collections4-4.1.jar
 33  * 
 34  * 工作簿：Workbook
 35  * 工作表：Sheet
 36  * 行：    Row
 37  * 表格：Cell
 38  */
 39 public class Demo {
 40 
 41     /**
 42      * 读取Excel表格
 43      * @throws IOEception 
 44      * @throws InvalidFormatException 
 45      * @throws EncryptedDocumentException 
 46      */
 47     @Test
 48     public void readExcel() throws EncryptedDocumentException, InvalidFormatException, IOException {
 49         //工作簿
 50         Workbook workbook = WorkbookFactory.create(new File("src/userExce1.xls"));
 51         //工作表
 52         Sheet sheet = workbook.getSheetAt(0);
 53         //行
 54         Row row = sheet.getRow(1);
 55         //列
 56         Cell cell = row.getCell(0);
 57 
 58         System.out.println("表格值为：" + cell.getStringCellValue());
 59     }
 60 
 61     /**
 62      * 创建Excel表格
 63      * @throws IOException 
 64      * @throws InvalidFormatException 
 65      * @throws EncryptedDocumentException 
 66      */
 67     @Test
 68     public void writeExcel() throws EncryptedDocumentException, InvalidFormatException, IOException {
 69         //工作簿
 70         Workbook workbook = new XSSFWorkbook();
 71         //工作表
 72         Sheet sheet = workbook.createSheet("我的第一个sheet");
 73         //行
 74         Row row = sheet.createRow(0);
 75         //列
 76         Cell cell = row.createCell(3);
 77         
 78         cell.setCellValue("哈哈表格插入一个内容");
 79         
 80         workbook.write(new FileOutputStream("c:/test.xlsx"));
 81     }
 82 
 83     /**
 84      * 读取Excel表格,修改样式和内容并保存
 85      * @throws IOEception 
 86      * @throws InvalidFormatException 
 87      * @throws EncryptedDocumentException 
 88      */
 89     @Test
 90     public void readAndModifyExcel() throws EncryptedDocumentException, InvalidFormatException, IOException {
 91 
 92         String filename = "src/userExcel.xlsx";
 93         File file = new File(filename);
 94         
 95         System.out.println(file.getAbsolutePath());
 96         
 97         Workbook workbook = WorkbookFactory.create(file);
 98         Sheet sheet = workbook.getSheetAt(0);
 99         
100         //合并单元格，在工作表添加合并单元格
101         CellRangeAddress headSpan = new CellRangeAddress(2, 2, 0, 4);
102         sheet.addMergedRegion(headSpan);
103         
104         //在合并的位置设置表头文字
105         Row row = sheet.createRow(0);
106         Cell cell = row.createCell(0);
107         cell.setCellValue("这是表头");
108 
109         //写入文本中
110         String savedName = filename.matches("\\S+\\.xls") ? "c:/" + filename.replace("src/", "") : "c:/" + filename.replace("src/", "");
111         workbook.write(new FileOutputStream(savedName));
112     }
113 
114 }