Java Excel下载的方法

导出excel表格
/**
* 导出Excel
* @response
* @param exportData 导出值
* @param elements 要素代码
* @param sheetName Sheet名称
* @param titleName 标题名称
* @return fileName 自动生成文件名
*/
public static String exportExcel(HttpServletRequest request, List exportData, List elements, String sheetName, String titleName) throws IOException, WriteException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
//放在admin文件夹下 没有登录无法下载
String fileName =”E:/气象数据/”+ UUID.randomUUID().toString().replace(“-“,”“)+”.cvs”;//文件名

/*File exportfile = new File(request.getServletContext().getRealPath(fileName));
if(!exportfile.getParentFile().exists()){
    exportfile.getParentFile().mkdirs();
}*/
File file = new File(fileName);
if(!file.getParentFile().exists()){
    file.getParentFile().mkdirs();
}
//输出流
FileOutputStream os = new FileOutputStream(fileName);

//cvs 文件内容
StringBuffer cvsbuffer = new StringBuffer();
if(titleName!=null && titleName.length()>0){
    cvsbuffer.append("\""+titleName+"\"\n");
}

//根据指定的字段参数读取对象中的值
int columnSize = elements.size();
if(columnSize>0) {
    cvsbuffer.append("\"序号\"");
    for(int i=0;i<elements.size();i++) {
        ElementCodeTables ele = elements.get(i);
        cvsbuffer.append("\""+ele.getEleName()+"\"");
        if(i<elements.size()-1){
            cvsbuffer.append("\t");
        }
    }
    cvsbuffer.append("\n");//换行

    int row = 2;
    for(Object obj:exportData) {
        cvsbuffer.append("\""+(row-1)+"\"");
        for(ElementCodeTables element:elements) {
            //通过属性名取值
            Object value = ((JSONObject)obj).get(element.getEleCode());
            cvsbuffer.append("\""+value+"\"");
            if((row-1)<elements.size()){
                cvsbuffer.append("\t");
            }
        }
        cvsbuffer.append("\n");//换行
        row++;
    }
}

os.write(cvsbuffer.toString().getBytes());
os.flush();
os.close();
return fileName;

}
public static void excel(HttpServletResponse response, List exportData, List elements, String titleName) throws IOException, WriteException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
String fname = titleName;
OutputStream os;
try {
os = response.getOutputStream();
response.reset();
response.setCharacterEncoding(“UTF-8”);
fname = URLEncoder.encode(fname,”UTF-8”);
response.setHeader(“Content-Disposition”,
“attachment;filename=” + new String(fname.getBytes(“UTF-8”), “GBK”) + “.xlsx”);
response.setContentType(“application/msexcel”);

    WritableWorkbook workbook = Workbook.createWorkbook(os);
    WritableSheet sheet = workbook.createSheet("数据统计",0);
    sheet.setColumnView(1,20);
    sheet.setColumnView(3,15);
    sheet.mergeCells(0,0,elements.size()+2,0);
    WritableFont wf = new WritableFont(WritableFont.COURIER,20,WritableFont.NO_BOLD);
    WritableCellFormat wcf = new WritableCellFormat(wf);
    wcf.setAlignment(jxl.format.Alignment.CENTRE);
    wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    wcf.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
    Label lable = new Label(0,0,titleName,wcf);
    sheet.setRowView(0,600,false);
    sheet.addCell(lable);

    // 创建要显示的内容,创建一个单元格，第一个参数为列坐标，第二个参数为行坐标，第三个参数为内容
    WritableFont underline = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.NO_BOLD);
    WritableCellFormat greyBackground = new WritableCellFormat(underline);
    greyBackground.setWrap(true);
    // 设置背景颜色为灰色
    greyBackground.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
    // 单元格中的内容水平方向居中
    greyBackground.setAlignment(jxl.format.Alignment.CENTRE);
    // 单元格的内容垂直方向居中
    greyBackground.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    Label no = new Label(0,1,"序号",greyBackground);
    sheet.addCell(no);
    int cell = 1;
    int row = 1;
    for(ElementCodeTables ect : elements){
        WritableCell ele = new Label(cell,row,ect.getEleName(),greyBackground);
        sheet.setRowView(row,500,false);
        sheet.addCell(ele);
        cell++;
    }
    row = 2;
    for(Object obj:exportData) {
        cell = 1;
        for(ElementCodeTables element:elements) {
            //通过属性名取值
            Object value = ((JSONObject)obj).get(element.getEleCode());
            WritableCell sort = new Label(0,row,row-1+"",greyBackground);
            sheet.addCell(sort);
            WritableCell elel = new Label(cell,row,value.toString(),greyBackground);
            sheet.setRowView(row,500,false);
            sheet.setColumnView(cell,15);
            sheet.addCell(elel);
            cell++;
        }
        row++;
    }

    workbook.write();
    workbook.close();
    os.close();
} catch (IOException e) {
    e.printStackTrace();
} catch (WriteException e) {
    e.printStackTrace();
} finally {

}

}

/**
* 大量数据的输出
* @param response
* @param exportData
* @param elements
* @param titleName
* @throws IOException
* @throws WriteException
* @throws IllegalAccessException
* @throws NoSuchMethodException
* @throws InvocationTargetException
*/
public static void exportData(HttpServletResponse response, List exportData, List elements, String titleName) throws IOException, WriteException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
OutputStream os = null;
try {
response.setCharacterEncoding(“UTF-8”);
response.setContentType(“application/force-download”); // 设置下载类型
String filename = titleName+”.xlsx”;
response.setHeader(“Content-Disposition”, “attachment;filename=” + filename); // 设置文件的名称
os = response.getOutputStream(); // 输出流
SXSSFWorkbook wb = new SXSSFWorkbook(1000);//内存中保留 1000 条数据，以免内存溢出，其余写入 硬盘
//获得该工作区的第一个sheet
Sheet sheet1 = wb.createSheet(“sheet1”);
int excelRow = 0;
//标题行
Row titleRow = (Row) sheet1.createRow(excelRow++);
for (int i = 0; i < elements.size(); i++) {
Cell cell = titleRow.createCell(i);
cell.setCellValue(elements.get(i).getEleName());
}

    if (exportData != null && exportData.size() > 0) {
        for (int i = 0; i < exportData.size(); i++) {
            //明细行
            Row contentRow = (Row) sheet1.createRow(excelRow++);
            for (int j = 0; j < elements.size(); j++) {
                Cell cell = contentRow.createCell(j);
                Object value = ((JSONObject)exportData.get(i)).get(elements.get(j).getEleCode());
                cell.setCellValue(value.toString());
            }
        }
    }
    wb.write(os);
} catch (Exception e) {
    e.printStackTrace();
} finally {
    try {
        if (os != null) {
            os.close();
        }
    } catch (IOException e) {
        e.printStackTrace();
    } // 关闭输出流
}

}