Excel导出JAVA

1.excel工具类

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;

import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @author zhaohongda
 * @create 2016年6月27日 下午10:08:50
 */
public class ExcelUtil {
    // excel模板路径
    private String srcXlsPath = "";

    private String fileName = "";

    private String sheetName = "";

    Workbook wb = null;

    Sheet sheet = null;

    /** 
     * 设置excel模板路径 
     *  
     * @param srcXlsPath 
     */
    public void setSrcPath(String srcXlsPath) {
        this.srcXlsPath = srcXlsPath;
    }

    /** 
     * 设置要生成excel文件名称 
     *  
     * @param fileName 
     */
    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    /** 
     * 设置模板中哪个Sheet列 
     *  
     * @param sheetName 
     */
    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    /** 
     * 获取所读取excel模板的对象，支持2003和2007
     */
    public Sheet getSheet() {
        InputStream is = null;
        try {
            File fi = new File(srcXlsPath);
            if (!fi.exists()) {
                System.out.println("模板文件:" + srcXlsPath + "不存在!");
                return null;
            }

            boolean isExcel2003 = false;

            // 判断excel模版版本
            if (isExcel2003(srcXlsPath)) {

                isExcel2003 = true;

            }

            is = new FileInputStream(fi);

            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }

            sheet = wb.getSheet(sheetName);
            if (sheet == null) {
                wb.createSheet("default");
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sheet;
    }

    /** 
     * 设置字符串类型的数据 
     *  
     * @param rowIndex 
     *            --行值 
     * @param cellnum 
     *            --列值 
     * @param value 
     *            --字符串类型的数据 
     */
    public void setCellStrValue(Sheet sheet, int rowIndex, int cellnum, String value) {
        Row row = sheet.getRow(rowIndex);
        row = row == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);

        Cell cell = row.getCell(cellnum);
        cell = cell == null ? row.createCell(cellnum) : row.getCell(cellnum);

        cell.setCellValue(value);
    }

    /** 
     * 设置日期/时间类型的数据 
     *  
     * @param rowIndex 
     *            --行值 
     * @param cellnum 
     *            --列值 
     * @param value 
     *            --日期/时间类型的数据 
     */
    public void setCellDateValue(Sheet sheet, int rowIndex, int cellnum, Date value) {
        Row row = sheet.getRow(rowIndex);
        row = row == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);

        Cell cell = row.getCell(cellnum);
        cell = cell == null ? row.createCell(cellnum) : row.getCell(cellnum);

        cell.setCellValue(value);
    }

    /** 
     * 设置浮点类型的数据 
     *  
     * @param rowIndex 
     *            --行值 
     * @param cellnum 
     *            --列值 
     * @param value 
     *            --浮点类型的数据 
     */
    public void setCellDoubleValue(Sheet sheet, int rowIndex, int cellnum, double value) {
        Row row = sheet.getRow(rowIndex);
        row = row == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);

        Cell cell = row.getCell(cellnum);
        cell = cell == null ? row.createCell(cellnum) : row.getCell(cellnum);

        cell.setCellValue(value);
    }

    /** 
     * 设置Bool类型的数据 
     *  
     * @param rowIndex 
     *            --行值 
     * @param cellnum 
     *            --列值 
     * @param value 
     *            --Bool类型的数据 
     */
    public void setCellBoolValue(Sheet sheet, int rowIndex, int cellnum, boolean value) {
        Row row = sheet.getRow(rowIndex);
        row = row == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);

        Cell cell = row.getCell(cellnum);
        cell = cell == null ? row.createCell(cellnum) : row.getCell(cellnum);

        cell.setCellValue(value);
    }

    /** 
     * 设置日历类型的数据 
     *  
     * @param rowIndex 
     *            --行值 
     * @param cellnum 
     *            --列值 
     * @param value 
     *            --日历类型的数据 
     */
    public void setCellCalendarValue(Sheet sheet, int rowIndex, int cellnum, Calendar value) {
        Row row = sheet.getRow(rowIndex);
        row = row == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);

        Cell cell = row.getCell(cellnum);
        cell = cell == null ? row.createCell(cellnum) : row.getCell(cellnum);

        cell.setCellValue(value);
    }

    /** 
     * 设置富文本字符串类型的数据。可以为同一个单元格内的字符串的不同部分设置不同的字体、颜色、下划线 
     *  
     * @param rowIndex 
     *            --行值 
     * @param cellnum 
     *            --列值 
     * @param value 
     *            --富文本字符串类型的数据 
     */
    public void setCellRichTextStrValue(Sheet sheet, int rowIndex, int cellnum, RichTextString value) {
        Row row = sheet.getRow(rowIndex);
        row = row == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);

        Cell cell = row.getCell(cellnum);
        cell = cell == null ? row.createCell(cellnum) : row.getCell(cellnum);

        cell.setCellValue(value);
    }

    /** 
     * 完成导出 
     */
    public void exportToNewFile(HttpServletResponse response) {
        OutputStream out;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public boolean isExcel2003(String filePath) {

        return filePath.matches("^.+\\.(?i)(xls)$");

    }

2.AbstractExcelView类

import java.util.List;
import java.util.Map;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.vdian.saturn.excel.ExcelUtil;

/**
 * 
 * @author zhaohongda
 * @create 2016年6月28日 上午11:53:37
 */
public class GetAppTrendsExcelView extends AbstractExcelView {

    ExcelUtil excelUtil = new ExcelUtil();

    @Override
    protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        // TODO Auto-generated method stub
        excelUtil.setSrcPath("/opt/tmp/template/template.xlsx");
        excelUtil.setFileName("text.xlsx");
        excelUtil.setSheetName("test");
        Sheet sheet = excelUtil.getSheet();
        List<List<Object>> list = (List<List<Object>>) model.get("list");
        for (int i = 1; i <= list.size(); i++) {
            excelUtil.setCellStrValue(sheet,i, 0, String.valueOf(list.get(i - 1).get(0)));
            excelUtil.setCellStrValue(sheet,i, 1, String.valueOf(list.get(i - 1).get(1)));
            excelUtil.setCellStrValue(sheet,i, 2, String.valueOf(list.get(i - 1).get(2)));
            excelUtil.setCellStrValue(sheet,i, 3, String.valueOf(list.get(i - 1).get(3)));
        }

        excelUtil.exportToNewFile(response);
    }
}

3.controller

    @ResponseBody
    @RequestMapping("/excel/apptrends")
    public ModelAndView excelAppTrends(ModelMap model, HttpServletRequest request) throws Exception {
        BaseQuery baseQuery = new BaseQuery();
        baseQuery.setStartTime("2016-06-20");
        baseQuery.setEndTime("2016-06-22");
        List<List<Object>> list = (List<List<Object>>) baseDataService.getCycleData(baseQuery).get("table");
        model.put("list", list);
        GetAppTrendsExcelView viewExcel = new GetAppTrendsExcelView();
        return new ModelAndView(viewExcel,model);
    }

4.界面

<input type="button" οnclick="javascript:outputExcel();" value="导出excel">

<script>
<span style="white-space:pre">	</span>function outputExcel() { 
		location.href="/excel/apptrends";
	} 
</script>