Excel导出JAVA

来源：互联网发布：中国统一台湾知乎编辑：程序博客网时间：2024/05/07 08:23

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" onclick="javascript:outputExcel();" value="导出excel">

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

0 0