JAVA实现Excel导入导出

JXL实现Excel的Excel导入导出

首先要下载jxl的jar包
jxl2.6.12.jar下载
https://nchc.dl.sourceforge.net/project/jexcelapi/jexcelapi/2.6.12/jexcelapi_2_6_12.zip
创建工程导入jar包

JXL实现Excel的创建

JXL创建Excel文件

/**
 * JXL创建Excel文件
 * @author c
 *
 */
public class JxlExpExcel {


    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);
            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"+i);
                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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }


}

运行后可以在工程目录下查看到jxl_test.xls文件
打开查看内容：

---

JXL实现Excel的解析

JXL解析Excel

/**
 * JXL解析Excel
 * @author c
 *
 */
public class JxlReadExcel {

    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.println(cell.getContents());
                }
                System.out.println();
            }
            workbook.close();
        }catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

运行结果如下：

---

POI实现Excel的Excel导入导出

首先需要下载poi的jar包
poi.3.15.jar下载地址：https://mirrors.tuna.tsinghua.edu.cn/apache/poi/release/bin/poi-bin-3.15-20160924.zip
我们还需要导入commons.io.jar包
commons.io. 2.5jar下载地址：
https://mirrors.tuna.tsinghua.edu.cn/apache//commons/io/binaries/commons-io-2.5-bin.zip

导入jar包

POI实现Excel的创建

POI生成Excel文件

/**
 * POI生成Excel文件
 * @author c
 *
 */
public class PoiExpExcel {

    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;
        //插入第一行数据
        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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

运行后可以在工程目录下查看到poi_test.xls文件
查看里面的内容：

---

POI实现Excel的解析

POI解析文件内容

/**
 * POI解析文件内容
 * @author c
 *
 */
public class PoiReadExcel {

    public static void main(String[] args) {

        //需要解析的Excel文件
        File file = new File("poi_test.xls");
        try {
            //创建Excel,读取文件内容
            HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
            //获取第一个工作表
        //  HSSFSheet sheet = workbook.getSheet("Sheet0");
            //读取默认第一个工作表sheet
            HSSFSheet sheet = workbook.getSheetAt(0);

            int firstRowNum = 0;
            //获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 0; 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.println(value+" ");
                }
                System.out.println();
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

运行结果如下：