导出Excel（可以合并相同数据单元格）

最新推荐文章于 2024-09-25 20:22:09 发布

原创最新推荐文章于 2024-09-25 20:22:09 发布 · 1.8k 阅读

3 ·

CC 4.0 BY-SA版权

java 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一种使用Java和Apache POI库导出Excel并实现单元格合并的方法。通过示例代码展示了如何设置Excel文件的内容、样式及合并单元格的具体实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pom.xml中

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.7</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-scratchpad</artifactId>
		<version>3.14</version>
	</dependency>

util工具类

package com.mdsw.dses.statistics.util;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.ss.util.CellRangeAddress;

public class ExcelUtilIndex {
	private ExcelUtilIndex() {
		 throw new IllegalStateException("Utility class");
	}
	   /**
     * @param fileName 文件名称
     * @param headers 表头
     * @param dataset 数据集
     * @param response HttpServletResponse
     * @param mergeBasis 合并基准列 可选
     * @param mergeCells 要合并的列 可选
     * @throws IOException
     */ 
    public static void exportExelMerge(String fileName,final String[] headers,List<String[]> dataset,HttpServletResponse response, final Integer[] mergeBasis, final Integer[] mergeCells) throws IOException{ 
        String title = "Sheet1"; 
        response.setContentType("application/vnd.ms-excel;charset=utf-8");  
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8")); 
           
        createExcelMerge(title,headers,dataset,response.getOutputStream(),mergeBasis,mergeCells); 
           
        response.setStatus(HttpServletResponse.SC_OK); 
        response.flushBuffer(); 
    } 
       
    /**
     * @param fileName 文件名称
     * @param title 表头
     * @param dataset 数据集
     * @param out OutputStream
     * @param mergeBasis 合并基准列 可选
     * @param mergeCells 要合并的列
     */ 
    public static void createExcelMerge(String fileName,String[] title,List<String[]> dataset, OutputStream out, final Integer[] mergeBasis, final Integer[] mergeCells){ 
        HSSFWorkbook workbook = new HSSFWorkbook(); 
        HSSFSheet sheet = workbook.createSheet(fileName); 
           
        if(title == null || title.length <= 0){ 
            return; 
        } 
        
        HSSFRow row = sheet.createRow(0); // 产生表格标题行   
        for (int i = 0; i < title.length; i++) { 
            HSSFCell cell = row.createCell(i); 
            HSSFRichTextString text = new HSSFRichTextString(title[i]); 
            cell.setCellValue(text); 
        } 

        // 遍历集合数据，产生数据行   
        Iterator<String[]> it = dataset.iterator();   
        int index = 0;   
        while (it.hasNext()) { 
            index++;   
            row = sheet.createRow(index);   
            String[] dataSources = it.next() ; 
            for (int i = 0; i < dataSources.length; i++) {   
                HSSFCell cell = row.createCell(i);   
                cell.setCellValue(dataSources[i]); 
            } 
        }   
        try {   
            if(mergeBasis != null && mergeBasis.length > 0 && mergeCells != null && mergeCells.length > 0){ 
                for(int i = 0 ; i < mergeCells.length ; i++){ 
                    mergedRegion(sheet,mergeCells[i],1,sheet.getLastRowNum(),workbook,mergeBasis); 
                } 
            } 
            workbook.write(out);   
        } catch (IOException e) {   
            e.printStackTrace();   
        } finally { 
            try { 
                out.close(); 
            } catch (IOException e) { 
                e.printStackTrace(); 
            } 
        } 
    }  
       
    /**
     * 合并单元格
     * @param sheet
     * @param cellLine
     * @param startRow
     * @param endRow
     * @param workbook
     * @param mergeBasis
     */ 
    private static void mergedRegion(HSSFSheet sheet, int cellLine,int startRow, int endRow, HSSFWorkbook workbook, Integer[] mergeBasis) { 
        String s_will = sheet.getRow(startRow).getCell(cellLine).getStringCellValue();  // 获取第一行的数据,以便后面进行比较   
        int count = 0;   
        Set<Integer> set = new HashSet<Integer>(); 
        CollectionUtils.addAll(set, mergeBasis); 
        for (int i = 2; i <= endRow; i++) {   
            String s_current = sheet.getRow(i).getCell(cellLine).getStringCellValue();   
            if (s_will.equals(s_current)) {  
                boolean isMerge = true; 
                if(!set.contains(cellLine)){//如果不是作为基准列的列 需要所有基准列都相同 
                    for(int j = 0 ; j < mergeBasis.length ; j++){ 
                        if(!sheet.getRow(i).getCell(mergeBasis[j]).getStringCellValue() 
                                .equals(sheet.getRow(i-1).getCell(mergeBasis[j]).getStringCellValue())){ 
                            isMerge = false; 
                        } 
                    } 
                }else{//如果作为基准列的列 只需要比较列号比本列号小的列相同 
                    for(int j = 0 ; j < mergeBasis.length && mergeBasis[j] < cellLine ; j++){ 
                        if(!sheet.getRow(i).getCell(mergeBasis[j]).getStringCellValue() 
                                .equals(sheet.getRow(i-1).getCell(mergeBasis[j]).getStringCellValue())){ 
                            isMerge = false; 
                        } 
                    } 
                } 
                if(isMerge){ 
                    count++; 
                }else{ 
                    sheet.addMergedRegion(new CellRangeAddress( startRow, startRow+count,cellLine , cellLine)); 
                    startRow = i;   
                    s_will = s_current; 
                    count = 0; 
                } 
            } else {   
                sheet.addMergedRegion(new CellRangeAddress( startRow, startRow+count,cellLine , cellLine));   
                startRow = i;   
                s_will = s_current; 
                count = 0; 
            }   
            if (i == endRow && count > 0) {   
                sheet.addMergedRegion(new CellRangeAddress(startRow,startRow+count ,cellLine , cellLine));   
            }   
        }   
    }
}

实体类

//不需要合并的类
public class AntibacterialDrugUse {
	
	/**属性1*/
    private String 属性1;

    /**属性2*/
    private String 属性2;

    /**属性3*/
    private String 属性3;

    /**属性4*/
    private String 属性4;

	/**属性5*/
    private String 属性5;

    /**需要合并的类*/
    private List<需要合并的类> adrugs;
}

Server层

public void exportExcel(String 条件, HttpServletResponse rep);

Server实现类

@Override
	public void exportExcel(String 条件1, HttpServletResponse rep) {
		String[] title =  { "列1","列2","列3","列4","列5","列6","列7","列8","列9","列10" };
		List<实体类> adu = aduMapper.selectData(条件1);
		List<String[]> dataset = new ArrayList<String[]>(); 
        for (不需要合并的实体类 adus : adu) {
        	for (需要合并的列实体类 aaa : adus.getAdrugs()) {
        		dataset.add(new String[] {
        				//不需要合并的列
        				adus.getClinicno(),adus.getVisitTime(),adus.getPatientName(),adus.getAge(),adus.getVisitDoctor(),
        				//需要合并的列
						aaa.getAntiName(),aaa.getDrugDose(),aaa.getDrugSpec(),aaa.getDrugCost().toString()}); 
			}
		}
        try {
        	ExcelUtilIndex.exportExelMerge("表.xls", title, dataset,rep, new Integer[]{0}, new Integer[]{0,1,2,3,4,5});
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
	}