POI jar包下载:https://pan.baidu.com/s/1lnLbRwUTzuYcGYCSH4PNkA 密码:2yyx
POITest.java(测试类,读取Excel文件):
package com.xxx.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 {
//使用POI解析Excel文件
@Test
public void test1() throws FileNotFoundException, IOException{
String filePath = "C:\\xxx.xls"; //Excel文件
//包装一个Excel文件对象
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
//读取文件中第一个Sheet标签页
HSSFSheet hssfSheet = workbook.getSheetAt(0);
//遍历标签页中所有的行
for (Row row : hssfSheet) {
int rowNum = row.getRowNum();//行号
if(rowNum == 0){
continue; //跳过第一行(表头)
}
System.out.println();
//row.getCell(0); //获取第一个单元格
//遍历行中的每个单元格
for (Cell cell : row) {
String value = cell.getStringCellValue();
System.out.print(value + " ");
}
}
}
}
SubareaAction.java(基于Struts2的Action类,写入Excel,附件下载):
package com.xxx.bos.web.action;
import java.io.IOException;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import com.xxx.bos.domain.Region;
import com.xxx.bos.domain.Subarea;
import com.xxx.bos.service.ISubareaService;
import com.xxx.bos.utils.FileUtils; //自定义的工具类 (判断客户端浏览器类型)
import com.xxx.bos.web.action.base.BaseAction;
@Controller
@Scope("prototype")
public class SubareaAction extends BaseAction<Subarea>{
@Resource
private ISubareaService subareaService;
/**
* 分区数据导出功能
* @throws IOException
*/
public String exportXls() throws IOException{
//第一步:查询所有的分区数据
List<Subarea> list = subareaService.findAll();
//第二步:使用POI将数据写到Excel文件中
//在内存中创建一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个sheet标签页
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("省市区");
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);
ServletActionContext.getResponse().setContentType(contentType);
//获取客户端浏览器类型
String agent = ServletActionContext.getRequest().getHeader("User-Agent");
filename = FileUtils.encodeDownloadFilename(filename, agent); //解决文件名中文乱码问题
ServletActionContext.getResponse().setHeader("content-disposition", "attachment;filename="+filename); //以附件形式下载
ServletOutputStream out = ServletActionContext.getResponse().getOutputStream();
workbook.write(out);
return NONE;
}
}
FileUtils.java(工具类,解决中文附件名编码的工具类):
package com.xxx.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;
}
}