java导出excel文件------第二种方法

来源：互联网发布：细说php第二版微盘编辑：程序博客网时间：2024/06/07 19:45

1.为了方便excel导出方便，说先我在项目中创建了一个文件夹，用来存放所有的excel模板，当然了根据你自己的需要添加模板，如下图：

2.然后进行导出操作，我这里用的还是spring mvc,进行求导出操作，代码如下：

    @RequestMapping(value = "/exportOrderReport")    @ResponseBody    public ResultVo exportOrderReport(HttpServletRequest request,            HttpServletResponse response, String orderNo, String productName,            String groupName, String groupLeaderName, String userWxNickname,            String orderStatus,String startTime,String endTime,String receiverName,String receiverPhoneNo) throws Exception    {        // 获取excel模板文件        String reportTemplatePath = Constants.PROJECT_ROOT_PATH                + "/reportTemplate/order_report.xls";        // 创建工作簿        HSSFWorkbook workBook = POIUtil.getWorkbook(reportTemplatePath);        HSSFSheet sheet = workBook.getSheetAt(0);        // 获取数据list        OrderVo orderVo = new OrderVo();        orderVo.setOrderNo(orderNo);        orderVo.setProductName(productName);        orderVo.setGroupName(groupName);        orderVo.setGroupLeaderName(groupLeaderName);        orderVo.setUserWxNickname(userWxNickname);        orderVo.setOrderStatus(orderStatus);        orderVo.setStartTime(startTime);        orderVo.setEndTime(endTime);        orderVo.setReceiverName(receiverName);        orderVo.setReceiverPhoneNo(receiverPhoneNo);        List<OrderVo> orderList = orderService.selectOrderList(orderVo);        if (orderList != null && orderList.size() > 0)        {

            //循环list往模板中添加数据            HSSFCell cell = null;            HSSFRow row = null;            for (int i = 0; i < orderList.size(); i++)            {                OrderVo vo = orderList.get(i);                int index = i + 2;                row = sheet.createRow(index);                                cell = row.createCell(0);                                cell.setCellValue(i + 1);                                cell = row.createCell(1);                cell.setCellValue(vo.getOrderNo());                                cell = row.createCell(2);                cell.setCellValue(vo.getOrderStatusName());                                cell = row.createCell(3);                cell.setCellValue(vo.getTradeTime());                                cell = row.createCell(4);                cell.setCellValue(vo.getTotalPrice());                                cell = row.createCell(5);                cell.setCellValue(vo.getProductName());                               cell = row.createCell(6);                cell.setCellValue(vo.getTradeCount());                              cell = row.createCell(7);                cell.setCellValue(vo.getTradePrice());                               cell = row.createCell(8);                cell.setCellValue(vo.getSupplierName());                                cell = row.createCell(9);                cell.setCellValue(vo.getReceiverName());                               cell = row.createCell(10);                cell.setCellValue(vo.getReceiverPhoneNo());                                cell = row.createCell(11);                cell.setCellValue(vo.getReceiverAddress());                                cell = row.createCell(12);                cell.setCellValue(vo.getPostCode());                              cell = row.createCell(13);                cell.setCellValue(vo.getOrderRemark());                              cell = row.createCell(14);                cell.setCellValue(vo.getUserWxNickname());                cell = row.createCell(15);                cell.setCellValue(vo.getGroupLeaderName());   cell = row.createCell(16);                cell.setCellValue(vo.getBaseBonus());                                cell = row.createCell(17);                cell.setCellValue(vo.getHighBonus());                     }        }        // 导出excel        POIUtil.doExportExcel(response, workBook, "订单报表");            return null;    }

3.POIUtil.java是个工具类，主要包含对excel的各种操作，比如创建工作簿，移除sheet，获取cell等等，具体参考下面代码：

import java.awt.image.BufferedImage;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.OutputStream;import java.net.URLEncoder;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.Iterator;import java.util.List;import java.util.regex.Matcher;import java.util.regex.Pattern;import javax.imageio.ImageIO;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFPatriarch;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.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;public class POIUtil{    /**     * LATTER_ASSCII     */    private static final int LATTER_ASSCII = 64;    /**     * LATTER_COUNT     */    private static final int LATTER_COUNT = 26;    /**     * NUMBER_255     */    private static final int NUMBER_255 = 255;    /**     * NUMBER_6     */    private static final int NUMBER_6 = 6;    /**     * DT_SF(时间格式字符串)     */    private static final String DT_SF = "yyyy-MM-dd";    /**     * 创建工作薄     *      * @param tmpNm     * @return     * @throws Exception     */    public static HSSFWorkbook getWorkbook(String tmpNm) throws Exception    {        FileInputStream fis = new FileInputStream(tmpNm);        HSSFWorkbook wb = new HSSFWorkbook(fis);        fis.close();        return wb;    }    /**     * 创建工作薄     *      * @param tmpNm     * @return     * @throws Exception     */    public static HSSFWorkbook getWorkbook(File file) throws Exception    {        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));        HSSFWorkbook workbook = new HSSFWorkbook(fs);        return workbook;    }    /**     * 导出Excel     *      * @param response     * @param wb     * @param excelName     * @return     * @throws Exception     */    public static boolean doExportExcel(HttpServletResponse response,            Workbook wb, String excelName) throws Exception    {        SimpleDateFormat sf = new SimpleDateFormat(DT_SF);        String ctime = sf.format(new Date()).toString();// 取时间字符串        String fileName = excelName + ctime + ".xls";// 文件名        OutputStream out = response.getOutputStream();        try        {            response.setHeader("Content-disposition", "attachment;filename="                    + URLEncoder.encode(fileName, "UTF-8"));            response.setContentType("application/msexcel;charset=UTF-8");            wb.write(out);            return true;        } finally        {            out.close();        }    }    /**     * 移除Sheet     *      * @param wb     * @param name     */    public static void removeSheet(HSSFWorkbook wb, String name)    {        int index = wb.getSheetIndex(name);        wb.removeSheetAt(index);    }    /**     * 获取Cell     *      * @param sheet     * @param rowIndex     * @param columnIndex     * @return     */    public static HSSFCell getCell(HSSFSheet sheet, int rowIndex,            int columnIndex)    {        HSSFCell cell = null;        if (rowIndex < 0 || columnIndex < 0)        {            return cell;        }        HSSFRow row = sheet.getRow(rowIndex);        if (row != null)        {            cell = row.getCell(columnIndex);        }        else        {            row = sheet.createRow(columnIndex);        }        if (cell == null)        {            cell = row.createCell(columnIndex);        }        return cell;    }    /**     * 获取Cell     *      * @param sheet     * @param rowIndex     * @param columnName     * @return     */    public static HSSFCell getCell(HSSFSheet sheet, int rowIndex,            String columnName)    {        int columnIndex = POIUtil.getColumnIndex(columnName);        return POIUtil.getCell(sheet, rowIndex, columnIndex);    }    /**     * 获取Cell     *      * @param sheet     * @param cellName     * @return     */    public static HSSFCell getCell(HSSFSheet sheet, String cellName)    {        Pattern pattern = Pattern.compile("^([A-Z]*)([0-9]*)");        Matcher matcher = pattern.matcher(cellName);        if (!matcher.find())        {            return null;        }        String columnName = matcher.group(1);        int columnIndex = POIUtil.getColumnIndex(columnName);        int rowIndex = Integer.parseInt(matcher.group(2));        return POIUtil.getCell(sheet, rowIndex - 1, columnIndex);    }    /**     * 获取Cell     *      * @param row     * @param columnName     * @return     */    public static HSSFCell getCell(HSSFRow row, String columnName)    {        int columnIndex = POIUtil.getColumnIndex(columnName);        return row.getCell(columnIndex);    }    /**     * 读取不同格式的值     *      * @param cell     * @return     */    public static String readXls(Cell cell)    {        if (cell != null)        {            switch (cell.getCellType())            {            case Cell.CELL_TYPE_NUMERIC:                double d = cell.getNumericCellValue();                int i = (int) d;                return String.valueOf(i).toString().trim();            case Cell.CELL_TYPE_STRING:                return cell.getStringCellValue().toString().trim();            case Cell.CELL_TYPE_BOOLEAN:                return String.valueOf(cell.getBooleanCellValue()).toString()                        .trim();            case Cell.CELL_TYPE_FORMULA:                return String.valueOf(cell.getNumericCellValue()).toString()                        .trim();            case Cell.CELL_TYPE_BLANK:                return "";            default:                // 类型不匹配 返回空                return "";            }        }        else        {            return "";        }    }    /**     * 获取列索引     *      * @param columnName     * @return     */    public static int getColumnIndex(String columnName)    {        int index = 0;        if (StringUtils.isBlank(columnName))        {            return index;        }        int len = columnName.length();        for (int i = 0; i < len; i++)        {            int num = ((int) columnName.toUpperCase().charAt(len - 1 - i))                    - LATTER_ASSCII;            if (i == 0)            {                index = num;                continue;            }            index = LATTER_COUNT * i * num + index;        }        return --index;    }    /**     * 插入行（行之前）     *      * @param sheet     * @param rowIndex     * @param tr     * @return     */    public static HSSFRow insertRowBefore(HSSFSheet sheet, int rowIndex,            HSSFRow tr)    {        sheet.shiftRows(rowIndex, sheet.getLastRowNum(), 1, true, false);        HSSFRow row = sheet.createRow(rowIndex);        initRowAsTemplate(row, tr);        return row;    }    /**     * 插入行（行之后）     *      * @param sheet     * @param rowIndex     * @param tr     * @return     */    public static HSSFRow insertRowAfter(HSSFSheet sheet, int rowIndex,            HSSFRow tr)    {        return insertRowBefore(sheet, rowIndex + 1, tr);    }    /**     * 插入行（行之后）     *      * @param sheet     * @param rowIndex     * @param tr     * @return     */    public static void insertRowAfter(HSSFSheet sheet, int rowIndex,            HSSFRow tr, int count)    {        for (int i = 0; i < count; i++)        {            insertRowBefore(sheet, rowIndex + i + 1, tr);        }    }    /**     * 插入行（模板行）     *      * @param row     * @param tr     */    public static void initRowAsTemplate(HSSFRow row, HSSFRow tr)    {        HSSFSheet sheet = row.getSheet();        row.setHeight(tr.getHeight());        // set style of inserted cell        Iterator<Cell> templateCells = tr.cellIterator();        while (templateCells.hasNext())        {            Cell tc = templateCells.next();            CellStyle cellStyle = tc.getCellStyle();            Integer cellType = tc.getCellType();            Cell cell = row.createCell(tc.getColumnIndex());            if (cellStyle != null)            {                cell.setCellStyle(cellStyle);            }            if (cellType != null)            {                cell.setCellType(cellType);            }        }        // merged cell of inserted row        List<CellRangeAddress> mergedRegionList = POIUtil.getMergedRegions(                tr.getSheet(), tr.getRowNum());        for (CellRangeAddress cra : mergedRegionList)        {            CellRangeAddress mr = new CellRangeAddress(row.getRowNum(),                    row.getRowNum(), cra.getFirstColumn(), cra.getLastColumn());            sheet.addMergedRegion(mr);        }    }    public static List<CellRangeAddress> getMergedRegions(HSSFSheet sheet,            int rowIndex)    {        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();        int sheetMergeCount = sheet.getNumMergedRegions();        for (int i = 0; i < sheetMergeCount; i++)        {            CellRangeAddress cra = sheet.getMergedRegion(i);            int firstRow = cra.getFirstRow();            if (firstRow == (rowIndex))            {                list.add(cra);            }        }        return list;    }    public static void merge(HSSFSheet sheet, int ltRowIndex,            int ltColumnIndex, int rbRowIndex, int rbColumnIndex)    {        CellRangeAddress mr = new CellRangeAddress(ltRowIndex, rbRowIndex,                ltColumnIndex, rbColumnIndex);        sheet.addMergedRegion(mr);    }    public static void merge(HSSFCell ltCell, HSSFCell rbCell)    {        if (ltCell != null && rbCell != null)        {            merge(ltCell.getSheet(), ltCell.getRowIndex(),                    ltCell.getColumnIndex(), rbCell.getRowIndex(),                    rbCell.getColumnIndex());        }    }    public static void merge(HSSFSheet sheet, String ltCellName,            String rbCellName)    {        HSSFCell ltCell = POIUtil.getCell(sheet, ltCellName);        HSSFCell rbCell = POIUtil.getCell(sheet, rbCellName);        POIUtil.merge(ltCell, rbCell);    }    public static void mergeRow(HSSFSheet sheet, int rowStartIndex,            int rowEndIndex, String columnName)    {        int columnIndex = POIUtil.getColumnIndex(columnName);        merge(sheet, rowStartIndex, columnIndex, rowEndIndex, columnIndex);    }    public static void lock(HSSFCell cell, boolean locked)    {        HSSFWorkbook wb = cell.getSheet().getWorkbook();        HSSFCellStyle style = wb.createCellStyle();        style.cloneStyleFrom(cell.getCellStyle());        style.setLocked(locked);        cell.setCellStyle(style);    }    /**     * 导出图片（Excel中）     *      * @param wb     * @param patriarch     * @param imgName     * @param col1     * @param row1     * @param col2     * @param row2     * @throws IOException     */    public static void printPicture(HSSFWorkbook wb, HSSFPatriarch patriarch,            String imgName, int col1, int row1, int col2, int row2)            throws IOException    {        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();        try        {            if (imgName != null)            {                BufferedImage bufferImg = ImageIO.read(new File(imgName));                ImageIO.write(bufferImg, "jpg", byteArrayOut);            }        }        catch(IOException e)        {            throw new IOException(e.getMessage(), e);        }        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, NUMBER_255,                NUMBER_255, (short) col1, row1, (short) col2, row2);        anchor.setAnchorType(NUMBER_6);        patriarch.createPicture(anchor, wb.addPicture(                byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));    }}

至此，代码结束，具体可以看代码中的注释。

1 0