Java 如何读写excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1516144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read and write excel file
提问by user177785
I want to read and write an Excel file from Java with 3 columns and N rows, printing one string in each cell. Can anyone give me simple code snippet for this? Do I need to use any external lib or does Java have built-in support for it?
我想从 Java 读取和写入一个 3 列 N 行的 Excel 文件，在每个单元格中打印一个字符串。谁能给我简单的代码片段？我需要使用任何外部库还是 Java 有内置支持？
I want to do the following:
我想做以下事情：
for(i=0; i <rows; i++)
//read [i,col1] ,[i,col2], [i,col3]
for(i=0; i<rows; i++)
//write [i,col1], [i,col2], [i,col3]
回答by Brian Agnew
Apache POIcan do this for you. Specifically the HSSFmodule. The quick guideis most useful. Here's how to do what you want - specifically create a sheet and write it out.
Apache POI可以为您做到这一点。特别是HSSF模块。该快速指南是最有用的。以下是如何做你想做的 - 特别是创建一个工作表并将其写出来。
Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);
// Or do it on one line.
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(
createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
回答by rogeriopvl
Try the Apache POI HSSF. Here's an example on how to read an excel file:
试试Apache POI HSSF。下面是一个关于如何读取 Excel 文件的示例：
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();
int cols = 0; // No of columns
int tmp = 0;
// This trick ensures that we get the data properly even if it doesn't start from first few rows
for(int i = 0; i < 10 || i < rows; i++) {
row = sheet.getRow(i);
if(row != null) {
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
if(tmp > cols) cols = tmp;
}
}
for(int r = 0; r < rows; r++) {
row = sheet.getRow(r);
if(row != null) {
for(int c = 0; c < cols; c++) {
cell = row.getCell((short)c);
if(cell != null) {
// Your code here
}
}
}
}
} catch(Exception ioe) {
ioe.printStackTrace();
}
On the documentation page you also have examples of how to write to excel files.
在文档页面上，您还有如何写入 Excel 文件的示例。
回答by John La Rooy
A simple CSV file should suffice
一个简单的 CSV 文件就足够了
回答by Adriaan Koster
If you need to do anything more with office documents in Java, go for POI as mentioned.
如果您需要对 Java 中的 Office 文档执行更多操作，请选择前面提到的 POI。
For simple reading/writing an excel document like you requested, you can use the CSV format (also as mentioned):
为了像您要求的那样简单地读/写 excel 文档，您可以使用 CSV 格式（也如上所述）：
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Scanner;
public class CsvWriter {
public static void main(String args[]) throws IOException {
String fileName = "test.xls";
PrintWriter out = new PrintWriter(new FileWriter(fileName));
out.println("a,b,c,d");
out.println("e,f,g,h");
out.println("i,j,k,l");
out.close();
BufferedReader in = new BufferedReader(new FileReader(fileName));
String line = null;
while ((line = in.readLine()) != null) {
Scanner scanner = new Scanner(line);
String sep = "";
while (scanner.hasNext()) {
System.out.println(sep + scanner.next());
sep = ",";
}
}
in.close();
}
}
回答by duffymo
回答by javashlook
回答by Satish Reddy
For reading data from .xlsx workbooks we need to use XSSFworkbook classes.
要从 .xlsx 工作簿读取数据，我们需要使用 XSSFworkbook 类。
XSSFWorkbook xlsxBook = new XSSFWorkbook(fis);
XSSFWorkbook xlsxBook = new XSSFWorkbook(fis);
XSSFSheet sheet = xlsxBook.getSheetAt(0);etc.
XSSFSheet sheet = xlsxBook.getSheetAt(0);等等。
We need to use Apache-poi 3.9 @ http://poi.apache.org/
我们需要使用 Apache-poi 3.9 @ http://poi.apache.org/
For detailed info with example visit : http://java-recent.blogspot.in
有关示例的详细信息，请访问：http: //java-recent.blogspot.in
回答by Prashant Tiwari
First add all these jar files in your project class path:
首先在您的项目类路径中添加所有这些 jar 文件：
- poi-scratchpad-3.7-20101029
- poi-3.2-FINAL-20081019
- poi-3.7-20101029
- poi-examples-3.7-20101029
- poi-ooxml-3.7-20101029
- poi-ooxml-schemas-3.7-20101029
- xmlbeans-2.3.0
- dom4j-1.6.1
- poi-scratchpad-3.7-20101029
- poi-3.2-FINAL-20081019
- poi-3.7-20101029
- poi-examples-3.7-20101029
- poi-ooxml-3.7-20101029
- poi-ooxml-schemas-3.7-20101029
- xmlbeans-2.3.0
- dom4j-1.6.1
Code for writing in a excel file:
写入excel文件的代码：
public static void main(String[] args) {
//Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
//Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Employee Data");
//This data needs to be written (Object[])
Map<String, Object[]> data = new TreeMap<String, Object[]>();
data.put("1", new Object[]{"ID", "NAME", "LASTNAME"});
data.put("2", new Object[]{1, "Amit", "Shukla"});
data.put("3", new Object[]{2, "Lokesh", "Gupta"});
data.put("4", new Object[]{3, "John", "Adwards"});
data.put("5", new Object[]{4, "Brian", "Schultz"});
//Iterate over data and write to sheet
Set<String> keyset = data.keySet();
int rownum = 0;
for (String key : keyset)
{
//create a row of excelsheet
Row row = sheet.createRow(rownum++);
//get object array of prerticuler key
Object[] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr)
{
Cell cell = row.createCell(cellnum++);
if (obj instanceof String)
{
cell.setCellValue((String) obj);
}
else if (obj instanceof Integer)
{
cell.setCellValue((Integer) obj);
}
}
}
try
{
//Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File("C:\Documents and Settings\admin\Desktop\imp data\howtodoinjava_demo.xlsx"));
workbook.write(out);
out.close();
System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
}
catch (Exception e)
{
e.printStackTrace();
}
}
Code for reading from excel file
读取excel文件的代码
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
public static void main(String[] args) {
try {
FileInputStream file = new FileInputStream(new File("C:\Documents and Settings\admin\Desktop\imp data\howtodoinjava_demo.xlsx"));
//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
}
System.out.println("");
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
回答by Doug Hauf
This will write a JTable to a tab separated file that can be easily imported into Excel. This works.
这会将 JTable 写入一个制表符分隔的文件，该文件可以轻松导入到 Excel 中。这有效。
If you save an Excel worksheet as an XML document you could also build the XML file for EXCEL with code. I have done this with word so you do not have to use third-party packages.
如果您将 Excel 工作表保存为 XML 文档，您还可以使用代码为 EXCEL 构建 XML 文件。我已经用 word 完成了这项工作，因此您不必使用第三方软件包。
This could code have the JTable taken out and then just write a tab separated to any text file and then import into Excel. I hope this helps.
这可以将代码取出 JTable，然后将一个选项卡写入任何文本文件，然后导入 Excel。我希望这有帮助。
Code:
代码：
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import javax.swing.JTable;
import javax.swing.table.TableModel;
public class excel {
String columnNames[] = { "Column 1", "Column 2", "Column 3" };
// Create some data
String dataValues[][] =
{
{ "12", "234", "67" },
{ "-123", "43", "853" },
{ "93", "89.2", "109" },
{ "279", "9033", "3092" }
};
JTable table;
excel() {
table = new JTable( dataValues, columnNames );
}
public void toExcel(JTable table, File file){
try{
TableModel model = table.getModel();
FileWriter excel = new FileWriter(file);
for(int i = 0; i < model.getColumnCount(); i++){
excel.write(model.getColumnName(i) + "\t");
}
excel.write("\n");
for(int i=0; i< model.getRowCount(); i++) {
for(int j=0; j < model.getColumnCount(); j++) {
excel.write(model.getValueAt(i,j).toString()+"\t");
}
excel.write("\n");
}
excel.close();
}catch(IOException e){ System.out.println(e); }
}
public static void main(String[] o) {
excel cv = new excel();
cv.toExcel(cv.table,new File("C:\Users\itpr13266\Desktop\cs.tbv"));
}
}
回答by Toumi
String path="C:\Book2.xlsx";
try {
File f = new File( path );
Workbook wb = WorkbookFactory.create(f);
Sheet mySheet = wb.getSheetAt(0);
Iterator<Row> rowIter = mySheet.rowIterator();
for ( Iterator<Row> rowIterator = mySheet.rowIterator() ;rowIterator.hasNext(); )
{
for ( Iterator<Cell> cellIterator = ((Row)rowIterator.next()).cellIterator() ; cellIterator.hasNext() ; )
{
System.out.println ( ( (Cell)cellIterator.next() ).toString() );
}
System.out.println( " **************************************************************** ");
}
} catch ( Exception e )
{
System.out.println( "exception" );
e.printStackTrace();
}
and make sure to have added the jars poi and poi-ooxml (org.apache.poi) to your project
并确保已将 jars poi 和 poi-ooxml (org.apache.poi) 添加到您的项目中
