• java导出xls


    package com.spring.mvc.xls;

    import java.io.File;
    import java.io.FileInputStream;
    import java.text.DecimalFormat;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    import java.util.Properties;
    import java.util.Set;

    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.Region;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;

    /**
    * 功能描述: Excel操纵类

    * @date: 2013-4-13
    */
    public class ExcelTemplate {
    private static Log logger = LogFactory.getLog(ExcelTemplate.class);
    private static final String DATAS = "datas";

    private HSSFWorkbook workbook;
    private HSSFSheet sheet;
    private HSSFRow currentRow;
    private Map styles = new HashMap(); //数据行的默认样式配置
    private Map confStyles = new HashMap(); //通过设置"#STYLE_XXX"来标识的样式配置
    private int initrow; //数据输出起始行
    private int initcol; //数据输出起始列
    private int num; //index number
    private int currentcol; //当前列
    private int currentRowIndex; //当前行index
    private int rowheight = 22; //行高
    private int lastLowNum = 0;
    private String cellStyle = null;

    private ExcelTemplate() {
    }

    /**
    * 使用默认模板创建ExcelTemplate对象
    * @return 根据模板已初始化完成的ExcelTemplate对象
    */
    public ExcelTemplate newInstance(){
    return newInstance("templates/default.xls");
    }

    /**
    * 指定模板创建ExcelTemplate对象
    * @param templates 模板名称
    * @return 根据模板已初始化完成的ExcelTemplate对象
    */
    public static ExcelTemplate newInstance(String templates){
    try {
    ExcelTemplate excel = new ExcelTemplate();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(new File(templates)));
    excel.workbook = new HSSFWorkbook(fs);
    excel.sheet = excel.workbook.getSheetAt(0);
    //查找配置
    excel.initConfig();
    //查找其它样式配置
    excel.readCellStyles();
    //删除配置行
    excel.sheet.removeRow( excel.sheet.getRow(excel.initrow) );
    return excel;
    } catch (Exception e) {
    e.printStackTrace();
    logger.trace("创建Excel对象出现异常",e);
    throw new RuntimeException("创建Excel对象出现异常");
    }
    }


    /**
    * 设置特定的单元格样式,此样式可以通过在模板文件中定义"#STYLE_XX"来得到,如:
    * #STYLE_1,传入的参数就是"STYLE_1"
    * @param style
    */
    public void setCellStyle(String style){
    cellStyle = style;
    }

    /**
    * 取消特定的单元格格式,恢复默认的配置值,即DATAS所在行的值
    */
    public void setCellDefaultStyle(){
    cellStyle = null;
    }

    /**
    * 创建新行
    * @param index 从0开始计数
    */
    public void createRow(int index){
    //如果在当前插入数据的区域有后续行,则将其后面的行往后移动
    if(lastLowNum > initrow && index > 0){
    sheet.shiftRows(index + initrow ,lastLowNum + index,1,true,true);
    }
    currentRow = sheet.createRow(index + initrow);
    currentRow.setHeight((short)rowheight);
    currentRowIndex = index;
    currentcol = initcol;
    }
    public HSSFCell getCell(int row,int column){
    HSSFCell cell=null;
    try{
    cell=sheet.getRow(row).getCell(column);
    }catch(Exception e){}
    return cell;
    }
    /**
    * 根据传入的字符串值,在当前行上创建新列
    * @param value 列的值(字符串)
    */
    public void createCell(String value){
    HSSFCell cell = createCell();
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(value);
    }
    public void createCell(float value){
    HSSFCell cell = createCell();
    HSSFCellStyle cstyle = (HSSFCellStyle)confStyles.get(cellStyle);
    if(null==cstyle){
    cstyle=workbook.createCellStyle();
    confStyles.put(cellStyle,cstyle);
    }
    cstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
    cell.setCellStyle(cstyle);
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    DecimalFormat df=new DecimalFormat("#0.00");
    cell.setCellValue(Double.parseDouble(df.format(value)));
    }
    public void createCell(double value){
    HSSFCell cell = createCell();
    HSSFCellStyle cstyle = (HSSFCellStyle)confStyles.get(cellStyle);
    if(null==cstyle){
    cstyle=workbook.createCellStyle();
    confStyles.put(cellStyle,cstyle);
    }
    cstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
    cell.setCellStyle(cstyle);
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    DecimalFormat df=new DecimalFormat("#0.00");
    cell.setCellValue(Double.parseDouble(df.format(value)));
    }
    public void createDoubleCell(double value){
    HSSFCell cell = createCell();
    HSSFCellStyle cstyle = (HSSFCellStyle)confStyles.get(cellStyle);
    if(null==cstyle){
    cstyle=workbook.createCellStyle();
    confStyles.put(cellStyle,cstyle);
    }
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    cell.setCellValue(value);
    }
    public void createPercentageCell(double value){
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));

    HSSFCell cell = createCell();
    HSSFCellStyle cstyle = (HSSFCellStyle)confStyles.get(cellStyle);
    if(null==cstyle){
    cstyle=workbook.createCellStyle();
    confStyles.put(cellStyle,cstyle);
    }

    cell.setCellStyle(cellStyle);
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    cell.setCellValue(value);
    }
    public void createNumberCell(Integer value){
    HSSFCell cell = createCell();
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    cell.setCellValue(value);
    }
    public void createNumberCell(Long value){
    HSSFCell cell = createCell();
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    cell.setCellValue(value);
    }
    /**
    * 根据传入的日期值,在当前行上创建新列
    * 在这种情况下(传入日期),你可以在模板中定义对应列
    * 的日期格式,这样可以灵活通过模板来控制输出的日期格式
    * @param value 日期
    */
    public void createCell(Date value){
    HSSFCell cell = createCell();
    cell.setCellValue(value);
    }

    /**
    * 创建当前行的序列号列,通常在一行的开头便会创建
    * 注意要使用这个方法,你必需在创建行之前调用initPageNumber方法
    */
    public void createSerialNumCell(){
    HSSFCell cell = createCell();
    cell.setCellValue(currentRowIndex + num);
    }

    @SuppressWarnings("deprecation")
    private HSSFCell createCell(){
    HSSFCell cell = currentRow.createCell((short)currentcol++);
    // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    HSSFCellStyle style = (HSSFCellStyle)styles.get(new Integer(cell.getCellNum()));
    if(style != null){
    cell.setCellStyle(style);
    }

    //设置了特定格式
    if(cellStyle != null){
    HSSFCellStyle ts = (HSSFCellStyle)confStyles.get(cellStyle);
    if(ts != null){
    cell.setCellStyle(ts);
    }
    }
    return cell;
    }

    /**
    * 获取当前HSSFWorkbook的实例
    * @return
    */
    public HSSFWorkbook getWorkbook(){
    return workbook;
    }

    /**
    * 获取模板中定义的单元格样式,如果没有定义,则返回空
    * @param style 模板定义的样式名称
    * @return 模板定义的单元格的样式,如果没有定义则返回空
    */
    public HSSFCellStyle getTemplateStyle(String style){
    return (HSSFCellStyle)confStyles.get(style);
    }

    /**
    * 替换模板中的文本参数
    * 参数以“#”开始
    * @param props
    */
    public void replaceParameters(Properties props){
    if(props == null || props.size() == 0){
    return;
    }
    Set propsets = props.entrySet();
    Iterator rowit = sheet.rowIterator();
    while(rowit.hasNext()){
    HSSFRow row = (HSSFRow)rowit.next();
    if(row == null) continue;
    int cellLength = row.getLastCellNum();
    for(int i=0; i<cellLength; i++){
    HSSFCell cell = (HSSFCell)row.getCell((short)i);
    if(cell == null) continue;
    String value = cell.getStringCellValue();
    if(value != null && value.indexOf("#") != -1){
    for (Iterator iter = propsets.iterator(); iter.hasNext();) {
    Map.Entry entry = (Map.Entry) iter.next();
    value = value.replaceAll("#"+entry.getKey(),(String)entry.getValue());
    }
    }
    // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell.setCellValue(value);
    }
    }
    }

    /**
    * 初始化Excel配置
    */
    private void initConfig(){
    lastLowNum = sheet.getLastRowNum();
    Iterator rowit = sheet.rowIterator();
    boolean configFinish = false;
    while(rowit.hasNext()){
    if(configFinish){
    break;
    }
    HSSFRow row = (HSSFRow)rowit.next();
    if(row == null) continue;
    int cellLength = row.getLastCellNum();
    int rownum = row.getRowNum();
    for(int i=0; i<cellLength; i++){
    HSSFCell cell = (HSSFCell)row.getCell((short)i);
    if(cell == null) continue;
    String config = cell.getStringCellValue();
    if(DATAS.equalsIgnoreCase(config)){
    //本行是数据开始行和样式配置行,需要读取相应的配置信息
    initrow = row.getRowNum();
    rowheight = row.getHeight();
    initcol = cell.getCellNum();
    configFinish = true;
    }
    if(configFinish){
    readCellStyle(cell);
    }
    }
    }
    }

    /**
    * 读取cell的样式
    * @param cell
    */
    @SuppressWarnings("unchecked")
    private void readCellStyle(HSSFCell cell){
    HSSFCellStyle style = cell.getCellStyle();
    if(style == null) return;

    styles.put(new Integer(cell.getCellNum()),style);
    }

    /**
    * 读取模板中其它单元格的样式配置
    */
    private void readCellStyles(){
    Iterator rowit = sheet.rowIterator();
    while(rowit.hasNext()){
    HSSFRow row = (HSSFRow)rowit.next();
    if(row == null) continue;
    int cellLength = row.getLastCellNum();
    for(int i=0; i<cellLength; i++){
    HSSFCell cell = (HSSFCell)row.getCell((short)i);
    if(cell == null) continue;
    String value = cell.getStringCellValue();
    if(value != null && value.indexOf("#STYLE_") != -1){
    HSSFCellStyle style = cell.getCellStyle();
    if(style == null) continue;
    confStyles.put(value.substring(1),style);
    //remove it
    row.removeCell(cell);
    }
    }
    }
    }
    public void setValue(int rowNum,int colNum,String value){
    HSSFRow row=this.sheet.getRow(rowNum);
    HSSFCell cell=row.getCell((short)colNum);
    if (cell==null){
    cell = row.createCell((short) colNum);
    }
    // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    cell.setCellValue(value);
    }
    public void mergedRegion(int rowNum,int colNum,int cols,int rows){
    sheet.addMergedRegion(new Region(rowNum,(short)colNum,cols,(short)rows));
    }
    }

    //封装导出xls方法

    package com.spring.mvc.xls;

    import java.io.IOException;
    import java.util.List;

    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;

    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.context.request.RequestContextHolder;
    import org.springframework.web.context.request.ServletRequestAttributes;

    /**
    * @description 功能描述: 导出xls方法
    * @author 作 者: 周志伟
    * @param 参 数:
    * @createdate 建立日期: 2014-8-1下午3:38:52
    * @projectname 项目名称: spring_mvc
    * @packageclass 包及类名: com.spring.mvc.xls.Xls.java
    */
    public class Xls {


    /**
    * @projectname 项目名称: spring_mvc
    * @packageclass 包及类名: com.spring.mvc.xls.Xls.java
    * @description 功能描述: 导出xls方法
    * @author 作 者: 周志伟
    * @param 参 数: @param response
    * @param 参 数: @param xls 模板名字
    * @param 参 数: @param xlsname 生成xls标题
    * @param 参 数: @param list 结果集
    * @param 参 数: @throws IOException
    * @return 返回类型: void
    * @createdate 建立日期: 2014-8-1下午3:37:42
    */
    @SuppressWarnings("unused")
    @RequestMapping(value = "aee")

    public static void dataWrite(HttpServletResponse response,String xls,String xlsname,List list) throws IOException{
    int count=0;
    HttpServletRequest req =((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
    @SuppressWarnings("deprecation")
    String realpath = req.getRealPath("/static/template/"+xls+".xls");
    ExcelTemplate template =ExcelTemplate.newInstance(realpath);
    for (int i = 0; i < list.size(); i++) {
    List tmp=(List)list.get(i);
    template.createRow(count);
    template.createNumberCell(Integer.valueOf(count+1));
    for (int j = 1; j < tmp.size()-1; j++) {
    template.createCell(tmp.get(j).toString());
    }
    count++;
    }
    response.reset();
    response.setContentType("application/x-download;charset=GBK");
    response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(xlsname + ".xls", "UTF-8"));
    template.getWorkbook().write(response.getOutputStream());
    response.getOutputStream().flush();
    response.getOutputStream().close();
    }
    }

    在控制层调用

    /**
    * @projectname 项目名称: spring_mvc
    * @packageclass 包及类名: com.spring.mvc.Test.java
    * @description 功能描述: 导出xls
    * @author 作 者: 周志伟
    * @param 参 数: @param response
    * @param 参 数: @throws Exception
    * @return 返回类型: void
    * @createdate 建立日期: 2014-8-1下午3:45:54
    */
    @RequestMapping(value = "xls")

    public void xls(HttpServletResponse response) throws Exception{
    List list = new ArrayList();
    list=testservice.tu();
    Xls.dataWrite(response, "expContractList", "合同", list);
    }

  • 相关阅读:
    MySQL字段数据全部查出【只保留中文、英文、数字、空格的词表】
    MySQL查看当前运行的事务和执行的账户
    【转】【MySQL报错】ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 43, found 39.
    【转】mysqldump的锁表的问题
    mysql 通过echo的方式写入数据库 中文乱码解决方案
    Python3.5爬取豆瓣电视剧数据并且同步到mysql中
    Python3.5爬取cbooo.cn数据并且同步到mysql中
    【转&参考】MySQL利用frm和ibd文件进行数据恢复
    [算法]从一道题引出variable-precision SWAR算法
    [转]nginx负载均衡的五种算法
  • 原文地址:https://www.cnblogs.com/yy123/p/4011269.html
Copyright © 2020-2023  润新知