java无实体类从数据库导出数据到excle
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();
}
}
}
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
1
0- 0
已为社区贡献1条内容
所有评论(0)