使用Apache的POI,将数据导出至EXCEL
来源:互联网 发布:微信授权登录demo源码 编辑:程序博客网 时间:2024/06/05 04:34
没什么事干,写篇关于java中将数据库记录导出到Excel的,自己也再次回顾下手先得到Apache下载几个POI包放到 lib目录下下面直接贴代码相关代码,从数据库里面读记录的省略,假定我的List里面已经有数据了:import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//下面只贴出要导入POI的包,别的包自己导
public class ExcelDemo extends DispatchAction
{
/**
* 绘出Excel
*
* @param actionMapping
* @param actionForm
* @param request
* @param response
* @return
*/
public ActionForward exportSalaryBase(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest request,
HttpServletResponse response) {
try{
MessageResources resources (MessageResources)request.getAttribute(Globals.MESSAGES_KEY);
Locale locale = request.getLocale();
XXXForm form = (XXXForm) actionForm;
HSSFWorkbook workbook = exportExcel();
if(workbook != null){
this.printExcel(workbook,response,"ExcelDemo.xls");
}
}catch(Exception e){
request.setAttribute("ErrorMessage",e.getMessage());
}
return null;
}
//导出Excel
private printExcel(HSSFWorkbook workbook,HttpServletResponse response, String fileName) throw Excepton
{
OutputStream out = response.getOutputStream();
response.setHeader("Content-disposition","attachment; filename=" + fileName);
response.setContentType("application/msexcel;charset=UTF-8");
workbook.write(out);
out.flush();
out.close();
}
public HSSFWorkbook exportExcel() throw Exception
{
HSSFWorkbook workbook = null;
try
{
//这里的数据即时你要从后台取得的数据
List dataList = XXXXDAO.findXXXXByXXXX();
// 创建工作簿实例
workbook = new HSSFWorkbook();
// 创建工作表实例
HSSFSheet sheet = workbook.createSheet("exceldemo");
// 设置列宽
this.setSheetColumnWidth(sheet);
// 获取样式
HSSFCellStyle style = this.createTitleStyle(workbook);
//
if (dataList != null && dataList.size() > 0)
{
// 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
HSSFRow row = sheet.createRow((short) 0);// 建立新行
this.createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
this.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
//给excel填充数据
for(int i=0;i<dataList.size();i++)
{
//将dataList里面的数据取出来,假设这里取出来的是Model,也就是某个javaBean的意思啦
***Model model= (****Model)dataList.get(i);
HSSFRow row1 = sheet.createRow((short) (i + 1));// 建立新行
this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING,
model.get****());
this.createCell(row1, 1, style, HSSFCell.CELL_TYPE_STRING,
model.get****());
this.createCell(row1, 4, style, HSSFCell.CELL_TYPE_NUMERIC,
model.get****());
this.createCell(row1, 2, style, HSSFCell.CELL_TYPE_NUMERIC,
model.get****());
this.createCell(row1, 3, style, HSSFCell.CELL_TYPE_NUMERIC,
model.get****());
}else {
this.createCell(sheet.createRow(0), 0, style,
HSSFCell.CELL_TYPE_STRING, resources.getMessage(locale,
"查无资料"));
}
}
}catch(Exception e)
{
request.setAttribute("ErrorMessage",e.getMessage());
}
}
private void setSheetColumnWidth(HSSFSheet sheet)
{
//根据你数据里面的记录有多少列,就设置多少列
sheet.setColumnWidth((short) 0, (short) 3000);
sheet.setColumnWidth((short) 1, (short) 3000);
sheet.setColumnWidth((short) 2, (short) 3000);
sheet.setColumnWidth((short) 3, (short) 3000);
sheet.setColumnWidth((short) 4, (short) 5000);
}
//设置excel的title样式
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 200);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
return style;
}
//创建Excel单元格
private void createCell(HSSFRow row, int column, HSSFCellStyle style,
int cellType, Object value) {
HSSFCell cell = row.createCell((short) column);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (style != null) {
cell.setCellStyle(style);
}
switch (cellType) {
case HSSFCell.CELL_TYPE_BLANK: {
}
break;
case HSSFCell.CELL_TYPE_STRING: {
cell.setCellValue(value.toString());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
// DecimalFormat format = new DecimalFormat("###,##0.00");
// cell.setCellValue(Float.parseFloat(value.toString()));
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
default:
break;
}
}
}哎,总算写完了,这里总结一下1. 首先要获得HSSFWorkbook----->(通过createSheet())------->得到HSSFSheet--------->(通过createRow())
---------->得到HSSFRow--------->(通过createCell--------->得到HSSFCell
2. 然后给每个单元格填充值
3. 用response发布就好啦
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//下面只贴出要导入POI的包,别的包自己导
public class ExcelDemo extends DispatchAction
{
/**
* 绘出Excel
*
* @param actionMapping
* @param actionForm
* @param request
* @param response
* @return
*/
public ActionForward exportSalaryBase(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest request,
HttpServletResponse response) {
try{
MessageResources resources (MessageResources)request.getAttribute(Globals.MESSAGES_KEY);
Locale locale = request.getLocale();
XXXForm form = (XXXForm) actionForm;
HSSFWorkbook workbook = exportExcel();
if(workbook != null){
this.printExcel(workbook,response,"ExcelDemo.xls");
}
}catch(Exception e){
request.setAttribute("ErrorMessage",e.getMessage());
}
return null;
}
//导出Excel
private printExcel(HSSFWorkbook workbook,HttpServletResponse response, String fileName) throw Excepton
{
OutputStream out = response.getOutputStream();
response.setHeader("Content-disposition","attachment; filename=" + fileName);
response.setContentType("application/msexcel;charset=UTF-8");
workbook.write(out);
out.flush();
out.close();
}
public HSSFWorkbook exportExcel() throw Exception
{
HSSFWorkbook workbook = null;
try
{
//这里的数据即时你要从后台取得的数据
List dataList = XXXXDAO.findXXXXByXXXX();
// 创建工作簿实例
workbook = new HSSFWorkbook();
// 创建工作表实例
HSSFSheet sheet = workbook.createSheet("exceldemo");
// 设置列宽
this.setSheetColumnWidth(sheet);
// 获取样式
HSSFCellStyle style = this.createTitleStyle(workbook);
//
if (dataList != null && dataList.size() > 0)
{
// 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
HSSFRow row = sheet.createRow((short) 0);// 建立新行
this.createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
this.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING,
resources.getMessage(locale, "*****"));
//给excel填充数据
for(int i=0;i<dataList.size();i++)
{
//将dataList里面的数据取出来,假设这里取出来的是Model,也就是某个javaBean的意思啦
***Model model= (****Model)dataList.get(i);
HSSFRow row1 = sheet.createRow((short) (i + 1));// 建立新行
this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING,
model.get****());
this.createCell(row1, 1, style, HSSFCell.CELL_TYPE_STRING,
model.get****());
this.createCell(row1, 4, style, HSSFCell.CELL_TYPE_NUMERIC,
model.get****());
this.createCell(row1, 2, style, HSSFCell.CELL_TYPE_NUMERIC,
model.get****());
this.createCell(row1, 3, style, HSSFCell.CELL_TYPE_NUMERIC,
model.get****());
}else {
this.createCell(sheet.createRow(0), 0, style,
HSSFCell.CELL_TYPE_STRING, resources.getMessage(locale,
"查无资料"));
}
}
}catch(Exception e)
{
request.setAttribute("ErrorMessage",e.getMessage());
}
}
private void setSheetColumnWidth(HSSFSheet sheet)
{
//根据你数据里面的记录有多少列,就设置多少列
sheet.setColumnWidth((short) 0, (short) 3000);
sheet.setColumnWidth((short) 1, (short) 3000);
sheet.setColumnWidth((short) 2, (short) 3000);
sheet.setColumnWidth((short) 3, (short) 3000);
sheet.setColumnWidth((short) 4, (short) 5000);
}
//设置excel的title样式
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 200);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
return style;
}
//创建Excel单元格
private void createCell(HSSFRow row, int column, HSSFCellStyle style,
int cellType, Object value) {
HSSFCell cell = row.createCell((short) column);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (style != null) {
cell.setCellStyle(style);
}
switch (cellType) {
case HSSFCell.CELL_TYPE_BLANK: {
}
break;
case HSSFCell.CELL_TYPE_STRING: {
cell.setCellValue(value.toString());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
// DecimalFormat format = new DecimalFormat("###,##0.00");
// cell.setCellValue(Float.parseFloat(value.toString()));
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
default:
break;
}
}
}哎,总算写完了,这里总结一下1. 首先要获得HSSFWorkbook----->(通过createSheet())------->得到HSSFSheet--------->(通过createRow())
---------->得到HSSFRow--------->(通过createCell--------->得到HSSFCell
2. 然后给每个单元格填充值
3. 用response发布就好啦
- 使用Apache的POI,将数据导出至EXCEL
- 使用poi将数据导出到excel
- Apache POI实现数据的Excel导出
- 使用Apache的poi导出Excel
- 使用Apache POI导出Excel数据有BUG
- 使用POI将数据导出到Excel文件
- 使用poi将jtable数据导出到excel中
- Java中使用Apache POI实现数据的Excel导入和导出
- 用apache poi导出数据到Excel
- 使用poi将数据导出为excel文件的几点注意
- Java使用POI将数据导出到Excel的工具方法
- 使用Apache POI,实现导出Excel的功能
- 使用apache的poi包导出excel表格
- Jenke 使用apache的poi实现导入导出excel
- 使用apache的poi实现导入导出excel
- 使用apache的poi实现导入导出excel
- 使用Apache POI读取Excel文件,将读取的数据转换成json写入文本文件
- poi将数据库数据导出excel文件
- 网页中插入FLASH代码的参数详解
- 回调函数
- 40种网站设计常用技巧
- 缓存技术
- 非常简单的让网站排名靠前的办法
- 使用Apache的POI,将数据导出至EXCEL
- 在JSTL中使用JavaBean
- 来了好久,终于写文章了!
- SortedList 类
- top 命令详解
- 什么是Web服务
- sar命令详解
- SQL存储过程生成流水号
- 在php中让用户自定义时区 正确显示当前时间