Java解析Excel
使用poi
下面是两个实例(Excel2007)
写入Excel
package execel01;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcel {
public static void main(String[] args) throws IOException {
Workbook wb = new XSSFWorkbook();
CreationHelper creationHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("第一页");
//创建行
Row row = sheet.createRow((short)0);
//创建单元格,方法1
Cell cell = row.createCell(0);
cell.setCellValue("1");
//直接创建单元个,方法2
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(creationHelper.createRichTextString("你好啊,我是bug"));
row.createCell(3).setCellValue(creationHelper.createRichTextString("www.baidu.com"));
row.createCell(4).setCellValue(true);
//写入文件
FileOutputStream fileOutputStream;
fileOutputStream = new FileOutputStream("F:\\java\\bug315.xlsx");
wb.write(fileOutputStream);
fileOutputStream.close();
System.out.println("写入成功");
}
}
读取Excel
package execel01;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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 ReadExcel {
public static void main(String[] args) throws EncryptedDocumentException,
InvalidFormatException, IOException {
// TODO Auto-generated method stub
InputStream in = new FileInputStream("F:\\java\\bug315.xlsx");
Workbook wb = WorkbookFactory.create(in);
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
System.out.println("\n\n\n");
System.out.println("=========="+sheet.getSheetName()+"================");
Iterator iterator = sheet.rowIterator();
while (iterator.hasNext()) {
Row row = (Row) iterator.next();
System.out.println();
System.out.print("第" + (row.getRowNum() + 1)
+ "行: ");
Iterator iteratorCell = row.cellIterator();
while (iteratorCell.hasNext()) {
Cell cell = (Cell) iteratorCell.next();
// 打印单元格的数据
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getRichStringCellValue()
.getString() + " ");
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.print(cell.getDateCellValue() + " ");
} else {
System.out.print(cell.getNumericCellValue() + " ");
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + " ");
break;
case Cell.CELL_TYPE_FORMULA:
System.out.print(cell.getCellFormula() + " ");
default:
break;
}
}
}
}
}
}