poi实现Excel文件的导入导出

ゞ浴缸里的玫瑰 2022-03-29 03:48 503阅读 0赞

## Maven坐标 ##

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

## 行数限制 ##

*  HSSFWorkbook 最多导出65535条数据（poi-3.17.jar）
 *  XSSFWorkbook 导出行数不受限制（poi-ooxml-3.17.jar），Excel2007（以上）的版本

## 源码 ##

### 实体类 ###

public class TblVoucher { 
        private String identiCode;
        private String ticketCode;
        private String ticketPwd;
        private BigDecimal faceValue;
        private Date createTime;
        private Date endTime;
        private String isValid;
    
        public String getIdentiCode() { 
            return identiCode;
        }
    
        public void setIdentiCode(String identiCode) { 
            this.identiCode = identiCode;
        }
    
        public String getTicketCode() { 
            return ticketCode;
        }
    
        public void setTicketCode(String ticketCode) { 
            this.ticketCode = ticketCode;
        }
    
        public String getTicketPwd() { 
            return ticketPwd;
        }
    
        public void setTicketPwd(String ticketPwd) { 
            this.ticketPwd = ticketPwd;
        }
    
        public BigDecimal getFaceValue() { 
            return faceValue;
        }
    
        public void setFaceValue(BigDecimal faceValue) { 
            this.faceValue = faceValue;
        }
    
        public Date getCreateTime() { 
            return createTime;
        }
    
        public void setCreateTime(Date createTime) { 
            this.createTime = createTime;
        }
    
        public Date getEndTime() { 
            return endTime;
        }
    
        public void setEndTime(Date endTime) { 
            this.endTime = endTime;
        }
    
        public String getIsValid() { 
            return isValid;
        }
    
        public void setIsValid(String isValid) { 
            this.isValid = isValid;
        }
    }

### 读取Excel工具类 ###

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.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import sto.customerapp.materialquery.entity.TblVoucher;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    public class ReadExcelUtils { 
    
    	// XSSF -- 提供读写Microsoft Excel OOXML格式档案的功能
    	// XSSFWorkbook:是操作Excel2007（以上）的版本，扩展名是.xlsx
    	public static List<TblVoucher> read2007Xlsx(InputStream is) throws IOException { 
    		XSSFWorkbook xWorkbook = new XSSFWorkbook(is);
    		List<TblVoucher> list = new ArrayList<>();
    
    		// Read the Sheet
    		XSSFSheet xssfSheet = xWorkbook.getSheetAt(0);
    		// Read the Row
    		for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { 
    			XSSFRow xssfRow = xssfSheet.getRow(rowNum);
    			if (xssfRow != null) { 
    				TblVoucher voucher = new TblVoucher();
    				voucher.setIdentiCode(xssfRow.getCell(0).getStringCellValue());// 验证码
    				voucher.setTicketCode(xssfRow.getCell(1).getStringCellValue());// 券号码
    				voucher.setTicketPwd(xssfRow.getCell(2).getStringCellValue());// 券密码
    				voucher.setFaceValue(BigDecimal.valueOf(xssfRow.getCell(3).getNumericCellValue()));// 面值
    				voucher.setCreateTime(new Date());// 生成时间
    				voucher.setEndTime(xssfRow.getCell(4).getDateCellValue());// 截止有效时间
    				voucher.setIsValid("1");// 有效
    				list.add(voucher);
    			}
    		}
    
    		xWorkbook.close();
    		return list;
    	}
    
    	// HSSF -- 提供读写Microsoft Excel格式档案的功能。
    	// HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
    	public static List<TblVoucher> read2003Xls(InputStream is) throws IOException { 
    		HSSFWorkbook hWorkbook = new HSSFWorkbook(is);
    		List<TblVoucher> list = new ArrayList<>();
    
    		// Read the Sheet
    		HSSFSheet hssfSheet = hWorkbook.getSheetAt(0);
    		// Read the Row
    		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 
    			HSSFRow xssfRow = hssfSheet.getRow(rowNum);
    			if (xssfRow != null) { 
    				TblVoucher voucher = new TblVoucher();
    				voucher.setIdentiCode(xssfRow.getCell(0).getStringCellValue());// 验证码
    				voucher.setTicketCode(xssfRow.getCell(1).getStringCellValue());// 券号码
    				voucher.setTicketPwd(xssfRow.getCell(2).getStringCellValue());// 券密码
    				voucher.setFaceValue(BigDecimal.valueOf(xssfRow.getCell(3).getNumericCellValue()));// 面值
    				voucher.setCreateTime(new Date());// 生成时间
    				voucher.setIsValid("1");// 有效
    				list.add(voucher);
    			}
    		}
    
    		hWorkbook.close();
    		return list;
    	}
    }

### 导出Excel工具类 ###

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    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 sto.customerapp.materialquery.entity.TblVoucher;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    
    public class WriteExcelUtils { 
    	private static final String EXCEL_XLS = "xls";
    	private static final String EXCEL_XLSX = "xlsx";
    
    	public static void writeExcel(String[] titles, List<TblVoucher> list, String fullFileName) { 
    		Workbook workBook = null;
    		OutputStream out = null;
    		Sheet sheet = null;
    		Row row = null;
    
    		try { 
    			// 创建Excel文档
    			File file = new File(fullFileName);
    			if (!file.exists()) { 
    				// 判断父目录是否存在
    				if (!file.getParentFile().exists()) { 
    					file.getParentFile().mkdirs();// 多层目录创建
    				}
    				if (file.getName().endsWith(EXCEL_XLS)) {  // Excel 2003
    					workBook = new HSSFWorkbook();
    				} else if (file.getName().endsWith(EXCEL_XLSX)) {  // Excel // 2007/2010
    					workBook = new XSSFWorkbook();
    				}
    
    				// 创建sheet对象
    				sheet = (Sheet) workBook.createSheet("sheet1");
    				out = new FileOutputStream(fullFileName);
    				workBook.write(out);
    				out.flush();
    				out.close();
    			} else { 
    				if (file.getName().endsWith(EXCEL_XLS)) {  // Excel 2003
    					workBook = new HSSFWorkbook();
    				} else if (file.getName().endsWith(EXCEL_XLSX)) {  // Excel // 2007/2010
    					workBook = new XSSFWorkbook();
    				}
    			}
    
    			if (sheet == null) { 
    				sheet = workBook.createSheet("sheet1");
    			}
    
    			// 设置标题行
    			row = sheet.createRow(0);
    			for (int i = 0; i < titles.length; i++) { 
    				row.createCell(i).setCellValue(titles[i]);
    			}
    
    			for (int j = 1; j <= list.size(); j++) { 
    				row = sheet.createRow(j);
    				TblVoucher voucher = list.get(j - 1);
    				row.createCell(0).setCellValue(voucher.getIdentiCode());
    				row.createCell(1).setCellValue(voucher.getTicketCode());
    			}
    
    			// 创建文件输出流，准备输出电子表格
    			out = new FileOutputStream(fullFileName);
    			workBook.write(out);
    		} catch (Exception e) { 
    			e.printStackTrace();
    		} finally { 
    			try { 
    				if (out != null) { 
    					out.flush();
    					out.close();
    				}
    			} catch (IOException e) { 
    				e.printStackTrace();
    			}
    		}
    	}
    
    	public static void writeExcelDownload(String[] titles, List<TblVoucher> list, String filename,
    			HttpServletRequest request, HttpServletResponse response) throws IOException { 
    		@SuppressWarnings("resource")
    		Workbook wb = new XSSFWorkbook();
    		// 创建第一个sheet（页），并命名
    		Sheet sheet = wb.createSheet("Hello Sheet");
    		// 创建标题行
    		Row row = sheet.createRow(0);
    		for (int i = 0; i < titles.length; i++) { 
    			row.createCell(i).setCellValue(titles[i]);
    		}
    		for (int j = 1; j <= list.size(); j++) { 
    			row = sheet.createRow(j);
    			TblVoucher voucher = list.get(j - 1);
    			row.createCell(0).setCellValue(voucher.getIdentiCode());
    			row.createCell(1).setCellValue(voucher.getTicketCode());
    		}
    
    		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    		String currentTime = sdf.format(new Date());
    		response.setHeader("Content-disposition",
    				"attachment;filename=" + URLEncoder.encode(filename + currentTime + ".xlsx", "UTF-8"));
    		OutputStream ouputStream = response.getOutputStream();
    		wb.write(ouputStream);
    		ouputStream.flush();
    		ouputStream.close();
    	}
    }

--------------------

推荐文档：

1.  https://blog.csdn.net/gaoweijiegwj/article/details/77198828