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();
}
在文档页面上,您还可以获得有关如何写入Excel文件的示例 .
1
请使用Apache POI库并尝试此操作 .
public class TakingDataFromExcel {
public static void main(String[] args) {
try
{
FileInputStream x = new FileInputStream(new File("/Users/rajesh/Documents/rajesh.xls"));
//Create Workbook instance holding reference to .xlsx file
Workbook workbook = new HSSFWorkbook(x);
//Get first/desired sheet from the workbook
Sheet sheet = workbook.getSheetAt(0);
//Iterate through each rows one by one
for (Iterator<Row> iterator = sheet.iterator(); iterator.hasNext();) {
Row row = (Row) iterator.next();
for (Iterator<Cell> iterator2 = row.iterator(); iterator2
.hasNext();) {
Cell cell = (Cell) iterator2.next();
System.out.println(cell.getStringCellValue());
}
}
x.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
9
如果您需要使用Java中的office文档执行更多操作,请按照提及的方式访问POI .
为了简单地读取/写入您请求的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();
}
}
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();
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(Dir));
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;
int cblacks=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;else{rows++;cblacks++;}
}
cols++;
}
cols=cols+cblacks;
for(int r = 0; r < rows; r++) {
row = sheet.getRow(r);
if(row != null) {
for(int c = 0; c < cols; c++) {
cell = row.getCell(c);
if(cell != null) {
System.out.print(cell+"\n");//Your Code here
}
}
}
}} catch(Exception ioe) {
ioe.printStackTrace();}
0
首先在项目类路径中添加所有这些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
Code for writing in a excel file:
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
/*
* 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();
}
}
19 回答
并确保已将jar poi和poi-ooxml(org.apache.poi)添加到您的项目中
要从.xlsx工作簿中读取数据,我们需要使用XSSFworkbook类 .
XSSFWorkbook xlsxBook = new XSSFWorkbook(fis);
XSSFSheet sheet = xlsxBook.getSheetAt(0);
等我们需要使用Apache-poi 3.9 @ http://poi.apache.org/
有关详细信息,请访问:http://java-recent.blogspot.in
使用spring apache poi repo
读/写Excel文件的另一种方法是使用Windmill . 它提供了一个流畅的API来处理Excel和CSV文件 .
导入数据
导出数据
.csv或POI肯定会这样做,但你应该知道Andy Khan的JExcel . 我认为它是迄今为止用于Excel的最佳Java库 .
要读取xlsx文件,我们可以使用Apache POI libs试试这个:
一个简单的CSV文件就足够了
您无法并行读取和写入相同的文件(Read-write lock) . 但是,我们可以对临时数据(即输入/输出流)进行并行操作 . 仅在关闭输入流后才将数据写入文件 . 应遵循以下步骤 .
将文件打开到输入流
将同一文件打开到输出流
阅读并进行处理
将内容写入输出流 .
关闭读取/输入流,关闭文件
关闭输出流,关闭文件 .
Apache POI - 读/写相同的excel示例
如果列号变化,您可以使用它
可以找到相应的mavendependency here
当然,您会发现下面的代码很有用,易于阅读和编写 . 这是一个
util
类,您可以在main方法中使用它,然后您可以使用下面的所有方法 .有一个新的简单和非常酷的工具(10倍到Kfir):xcelite
Write:
Read:
试试Apache POI HSSF . 这是一个如何读取excel文件的示例:
在文档页面上,您还可以获得有关如何写入Excel文件的示例 .
请使用Apache POI库并尝试此操作 .
如果您需要使用Java中的office文档执行更多操作,请按照提及的方式访问POI .
为了简单地读取/写入您请求的Excel文档,您可以使用CSV格式(也如上所述):
Apache POI可以为你做到这一点 . 特别是HSSF模块 . quick guide最有用 . 这是如何做你想要的 - 特别是创建一个表并写出来 .
我编辑了最多投票的一个,因为它没有完全计算空白列或行,所以这是我的代码我测试它现在可以获得excel文件的任何部分中的任何单元格 . 此外,你现在可以在填充列之间有空白列,它会读取它们
首先在项目类路径中添加所有这些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
Code for writing in a excel file:
Code for reading from excel file
你也可以考虑JExcelApi . 我发现它比POI设计得更好 . 有一个教程here .
这会将JTable编写为制表符分隔文件,可以轻松导入Excel . 这很有效 .
如果将Excel工作表另存为XML文档,则还可以使用代码为EXCEL构建XML文件 . 我用这个词做了这个,所以你不必使用第三方包 .
这可以代码将JTable取出然后只写一个分隔到任何文本文件的选项卡,然后导入到Excel中 . 我希望这有帮助 .
码: