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

小鱼儿 2022-06-18 07:17 157阅读 0赞

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

![Image 1][]   ![Center][]

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));
        }
    }

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

[Image 1]: 
[Center]: /images/20220618/c76a3be0530b45b1a4b250a6a607d03d.png