java读取本地excel

作者:原创时间:2022-03-30
文档

很多朋友都想知道java本地excel是什么?怎么读取?下面就一起来了解一下吧~

java excel api:是一开放源码项目,通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。使用该API非Windows操作系统也可以通过纯Java应用来处理Excel数据表。因为它是使用Java编写的,所以在Web应用中可以通过JSP、Servlet来调用API实现对Excel数据表的访问。

 Java通过jexcelapi包操作excel文件:

//in ExcelHandle
    import jxl.*;
    import jxl.format.UnderlineStyle;
    import jxl.write.*;
    import jxl.write.Number;
    import jxl.write.Boolean;
    import jxl.Cell;
    import java.io.*;
    public class ExcelHandle
    {
        public ExcelHandle()
        {
        }
        /***读取Excel*/
        public static 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();
            }
        }
        /**输出Excel*/
        public static void writeExcel(OutputStream os)
        {
            try
            {
       /** 只能通过API提供的 工厂方法来创建Workbook,而不能使用WritableWorkbook的构造函数,因为类WritableWorkbook的构造函数为 protected类型:方法一:直接从目标文件中读取 WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));方法 二:如下实例所示 将WritableWorkbook直接写入到输出流*/
                WritableWorkbook wwb = Workbook.createWorkbook(os);
                //创建Excel工作表 指定名称和位置
                WritableSheet ws = wwb.createSheet("Test Sheet 1",0);
                /**************往工作表中添加数据*****************/
                //1.添加Label对象
                Label label = new Label(0,0,"测试");
                ws.addCell(label);
                //添加带有字型Formatting对象
                WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
                WritableCellFormat wcf = new WritableCellFormat(wf);
                Label labelcf = new Label(1,0,"this is a label test",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,"Ok",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("f:\\1.png");
                WritableImage wimage = new WritableImage(0,4,6,17,image);
                ws.addImage(wimage);
                //7.写入工作表
                wwb.write();
                wwb.close();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }
        /** 将file1拷贝后,进行修改并创建输出对象file2
         * 单元格原有的格式化修饰不能去掉,但仍可将新的单元格修饰加上去,
         * 以使单元格的内容以不同的形式表现
         */
        public static 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(0,0,"人物(新)",wcfFC);
                    ws.addCell(labelCF);
                  //Label label = (Label)wc;
                  //label.setString("被修改");
                }
              wwb.write();
                wwb.close();
                rwb.close();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }
        //测试
        public static void main(String args[])
        {
            try
            {
                //读EXCEL
            ExcelHandle.readExcel("F:/红楼人物.xls");
                //输出EXCEL
            File filewrite=new File("F:/红楼人物2.xls");
            filewrite.createNewFile();
            OutputStream os=new FileOutputStream(filewrite);
            ExcelHandle.writeExcel(os);
                //修改EXCEL
            ExcelHandle.modifyExcel(new File("F:/红楼人物.xls"), new File("F:/红楼人物3.xls"));
            }
            catch(Exception e)
            {
            e.printStackTrace();
            }
        }
    }

以上就是小编今天的分享,希望能够帮到大家。

显示全文
java decaf java调python java decode java decodeuri java delegate java调用c++接口 java调用c++ java获取绝对值 java delete java调用asmx接口 java dependency java dev java fastdfs java development java dict java fastjson java feign java divide java field java field获取值 java读取json java dataset java规则引擎 java dataframe java表单校验 java获得当前路径 java dashboard java获取随机数 java daemon java获取随机字符串 java ctp java获取运行时间 java cssbox java获取路径 java获取请求域名 java csrf java获取视频时长 java crontab java获取网络时间 java crash