【POI】导出excel（不依赖于框架）

使用的是spring mvc

1.首先配置需要的jar包（使用的是POI报表）

 

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.12</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>

2.使用的util类ExcelFileGenerator

 

   

/**
 * 系统数据导出Excel 生成器
 * @version 1.0
 */

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import java.io.OutputStream;
import java.util.ArrayList;

public class ExcelFileGenerator {

	private final int SPLIT_COUNT = 65536; //Excel每个工作簿的行数

	private ArrayList<String> fieldName = null; //excel标题数据集

	private ArrayList<ArrayList<String>> fieldData = null; //excel数据内容	

	private HSSFWorkbook workBook = null;

	/**
	 * 构造器
	 * @param fieldName 结果集的字段名
	 * @param fieldData
	 */
	public ExcelFileGenerator(ArrayList<String> fieldName, ArrayList<ArrayList<String>> fieldData) {

		this.fieldName = fieldName;
		this.fieldData = fieldData;
	}

	/**
	 * 创建HSSFWorkbook对象
	 * @return HSSFWorkbook
	 */
	public HSSFWorkbook createWorkbook() {

		workBook = new HSSFWorkbook();//创建一个工作薄对象
		int rows = fieldData.size();//总的记录数
        int lines = fieldData.size() + 1;
		int sheetNum = 0;           //指定sheet的页数

		if (lines % SPLIT_COUNT == 0) {
			sheetNum = lines / SPLIT_COUNT;
		} else {
			sheetNum = lines / SPLIT_COUNT + 1;
		}

		for (int i = 1; i <= sheetNum; i++) {//循环2个sheet的值
			HSSFSheet sheet = workBook.createSheet("Page " + i);//使用workbook对象创建sheet对象
			HSSFRow headRow = sheet.createRow((short) 0); //创建行，0表示第一行（本例是excel的标题）
			for (int j = 0; j < fieldName.size(); j++) {//循环excel的标题
				HSSFCell cell = headRow.createCell( j);//使用行对象创建列对象，0表示第1列
				/**************对标题添加样式begin********************/
				//设置列的宽度/
				sheet.setColumnWidth(j, 6000);
				HSSFCellStyle cellStyle = workBook.createCellStyle();//创建列的样式对象
				HSSFFont font = workBook.createFont();//创建字体对象
				//字体加粗
				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				//字体颜色变红
				font.setColor(HSSFColor.RED.index);
				//如果font中存在设置后的字体，并放置到cellStyle对象中，此时该单元格中就具有了样式字体
				cellStyle.setFont(font);
				/**************对标题添加样式end********************/
				
				//添加样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				if(fieldName.get(j) != null){
					//将创建好的样式放置到对应的单元格中
					cell.setCellStyle(cellStyle);
					cell.setCellValue((String) fieldName.get(j));//为标题中的单元格设置值
				}else{
					cell.setCellValue("-");
				}
			}
			//分页处理excel的数据，遍历所有的结果
			for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
				if (((i - 1) * SPLIT_COUNT + k) >= rows)//如果数据超出总的记录数的时候，就退出循环
					break;
				HSSFRow row = sheet.createRow((short) (k + 1));//创建1行
				//分页处理，获取每页的结果集，并将数据内容放入excel单元格
				ArrayList<String> rowList = (ArrayList<String>) fieldData.get((i - 1) * SPLIT_COUNT + k);
				for (int n = 0; n < rowList.size(); n++) {//遍历某一行的结果
					HSSFCell cell = row.createCell( n);//使用行创建列对象
					if(rowList.get(n) != null){
						cell.setCellValue((String) rowList.get(n).toString());
					}else{
						cell.setCellValue("");
					}
				}
			}
		}
		return workBook;
	}

	public void expordExcel(OutputStream os) throws Exception {
		workBook = createWorkbook();
		workBook.write(os);//将excel中的数据写到输出流中，用于文件的输出
		os.close();
	}

}

 

补充util方法，解决不同浏览器导出名乱码问题

 

    public static String processFileName(HttpServletRequest request, String fileNames) {
        String codedFileName = null;
        try {
            String agent = request.getHeader("USER-AGENT");
            if (null != agent && agent.contains("MSIE") || null != agent
                    && agent.contains("Trident")) { // ie
                codedFileName = java.net.URLEncoder.encode(fileNames, "UTF8");
            } else if (null != agent && agent.contains("Mozilla")) { // 火狐,chrome等
                codedFileName = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return codedFileName;
    }

 

 

 

 

 

3.controller中调用

@Controller
@RequestMapping(value = "/egg")
public class EggController {
    @RequestMapping(value = "/exportExcel",method = RequestMethod.POST)
    public String exportExcel (@RequestParam("actId") Long actId,HttpServletRequest request,HttpServletResponse response,StatisticsCondition statisticsCondition,String customerCollItemsIds) throws Exception{
        Long entId = CasUtils.getEntId(request);
        if (statisticsCondition.getIsWin()==0){
            statisticsCondition.setIsUse(0);
        }
        statisticsCondition.setTelephone(statisticsCondition.getTelephone().trim());
        ArrayList<String> fieldName =new ArrayList<>();
        fieldName.add("昵称");//这里生成数据的方式不太好，如果可以的话最好不要这么写
        fieldName.add("openId");
        fieldName.add("是否中奖");
        fieldName.add("奖项等级");
        fieldName.add("奖项名称");
        fieldName.add("是否领取");
        List<String> list = ListUtils.stringToList(customerCollItemsIds,"#");
        if (CollectionUtils.isNotEmpty(list)){
            for (String s :list){
                fieldName.add(CollectItemsEnum.getCnName(Integer.valueOf(s)));
            }
        }
//        fieldName.add("姓名");
//        fieldName.add("电话");
//        fieldName.add("邮箱");
//        fieldName.add("地址");
//        fieldName.add("备注");
        fieldName.add("参与时间");
        ArrayList<ArrayList<String>> fieldData = eggService.findExportUserInfo(entId,actId,statisticsCondition,customerCollItemsIds);
        ExcelFileGenerator excelFileGenerator = new ExcelFileGenerator(fieldName,fieldData);
        OutputStream os = response.getOutputStream();
        String filename = "砸金蛋数据统计结果（"+ com.raipeng.micro.core.utils.DateUtils.format(new Date(), com.raipeng.micro.core.utils.DateUtils.PATTERN_TIMESTAMP)+"）.xls";
        //filename = new String(filename.getBytes("gbk"),"iso-8859-1");
        filename = ExcelFileGenerator.processFileName(request,filename)
/**设置response对象的设置*/ //可以不加，但是保证response缓冲区没有任何数据，开发时建议加上 
        response.reset(); 
        response.setContentType("application/vnd.ms-excel"); 
        response.setHeader("Content-disposition", "attachment;filename="+filename); 
        response.setBufferSize(1024); 
/**将生成的excel报表，写到os中*/ 
       excelFileGenerator.expordExcel(os);
      return null; 
}

 

 

4 serviceImp查询需要显示的数据，其实看标红的就可以

 

 

 

 

@Override
    @Transactional
    public <span style="color:#FF0000;">ArrayList<ArrayList<String>></span> findExportUserInfo(Long entId,Long actId,StatisticsCondition statisticsCondition,String customerCollItemsIds) {
        List<Object[]> objectList = wactPlayRecordDao.findStatisticsByConditionForExport(entId, actId,
                statisticsCondition.getParticipateBegin(), statisticsCondition.getParticipateEnd()
                , statisticsCondition.getTelephone(), statisticsCondition.getIsWin(), statisticsCondition.getIsUse());
        ArrayList<ArrayList<String>> <span style="color:#FF0000;">fieldDataList </span>= new ArrayList<>();//创建保存一行数据list的list
        if (objectList.size()>0){
            for (Object[] obj:objectList){
                ArrayList<String> <span style="color:#FF0000;">fieldData </span>= new ArrayList<>();//创建保存一行数据的list
                ExportFieldsDto exportFieldsDto = new ExportFieldsDto();
                Long id = new BigInteger(obj[0].toString()).longValue();
                Integer isWin = new Short(obj[1].toString()).intValue();
                Integer isUse = new Short(obj[2].toString()).intValue();
                String openid = (String)obj[3];
                String createdDateStr = com.raipeng.micro.core.utils.DateUtils.format((Date)obj[4], com.raipeng.micro.core.utils.DateUtils.PATTERN_2);
                WactAwards wactAwards = new WactAwards();
                Long awardsId = new BigInteger(obj[5].toString()).longValue();
                if (awardsId != 0l){
                    wactAwards = wactAwardsDao.findAwardByAwardId(awardsId);
                }
                exportFieldsDto.setIsWin(isWin==0?"未中奖":"已中奖");
                exportFieldsDto.setAwardsGrade(wactAwards.getGradeName()==null?"":wactAwards.getGradeName());
                exportFieldsDto.setAwardsName(wactAwards.getName()==null?"":wactAwards.getName());
                exportFieldsDto.setIsUse(isUse==0?"未领取":"已领取");
                if (isWin==0){
                    exportFieldsDto.setIsUse("");
                }
                exportFieldsDto.setOpenid(openid);
                exportFieldsDto.setCreatedDateStr(createdDateStr);
                Object[] object = wactPlayRecordDao.findUserInfoByOpenId(openid);
                if (object[2] != null){
                    exportFieldsDto.setNickName((String)object[2]);
                }else if (object[4] != null){
                    exportFieldsDto.setNickName((String)object[4]);
                }else {
                    exportFieldsDto.setNickName("");
                }
                fieldData.add(exportFieldsDto.getNickName()==null?"":exportFieldsDto.getNickName());
                fieldData.add(exportFieldsDto.getOpenid()==null?"":exportFieldsDto.getOpenid());
                fieldData.add(exportFieldsDto.getIsWin().toString()==null?"":exportFieldsDto.getIsWin().toString());
                fieldData.add(exportFieldsDto.getAwardsName());
                fieldData.add(exportFieldsDto.getAwardsName());
                fieldData.add(exportFieldsDto.getIsUse().toString()==null?"":exportFieldsDto.getIsUse().toString());//保存一行中的信息

                List<String> list = ListUtils.stringToList(customerCollItemsIds, "#");
                if (CollectionUtils.isNotEmpty(list)){
                    for (String s :list){
                        Object val =customerCollitemInfoDao.findValByPlayRecordIdAndCustCollItemsId(id, Long.valueOf(s));
                        if ("未中奖".equals(exportFieldsDto.getIsWin()) && val == null){
                            fieldData.add("");
                        }else if ("已中奖".equals(exportFieldsDto.getIsWin()) && val== null){
                            fieldData.add("未填写中奖信息");
                        }else {
                            fieldData.add(val.toString());
                        }
                    }
                }
                fieldData.add(exportFieldsDto.getCreatedDateStr()==null?"":exportFieldsDto.getCreatedDateStr());
                fieldDataList.add(fieldData);//保存这一行的list
            }
            return fieldDataList;
        }
        return null;
    }

5.dao使用原生的sql查询需要显示的数据