struts2+poi 对excel进行导出

最新推荐文章于 2017-06-21 14:40:29 发布

原创最新推荐文章于 2017-06-21 14:40:29 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍如何使用Struts2框架实现从网页表单数据到Excel文件的导出功能。通过具体代码示例展示了如何配置Struts2 Action来处理导出请求，并利用HSSFWorkbook进行Excel文件的创建及格式化。

页面的话传一个集合给action

页面list name取值格式为list[i].属性名由于用到struts2标签 i的值等于

<s:iterator value="carlist" status="num">
 <tr>
     <td><input type="text" name="carlist[<s:property value='#num.index'/>].//这里不用拼接直接用infolicense" value="${infolicense}" /><s:property value="infolicense"/> </td>
              <td><input type="text" name="carlist[<s:property value='#num.index'/>].tcmwholename" value="${ tcmwholename}" /><s:property value="tcmwholename" /> </td>
              <td><input type="text" name="carlist[<s:property value='#num.index'/>].attiname" value="${ attiname}" /><s:property value="attiname"/></td>
              <td><input type="text" name="carlist[<s:property value='#num.index'/>].deptname" value="${ deptname}" /><s:property value="deptname"/></td>
</tr>

private String fileNames;//提供get/set方法
private InputStream excelStream;//

public String doExport() throws TargetException{

String result = "";
String tmpContent="编号,车型,使用公司,使用部门";//标题
String tmpContentCn="编号,车型,使用公司,使用部门";
HSSFWorkbook workbook = printExcel(tmpContent,tmpContentCn,carlist);

if(workbook != null){
try{
Calendar c = Calendar.getInstance();
int year = c.get(Calendar.YEAR);
int month = c.get(Calendar.MONTH)+1;
String month_ = new String(""+month);
if(month<10){
month_ = "0"+month;
}
int day = c.get(Calendar.DAY_OF_MONTH);
String day_ = new String(""+day);
if(day<10){
day_ = "0"+day;
}
this.exportExcel(workbook,year+month_+""+day_+"sxta.xls");
result = "outExcel";
}catch(IOException e){
e.printStackTrace();
//message = "出错了！";
// redirectTo = this.getHttpServletRequest().getContextPath()+"/task/listProject.action";
//return SUCCESS;
}
}

return result;
}
private HSSFWorkbook printExcel(String tmpContent,String tmpContentCn,List dataList){
System.out.println("+++++++++++++++++++++"+carlist.size());
HSSFWorkbook workbook = null;
String[] titles_CN = tmpContentCn.split(",");
String[] titles_EN = tmpContent.split(",");
try{
//创建工作簿实例
workbook = new HSSFWorkbook();
//创建工作表实例
HSSFSheet sheet = workbook.createSheet("sxtaExcel");
sheet.setDefaultColumnWidth((short) 25);
//设置列宽
this.setSheetColumnWidth(titles_CN,sheet);
// 获取样式
HSSFCellStyle style = this.createTitleStyle(workbook);
HSSFCellStyle style1 = this.createTitleStyle1(workbook);
if(dataList != null){
//创建第一行标题
HSSFRow row = sheet.createRow((short)0);// 建立新行
for(int i=0;i<titles_CN.length;i++){
this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING,
this.getText(titles_CN[i]));
}
//给excel填充数据
for(int i=0;i<dataList.size();i++){
//将dataList里面的数据取出来
AppCarinfo c= (AppCarinfo)dataList.get(i);
HSSFRow row1 = sheet.createRow((short) (i + 1));// 建立新行

boolean isOverTime = false;
for(int j=0;j<titles_EN.length;j++){
String tmpstr = "";
if (titles_EN[j].equals("编号")){
System.out.println("======================");
this.createCell(row1, j, style1, HSSFCell.CELL_TYPE_STRING,c.getInfolicense());
}else if(titles_EN[j].equals("车型")){
this.createCell(row1, j, style1, HSSFCell.CELL_TYPE_STRING,c.getTcmwholename());
}else if(titles_EN[j].equals("使用公司")){
this.createCell(row1, j, style1, HSSFCell.CELL_TYPE_STRING,c.getAttiname());
}else if(titles_EN[j].equals("使用部门")){
this.createCell(row1, j, style1, HSSFCell.CELL_TYPE_STRING,c.getDeptname());
}
}

}
}else{
this.createCell(sheet.createRow(0), 0, style,HSSFCell.CELL_TYPE_STRING, "查无资料");
}
}catch(Exception e){
e.printStackTrace();
}
return workbook;
}
//设置列宽
private void setSheetColumnWidth(String[] titles_CN,HSSFSheet sheet){
//根据你数据里面的记录有多少列，就设置多少列
for(int i=0;i<titles_CN.length;i++){
sheet.setColumnWidth((short)i, (short) 3000);
}
}

//设置excel的title样式
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
private HSSFCellStyle createTitleStyle1(HSSFWorkbook wb) {
HSSFCellStyle style2 = wb.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = wb.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
return style2;
}

//创建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);
// style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 把字体应用到当前的样式
if (style != null) {
cell.setCellStyle(style);
}
switch(cellType){
case HSSFCell.CELL_TYPE_BLANK: {} break;
case HSSFCell.CELL_TYPE_STRING: {
System.out.println("进来了没");
cell.setCellValue(value.toString()+"");
}
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(value.toString()));}break;
default: break;
}
}
//写入输入流中
private void exportExcel(HSSFWorkbook workbook,String fileName) throws IOException{
fileNames = fileName;
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
baos.flush();
byte[] aa = baos.toByteArray();
excelStream = new ByteArrayInputStream(aa, 0, aa.length);
baos.close();

}

配置文件

<action name="doExport" class="actions.car.annualcheck.CarCheckAction" method="doExport">
<result name="outExcel" type="stream">
<param name="contentType">application/vnd.ms-excel</param> 
<param name="inputName">excelStream</param>
<param name="contentDisposition">attachment;filename="${fileNames}"</param>
<param name="bufferSize">1024</param>
</result>
</action>