文档章节

java简单的从数据库查询数据使用POI导出excel文件

TSMYK
 TSMYK
发布于 2017/03/31 21:02
字数 1556
阅读 3742
收藏 0

个人公众号:Java技术大杂烩,每天10点精美文章准时奉上

 

使用java简单的从数据库中查询数据,然后写入到excel中,数据的类型为 List<Map<String, Object>>格式的数据。

首先下载POI的jar包,网址:https://poi.apache.org/download.html#POI-3.16-beta2

然后导入jar包到工程下

此外还需要 commons-collections4-4.1.jar 和 xmlbeans-2.6.0.jar 两个额外的jar包,因为我使用的是 3.15 版本的,所以 commons-collections4-4.1.jar  需要使用 4.1 版本的,原来使用的是 4.0 ,报错了。

这两个包可以在这里下载:

https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans/2.6.0

https://mvnrepository.com/artifact/org.apache.commons/commons-collections4/4.1

导入的类:

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 

因为需要解析成为 .xlsx 格式的 Excel 文件,所以需要的是 XSSFWorkbook.

准备数据:

	public static List<Map<String, Object>> getData() {
		List<Map<String, Object>> data = new ArrayList<>();
		// 使用 LinkedHashMap 保证有序,即标题和数据对应上
		Map<String, Object> map1 = new LinkedHashMap<>();
		map1.put("id", 1);
		map1.put("name", "张三");
		map1.put("age", 23);
		map1.put("sex", "男");
		
		Map<String, Object> map2 = new LinkedHashMap<>();
		map2.put("id", 2);
		map2.put("name", "李四");
		map2.put("age", 20);
		map2.put("sex", "女");
		
		Map<String, Object> map3 = new LinkedHashMap<>();
		map3.put("id", 3);
		map3.put("name", "王五");
		map3.put("age", 19);
		map3.put("sex", "男");
		
		Map<String, Object> map4 = new LinkedHashMap<>();
		map4.put("id", 4);
		map4.put("name", "赵六");
		map4.put("age", 18);
		map4.put("sex", "女");
		
		Map<String, Object> map5 = new LinkedHashMap<>();
		map5.put("id", 5);
		map5.put("name", "小七");
		map5.put("age", 22);
		map5.put("sex", "男");
		
		data.add(map1);
		data.add(map2);
		data.add(map3);
		data.add(map4);
		data.add(map5);
	
		return data;
	}

PS : 懒得去连接数据库查询了,就自己随便搞了一个。

下面就是把数据写到 Excel 文件中,只能创建一个 sheet。

	 /** 
     *  导出Excel
     * @param sheetName 表格 sheet 的名称
     * @param headers  标题名称
     * @param dataList 需要显示的数据集合
     * @param exportExcelName 导出excel文件的名字
     */ 
	public  static void exportExcel(String sheetName, List<Map<String, Object>> dataList,
				String[] headers,String exportExcelName) {

		// 声明一个工作薄
		XSSFWorkbook  workbook = new XSSFWorkbook();
		// 生成一个表格
		XSSFSheet sheet = workbook.createSheet(sheetName);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		
		// 生成表格中非标题栏的样式
		XSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.WHITE.index);//背景色
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		// 生成表格中非标题栏的字体
		XSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);
		// 把字体应用到当前的样式
		style.setFont(font);
		

		// 设置表格标题栏的样式
		XSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleStyle.setBorderBottom(BorderStyle.THIN);
		titleStyle.setBorderLeft(BorderStyle.THIN);
		titleStyle.setBorderRight(BorderStyle.THIN);
		titleStyle.setBorderTop(BorderStyle.THIN);
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 设置标题栏字体
		XSSFFont titleFont = workbook.createFont();
		titleFont.setColor(HSSFColor.WHITE.index);
		titleFont.setFontHeightInPoints((short) 12);
		titleFont.setBold(true);
		// 把字体应用到当前的样式
		titleStyle.setFont(titleFont);
		
		// 产生表格标题行
		XSSFRow row = sheet.createRow(0);
		for (short i = 0; i < headers.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellStyle(titleStyle);
			XSSFRichTextString text = new XSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
		
        // 遍历集合数据,产生数据行
		Iterator<Map<String, Object>> it = dataList.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			Map<String, Object> data = it.next();
			int i = 0;
			for(String key : data.keySet()){
				XSSFCell cell = row.createCell(i);
				cell.setCellStyle(style);
				XSSFRichTextString text = new XSSFRichTextString(data.get(key)+"");
				cell.setCellValue(text);
				i++;
			}
		}
		try {
            
            OutputStream out = null;	
			String tmpPath = "G:\\excel\\" + exportExcelName + ".xlsx";
			out = new FileOutputStream(tmpPath);
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			if(workbook != null){
				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if(out != null){
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

 

测试:

	public static void main(String[] args) {
		List<Map<String, Object>> data = MyExecl.getData();
		String sheetName = "学生表";
		String[] headers = {"ID","名称","年龄","性别"};
		String exportExcelName = "student";
		MyExecl.exportExcel(sheetName, data, headers, exportExcelName);
	}

 

结果:

 

重构上面的代码,封装一下

   /**
    * 创建每个 sheet 页的数据
    */
    private void createSheetData(HSSFWorkbook aWorkbook, String[] aTitles, String aSheetName,
                                 List<String[]> aRowData) {
        HSSFSheet tmpSheet = aWorkbook.createSheet(aSheetName);
        // 设置sheet的标题
        HSSFRow tmpTitileRow = tmpSheet.createRow(0);
        for (int i = 0; i < aTitles.length; i++) {
            tmpTitileRow.createCell(i).setCellValue(aTitles[i]);
        }
        // 遍历填充每行的数据
        HSSFRow tmpRow = null;
        int tmpRowNumber = 1;
        for (String[] rowData : aRowData) {
            tmpRow = tmpSheet.createRow(tmpRowNumber);
            for (int i = 0; i < rowData.length; i++) {
                tmpRow.createCell(i).setCellValue(rowData[i]);
            }
            tmpRowNumber++;
        }
    }

    /**
     * 设置响应头
     */
    private void setResponseHeader(HttpServletResponse aResponse, String aFileName){
       try {
           try {
               aFileName = new String(aFileName.getBytes(), "ISO8859-1");
           } catch (UnsupportedEncodingException e) {
               e.printStackTrace();
           }
           aResponse.setContentType("application/octet-stream;charset=ISO8859-1");
           aResponse.setHeader("Content-Disposition", "attachment;filename=" + aFileName);
           aResponse.addHeader("Pargam", "no-cache");
           aResponse.addHeader("Cache-Control", "no-cache");
       } catch (Exception e) {
        e.printStackTrace();
        }
    }

测试:

    /**
     * 通过页面导出
     * @param aResponse
     * @throws IOException
     */
    public void export(HttpServletResponse aResponse) throws IOException {
        HSSFWorkbook tmpWorkbook = new HSSFWorkbook();
        String[] tmpUserTitles = {"姓名", "性别", "年龄", "工作"};
        List<String[]> tmpUsers = getUsers();
        createSheetData(tmpWorkbook, tmpUserTitles, "用户信息", tmpUsers);
        setResponseHeader(aResponse, "用户信息表.xls");
        OutputStream tmpOutputStream = aResponse.getOutputStream();
        tmpWorkbook.write(tmpOutputStream);
        tmpOutputStream.flush();
        tmpOutputStream.close();
    }

    /**
     * 导出,不通过页面导出
     */
    public void export() throws IOException {
        HSSFWorkbook tmpWorkbook = new HSSFWorkbook();
        String[] tmpUserTitles = {"姓名", "性别", "年龄", "工作"};
        List<String[]> tmpUsers = getUsers();
        createSheetData(tmpWorkbook, tmpUserTitles, "用户信息", tmpUsers);
        String[] tmpAddressTitles = {"城市", "区域"};
        List<String[]> getAddress = getAddress();
        createSheetData(tmpWorkbook, tmpAddressTitles, "地址信息", getAddress);
        OutputStream tmpOutputStream = new FileOutputStream("E:\\" + System.currentTimeMillis() + ".xls");
        tmpWorkbook.write(tmpOutputStream);
        tmpOutputStream.flush();
        tmpOutputStream.close();
    }

	/**
	* 测试数据
	*/
    private List<String[]> getAddress(){
        List<String[]> address = new ArrayList<>();
        for(int i = 1; i <= 5; i++){
            String[] addr = new String[2];
            addr[0] = "四川";
            addr[1] = "高新 - " + i;
            address.add(addr);
        }
        return address;
    }
	/**
	* 测试数据
	*/
    private List<String[]> getUsers(){
        List<String[]> users = new ArrayList<>();
        for(int i = 0; i < 10; i++){
            String[] user = new String[4];
            user[0] = "zhangsan - " + i;
            user[1] = "男";
            user[2] = "2" + i;
            user[3] = "Java - " + i;
            users.add(user);
        }
        return users;
    }

给单元格添加批注

	@SuppressWarnings("resource")
	private void download(HttpServletResponse resp) throws IOException 
	{
		XSSFWorkbook  workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("用户表");
		XSSFRow row = sheet.createRow(0);
		
        XSSFDrawing p=sheet.createDrawingPatriarch();
       
        XSSFCell cel0 = row.createCell(13);
        XSSFComment comment=p.createCellComment(new XSSFClientAnchor(0,0,0,0,(short)0,0,(short)2,4));
        comment.setString(new XSSFRichTextString("选填项\r\n最多512个字符"));
		cel0.setCellValue("名称");
		cel0.setCellComment(comment);
		
		XSSFCell cel1 = row.createCell(14);
		XSSFComment comment1=p.createCellComment(new XSSFClientAnchor(0,0,0,0,(short)1,0,(short)3,4));
		comment1.setString(new XSSFRichTextString("选填项\r\n最多512个字符"));
		cel1.setCellValue("年龄");
		cel1.setCellComment(comment1);
		
		// 批注相关
		XSSFCell tmpCell = null;
		XSSFComment tmpComment = null;
		XSSFRichTextString tmpVal = new XSSFRichTextString("选填项\r\n最多512个字符");
		int dx1 = 80;
		int dx2 = 140;
		short col1 = 15; // 批注开始的列,即15列
		short col2 = 17; // 批注结束的列,即17列
		// 动态创建列和列上的批注
		for(int i = 1; i <= 5; i++)
		{
			tmpCell = row.createCell(row.getLastCellNum());
			tmpComment = p.createCellComment(new XSSFClientAnchor(dx1,0,dx2,0, col1,0, col2,4));
			tmpCell.setCellValue("扩展裂-" + i);
			tmpComment.setString(tmpVal);
			col1++;
			col2++;
		}
		
		setResponseHeader(resp, "用户表.xlsx");
	    OutputStream tmpOutputStream = resp.getOutputStream();
	    workbook.write(tmpOutputStream);
        tmpOutputStream.flush();
        tmpOutputStream.close();
	}

 

注意: 创建批注的类 XSSFClientAnchor 的构造参数有四个 :

public XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2){....}

它们的作用如下:

    dx1,dy1 :起始单元格中的 x, y 坐标.

    dx2,dy2 :结束单元格中的 x , y 坐标

    col1,row1 :指定起始的单元格,下标从0开始

    col2,,row2 :指定结束的单元格 ,下标从0开始

 

 

© 著作权归作者所有

TSMYK
粉丝 101
博文 84
码字总数 207009
作品 0
成都
程序员
私信 提问
加载中

评论(1)

windyear
windyear
很完整的一个例子,注释也很清楚!收获很大,谢谢!
Java程序员从笨鸟到菜鸟之(一百零四)java操作office和pdf文件(二)利用POI实现数据导出excel报表

在上一篇博客中,我们简单介绍了java读取word,excel和pdf文档内容 ,但在实际开发中,我们用到最多的是把数据库中数据导出excel报表形式。不仅仅简单的读取office中的数据.尤其是在生产管理...

长平狐
2012/11/12
1K
0
使用 Apache 的 POI 和 HSSF 将 Excel 电子表格数据加载到 DB2

简介 在 developerWorks DB2 上本文的 先行篇 中,我们研究了 Apache 的 POI 项目的开放源代码产品。期间,我们开始在了解 SQL 的技术专家和通常将电子表格选作工具的商务专家之间架起一座桥...

红薯
2008/12/05
957
0
Java Apache POI 操作 Excel 导出

版权声明:本文首发 http://asing1elife.com ,转载请注明出处。 https://blog.csdn.net/asing1elife/article/details/82655653 Java Apache POI 操作 Excel 导出 Java 可以通过 Apache POI......

asing1elife
2018/09/12
0
0
jxl导入/导出Excel(Maven)

About jxl is a pure java 5 super-project that includes Enclosure, Peermi, Enshell, and Xlp. jxl && POI jxl是一个开源的Java Excel API项目,通过Jxl,Java可以很方便的操作微软的Exc......

coderzs
2017/08/31
0
0
GrapeCity Documents for Excel 与 Apache POI 功能对比

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 https://blog.csdn.net/powertoolsteam/article/details/95194828 GrapeCity Documents for ...

powertoolsteam
07/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

PostgreSQL 11.3 locking

rudi
今天
5
0
Mybatis Plus sql注入器

一、继承AbstractMethod /** * @author beth * @data 2019-10-23 20:39 */public class DeleteAllMethod extends AbstractMethod { @Override public MappedStatement injectMap......

一个yuanbeth
今天
11
1
一次写shell脚本的经历记录——特殊字符惹的祸

本文首发于微信公众号“我的小碗汤”,扫码文末二维码即可关注,欢迎一起交流! redis在容器化的过程中,涉及到纵向扩pod实例cpu、内存以及redis实例的maxmemory值,statefulset管理的pod需要...

码农实战
今天
4
0
为什么阿里巴巴Java开发手册中不建议在循环体中使用+进行字符串拼接?

之前在阅读《阿里巴巴Java开发手册》时,发现有一条是关于循环体中字符串拼接的建议,具体内容如下: 那么我们首先来用例子来看看在循环体中用 + 或者用 StringBuilder 进行字符串拼接的效率...

武培轩
今天
9
0
队列-链式(c/c++实现)

队列是在线性表功能稍作修改形成的,在生活中排队是不能插队的吧,先排队先得到对待,慢来得排在最后面,这样来就形成了”先进先出“的队列。作用就是通过伟大的程序员来实现算法解决现实生活...

白客C
今天
81
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部