使用 poi 实现 excel 导入、导出
excel 导入 注意事项：
1). excel 导入可以是表单提交， 也可以是 ajax提交。
2). form 表单需添加 enctype 属性，例如：enctype= “multipart/form-data”.
3). 在 spring 配置文件里，需添加 multipartResolver 解析器。e.g.:<!-- SpringMVC上传文件时，需要配置MultipartResolver处理器 --> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <property name="defaultEncoding" value="UTF-8" /> <!-- 指定所上传文件的总大小不能超过10485760000B。注意maxUploadSize属性的限制不是针对单个文件，而是所有文件的容量之和 --> <property name="maxUploadSize" value="10485760000"></property> <property name="maxInMemorySize" value="40960"></property> </bean>- excel 导出 注意事项：
1). excel 导出可以是 form 提交，也可以用 window.location.href = url， ajax返回类型不支持流类型。
2). excel 导出中文文件名乱码问题，先用 gb2312 编码，再用 iso_8859_1解码。
3). HSSF 结构的 workbook 只能导出 .xls文件，而 SXSSF 结构的 workbook 即可导出 .xls文件，也可以导出 .xlsx文件。 - 下面是代码事例：
1). controller 层代码如下：
package com.tarena.excel.controller;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.tarena.excel.service.StuService;
@RequestMapping("/excel")
@Controller
public class ExcelController {
@Autowired
private StuService service;
/**
* HSSF workbook 导出 .xls 文件
* @param response
*/
@RequestMapping("/export")
public void HSSFexportExcel(HttpServletResponse response) {
OutputStream out = null;
try {
// 设置excel文件名
String sql = "学生";
// 防止中文乱码
String string = new String(sql.getBytes("gb2312"), "iso_8859_1");
out = response.getOutputStream();
// 设置字符集编码
response.setCharacterEncoding("utf-8");
// 设置内容类型
response.setContentType("application/vnd.ms-excel");
// 设置消息头
response.setHeader("content-disposition", "attachment;filename=" + string + ".xls");
// 使用 HSSF结构导出 excel
HSSFWorkbook workbook = service.retrieveListByHSSF();
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* SXSSF workbook 既可以导出 .xls文件， 也可以导出 .xlsx文件。
* @param response
*/
@RequestMapping("/export2")
public void SXSSFexportExcel(HttpServletResponse response) {
OutputStream out = null;
try {
// 设置excel文件名
String sql = "学生";
// 防止中文乱码
String string = new String(sql.getBytes("gb2312"), "iso_8859_1");
out = response.getOutputStream();
// 设置字符集编码
response.setCharacterEncoding("utf-8");
// 设置内容类型
response.setContentType("application/vnd.ms-excel");
// 设置消息头
response.setHeader("content-disposition", "attachment;filename=" + string + ".xls");
// 使用 HSSF结构导出 excel
SXSSFWorkbook workbook = service.retrieveListBySXSSF();
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* excel 导入
* @param request
* @param response
* @param MultipartHttpServletRequest
*/
@RequestMapping(value = "/import")
public void importExcel(HttpServletRequest request, HttpServletResponse response,
HttpServletRequest MultipartHttpServletRequest) {
MultipartHttpServletRequest req = (MultipartHttpServletRequest) request;
// excel 是 输入框 类型为file 的name
MultipartFile file = req.getFile("excel");
String fileName = file.getOriginalFilename();
try {
if (fileName != null && fileName != "") {
InputStream is = file.getInputStream();
int[] rows = service.insertDataToDB(is);
System.out.println("rows.length:" + rows.length);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}2). service 层代码如下：
package com.tarena.excel.service;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import com.tarena.excel.dao.StuDao;
import com.tarena.excel.dto.StuEntity;
import com.tarena.excel.util.ReadExcel;
@Service
public class StuService {
@Autowired
private StuDao studao;
/**
* 给创建的工作傅赋值
* HSSF workbook
* @return
*/
public HSSFWorkbook retrieveListByHSSF(){
List<StuEntity> stus = studao.fetchListByDB();
HSSFWorkbook workbook = new HSSFWorkbook();
// create sheet
HSSFSheet sheet = workbook.createSheet("students");
// create row
HSSFRow titleRow = sheet.createRow(0);
// set cell style
HSSFCellStyle style = workbook.createCellStyle();
// set column width
sheet.setColumnWidth(3, 50*80);
// set alignment
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// set foregroundColor(背景颜色)
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// set border
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// set title cell value
HSSFCell cell1 = titleRow.createCell(0);
cell1.setCellStyle(style);
cell1.setCellValue("id");
HSSFCell cell2 = titleRow.createCell(1);
cell2.setCellValue("name");
cell2.setCellStyle(style);
HSSFCell cell3 = titleRow.createCell(2);
cell3.setCellValue("sex");
HSSFCell cell4 = titleRow.createCell(3);
cell4.setCellValue("num");
// set content cell value
if(!CollectionUtils.isEmpty(stus)){
for (int i = 0; i < stus.size(); i++) {
StuEntity stu = stus.get(i);
HSSFRow row = sheet.createRow(i+1);
HSSFCell id = row.createCell(0);
id.setCellValue(stu.getId());
HSSFCell name = row.createCell(1);
name.setCellValue(stu.getName());
HSSFCell sex = row.createCell(2);
sex.setCellValue(stu.getSex());
HSSFCell num = row.createCell(3);
num.setCellValue(stu.getNum());
}
}
return workbook;
}
/**
* SXSSF workbook
* @return
*/
public SXSSFWorkbook retrieveListBySXSSF(){
List<StuEntity> stus = studao.fetchListByDB();
SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("students");
Row titleRow = sheet.createRow(0);
CellStyle style = workbook.createCellStyle();
sheet.setColumnWidth(3, 50*80);
// set alignment
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// set foregroundColor
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// set border
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
Cell cell1 = titleRow.createCell(0);
cell1.setCellStyle(style);
cell1.setCellValue("id");
Cell cell2 = titleRow.createCell(1);
cell2.setCellValue("name");
cell2.setCellStyle(style);
Cell cell3 = titleRow.createCell(2);
cell3.setCellValue("sex");
Cell cell4 = titleRow.createCell(3);
cell4.setCellValue("num");
if(!CollectionUtils.isEmpty(stus)){
for (int i = 0; i < stus.size(); i++) {
StuEntity stu = stus.get(i);
Row row = sheet.createRow(i+1);
Cell id = row.createCell(0);
id.setCellValue(stu.getId());
Cell name = row.createCell(1);
name.setCellValue(stu.getName());
Cell sex = row.createCell(2);
sex.setCellValue(stu.getSex());
Cell num = row.createCell(3);
num.setCellValue(stu.getNum());
}
}
return workbook;
}
public int[] insertDataToDB(InputStream in) throws Exception{
ReadExcel readExcel = new ReadExcel();
List<Object[]> list = readExcel.readExcelContent(in);
int[] ints = studao.insert(list);
return ints;
}
}3). dao 层代码如下：
package com.tarena.excel.service;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import com.tarena.excel.dao.StuDao;
import com.tarena.excel.dto.StuEntity;
import com.tarena.excel.util.ReadExcel;
@Service
public class StuService {
@Autowired
private StuDao studao;
/**
* 给创建的工作傅赋值
* HSSF workbook
* @return
*/
public HSSFWorkbook retrieveListByHSSF(){
List<StuEntity> stus = studao.fetchListByDB();
HSSFWorkbook workbook = new HSSFWorkbook();
// create sheet
HSSFSheet sheet = workbook.createSheet("students");
// create row
HSSFRow titleRow = sheet.createRow(0);
// set cell style
HSSFCellStyle style = workbook.createCellStyle();
// set column width
sheet.setColumnWidth(3, 50*80);
// set alignment
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// set foregroundColor(背景颜色)
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// set border
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// set title cell value
HSSFCell cell1 = titleRow.createCell(0);
cell1.setCellStyle(style);
cell1.setCellValue("id");
HSSFCell cell2 = titleRow.createCell(1);
cell2.setCellValue("name");
cell2.setCellStyle(style);
HSSFCell cell3 = titleRow.createCell(2);
cell3.setCellValue("sex");
HSSFCell cell4 = titleRow.createCell(3);
cell4.setCellValue("num");
// set content cell value
if(!CollectionUtils.isEmpty(stus)){
for (int i = 0; i < stus.size(); i++) {
StuEntity stu = stus.get(i);
HSSFRow row = sheet.createRow(i+1);
HSSFCell id = row.createCell(0);
id.setCellValue(stu.getId());
HSSFCell name = row.createCell(1);
name.setCellValue(stu.getName());
HSSFCell sex = row.createCell(2);
sex.setCellValue(stu.getSex());
HSSFCell num = row.createCell(3);
num.setCellValue(stu.getNum());
}
}
return workbook;
}
/**
* SXSSF workbook
* @return
*/
public SXSSFWorkbook retrieveListBySXSSF(){
List<StuEntity> stus = studao.fetchListByDB();
SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("students");
Row titleRow = sheet.createRow(0);
CellStyle style = workbook.createCellStyle();
sheet.setColumnWidth(3, 50*80);
// set alignment
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// set foregroundColor
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// set border
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
Cell cell1 = titleRow.createCell(0);
cell1.setCellStyle(style);
cell1.setCellValue("id");
Cell cell2 = titleRow.createCell(1);
cell2.setCellValue("name");
cell2.setCellStyle(style);
Cell cell3 = titleRow.createCell(2);
cell3.setCellValue("sex");
Cell cell4 = titleRow.createCell(3);
cell4.setCellValue("num");
if(!CollectionUtils.isEmpty(stus)){
for (int i = 0; i < stus.size(); i++) {
StuEntity stu = stus.get(i);
Row row = sheet.createRow(i+1);
Cell id = row.createCell(0);
id.setCellValue(stu.getId());
Cell name = row.createCell(1);
name.setCellValue(stu.getName());
Cell sex = row.createCell(2);
sex.setCellValue(stu.getSex());
Cell num = row.createCell(3);
num.setCellValue(stu.getNum());
}
}
return workbook;
}
public int[] insertDataToDB(InputStream in) throws Exception{
ReadExcel readExcel = new ReadExcel();
List<Object[]> list = readExcel.readExcelContent(in);
int[] ints = studao.insert(list);
return ints;
}
}
4). ReadExcel 工具类如下：
package com.tarena.excel.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
public class ReadExcel {
/**
* 获取 excel 内容
* @param io
* @return
* @throws Exception
*/
public List<Object[]> readExcelContent(InputStream io) throws Exception {
List<Object[]> list = new ArrayList<Object[]>();
HSSFWorkbook workbook = new HSSFWorkbook(io);
int sheets = workbook.getNumberOfSheets();
for(int i =0;i<sheets;i++){
HSSFSheet sheet = workbook.getSheetAt(i);
for(int j=1;j<sheet.getPhysicalNumberOfRows();j++){
HSSFRow row = sheet.getRow(j);
Object[] arr = new Object[row.getPhysicalNumberOfCells()];
for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {
arr[k] = getformatCellValue(row.getCell(k));
}
list.add(arr);
}
}
return list;
}
/**
* 获取 excel 标题
* @param io
* @return
* @throws Exception
*/
public String[] readExcelTitle(FileInputStream io) throws Exception {
POIFSFileSystem poi = new POIFSFileSystem(io);
HSSFWorkbook workbook = new HSSFWorkbook(poi);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
int nums = row.getPhysicalNumberOfCells();
String[] titles = new String[nums];
for (int i = 0; i < nums; i++) {
titles[i] = getformatCellValue(row.getCell(i));
}
return titles;
}
private String getformatCellValue(HSSFCell cell) {
String cellValue = "";
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellValue = sdf.format(date);
}
break;
default:
cellValue = " ";
}
}else{
cellValue = "";
}
return cellValue;
}
}
5). spring 配置文件如下：
<!-- SpringMVC上传文件时，需要配置MultipartResolver处理器 -->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding" value="UTF-8" />
<!-- 指定所上传文件的总大小不能超过10485760000B。注意maxUploadSize属性的限制不是针对单个文件，而是所有文件的容量之和 -->
<property name="maxUploadSize" value="10485760000"></property>
<property name="maxInMemorySize" value="40960"></property>
</bean>6). pom.xml 文件如下：
<!-- poi -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency> 
扫一扫
434
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
