项目总Excel的导出总结

1,类中的整体实现直接导出

response.setContentType("octets/stream;charset=utf-8");

String str=URLEncoder.encode("产品边界-销量导出.xls","utf-8");
response.setHeader("Content-Disposition","attachment;filename="+str);
OutputStream out =response.getOutputStream();
HSSFWorkbook work=new HSSFWorkbook();
HSSFCellStyle style=work.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillBackgroundColor((short)23);
HSSFFont font=work.createFont();
font.setColor(HSSFFont.COLOR_NORMAL);
style.setFont(font);
HSSFSheet sheet=work.createSheet("产品边界-销量导出");
sheet.setDefaultColumnWidth(25);
//年份的数目
int yearnum=compareForm.getYearsSize();
ExcelUtil.setCellValue(sheet, 0, 0, "销量volume de vente ", style);
sheet.addMergedRegion(new Region(0,(short)0, 0,  (short)(yearnum+1)));
ExcelUtil.setCellValue(sheet, 1, 0, "销量volume de vente ",style);
ExcelUtil.setCellValue(sheet, 2, 0, compareForm.getAstage(),style);
ExcelUtil.setCellValue(sheet, 3, 0, compareForm.getBstage(),style);
ExcelUtil.setCellValue(sheet, 4, 0, compareForm.getBstage()+"-"+ compareForm.getAstage() ,style);
//插入年份
List<String> list=compareForm.getYears1();
Map<String,DevDRateDto> map=compareForm.getSaleMap();
for(int i=0;i<list.size();i++){
ExcelUtil.setCellValue(sheet, 1, i+1,list.get(i), style);
ExcelUtil.setCellValue(sheet, 2, i+1,map.get(list.get(i)).getValue1() , style);
ExcelUtil.setCellValue(sheet, 2, i+1, map.get(list.get(i)).getValue2(), style);
}
ExcelUtil.setCellValue(sheet, 1, yearnum+1, "TOTAL", style);
ExcelUtil.setCellValue(sheet, 2,yearnum+1, compareForm.getTotalSum1(), style);
ExcelUtil.setCellValue(sheet, 3,yearnum+1 , compareForm.getTotalSum2(), style);
work.write(out);
out.flush();

out.close();

2,ExcelUtil中的setCellValue方法

public static void setCellValue(HSSFSheet sheet,int iRow, int iColumn, String values,
HSSFCellStyle style) throws Exception {
    try {
HSSFRow row = sheet.getRow(iRow);
if (null == row) {
row = sheet.createRow(iRow);
}
HSSFCell cell = row.getCell((short) iColumn);
if (cell == null) {
cell = row.createCell((short) iColumn);
}
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (values == null) {
values = "";
}
cell.setCellValue(values);
cell.setCellStyle(style);
} catch (Exception e) {
// this.close();
throw e;
}
    }

3,加入模板后的导出

response.setCharacterEncoding("UTF-8");
response.setContentType("application/msexcel");
String strFileName = java.net.URLEncoder.encode("东风,毛利表.xls", "UTF-8");
response.setHeader("Content-disposition", "attachment; filename="
+ strFileName);
   HSSFWorkbook work = this.getTemplate(request.getRealPath("/")+"resource/excel/dpmvalue.xls");
   HSSFSheet sheet=work.getSheetAt(0);
   HSSFCellStyle style=work.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font=work.createFont();
font.setColor(HSSFFont.COLOR_RED);
style.setFont(font);
ExcelUtil.setCellValue(sheet, 0, 0, "净现值和MOC(%)演变 Evolution de VAN", style);
sheet.addMergedRegion(new Region(0, (short)0,0, (short)3));
ExcelUtil.setCellValue(sheet, 1, 1, compareForm.getAstage(), style);
ExcelUtil.setCellValue(sheet, 1, 2, compareForm.getBstage(), style);
ExcelUtil.setCellValue(sheet, 2, 1, compareForm.getJsumrate()+"", style);
ExcelUtil.setCellValue(sheet, 2, 2, compareForm.getAsumrate()+"", style);
//van
if(compareForm.getJcalResBaseDto()!=null){
ExcelUtil.setCellValue(sheet, 3, 1, compareForm.getJcalResBaseDto().getVan(), style);
}
if(compareForm.getAcalResBaseDto()!=null){
ExcelUtil.setCellValue(sheet, 3, 2, compareForm.getAcalResBaseDto().getVan(), style);
}
if(compareForm.getGvanDto()!=null){
ExcelUtil.setCellValue(sheet, 3, 3, compareForm.getGvanDto().getGvan(), style);
}
//东风毛利率
if(compareForm.getJdfProfitmmDto()!=null){
ExcelUtil.setCellValue(sheet,4 ,1,compareForm.getJdfProfitmmDto().getAvgprofit(), style);
}
if(compareForm.getAdfProfitmmDto()!=null){
ExcelUtil.setCellValue(sheet,4 ,2,compareForm.getAdfProfitmmDto().getAvgprofit(), style);
}
if(compareForm.getGvanDto()!=null){
ExcelUtil.setCellValue(sheet, 4, 3, compareForm.getGvanDto().getGgrossm(), style);
}
//经营利润率MOC%
if(compareForm.getJcalResBaseDto()!=null){
ExcelUtil.setCellValue(sheet, 5, 1, compareForm.getJcalResBaseDto().getMocr(), style);
}
if(compareForm.getAcalResBaseDto()!=null){
ExcelUtil.setCellValue(sheet, 5, 2, compareForm.getAcalResBaseDto().getMocr(), style);
}
if(compareForm.getGvanDto()!=null){
ExcelUtil.setCellValue(sheet, 5, 3, compareForm.getGvanDto().getGmoc(), style);
}
//投资回收期 月Duree de recuperation Mois
if(compareForm.getJcalResBaseDto()!=null){
ExcelUtil.setCellValue(sheet, 6, 1, compareForm.getJcalResBaseDto().getRetour(), style);
}
if(compareForm.getAcalResBaseDto()!=null){
ExcelUtil.setCellValue(sheet, 6, 2, compareForm.getAcalResBaseDto().getRetour(), style);
}
}
4,获得模板的方法

public HSSFWorkbook   getTemplate(String filepath) {
FileInputStream fis = null;
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
fis   = new FileInputStream(filepath);
fs = new POIFSFileSystem(fis);
wb = new HSSFWorkbook(fs);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return wb;
}