Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "how to achieve batch import and export of files by Java". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Introduction

Java to achieve the import and export of files in the database, at present in most systems is a relatively common function, today to write a small demo to understand its principle, students who have not come into contact with can also take a look at the reference.

At present, the main import and export technologies I have come into contact with are POI and iReport,poi, which are mainly imported into the database in batches as some data, and iReport for report export. In addition, jxl is similar to poi, but it doesn't seem to be new for a long time, and office after 2007 doesn't seem to support it, so I won't talk about it here.

2. Detailed explanation of the use of POI

2.1What is Apache POI?

Apache POI is an open source library of the Apache Software Foundation. POI provides API to Java programs to read and write files in Microsoft Office format.

2.2 jar package import for POI

The maven project is used in this explanation, and poi-3.14 and poi-ooxml-3.14 are used in the jar package version. The latest version is 3.16. Because the relevant api has been updated after 3.15, some operations may be different, please pay attention.

Org.apache.poi poi 3.14 org.apache.poi poi-ooxml 3.14

2.3 API explanation of POI

2.3.1 structure

HSSF-provides the ability to read and write files in Microsoft Excel format.

XSSF-provides the ability to read and write files in Microsoft Excel OOXML format.

HWPF-provides the ability to read and write files in Microsoft Word format.

HSLF-provides the ability to read and write files in Microsoft PowerPoint format.

HDGF-provides the ability to read and write files in Microsoft Visio format.

2.3.2 object

This article mainly introduces HSSF and XSSF two components, simply said that HSSF is used to operate Office 2007 version of pre-excel.xls files, XSSF is used to operate Office 2007 version of excel.xlsx files, note that the two suffixes are not the same.

HSSF is in the org.apache.poi.hssf.usermodel package. It implements the Workbook interface for .xls format in Excel files

Common components:

HSSFWorkbook: document object for excel

HSSFSheet: excel's form

HSSFRow: the line of excel

HSSFCell: the lattice unit of excel

HSSFFont: excel font

HSSFDataFormat: date format

HSSFHeader: sheet header

HSSFFooter: sheet tail (the effect can only be seen when printing)

Style:

HSSFCellStyle: cell styl

Auxiliary operations include:

HSSFDateUtil: date

HSSFPrintSetup: printing

HSSFErrorConstants: error message table

XSSF is in the org.apache.xssf.usemodel package and implements the Workbook interface for .xlsx format in Excel files

Common components:

XSSFWorkbook: document object for excel

XSSFSheet: excel's form

XSSFRow: the line of excel

XSSFCell: the lattice unit of excel

XSSFFont: excel font

XSSFDataFormat: date format

Similar to HSSF

2.3.3 Field type description common to the two components

In fact, the two components are for the two formats of excel, and most of the operations are the same.

2.3.4 procedure

Take HSSF as an example, the XSSF operation is the same.

First of all, understand the organizational form of an Excel file. An Excel file corresponds to a workbook (HSSFWorkbook), a workbook can have multiple sheet (HSSFSheet), a sheet is composed of multiple row (HSSFRow), and a row is composed of multiple cell (HSSFCell).

1. Open or create a "Excel file object" with HSSFWorkbook

2. Use HSSFWorkbook object to return or create Sheet object

3. Return the row object with the Sheet object and get the Cell object with the row object

4. Read and write Cell objects.

3. Code operation

3.1 effect drawing

As a rule, look at the effect picture before pasting the code.

One for each of the two formats of the Excel file:

Code structure:

After import: (I imported it twice without verification)

Export effect:

3.2 detailed code explanation

Here I take Spring+SpringMVC+Mybatis as the basis, expand: SpringBoot+Mybatis multi-module (module) project building tutorial

Controller:

Package com.allan.controller; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.poi.ss.formula.functions.Mode; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody Import org.springframework.web.multipart.MultipartFile; import org.springframework.web.servlet.ModelAndView; import com.allan.pojo.Student; import com.allan.service.StudentService; / * @ grandfather of author Shop * * / @ Controller public class StudentController {@ Autowired private StudentService studentService / * bulk import form data * * @ param request * @ param myfile * @ return * / @ RequestMapping (value= "/ importExcel", method=RequestMethod.POST) public String importExcel (@ RequestParam ("myfile") MultipartFile myFile) {ModelAndView modelAndView = new ModelAndView (); try {Integer num = studentService.importExcel (myFile) } catch (Exception e) {modelAndView.addObject ("msg", e.getMessage ()); return "index";} modelAndView.addObject ("msg", "data imported successfully"); return "index" } @ RequestMapping (value= "/ exportExcel", method=RequestMethod.GET) public void exportExcel (HttpServletResponse response) {try {studentService.exportExcel (response);} catch (Exception e) {e.printStackTrace ();}

Service

Package com.allan.service.impl; import java.io.OutputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRow Import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service Import org.springframework.web.multipart.MultipartFile; import com.allan.mapper.StudentMapper; import com.allan.pojo.Student; import com.allan.service.StudentService; / * @ grandfather of author Shop * * / @ Service public class StudentServiceImpl implements StudentService {private final static String XLS = "xls"; private final static String XLSX = "xlsx"; @ Autowired private StudentMapper studentMapper / * Import Excel Compatible with xls and xlsx * / @ SuppressWarnings ("resource") public Integer importExcel (MultipartFile myFile) throws Exception {/ / 1, open or create "Excel file object" / 2 with HSSFWorkbook, return or create Sheet object / 3 with HSSFWorkbook object, return line object with Sheet object Use the row object to get the Cell object / 4, read and write the Cell object. / / get the file name Workbook workbook = null; String fileName = myFile.getOriginalFilename (); if (fileName.endsWith (XLS)) {/ / 2003 workbook = new HSSFWorkbook (myFile.getInputStream ());} else if (fileName.endsWith (XLSX)) {/ / 2007 workbook = new XSSFWorkbook (myFile.getInputStream ()) } else {throw new Exception ("file is not an Excel file");} Sheet sheet = workbook.getSheet ("Sheet1"); int rows= sheet.getLastRowNum (); / / refers to the number of lines, the total number of lines + if (rows==0) {throw new Exception ("Please fill in the data");} for (int I = 1; I)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.