导如excel文件
工具类
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import net.sf.ehcache.hibernate.management.impl.BeanUtils;
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.HSSFFont;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExcelUtil {
private ExcelUtil() {
}
/**
* 主标题
* @param title
* @param cellRangeAddressLength
* @return
*/
public static HSSFWorkbook makeExcelHead(String title, int cellRangeAddressLength){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle styleTitle = createStyle(workbook, (short)16);
HSSFSheet sheet = workbook.createSheet(title);
sheet.setDefaultColumnWidth(25);
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, cellRangeAddressLength);
sheet.addMergedRegion(cellRangeAddress);
HSSFRow rowTitle = sheet.createRow(0);
HSSFCell cellTitle = rowTitle.createCell(0);
// 为标题设置背景颜色
styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleTitle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
cellTitle.setCellValue(title);
cellTitle.setCellStyle(styleTitle);
return workbook;
}
/**
* 二级标题
* @param workbook
* @param secondTitles
* @return
*/
public static HSSFWorkbook makeSecondHead(HSSFWorkbook workbook, String[] secondTitles){
// 创建用户属性栏
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow rowField = sheet.createRow(1);
HSSFCellStyle styleField = createStyle(workbook, (short)13);
for (int i = 0; i < secondTitles.length; i++) {
HSSFCell cell = rowField.createCell(i);
cell.setCellValue(secondTitles[i]);
cell.setCellStyle(styleField);
}
return workbook;
}
/**
* 插入数据
* @param workbook
* @param dataList
* @param beanPropertys
* @return
*/
public static <T> HSSFWorkbook exportExcelData(HSSFWorkbook workbook, List<T> dataList, String[] beanPropertys) {
HSSFSheet sheet = workbook.getSheetAt(0);
// 填充数据
HSSFCellStyle styleData = workbook.createCellStyle();
styleData.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleData.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
for (int j = 0; j < dataList.size(); j++) {
HSSFRow rowData = sheet.createRow(j + 2);
T t = dataList.get(j);
for(int k=0; k<beanPropertys.length; k++){
Object value = BeanUtils.getBeanProperty(t, beanPropertys[k]);
HSSFCell cellData = rowData.createCell(k);
cellData.setCellValue(value.toString());
cellData.setCellStyle(styleData);
}
}
return workbook;
}
/**
* 使用批量导入方法时,请注意需要导入的Bean的字段和excel的列一一对应
* @param clazz
* @param file
* @param beanPropertys
* @return
*/
public static <T> List<T> parserExcel(Class<T> clazz, File file, String[] beanPropertys) {
// 得到workbook
List<T> list = new ArrayList<T>();
try {
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
// 直接从第三行开始获取数据
int rowSize = sheet.getPhysicalNumberOfRows();
if(rowSize > 2){
for (int i = 2; i < rowSize; i++) {
T t = clazz.newInstance();
Row row = sheet.getRow(i);
int cellSize = row.getPhysicalNumberOfCells();
for(int j=0; j<cellSize; j++){
Object cellValue = getCellValue(row.getCell(j));
org.apache.commons.beanutils.BeanUtils.copyProperty(t, beanPropertys[j], cellValue);
}
list.add(t);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 通用的读取excel单元格的处理方法
* @param cell
* @return
*/
private static Object getCellValue(Cell cell) {
Object result = null;
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
//对日期进行判断和解析
if(HSSFDateUtil.isCellDateFormatted(cell)){
double cellValue = cell.getNumericCellValue();
result = HSSFDateUtil.getJavaDate(cellValue);
}
break;
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR:
result = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
break;
}
}
return result;
}
/**
* 提取公共的样式
* @param workbook
* @param fontSize
* @return
*/
private static HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize){
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 创建一个字体样式
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints(fontSize);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
return style;
}
}
实体类
public class User {
public String name;
public String account;
public String dept;
public String gender;
public String email;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public String getDept() {
return dept;
}
public void setDept(String dept) {
this.dept = dept;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public User(String name, String account, String dept, String gender, String email) {
super();
this.name = name;
this.account = account;
this.dept = dept;
this.gender = gender;
this.email = email;
}
public User() {
super();
}
}
controller类
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import cn.itcast.core.model.User;
@Controller
@RequestMapping("/excel")
public class ExcelController {
@RequestMapping("/export.do")
@ResponseBody
public void getExcel(HttpServletRequest req, HttpServletResponse response) throws IOException {
String fileName = new Date().getTime() + ".xls";
List<User> userList = new ArrayList<User>();
User u1 = new User("张三", "zhangsan", "10", "男", "zhangsan@163.com");
User u2 = new User("张三1", "zhangsan1", "10", "男", "zhangsan@163.com");
User u3 = new User("张三2", "zhangsan2", "10", "男", "zhangsan@163.com");
userList.add(u1);
userList.add(u2);
userList.add(u3);
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("用户列表", 4);
String[] secondTitles = { "用户名", "账号", "所属部门", "性别", "电子邮箱" };
HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);
String[] beanProperty = { "name", "account", "dept", "gender", "email" };
HSSFWorkbook workbook = ExcelUtil.exportExcelData(workbook2, userList, beanProperty);
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}
@RequestMapping("/import.do")
public void upload(MultipartFile uploadFile, HttpSession session) throws IllegalStateException, IOException {
String filename = uploadFile.getOriginalFilename();
String path = session.getServletContext().getRealPath("/");
File file = new File(path, filename);
uploadFile.transferTo(file);
String[] beanProperty = { "name", "account", "dept", "gender", "email" };
List<User> list = ExcelUtil.parserExcel(User.class, file, beanProperty);
}
}
springmvc 新增视图解析器
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="104857600" />
<property name="maxInMemorySize" value="4096" />
<property name="defaultEncoding" value="UTF-8"></property>
</bean>
maven依赖
<!-- poi 开始 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>net.sf.ehcache</groupId>
<artifactId>ehcache</artifactId>
<version>2.10.4</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
<!--poi 结束 -->