通过EasyExcel实现导出数据

最新推荐文章于 2024-05-23 15:58:06 发布

KillerCodes

最新推荐文章于 2024-05-23 15:58:06 发布

阅读量290

点赞数 3

文章标签：笔记

本文链接：https://blog.csdn.net/aaassslll147/article/details/135225088

版权

package util;

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.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

public class CellStyleUtils {
	public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
		WriteCellStyle headWriteCellStyle=new WriteCellStyle();
		headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
		headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
		headWriteCellStyle.setBorderRight(BorderStyle.THIN);
		headWriteCellStyle.setBorderTop(BorderStyle.THIN);
		
		headWriteCellStyle.setWrapped(true);
		WriteFont headWriteFont=new WriteFont();
		headWriteFont.setBold(true);
		headWriteFont.setFontName("宋体");
		headWriteFont.setFontHeightInPoints((short)12);
		headWriteCellStyle.setWriteFont(headWriteFont);
		
		WriteCellStyle contentWriteCellStyle=new WriteCellStyle();
		contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
		contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
		contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
		contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
		contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
		
		contentWriteCellStyle.setWrapped(true);
		WriteFont contentWriteFont=new WriteFont();
		contentWriteFont.setFontHeightInPoints((short)12);
		contentWriteFont.setFontName("宋体");
		contentWriteCellStyle.setWriteFont(contentWriteFont);
		return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
		
	}
}

package util;

import java.util.List;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;

public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy{

	@Override
	protected void setColumnWidth(WriteSheetHolder WriteSheetHolder, List<CellData> cellDataList, Cell cell, Head head, 
			Integer relativeRowIndex,Boolean isHead) {
		// TODO Auto-generated method stub
		Sheet sheet=WriteSheetHolder.getSheet();
		sheet.setColumnWidth(cell.getColumnIndex(), 5000);
	}

}

package util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.*;
public class ModelConvert{
	/用于结果集转换，放多条记录
	public static List<Map<String,Object>> convertList(ResultSet rs){
		//新建一个map，list集合用于存放多条查询记录
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		try{
		//结果集rs的结构信息，比如字段数，字段名等
		ResultSetMetaData md=(ResultSetMetaData) rs.getMetaData();
		int columnCount=md.getColumnCount();//取得查询出来的字段个数
		while(rs.next){
				//新建一个Map集合，将查询出来的内容按照字段名：值的键值对存储在map集合中
				Map<String,Object> rowData=new HashMap<String,Object>();
				for(int i=1;i<=columnCount;i++){
					rowData.put(md.getColumnName(i),rs.getObject(i));
				}
				list.add(rowData);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null) rs.close;
				rs=null;
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return list;
	}
	public List<Map<String,Object>> findAll(){
		Connection conn=null;
		try{
			conn=JDBCUtil.getConn();
			String sql="select * from data1";
			PreparedStatement pstm=conn.prepareStatement(sql);
			ResultSet rs=pstm.executeQuery();
			List<Map<String,Object>> list=convertList(rs);
			return list;
		}catch(SQLException e){
			e.printStackTrace();
			throw new RuntimeException("查询用户失败",e);
		}finally{
		JdbcUtil.close(conn);
		}
	}
}

package test;
import java.util.*;
import com.google.common.collect.Lists;
import util.ModelConvert;
public class LastProductRequestTime{
	List<List<String>> headTities=Lists.newArrayList();
	String aliasInfo="产品线";
	headTities.add(Lists.newArrayList(aliasInfo,aliasInfo));
	return headTities;
}
public static List<List<Object>> contentData3(){
	List<List<Object>> contentList=Lists.newArrayList();
	ModelConvert dao=new ModelConvert();
	List<Map<String,Object>> list=dao.findLastProducts();
	for(Map<String,Object> map:list){
		contentList.add(Lists.newArrayList(map.get("alias")));
	}
	return contentList;
}
public static void LastProductsReqsBugs() throws IOException{
	OutputStream out3=new FileOutputStream("d://LastProductsReqsBugs.xlsx");
	EasyExcel.write(out3).needHead(true)
	.head(head3())
	.excelType(ExcelTypeEnum.XLSX)
	.registerWriteHandler(CellStyleUtils.getHorizontalCellStyleStrategy()).
	.registerWriteHandler(new ExcelWidthStyleStrategy())
	.sheet(0,"延期问题和需求表")
	.dowrite(contentData3());
	out3.close();
}