Java实现excel的导出

1.页面点击导出按钮触发函数
        function exportCharge(){
        window.location.href="<%=request.getContextPath()%>/user/exportQQCharge?uid=123";
        }

        2.后台处理
@RequestMapping(value = "/exportQQCharge")
public void exportEventStatistic(HttpServletRequest request, HttpServletResponse response, UserCharge UserCharge){
        List<UserCharge> chargeList=new ArrayList<UserCharge>();
        if(StringUtils.isNotEmpty(UserCharge.getUid())){
        chargeList=userChargeService.geAllCharge(UserCharge.getUid());
        if(chargeList.size()>0){
        for(int i=0;i<chargeList.size();i++){
        if("0".equals(chargeList.get(i).getStatus())){
        chargeList.get(i).setStatus("已申请");
        }else{
        chargeList.get(i).setStatus("充值失败");
        }
        String tmp = chargeList.get(i).getCreatedate();
        int begin = tmp.indexOf(".");
        if (begin > 0) {
        chargeList.get(i).setCreatedate(tmp.substring(0, begin));
        }
        }
        }
        }
        Map<String,Object> beanParams = new HashMap<String, Object>();
        beanParams.put("chargeList", chargeList);
//导出
        ExportUtil.exportExcelForMultiple(beanParams, "/tpl/充值记录.xls", response);
        }

        3.ExportUtil工具类

        package cp.pay.mj.utils;

        import net.sf.jxls.transformer.XLSTransformer;
        import org.apache.commons.lang.StringUtils;
        import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
        import org.apache.poi.hssf.usermodel.HSSFCellStyle;
        import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
        import org.apache.poi.hssf.usermodel.HSSFDataFormat;
        import org.apache.poi.hssf.util.HSSFColor;
        import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
        import org.apache.poi.ss.usermodel.CellStyle;
        import org.apache.poi.ss.usermodel.Drawing;
        import org.apache.poi.ss.usermodel.Sheet;
        import org.apache.poi.ss.usermodel.Workbook;
        import org.apache.poi.ss.util.CellRangeAddress;
        import org.slf4j.Logger;
        import org.slf4j.LoggerFactory;

        import javax.imageio.ImageIO;
        import javax.servlet.http.HttpServletResponse;
        import java.awt.image.BufferedImage;
        import java.io.*;
        import java.net.URL;
        import java.text.SimpleDateFormat;
        import java.util.Date;
        import java.util.HashMap;
        import java.util.List;
        import java.util.Map;
public class ExportUtil {
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
    private static Logger logger = LoggerFactory.getLogger(ExportUtil.class);

    /**
     *
     * @param beanParams
     * @param excelTemplateName
     * @param response
     */
    public static void exportExcel(Map<String,List> beanParams,String excelTemplateName,HttpServletResponse response){

        InputStream is = ExportUtil.class.getResourceAsStream(excelTemplateName);
        XLSTransformer former = new XLSTransformer();
        try {

// XSSFWorkbook workbook = (XSSFWorkbook)former.transformXLS(is, beanParams);
            Workbook workbook = former.transformXLS(is, beanParams);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            OutputStream os;
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (InvalidFormatException e) {
            logger.error(e.getMessage(),e);
        } catch (IOException e) {
            logger.error(e.getMessage());
        }
    }

    /**
     *
     * @param beanParams
     * @param url
     * @param response
     */
    public static void exportExcel(Map<String,List> beanParams, URL url, HttpServletResponse response){
        try {
            InputStream is = url.openStream();
            XLSTransformer former = new XLSTransformer();

            Workbook workbook = former.transformXLS(is, beanParams);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            OutputStream os;
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (InvalidFormatException e) {
            logger.error(e.getMessage(),e);
        } catch (IOException e) {
            logger.error(e.getMessage());
        }
    }

    /**
     *导出excel（多种参数）
     * @param beanParams
     * @param excelTemplateName
     * @param response
     */
    public static void exportExcelForMultiple(Map<String,Object> beanParams,String excelTemplateName,HttpServletResponse response){

        InputStream is = ExportUtil.class.getResourceAsStream(excelTemplateName);
        XLSTransformer former = new XLSTransformer();
        try {
            System.out.println(excelTemplateName);
// String a="/tpl/充值记录.xls";
            int b=excelTemplateName.indexOf(".");
            String c=excelTemplateName.substring(5,b);
            Date date=new Date();
            String d=sdf.format(date);
            String f=d+c+".xls";
            System.out.println(f);
// XSSFWorkbook workbook = (XSSFWorkbook)former.transformXLS(is, beanParams);
            Workbook workbook = former.transformXLS(is, beanParams);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
// response.setHeader("Content- Disposition","attachment;filename="+excelTemplateName+".xls");
//设置导出的文件名，若果header啥都不设置，导出来的文件只有你excelTemplateName的文件名，且不带后缀名
//文件名根据自己的需要设置，避免中文乱码
            response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(f, "UTF-8"));
            OutputStream os;
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (InvalidFormatException e) {
            logger.error(e.getMessage());
        } catch (IOException e) {
            logger.error(e.getMessage());
        }
    }

    /**
     * @param beanParams
     * @param url
     * @param response
     */
    public static void exportExcelForMultiple(Map<String, Object> beanParams, URL url, HttpServletResponse response) {

        try {
            InputStream is = url.openStream();
            XLSTransformer former = new XLSTransformer();
            Workbook workbook = former.transformXLS(is, beanParams);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            OutputStream os;
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            logger.error(e.getMessage(),e);
        } catch (InvalidFormatException e) {
            logger.error(e.getMessage(),e);
        }

    }

    /**
     * @param beanParamsList
     * @param sheetNames
     * @param beanName
     * @param excelTemplateName
     * @param imageUrl
     * @param dx1 the x coordinate within the first cell.
     * @param dy1 the y coordinate within the first cell.
     * @param dx2 the x coordinate within the second cell.
     * @param dy2 the y coordinate within the second cell.
     * @param col1 the column (0 based) of the first cell.
     * @param row1 the row (0 based) of the first cell.
     * @param col2 the column (0 based) of the second cell.
     * @param row2 the row (0 based) of the second cell.
     * @param response
     */
    public static void exportExcelForMultipleSheetsList(List beanParamsList, List sheetNames, String beanName, String excelTemplateName, String imageUrl,
                                                        int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2,
                                                        HttpServletResponse response) {

        InputStream is = ExportUtil.class.getResourceAsStream(excelTemplateName);
        XLSTransformer former = new XLSTransformer();
        try {

            Workbook workbook = former.transformMultipleSheetsList(is, beanParamsList, sheetNames, beanName, new HashMap(), 0);

            if(StringUtils.isNotEmpty(imageUrl)) {
                for (Object sheetName : sheetNames) {
                    Sheet sheet = workbook.getSheet(sheetName.toString());
                    Drawing drawing = sheet.createDrawingPatriarch();

                    ByteArrayOutputStream bos = new ByteArrayOutputStream();
                    BufferedImage BufferImg = ImageIO.read(new File(imageUrl));
                    ImageIO.write(BufferImg, "PNG", bos);

                    HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2); //设置图片显示区域
                    drawing.createPicture(anchor, workbook.addPicture(bos.toByteArray(), workbook.PICTURE_TYPE_PNG));
                }
            }

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            OutputStream os;
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (InvalidFormatException e) {
            logger.error(e.getMessage());
        } catch (IOException e) {
            logger.error(e.getMessage());
        }
    }

    /**
     * 导出excel（合并单元格）
     * @param beanParams
     * @param excelTemplateName
     * @param response
     */
    public static void exportExcelForFeeMerge(Map<String,Object> beanParams,String excelTemplateName,
                                              HttpServletResponse response){

        InputStream is = ExportUtil.class.getResourceAsStream(excelTemplateName);
        XLSTransformer former = new XLSTransformer();
        try {
            Workbook workbook = former.transformXLS(is, beanParams);
            int startDateRowIndex = Integer.parseInt(String.valueOf(beanParams.get("startDateRowIndex")));
            int startRow = startDateRowIndex;
            Sheet sheet = workbook.getSheetAt(0);
            int allDataRows = Integer.parseInt(String.valueOf(beanParams.get("allDataRows")));
            int columnIndex = Integer.parseInt(String.valueOf(beanParams.get("columnIndex")));
            for(int i=startDateRowIndex+1; i<startDateRowIndex+allDataRows; i++){
                if(!sheet.getRow(startRow).getCell(columnIndex).toString()
                        .equals(sheet.getRow(i).getCell(columnIndex).toString())){
                    if(i-startRow > 1){
//需要合并
                        sheet.addMergedRegion(new CellRangeAddress(startRow,i-1,columnIndex,columnIndex));
                    }
//更新开始行
                    startRow = i;
                }
            }

            List<Integer> moneyColumnIndex = (List<Integer>) beanParams.get("moneyColumnIndex");
//设置最后一行的颜色
            for(int i=0; i<sheet.getRow(startDateRowIndex+allDataRows-1).getLastCellNum(); i++){
                CellStyle cellStyle =workbook.createCellStyle();
                cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
                cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
                cellStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
                cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
                cellStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                if(null != moneyColumnIndex && moneyColumnIndex.contains(i)){
                    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
                    sheet.getRow(startDateRowIndex+allDataRows-1).getCell(i).setCellStyle(cellStyle);
                }else{
                    sheet.getRow(startDateRowIndex+allDataRows-1).getCell(i).setCellStyle(cellStyle);
                }
            }

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            OutputStream os;
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (InvalidFormatException e) {
            logger.error(e.getMessage());
        } catch (IOException e) {
            logger.error(e.getMessage());
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
    }
}

 

 

4.需要的jar

<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.6</version>
<exclusions>
<exclusion>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
</exclusion>
</exclusions>
</dependency>

如果需要的jar,没有给出的话，可以到百度maven仓库中，搜索对应的pom

5.文件模板的位置必须是可以访问的，我的是放在resource目录下面

模板我上传到文件中，需要的自行下载

如果对您有一点帮助的话，可以给小编一点鼓励哦
欢迎转载，转载请注明出处，谢谢