JAVA对Excel的导出与导入

相关jar包:jxl

使用pom配置导入

		<dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

Excel的导出

示例代码

@Test
    public void testOut() throws Exception {
        //1,创建excel对象
        WritableWorkbook excel = Workbook.createWorkbook(new File("out.xls"));
        //2,创建Sheet对象(名称,位置)
        WritableSheet sheet = excel.createSheet("第一个Sheet", 0);
        //-------------------------普通单元格---------------------------
        //设置字体的格式
        WritableFont wf = new WritableFont(WritableFont.ARIAL);
        wf.setColour(Colour.BLUE);
        //设置单元格的格式
        WritableCellFormat wcf = new WritableCellFormat(wf);
        //设置背景颜色
        wcf.setBackground(Colour.RED);
        //设置水平居中
        wcf.setAlignment(Alignment.CENTRE);
        //设置竖直居中
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
        //3,建立单元格(列,行,内容,格式)
        Label cell = new Label(0, 0, "第一个单元格", wcf);
        //--------------------------日期单元格-----------------------------------
        //设置日期单元格的格式
        WritableCellFormat st = new WritableCellFormat(new DateFormat("yyyy-MM-dd hh:mm:ss"));
        st.setVerticalAlignment(VerticalAlignment.CENTRE);
        st.setAlignment(Alignment.CENTRE);
        //创建日期单元格对象
        DateTime cellDate = new DateTime(6, 6, new Date(), st);
        //-------------------------------------------------------------
        //4,把单元格添加进sheet中
        sheet.addCell(cell);
        sheet.addCell(cellDate);
        //设置列和行的大小
        sheet.setColumnView(0, 50);
        sheet.setColumnView(6, 50);
        sheet.setRowView(0, 500);
        //合并单元格(左上角坐标(列,行),右下角坐标列,行))
        sheet.mergeCells(0, 0, 2, 2);
        //5,导出
        excel.write();
        //6,关闭流
        excel.close();
    }

Excel文件内容:

Excel的导入

Excel文件内容:

示例代码:

    @Test
    public void readExcel() throws Exception {
        Workbook wb = Workbook.getWorkbook(new File("H:\\IDEA_WorkSpace\\CRM\\read.xls"));
        Sheet sheet = wb.getSheet(0);
        int cols = sheet.getColumns();//总列数
        int rows = sheet.getRows();//总行数
        for (int i = 0; i < rows; i++) {
            for (int j = 0; j < cols; j++) {
                //根据列-行获取每个单元格对象
                Cell cell = sheet.getCell(j, i);
                //日期类型的处理
                if (cell.getType().equals(CellType.DATE)) {
                    DateCell dateCell = (DateCell) sheet.getCell(j, i);
                    Date jxlDate = dateCell.getDate();
                    //设置时间格式
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                    //设置时区(东八区)
                    sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
                    System.out.println(sdf.format(jxlDate));
                } else {
                    //打印普通单元格的内容
                    System.out.print(cell.getContents() + "\t");
                }
            }
            System.out.println();
        }
    }

注:jxl无法对后缀为.xlsx的Excel文件导入,只能导入.xls格式:会报:
jxl.read.biff.BiffException: Unable to recognize OLE stream异常