I have wanted to study the method of using Java to operate Excel for a long time. I have nothing to do today, so I just learned a little bit and summarized it. To use java to operate Excel, there is an open source tool - jxl.jar, which can be downloaded from http://sourceforge.net/projects/jexcelapi/files/.
1. Read the contents of Excel file
Copy the code code as follows:
/** *//**Read the contents of the Excel file
* @param file file to be read
* @return
*/
public static String readExcel(File file)...{
StringBuffer sb = new StringBuffer();
Workbook wb = null;
try...{
//Construct Workbook object
wb=Workbook.getWorkbook(file);
} catch (BiffException e) ...{
e.printStackTrace();
} catch (IOException e) ...{
e.printStackTrace();
}
if(wb==null)
return null;
//After obtaining the Workbook object, you can get the Sheet (worksheet) object through it
Sheet[] sheet = wb.getSheets();
if(sheet!=null&&sheet.length>0)...{
//Loop through each worksheet
for(int i=0;i<sheet.length;i++)...{
//Get the number of rows in the current worksheet
int rowNum = sheet[i].getRows();
for(int j=0;j<rowNum;j++)...{
//Get all 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++)...{
//Read the value of the current cell
String cellValue = cells[k].getContents();
sb.append(cellValue+" ");
}
}
sb.append(" ");
}
sb.append(" ");
}
}
//Finally close the resource and release memory
wb.close();
return sb.toString();
}
2. Write to Excel file
There are many formats here, such as bolding text content, adding certain colors, etc. You can refer to jxl's API
Copy the code code as follows:
/**Generate an Excel file
* @param fileName The name of the Excel file to be generated
*/
public static void writeExcel(String fileName)...{
WritableWorkbook wwb = null;
try...{
//First use the factory method of the Workbook class to create a writable workbook (Workbook) object
wwb = Workbook.createWorkbook(new File(fileName));
} catch (IOException e) ...{
e.printStackTrace();
}
if(wwb!=null)...{
//Create a writable worksheet
//The createSheet method of Workbook has two parameters. The first is the name of the worksheet, and the second is the position of the worksheet in the workbook.
WritableSheet ws = wwb.createSheet("sheet1", 0);
//Start adding cells below
for(int i=0;i<10;i++)...{
for(int j=0;j<5;j++)...{
//What needs to be noted here is that in Excel, the first parameter represents the column and the second parameter represents the row.
Label labelC = new Label(j, i, "This is the "+(i+1)+" row, "+(j+1)+" column");
try...{
//Add the generated cells to the worksheet
ws.addCell(labelC);
} catch (RowsExceededException e) ...{
e.printStackTrace();
} catch (WriteException e) ...{
e.printStackTrace();
}
}
}
try...{
//Write from memory to file
wwb.write();
//Close resources and release memory
wwb.close();
} catch (IOException e) ...{
e.printStackTrace();
} catch (WriteException e) ...{
e.printStackTrace();
}
}
}
Note: If you want to write content to an existing Excel, you need to proceed as follows:
Copy the code code as follows:
WritableWorkbook book = null;
try...{
// Excel gets the file
Workbook wb = Workbook.getWorkbook(new File("D:/test.xls"));
// Open a copy of a file and write the specified data 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, "Something is added in row 3, column 8"));
//TODO The following parts are omitted
}catch(Exception e)...{
e.printStackTrace();
}
3. Find whether an Excel file contains a certain keyword
Copy the code code as follows:
/** Search whether a certain file contains a certain keyword
* @param file file to be searched
* @param keyWord The keyword to search for
* @return
*/
public static boolean searchKeyWord(File file,String keyWord)...{
boolean res = false;
Workbook wb = null;
try...{
//Construct Workbook object
wb=Workbook.getWorkbook(file);
} catch (BiffException e) ...{
return res;
} catch (IOException e) ...{
return res;
}
if(wb==null)
return res;
//After obtaining the Workbook object, you can get the Sheet (worksheet) object through it
Sheet[] sheet = wb.getSheets();
boolean breakSheet = false;
if(sheet!=null&&sheet.length>0)...{
//Loop through each worksheet
for(int i=0;i<sheet.length;i++)...{
if(breakSheet)
break;
//Get 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;
//Get all 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;
//Read 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 release memory
wb.close();
return res;
}
4. Insert picture icon into Excel
Inserting pictures is easy, see the following code:
Copy the code code as follows:
/** *//**Insert pictures into Excel
* @param dataSheet The worksheet to be inserted
* @param col The image starts from this column
* @param row The picture starts from this row
* @param width The number of columns occupied by the image
* @param height The number of rows occupied by the image
* @param imgFile The image file to be inserted
*/
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 very clear, and there is probably no need to explain it anymore. We can verify it with the following program:
Copy the code code as follows:
try...{
//Create a workbook
WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls"));
//Worksheet to be inserted
WritableSheet imgSheet = workbook.createSheet("Images",0);
//Image file to be inserted
File imgFile = new File("D:/1.png");
//The picture is inserted into the first cell of the second row, occupying six cells each in length and width.
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 pictures in png format, and neither jpg nor gif format is supported.
5. Insert header and footer
Generally, headers and footers are divided into three parts, left, center and right. You can use the following code to insert headers and footers.
Copy the code code as follows:
/** *//**Add header and footer to Excel
* @param dataSheet The worksheet to be added to the header
* @param left
* @param center
* @param right
*/
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);
//Add header
dataSheet.getSettings().setHeader(hf);
//Add footer
//dataSheet.getSettings().setFooter(hf);
}
We can test this method with the following code:
Copy the code code as follows:
try...{
//Create a workbook
WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls"));
//Worksheet to be inserted
WritableSheet dataSheet = workbook.createSheet("Add header",0);
ExcelUtils.setHeader(dataSheet, "chb", "2007-03-06", "Page 1 of 3");
workbook.write();
workbook.close();
} catch (IOException e) ...{
e.printStackTrace();
} catch (WriteException e) ...{
e.printStackTrace();
}