מדריך Apache POI

Java

ברוך הבא למדריך של Apache POI. לפעמים אנו זקוקים לקרוא נתונים מקבצי Microsoft Excel או ליצור דוחות בתצורת Excel, בעיקר לצרכי עסקיים או פיננסיים. Java אינה מספקת תמיכה מובנית לעבודה עם קבצי Excel, ולכן אנו צריכים לחפש API קוד פתוח למשימה זו. כאשר התחלתי לחפש עבור Java APIs לעבודה עם Excel, רוב האנשים הממליצים הזכירו את JExcel או Apache POI. לאחר מחקר נוסף, גיליתי ש-Apache POI הוא הדרך ללכת, וזאת מסיבות הבאות. יש עוד סיבות אחרות הקשורות לתכונות מתקדמות, אך אני לא אתרחף לפרטים רבים בזה.

תמיכה מצוינת מצד קרן אפאצ'י.
JExcel אינו תומך בפורמט xlsx, כאשר POI תומך גם בפורמטים xls ו-xlsx.
Apache POI מספק עיבוד מבוסס זרם, המתאים לקבצים גדולים ודורש פחות זכרון.

Apache POI

Apache POI מספק תמיכה מצוינת לעבודה עם מסמכי Microsoft Excel. Apache POI מסוגל להתמודד עם שני פורמטים של גליוני אלקטרוניקה, XLS ו-XLSX. נקודות חשובות על Apache POI API הן:

Apache POI כוללת יישום HSSF לפורמט קובץ Excel '97(-2007) כלומר XLS.
יש להשתמש ביישום Apache POI XSSF לפורמט קובץ Excel 2007 OOXML (.xlsx).
API של Apache POI HSSF ו-XSSF מספקים אמצעים לקריאה, כתיבה או שינוי של גליונות Excel.
אפאצ'ה POI מספק גם ממשק SXSSF API שהוא תוספת של XSSF לעבודה עם גליונות אקסל גדולים מאוד. ממשק SXSSF דורש פחות זיכרון ומתאים כאשר עובדים עם גליונות אקסל גדולים מאוד והזיכרון הגל נמוך.
ישנם שני מודלים לבחירה – מודל אירוע ומודל משתמש. מודל האירועים דורש פחות זיכרון מאחר והקובץ של אקסל נקרא באמצעות טוקנים ודורש איתור שלהם. מודל המשתמש הוא יותר מונח על אובייקטים ונוח לשימוש ונשתמש בו בדוגמאות שלנו.
אפאצ'ה POI מספק תמיכה מעולה לתכונות נוספות של אקסל כגון עבודה עם נוסחאות, יצירת סגנונות תא על ידי מילוי צבעים ומסגרות, גופנים, כותרות וכותרות תחתונות, אימות נתונים, תמונות, קישורים וכו'

תלויות Maven של אפאצ'ה POI

אם אתה משתמש במייבן, הוסף את התלויות אפאצ'ה POI הבאות.

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.10-FINAL</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.10-FINAL</version>
</dependency>

הגרסה הנוכחית של אפאצ'ה POI היא 3.10-FINAL. אם יש לך יישום ג'אווה סטנדלון, כלול צנצנות מהתמונה שלמטה.

דוגמה לאפאצ'ה POI – קריאת קובץ אקסל

נניח שיש לנו קובץ אקסל בשם "Sample.xlsx" עם שני גליונות ומידע כמו בתמונה למטה. אנו רוצים לקרוא את קובץ האקסל וליצור את רשימת המדינות. גליון 1 כולל מידע נוסף שנתעלם ממנו בעת ניתוחו. קוד ה-Bean של המדינה שלנו ב-Java הוא: `Country.java`.

package com.journaldev.excel.read;

public class Country {

	private String name;
	private String shortCode;
	
	public Country(String n, String c){
		this.name=n;
		this.shortCode=c;
	}
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getShortCode() {
		return shortCode;
	}
	public void setShortCode(String shortCode) {
		this.shortCode = shortCode;
	}
	
	@Override
	public String toString(){
		return name + "::" + shortCode;
	}
	
}

דוגמת קוד של Apache POI לקריאת קובץ אקסל ויצירת רשימת מדינות נראית כך: `ReadExcelFileToList.java`.

package com.journaldev.excel.read;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFileToList {

	public static List readExcelData(String fileName) {
		List countriesList = new ArrayList();
		
		try {
			 // יצירת נתוני קלט מקובץ xlsx / xls
			FileInputStream fis = new FileInputStream(fileName);
			
			 // יצירת מופע Workbook עבור קלט קובץ xlsx / xls
			Workbook workbook = null;
			if(fileName.toLowerCase().endsWith("xlsx")){
				workbook = new XSSFWorkbook(fis);
			}else if(fileName.toLowerCase().endsWith("xls")){
				workbook = new HSSFWorkbook(fis);
			}
			
			 // קבלת מספר הגליונות בקובץ xlsx
			int numberOfSheets = workbook.getNumberOfSheets();
			
			 // לעבור דרך כל הגליונות
			for(int i=0; i < numberOfSheets; i++){
				
				 // לקבל את הגליון ה-nth מתוך החוברת
				Sheet sheet = workbook.getSheetAt(i);
				
				 // לכל גליון יש שורות, לעבור עליהם
				Iterator rowIterator = sheet.iterator();
				while (rowIterator.hasNext()) 
		        {
					String name = "";
					String shortCode = "";
					
					 // לקבל את אובייקט השורה
					Row row = rowIterator.next();
					
					 // לכל שורה יש עמודות, לקבל את איטרטור העמודות ולעבור עליהם
					Iterator cellIterator = row.cellIterator();
		             
		            while (cellIterator.hasNext()) 
		            {
		            	 // לקבל את אובייקט התא
		            	Cell cell = cellIterator.next();
		            	
		            	 // לבדוק את סוג התא ולעבד בהתאם
		            	switch(cell.getCellType()){
		            	case Cell.CELL_TYPE_STRING:
		            		if(shortCode.equalsIgnoreCase("")){
		            			shortCode = cell.getStringCellValue().trim();
		            		}else if(name.equalsIgnoreCase("")){
		            			 // עמודה 2
		            			name = cell.getStringCellValue().trim();
		            		}else{
		            			 // נתונים אקראיים, נשאיר אותם
		            			System.out.println("Random data::"+cell.getStringCellValue());
		            		}
		            		break;
		            	case Cell.CELL_TYPE_NUMERIC:
		            		System.out.println("Random data::"+cell.getNumericCellValue());
		            	}
		            } //end of cell iterator
		            Country c = new Country(name, shortCode);
		            countriesList.add(c);
		        } //end of rows iterator
				
				
			} //end of sheets for loop
			
			 // סגירת מזרז קלט קובץ
			fis.close();
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return countriesList;
	}

	public static void main(String args[]){
		List list = readExcelData("Sample.xlsx");
		System.out.println("Country List\n"+list);
	}

}

התוכנית קלה מאוד להבנה וכוללת את השלבים הבאים:

צור מופע של Workbook על פי סוג הקובץ. XSSFWorkbook עבור פורמט xlsx ו־HSSFWorkbook עבור פורמט xls. שים לב שיכולנו ליצור מחלקת עטיפה עם דפוס פקטוריה כדי לקבל את מופע ה־workbook על פי שם הקובץ.
השתמש ב־workbook getNumberOfSheets() כדי לקבל את מספר הגליונות ואז השתמש בלולאת for כדי לנתח כל אחד מהם. קבל את מופע ה־Sheet באמצעות השיטה getSheetAt(int i).
קבל את מעגל ה־Row ואז את מעגל ה־Cell כדי לקבל את אובייקט התא. Apache POI משתמשת ב־דפוס המשובץ כאן.
השתמש ב־switch-case כדי לקרוא את סוג התא ולעבד אותו בהתאם.

כעת כאשר אנו מריצים את תכנית הדוגמה של Apache POI שלמעלה, היא מייצרת את הפלט הבא בקונסול.

Random data::1.0
Random data::2.0
Random data::3.0
Random data::4.0
Country List
[India::IND, Afghanistan::AFG, United States of America::USA, Anguilla::AIA, 
Denmark ::DNK, Dominican Republic ::DOM, Algeria ::DZA, Ecuador ::ECU]

דוגמה ל־Apache POI – כתיבת קובץ אקסל

כתיבת קובץ Excel ב־apache POI דומה לקריאה, אלא שכאן אנו יוצרים תחילה חוברת עבודה. לאחר מכן, אנו מגדירים ערכי גליונות, שורות ותאים ומשתמשים ב־FileOutputStream כדי לכתוב אותו לקובץ. בואו נכתוב דוגמה פשוטה של apache POI בה נשתמש ברשימת מדינות מהשיטה למעלה לשמירה בקובץ אחר בגליון יחיד. WriteListToExcelFile.java

package com.journaldev.excel.read;

import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;

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.xssf.usermodel.XSSFWorkbook;

public class WriteListToExcelFile {

	public static void writeCountryListToFile(String fileName, List countryList) throws Exception{
		Workbook workbook = null;
		
		if(fileName.endsWith("xlsx")){
			workbook = new XSSFWorkbook();
		}else if(fileName.endsWith("xls")){
			workbook = new HSSFWorkbook();
		}else{
			throw new Exception("invalid file name, should be xls or xlsx");
		}
		
		Sheet sheet = workbook.createSheet("Countries");
		
		Iterator iterator = countryList.iterator();
		
		int rowIndex = 0;
		while(iterator.hasNext()){
			Country country = iterator.next();
			Row row = sheet.createRow(rowIndex++);
			Cell cell0 = row.createCell(0);
			cell0.setCellValue(country.getName());
			Cell cell1 = row.createCell(1);
			cell1.setCellValue(country.getShortCode());
		}
		
		// בואו נכתוב כעת את הנתונים של Excel לקובץ
		FileOutputStream fos = new FileOutputStream(fileName);
		workbook.write(fos);
		fos.close();
		System.out.println(fileName + " written successfully");
	}
	
	public static void main(String args[]) throws Exception{
		List list = ReadExcelFileToList.readExcelData("Sample.xlsx");
		WriteListToExcelFile.writeCountryListToFile("Countries.xls", list);
	}
}

כאשר אני מפעיל את תכנית הדוגמה של apache POI שלמעלה, הקובץ Excel שנוצר נראה כמו בדימוי התמונה למטה.

דוגמה ל־Apache POI – קריאת נוסח Excel

לפעמים יש צורך להתמודד עם קבצי Excel מורכבים עם נוסחאות, בואו נראה דוגמה פשוטה של apache POI לקריאת נוסחה של תא עם הערך שלו. ReadExcelFormula.java

package com.journaldev.excel.read;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

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.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFormula {

	public static void readExcelFormula(String fileName) throws IOException{
		
		FileInputStream fis = new FileInputStream(fileName);
		
		// בהנחה שזו קובץ xlsx
		Workbook workbook = new XSSFWorkbook(fis);
		Sheet sheet = workbook.getSheetAt(0);
		Iterator rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) 
        {
			Row row = rowIterator.next();
			Iterator cellIterator = row.cellIterator();
            
            while (cellIterator.hasNext()) 
            {
            	Cell cell = cellIterator.next();
            	switch(cell.getCellType()){
            	case Cell.CELL_TYPE_NUMERIC:
            		System.out.println(cell.getNumericCellValue());
            		break;
            	case Cell.CELL_TYPE_FORMULA:
            		System.out.println("Cell Formula="+cell.getCellFormula());
            		System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
            		if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
            			System.out.println("Formula Value="+cell.getNumericCellValue());
            		}
            	}
            }
        }
	}
	
	public static void main(String args[]) throws IOException {
		readExcelFormula("FormulaMultiply.xlsx");
	}
}

כאשר אנו מפעילים את תכנית הדוגמה של apache poi שלמעלה, אנו מקבלים את הפלט הבא.

1.0
2.0
3.0
4.0
Cell Formula=A1*A2*A3*A4
Cell Formula Result Type=0
Formula Value=24.0

דוגמה של Apache POI – כתיבת נוסחאות ב-Excel

לעיתים קרובות אנו צריכים לבצע חישובים ולכתוב את ערכי התא. ניתן להשתמש בנוסחאות של Excel כדי לבצע את החישוב הזה וזה יגרום לו להיות יותר מדויק מכיוון שערכים ישתנו אם ערכי התא המשמשים בחישובים משתנים. בואו נראה דוגמה פשוטה לכתיבת קובץ Excel עם נוסחאות באמצעות API של Apache POI. WriteExcelWithFormula.java

package com.journaldev.excel.read;

import java.io.FileOutputStream;
import java.io.IOException;

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 WriteExcelWithFormula {

	public static void writeExcelWithFormula(String fileName) throws IOException{
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Numbers");
		Row row = sheet.createRow(0);
		row.createCell(0).setCellValue(10);
		row.createCell(1).setCellValue(20);
		row.createCell(2).setCellValue(30);
		//קביעת תא נוסחה
		row.createCell(3).setCellFormula("A1*B1*C1");
		
		//בואו נכתוב לקובץ
		FileOutputStream fos = new FileOutputStream(fileName);
		workbook.write(fos);
		fos.close();
		System.out.println(fileName + " written successfully");
	}
	
	public static void main(String[] args) throws IOException {
		writeExcelWithFormula("Formulas.xlsx");
	}
}

הקובץ Excel שיוצר עם דוגמה זו של תכנית API של Apache POI נראה כמו בתמונה למטה. זהו הכל בנושא הדרכת Apache POI לעבוד עם קבצי Excel, עיינו במתודות של מחלקות Apache POI כדי ללמוד עוד תכונות שלה. הפניות: מדריך מפתחי Apache POI

Source:
https://www.digitalocean.com/community/tutorials/apache-poi-tutorial