Apache POI usage details

1: Introduction

Excel processing is often designed in development, such as exporting excel and importing excel into database. At present, there are two frameworks for operating excel, one is apache poi and the other is Java Excel

Apache POI Introduction is used Java Written by free open source cross platform Java API，Apache POI provide API to Java Program pair Microsoft Office(Excel,WORD,PowerPoint,Visio Format file read and write function. POI For“ Poor Obfuscation Implementation"Acronym for "poor fuzzy implementation".

Official homepage: http://poi.apache.org/index.html
API file: http://poi.apache.org/apidocs/index.html

Java Excel Is an open source project through it Java Developers can read Excel Contents of the file, creating a new Excel File, update existing Excel File. jxl Because of its small and easy-to-use characteristics, Has gradually replaced POI-excel Status of, Become more and more java Developer generation excel File preferences.

2: Classes commonly used by Apache POI

• HSSF - provides the function of reading and writing files in Microsoft Excel XLS format.
• XSSF - provides the function of reading and writing Microsoft Excel OOXML XLSX format files.
• HWPF - provides the function of reading and writing files in Microsoft Word DOC97 format.
• XWPF - provides the function of reading and writing files in Microsoft Word DOC2003 format.
• HSLF - provides the ability to read and write files in Microsoft PowerPoint format.
• HDGF - provides the ability to read files in Microsoft Visio format.
• HPBF - provides the ability to read files in Microsoft Publisher format.
• HSMF - provides the ability to read files in Microsoft Outlook format

In the development, we often use HSSF to operate Excel to process tabular data, but we don't often use it for others.

HSSF is the abbreviation of Horrible SpreadSheet Format. Through HSSF, you can read, write and modify Excel files with pure Java code. HSSF provides two types of API s for read operations: usermodel and eventusermodel, namely "user model" and "event user model".

Common classes and methods

• HSSF Workbook: a workbook that represents the entire document of an excel
  • HSSFWorkbook(); // Create a new workbook
  • HSSFWorkbook(InputStream inputStream); // Create a workbook associated with the input stream. You can package an excel file into a workbook
  • HSSFSheet createSheet(String sheetname); Create a new Sheet
  • HSSFSheet getSheet(String sheetName); Get Sheet by name
  • HSSFSheet getSheetAt(int index); // Get the Sheet through the index. The index starts from 0
  • HSSFCellStyle createCellStyle()； Create cell style
  • int getNumberOfSheets(); Get the number of sheet s
  • setActiveSheet(int index); Sets the default selected worksheet
  • write();
  • write(File newFile);
  • write(OutputStream stream);
• HSSFSheet: worksheet
  • HSSFRow createRow(int rownum); To create a new line, you need to specify the line number, which starts from 0
  • HSSFRow getRow(int index); Gets the specified row based on the index
  • int addMergedRegion(CellRangeAddress region); merge cell
  • CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol); Cell range is used to merge cells. You need to specify the first row, last row, first column and last column to merge.
  • autoSizeColumn(int column); Automatically adjust the width of the column to fit the content
  • getLastRowNum(); Get the index of the last row. If there is no row or only one row, return 0
  • setColumnWidth(int columnIndex, int width); Set the width of a column. Width = number of characters * 256. For example, the width of 20 characters is 20 * 25
• HSSFRow: row
  • HSSFCell createCell(int column); Create a new cell
  • HSSFCell setCell(shot index);
  • HSSFCell getCell(shot index);
  • setRowStyle(HSSFCellStyle style); Set row style
  • short getLastCellNum(); Get the last cell number. If the cell has the first start, lastCellNum is the number of columns
  • setHeightInPoints(float height); Sets the height of the row
• HSSFCell: cell
  • setCellValue(String value); Sets the value of the cell
  • setCellType(); Set the cell type, such as string, number, Boolean, etc
  • setCellStyle(); Set cell style
  • String getStringCellValue(); Gets the string value in the cell
  • setCellStyle(HSSFCellStyle style); Set cell styles, such as font, bold, formatting
  • setCellFormula(String formula); The calculation formula is set. The calculation result is used as the value of the cell. It also provides commonly used functions, such as sum(A1,C1), date function, string correlation function, CountIf and SumIf functions, random number function, etc
• HSSFCellStyle: cell style
  • setFont(Font font); Set font style for cells
  • setAlignment(HorizontalAlignment align); // Set horizontal alignment
  • setVerticalAlignment(VerticalAlignment align); // Set vertical alignment
  • setFillPattern(FillPatternType fp);
  • setFillForegroundColor(short bg); set foreground color
  • setFillBackgroundColor(short bg); Set background color
• HSSFFont: font
  • setColor(short color); // Set font color
  • setBold(boolean bold); // Set whether bold
  • setItalic(boolean italic); Set tilt
  • setUnderline(byte underline); Set underline
• HSSFName: name
  • HSSFDataFormat: date formatting
  • HSSFHeader: the header of the Sheet
  • Hssffolder: the tail of the Sheet
  • HSSFDateUtil: Date tool
  • HSSFPrintSetup: Print Setup
  • HSSFErrorConstants: error information table

Relationships in workbooks, worksheets, rows, and cells in Excel:

One Excel File corresponds to a workbook(HSSFWorkbook)，
One workbook There can be more than one sheet(HSSFSheet)form,
One sheet Is composed of multiple row(HSSFRow)form,
One row Is composed of multiple cell(HSSFCell)form

3: Basic example

Firstly, the dependency of apache poi is introduced

<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi</artifactId>  
    <version>3.8</version>  
</dependency>

Example 1: generate an Excel file on the desktop

public static void createExcel() throws IOException{
	// Get desktop path
	FileSystemView fsv = FileSystemView.getFileSystemView();
	String desktop = fsv.getHomeDirectory().getPath();
	String filePath = desktop + "/template.xls";
    File file = new File(filePath);
    OutputStream outputStream = new FileOutputStream(file);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue("id");
    row.createCell(1).setCellValue("order number");
    row.createCell(2).setCellValue("Order time ");
    row.createCell(3).setCellValue("number");
    row.createCell(4).setCellValue("Unit Price");
    row.createCell(5).setCellValue("Order amount");
    row.setHeightInPoints(30); // Sets the height of the row

    HSSFRow row1 = sheet.createRow(1);
    row1.createCell(0).setCellValue("1");
    row1.createCell(1).setCellValue("NO00001");

    // Date formatting
    HSSFCellStyle cellStyle2 = workbook.createCellStyle();
    HSSFCreationHelper creationHelper = workbook.getCreationHelper();
    cellStyle2.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
    sheet.setColumnWidth(2, 20 * 256); // Sets the width of the column

    HSSFCell cell2 = row1.createCell(2);
    cell2.setCellStyle(cellStyle2);
    cell2.setCellValue(new Date());

    row1.createCell(3).setCellValue(2);


    // Keep two decimal places
    HSSFCellStyle cellStyle3 = workbook.createCellStyle();
    cellStyle3.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
    HSSFCell cell4 = row1.createCell(4);
    cell4.setCellStyle(cellStyle3);
    cell4.setCellValue(29.5);

    // Currency formatting
    HSSFCellStyle cellStyle4 = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setFontName("Chinese block letters");
    font.setFontHeightInPoints((short)15);
    font.setColor(HSSFColor.RED.index);
    cellStyle4.setFont(font);

    HSSFCell cell5 = row1.createCell(5);
    cell5.setCellFormula("D2*E2");  // Set calculation formula

    // Gets the value of the calculation formula
    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
    cell5 = e.evaluateInCell(cell5);
    System.out.println(cell5.getNumericCellValue());

    workbook.setActiveSheet(0);
    workbook.write(outputStream);
    outputStream.close();

}

Example 2: reading Excel and parsing data

public static void readExcel() throws IOException{
	FileSystemView fsv = FileSystemView.getFileSystemView();
	String desktop = fsv.getHomeDirectory().getPath();
	String filePath = desktop + "/template.xls";
    FileInputStream fileInputStream = new FileInputStream(filePath);
    BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
    POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
    HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
    HSSFSheet sheet = workbook.getSheet("Sheet1");

    int lastRowIndex = sheet.getLastRowNum();
    System.out.println(lastRowIndex);
    for (int i = 0; i <= lastRowIndex; i++) {
        HSSFRow row = sheet.getRow(i);
        if (row == null) { break; }

        short lastCellNum = row.getLastCellNum();
        for (int j = 0; j < lastCellNum; j++) {
            String cellValue = row.getCell(j).getStringCellValue();
            System.out.println(cellValue);
        }
    }
    bufferedInputStream.close();
}

4: Exporting and importing Excel from Java Web

1. Export example

@SuppressWarnings("resource")
@RequestMapping("/export") 
public void exportExcel(HttpServletResponse response, HttpSession session, String name) throws Exception {  
	
	String[] tableHeaders = {"id", "full name", "Age"}; 
	
	HSSFWorkbook workbook = new HSSFWorkbook();
	HSSFSheet sheet = workbook.createSheet("Sheet1");
	HSSFCellStyle cellStyle = workbook.createCellStyle();    
	cellStyle.setAlignment(HorizontalAlignment.CENTER);  
	cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	
	Font font = workbook.createFont();  
	font.setColor(HSSFColor.RED.index);  
	font.setBold(true);
	cellStyle.setFont(font);
	
	// Merge the three cells in the first row
	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
	HSSFRow row = sheet.createRow(0);
	HSSFCell beginCell = row.createCell(0);
	beginCell.setCellValue("mail list");	
	beginCell.setCellStyle(cellStyle);
	
	row = sheet.createRow(1);
	// Create header
	for (int i = 0; i < tableHeaders.length; i++) {
		HSSFCell cell = row.createCell(i);
		cell.setCellValue(tableHeaders[i]);
		cell.setCellStyle(cellStyle);    
	}
	
	List<User> users = new ArrayList<>();
	users.add(new User(1L, "Zhang San", 20));
	users.add(new User(2L, "Li Si", 21));
	users.add(new User(3L, "Wang Wu", 22));
	
	for (int i = 0; i < users.size(); i++) {
		row = sheet.createRow(i + 2);
		
		User user = users.get(i);
		row.createCell(0).setCellValue(user.getId());    
		row.createCell(1).setCellValue(user.getName());    
		row.createCell(2).setCellValue(user.getAge());    
	}
	
	OutputStream outputStream = response.getOutputStream(); 
	response.reset(); 
	response.setContentType("application/vnd.ms-excel");    
	response.setHeader("Content-disposition", "attachment;filename=template.xls");      

	workbook.write(outputStream);
	outputStream.flush();    
	outputStream.close();
}

2. Import example

1. To upload files using spring MVC, you need to use commons file upload

<dependency>
	<groupId>commons-fileupload</groupId>
	<artifactId>commons-fileupload</artifactId>
	<version>1.3</version>
</dependency>

2. You need to configure multipartResolver in the spring configuration file

<bean name="multipartResolver"  
    	class="org.springframework.web.multipart.commons.CommonsMultipartResolver">  
	<property name="defaultEncoding" value="UTF-8" />
</bean>

3,index.jsp

<a href="/Spring-Mybatis-Druid/user/export">export</a> <br/>

<form action="/Spring-Mybatis-Druid/user/import" enctype="multipart/form-data" method="post">
	<input type="file" name="file"/> 
	<input type="submit" value="Import Excel">
</form>

4. Analyze the uploaded xls file

@SuppressWarnings("resource")
@RequestMapping("/import")
public void importExcel(@RequestParam("file") MultipartFile file) throws Exception{
	InputStream inputStream = file.getInputStream();
	BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
	POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
	HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
	//HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
	HSSFSheet sheet = workbook.getSheetAt(0);
	
	int lastRowNum = sheet.getLastRowNum();
	for (int i = 2; i <= lastRowNum; i++) {
		HSSFRow row = sheet.getRow(i);
		int id = (int) row.getCell(0).getNumericCellValue();
		String name = row.getCell(1).getStringCellValue();
		int age = (int) row.getCell(2).getNumericCellValue();
		
		System.out.println(id + "-" + name + "-" + age);
	}
}