Tags: Tax Service System project
preface
Going back to our user module, we see that there are still two features left unfinished:
We didn’t learn anything about importing or exporting data from web pages into Excel files. However, excel is relatively common in Java, so there are components for us to use
There are two mainstream toolkits for using Excel in JAVA
- JXL
- POI
This time we mainly learn POI operation Excel.
JXL has a bug that only works with 03 or earlier versions of Excel, while POI can work with 97-07 versions.
POI basis
Object oriented look at Excel
First of all, if you want to use Java to manipulate Excel, you must specify excel related content with objects. Let’s see what Excel consists of:
POI looks at it this way:
- The entire Excel is called a workbook
- Many tables can be created under a workbook, called worksheets
- The worksheet has very many rows
- Rows can be subdivided into cells [specify row columns can be positioned anywhere in the worksheet]
After giving us a thorough analysis, we find that there is a subordinate relationship between them:
- Worksheets are subordinate to workbooks
- Rows are subordinate to the worksheet
- Cells are subordinate to rows
Procedures for Operating Excel
Import the POI development package:
- Poi – ooxml – 3.10.1-20140818. The jar.
- Poi – ooxml – schemas – 3.10.1-20140818. The jar,
- And copy xmlBeans-2.6.0.jar in ooxmL-lib, dom4J-1.6.1.jar (dom4j usually exists when the project is imported).
- Poi – ooxml – 3.11-20141221. The jar
/** * There are only 4 steps to Excel using POI1: ** Create/read workbooks * Create/read worksheets * Create/read rows * Create/read cells ** ** /
Copy the codeCreate Excel and write data
@Test
public void testWrite(a) throws IOException {
// Create a workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// Create a worksheet
HSSFSheet sheet = workbook.createSheet("I'm the new worksheet.");
// Create row, coordinates start at 0, I create row 3
HSSFRow row = sheet.createRow(2);
// Create a cell with coordinates starting at 0, so row 3 and column 3
HSSFCell cell = row.createCell(2);
// Write data to cells
cell.setCellValue("helloWorld");
// Write the workbook to hard disk
FileOutputStream outputStream = new FileOutputStream("C:\\ workbook.xls");
workbook.write(outputStream);
/ / close the flow
workbook.close();
outputStream.close();
}
Copy the code
Read Excel data
@Test
public void testRead(a) throws IOException {
// Get input stream, read Excel data
FileInputStream inputStream = new FileInputStream("C:\\ workbook.xls");
// Create a workbook
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
// Get the worksheet
HSSFSheet sheet = workbook.getSheetAt(0);
/ / line
HSSFRow row = sheet.getRow(2);
// Get the cell
HSSFCell cell = row.getCell(2);
// Get the cell data
String cellValue = cell.getStringCellValue();
System.out.println(cellValue);
}
Copy the code
Version 03 and 07
- Version 03 uses a class called HSSFWorkbook to manipulate Excel data in version 03
- Version 07 uses a class called XSSFWorkbook to manipulate Excel data in 2007
In fact, their methods are the same, just different classes. And which object to use, we can determine which object to create based on the suffix.
@Test
public void testRead03And07Excel(a) throws Exception {
String fileName = "D: \ \ itcast \ \ test. XLSX." ";
if(fileName.matches("^. + \ \. (? i)((xls)|(xlsx))$")) {// Check if it is an Excel document
boolean is03Excel = fileName.matches("^. + \ \. (? i)(xls)$");
FileInputStream inputStream = new FileInputStream(fileName);
// read the workbook
Workbook workbook = is03Excel ?new HSSFWorkbook(inputStream):new XSSFWorkbook(inputStream);
// select * from worksheet 1
Sheet sheet = workbook.getSheetAt(0);
//3. Read line 3
Row row = sheet.getRow(2);
//4. Read row 3 column 3
Cell cell = row.getCell(2);
System.out.println("The contents of the cell in row 3, column 3 are:"+ cell.getStringCellValue()); workbook.close(); inputStream.close(); }}Copy the codeExcel style
Back to our requirements, Excel should look good when we export data using POI. Similar to the following template:
In the POI, you can use formatting objects to format Excel documents; That is, styling excel content.
The main formatting objects used in POI are:
- Merged cell
- Set the cell style
- Set the cell font
- In the middle
- Background color and so on
The style object of the POI is obviously property workbook. Apply to worksheets
Merged cell
Belongs to a workbook and applies to a worksheet
The merged cell object is created with four parameters:
- Start position of row
- End position of line
- The starting position of the column
- The end position of the column
@Test
public void testCellRange(a) throws IOException {
// Create a workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// Create merged cell objects, starting from row 6 through 10, starting from column 6 through 10
CellRangeAddress cellRangeAddress = new CellRangeAddress(5.9.5.9);
// Create a worksheet
HSSFSheet sheet = workbook.createSheet("I'm the new worksheet.");
// Apply to the worksheet
sheet.addMergedRegion(cellRangeAddress);
// Create row, coordinates start at 0, I create row 6
HSSFRow row = sheet.createRow(5);
// Create a cell with coordinates starting from 0, so row 6 and column 6
HSSFCell cell = row.createCell(5);
// Write data to cells
cell.setCellValue("helloWorld");
// Write the workbook to hard disk
FileOutputStream outputStream = new FileOutputStream("C:\\ workbook.xls");
workbook.write(outputStream);
/ / close the flow
workbook.close();
outputStream.close();
}
Copy the code
Set the cell style
As you can see in the figure above, we have implemented merging cells, but generally we set the font to be centered, font size, and so on. The POI also provides corresponding objects for us to implement:
Set up in the middle
The style belongs to the workbook and applies to cells:
@Test
public void test(a) throws IOException {
// Create a workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// Create a style object
HSSFCellStyle style = workbook.createCellStyle();
// Create merged cell objects, starting from row 6 through 10, starting from column 6 through 10
CellRangeAddress cellRangeAddress = new CellRangeAddress(5.9.5.9);
// Set the horizontal center
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// Set vertical center
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Create a worksheet
HSSFSheet sheet = workbook.createSheet("I'm the new worksheet.");
sheet.addMergedRegion(cellRangeAddress);
// Create row, coordinates start at 0, I create row 6
HSSFRow row = sheet.createRow(5);
// Create a cell with coordinates starting from 0, so row 6 and column 6
HSSFCell cell = row.createCell(5);
// Write data to cells
cell.setCellValue("helloWorld");
// Set the style of the cell
cell.setCellStyle(style);
// Write the workbook to hard disk
FileOutputStream outputStream = new FileOutputStream("C:\\ workbook.xls");
workbook.write(outputStream);
/ / close the flow
workbook.close();
outputStream.close();
}
Copy the code
Set the font
Fonts belong to the workbook and are applied to styles.
@Test
public void test(a) throws IOException {
// Create a workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// Create a style object
HSSFCellStyle style = workbook.createCellStyle();
// Create merged cell objects, starting from row 6 through 10, starting from column 6 through 10
CellRangeAddress cellRangeAddress = new CellRangeAddress(5.9.5.9);
// Set the horizontal center
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// Set vertical center
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Create a font object
HSSFFont font = workbook.createFont();
// Set the font to bold
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// The font is 23 points
font.setFontHeightInPoints((short) 23);
// Set the font color
font.setColor(HSSFFont.COLOR_RED);
// The font applies to the style
style.setFont(font);
// Create a worksheet
HSSFSheet sheet = workbook.createSheet("I'm the new worksheet.");
sheet.addMergedRegion(cellRangeAddress);
// Create row, coordinates start at 0, I create row 6
HSSFRow row = sheet.createRow(5);
// Create a cell with coordinates starting from 0, so row 6 and column 6
HSSFCell cell = row.createCell(5);
// Write data to cells
cell.setCellValue("helloWorld");
// Set the style of the cell
cell.setCellStyle(style);
// Write the workbook to hard disk
FileOutputStream outputStream = new FileOutputStream("C:\\ workbook.xls");
workbook.write(outputStream);
/ / close the flow
workbook.close();
outputStream.close();
}
Copy the code
Implement export function #
Bind the button event to request the Action to handle the export and open an input box for the user to download
function doExportExcel() {
window.open("${basePath}user/user_exportExcel.action");
}
Copy the codeThe Action to deal with
/ * * * * * * * * * * * * export Excel * * * * * * * * * * * * * * * * * * * * * * * * * /
public void exportExcel(a) throws IOException {
// Find all the data in the list
List<User> list = userServiceImpl.findObjects();
// Export is simply an Excel file that the user can download
HttpServletResponse response = ServletActionContext.getResponse();
// Set the header and specify the name
response.setHeader("Content-Disposition"."attachment; filename=" + URLEncoder.encode("List display.xls"."UTF-8"));
// Specify the class capacity data to return
response.setContentType("application/x-execl");
ServletOutputStream outputStream = response.getOutputStream();
// Export Excel to Service layer
userServiceImpl.exportExcel(list, outputStream);
}
Copy the codeThe Service implementation
/** * the first line is dead, font size 11, centered, bold, merge cells * the second line is dead, bold * the third line starts, is the database list of data */
@Override
public void exportExcel(List<User> list, ServletOutputStream outputStream) {
/*********** Create workbook -- Style -- Font -- cell *************/
HSSFWorkbook workbook = new HSSFWorkbook();
// Merge cells in the first row
CellRangeAddress cellRangeAddress = new CellRangeAddress(0.0.0.4);
// Create the first line style.
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// Create a second line style.
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// Create the first line of font
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 23);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Create the second line font
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// The font applies to the style
cellStyle.setFont(font);
cellStyle2.setFont(font2);
/*********** Create a worksheet *************/
HSSFSheet sheet = workbook.createSheet("User List");
// The first row of cells applies to the worksheet
sheet.addMergedRegion(cellRangeAddress);
// Set the default column width
sheet.setDefaultColumnWidth(25);
/*********** Create a row *************/
/ / the first line
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue("User List");
// The second line of data is also written dead, we can use the array traversal
String[] data = {"Username"."Account"."Department"."Gender"."E-mail"};
HSSFRow row1 = sheet.createRow(1);
for (int i = 0; i < data.length; i++) {
HSSFCell cell1 = row1.createCell(i);
cell1.setCellValue(data[i]);
// Load the second line style
cell1.setCellStyle(cellStyle2);
}
/*************** Don't repeat rows and columns as you loop. Otherwise, an error will be reported ！！！！ * * * * * * * * * * * * * * * * * /
// The third row of data is the data stored in our database
if(list ! =null) {
int i=2;
for (User user : list) {
// Start at line 3
HSSFRow row2 = sheet.createRow(i);
HSSFCell row2Cel0 = row2.createCell(0);
row2Cel0.setCellValue(user.getName());
HSSFCell row2Cell = row2.createCell(1);
row2Cell.setCellValue(user.getAccount());
HSSFCell row2Cel2 = row2.createCell(2);
row2Cel2.setCellValue(user.getDept());
HSSFCell row2Cel3 = row2.createCell(3);
row2Cel3.setCellValue(user.isGender() ? "Male" : "Female");
HSSFCell row2Cel4 = row2.createCell(4); row2Cel4.setCellValue(user.getEmail()); i++; }}try {
// Write to outputSteam
workbook.write(outputStream);
workbook.close();
outputStream.close();
} catch(IOException e) { e.printStackTrace(); }}Copy the codeThe effect
Optimization of a
Let’s look at the following code. They all have to be centered except for the size of the font. Everything else is the same. It takes up so much code!!
// Create the first line style.
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// Create a second line style.
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// Create the first line of font
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 23);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Create the second line font
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// The font applies to the style
cellStyle.setFont(font);
cellStyle2.setFont(font2);
Copy the codeSo I extract a method to get the style
- A workbook is required to create styles
- Only the font size changes
/ * * *@paramWorkbook currently uses workbook *@paramFontSize fontSize * * */
public HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints(fontSize);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
return cellStyle;
}
Copy the codeWhen used, the code is called like this:
HSSFCellStyle cellStyle = createStyle(workbook, (short) 24);
HSSFCellStyle cellStyle2 = createStyle(workbook, (short) 13);
Copy the codeOptimization of two
Our Service business layer seems to have too much code. This makes it inconvenient for us to maintain.
What I do is: extract the code into Utils methods and call the Service layer.
Implement import function
Now I have this Excel file, and I want to store the information in the database and display it in the browser
In fact, importing Excel is like uploading a file, but instead of saving the file on the server’s hard drive, you save it in a database and export it to the browser.
function doImportExcel() {
document.forms[0].action = "${basePath}user/user_importExcel.action";
document.forms[0].submit();
}
Copy the codeAction Encapsulates file upload
/ * * * * * * * * * * * * * upload Excel * * * * * * * * * * * * * * * * * * * * * * * * /
private File userExcel;
private String userExcelFileName;
private String userExcelContentType;
public void setUserExcel(File userExcel) {
this.userExcel = userExcel;
}
public void setUserExcelFileName(String userExcelFileName) {
this.userExcelFileName = userExcelFileName;
}
public void setUserExcelContentType(String userExcelContentType) {
this.userExcelContentType = userExcelContentType;
}
Copy the codeThe Action to deal with
Mainly determine whether to upload files. The Service layer
/ * * * * * * * * * * * * import Excel * * * * * * * * * * * * * * * * * * * * * * * * * /
public String importExcel(a) throws IOException {
// get the excel file
if(userExcel ! =null) {// Is it excel
if(userExcelFileName.matches("^. + \ \. (? i)((xls)|(xlsx))$")) {/ / 2, importuserServiceImpl.importExcel(userExcel, userExcelFileName); }}return "list";
}
Copy the codeUtils returns wrapped as a collection
public static List<User> importExcel(File userExcel, String userExcelFileName) {
try {
FileInputStream fileInputStream = new FileInputStream(userExcel);
boolean is03Excel = userExcelFileName.matches("^. + \ \. (? i)(xls)$");
// read the workbook
Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream) : new XSSFWorkbook(fileInputStream);
//2
Sheet sheet = workbook.getSheetAt(0);
//3
List<User> users = new ArrayList<>();
if (sheet.getPhysicalNumberOfRows() > 2) {
User user = null;
for (int k = 2; k < sheet.getPhysicalNumberOfRows(); k++) {
//4, read the cell
Row row = sheet.getRow(k);
user = new User();
/ / user name
Cell cell0 = row.getCell(0);
user.setName(cell0.getStringCellValue());
/ / account
Cell cell1 = row.getCell(1);
user.setAccount(cell1.getStringCellValue());
// Department
Cell cell2 = row.getCell(2);
user.setDept(cell2.getStringCellValue());
/ / gender
Cell cell3 = row.getCell(3);
user.setGender(cell3.getStringCellValue().equals("Male"));
/ / cell phone number
String mobile = "";
Cell cell4 = row.getCell(4);
try {
mobile = cell4.getStringCellValue();
} catch (Exception e) {
double dMobile = cell4.getNumericCellValue();
mobile = BigDecimal.valueOf(dMobile).toString();
}
user.setMobile(mobile);
// Email address
Cell cell5 = row.getCell(5);
user.setEmail(cell5.getStringCellValue());
/ / birthday
Cell cell6 = row.getCell(6);
if(cell6.getDateCellValue() ! =null) {
user.setBirthday(cell6.getDateCellValue());
}
// The default password is 123456
user.setPassword("123456");
// The default user status is valid
user.setState(User.USER_STATE_VALID);
users.add(user);
}
}
workbook.close();
fileInputStream.close();
return users;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
Copy the codeService call
public void importExcel(File userExcel, String userExcelFileName) {
List<User> users = ExcelUtils.importExcel(userExcel, userExcelFileName);
for(User user : users) { save(user); }}Copy the codeconclusion
- Import means that users upload an Excel file, we read the data of the Excel file, encapsulate it into objects, and store it in the database
- Exporting means writing our database data to Excel files for users to download
- Specify that our return type is Excel
- Worksheets belong to workbooks, rows belong to worksheets, and cells belong to rows
- In the POI component, we divided Excel files into two categories, version 03 and version 07, which correspond to different objects, but the API of both objects is the same. Therefore, we can determine which object to use by the suffix of the file
- Our Excel should have some style to look good. The POI also provides a corresponding API for us to modify the style
- Merge cells, subordinate to workbooks, applications, and worksheets
- Setting the center and font size are style specific. Belongs to workbooks, applications and cells.
If you find this article helpful, give the author a little encouragement
