POI实现Servlet导出Excel_poi-servlet-CSDN博客

本文链接：https://blog.csdn.net/xfht2011/article/details/17606695

这是在做项目的时候遇到的一个功能，经过找资料，在这里总结共享

package com.eeplat.servlet;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.swing.plaf.synth.Region;

import org.apache.poi.hssf.model.Sheet;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.exedosoft.plat.bo.BOInstance;
import com.exedosoft.plat.bo.DOService;

import java.io.*;
import java.text.Format;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class ImportExcelServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//导出Excel报表
String country=request.getParameter("importcountrySelect");

XSSFWorkbook workbook=new XSSFWorkbook();
XSSFSheet sheet=workbook.createSheet("XX实力统计");
sheet.setColumnWidth(0, 5000);
sheet.setColumnWidth(1, 5000);
sheet.setColumnWidth(2, 5000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 5000);
sheet.setColumnWidth(5, 5000);
sheet.setColumnWidth(6, 5000);
sheet.setColumnWidth(7, 5000);
XSSFCellStyle setBorder=workbook.createCellStyle();
setBorder.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
setBorder.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
setBorder.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFDataFormat format=workbook.createDataFormat();
setBorder.setDataFormat(format.getFormat("yyyy年mm月dd日"));

XSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 12);//设置字体大小
setBorder.setFont(font);
font.setBold(true);

XSSFRow title=sheet.createRow(0);
XSSFCell cell0=title.createCell(0);
cell0.setCellStyle(setBorder);
cell0.setCellValue("舰种");

//title.createCell(0).setCellValue("舰种");
//title.createCell(1).setCellValue("舰级");
XSSFCell cell1=title.createCell(1);
cell1.setCellStyle(setBorder);
cell1.setCellValue("舰级");
//title.createCell(2).setCellValue("数量（艘）");
XSSFCell cell2=title.createCell(2);
cell2.setCellStyle(setBorder);
cell2.setCellValue("数量（艘）");
//title.createCell(3).setCellValue("占总数量%");
XSSFCell cell3=title.createCell(3);
cell3.setCellStyle(setBorder);
cell3.setCellValue("占总数量%");
//title.createCell(4).setCellValue("排水量（吨）");
XSSFCell cell4=title.createCell(4);
cell4.setCellValue("排水量（吨）");
cell4.setCellStyle(setBorder);
//title.createCell(5).setCellValue("");
XSSFCell cell5=title.createCell(5);
cell5.setCellStyle(setBorder);
cell5.setCellValue("");
//title.createCell(6).setCellValue("占总吨位%");
XSSFCell cell6=title.createCell(6);
cell6.setCellStyle(setBorder);
cell6.setCellValue("占总吨位%");
//title.createCell(7).setCellValue("服役时间");
XSSFCell cell7=title.createCell(7);
cell7.setCellStyle(setBorder);
cell7.setCellValue("服役时间");
//CellRangeAddress range1=new CellRangeAddress(0,0,4,5);
sheet.addMergedRegion(new CellRangeAddress(0,0,4,5));

XSSFRow row1=sheet.createRow(1);
// row1.createCell(0);
XSSFCell cell10=row1.createCell(0);
cell10.setCellStyle(setBorder);
//row1.createCell(1);
XSSFCell cell11=row1.createCell(1);
cell11.setCellStyle(setBorder);
//row1.createCell(2);
XSSFCell cell12=row1.createCell(2);
cell12.setCellStyle(setBorder);
//row1.createCell(3);
XSSFCell cell13=row1.createCell(3);
cell13.setCellStyle(setBorder);
// row1.createCell(4).setCellValue("满载（水下）");
XSSFCell cell14=row1.createCell(4);
cell14.setCellStyle(setBorder);
cell14.setCellValue("满载（水下）");

//row1.createCell(5).setCellValue("合计");
XSSFCell cell15=row1.createCell(5);
cell15.setCellStyle(setBorder);
cell15.setCellValue("合计");
row1.createCell(6).setCellStyle(setBorder);
row1.createCell(7).setCellStyle(setBorder);

sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));
sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));
sheet.addMergedRegion(new CellRangeAddress(0,1,2,2));
sheet.addMergedRegion(new CellRangeAddress(0,1,3,3));
sheet.addMergedRegion(new CellRangeAddress(0,1,6,6));
sheet.addMergedRegion(new CellRangeAddress(0,1,7,7));
List rst=this.getDataSet(country);
int rowNum=2;
int sumCount=0;
Float sum=0.000000f;
for (int i = 0; i < rst.size(); i++) {
BOInstance b=(BOInstance)rst.get(i);
XSSFRow dataRow=sheet.createRow(rowNum);
//dataRow.createCell(0).setCellValue(b.getValue("type_chs"));
XSSFCell cellType=dataRow.createCell(0);
cellType.setCellStyle(setBorder);
cellType.setCellValue(b.getValue("type_chs"));
dataRow.createCell(1).setCellValue(b.getValue("level"));
dataRow.createCell(2).setCellValue(b.getValue("fuyishuliang"));
int shuliang=Integer.parseInt(b.getValue("fuyishuliang"));
sumCount+=shuliang;
dataRow.createCell(3).setCellValue("比例");
dataRow.createCell(4).setCellValue(b.getValue("maxton"));
//计算吨位和数量的乘积
Float maxton=Float.parseFloat(b.getValue("maxton"));
Integer count=Integer.parseInt(b.getValue("fuyishuliang"));
sum+=maxton*count;
dataRow.createCell(5).setCellValue(""+maxton*count);
dataRow.createCell(6).setCellValue("占总吨位%");
dataRow.createCell(7).setCellValue(b.getValue("fuyiyear"));
rowNum++;
//合并相同的舰种
}
//遍历workbook的列
System.out.println(sumCount);
Map<String, Integer> mp=new HashMap<String, Integer>();
for (int i = 2; i <=sheet.getLastRowNum(); i++) {
XSSFRow r=sheet.getRow(i);
XSSFCell c=r.getCell(5);
XSSFCell shuliang=r.getCell(2);
Float dunwei=Float.parseFloat(c.getStringCellValue());
Float count=Float.parseFloat(shuliang.getStringCellValue());
XSSFCell dunweiPercent=r.getCell(6);
XSSFCell countPercent=r.getCell(3);
NumberFormat nf = NumberFormat.getPercentInstance();
nf.setMaximumFractionDigits(6);
dunweiPercent.setCellValue(nf.format(dunwei/sum));
countPercent.setCellValue(nf.format(count/sumCount));
}

/* Iterator it=mp.keySet().iterator();
while(it.hasNext()){
String key=it.next().toString();
Integer value=mp.get(key);
System.out.println("key:"+key+"Value:"+value);
}
for (int i = 2; i <sheet.getLastRowNum(); i++) {
XSSFRow r=sheet.getRow(i);
XSSFCell c=r.getCell(0);
String cellValue=c.getStringCellValue();
if(mp.containsKey(cellValue)){
int num=mp.get(cellValue);
sheet.addMergedRegion(new CellRangeAddress(i,num,0,0));
}
} */
//合并相同的列，并设置样式，垂直居中显示
this.addMergedRegion(sheet, 0, 2, sheet.getLastRowNum(), workbook);

response.setContentType("application/msexcel;charset=UTF-8");
try {
response.addHeader("Content-Disposition", "attachment;filename=\""
+ new String(("XX实力统计" +new Date().toString()+ ".xlsx").getBytes("GBK"),
"ISO8859_1") + "\"");
//创建输出流对象
OutputStream out = response.getOutputStream();
//将创建的Excel对象利用二进制流的形式强制输出到客户端去
workbook.write(out);
//强制将数据从内存中保存
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}

//用的是EEPLAT平台的API
public List getDataSet(String country){
DOService service=DOService.getService("shipclass_import_excel");
List paramList=new ArrayList();
paramList.add(country);
service.setParaList(paramList);
List shipClassList=service.invokeSelect();
return shipClassList;
}

/**
* 合并单元格
* @param sheet 要合并单元格的excel 的sheet
* @param cellLine 要合并的列
* @param startRow 要合并列的开始行
* @param endRow 要合并列的结束行
*/
private static void addMergedRegion(XSSFSheet sheet, int cellLine, int startRow, int endRow,XSSFWorkbook workBook){

XSSFCellStyle style = workBook.createCellStyle(); // 样式对象

style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平
XSSFFont font = workBook.createFont();
font.setFontName("黑体");
font.setBold(true);
font.setFontHeightInPoints((short) 12);//设置字体大小
style.setFont(font);
//获取第一行的数据,以便后面进行比较
String s_will = sheet.getRow(startRow).getCell(cellLine).getStringCellValue();
int count = 0;
boolean flag = false;
for (int i = 3; i <= endRow; i++) {
String s_current = sheet.getRow(i).getCell(0).getStringCellValue();
if(s_will.equals(s_current))
{
s_will = s_current;
if(flag)
{
sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine,cellLine));
XSSFRow row = sheet.getRow(startRow-count);
String cellValueTemp = sheet.getRow(startRow-count).getCell(0).getStringCellValue();
XSSFCell cell = row.createCell(0);
cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
cell.setCellStyle(style); // 样式
count = 0;
flag = false;
}
startRow=i;
count++;
}else{
flag = true;
s_will = s_current;
}
//由于上面循环中合并的单元放在有下一次相同单元格的时候做的，所以最后如果几行有相同单元格则要运行下面的合并单元格。
if(i==endRow&&count>0)
{
sheet.addMergedRegion(new CellRangeAddress(endRow-count,endRow,cellLine,cellLine));
String cellValueTemp = sheet.getRow(startRow-count).getCell(0).getStringCellValue();
XSSFRow row = sheet.getRow(startRow-count);
XSSFCell cell = row.createCell(0);
cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
cell.setCellStyle(style); // 样式
}
}
}


}