Use Java to manipulate Excel files

  • 2020-04-01 02:17:23
  • OfStack

I have long wanted to study the method of using Java to operate Excel. Today I have nothing to do, so I have a little understanding of it. Using Java Excel operation, there is a open source stuff - JXL. Jar, can be downloaded to the (link: http://sourceforge.net/projects/jexcelapi/files/).

I. read the contents of Excel file



    public static String readExcel(File file)...{
        StringBuffer sb = new StringBuffer();

        Workbook wb = null;
        try ...{
            //Construct the Workbook object
            wb=Workbook.getWorkbook(file);
        } catch (BiffException e) ...{
            e.printStackTrace();
        } catch (IOException e) ...{
            e.printStackTrace();
        }

        if(wb==null)
            return null;

        //Once you have the Workbook object, you can use it to get the Sheet object
        Sheet[] sheet = wb.getSheets();

        if(sheet!=null&&sheet.length>0)...{
            //Loop through each sheet
            for(int i=0;i<sheet.length;i++)...{
                //Gets the number of rows in the current worksheet
                int rowNum = sheet[i].getRows();
                for(int j=0;j<rowNum;j++)...{
                    //Gets all the cells in the current row
                    Cell[] cells = sheet[i].getRow(j);
                    if(cells!=null&&cells.length>0)...{
                        //Loop through each cell
                        for(int k=0;k<cells.length;k++)...{
                            //Reads the value of the current cell
                            String cellValue = cells[k].getContents();
                            sb.append(cellValue+" ");
                        }
                    }
                    sb.append(" ");
                }
                sb.append(" ");
            }
        }
        //Finally, close the resource and free the memory
        wb.close();
        return sb.toString();
    }

Write Excel file
There are many formats, such as bold text content, some colors, etc., you can refer to the JXL API


    public static void writeExcel(String fileName)...{
        WritableWorkbook wwb = null;
        try ...{
            //You first create a writable Workbook object using the Workbook class's factory methods
            wwb = Workbook.createWorkbook(new File(fileName));
        } catch (IOException e) ...{
            e.printStackTrace();
        }
        if(wwb!=null)...{
            //Create a writable worksheet
            //The Workbook's createSheet method takes two arguments, the first being the name of the worksheet and the second the location of the worksheet in the Workbook
            WritableSheet ws = wwb.createSheet("sheet1", 0);

            //Let's start adding cells
            for(int i=0;i<10;i++)...{
                for(int j=0;j<5;j++)...{
                    //The important thing to note here is that in Excel, the first argument represents the column and the second represents the row
                    Label labelC = new Label(j, i, " This is the first "+(i+1)+" Ok, the first "+(j+1)+" column ");
                    try ...{
                        //Adds the generated cells to the worksheet
                        ws.addCell(labelC);
                    } catch (RowsExceededException e) ...{
                        e.printStackTrace();
                    } catch (WriteException e) ...{
                        e.printStackTrace();
                    }
                }
            }
            try ...{
                //Writes to a file from memory
                wwb.write();
                //Close the resource and free the memory
                wwb.close();
            } catch (IOException e) ...{
                e.printStackTrace();
            } catch (WriteException e) ...{
                e.printStackTrace();
            }
        }
    } 

Note: if you want to write to an existing Excel, you need to do the following:

 WritableWorkbook book = null;
        try...{
            //Excel access file
            Workbook wb = Workbook.getWorkbook(new File("D:/test.xls"));
            //Opens a copy of the file and specifies that the data be written back to the original file
            book = Workbook.createWorkbook(new File("D:/test.xls"), wb);

            //Add a worksheet
            WritableSheet sheet = book.getSheet("sheet1");
            sheet.addCell(new Label(8,3, " The first 3 Line first 8 I've added something "));

            //The following part of TODO is omitted

        }catch(Exception e)...{
            e.printStackTrace();
        }

Search for a keyword in an Excel file


    public static boolean searchKeyWord(File file,String keyWord)...{
        boolean res = false;

        Workbook wb = null;
        try ...{
            //Construct the Workbook object
            wb=Workbook.getWorkbook(file);
        } catch (BiffException e) ...{
            return res;
        } catch (IOException e) ...{
            return res;
        }

        if(wb==null)
            return res;

        //Once you have the Workbook object, you can use it to get the Sheet object
        Sheet[] sheet = wb.getSheets();

        boolean breakSheet = false;

        if(sheet!=null&&sheet.length>0)...{
            //Loop through each sheet
            for(int i=0;i<sheet.length;i++)...{
                if(breakSheet)
                    break;

                //Gets the number of rows in the current worksheet
                int rowNum = sheet[i].getRows();

                boolean breakRow = false;

                for(int j=0;j<rowNum;j++)...{
                    if(breakRow)
                        break;
                    //Gets all the cells in the current row
                    Cell[] cells = sheet[i].getRow(j);
                    if(cells!=null&&cells.length>0)...{
                        boolean breakCell = false;
                        //Loop through each cell
                        for(int k=0;k<cells.length;k++)...{
                            if(breakCell)
                                break;
                            //Reads the value of the current cell
                            String cellValue = cells[k].getContents();
                            if(cellValue==null)
                                continue;
                            if(cellValue.contains(keyWord))...{
                                res = true;
                                breakCell = true;
                                breakRow = true;
                                breakSheet = true;
                            }
                        }
                    }
                }
            }
        }
        //Finally, close the resource and free the memory
        wb.close();

        return res;
    }

4. Insert picture icon into Excel
The implementation of inserting images is easy, see the following code:

 
    public static void insertImg(WritableSheet dataSheet, int col, int row, int width,
            int height, File imgFile)...{
        WritableImage img = new WritableImage(col, row, width, height, imgFile);
        dataSheet.addImage(img);
    } 

The comments of the above code are already clear, and probably need not be explained. We can use the following program to verify:

   try ...{
            //Create a workbook
            WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls"));
            //The worksheet to be inserted
            WritableSheet imgSheet = workbook.createSheet("Images",0);
            //The image file to insert
            File imgFile = new File("D:/1.png");
            //The image is inserted into the first cell in the second row, each six cells long and wide
            insertImg(imgSheet,0,1,6,6,imgFile);
            workbook.write();
            workbook.close();
        } catch (IOException e) ...{
            e.printStackTrace();
        } catch (WriteException e) ...{
            e.printStackTrace();
        }

But JXL only supports PNG images, not JPG or GIF

Insert header and footer
The general header footer is divided into three parts, left, middle, right three parts, using the following code can be inserted into the header footer


   
    public static void setHeader(WritableSheet dataSheet,String left,String center,String right)...{
        HeaderFooter hf = new HeaderFooter();
        hf.getLeft().append(left);
        hf.getCentre().append(center);
        hf.getRight().append(right);
        //Join the header
        dataSheet.getSettings().setHeader(hf);
        //Join the footer
        //dataSheet.getSettings().setFooter(hf);
    }

We can test this method with the following code:

    try ...{
            //Create a workbook
            WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls"));
            //The worksheet to be inserted
            WritableSheet dataSheet = workbook.createSheet(" Join the header ",0);
            ExcelUtils.setHeader(dataSheet, "chb", "2007-03-06", " The first 1 page , A total of 3 page ");
            workbook.write();
            workbook.close();
        } catch (IOException e) ...{
            e.printStackTrace();
        } catch (WriteException e) ...{
            e.printStackTrace();
        }


Related articles: