POI Excel 学习

￡神魔★判官ぃ 2023-09-27 19:59 71阅读 0赞

引入依赖

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>

工作簿(Workbook)表示使用HSSF Workbook类创建的Excel文件

1、创建工作簿

package poiexample;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Workbook;
    public class CreateWorkBook {
    	public static void main(String[] args) throws FileNotFoundException, IOException {
    		Workbook wb = new HSSFWorkbook();
    	    try(OutputStream fileOut = new FileOutputStream("Learnfk.xls")) {
    	        wb.write(fileOut);
    	    }catch(Exception e) {
    	    	System.out.println(e.getMessage());
    	    }
    	}
    }

2、创建工作表

3、创建单元格（实际上是定位到单元格）

4、日期单元格

package poiexample;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.Date;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.CreationHelper;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    public class DateCellExample {
    	
    	public static void main(String[] args) throws FileNotFoundException, IOException {
    		Workbook wb = new HSSFWorkbook();
    		CreationHelper createHelper = wb.getCreationHelper();
    	    try(OutputStream os = new FileOutputStream("Learnfk.xls")){
    	        Sheet sheet = wb.createSheet("New Sheet");
    	        Row row     = sheet.createRow(0);
    	        Cell cell   = row.createCell(0);
    	        CellStyle cellStyle = wb.createCellStyle();
    	        cellStyle.setDataFormat(
    	            createHelper.createDataFormat().getFormat("d/m/yy h:mm"));
    	        cell = row.createCell(1);
    	        cell.setCellValue(new Date());
    	        cell.setCellStyle(cellStyle);
    	        wb.write(os);
    	    }catch(Exception e) {
    	    	System.out.println(e.getMessage());
    	    }
    	}
    }

5、单元格对齐、高度和宽度

复制代码
    package poiexample;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.Date;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.CreationHelper;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    
    public class AlignExample {
    	public static void main(String[] args) throws FileNotFoundException, IOException {
    		Workbook wb = new HSSFWorkbook();
    	    try  (OutputStream os = new FileOutputStream("Learnfk.xls")) {
    	        Sheet sheet = wb.createSheet("New Sheet");
    	        Row row = sheet.createRow(0);
    	        Cell cell = row.createCell(0);
    	        cell.setCellValue("Javatpoint");
    	        CellStyle cellStyle = wb.createCellStyle();
    	        row = sheet.createRow(5); 
    	        cell = row.createCell(0);
    	       //左上对齐
    	        HSSFCellStyle style1 = (HSSFCellStyle) wb.createCellStyle();
    	        sheet.setColumnWidth(0, 8000);
    	        cell.setCellValue("Top Left");
    	        cell.setCellStyle(style1);
    	        row = sheet.createRow(6); 
    	        cell = row.createCell(1);
    	        row.setHeight((short) 800);
    	        wb.write(os);
    	    }catch(Exception e) {
    	    	System.out.println(e.getMessage());
    	    }
    	}
    
    }

6、单元格边框样式

复制代码
    package poiexample;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    public class BorderExample {
    	public static void main(String[] args) throws FileNotFoundException, IOException {
    		    Workbook wb = new HSSFWorkbook();
    		    Sheet sheet = wb.createSheet("Sheet");
    		    Row row     = sheet.createRow(1);
    		    Cell cell   = row.createCell(1);
    		    cell.setCellValue(101);
    		   //单元格的样式边框。
    		    CellStyle style = wb.createCellStyle();
    		    style.setBorderBottom(BorderStyle.THIN);
    		    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    		    style.setBorderRight(BorderStyle.THIN);
    		    style.setRightBorderColor(IndexedColors.BLUE.getIndex());
    		    style.setBorderTop(BorderStyle.MEDIUM_DASHED);
    		    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    		    cell.setCellStyle(style);
    		    try (OutputStream fileOut = new FileOutputStream("Learnfk.xls")) {
    		        wb.write(fileOut);
    		    }catch(Exception e) {
    		    	System.out.println(e.getMessage());
    		    }
    	}
    }

7、单元格颜色

package poiexample;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.FillPatternType;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    public class ColorExample {
    	public static void main(String[] args) throws FileNotFoundException, IOException {
    		try (OutputStream fileOut = new FileOutputStream("Learnfk.xls")) {
    		    Workbook wb = new XSSFWorkbook();
    		    Sheet sheet = wb.createSheet("Sheet");
    		    Row row = sheet.createRow(1);
    		    CellStyle style = wb.createCellStyle();
    		   //设置背景颜色
    		    style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
    		    style.setFillPattern(FillPatternType.BIG_SPOTS);
    		    Cell cell = row.createCell(1);
    		    cell.setCellValue("Javatpoint");
    		    cell.setCellStyle(style);
    		   //设置前景色
    		    style = wb.createCellStyle();
    		    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    		    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    		    cell = row.createCell(2);
    		    cell.setCellValue("A Technical Portal");
    		    cell.setCellStyle(style);
                        wb.write(fileOut);
                     }catch(Exception e) {
    		    	System.out.println(e.getMessage());
    		}
    	}
    }

8、合并单元格

package poiexample;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    public class MergingCellExample {
    	public static void main(String[] args) {
    	    try (OutputStream fileOut = new FileOutputStream("Learnfk.xls")) {
    		    Workbook wb = new HSSFWorkbook();
    		    Sheet sheet = wb.createSheet("Sheet");	
    		    Row row = sheet.createRow(1);
    		    Cell cell = row.createCell(1);
    		    cell.setCellValue("Two cells have merged");
    	          //通过提供单元格索引来合并单元格
    		    sheet.addMergedRegion(new CellRangeAddress(1,1,1,2));
    		    wb.write(fileOut);
    	    }catch(Exception e) {
    	    	System.out.println(e.getMessage());
    	    }
    	}
    }

9、单元格属性

[POI Excel 属性单元格 - Apache Excel 入门教程 - 无涯教程网][POI Excel _ - Apache Excel _ -]

10、单元格类型（数据类型）

复制代码
    package poiexample;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import java.util.Calendar;
    import java.util.Date;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    public class CellTypeExample {
    	public static void main(String[] args) {
    		try (OutputStream os = new FileOutputStream("Learnfk.xls")) {
    		Workbook wb = new HSSFWorkbook();
    	    Sheet sheet = wb.createSheet("Sheet");
    	    Row row = sheet.createRow(2);                          
    	    row.createCell(0).setCellValue(1.1);//浮点值
    	    row.createCell(1).setCellValue(" "+new Date());//日期类型
    	    row.createCell(2).setCellValue(" "+Calendar.getInstance());// Calendar
    	    row.createCell(3).setCellValue("a string value");//字符串
    	    row.createCell(4).setCellValue(true);//布尔类型
    	    row.createCell(5).setCellType(CellType.ERROR);//错误
    	    wb.write(os);
    	    }catch(Exception e) {
    	    	System.out.println(e.getMessage());
    	    }
    	}
    }

11、单元格换行（写入多行数据）

package poiexample;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    public class NewlineExample {
    	public static void main(String[] args) {
    		try (OutputStream fileOut = new FileOutputStream("Learnfk.xls")) {
    			Workbook wb = new HSSFWorkbook();
    			Sheet sheet = wb.createSheet("Sheet");
    			Row row     = sheet.createRow(1);
    			Cell cell   = row.createCell(1);
    			cell.setCellValue("This is first line and \n this is second line");
    			CellStyle cs = wb.createCellStyle();
    			cs.setWrapText(true);
    			cell.setCellStyle(cs);
    			row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
    			sheet.autoSizeColumn(2);
    	        wb.write(fileOut);
    	    }catch(Exception e) {
    	    	System.out.println(e.getMessage());
    	    }
    	}
    }

12、读取单元格

package poiexample;
    import java.io.FileInputStream;
    import java.io.InputStream;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    public class GettingCellContentExample {
    	public static void main(String[] args) {
    		try (InputStream inp = new FileInputStream("Learnfk.xls")) {
    		        Workbook wb = WorkbookFactory.create(inp);
    		        Sheet sheet = wb.getSheetAt(0);
    		        Row row = sheet.getRow(2);
    		        Cell cell = row.getCell(3);
    		        if (cell != null)
    		        	System.out.println("Data: "+cell);
    		        else
    		            System.out.println("Cell is empty");
    	    }catch(Exception e) {
    	    	System.out.println(e);
    	    }
    	}
    }

13、字体

复制代码
    package poiexample;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    public class FontExample {
    	public static void main(String[] args) {
    		try (OutputStream fileOut = new FileOutputStream("Learnfk.xls")) {
    			Workbook wb = new HSSFWorkbook();//创建工作簿
    			Sheet sheet = wb.createSheet("Sheet");//创建工作表
    			Row row = sheet.createRow(1);//创建行
    			Cell cell = row.createCell(1);//创建一个单元格
    			CellStyle style = wb.createCellStyle();//创建样式
    			cell.setCellValue("Hello, Javatpoint!");
    			// 创建字体和设置
    			Font font = wb.createFont();
    			font.setFontHeightInPoints((short)11);
    			font.setFontName("Courier New");
    			font.setItalic(true);
    			font.setStrikeout(true);
    			// 将字体应用于样式
    			style.setFont(font);
    			// 将样式应用于单元格
    			cell.setCellStyle(style);    
    	                wb.write(fileOut);
    	       }catch(Exception e) {
    	        	System.out.println(e.getMessage());
    	       }
    	}
    }

14、设置标题

复制代码
    package poiexample;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Header;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    public class HeaderExample {
    	public static void main(String[] args) {
    		try (OutputStream fileOut = new FileOutputStream("Learnfk.xls")) {
    			Workbook wb = new HSSFWorkbook();
    			Sheet sheet = wb.createSheet("Sheet");
    			//创建标题
    			Header header = sheet.getHeader();
    			header.setCenter("Center Header");
    			header.setLeft  ("Left Header");
    			header.setRight ("Right align Header");
    			// 创建行
    			Row row = sheet.createRow(4);//Creating a row
    			Cell cell = row.createCell(1);//Creating a cell
    			cell.setCellValue("Hello, Javaipoint!");
    	                wb.write(fileOut);
    	      }catch(Exception e) {
    	    	System.out.println(e.getMessage());
    	      }
    	}
    }

15、隐藏行

import java.io.FileOutputStream;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    public class HideExample {
    	public static void main(String[] args) {
    		try (OutputStream os = new FileOutputStream("Learnfk.xls")) {
    			Workbook workbook = new HSSFWorkbook();
    		    Sheet sheet = workbook.createSheet();
    		    Row row = sheet.createRow(0);
    		    Cell cell = row.createCell(0);
    		    cell.setCellValue("102");
    		    row.setZeroHeight(false);
    		    workbook.write(os);
    		}catch(Exception e) {
    			System.out.println(e.getMessage());
    		}
    	}
    }

16、绘图边框

import java.io.FileOutputStream;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.BorderExtent;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.ss.util.PropertyTemplate;
    public class DrawingBorderExample {
    	public static void main(String[] args) {		
    		try (OutputStream os = new FileOutputStream("Learnfk.xls")) {	
    			 PropertyTemplate pt = new PropertyTemplate();
    			 pt.drawBorders(new CellRangeAddress(1, 2, 1, 2),
    			          BorderStyle.MEDIUM, BorderExtent.ALL);
    			 pt.drawBorders(new CellRangeAddress(5, 6, 1, 2),
    			          BorderStyle.MEDIUM, BorderExtent.OUTSIDE);
    			 pt.drawBorders(new CellRangeAddress(5, 6, 1, 2), BorderStyle.THIN,
    			          BorderExtent.INSIDE);
    			 pt.drawBorders(new CellRangeAddress(9, 10, 1, 3),
    			          BorderStyle.MEDIUM, IndexedColors.GREEN.getIndex(),
    			          BorderExtent.OUTSIDE);
    			 pt.drawBorders(new CellRangeAddress(9, 10, 1, 3),
    			          BorderStyle.MEDIUM, IndexedColors.BLUE.getIndex(),
    			          BorderExtent.INSIDE_VERTICAL);
    			 pt.drawBorders(new CellRangeAddress(9, 10, 1, 3),
    			          BorderStyle.MEDIUM, IndexedColors.RED.getIndex(),
    			          BorderExtent.INSIDE_HORIZONTAL);
    			 pt.drawBorders(new CellRangeAddress(10, 10, 2, 2),
    			          BorderStyle.NONE, 
    			          BorderExtent.ALL);
    			 Workbook wb = new HSSFWorkbook();
    			 Sheet sheet = wb.createSheet("Sheet");
    			 pt.applyBorders(sheet);
    			 wb.write(os);
    		}catch(Exception e) {
    			System.out.println(e.getMessage());
    		}
    	}
    }

17、插入图片

import java.io.FileOutputStream;
    import java.io.OutputStream;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    public class OutlineExample {
    	public static void main(String[] args) {
    		 try (OutputStream os = new FileOutputStream("Learnfk.xls")) {
    			 Workbook wb = new HSSFWorkbook();
    			 Sheet sheet = wb.createSheet("Sheet");
    			 sheet.groupRow( 4, 10 );
    			 sheet.groupColumn( 2, 8 );
    		     wb.write(os);
    		 }catch(Exception e) {
    			 System.out.println(e.getMessage());
    		 }
    	}
    }

[POI Excel _ - Apache Excel _ -]: https://www.learnfk.com/apache-poi-word/apache-poi-excel-cell-properties.html