转载http://www.lookhan.com/experience/experience/20110113211355.html
在我们的项目当中经常会遇到把数据导入到Excel中,或者读取Excel中的数据到数据库中,用Apache POI可以很方便的实现,Apache POI是Apache基金会的开放源码函式库,可以在其官网上下载其Jar包,官网是http://poi.apache.org,下载后把里面所有的jar包拷贝到项目中(其中不仅仅是根目录下的Jar包),好,我们先来看看如何写入数据到Excel中,注意,这里讲的是针对Excel 2007的,如果是以前的版本的,读取的方式是不一样的,请注意:
Java代码
复制代码
- import java.io.FileNotFoundException;
- import java.io.FileOutputStream;
- import java.io.IOException;
-
- 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){
- try{
- Workbook wb = new XSSFWorkbook();
- CreationHelper createHelper = wb.getCreationHelper();
-
- Sheet sheet = wb.createSheet("sheet1");
-
- Row row = sheet.createRow((short) 0);
-
- row.createCell(0).setCellValue(258258258);
- row.createCell(1).setCellValue(0.67);
- row.createCell(2).setCellValue(createHelper.createRichTextString("http://www.lookhan.com"));
- row.createCell(3).setCellValue(createHelper.createRichTextString("Java知识笔记"));
-
- FileOutputStream fileOut = null;
- fileOut = new FileOutputStream("D:\\lookhan.xlsx");
- wb.write(fileOut);
- fileOut.close();
- System.out.println("写入成功!");
- } catch (FileNotFoundException e){
- e.printStackTrace();
- } catch (IOException e){
- e.printStackTrace();
- }
- }
-
- }
上面的测试类运行后,你就能在D盘下看到Excel文件了。 再看看如何读取Excel,首先定义一个自定义异常类,便于提示在读取Excel文件时出错的地方,因为一般对Excel的读取情况常见的是导入Excel数据到数据库中,一般如果用户在导入出错的话,可能是某个单元格的格式不对,用一个自定义异常可以很好的提示:
Java代码
复制代码
-
-
-
-
-
- public class ExcelFormatException extends Exception {
-
- private static final long serialVersionUID = 3435456589196458401L;
- private int row;
- private int column;
-
- public ExcelFormatException(String message, int row, int column){
- super(message);
- this.row = row;
- this.column = column;
- }
-
- public int getRow() {
- return row;
- }
-
- public int getColumn() {
- return column;
- }
-
- }
再来看看测试类:
Java代码
复制代码
- import java.io.FileInputStream;
- import java.io.InputStream;
-
- 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 ReaderExcel {
-
- public static void main(String[] args){
- String src = "D:\\lookhan.xlsx";
- try {
- ReaderExcel test = new ReaderExcel();
- test.getExcel(src);
- } catch (Exception e) {
- ExcelFormatException excelException = (ExcelFormatException)e;
- System.out.println(excelException.getMessage()+"行:"+excelException.getColumn()+"列:"+excelException.getRow());
- }
- }
-
- public void getExcel(String src) throws Exception{
-
- InputStream inp;
- inp = new FileInputStream(src);
- Workbook wb = WorkbookFactory.create(inp);
-
- Sheet sheet = wb.getSheetAt(0);
-
- for(int i=1; i1;i++){
- Row row = sheet.getRow(i);
-
- for (int j=0; j<4; j++){
- Cell cell = row.getCell(j);
- if(j == 0){
- switch (cell.getCellType()){
- case Cell.CELL_TYPE_NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)){
- System.out.println(cell.getDateCellValue());
- } else {
- throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));
- }
- break;
- default:
- throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));
- }
- }
- if(j == 1){
- switch (cell.getCellType()){
- case Cell.CELL_TYPE_STRING:
- System.out.println(cell.getRichStringCellValue());
- break;
- case Cell.CELL_TYPE_NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)){
- System.out.println(cell.getDateCellValue().toString());
- } else {
- System.out.println(cell.getNumericCellValue());
- }
- break;
- case Cell.CELL_TYPE_BOOLEAN:
- System.out.println(cell.getBooleanCellValue());
- break;
- case Cell.CELL_TYPE_FORMULA:
- System.out.println(cell.getCellFormula());
- break;
- default:
- throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));
- }
- }
- if(j == 2){
- switch (cell.getCellType()){
- case Cell.CELL_TYPE_STRING:
- System.out.println(cell.getRichStringCellValue());
- break;
- case Cell.CELL_TYPE_NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)){
- System.out.println(cell.getDateCellValue());
- } else {
- System.out.println(cell.getNumericCellValue());
- }
- break;
- case Cell.CELL_TYPE_BOOLEAN:
- System.out.println(cell.getBooleanCellValue());
- break;
- case Cell.CELL_TYPE_FORMULA:
- System.out.println(cell.getCellFormula());
- break;
- default:
- throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));
- }
- }
- if(j == 3){
- switch (cell.getCellType()){
- case Cell.CELL_TYPE_STRING:
- System.out.println(cell.getRichStringCellValue());
- break;
- case Cell.CELL_TYPE_NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)){
- throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));
- } else {
- System.out.println(cell.getNumericCellValue());
- }
- break;
- case Cell.CELL_TYPE_BOOLEAN:
- throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));
- case Cell.CELL_TYPE_FORMULA:
- throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));
- default:
- throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));
- }
- }
- }
- }
- inp.close();
-
- }
-
- }
上面的读取Excel的代码写的可能不是很好,里面有好多的判断,是这样的,POI对Excel的读取是要看里面数据的类型的,而有些数据有可能是字符串,又有可能是数字类型,而对不同的类型的读取所用的方法是不一样的,所以我在这里是先得到数据的类型,然后根据其类型来调用不同的方法。感觉这里用的不是很好,可是我也找不到比较好的方法,有知道的告诉我一声。 |