目录
1.servlet类
2.导出处理类
第一部分
第二部分
3.excel生成类
1.servlet类
下面为导出的servlet类，继承了HttpServlet,实现了service方法，关键代码为红色部分，excelBook = export(request);
是根据请求参数查询结果并的到Workbook的实体，response用两个set方法设置好之后，用getOutputStream
这种写法，用bufferedOutPut可以提高一定性能，但当数据较少时可忽略。下面就是servlet类的源码。
@Named("/servlet/exportBarometerServlet/export")
public class ExportBarometerServlet extends HttpServlet
{
private static final ILogger logger = JalorLoggerFactory.getLogger(ExportBarometerServlet.class);
//excelName加上导出的时间为excel的名称
private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
@Inject
private IRiskBigScreenService riskBigScreenService;
@Inject
private IAosService AosService;
@Override
public void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException{
Workbook excelBook = null;
try
{
excelBook = export(request);
}
catch (WholeException e)
{
throw new ServletException(e);
} catch (ApplicationException e) {
e.printStackTrace();
}
if (excelBook != null)
{
String excelName = "IPMO-RiskProjectListMS";
Date date = new Date();
response.setContentType("application/mx-excel");
response.setHeader("Content-Disposition", "attachment;filename=\"" + excelName+dateFormat.format(date)
+ ".xlsx\"");
OutputStream out = null;
try
{
out = response.getOutputStream();
excelBook.write(out);
out.flush();
}
finally
{
if (out != null)
{
out.close();
}
}
}
}2.导出处理类
第一部分
excelBook = export(request);export()方法我分为两个部分，第一个部分根据request获得List或者Map形式的结果（根据需要转换格式）
private Workbook export(HttpServletRequest request) throws ApplicationException
{
String paramsJson = request.getParameter("paramsJson");
Map map = null;
try {
map = JsonUtil.stringToObject(paramsJson, Map.class);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
XiAnQueryConditionVO vo = new XiAnQueryConditionVO();
if( null != map)
{
vo.setPeriod(map.get("period").toString());
vo.setOrgLevel(map.get("orgLevel").toString());
vo.setOrgCode(map.get("orgCode").toString());
vo.setL_proj_bu(map.get("l_proj_bu").toString());
}由请求获得json字符串，转化为map，得到入参的vo，调用service方法查询数据，可根据需求选择是否分页。
pagedResult = riskBigScreenService.findBarometerList(vo, pageVO);
List<XiAnBigScreenVO> list = pagedResult.getResult();将list转化为LinkedHashMap<String,List<String>>，这种格式而不是HashMap是因为LinkedHashMap有序，顺序与getMap(list);中list的顺序一致。也可以转化为List<List<String>>保证顺序。无论哪种，都是为了为excel表格赋值时有序。
LinkedHashMap<String,List<String>> mapresult = getMap(list);LinkedHashMap<String, List<String>>的String可选择两种，一种是可以确定唯一数据的标识，来自于一条list中的某一字段。
另一种就是直接1,2,3...。
private LinkedHashMap<String, List<String>> getMap(List<XiAnBigScreenVO> resultlist) {
LinkedHashMap<String, List<String>> map = new LinkedHashMap<>();
for(XiAnBigScreenVO resultVO : resultlist)
{
List<String> list = new ArrayList<>();
list.add(resultVO.getRegionName());
list.add(resultVO.getRepofficeName());
list.add(resultVO.getProjNum());
list.add(resultVO.getProjName());
list.add(resultVO.getProjLevel());
list.add(resultVO.getYtd_complete_rate());
list.add(resultVO.getGap());
list.add(resultVO.getYtd_aos_complete_rate());
list.add(resultVO.getYtd_aos_gap());
list.add(resultVO.getDe_lctd_cgp());
list.add(resultVO.getDe_ytm_cgp());
list.add(resultVO.getEx_lctd_ser_cost());
list.add(resultVO.getEx_ytm_ser_cost());
list.add(resultVO.getOperat_warning());
//未有对应的服务出参
list.add("");
list.add("");
list.add(resultVO.getProjProgressScorePercent());
list.add(resultVO.getEhsScorePercent());
list.add(resultVO.getCustomercomplaintsray());
list.add(resultVO.getNontechnicalray());
list.add(resultVO.getCyberaccidentsowner());
list.add(resultVO.getCyberaccidentsother());
list.add(resultVO.getItrmangementray());
list.add(null !=resultVO.getQualitTechScore()?resultVO.getQualitTechScore()+"":"");
list.add(null !=resultVO.getRationTechScore()?resultVO.getRationTechScore()+"":"");
//list.add(null !=resultVO.getInternalControlScore()?resultVO.getInternalControlScore()+"":"");
list.add(resultVO.getRiskTopItems());
list.add(null !=resultVO.getCooperativeResourceScore()?resultVO.getCooperativeResourceScore()+"":"");
map.put(resultVO.getProjNum(), list);
}
return map;
}
第二部分
第二个部分就是wb = ExportBarometer.getXSSFWorkbook(mapresult,list,title1,title2);调用getXSSFWorkbook()这个方法，需要说明的是，入参可以根据需要灵活变化，一般来说，入参应该有标题行，查询的结果List集合，sheetName。
XSSFWorkbook wb =null;
//用数组也可以用list
if ("zh_CN".equals(BaseSdcpUtils.getCurrentLanguage()))
{
String[] title1 = {"地区部","代表处","项目编码","项目名称","项目级别","进度","","","","经营","","","","","","","质量","","客满","","","","","技术","","合同","合作"};
String[] title2 = {"","","","","", "改良计划完成率",
"改良实际完成值",
"AOS改良计划完成率",
"AOS改良实际完成值",
"全周期预测贡献毛利(拨备前)和预算偏差(扣除设备的影响)",
"YTD预测贡献毛利(拨备前)和预算偏差（扣除设备的影响）",
"ITD服务成本超预算",
"YTD服务成本超预算",
"ITD的贡毛偏差",
"收入完成率(YTD)",
"经营预警",
"SLA达标率",
"网络可用率",
"重大客户投诉",
"非技术问题",
"网络事故",
"MV重大故障",
"管理升级",
"未关闭重大风险数量",
"未关闭重大问题数量",
"关键假设闭环率",
"（合作）/自有租赁资源使用资源SLA(自有&租赁)"};
wb = ExportBarometer.getXSSFWorkbook(mapresult,list,title1,title2);
}else
{
//英文状态下的列标题
String[] title1 = {"Region","RepOffice","Project Code","Project Name","Project Level","Plan&Execution","","","","Operation","","","","","","","Quality","","Customer","","","","","Technical","","Agreement","Cooperation"};
String[] title2 = {"","","","","", "Transformation Plan Achievement Rate",
"Transformation Actual",
"AOS Transformation Plan Achievement Rate",
"AOS Transformation Actual",
"LIFECYCLE CGP Forecasts (Before Loss Provision)Compared With Latest Budgets (ex.equipment)",
"YTD LCGP Forecasts (Before Loss Provision)Compared With Latest Budgets (ex.equipment)",
"ITD Service Cost Over Lifecycle Budget",
"YTD Service Cost Over Annual Budget",
"ITD Contribution Gross Profit GAP",
"Revenue Achieving Rate (YTD)",
"Operation Alert",
"SLA Standard Rate",
"Network Availability",
"ok",
"Non-Tech Issue",
"Accident",
"MV Critical Incident",
"Management Escalation",
"Unclosed Major Risk Amount",
"Unclosed Major Issue Amount",
"MS Key Assumptions Closed Rate",
"TFPR /Timely Fulfillment of Partner Resources"};
wb = ExportBarometer.getXSSFWorkbook(mapresult,list,title1,title2);
}
return wb;
3.excel生成类
getXSSFWorkbook()方法的具体实现。
与XSSFWorkbook并列的是HSSFWorkbook，这取决于excel格式是xlsx还是xls，一个是2007，一个是2003版本。
//标题行有可能为第一行或者第二行 所以不定长传参
public static XSSFWorkbook getXSSFWorkbook(LinkedHashMap<String, List<String>> map,List<XiAnBigScreenVO> listResult, String[]... title) {
XSSFWorkbook wb = new XSSFWorkbook();
String sheetName = "";
if ("zh_CN".equals(BaseSdcpUtils.getCurrentLanguage()))
{
sheetName = "MS晴雨表项目明细";
}else
{
sheetName = "MsFindBarometerList";
}
XSSFSheet sheet = wb.createSheet(sheetName);
//冻结窗格
sheet.createFreezePane(0, 2);
XSSFRow row = sheet.createRow(0);
row.setHeightInPoints(30);
// XSSFCellStyle style = wb.createCellStyle();
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle style = getColumnTopStyleX(wb);
XSSFCell cell = null;
for (int i = 0; i < title[0].length; i++)
{
cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(title[0][i]);
//9000对应34.5 ，则框架导出的宽度20.88对应5447
sheet.setColumnWidth(i, 5447);
}
row = sheet.createRow(1);
row.setHeightInPoints(40);
for (int i = 0; i < title[1].length; i++)
{
cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(title[1][i]);
}
for(int j=0;j<=4;j++)
{
//合并单元格,四个参数代表起始行结束行起始列结束列
CellRangeAddress region = new CellRangeAddress(0,1,j,j);
sheet.addMergedRegion(region);
}
//根据标题合并单元格 修改麻烦 可考虑优化
CellRangeAddress region = new CellRangeAddress(0,0,5,8);
sheet.addMergedRegion(region);
region = new CellRangeAddress(0,0,9,15);
sheet.addMergedRegion(region);
region = new CellRangeAddress(0,0,16,17);
sheet.addMergedRegion(region);
region = new CellRangeAddress(0,0,18,22);
sheet.addMergedRegion(region);
region = new CellRangeAddress(0,0,23,24);
sheet.addMergedRegion(region);
//写入实体数据 ,list中字符串的顺序必须和数组strArray中的顺序一致
int i = 1;
for (String str : map.keySet()) {
row = sheet.createRow((int) i + 1);
List<String> list = map.get(str); // 创建单元格，并设置值
for (int j = 0; j < title[0].length; j++) {
XSSFCell datecell = row.createCell(j);
datecell.setCellValue(list.get(j));
//为单元格设置颜色
setColor(i+1,j,datecell,listResult.get(i-1),wb);
}
i++;
}
return wb;
}sheet，row，cell为三个关键对象，除了完成基本的赋值之外，有时需要设置单元格大小，颜色，字体，字体颜色大小等，合并单元格，冻结窗格（一般冻结标题行）等。以上是最原始的版本，根据个人习惯可以自由封装方法，使代码看起来更加条理清晰。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
