Java POI 导出Excel

梦里梦外; 2022-08-08 14:56 255阅读 0赞

利用POI导出Excel，要导出的数据组织成List<Object\[\]>

需要添加POI包，xmlbeans、dom4j这两个也需要添加，不然会报错

调用的时候，要设置表头等信息，调用下面的setxxx方法就好了

![SouthEast][]

package com;
    
    import java.io.FileOutputStream;
    import java.util.List;
    
    import org.apache.commons.lang.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    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.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class ExcelOperation
    {
        /**
         * Excel导出文件夹地址
         */
        private String m_sExportPath;
        
        /**
         * Excel文件的名称
         */
        private String m_sFileName;
        
        /**
         * Excel文件标题
         */
        private String m_sTitle;
        
        /**
         * 占的列数
         */
        private int m_iColspan;
        
        /**
         * 左边提示信息
         */
        private String m_sLeftTips;
        
        /**
         * 表头
         */
        private List<Object[]> m_listHeader;
        
        /**
         * 构造函数
         * @param sExportPath 导出文件夹地址
         * @param sFileName 文件名
         * @param iColspan 占得列数
         */
        public ExcelOperation(String sExportPath, String sFileName, int iColspan)
        {
            setM_sExportPath(sExportPath);
            setM_sFileName(sFileName);
            setM_iColspan(iColspan - 1);
        }
        
        /**
         * 导出Excel
         * <功能详细描述>
         * @param listHeader 列头
         * @param listContent 数据内容
         * @param sTitle 标题
         * @param sLeftTips 左边的提示信息
         * @return
         * @throws Exception
         * @see [类、类#方法、类#成员]
         */
        public boolean ExportToExcel(List<Object[]> listHeader, List<Object[]> listContent)
        
        {
            try
            {
                //1.创建工作簿
                Workbook wkb;
                if (StringUtils.isNotEmpty(m_sFileName) && m_sFileName.endsWith("xls"))
                {
                    //HSSFWorkbook是用来导出Excel2003格式的
                    wkb = new HSSFWorkbook();
                }
                else if (m_sFileName.endsWith("xlsx"))
                {
                    //XSSFWorkbook是用来导出Excel2007格式的
                    wkb = new XSSFWorkbook();
                }
                else
                {
                    return false;
                }
                
                //2.创建页
                Sheet sheet = wkb.createSheet();
                //设置单元格格式 居中
                CellStyle cellStyle = wkb.createCellStyle();
                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                
                //设置标题
                if (StringUtils.isNotEmpty(m_sTitle))
                {
                    Row titleRow = sheet.createRow(0);
                    //这里是合并单元格，四个参数代表 （开始行，结束行，开始列，结束列）
                    CellRangeAddress range = new CellRangeAddress(0, 0, 0, m_iColspan);
                    sheet.addMergedRegion(range);
                    Cell cell = titleRow.createCell(0);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(m_sTitle);
                }
                
                //设置左边提示信息
                if (StringUtils.isNotEmpty(m_sLeftTips))
                {
                    Row leftRow = sheet.createRow(1);
                    leftRow.createCell(0).setCellValue(m_sLeftTips);
                    
                }
                
                //设置列头
                if (listHeader != null && listHeader.size() > 0)
                {
                    for (int i = 0; i < listHeader.size(); i++)
                    {
                        Row headerRow = sheet.createRow(2 + i);
                        Object[] oArrayHeader = listHeader.get(i);
                        
                        int j = 0;
                        for (Object oHeader : oArrayHeader)
                        {
                            oHeader = oHeader != null ? oHeader : "";
                            headerRow.createCell(0 + j).setCellValue(oHeader.toString());
                            j++;
                        }
                        
                    }
                }
                
                //设置内容
                if (listContent != null && listContent.size() > 0)
                {
                    //正文数据起始行
                    int iContentStartRow = 0;
                    iContentStartRow = listHeader == null ? 2 : listHeader.size() + 2;
                    
                    for (int i = 0; i < listContent.size(); i++)
                    {
                        Row contentRow = sheet.createRow(iContentStartRow + i);
                        Object[] oArrayContent = listContent.get(i);
                        
                        for (int j = 0; j < oArrayContent.length; j++)
                        {
                            Object oContent = oArrayContent[j] != null ? oArrayContent[j] : "";
                            contentRow.createCell(j).setCellValue(oContent.toString());
                        }
                    }
                }
                
                //将文件存到指定位置
                FileOutputStream outputStream = new FileOutputStream(m_sExportPath + "\\" + m_sFileName);
                wkb.write(outputStream);
                outputStream.close();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        
        public String getM_sFileName()
        {
            return m_sFileName;
        }
        
        public void setM_sFileName(String m_sFileName)
        {
            this.m_sFileName = m_sFileName;
        }
        
        public String getM_sExportPath()
        {
            return m_sExportPath;
        }
        
        public void setM_sExportPath(String m_sExportPath)
        {
            this.m_sExportPath = m_sExportPath;
        }
        
        public String getM_sTitle()
        {
            return m_sTitle;
        }
        
        public void setM_sTitle(String m_sTitle)
        {
            this.m_sTitle = m_sTitle;
        }
        
        public String getM_sLeftTips()
        {
            return m_sLeftTips;
        }
        
        public void setM_sLeftTips(String m_sLeftTips)
        {
            this.m_sLeftTips = m_sLeftTips;
        }
        
        public List<Object[]> getM_listHeader()
        {
            return m_listHeader;
        }
        
        public void setM_listHeader(List<Object[]> m_listHeader)
        {
            this.m_listHeader = m_listHeader;
        }
        
        public int getM_iColspan()
        {
            return m_iColspan;
        }
        
        public void setM_iColspan(int m_iColspan)
        {
            this.m_iColspan = m_iColspan;
        }
    }

[SouthEast]: /images/20220731/9a98ddfd2a4a4255b85be0cd105c73d8.png