在实际的项目开发中，经常会有一些涉及到导入导出的文档的功能。apache开源项目之一poi对此有很好的支持，对之前的使用做一些简要的总结。
1，导入jar
为了保证对格式的兼容性，在项目的pom.xml添加这三个jar：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
2，生成文件、下载
一般我们对导出的数据变化不会太大，所以考虑到重复使用，我们可以在服务端生成一个文件，在用户再次导出时直接取这个文件下载即可，相当于一个缓存，当然如果服务端文件数过多需要定时任务或者shell去清理.. 不多啰嗦，下面开始贴代码
/**
* 生成考勤信息文档
* @param date 导出日期
* @param type 1考勤统计总数word，2考勤详情excle
* @return
* @throws IOException
*/
@RequestMapping(value="/export_message",method=RequestMethod.GET)
public @ResponseBody CommonResponse export(@RequestParam String date,
@RequestParam Integer type,
@RequestParam Integer orgId,
@RequestParam String orgName,
HttpServletResponse response,
HttpServletRequest request) throws IOException{
CommonResponse rsp = null;
if (!StringUtil.isEmpty(date)) {
String fileName = "";
File file;
if (type == 1) {
fileName = date.trim()+ ".docx";
file = new File(WORD+ fileName);
}else{
fileName = date.trim() + orgId + ".xls";
file = new File(EXCEL + fileName);
} if (file.exists()) {
rsp = new CommonResponse(1, "exists",fileName);
}else{
//文件不存在，用poi生成
int rs = messageService.exportMessage(DateUtil.str2Date(date),type,orgId,orgName);
if (rs == 1){
rsp = new CommonResponse(0, "ok",fileName);
}else{
rsp = new CommonResponse(-1, "error");
}
}
}else{
//参数错误
rsp = new CommonResponse(-1,"error");
logger.error("Fail to convert the parameters for export_message,date:{}", date);
}
return rsp;
}
/**
* 下载考勤信息文档
* @param response
* @param request
* @throws FileNotFoundException
* @throws UnsupportedEncodingException
*/
@RequestMapping(value="/down_info_file",method = RequestMethod.GET)
public @ResponseBody void downFile(HttpServletResponse response,HttpServletRequest request) throws FileNotFoundException, UnsupportedEncodingException { String filePath,newFileName;
String fileName = request.getParameter("fileName"); //兼容性配置
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0){
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");//firefox
}else if(request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0){
fileName = URLEncoder.encode(fileName, "UTF-8");//IE
} if (fileName.endsWith(".docx"))
filePath = WORD;
else
filePath = EXCEL; // 读到流中
InputStream inStream = new FileInputStream(filePath + fileName); if (fileName.endsWith(".docx"))
newFileName = fileName.substring(0, 10) + "勤务上报数与排名统计.docx";
else
newFileName = fileName.substring(0, 10) + "勤务详情统计.xls"; // 设置输出的格式
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(newFileName, "UTF-8")+ "\"");
// 循环取出流中的数据
byte[] b = new byte[1024];
int len;
try {
while ((len = inStream.read(b)) > 0)
response.getOutputStream().write(b, 0, len);
inStream.close();
} catch (IOException e) {
logger.error("Download the document is failed,message:{}", e.getMessage());
}
}
@Override
public int exportMessage(Date date,Integer type,Integer orgId,String orgName) { int rs = 0; File wordfile = new File(WORD);
File excelfile = new File(EXCEL);
if (!wordfile.exists() || !excelfile.exists()) {
wordfile.mkdirs();// 目录不存在的情况下，创建目录。
excelfile.mkdirs();
} String exportExcel = EXCEL + DateUtil.date2Str(date) + orgId +".xls";
String exportWord = WORD + DateUtil.date2Str(date) +".docx";
if (type == 1) {
try {
List<MessageCount> listMc = new ArrayList<>();
List<MessageTopUser> listMt = null;
List<Organization> list = organizationService.findLessFour();
for (int i = 0; i < list.size(); i++) {
Integer _orgId = list.get(i).getOrgId();
if (_orgId != 1) {
MessageCount mc = messageDao.findAllCountByOrgId(date, _orgId);
listMt = messageDao.findTopThree(date);
listMc.add(mc);
}
} rs = exportWord(date, exportWord, listMc, listMt);
} catch (Exception e) {
e.printStackTrace();
}
}else{
List<Message> mes = messageDao.exportMessage(date,orgId);
rs = exportExcle(date, mes, exportExcel, orgName);
} return rs;
}
3，word文档流生成
public int exportWord(Date date,String exportPath, List<MessageCount> listMc, List<MessageTopUser> listMt) throws Exception{
int result = 1;
logger.debug("export excle start!,which date is : {}" + date);
XWPFDocument doc = new XWPFDocument();//新建一个文档
XWPFParagraph nav = doc.createParagraph();
nav.setStyle("1"); //1级大纲
XWPFRun runNav = nav.createRun();
runNav.setBold(true); //加粗
runNav.setText("【塔式智能系统每日监测情况通报】");
String text = DateUtil.date2Str(date) + "，";
int alarm = 0,news = 0,danger = 0,duty = 0,other = 0,police = 0,trail = 0;
String allOutText = "";
for (int i = 0; i < listMc.size(); i++) {
MessageCount list = listMc.get(i);
int getalarm = list.getAlarm().intValue();//一键报警
int getnews = list.getNews().intValue();//动态
int getdanger = list.getDanger().intValue();//隐患
int getduty = list.getDuty().intValue();//勤务记录
int getother = list.getOther().intValue();//其他
int getpolice = list.getPolice().intValue();//警情
int gettrail = list.getTrail().intValue();//线索
alarm += getalarm;//一键报警
news += getnews;//动态
danger += getdanger;//隐患
duty += getduty;//勤务记录
other += getother;//其他
police += getpolice;//警情
trail += gettrail;//线索
int count = getalarm + getnews + getdanger + getduty + getother + getpolice + gettrail;
/*if (count != 0) {}*/
String name = organizationService.findByOrgId(list.getOrgId()).getName().replace("中队", "所");
allOutText += i+1 +"." + name + count + "条。";
}
int allCount = alarm + news + danger + duty + other + police + trail;
text += "发布信息总数" + allCount + "条。";
text += "其中动态" + news + "条，";
text += "警情" + police + "条，";
text += "线索" + trail + "条，";
text += "隐患" + danger + "条，";
text += "勤务记录" + duty + "条，";
text += "一键报警" + alarm + "条，";
text += "其他" + other + "条。";
XWPFParagraph para = doc.createParagraph();
XWPFRun run = para.createRun();
run.setText(text);
run.setFontSize(8);
run.setFontFamily("微软雅黑");
XWPFParagraph everyOrg = doc.createParagraph();
XWPFRun ev = everyOrg.createRun();
ev.setText(allOutText);
ev.setFontSize(8);
ev.setFontFamily("微软雅黑");
String topThree = "";
for (int j = 0; j < listMt.size(); j++) {
int total = listMt.get(j).getTotal().intValue();
NumberFormat nt = NumberFormat.getPercentInstance();
nt.setMinimumFractionDigits(2);//百分数保留两位
double divisor = (double)total/allCount;
String percent = nt.format(divisor);
logger.debug("The export word data for percent is "+percent);
/*DecimalFormat df2 = new DecimalFormat("###.00");//保留两位小数
System.out.println("小数"+df2.format(p));*/
topThree += j+1 + "." + listMt.get(j).getOrgName().replace("中队", "所") + listMt.get(j).getUname() + "，发布数" + total + "条，占比" + percent + "; ";
}
XWPFParagraph topThreeP = doc.createParagraph();
XWPFRun evp = topThreeP.createRun();
evp.setText(topThree);
evp.setFontSize(8);
evp.setFontFamily("微软雅黑");
try {
FileOutputStream fout = new FileOutputStream(exportPath);
logger.debug("export excle end!");
doc.write(fout); //把doc输出到输出流
fout.close();
}
catch (Exception e){
result = 0;
logger.error("export excle error!,{}",e.getLocalizedMessage());
}
return result;
}
4，excel文档流生成
@SuppressWarnings("deprecation")
public int exportExcle(Date date,List<Message> mes,String exportPath,String orgName){
int result = 1;
logger.debug("Start export excle,which date is : {}" + date);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(DateUtil.date2Str(date));
HSSFRow rowMerging = sheet.createRow((short) 0);
HSSFRow row = sheet.createRow((short) 1);
//标题样式
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
titleStyle.setBorderBottom((short)100);// 下边框
titleStyle.setBorderTop((short)100);// 上边框
HSSFFont titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 20);//设置字体大小
titleFont.setFontName("宋体");
titleFont.setColor(HSSFColor.RED.index);//红字
titleStyle.setFont(titleFont);
//导航行样式
HSSFCellStyle navStyle = wb.createCellStyle();
navStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
navStyle.setBorderBottom((short)50);// 下边框
navStyle.setBorderTop((short)50);// 上边框
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 14);
navStyle.setFont(font);
sheet.addMergedRegion(new Region(0, (short) (0), 0,(short) (11))); //设置跨行， 四个参数分别是：起始行，起始列，结束行，结束列
//设置列宽，第一个参数代表列id(从0开始),第2个参数代表宽度值
sheet.setColumnWidth(0, 6500);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 8000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 4000);
sheet.setColumnWidth(5, 4000);
sheet.setColumnWidth(6, 4000);
sheet.setColumnWidth(7, 4000);
sheet.setColumnWidth(8, 4000);
sheet.setColumnWidth(9, 4000);
sheet.setColumnWidth(10, 4000);
HSSFCell merging = rowMerging.createCell((short) 0);
merging.setCellValue(DateUtil.date2Str(date) + orgName + "群防群治数据统计表");
merging.setCellStyle(titleStyle);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("时间");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 1);
cell.setCellValue("主题");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 2);
cell.setCellValue("描述");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 3);
cell.setCellValue("发送人");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 4);
cell.setCellValue("机构ID");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 5);
cell.setCellValue("机构名称");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 6);
cell.setCellValue("派出所ID");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 7);
cell.setCellValue("所属派出所");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 8);
cell.setCellValue("发布数");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 9);
cell.setCellValue("发布率");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 10);
cell.setCellValue("重复数");
cell.setCellStyle(navStyle);
cell = row.createCell((short) 11);
cell.setCellValue("不规范数");
cell.setCellStyle(navStyle);
//内容样式
HSSFCellStyle contentStyle = wb.createCellStyle();
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setBorderBottom((short)50);// 下边框
titleStyle.setBorderTop((short)50);// 上边框
HSSFFont fontcontent = wb.createFont();
fontcontent.setFontName("宋体");
fontcontent.setFontHeightInPoints((short) 12);
navStyle.setFont(fontcontent);
for (int i = 1; i <= mes.size(); i++)
{
row = sheet.createRow((int) i + 1);
Message rs = mes.get(i - 1);
HSSFCell cellContent = row.createCell((short) 0);
cellContent.setCellValue(DateUtil.toTimeString(rs.getSendTime()));
cellContent.setCellStyle(contentStyle);
cellContent = row.createCell((short) 1);
cellContent.setCellValue(rs.getMsgTypeName());
cellContent.setCellStyle(contentStyle);
cellContent = row.createCell((short) 2);
cellContent.setCellValue(StringUtil.isNull(rs.getContent())?"":rs.getContent());
cellContent.setCellStyle(contentStyle);
cellContent = row.createCell((short) 3);
cellContent.setCellValue(rs.getUname());
cellContent.setCellStyle(contentStyle);
cellContent = row.createCell((short) 4);
cellContent.setCellValue(rs.getOrgId());
cellContent.setCellStyle(contentStyle);
cellContent = row.createCell((short) 5);
cellContent.setCellValue(rs.getOrgName());
cellContent.setCellStyle(contentStyle);
cellContent = row.createCell((short) 6);
int ancestors = rs.getAncestors().equals("") ? rs.getOrgId() : Integer.parseInt(rs.getAncestors());
cellContent.setCellValue(ancestors);
cellContent.setCellStyle(contentStyle);
}
try
{
FileOutputStream fout = new FileOutputStream(exportPath);
logger.debug("export excle end!");
wb.write(fout);
fout.close();
}
catch (Exception e)
{
result = 0;
logger.error("export excle error!,{}",e.getLocalizedMessage());
}
return result;
}
5，总结
其实导出是很简单的，贴上代码后基本上意思就出来了，无非就是通过使用XWPFDocument、HSSFWorkbook这两个类，set相关属性值，数据通过sql从数据库里读取数据做一些相关的循环逻辑，再通过文档流的形式反馈给客户端。可能相对来说导入会比较麻烦，首先是从服务端下载一个模板，然后再根据模板填入数据上传到服务器，这个时候需要做大量的验证处理，再反馈给客户有一个预览界面等等，思路大致是这样，后续再整理excel导入。
poi相关属性参考：http://poi.apache.org/
各jar包作用参考：http://www.07net01.com/2014/09/63956.html