手先得到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发布就好啦