java实现的数据库导出成excel功能

最新推荐文章于 2022-08-05 16:48:52 发布

sd_windy

最新推荐文章于 2022-08-05 16:48:52 发布

阅读量640

点赞数

分类专栏： java

java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

目前用的是poi来实现的导出操作

ExcelFile 类，用来封装了执行操作的过程

package poi;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelFile {
/**
* 写Excel操作
* @param fileName
* 文件名，文件要写入到的盘符和文件名，但不需要后缀名
* @param fieldName
* 表头名
* @param res
* 数据对象,java.sql.ResultSet
*/
public static void writeExcel(String fileName,String [] fieldName,java.sql.ResultSet res)
{
FileOutputStream fos =null;
try {
fos = new FileOutputStream(fileName+".xls");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle style3 = wb.createCellStyle(); // 创建单元格风格.
style3.setAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER); // /水平居中
HSSFSheet s = wb.createSheet();
createTag(fieldName,s,style3);//写表格的头部
createValue(res,s,style3);//获取数据集，然后获得数据，写文件
wb.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
finally
{
if(fos!=null)
{
try {
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

/**
* 创建表格表头
* @param tags
* @param s
*/
private static void createTag(String [] tags,HSSFSheet s, HSSFCellStyle style3)
{

HSSFRow row = s.createRow(0);
HSSFCell cell = null;

// cell0.setCellType()
for(int i=0;i<tags.length;i++)
{
cell = row.createCell((short)i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style3);
cell.setCellValue(tags[i]);
}
}

/**
* 设置表格内容
* @param res
* @param s
*/
private static void createValue(java.sql.ResultSet res,HSSFSheet s,HSSFCellStyle style3)
{
try {
int flag = 1;
int count = res.getMetaData().getColumnCount();
HSSFRow row = null;
HSSFCell cell = null;
while(res.next())
{
row = s.createRow(flag);
for(int i=1;i<=count;i++)
{
cell = row.createCell((short)(i-1));
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style3);
Object obj = res.getObject(i);
cell.setCellValue(obj+"");
}
flag++;
}
} catch (SQLException e) {
e.printStackTrace();
}

}

}

ExcelFileText 类用来测试：

package poi;

import java.sql.ResultSet;

import dao.PageMarDAO;
import dao.PageMarDAO.BaseDao;
import dao.PageMarDAO.ConnectionType;
import poi.ExcelFile;

public class ExcelFileText {
public static void main(String [] args)
{
PageMarDAO dao = PageMarDAO.getInstance(ConnectionType.jdbc);
String sql = "SELECT * FROM <table_name>";
BaseDao b = dao.getBaseDAO();
ResultSet rs = b.getResultSet(sql);
ExcelFile.writeExcel("F://text", new String[]{"列1","列2","列3","列4","列5","列6"}, rs);
}
}