java无实体类从数据库导出数据到excle

操作excle需要poi包

ExportExcelUtil工具类：

package utils;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
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.HSSFColor;


public class ExportExcelUtil {


	public void exportExcel(String rootPath,String title, String[] headers,
			List<String[]> dataset, OutputStream out) {
		exportExcel(rootPath,title, headers, dataset, out, "yyyy-MM-dd");
	}


	/**
	 * 
	 * 
	 * @param title
	 *            表格标题名
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 */
	@SuppressWarnings("unchecked")
	public void exportExcel(String rootPath,String title, String[] headers,
			List<String[]> dataset, OutputStream out, String pattern) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 20);
		// 生成一个样式
//		HSSFCellStyle style = workbook.createCellStyle();
//		// 设置样式
//		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
//		// 生成一个字体
//		HSSFFont font = workbook.createFont();
//		font.setColor(HSSFColor.VIOLET.index);
//		font.setFontHeightInPoints((short) 12);
//		// 把字体应用到当前的样式
//		style.setFont(font);

		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置
		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
				0, 0, 0, (short) 4, 2, (short) 6, 5));
		// 设置注释内容
		comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		comment.setAuthor("leno");

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (short i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			//cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}

		// 遍历集合数据，产生数据行
		Iterator<String[]> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			String[] t = (String[]) it.next();
			for (short i = 0; i < t.length; i++) {
				HSSFCell cell = row.createCell(i);
				//cell.setCellStyle(style);
				// 判断值的类型后进行强制类型转换
				String textValue = t[i];
				 
				if(textValue.startsWith("upload/"))
				{
					// 有图片时，设置行高为50px;  
                    row.setHeightInPoints(50);   
					// 设置图片所在列宽度为80px,注意这里单位的一个换算
					sheet.setColumnWidth(i, (short) (35.7 * 80)); // 
					//sheet.autoSizeColumn(i);  
					BufferedInputStream bis;
					byte[] buf = null;
					try {
						bis = new BufferedInputStream(  
						        new FileInputStream(rootPath + textValue));
						buf = new byte[bis.available()];  
			            while ((bis.read(buf)) != -1) {}   
			            
			            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023,
								255, (short) i, index, (short) i, index);
						patriarch.createPicture(anchor,
								workbook.addPicture( buf,
						HSSFWorkbook.PICTURE_TYPE_JPEG));  
					} catch (Exception e) {  
						e.printStackTrace();
					}     
					
				} else {
					HSSFRichTextString richString = new HSSFRichTextString(
							textValue);
					HSSFFont font3 = workbook.createFont();
					font3.setColor(HSSFColor.BLUE.index);
					richString.applyFont(font3);
					cell.setCellValue(richString);
				}
				
			

			}
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
}

连接数据库工具类：

package utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
	private static Connection connection;
	public static Connection getConnection()
	{
		//加载驱动程序，下面的代码为加载JDBD-ODBC驱动程序
		try {
			Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
			//用适当的驱动程序连接到数据库，test"是系统dsn名
			String url="jdbc:mysql://localhost:3306/movies?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT" + "";
			//建立连接，类似于ASP中的创建数据库联接
			connection=DriverManager.getConnection(url,"root","zxcv1234..");
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}
	public static void closeConnection()
	{
		try {
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

dao:

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import utils.DBUtil;
import po.PersonMovie;

public class PersonMovieDAO {

	private static Connection conn = null;
	private static Statement stmt = null;

	public static List<HashMap<String, String>> getAllPersonMovies() {

		List<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM Person_Movie");

			ResultSetMetaData data = rs.getMetaData();

			while (rs.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				for (int i = 1; i <= data.getColumnCount(); i++) {// 数据库里从 1 开始
					String c = data.getColumnName(i);
					String v = rs.getString(c);
					System.out.println(c + ":" + v + "\t");
					map.put(c, v);
				}
				al.add(map);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
			System.err.println(e.getClass().getName() + ":" + e.getMessage());
		}
		return al;
	}
}

调用dao和工具类将查询结果导出到excle：

package dao;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Set;

import dao.PersonMovieDAO;
import po.PersonMovie;
import utils.ExportExcelUtil;

public class c {

	public static void main(String[] args) {

		PersonMovieDAO dao = new PersonMovieDAO();
		List<HashMap<String, String>>  list = dao.getAllPersonMovies();
		
		HashMap<String, String> titles = list.get(0);
		  Set<String> keySet = titles.keySet();
		  
	
		  Object[] o = keySet.toArray();
		  
		  String []headers = new String [keySet.size()];
		  for (int i = 0; i < keySet.size(); i++) {
			  
			   headers[i] = String.valueOf(o[i]);
			  }
		
		ExportExcelUtil ex = new ExportExcelUtil();
		String _title = "记录";
		
		List<String[]> dataset = new ArrayList<String[]>();
		for (int i = 0; i < list.size(); i++) {
			HashMap<String, String> values = list.get(i);
			String[] a = new String[headers.length];
			for(int j = 0 ; j < headers.length ; j++) {
			   
			    String key = headers[j];
			    String value = String.valueOf(values.get(key));
			    a[j] = value;
			
		}
			dataset.add(a);
		}
		OutputStream out = null;
		try {
			out = new FileOutputStream("C:\\Users\\yangd\\Desktop\\output.xls");
			ex.exportExcel("", _title, headers, dataset, out);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}