excel与java报表开发

来源：互联网发布：iphone怎么传文件到mac 编辑：程序博客网时间：2024/04/27 08:59

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.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();
HSSFSheet s = wb.createSheet();
createTag(fieldName,s);//写表格的头部
createValue(res,s);//获取数据集，然后获得数据，写文件
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)
{
HSSFRow row = s.createRow(0);
HSSFCell cell = null;
for(int i=0;i<tags.length;i++)
{
cell = row.createCell(i);
cell.setCellValue(tags[i]);
}
}
/**
* 设置表格内容
* @param res
* @param s
*/
private static void createValue(java.sql.ResultSet res,HSSFSheet s)
{
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(i-1);
Object obj = res.getObject(i);
cell.setCellValue(obj+"");
}
flag++;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

test

import com.jn.util.file.office.ExcelFile;
public class ExcelFileText {
public static void main(String [] args)
{
String sql = "select title_id,title,pub_id from titles";
DBAccess dba = new DBAccess();
ExcelFile.writeExcel("D://text",new String[]{"文章编号","文章主题","Pub_ID"},dba.query(sql));
dba.closeConn();
}
}