Maven坐标
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<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();
}
}
推荐文档:
- https://blog.csdn.net/gaoweijiegwj/article/details/77198828