java对excel文件内容读写修改操作

时间：2023-03-08 20:53:19

java对excel文件内容读写修改操作

Read.java

package domain;

import java.io.FileInputStream;

import java.io.InputStream;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

public class Read {

    public void readExcel(String filePath)

    {

        try

        {

            InputStream is = new FileInputStream(filePath);

            Workbook rwb = Workbook.getWorkbook(is);

            // 这里有两种方法获取sheet表:名字和下标（从0开始）

            // Sheet st = rwb.getSheet("original");

            Sheet st = rwb.getSheet(0);

            /**

             *

             * //获得第一行第一列单元的值

             *

             * Cell c00 = st.getCell(0,0);

             *

             * //通用的获取cell值的方式,返回字符串

             *

             * String strc00 = c00.getContents();

             *

             * //获得cell具体类型值的方式

             *

             * if(c00.getType() == CellType.LABEL)

             *

             * {

             *

             * LabelCell labelc00 = (LabelCell)c00;

             *

             * strc00 = labelc00.getString();

             *

             * }

             *

             * //输出

             *

             * System.out.println(strc00);

             */

            // Sheet的下标是从0开始

            // 获取第一张Sheet表

            Sheet rst = rwb.getSheet(0);

            // 获取Sheet表中所包含的总列数

            int rsColumns = rst.getColumns();

            // 获取Sheet表中所包含的总行数

            int rsRows = rst.getRows();

            // 获取指定单元格的对象引用

            for (int i = 0; i < rsRows; i++)

            {

                for (int j = 0; j < rsColumns; j++)

                {

                    Cell cell = rst.getCell(j, i);

                    System.out.print(cell.getContents() + " ");

                }

                System.out.println();

            }

            // 关闭

            rwb.close();

        }

        catch (Exception e)

        {

            e.printStackTrace();

        }

    }

}

Writeadd.java

package domain;

import java.io.File;

import java.io.OutputStream;

import jxl.Workbook;

import jxl.format.UnderlineStyle;

import jxl.write.Boolean;

import jxl.write.DateFormat;

import jxl.write.DateTime;

import jxl.write.Label;

import jxl.write.Number;

import jxl.write.NumberFormat;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableImage;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

public class Writeadd {

    /** 输出Excel */

    public void writeExcel(OutputStream os)

    {

        try

        {

            WritableWorkbook wwb = Workbook.createWorkbook(os);

            // 创建Excel工作表 指定名称和位置

            WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);

            /************** 往工作表中添加数据 *****************/

            // 1.添加Label对象

            Label label = new Label(5, 0, "测试");

            Label labe2 = new Label(6, 0, "20163432");

            Label labe3 = new Label(7, 0, "20163432");

            ws.addCell(label);

            ws.addCell(labe2);

            ws.addCell(labe3);

            // 添加带有字型Formatting对象

            WritableFont wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);

            WritableCellFormat wcf = new WritableCellFormat(wf);

            Label labelcf = new Label(1, 0, "hello word !", wcf);

            ws.addCell(labelcf);

            // 添加带有字体颜色的Formatting对象

            WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,

                    UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.DARK_YELLOW);

            WritableCellFormat wcfFC = new WritableCellFormat(wfc);

            Label labelCF = new Label(1, 0, "石家庄铁道大学软件工程系", wcfFC);

            ws.addCell(labelCF);

            // 2.添加Number对象

            Number labelN = new Number(0, 1, 3.1415926);

            ws.addCell(labelN);

            // 添加带有formatting的Number对象

            NumberFormat nf = new NumberFormat("#.##");

            WritableCellFormat wcfN = new WritableCellFormat(nf);

            Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);

            ws.addCell(labelNF);

            // 3.添加Boolean对象

            Boolean labelB = new jxl.write.Boolean(0, 2, true);

            ws.addCell(labelB);

            Boolean labelB1 = new jxl.write.Boolean(1, 2, false);

            ws.addCell(labelB1);

            // 4.添加DateTime对象

            jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());

            ws.addCell(labelDT);

            // 5.添加带有formatting的DateFormat对象

            DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");

            WritableCellFormat wcfDF = new WritableCellFormat(df);

            DateTime labelDTF = new DateTime(1, 3, new java.util.Date(), wcfDF);

            ws.addCell(labelDTF);

            // 6.添加图片对象,jxl只支持png格式图片

            File image = new File("C:\\Users\\lenovo\\Desktop\\timg.png");

            WritableImage wimage = new WritableImage(6, 4, 4, 20, image);

            ws.addImage(wimage);

            // 7.写入工作表

            wwb.write();

            wwb.close();

        }

        catch (Exception e)

        {

            e.printStackTrace();

        }

    }

}

Update.java

package domain;

import java.io.File;

import jxl.CellType;

import jxl.Workbook;

import jxl.format.UnderlineStyle;

import jxl.write.Label;

import jxl.write.WritableCell;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

public class Update {

    /**

     * 将file1拷贝后,进行修改并创建输出对象file2

     *

     * 单元格原有的格式化修饰不能去掉，但仍可将新的单元格修饰加上去，

     *

     * 以使单元格的内容以不同的形式表现

     *

     */

    public void modifyExcel(File file1, File file2)

    {

        try

        {

            Workbook rwb = Workbook.getWorkbook(file1);

            WritableWorkbook wwb = Workbook.createWorkbook(file2, rwb);// copy

            WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,

                    UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLUE);

            WritableCellFormat wcfFC = new WritableCellFormat(wfc);

            WritableSheet ws = wwb.getSheet(0);

            WritableCell wc = ws.getWritableCell(0, 0);

            // 判断单元格的类型,做出相应的转换

            if (wc.getType() == CellType.LABEL)

            {

                Label labelCF = new Label(1, 0, "人物（新）", wcfFC);

                ws.addCell(labelCF);

                Label label = (Label) wc;

                label.setString("被修改");

            }

            wwb.write();

            wwb.close();

            rwb.close();

        }

        catch (Exception e)

        {

            e.printStackTrace();

        }

    }

}

Zhuhanshu.java

package domain;

import java.io.File;

import java.io.FileOutputStream;

import java.io.OutputStream;

import java.util.Scanner;

public class Zhuhanshu {

    public static void main(String args[])

    {

        // 读EXCEL

        Read read = null;

        // 输出EXCEL

        Writeadd writeadd = null;

        // 修改EXCEL

        Update update = null;

        try {

            int n = 0;

            Scanner in = new Scanner(System.in);

            while (n != 20) {

                System.out.println("**********java实现对excel文件的读写修改操作××××××××××");

                System.out.println("1.读取指定的excel文件内容的信息");

                System.out.println("2.向excel文件中写入内容");

                System.out.println("3.修改excel文件");// 添加信息

                System.out.println("5.退出");

                System.out.println("请选择：");

                if (in.hasNextInt()) {

                    n = in.nextInt();

                } else {

                    System.out.println("输入的不是整数，请重新输入：");

                    continue;

                }

                switch (n) {

                case 1: {

                    // 读EXCEL

                    read = new Read();

                    read.readExcel("C:\\Users\\lenovo\\Desktop\\测试.xls");

                    break;

                }

                case 2: {

                    // 输出EXCEL

                    writeadd = new Writeadd();

                    File filewrite = new File("C:\\Users\\lenovo\\Desktop\\测试2.xls");

                    filewrite.createNewFile();

                    OutputStream os = new FileOutputStream(filewrite);

                    writeadd.writeExcel(os);

                    System.out.println("执行完成");

                    break;

                }

                case 3: {

                    // 修改EXCEL

                    update = new Update();

                    update.modifyExcel(new File("C:\\Users\\lenovo\\Desktop\\测试.xls"),

                            new File("C:\\Users\\lenovo\\Desktop\\测试3.xls"));

                    System.out.println("执行完成");

                    break;

                }

                case 5:

                    break;

                default:

                    System.out.println("输入错误，请重新输入");

                    break;

                }

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

}

运行截图

java对excel文件内容读写修改操作

java对excel文件内容读写修改操作

java对excel文件内容读写修改操作

源代码：https://pan.baidu.com/s/1cXgc2woaq5xrpkN4Pqhbeg

相关文章