Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
下载开发包:
解压上面的zip文件:
在项目中引入POI的依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
POI使用:使用POI读取Excel的数据
package com.test.bos.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
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.junit.Test;
public class POITest {
@Test
public void test() throws FileNotFoundException, IOException{
String filePath = "E:\\区域导入测试数据.xls";
//包装一个Excel文件对象
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
//读取文件中第一个Sheet标签页
HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
//遍历标签页中所有的行
for (Row row : sheetAt) {
System.out.println();//换行
//遍历行中的所有单元格
for (Cell cell : row) {
String value = cell.getStringCellValue();
System.out.print(value);
}
}
}
}
POI结合OCUpload的使用
package com.test.bos.web.action;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import com.test.bos.domain.Region;
import com.test.bos.service.IRegionService;
import com.test.bos.web.action.base.BaseAction;
/**
* 区域管理
*
* @author jepson
*
*/
@Controller("regionAction")
@Scope("prototype")
public class RegionAction extends BaseAction<Region> {
@Resource(name="regionService")
private IRegionService regionService;
// 属性驱动,接收上传的文件
private File regionFile;
public void setRegionFile(File regionFile) {
this.regionFile = regionFile;
}
List<Region> regionList = new ArrayList<Region>();
/**
* 区域导入
* @throws IOException
* @throws FileNotFoundException
*/
public String importXls() throws Exception {
//包装一个excel文件对象
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(regionFile));
//读取文件中第一个sheet标签页
HSSFSheet sheet = workbook.getSheet("Sheet1");
//遍历页面中的每一行
for (Row row : sheet) {
int rowNum = row.getRowNum();//获取行号,从0开始
if(rowNum==0){ //不需要第一行数据
continue;//跳出本次循环
}
String id = row.getCell(0).getStringCellValue();
String province = row.getCell(1).getStringCellValue();
String city = row.getCell(2).getStringCellValue();
String district = row.getCell(3).getStringCellValue();
String postcode = row.getCell(4).getStringCellValue();
//包装一个区域对象
Region region = new Region(id, province, city, district, postcode, null, null, null);
/*
* 这里不建议使用
* regionService.save(region);
* 而是加入一个list的集合中,然后进行批量保持。减少打开事务的次数
*/
regionList.add(region);
}
//调用service层的方法批量保存
regionService.saveBatch(regionList);
return NONE;
}
}
POI结合springmvc文件上传
@RequestMapping(value="/item/accountupload.action")
public String accountAddBatchByUploadFile(HttpServletRequest request,HttpSession session,
HttpServletResponse response, MultipartFile acountfile,Model model){
List<Account> accountList = new ArrayList<Account>();
try {
//首先应该判断一下是否上传了文件
if(!acountfile.isEmpty()){
//获取上传的文件类型判断是否是一个xls格式的文件
String contentType = acountfile.getContentType();
if(!"application/vnd.ms-excel".equals(contentType)){
model.addAttribute("uploadinfomsg", "你上传的文件类型不对");
return "uploadinfo";
}else{
//上传的文件格式正确,使用poi技术读取excel表格的数据
InputStream inputStream = acountfile.getInputStream();
//包装一个excel文件对象
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//读取文件中第一个sheet标签页
HSSFSheet sheet = workbook.getSheet("Sheet1");
//获取登录用户的id
Integer user_id = CommonUtils.getLoginUserId(session);
//遍历页面中的每一行
for (Row row : sheet) {
int rowNum = row.getRowNum();//获取行号,从0开始
if(rowNum==0){ //不需要第一行数据
continue;//跳出本次循环
}
String website = row.getCell(1).getStringCellValue();
String url = row.getCell(2).getStringCellValue();
String account = row.getCell(3).getStringCellValue();
String username = row.getCell(4).getStringCellValue();
String mail = row.getCell(5).getStringCellValue();
//这里需要判断一下表格的类型
//0表示是double类型的,1表示String类型
String telephone = null;
if(row.getCell(6).getCellType()==0){
Double tele = row.getCell(6).getNumericCellValue();
telephone = tele.toString();
}else{
telephone = row.getCell(6).getStringCellValue();
}
//这里需要判断一下表格的类型
//0表示是double类型的,1表示String类型
String hint = null;
if(row.getCell(7).getCellType()==0){
Double h = row.getCell(7).getNumericCellValue();
hint = h.toString();
}else{
hint = row.getCell(7).getStringCellValue();
}
//包装一个账户对象
Account ac = new Account(website, url, account, username, mail, telephone, hint, user_id);
/*
* 这里不建议使用
* itemService.save(ac);
* 而是加入一个list的集合中,然后进行批量保持。减少打开事务的次数
*/
accountList.add(ac);
}
//调用service层的方法批量保存
itemService.saveBatch(accountList);
model.addAttribute("uploadinfomsg", "恭喜你上传成功");
return "uploadinfo";
}
}else{
model.addAttribute("uploadinfomsg", "你没有选择上传的文件");
return "uploadinfo";
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("uploadinfomsg", "上传失败");
return "uploadinfo";
}
使用POI将数据写到Excel文件中
/**
* 分区数据导出功能
* @throws IOException
*/
public String exportXls() throws IOException{
//第一步:查询所有的分区数据
List<Subarea> list = subareaService.findAll();
//第二步:使用POI将数据写到Excel文件中
//在内存中创建一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个标签页
HSSFSheet sheet = workbook.createSheet("分区数据");
//创建标题行
HSSFRow headRow = sheet.createRow(0);
headRow.createCell(0).setCellValue("分区编号");
headRow.createCell(1).setCellValue("开始编号");
headRow.createCell(2).setCellValue("结束编号");
headRow.createCell(3).setCellValue("位置信息");
headRow.createCell(4).setCellValue("省市区");
//遍历list集合
for (Subarea subarea : list) {
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
dataRow.createCell(0).setCellValue(subarea.getId());
dataRow.createCell(1).setCellValue(subarea.getStartnum());
dataRow.createCell(2).setCellValue(subarea.getEndnum());
dataRow.createCell(3).setCellValue(subarea.getPosition());
dataRow.createCell(4).setCellValue(subarea.getRegion().getName());
}
//第三步:使用输出流进行文件下载(一个流、两个头)
String filename = "分区数据.xls";
String contentType = ServletActionContext.getServletContext().getMimeType(filename);
ServletOutputStream out = ServletActionContext.getResponse().getOutputStream();
ServletActionContext.getResponse().setContentType(contentType);
//获取客户端浏览器类型
String agent = ServletActionContext.getRequest().getHeader("User-Agent");
filename = FileUtils.encodeDownloadFilename(filename, agent);
ServletActionContext.getResponse().setHeader("content-disposition", "attachment;filename="+filename);
workbook.write(out);
return NONE;
}
解决下载文件不同浏览器附件名的编码
package cn.itcast.bos.utils;
import java.io.IOException;
import java.net.URLEncoder;
import sun.misc.BASE64Encoder;
public class FileUtils {
/**
* 下载文件时,针对不同浏览器,进行附件名的编码
*
* @param filename
* 下载文件名
* @param agent
* 客户端浏览器
* @return 编码后的下载附件名
* @throws IOException
*/
public static String encodeDownloadFilename(String filename, String agent)
throws IOException {
if (agent.contains("Firefox")) { // 火狐浏览器
filename = "=?UTF-8?B?"
+ new BASE64Encoder().encode(filename.getBytes("utf-8"))
+ "?=";
filename = filename.replaceAll("\r\n", "");
} else { // IE及其他浏览器
filename = URLEncoder.encode(filename, "utf-8");
filename = filename.replace("+"," ");
}
return filename;
}
}
使用POI将数据写到Excel中
@RequestMapping(value="/item/downloadaccount.action")
public String downloadAccount(QueryVo vo,Model model,HttpServletRequest request,
HttpServletResponse response,HttpSession session) throws Exception{
Integer userId = CommonUtils.getLoginUserId(session);
vo.setUserId(userId); // 设置当前登录用户的id
//查询登录用户的所有account信息
List<Account> accountList = itemService.QueryAccountList(vo);
//在内存中创建一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个标签页
HSSFSheet sheet = workbook.createSheet("Sheet1");
//创建标题行
HSSFRow headRow = sheet.createRow(0);
headRow.createCell(0).setCellValue("account_id");
headRow.createCell(1).setCellValue("website");
headRow.createCell(2).setCellValue("url");
headRow.createCell(3).setCellValue("account");
headRow.createCell(4).setCellValue("username");
headRow.createCell(5).setCellValue("mail");
headRow.createCell(6).setCellValue("telephone");
headRow.createCell(7).setCellValue("hint");
//遍历list集合
for (Account ac : accountList) {
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
dataRow.createCell(0).setCellValue(ac.getId());
dataRow.createCell(1).setCellValue(ac.getWebsite());
dataRow.createCell(2).setCellValue(ac.getUrl());
dataRow.createCell(3).setCellValue(ac.getAccount());
dataRow.createCell(4).setCellValue(ac.getUsername());
dataRow.createCell(5).setCellValue(ac.getMail());
dataRow.createCell(6).setCellValue(ac.getTelephone());
dataRow.createCell(7).setCellValue(ac.getHint());
}
//使用输出流进行文件下载(一个流、两个头)
String filename = "account.xls";
String contentType =request.getServletContext().getMimeType(filename);
ServletOutputStream out = response.getOutputStream();
response.setContentType(contentType);
//获取客户端浏览器类型
String agent = request.getHeader("User-Agent");
filename = this.encodeDownloadFilename(filename, agent);
response.setHeader("content-disposition", "attachment;filename="+filename);
workbook.write(out);
return null;
}
/**
* 下载文件时,针对不同浏览器,进行附件名的编码
*
* @param filename
* 下载文件名
* @param agent
* 客户端浏览器
* @return 编码后的下载附件名
* @throws IOException
*/
private String encodeDownloadFilename(String filename, String agent)
throws IOException {
if (agent.contains("Firefox")) { // 火狐浏览器
filename = "=?UTF-8?B?"
+ new BASE64Encoder().encode(filename.getBytes("utf-8"))
+ "?=";
filename = filename.replaceAll("\r\n", "");
} else { // IE及其他浏览器
filename = URLEncoder.encode(filename, "utf-8");
filename = filename.replace("+"," ");
}
return filename;
}