POI导出数据库数据到Excel

最新推荐文章于 2020-06-02 16:14:44 发布

大灰狼1913

最新推荐文章于 2020-06-02 16:14:44 发布

阅读量239

点赞数

CC 4.0 BY-SA版权

分类专栏：开发知识整理 java

本文链接：https://blog.youkuaiyun.com/zzwforbid_404/article/details/81391719

开发知识整理同时被 2 个专栏收录

8 篇文章

订阅专栏

java

8 篇文章

订阅专栏

本文介绍如何使用Java POI库实现Excel表格的绘制及数据导出功能，包括定义实体类、创建样式、合并单元格等操作，并提供了一个完整的测试案例。

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

1.实体类

package com.excel.model;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable{

	private String uid;
	private String username;
	private String age;
	private Date date;
	private String yingShou;
	private String shiShou;
	private String duShu;
	private String address;
	public User(String uid, String username, String age, String yingShou, String shiShou, String duShu,
			String address, Date date) {
		super();
		this.uid = uid;
		this.username = username;
		this.age = age;
		this.date = date;
		this.yingShou = yingShou;
		this.shiShou = shiShou;
		this.duShu = duShu;
		this.address = address;
	}
	public String getYingShou() {
		return yingShou;
	}
	public void setYingShou(String yingShou) {
		this.yingShou = yingShou;
	}
	public String getShiShou() {
		return shiShou;
	}
	public void setShiShou(String shiShou) {
		this.shiShou = shiShou;
	}
	public String getDuShu() {
		return duShu;
	}
	public void setDuShu(String duShu) {
		this.duShu = duShu;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getUid() {
		return uid;
	}
	public void setUid(String uid) {
		this.uid = uid;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getAge() {
		return age;
	}
	public void setAge(String age) {
		this.age = age;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	
}

2.POI绘制Excel表格,并导出

package com.excel.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.RegionUtil;

import com.excel.model.User;

public class PoiExcelHelper {

	/**读取当前文件夹路径*/
	private static String ExcelDir = System.getProperty("user.dir");

	@SuppressWarnings("resource")
	public static void printExcel(List<Map<String,Object>> list) throws IOException{
		File dir = new File(ExcelDir);
		//new File(dir,"导出数据文件目录");参数1:父级文件名;参数2:子级文件名
		File outputFolder = new File(dir,"导出数据文件目录");
		if(! outputFolder.exists()){
			outputFolder.mkdir();
		}
		//POI设计Excel并返回Workbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		wb = printDateToExcel(wb,list);

		//创建输出流
		FileOutputStream OutFile = new FileOutputStream(outputFolder+"/"+
				(new SimpleDateFormat("MM月dd日 hh-mm-ss").format(new java.sql.Date(System.currentTimeMillis())))+
				"数据库导出.xls");
		//将Workbook对象中的Excel内容写入文件
		wb.write(OutFile);
	}

	public static HSSFWorkbook printDateToExcel(HSSFWorkbook wb, List<Map<String, Object>> list) {

		HSSFSheet sheet=wb.createSheet("汇总");// 创建Excel的工作sheet,名称为部门名称
		HSSFPatriarch drawing =sheet.createDrawingPatriarch(); //创建绘图对象 
		sheet.setDefaultColumnWidth(10); //设置表格默认宽度为15个字节
		//sheet.createFreezePane(0, 5, 0, 5);//冻结表格上方五行
	
		
		
		int rownum=0;//用来做行数编号
		
		//确定报表跨单元格数；
		//创建第一行
		HSSFRow row0 = sheet.createRow(rownum);
		rownum++;//从第二行开始写入
		
		// 创建标题样式
		HSSFCellStyle titlestyle = wb.createCellStyle();
	    HSSFFont titlefont = wb.createFont();   
	    titlefont.setFontName("宋体");   //字体
	    titlefont.setFontHeightInPoints((short) 18);//设置字体大小（单位:磅）
	    titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体粗体
	    titlefont.setColor(HSSFColor.BLACK.index); //字体黑色
	    titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置样式居中
	    titlestyle.setFont(titlefont);//加载字体
	    //sheet.setColumnWidth(0, 宽度值)//列宽（单位:磅）
	    
		//创建第二行（标题行）
	    HSSFRow row1 = sheet.createRow(rownum);  //创建第二行
	    row1.setHeightInPoints(22);//行高
		sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 13));  //起始行，结束行，起始列，结束列
		HSSFCell cell1_0 = row1.createCell(0)   ; //创建第一个单元格
		cell1_0.setCellValue(new HSSFRichTextString("数据库导出Excel测试表"));
	    cell1_0.setCellStyle(titlestyle);
	    rownum++;
	    
	    //创建副标题样式
	    HSSFCellStyle stitlestyle = wb.createCellStyle();
	    HSSFFont stitlefont = wb.createFont();   
	    stitlefont.setFontName("宋体");   //字体
	    stitlefont.setFontHeightInPoints((short) 10);//设置字体大小（单位:磅）
	    stitlefont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//字体粗细
	    stitlefont.setColor(HSSFColor.BLACK.index); //字体黑色
	    stitlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置样式靠左
	    stitlestyle.setFont(stitlefont);//加载字体
	    
	    HSSFRow row2 = sheet.createRow(rownum);  //创建第三行（副标题行）
	    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 13));  //起始行，结束行，起始列，结束列
		HSSFCell cell2_0 = row2.createCell(0)   ; //创建第一个单元格
		
		cell2_0.setCellValue(new HSSFRichTextString("导出时间："+new SimpleDateFormat("yyyy-MM-dd").format(new Date())));
		
	    cell2_0.setCellStyle(stitlestyle);
	    rownum++;
	    
	    //表头样式
	    HSSFCellStyle biaotoustyle = wb.createCellStyle();//数据样式
	    biaotoustyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
	    biaotoustyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中  
	    biaotoustyle.setWrapText(true);//自动换行
	    
		
	    //创建第四行（表头）
	    HSSFRow row3 = sheet.createRow(rownum);  //创建第四行
	    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 0, 0));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_0 = row3.createCell(0)   ; //创建单元格
		cell3_0.setCellValue("编号");
		cell3_0.setCellStyle(biaotoustyle);
	    
	    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 1, 1));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_1 = row3.createCell(1)   ; //创建单元格
		cell3_1.setCellValue("姓名");
		cell3_1.setCellStyle(biaotoustyle);
		
		sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 2, 2));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_2 = row3.createCell(2)   ; //创建单元格
		cell3_2.setCellValue("年龄");
		cell3_2.setCellStyle(biaotoustyle);
		
		sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 3, 3));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_3 = row3.createCell(3)   ; //创建单元格
		cell3_3.setCellValue("实收");
		cell3_3.setCellStyle(biaotoustyle);
		
		sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 4, 4));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_4 = row3.createCell(4)   ; //创建单元格
		cell3_4.setCellValue(new HSSFRichTextString("应收"));
		cell3_4.setCellStyle(biaotoustyle);
		
		sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 5, 5));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_5 = row3.createCell(5)   ; //创建单元格
		cell3_5.setCellValue(new HSSFRichTextString("度数"));
		cell3_5.setCellStyle(biaotoustyle);
		
		sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 6, 6));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_6 = row3.createCell(6)   ; //创建单元格
		cell3_6.setCellValue((new HSSFRichTextString("所在区"
				+ "")));
		cell3_6.setCellStyle(biaotoustyle);
		
		sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 7, 7));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_7 = row3.createCell(7)   ; //创建单元格
		cell3_7.setCellValue((new HSSFRichTextString("日期")));
		cell3_7.setCellStyle(biaotoustyle);
		
		sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1, 8, 9));  //起始行，结束行，起始列，结束列
		HSSFCell cell3_8 = row3.createCell(8)   ; //创建单元格
		cell3_8.setCellValue((new HSSFRichTextString("备注")));
		cell3_8.setCellStyle(biaotoustyle);
		
		
		
		rownum++;
		
		//创建第五行（表头）
	    HSSFRow row4 = sheet.createRow(rownum);  //创建第五行
	    
	    
	   
	    
	   
	    
	    rownum++;
	    
	  //设置数据格式
	    HSSFCellStyle datastyle = wb.createCellStyle();//数据样式
	    datastyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
	    datastyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中  
	    datastyle.setWrapText(true);//自动换行
	  
	   
	   
	    
	    /**开始迭代数据*/
	    for (int i = 0; i < list.size(); i++) {

			for (Entry<String, Object> renmap : list.get(i).entrySet()) {
				
				User user = (User) renmap.getValue();
				//循环第一个人的信息
				HSSFRow row5 = sheet.createRow(rownum);
				//编号列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+list.get(i).size()-1, 0, 0));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_0 = row5.createCell(0) ; 
	    		cell5_0.setCellValue(user.getUid());
	    		cell5_0.setCellStyle(datastyle);
	    		
	    		//姓名列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 1, 1));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_1 = row5.createCell(1) ; 
	    		cell5_1.setCellValue(user.getUsername());
	    		cell5_1.setCellStyle(datastyle);
	    		
	    		//年龄列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 2, 2));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_2 = row5.createCell(2) ; 
	    		cell5_2.setCellValue(user.getAge());
	    		cell5_2.setCellStyle(datastyle);
	    		
	    		//实收列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 3, 3));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_3 = row5.createCell(3) ; 
	    		cell5_3.setCellValue(user.getShiShou());
	    		cell5_3.setCellStyle(datastyle);
	    		
	    		//应收列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 4, 4));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_4 = row5.createCell(4) ; 
	    		cell5_4.setCellValue(user.getYingShou());
	    		cell5_4.setCellStyle(datastyle);
	    		
	    		//度数列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 5, 5));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_5 = row5.createCell(5) ; 
	    		cell5_5.setCellValue(user.getDuShu());
	    		cell5_5.setCellStyle(datastyle);
	    		
	    		//地址列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 6, 6));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_6 = row5.createCell(6) ; 
	    		cell5_6.setCellValue(user.getAddress());
	    		cell5_6.setCellStyle(datastyle);
	    		
	    		//日期列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 7, 7));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_7 = row5.createCell(7) ; 
	    		cell5_7.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(user.getDate()));
	    		cell5_7.setCellStyle(datastyle);
	    		
	    		//备注列
				sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 8, 9));  //起始行，结束行，起始列，结束列
	    		HSSFCell cell5_8 = row5.createCell(8) ; 
	    		cell5_8.setCellValue("此处可以写备注");
	    		cell5_8.setCellStyle(datastyle);
	    		
	    		
	    		rownum++;
			}
			
		}
		   
	    
	
    	
	    
		
		    
		return wb;
	
	}
}

3.测试

package com.excel.test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.Test;

import com.excel.model.User;
import com.excel.util.PoiExcelHelper;

public class TestExcel {

	@Test
	public void testDemo() throws IOException{
		List<Map<String, Object>> list = new ArrayList<>();
		Map<String, Object> map1 = new HashMap<>();
		map1.put("第一名", new User("001", "肖明", "13","30","40","50","海淀区", new Date()));
		map1.put("第二名", new User("002", "肖明", "13","30","40","50","海淀区", new Date()));
		map1.put("第三名", new User("003", "肖明", "13","30","40","50","海淀区", new Date()));
		map1.put("第四名", new User("004", "肖明", "13","30","40","50","海淀区", new Date()));
		list.add(map1);
		
		Map<String, Object> map2 = new HashMap<>();
		map2.put("第一名", new User("001", "嘿嘿", "13","30","40","50","海淀区", new Date()));
		map2.put("第二名", new User("002", "呵呵", "13","30","40","50","海淀区", new Date()));
		map2.put("第三名", new User("003", "呵呵", "13","30","40","50","海淀区", new Date()));
		map2.put("第四名", new User("004", "呵呵", "13","30","40","50","海淀区", new Date()));
		list.add(map2);
		PoiExcelHelper.printExcel(list);
	}
	
}

4.输出Excel实例