本章介绍如何使用Java创建电子表格并对其进行操作.电子表格是Excel文件中的页面;它包含具有特定名称的行和列.
完成本章后，您将能够创建电子表格并对其执行读取操作.
首先，让我们使用前面章节中讨论的引用类创建电子表格.按照上一章的说法，首先创建一个工作簿然后我们可以继续创建工作表.
以下代码片段用于创建电子表格.
//创建空白工作簿
XSSFWorkbook工作簿=新的XSSFWorkbook();
//创建一个空白电子表格
XSSFSheet spreadsheet = workbook.createSheet("Sheet Name");电子表格具有网格布局.行和列用特定名称标识.列用字母和带数字的行标识.
以下代码片段用于创建行.
XSSFRow row = spreadsheet.createRow((short)1);
让我们考虑一下员工数据的示例.这里的员工数据以表格形式给出.
| Emp Id | Emp Name | 指定 |
|---|---|---|
| Tp01 | Gopal | 技术经理 |
| TP02 | Manisha | 证明读者 |
| Tp03 | Masthan | 技术作家 |
| Tp04 | Satish | 技术作家 |
| Tp05 | Krishna | 技术作家 |
以下代码用于将上述数据写入电子表格.
import java.io.File;
import java.io.FileOutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Writesheet {
public static void main(String[] args) throws Exception {
//Create blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
//Create a blank sheet
XSSFSheet spreadsheet = workbook.createSheet(" Employee Info ");
//Create row object
XSSFRow row;
//This data needs to be written (Object[])
Map < String, Object[] > empinfo =
new TreeMap < String, Object[] >();
empinfo.put( "1", new Object[] { "EMP ID", "EMP NAME", "DESIGNATION" });
empinfo.put( "2", new Object[] { "tp01", "Gopal", "Technical Manager" });
empinfo.put( "3", new Object[] { "tp02", "Manisha", "Proof Reader" });
empinfo.put( "4", new Object[] { "tp03", "Masthan", "Technical Writer" });
empinfo.put( "5", new Object[] { "tp04", "Satish", "Technical Writer" });
empinfo.put( "6", new Object[] { "tp05", "Krishna", "Technical Writer" });
//Iterate over data and write to sheet
Set < String > keyid = empinfo.keySet();
int rowid = 0;
for (String key : keyid) {
row = spreadsheet.createRow(rowid++);
Object [] objectArr = empinfo.get(key);
int cellid = 0;
for (Object obj : objectArr) {
Cell cell = row.createCell(cellid++);
cell.setCellValue((String)obj);
}
}
//Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File("Writesheet.xlsx"));
workbook.write(out);
out.close();
System.out.println("Writesheet.xlsx written successfully");
}
}将上述Java代码保存为 Writesheet.java ，然后编译并运行它来自命令提示符，如下所示 :
$javac Writesheet.java $java Writesheet
它将编译并执行以在当前目录中生成名为 Writesheet.xlsx 的Excel文件，您将在命令提示符下获得以下输出.
Writesheet.xlsx written successfully
Writesheet.xlsx 文件如下所示.
让我们考虑上面名为 Writesheet.xslx 的excel文件作为输入.请注意以下代码;它用于从电子表格中读取数据.
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Readsheet {
static XSSFRow row;
public static void main(String[] args) throws Exception {
FileInputStream fis = new FileInputStream(new File("WriteSheet.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet spreadsheet = workbook.getSheetAt(0);
Iterator < Row > rowIterator = spreadsheet.iterator();
while (rowIterator.hasNext()) {
row = (XSSFRow) rowIterator.next();
Iterator < Cell > cellIterator = row.cellIterator();
while ( cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + " \t\t ");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(
cell.getStringCellValue() + " \t\t ");
break;
}
}
System.out.println();
}
fis.close();
}
}让我们将上面的代码保存在 Readsheet.java 文件中，然后编译并从命令提示符运行它，如下所示 :
$javac Readsheet.java $java Readsheet
如果您的系统环境配置了POI库，它将编译并执行以在命令提示符中生成以下输出.
EMP ID EMP NAME DESIGNATION tp01 Gopal Technical Manager tp02 Manisha Proof Reader tp03 Masthan Technical Writer tp04 Satish Technical Writer tp05 Krishna Technical Writer
