XSSF POI工程对Excel 2007 OOXML (.xlsx)文件操作的纯Java实现

2019独角兽企业重金招聘Python工程师标准>>>

ExcelController 

/**
 * ExcelController
 * @author lenovo
 * @version 1.0
 *
 */
@RequestMapping("/excel/*")
public class ExcelController extends AbstractAction{

    @Autowired
    private IYhxwService yhService;
    
    @RequestMapping(value = "exportjl.htm")
    @ResponseBody
    public void exportjl(HttpServletResponse response,
            @RequestParam(value="cBh", required=false) String cBh,
            @RequestParam(value="startDate", required=false) String startDate,
            @RequestParam(value="endDate", required=false) String endDate
            ) throws UnsupportedEncodingException{
        Map<String, Object> map = new HashMap<String, Object>();
        if(!StringUtils.isBlank(cBh)){
            cBh = null;
        }
        map.put("cBh", cBh);
        map.put("startDate",
            StringUtils.isEmpty(startDate) ? null
                    : DateUtils.parseDate(startDate));
        map.put(
            "endDate",
            StringUtils.isEmpty(endDate) ? null
                    : DateUtils.toEndDate(DateUtils.parseDate(endDate)));
        List<CustomerBehavior> List = yhService.loadListByCondition(map);
        SimpleDateFormat exportSdf = new SimpleDateFormat("yyyyMMdd-HHmmss");
        String exportTime = exportSdf.format(System.currentTimeMillis());
        String fileName = "记录" + exportTime +".xls"; //文件名
        String sheetName = "记录";
        String []title ={"编号", "用户", "请求", "时间"};
        String [][]values = new String[List.size()][];
        for(int i=0; i<List.size(); i++){
            values[i] = new String[List.size()];
            CustomerBehavior obj = List.get(i);
            values[i][0] = obj.getcbh();
            for(Code code : obj.getCodeList()){
                if(code.getPid().equals(CODE_PID_1)){
                    values[i][1] = code.getName;
                }else{
                    values[i][1] = "暂无记录";
                }
            }
            values[i][2] = obj.getcQq();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String fssjTime = sdf.format(obj.getDtFssj());
            values[i][3] = fssjTime;
        }
        XSSFWorkbook wb = ExcelUtil.getXSSFWorkbook(sheetName, title, values, null);
//        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, values, null);
        try {  
            this.setResponseHeader(response, fileName);  
            OutputStream os = response.getOutputStream();  
            wb.write(os);  
            os.flush();  
            os.close();  
       } catch (Exception e) {  
            e.printStackTrace();  
       }  
    }
    
    public void setResponseHeader(HttpServletResponse response, String fileName) {  
        try {  
             try {  
                  fileName = new String(fileName.getBytes(),"ISO8859-1");  
             } catch (UnsupportedEncodingException e) {  
                  // TODO Auto-generated catch block  
                  e.printStackTrace();  
             }  
             response.setContentType("application/octet-stream;charset=ISO8859-1");  
             response.setHeader("Content-Disposition", "attachment;filename="+ fileName);  
             response.addHeader("Pargam", "no-cache");  
             response.addHeader("Cache-Control", "no-cache");  
        } catch (Exception ex) {  
             ex.printStackTrace();  
        }  
   } 
}

ExcelUtil

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * ExcelUtil
 * @author lenovo
 * @version 1.0
 *
 */
public class ExcelUtil {
    
    public static XSSFWorkbook getXSSFWorkbook(String sheetName,String []title,String [][]values, XSSFWorkbook wb){
         // 第一步，创建一个webbook，对应一个Excel文件  
        if(wb == null){
            wb = new XSSFWorkbook();
        }
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        XSSFSheet sheet = wb.createSheet(sheetName);  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        XSSFRow row = sheet.createRow(0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        XSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        XSSFCell cell = null;  
        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);  
            cell.setCellValue(String.valueOf(title[i]));  
            cell.setCellStyle(style);  
        }
        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1); 
            for(int j=0;j<values[i].length;j++){
                 row.createCell(j).setCellValue(values[i][j]);
            }
        }
        
       return wb;
    }
    
    
}

pom.xml 

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.16</version>
</dependency>

$('#exportEx').click(function(){
		 window.location.href="/***/excel/exportjl.htm?cBh="+$('#cBhHide').val()+"&startDate="+$('#startDateHide').val()+"&endDate="+$('#endDateHide').val();
    });

 

转载于:https://my.oschina.net/yuhangyes/blog/1358364