Excel 的基本操作

读写Excel常用的技术

1. POI
2. JXL
3. FASTEXCEL

POI:通过HSSF (Horrible SpreadSheet Format), 用纯Java代码来读取,写入, 修改Excel文件.
HSSF : 读写Microsoft Excel格式档案的功能.
XSSF : 读写Microsoft Excel OOXML格式档案的功能.
HWPF :读写Microsoft Word格式档案的功能.
HSLF :读写Mircosoft PowerPoint格式档案的功能.
HDGF :读写Microsoft Visio格式档案的功能.
iText :生成PDF或rtf的文档, 而且将XML, Html文件转换为PDF文件.

JXL基本操作-创建Excel

public static void main(String[] args) {
    String[] title = {"id","name","sex"};
    //创建Excel文件
    File file = new File("jxl_test.xls");
    try {
        file.createNewFile();
    //创建工作簿
        WritableWorkbook workbook = Workbook.createWorkbook(file);
    //创建sheet
        WritableSheet sheet = workbook.createSheet("sheet1", 0);
        Label label = null;
    //第一行设置列名
        for (int i = 0; i < title.length; i++) {
            label = new Label(i,0,title[i]);
            sheet.addCell(label);
        }
    //追加数据
        for (int i = 1; i < 10; i++) {
            label = new Label(0,i,"a" + 1);
            sheet.addCell(label);
            label = new Label(1,i,"user" + i);
            sheet.addCell(label);
            label = new Label(2,i,"男");
            sheet.addCell(label);
        }
    //写入数据
        workbook.write();
        workbook.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

JXL基本操作-解析Excel

public static void main(String[] args) {
    try {
    //创建workbook
        Workbook workbook = Workbook.getWorkbook(new File("jxl_test.xls"));
    //获取第一个工作表sheet
        Sheet sheet = workbook.getSheet(0);
    //获取数据
        for (int i = 0; i < sheet.getRows(); i++) {
            for (int j = 0; j < sheet.getColumns(); j++){
                Cell cell = sheet.getCell(j,i);
                System.out.print(cell.getContents() + "  ");
            }
            System.out.println();
        }
        workbook.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

POI基本操作(XSSF)-创建Excel

public static void main(String[] args) {

        String[] title = {"id","name","sex"};

        //创建Excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建一个工作表sheet
        Sheet sheet = workbook.createSheet();
        //创建第一行
        Row row = sheet.createRow(0);
        Cell cell = null;
        //插入第一行数据 id,name,sex
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        //追加数据
        for (int i = 1; i <= 10; i++) {
            Row nextrow = sheet.createRow(i);
            Cell cell2 = nextrow.createCell(0);
            cell2.setCellValue("a" + i);
            cell2 = nextrow.createCell(1);
            cell2.setCellValue("user" + i);
            cell2 = nextrow.createCell(2);
            cell2.setCellValue("男");
        }
        //创建一个文件
        File file = new File("poi_test.xlsx");
        try {
            file.createNewFile();
            //将Excel内容存盘
            FileOutputStream stream = FileUtils.openOutputStream(file);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

POI基本操作(HSSF)-创建Excel

public static void main(String[] args) {

        String[] title = {"id","name","sex"};

        //创建Excel工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个工作表sheet
        HSSFSheet sheet = workbook.createSheet();
        //创建第一行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
        //插入第一行数据 id,name,sex
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        //追加数据
        for (int i = 1; i <= 10; i++) {
            HSSFRow nextrow = sheet.createRow(i);
            HSSFCell cell2 = nextrow.createCell(0);
            cell2.setCellValue("a" + i);
            cell2 = nextrow.createCell(1);
            cell2.setCellValue("user" + i);
            cell2 = nextrow.createCell(2);
            cell2.setCellValue("男");
        }
        //创建一个文件
        File file = new File("poi_test.xls");
        try {
            file.createNewFile();
            //将Excel内容存盘
            FileOutputStream stream = FileUtils.openOutputStream(file);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

POI基本操作(HSSF)-解析Excel

public static void main(String[] args) {

    //需要解析的Excel文件
    File file = new File("poi_test.xls");
    try {
    //创建Excel，读取文件内容
        HSSFWorkbook workbook =
                    new HSSFWorkbook(FileUtils.openInputStream(file));
    //获取第一个工作表workbook.getSheet("Sheet0");
    //HSSFSheet sheet = workbook.getSheet("Sheet0");
    //读取默认第一个工作表sheet
        HSSFSheet sheet = workbook.getSheetAt(0);
        int firstRowNum = 0;
    //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();
        for (int i = firstRowNum; i <=lastRowNum; i++) {
            HSSFRow row = sheet.getRow(i);
    //获取当前行最后单元格列号
            int lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                HSSFCell cell = row.getCell(j);
                String value = cell.getStringCellValue();
                    System.out.print(value + "  ");
                }
            System.out.println();
        }

    } catch (IOException e) {
        e.printStackTrace();
    }
}