Small Hub read:
Using POI to import and export XLS files is relatively easy, there is a complete project code, you can clone down to study ha!
- Author: grandpa of small shop
- Cnblogs.com/laoyeye/p/6…
- Github.com/allanzhuo/m…
1, the introduction
Java file import and export database, currently in most of the system is more common function, today write a small demo to understand its principle, did not contact with the students can also look at the reference.
At present, THE import and export technologies I have come into contact with mainly include POI and iReport. POI is mainly used as some data to import into the database in batches, while iReport is used to export reports. In addition, JXL is similar to poI, but it seems to have not been updated for a long time, office seems not to support after 2007, here will not say.
2. Detailed explanation of POI usage
2.1 What is Apache POI?
Apache POI is the Apache Software Foundation’s open source functions library that provides apis for Java programs to read and write files in Microsoft Office format.
2.2 POI JAR Package Import
The maven project is used for this tutorial. The JAR package versions are PoI-3.14 and PoI-OOXML-3.14. The latest version is 3.16. Since relevant API has been updated after 3.15, some operations may be different, please note.
<! <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId> Poi-ooxml </artifactId> The < version > 3.14 < / version > < / dependency >Copy the code2.3 API explanation of POI
2.3.1 structure
HSSF – Provides the function of reading and writing Microsoft Excel files. XSSF – Provides the ability to read and write Microsoft Excel OOXML files. HWPF – Provides the function of reading and writing Microsoft Word files. HSLF – Allows you to read and write Microsoft PowerPoint files. HDGF – Provides the ability to read and write Microsoft Visio files.
2.3.2 object
XSSF is used to operate excel. XLS files before Office 2007, and XSSF is used to operate Excel. XLSX files after Office 2007. Note that the suffixes are different.
HSSF in org, apache poi. HSSF. Usermodel package. It implements the Workbook interface for the. XLS format in Excel files
Common components:
HSSFWorkbook: Excel document object HSSFSheet: Excel form HSSFRow: Excel row HSSFCell: Excel grid cell HSSFFont: Excel font HSSFDataFormat: date format HSSFHeader: sheet header HSSFFooter: sheet end (the result can be seen only when it is printed)
Style:
HSSFCellStyle: Cell style
Auxiliary operations include:
HSSFDateUtil: date HSSFPrintSetup: printing HSSFErrorConstants: Error information table
XSSF is in org.apache.xssf.usemodel package, and implements Workbook interface, used in Excel file. XLSX format
Common components:
XSSFWorkbook: Excel document object XSSFSheet: Excel form XSSFRow: Excel row XSSFCell: Excel grid cell XSSFFont: Excel font XSSFDataFormat: Date format
Similar to HSSF;
2.3.3 Description of field types common to two components
The two components are essentially two formats for Excel, and most of the operations are the same.
Cell Type Description CELL_TYPE_BLANK indicates a blank cell. CELL_TYPE_BOOLEAN indicates a Boolean cell (true or false). CELL_TYPE_ERROR indicates the error value CELL_TYPE_FORMULA in the cell Represents the result of a cell formula CELL_TYPE_NUMERIC means the numeric data for a cell CELL_TYPE_STRING means the numeric data for a cell string (text)Copy the code2.3.4 Procedure
The HSSF is used as an example. The XSSF operations are the same.
First, understand the organization form of an Excel file. An Excel file corresponds to a workbook(HSSFWorkbook). A workbook can be composed of multiple sheets (HSSFSheets). A sheet consists of multiple Rows (HSSFRow), and a row consists of multiple cells (HSSFCell).
1. Open or create “Excel file object” with HSSFWorkbook
2. Use HSSFWorkbook to return or create Sheet
3. Use the Sheet to return the row object, and use the row object to get the Cell object
4. Read and write the Cell object.
3. Code operation
3.1 rendering
As a rule, look at the renderings before Posting the code
Excel file in two formats:
Code structure:
After importing :(I imported it twice without checking it)
Export effect:
3.2 Code details
Here I am based on Spring+SpringMVC+Mybatis
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; /** ** @author ** / @controller public class StudentController {@autoWired private StudentService studentService; ** @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 import successful "); return "index"; } @RequestMapping(value="/exportExcel",method=RequestMethod.GET) public void exportExcel(HttpServletResponse response) { try { studentService.exportExcel(response); } catch (Exception e) { e.printStackTrace(); }}}Copy the codeService
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;
/**
*
* @author 小卖铺的老爷爷
*
*/
@Service
public class StudentServiceImpl implements StudentService{
private final static String XLS = "xls";
private final static String XLSX = "xlsx";
@Autowired
private StudentMapper studentMapper;
/**
* 导入Excel，兼容xls和xlsx
*/
@SuppressWarnings("resource")
public Integer importExcel(MultipartFile myFile) throws Exception {
// 1、用HSSFWorkbook打开或者创建“Excel文件对象”
//
// 2、用HSSFWorkbook对象返回或者创建Sheet对象
//
// 3、用Sheet对象返回行对象，用行对象得到Cell对象
//
// 4、对Cell对象读写。
//获得文件名
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("文件不是Excel文件");
}
Sheet sheet = workbook.getSheet("Sheet1");
int rows = sheet.getLastRowNum();// 指的行数，一共有多少行+
if(rows==0){
throw new Exception("请填写数据");
}
for (int i = 1; i <= rows+1; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
// **读取cell**
Student student = new Student();
//姓名
String name = getCellValue(row.getCell(0));
student.setName(name);
//班级
String classes = getCellValue(row.getCell(1));
student.setClasses(classes);
//分数
String scoreString = getCellValue(row.getCell(2));
if (!StringUtils.isEmpty(scoreString)) {
Integer score = Integer.parseInt(scoreString);
student.setScore(score);
}
//考试时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//小写的mm表示的是分钟
String dateString = getCellValue(row.getCell(3));
if (!StringUtils.isEmpty(dateString)) {
Date date=sdf.parse(dateString);
student.setTime(date);
}
studentMapper.insert(student);
}
}
return rows-1;
}
/**
* 获得Cell内容
*
* @param cell
* @return
*/
public String getCellValue(Cell cell) {
String value = "";
if (cell != null) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
value = cell.getNumericCellValue() + "";
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
}
return value.trim();
}
/**
* 导出excel文件
*/
public void exportExcel(HttpServletResponse response) throws Exception {
// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("Sheet1");
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell(0);
cell.setCellValue("姓名");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("班级");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("分数");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("时间");
cell.setCellStyle(style);
// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
List<Student> list = studentMapper.selectAll();
for (int i = 0; i < list.size(); i++){
row = sheet.createRow(i + 1);
Student stu = list.get(i);
// 第四步，创建单元格，并设置值
row.createCell(0).setCellValue(stu.getName());
row.createCell(1).setCellValue(stu.getClasses());
row.createCell(2).setCellValue(stu.getScore());
cell = row.createCell(3);
cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime()));
}
//第六步,输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
long filename = System.currentTimeMillis();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
String fileName = df.format(new Date());// new Date()为获取当前系统时间
response.setHeader("Content-disposition", "attachment; file);
response.setContentType("application/msexcel");
wb.write(output);
output.close();
}
}
Copy the code3.3 API supplement for Exporting Files
As you can see, the service code above is just a basic export.
In practical applications, exported Excel files often need to be read and printed, which requires setting typesetting and style of output Excel documents. The main operations include merging cells, setting cell style, setting font style, etc.
3.3.1 Cell merging
Use the addMergedRegion() method of HSSFSheet
public int addMergedRegion(CellRangeAddress region)
Copy the codeThe parameter CellRangeAddress represents the region to merge and is constructed as follows: start row, end row, start column, end column
CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
Copy the code3.3.2 Set the row height and column width of cells
HSSFSheet sheet=wb.createSheet(); sheet.setDefaultRowHeightInPoints(10); / / set the default column high sheet. SetDefaultColumnWidth (20); SetColumnWidth (cell.getColumnIndex(), 256 * 50); // Set the default column width.Copy the code3.3.3 Setting cell styles
Create HSSFCellStyle
HSSFCellStyle cellStyle=wkb.createCellStyle()
Copy the code2. Set styles
HSSFCellStyle cellstyle.setalignment (hssfcellstyle.align_justify); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); /* Set the cell filling method, and the foreground color and background color. If you want a foreground color or a background color, be sure to specify the fill method, regardless of the order. 2. If both foreground color and background color exist, the foreground color should be set first. 3. Foreground color is not font color. SetFillPattern (hssfCellStyle.diamonds); / / set the foreground color cellStyle. SetFillForegroundColor (HSSFColor. RED. The index); / / set the background color cellStyle. SetFillBackgroundColor (HSSFColor. LIGHT_YELLOW. Index); / / set the cell at the bottom of the frame and its style and color / / here only set a bottom frame, the left margin, right border, and top border similarly can set cellStyle setBorderBottom (HSSFCellStyle. BORDER_SLANTED_DASH_DOT); cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index); / / set data type date display cellStyle. SetDataFormat (HSSFDataFormat. GetBuiltinFormat (" m/d/yy h: mm "));Copy the code3. Apply styles to cells
cell.setCellStyle(cellStyle); // Apply styles to rows, but some styles only apply to cells row.setrowStyle (cellStyle);Copy the code3.3.4 Setting the Font Style
Create HSSFFont (createFont, HSSFWorkbook)
HSSFWorkbook wb=new HSSFWorkbook();
HSSFFont fontStyle=wb.createFont();
HSSFWorkbook wb=new HSSFWorkbook ();
Copy the code2, set various font styles
// set the fontStyle fontstyle.setfontname ("宋体"); / / set the font height fontStyle setFontHeightInPoints (20) (short); SetColor (hssfcolor.blue. Index); SetBoldweight (hssffont.boldweight_bold); // setItalic font. SetItalic (true); // Set the underline font-setunderline (hssffont.u_single);Copy the code3. Set font to cell style
// assign the font object to cellStyle object cellstyle.setfont (font); // assign the font object to cellStyle object cellstyle.setfont (font); // Apply the cell style to the cell cell.setcellStyle (cellStyle);Copy the codeAs you can see, exporting files using POI is still quite troublesome. Next time, I will introduce the method of irport for you.
That’s pretty much what the exported API is, and hopefully that was helpful in the end.
Source code address: github.com/allanzhuo/m…
(after)
Recommended reading
Great, this Java site, everything! https://markerhub.com
The UP master of this B station, speaks Java really good!
Too great! The latest edition of Java programming ideas can be viewed online!