（最全最灵活地）利用Jxl工具包实现Excel表的内…

2019-08-16 09:26:43来源：博客园阅读 ()

（最全最灵活地）利用Jxl工具包实现Excel表的内容读取、写入（可向已有表中追加数据）

1.引子

（1）读取

Jxl工具比较强大，可以方便地实现Excel表的读取和写入。另一款工具Poi也具有相似的功能，并且功能更多，运用也相对复杂。Poi读取Excel表内容时，需要先判断其内容格式，如日期、数字等，再使用相对应的方法对获取单元格内容。而Jxl则简单得多，将所有格式的单元格格式都视为字符串，这样一来获取单元格内容就容易些。另外我在网上也看了一些帖子，发现传入IO流参数时，在方法调用结束后，这个流会被自动关闭，后面不能再使用此流参数。我经过一番测试，用文件File对象作为参数，不会出现因流关闭而导致的空指针异常.

（2）写入

1）向原有的工作表中写入数据（不覆盖原有数据）

Jxl向Excel中写入数据时，相对读取数据要复杂得多。在Excel中已存在的表中写入数据不太容易，此时不能用“WritableWorkbook workbook = Workbook.createWorkbook(file)"这种方法是创建一个可写入的工作薄，调用此方法会覆盖掉原有的工作表数据。这种情况下要先用方法"Workbook book = Workbook.getWorkbook(excel);",先获取一个只读的工作薄对象，再使用代码“WritableWorkbook workbook = Workbook.createWorkbook(excel, book);"，传入两个参数，Excel文件对象excel、只读（刚才获得到的）工作薄对象book。这样就可以创建并初始化一个可追加写入数据的工作薄对象 workbook 。

    // 可写的工作薄
    private WritableWorkbook workbook;
    // 只读工作薄
    private Workbook book;
    public JxlExcelWriter(File excel)
    {
        try
        {
            // Excel文件不存在时，初始化一个可写入的工作薄
            if (!excel.exists())
            {
                File file = prepareFile(excel);
                workbook = Workbook.createWorkbook(file);
            }
            
           
            /**
             * Excel文件存在时，表明Excel中至少有一个工作表，
             *  初始化一个可向工作表追加数据且能写入新数据的工作薄
             */
            else
            {
                book = Workbook.getWorkbook(excel);
                /**
                 * 此静态方法通过传入两个参数，Excel文件对象excel、只读工作工作薄对象book，<br/>
                 * 来创建初始化一个可追加数据的工作薄对象
                 */
                workbook = Workbook.createWorkbook(excel, book);
            }
           
        }
        catch (IOException | BiffException e)
        {
            e.printStackTrace();
        }
        
    }

2）向工作薄/工作表多次写入数据

写入方法的调用
网上大多数据帖子都是向工作薄/工作表一次性写入数据，这显然不太灵活。经过多次测试试错终于找到了新大陆，原来只能单次写入数据的根本原因是:过早的调用了WritableWorkbook的write()和close()方法。

1   try
2         {
3             workbook.write();
4             workbook.close();
5         }
6         catch (WriteException | IOException e)
7         {
8             e.printStackTrace();
9         }

"sheel.addCell(Lable)"方法只是将数据写入工作表缓冲区中，而真正写入到磁盘Excel文件又不得不调用WritableWorkbook的write()方法，另外在最后我们一般还需要将资源关闭，又得调用WritableWorkbook的colse()方法。

  WritableSheet sheet = getWritableSheet(sheetName, sheetIndex);
 jxl.write.Label lable = new jxl.write.Label(i, rowIndex, rowContent[i]);
                        sheet.addCell(lable);

多次写入数据的思路
在一次写入数据时调用write()和close()方法后，下一次便不能再写入数据了。其中的原因可以理解，因为 WritableWorkbook的资源都已经被关闭了，肯定不能再次向其中添加数据，除非再次打开此资源，但WritableWorkbook对象没有reopen()方法。换个角度看问题，可以在每次写入数据时调用write()方法，在写入所有所需的数据之后再调用close()方法。但这种方式也不可行，只调用write()而不调用close()会导致Excel文件格式不对，不能正常打开Excel文件。由此可见write()和close()必须同时且一次性地调用。到此时不难想到:可以多次将数据添加到缓冲区，最终才调用write()、close()方法，将缓冲区的所有数据一次性刷新写入到Excel文件中。

2.代码

（1）读取Excel内容

代码较长，已折叠

import java.io.File;
import java.io.IOException;
import java.util.Map;
import java.util.TreeMap;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * Excel表文本数据的读取工具类
 * 
 * @author 小伍
 */
public final class JxlExcelReader
{
    // Excel表工作薄对象
    private Workbook workbook;
    
    /**
     * <构造方法>
     * @param excel Excel表文件对象
     */
    public JxlExcelReader(File excel)
    {
        if (!excel.exists())
            throw new IllegalArgumentException("excel文件不存在");
        try
        {
            workbook = Workbook.getWorkbook(excel);
        }
        catch (BiffException | IOException e)
        {
            e.printStackTrace();
        }
        
    }
    
    /**
     * 通过索引获得工作表
     * 
     * @param index
     * @return
     * @see [类、类#方法、类#成员]
     */
    public Sheet getSheetByIndex(int index)
    {
        Sheet sheet = null;
        if (index < workbook.getNumberOfSheets())
        {
            sheet = workbook.getSheet(index);
        }
        return sheet;
    }
    
    /**
     * 通过表名获得工作表
     * @param name
     * @return
     * @see [类、类#方法、类#成员]
     */
    public Sheet getSheetByName(String name)
    {
        return workbook.getSheet(name);
    }
    
    /**
     * 根据工作表索引、行索引、列索引获取一个指定单元格的文本内容
     * @param rowIndex 行索引
     * @param colIndex 列索引
     * @param sheetIndex 工作表索引
     * @return 单元格的文本内容
     * @see [类、类#方法、类#成员]
     * @Description: TODO
     */
    public String readCellContent(int rowIndex, int colIndex, int sheetIndex)
    {
        Sheet sheet = workbook.getSheet(sheetIndex);
        if ((rowIndex < 0 || rowIndex >= sheet.getRows()) || (colIndex < 0 || colIndex >= sheet.getColumns()))
            throw new IllegalArgumentException("参数 rowIndex 或colIndex超出了行、列索引的可用范围");
        Cell[] cells = sheet.getRow(rowIndex);
        String cellContent = cells[colIndex].getContents();
        return cellContent;
    }
    
    /**
     * 获取指定工作表中除第一行（表头）以外的所有单元格内容
     * 
     * @param sheetIndex 工作表索引
     * @return Map对象的key值是行索引，values值是包含每行的各单元格文本内容的字符串数组
     * @see [类、类#方法、类#成员]
     * @Description: TODO
     */
    public Map<Integer, String[]> readContent(int sheetIndex)
    {
        Sheet sheet = workbook.getSheet(sheetIndex);
        return readData(1, sheet.getRows() - 1, 0, sheet.getColumns(), sheetIndex);
    }
    
    /**
     * 获取指定工作表的第一行（表头）文本内容
     * 
     * @param sheetIndex 工作表索引
     * @return 第一行的各单元格文本内容的字符串数组
     * @see [类、类#方法、类#成员]
     * @Description: TODO
     */
    public String[] readHeader(int sheetIndex)
    {
        Sheet sheet = workbook.getSheet(sheetIndex);
        int cols = sheet.getColumns();
        Cell[] cells = sheet.getRow(0);
        String[] header = new String[cols];
        for (int j = 0; j < cols; j++)
        {
            header[j] = cells[j].getContents();
        }
        return header;
    }
    
    /**
     * 获取指定工作表的所有单元格文本内容
     * 
     * @param sheetIndex 工作表索引
     * @return Map对象的key值是行索引，values值是包含每行的各单元格文本内容的字符串数组
     * @see [类、类#方法、类#成员]
     * @Description: TODO
     */
    public Map<Integer, String[]> readHeaderAndContent(int sheetIndex)
    {
        Sheet sheet = workbook.getSheet(sheetIndex);
        return readData(0, sheet.getRows(), 0, sheet.getColumns(), sheetIndex);
    }
    
    /**
     * 获取指定工作表的指定范围的单元格文本内容
     * 
     * @param fromRowIndex 起始行的索引
     * @param rowNum 读取表的总行数
     * @param fromColIndex 起始列的索引
     * @param colNum 读取表的总列数
     * @param sheetIndex 工作表索引
     * @return Map对象的key值是行索引，values值是包含每行的各单元格文本内容的字符串数组
     * @see [类、类#方法、类#成员]
     * @Description: TODO
     */
    public Map<Integer, String[]> readData(int fromRowIndex, int rowNum, int fromColIndex, int colNum,
        int sheetIndex)
    {
        Sheet sheet = workbook.getSheet(sheetIndex);
        // 总行数
        int rows = sheet.getRows();
        // 总列数
        int cols = sheet.getColumns();
        // 要读取的最大列索引加1的值
        int endColIndex = fromColIndex + colNum;
        // 最大行索引加1的值：
        int endRowIndex = fromRowIndex + rowNum;
        if ((fromRowIndex < 0 || fromRowIndex >= rows) || (fromColIndex < 0 || fromColIndex >= cols))
            throw new IllegalArgumentException("参数 fromRow 或fromCol超出了行、列索引的可用范围。");
        if (rowNum < 1 || colNum < 1)
            throw new IllegalArgumentException("参数rowNum 、colNum必须大于1，读入的行总数、列总数都必须大于1");
        if (endRowIndex > rows || endColIndex > cols)
            throw new IllegalArgumentException("参数超出了Excel表可获取的最大列数或最大行数。");
        /*
         * 储存单元格内容的map,（TreeMap能有序排列） contents的key为行索引，value(String[])为单行各单元格的内容（string[0]为第一列的单元格）
         */
        Map<Integer, String[]> contents = new TreeMap<Integer, String[]>();
        for (int i = fromRowIndex; i < endRowIndex; i++)
        {
            Cell[] cells = sheet.getRow(i);
            // 定义一个列总数长度的字符数组，不读取的rowContent[K]就为null;
            String[] rowContent = new String[cols];
            for (int j = fromColIndex; j < endColIndex; j++)
            {
                // 要读取的rowContent[j],即使cell[j]为空，在添加空字符串""后rowContent[j]就不为null.
                rowContent[j] = cells[j].getContents() + "";
            }
            contents.put(i, rowContent);
        }
        return contents;
    }
    
    /**
     * 获取指定工作表的指定行范围的单元格文本内容
     * @param fromRowIndex 起始行的索引
     * @param rowNum 读取表的总行数
     * @param sheetIndex 工作表索引
     * @return Map对象的key值是行索引，values值是包含每行的各单元格文本内容的字符串数组
     * @see [类、类#方法、类#成员]
     * @Description: TODO
     */
    public Map<Integer, String[]> readData(int fromRowIndex, int rowNum, int sheetIndex)
    {
        Sheet sheet = workbook.getSheet(sheetIndex);
        return readData(fromRowIndex, rowNum, 0, sheet.getColumns(), sheetIndex);
    }
    
    /**
     * 关闭资源
     * @see [类、类#方法、类#成员]
     * @Description: TODO
     */
    public void close()
    {
        if (workbook != null)
            workbook.close();
    }
}

View Code

（2）写入数据至Excel

代码较长，已折叠

import java.io.File;
import java.io.IOException;
import java.util.Map;
import java.util.Set;

import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * 数据写入Excel工具类
 * 
 * @author 小伍
 */
public final class JxlExcelWriter
{
    // 可写的工作薄
    private WritableWorkbook workbook;
    
    // 只读工作薄
    private Workbook book;
    
    /**
     * 构造方法
     * @param excel Excel文件对象，允许其文件本身不存在<br/>
     *  若excel文件不存在，构造方法将自动创建一个文件对象对应的磁盘文件
     */
    public JxlExcelWriter(File excel)
    {
        try
        {
            // Excel文件不存在时，初始化一个可写入的工作薄
            if (!excel.exists())
            {
                File parentFile = excel.getParentFile();
                parentFile.mkdirs();
                workbook = Workbook.createWorkbook(excel);
            }
            
            /**
             * Excel文件存在时，表明Excel中至少有一个工作表， 
             * 初始化一个可向工作表追加数据且能写入新数据的工作薄
             */
            else
            {
                /**
                 * 此静态方法通过传入两个参数，Excel文件对象excel、只读工作工作薄对象book，<br/>
                 * 来创建初始化一个可追加数据的工作薄对象
                 */
                book = Workbook.getWorkbook(excel);
                workbook = Workbook.createWorkbook(excel, book);
            }
            
        }
        catch (IOException | BiffException e)
        {
            e.printStackTrace();
        }
    }
    
    /**
     * 将多行数据写入到Excel缓冲区
     * 
     * @param contents 要写入到Excel的数据（映射表），其key值表示行索引，<br/>
     *            其value值表示一行所有的单元格内容，字符串数组的每个元素对应一个单元格内容
     * @param sheetIndex 要写入到Excel的工作表索引
     * @see [类、类#方法、类#成员]
     */
    public void write(Map<Integer, String[]> contents, int sheetIndex)
    {
        String sheetName = generateSheetName(sheetIndex);
        write(contents, sheetName, sheetIndex);
    }
    
    /**
     * 将多行数据写入到Excel缓冲区
     * 
     * @param contents 要写入到Excel的数据（映射表），其key值表示行索引，<br/>
     *            其value值表示一行所有的单元格内容，字符串数组的每个元素对应一个单元格内容
     * @param sheetName 要写入到Excel的工作表名
     * @param sheetIndex 要写入到Excel的工作表索引
     */
    public void write(Map<Integer, String[]> contents, String sheetName, int sheetIndex)
    {
        if (contents == null || contents.isEmpty())
            throw new IllegalArgumentException("参数contents不包含任何内容或为空指针");
        // 得到工作表
        WritableSheet sheet = getWritableSheet(sheetName, sheetIndex);
        // 将数据添加到工作表的缓冲区中
        try
        {
            Set<Integer> keys = contents.keySet();
            for (int rowIndex : keys)
            {
                String[] rowContent = contents.get(rowIndex);
                for (int i = 0; i < rowContent.length; i++)
                {
                    // 文本内容为空时，sheet表增加一个Blank
                    if (rowContent[i] == null)
                    {
                        jxl.write.Blank blank = new jxl.write.Blank(i, rowIndex);
                        sheet.addCell(blank);
                    }
                    else
                    {
                        jxl.write.Label lable = new jxl.write.Label(i, rowIndex, rowContent[i]);
                        sheet.addCell(lable);
                    }
                }
            }
        }
        catch (WriteException e)
        {
            e.printStackTrace();
        }
    }
    
    /**
     * 创建/获取工作表
     * 
     * @param sheetName
     * @param sheetIndex
     * @return
     * @see [类、类#方法、类#成员]
     */
    private WritableSheet getWritableSheet(String sheetName, int sheetIndex)
    {
        WritableSheet sheet = null;
        if (sheetIndex < workbook.getNumberOfSheets())
        {
            sheet = workbook.getSheet(sheetIndex);
            sheet.setName(sheetName);
        }
        else
        {
            sheet = workbook.createSheet(sheetName, sheetIndex);
        }
        return sheet;
    }
    
    /**
     * 生成工作表表名
     * 
     * @param sheetIndex
     * @return
     * @see [类、类#方法、类#成员]
     */
    private String generateSheetName(int sheetIndex)
    {
        String sheetName = "";
        if (sheetIndex < workbook.getNumberOfSheets())
        {
            sheetName = workbook.getSheet(sheetIndex).getName();
        }
        else
        {
            sheetName = "sheet" + sheetIndex;
        }
        return sheetName;
    }
    
    /**
     * 将单行数据写入到Excel缓冲区
     * 
     * @param rowContent 要写入到Excel的数据，数组的每个元素对应一个单元格内容
     * @param rowIndex 写入到Excel的行索引
     * @param sheetIndex 要写入到Excel的工作表索引
     * @see [类、类#方法、类#成员]
     */
    public void writeRow(String[] rowContent, int rowIndex, int sheetIndex)
    {
        String sheetName = generateSheetName(sheetIndex);
        writeRow(rowContent, rowIndex, sheetName, sheetIndex);
    }
    
    /**
     * 将单行数据写入到Excel缓冲区
     * 
     * @param rowContent 要写入到Excel的数据，数组的每个元素对应一个单元格内容
     * @param rowIndex 写入到Excel的行索引
     * @param sheetName 要写入到Excel的工作表名
     * @param sheetIndex 要写入到Excel的工作表索引
     * @see [类、类#方法、类#成员]
     */
    public void writeRow(String[] rowContent, int rowIndex, String sheetName, int sheetIndex)
    {
        // 得到工作表
        WritableSheet sheet = getWritableSheet(sheetName, sheetIndex);
        try
        {
            for (int i = 0; i < rowContent.length; i++)
            {
                if (rowContent[i] == null)
                {
                    jxl.write.Blank blank = new jxl.write.Blank(i, rowIndex);
                    sheet.addCell(blank);
                }
                else
                {
                    jxl.write.Label lable = new jxl.write.Label(i, rowIndex, rowContent[i]);
                    sheet.addCell(lable);
                }
            }
        }
        catch (WriteException e)
        {
            e.printStackTrace();
        }
    }
    
    /**
     * 写入一个单元内容到Excel缓冲区
     * 
     * @param content 要写入的单元格内容
     * @param rowIndex 写入到Excel的行索引
     * @param colIndex 写入到Excel的列索引
     * @param sheetIndex 要写入到Excel的工作表索引
     */
    public void writeCell(String content, int rowIndex, int colIndex, int sheetIndex)
    {
        String sheetName = generateSheetName(sheetIndex);
        writeCell(content, rowIndex, colIndex, sheetName, sheetIndex);
        
    }
    
    /**
     * 写入一个单元内容到Excel缓冲区
     * 
     * @param content 要写入的单元格内容
     * @param rowIndex 写入到Excel的行索引
     * @param colIndex 写入到Excel的列索引
     * @param sheetName 要写入到Excel的工作表名
     * @param sheetIndex 要写入到Excel的工作表索引
     */
    public void writeCell(String content, int rowIndex, int colIndex, String sheetName, int sheetIndex)
    {
        // 得到工作表
        WritableSheet sheet = getWritableSheet(sheetName, sheetIndex);
        try
        {
            if (content == null)
            {
                jxl.write.Blank blank = new jxl.write.Blank(colIndex, rowIndex);
                sheet.addCell(blank);
            }
            else
            {
                jxl.write.Label lable = new jxl.write.Label(colIndex, rowIndex, content);
                sheet.addCell(lable);
            }
        }
        catch (WriteException e)
        {
            e.printStackTrace();
        }
    }
    
    /**
     * 将Excel缓冲区的数据刷新写入到Excel文件中, <br/>
     * 在最后此方法必须被调用，否则数据不能真正写入Excel文件中
     */
    public void flush()
    {
        try
        {
            if (workbook != null)
            {
                workbook.write();
                workbook.close();
            }
            if (book != null)
            {
                book.close();
            }
        }
        catch (WriteException | IOException e)
        {
            e.printStackTrace();
        }
    }
}

View Code

3.测试效果

（1）.数据读取测试

在读取Excel表中有一个基础方法，public Map<Integer, String[]> readData(int fromRowIndex, int rowNum, int fromColIndex, int colNum, int sheetIndex)。此方法的参数最多，控制能力最强，能够灵活适应各种场景。其他方法大都是调用此方法，以实现定制化的一些读取操作的。

下面对排班信息进行读取：

Excel源数据

这是一个Excel文件中的一张排班工作表Sheet的局部内容，虽然不太规整，但应该不会影响我的正常进行读取。

测试方法

调用工具类中的方法进行数据读取,读取attend.xls的第一张工作表（排班表）的从第3行起的8行数据

    @Test
    public void readRows(){
        JxlExcelReader excelReader = new JxlExcelReader(new File("d:/IOTest/attend.xls"));
        // 读取attend.xls的第一张工作表（排班表）的从第3行起的8行数据
        Map<Integer, String[]> datas = excelReader.readData(2, 8, 0);
        Set<Integer> rowIndexs = datas.keySet();
        for (int rowIndex : rowIndexs)
        {
            String[] rowData = datas.get(rowIndex);
            System.out.println(Arrays.toString(rowData));
        }
    }

控制台输出

信息读取并打印出来了, excel部分单元格无值，所以打印结果部分输出为空。

（2）.数据写入测试

1）一般数据的写入

在向Excel表中写入数据的工具类中也有一个基础方法，public void write(Map<Integer, String[]> contents, String sheetName, int sheetIndex) 。其他方法都是调用此方法得以实现的，如

public void write(Map<Integer, String[]> contents, int sheetIndex) 是先获得工作表的名字，将此名字作为sheetName参数。

public void write(Map<Integer, String[]> contents, int sheetIndex)
    {
        String sheetName = generateSheetName(sheetIndex);
        write(contents, sheetName, sheetIndex);
    }

测试方法

@Test
    public void writeRows(){
        JxlExcelReader excelReader = new JxlExcelReader(new File("d:/IOTest/attend.xls"));
        // 读取attend.xls的第一张工作表（排班表）的从第3行起的8行数据
        Map<Integer, String[]> datas = excelReader.readData(2, 8, 0);
        //将数据写入writer-result.xls文件中
        JxlExcelWriter excelWriter=new JxlExcelWriter(new File("d:/IOTest/writer-result.xls"));
        excelWriter.write(datas, 0);
        
    }

Excel文件写入结果

文件不能打开，提示文件格式错误、文件遭到损坏。这到底是什么原因？是因为我们只将数据写到内存缓冲区，并没有将数据刷新到磁盘文件中，需要调用方法flush() ,这里需要特别注意。

修改后的代码

 @Test
    public void writerRows(){
        JxlExcelReader excelReader = new JxlExcelReader(new File("d:/IOTest/attend.xls"));
        // 读取attend.xls的第一张工作表（排班表）的从第3行起的8行数据
        Map<Integer, String[]> datas = excelReader.readData(2, 8, 0);
        //将数据写入writer-result.xls文件中
        JxlExcelWriter excelWriter=new JxlExcelWriter(new File("d:/IOTest/writer-result.xls"));
        excelWriter.write(datas, 0);
　　　　//此方法flush（）一定得调用，否则无法真正写入数据
        excelWriter.flush();
        
    }

写入到excel中的数据

2）数据分批(多次)写入

可能同学会问，为什么最后非得调用flush方法，干嘛不将flush里面的代码写在write（）方法中，毕竟使用者很可能忘记了调用flush方法的。这样不多此一举吗，增加了让人出错的机率？其实这是一没办法的办法，一种妥协吧，在时需要分批多次地写入数据，此时只能用这条思路实现，否则除第一次调用write()方法写入的数据外不能再次写入新数据到excel表中。比如，我要做一个解析考勤的小系统，对原始考勤信息进行分类汇总

这是将汇总内容向Excle表中写入的部分代码

 public void deptAttend()
    {
        
        Set<Entry<String, List<Employee>>> entrys = attendGroupByDept.entrySet();
        
        Iterator<Entry<String, List<Employee>>> itor = entrys.iterator();
        int sheetIndex = 1;
        JxlExcelWriter excelWriter = new JxlExcelWriter(excelFile);
        
        while (itor.hasNext())
        {
            Entry<String, List<Employee>> entry = itor.next();
            List<Employee> emps = entry.getValue();
            //每次循环写入到同一个Excel文件的不同工作表sheet
            dataWriteToSheet(emps, sheetIndex, excelWriter);
            sheetIndex++;
        }
         //数据添加完成后，再调用flush方法
        excelWriter.flush();
        
    }

 private void dataWriteToSheet(List<Employee> emps, int sheetIndex, JxlExcelWriter excelWriter)
    {
      // .........省略部分代码
        //写入数据到指定索引的一个工作表
        excelWriter.write(contents, "部门" + deptNo + "的考勤", sheetIndex);
        
    }

　　原始Excel数据

按部门汇总后的Excel数据

3）向已有数据的工作表中追加写入新数据

测试代码

 public static void main(String[] args)
    {
        JxlExcelWriter excelWriter = new JxlExcelWriter(new File("d:/IOTest/stu1.xls"));
        String[] rowContent = new String[] {null,"009", "小何", "2018-12-01", "13451234", "2017", "广元", "网络案例"};
        excelWriter.writeRow(rowContent, 11,0);
        excelWriter.flush();
    }

Excel文件数据追加前