最近做了一个导出Excel的小功能，以前没接触过，现在分享下自己的代码，想让各位帮忙看看有啥地方可以优化，也方便自己以后查阅...
首先是excelAction的代码:
1 /** 2 * excelAction 3 * @author zhaoxz 4 * 5 */ 6 @Controller("excelAction") 7 @Scope("prototype") 8 public class ExcelAction extends BaseAction { 9 /**10 * 11 */12 private static final long serialVersionUID = -4596726723629076906L;13 private static final Logger logger = Logger.getLogger(ExcelAction.class);14 @Resource15 private CkdjService ckdjSrv;16 private ExportExcel ex = new ExportExcel();17 private Ckdj ckdj;18 private String exportBatch;19 private String fileName;20 private InputStream excelStream;21 22 23 public String execute() throws Exception {24 return SUCCESS;25 }26 27 /**28 * 将workbook转换为InputStream29 * @param workbook30 * @param fileName31 */32 private void workbookInputStream(HSSFWorkbook workbook, String fileName) {33 try {34 this.setFileName(URLEncoder.encode(fileName, "UTF-8"));35 ByteArrayOutputStream baos = new ByteArrayOutputStream();36 workbook.write(baos);37 baos.flush();38 byte[] aa = baos.toByteArray();39 this.excelStream = new ByteArrayInputStream(aa, 0, aa.length);40 baos.close();41 } catch (Exception e) {42 logger.error("转换失败！", e);43 }44 }45 46 /**************************** get set **************************************/47 48 /**49 * 获取文件流50 * @return51 */52 public InputStream getExcelStream() {53 try {54 String[] headers = 55 { "序号", "名称", "规格", "序列号", "数量", "单位", "出库人"};56 ckdj = ckdjSrv.findById(ckdj);57 System.out.println(getExportBatch());58 HSSFWorkbook workbook = ex.exportExcel("出库列表", headers, ckdj ,exportBatch);59 this.workbookInputStream(workbook, fileName);60 } catch (Exception e) {61 logger.error("获取文件流失败！", e);62 }63 return excelStream;64 }65 66 public void setExcelStream(InputStream excelStream) {67 this.excelStream = excelStream;68 }69 70 public Ckdj getCkdj() {71 return ckdj;72 }73 74 public void setCkdj(Ckdj ckdj) {75 this.ckdj = ckdj;76 }77 78 public String getFileName() {79 return fileName;80 }81 82 public void setFileName(String fileName) {83 this.fileName = fileName;84 }85 86 public void setCkdjSrv(CkdjService ckdjSrv) {87 this.ckdjSrv = ckdjSrv;88 }89 90 public String getExportBatch() {91 return exportBatch;92 }93 94 public void setExportBatch(String exportBatch) {95 this.exportBatch = exportBatch;96 }97 98 } 接着是struts2.xml文件
12 3 application/vnd.ms-excel4 excelStream5 attachment;filename="${fileName}.xls"6 1024 7 8
然后是ExportExcel.java类的代码：
1 /** 2 * 导出Excel文档 3 * @author zhaoxz 4 * 5 */ 6 @SuppressWarnings("unchecked") 7 public class ExportExcel { 8 9 /** 10 * 创建workbook 11 * @param title 12 * @param headers 13 * @param ckdj 14 */ 15 public HSSFWorkbook exportExcel(String title,String[] headers,Ckdj ckdj,String exportBatch){ 16 17 //声明一个工作薄 18 HSSFWorkbook workbook = new HSSFWorkbook(); 19 //生成一个表格 20 HSSFSheet sheet = workbook.createSheet(title); 21 //设置表格默认列宽为15个字节 22 sheet.setDefaultColumnWidth(15); 23 //生成一个样式 24 HSSFCellStyle style = workbook.createCellStyle(); 25 //设置样式 26 style.setFillForegroundColor(HSSFColor.WHITE.index); 27 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 28 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 29 style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 30 style.setBorderRight(HSSFCellStyle.BORDER_THIN); 31 style.setBorderTop(HSSFCellStyle.BORDER_THIN); 32 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 33 //生成一个字体 34 HSSFFont font = workbook.createFont(); 35 font.setColor(HSSFColor.BLACK.index); 36 font.setFontHeightInPoints((short)14); 37 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 38 //把字体应用到当前样式 39 style.setFont(font); 40 41 // 生成并设置另一个样式 42 HSSFCellStyle style2 = workbook.createCellStyle(); 43 style2.setFillForegroundColor(HSSFColor.WHITE.index); 44 style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 45 style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); 46 style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); 47 style2.setBorderRight(HSSFCellStyle.BORDER_THIN); 48 style2.setBorderTop(HSSFCellStyle.BORDER_THIN); 49 style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); 50 style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 51 // 生成另一个字体 52 HSSFFont font2 = workbook.createFont(); 53 font2.setFontHeightInPoints((short)12); 54 font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 55 // 把字体应用到当前的样式 56 style2.setFont(font2); 57 58 // 生成并设置另一个样式 59 HSSFCellStyle style3 = workbook.createCellStyle(); 60 style3.setFillForegroundColor(HSSFColor.WHITE.index); 61 style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 62 style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); 63 style3.setBorderLeft(HSSFCellStyle.BORDER_THIN); 64 style3.setBorderRight(HSSFCellStyle.BORDER_THIN); 65 style3.setBorderTop(HSSFCellStyle.BORDER_THIN); 66 style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); 67 style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 68 // 生成另一个字体 69 HSSFFont font3 = workbook.createFont(); 70 font3.setFontHeightInPoints((short)12); 71 font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 72 // 把字体应用到当前的样式 73 style3.setFont(font3); 74 75 // 生成并设置另一个样式 样式4 76 HSSFCellStyle style4 = workbook.createCellStyle(); 77 style4.setFillForegroundColor(HSSFColor.WHITE.index); 78 style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 79 style4.setBorderBottom(HSSFCellStyle.BORDER_THIN); 80 style4.setBorderLeft(HSSFCellStyle.BORDER_THIN); 81 style4.setBorderRight(HSSFCellStyle.BORDER_THIN); 82 style4.setBorderTop(HSSFCellStyle.BORDER_THIN); 83 style4.setAlignment(HSSFCellStyle.ALIGN_CENTER); 84 style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 85 // 生成另一个字体 86 HSSFFont font4 = workbook.createFont(); 87 font4.setFontHeightInPoints((short)11); 88 // 把字体应用到当前的样式 89 style4.setFont(font4); 90 91 //产生表格标题行 92 HSSFRow row = sheet.createRow(0); 93 HSSFCell cel = row.createCell(0); 94 cel.setCellStyle(style); 95 cel.setCellValue("天维讯达无线电设备检测（北京）有限责任公司"); 96 HSSFCell c2 = row.createCell(1); 97 c2.setCellStyle(style); 98 HSSFCell c3 = row.createCell(2); 99 c3.setCellStyle(style);100 HSSFCell c4 = row.createCell(3);101 c4.setCellStyle(style);102 HSSFCell c5 = row.createCell(4);103 c5.setCellStyle(style);104 HSSFCell c6 = row.createCell(5);105 c6.setCellStyle(style);106 HSSFCell c7 = row.createCell(6);107 c7.setCellStyle(style);108 //合并单元格109 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));110 111 //2行112 HSSFRow row2 = sheet.createRow(1);113 HSSFCell cel2l = row2.createCell(0);114 cel2l.setCellStyle(style2);115 if(exportBatch.equals("0")){116 cel2l.setCellValue("出库单");117 }else{118 cel2l.setCellValue("出库单(第"+ckdj.getBatch()+"批)");119 }120 HSSFCell rc2 = row2.createCell(1);121 rc2.setCellStyle(style);122 HSSFCell rc3 = row2.createCell(2);123 rc3.setCellStyle(style);124 HSSFCell rc4 = row2.createCell(3);125 rc4.setCellStyle(style);126 HSSFCell rc5 = row2.createCell(4);127 rc5.setCellStyle(style);128 HSSFCell rc6 = row2.createCell(5);129 rc6.setCellStyle(style);130 HSSFCell rc7 = row2.createCell(6);131 rc7.setCellStyle(style);132 sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); //合并单元格133 134 //3行135 HSSFRow rowhead = sheet.createRow(2);136 sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4)); //合并单元格137 //3行左138 HSSFCell cel3l = rowhead.createCell(0);139 cel3l.setCellStyle(style3);140 cel3l.setCellValue("所属项目："+ckdj.getProjectNumber());141 //3行右142 HSSFCell cel3r = rowhead.createCell(5);143 cel3r.setCellStyle(style2);144 cel3r.setCellValue("出库单号："+ckdj.getReleasingNumber());145 HSSFCell cel27 = rowhead.createCell(6);146 cel27.setCellStyle(style2);147 sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));148 149 HSSFRow rowbody = sheet.createRow(3);150 for (int i = 0; i < headers.length; i++) {151 HSSFCell cell = rowbody.createCell(i);152 cell.setCellStyle(style2);153 HSSFRichTextString text = new HSSFRichTextString(headers[i]);154 cell.setCellValue(text);155 }156 157 //遍历集合数据，产生数据行158 Iterator it = ckdj.getGoods().iterator();159 int index = 3;160 while (it.hasNext()) {161 index++;162 row = sheet.createRow(index);163 CkdjDetailed t = (CkdjDetailed)it.next();164 165 //创建单元格166 HSSFCell cell0 = row.createCell(0);167 cell0.setCellStyle(style4);168 cell0.setCellValue(t.getNum());169 170 HSSFCell cell1 = row.createCell(1);171 cell1.setCellStyle(style4);172 cell1.setCellValue(t.getEqName());173 174 HSSFCell cell2 = row.createCell(2);175 cell2.setCellStyle(style4);176 cell2.setCellValue(t.getEqStandard());177 178 HSSFCell cell3 = row.createCell(3);179 cell3.setCellStyle(style4);180 cell3.setCellValue(t.getEqSernumber());181 182 HSSFCell cell4 = row.createCell(4);183 cell4.setCellStyle(style4);184 cell4.setCellValue(t.getEqNums());185 186 HSSFCell cell5 = row.createCell(5);187 cell5.setCellStyle(style4);188 cell5.setCellValue(t.getEqUnits());189 190 HSSFCell cell6 = row.createCell(6);191 cell6.setCellStyle(style4);192 cell6.setCellValue("");193 }194 HSSFRow last1 = sheet.createRow(index+3);195 HSSFCell lcel1 = last1.createCell(4);196 lcel1.setCellValue("送货人：");197 HSSFRow last2 = sheet.createRow(index+4);198 HSSFCell lcel2 = last2.createCell(4);199 lcel2.setCellValue("送货时间：");200 HSSFRow last3 = sheet.createRow(index+5);201 HSSFCell lcel3 = last3.createCell(4);202 lcel3.setCellValue("收货人：");203 HSSFRow last4 = sheet.createRow(index+6);204 HSSFCell lcel4 = last4.createCell(4);205 lcel4.setCellValue("收货时间：");206 return workbook;207 }208 209 /**210 * 创建仓库workbook211 * @param title212 * @param headers213 * @param rkdjs214 * @return215 */216 public HSSFWorkbook exportWareExcel(String title,String[] headers,List rkdjs){217 218 //声明一个工作薄219 HSSFWorkbook workbook = new HSSFWorkbook();220 //生成一个表格221 HSSFSheet sheet = workbook.createSheet(title);222 //设置表格默认列宽为15个字节223 sheet.setDefaultColumnWidth(15);224 //生成一个样式225 HSSFCellStyle style = workbook.createCellStyle();226 //设置样式227 style.setFillForegroundColor(HSSFColor.WHITE.index);228 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);229 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);230 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);231 style.setBorderRight(HSSFCellStyle.BORDER_THIN);232 style.setBorderTop(HSSFCellStyle.BORDER_THIN);233 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);234 //生成一个字体235 HSSFFont font = workbook.createFont();236 font.setColor(HSSFColor.BLACK.index);237 font.setFontHeightInPoints((short)14);238 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);239 //把字体应用到当前样式240 style.setFont(font);241 242 // 生成并设置另一个样式 243 HSSFCellStyle style2 = workbook.createCellStyle(); 244 style2.setFillForegroundColor(HSSFColor.WHITE.index); 245 style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 246 style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); 247 style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); 248 style2.setBorderRight(HSSFCellStyle.BORDER_THIN); 249 style2.setBorderTop(HSSFCellStyle.BORDER_THIN); 250 style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); 251 style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 252 // 生成另一个字体 253 HSSFFont font2 = workbook.createFont(); 254 font2.setFontHeightInPoints((short)12);255 font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);256 // 把字体应用到当前的样式 257 style2.setFont(font2);258 259 // 生成并设置另一个样式 260 HSSFCellStyle style3 = workbook.createCellStyle(); 261 style3.setFillForegroundColor(HSSFColor.WHITE.index); 262 style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 263 style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); 264 style3.setBorderLeft(HSSFCellStyle.BORDER_THIN); 265 style3.setBorderRight(HSSFCellStyle.BORDER_THIN); 266 style3.setBorderTop(HSSFCellStyle.BORDER_THIN); 267 style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); 268 style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 269 // 生成另一个字体 270 HSSFFont font3 = workbook.createFont(); 271 font3.setFontHeightInPoints((short)12);272 font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);273 // 把字体应用到当前的样式 274 style3.setFont(font3);275 276 // 生成并设置另一个样式 样式4277 HSSFCellStyle style4 = workbook.createCellStyle(); 278 style4.setFillForegroundColor(HSSFColor.WHITE.index); 279 style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 280 style4.setBorderBottom(HSSFCellStyle.BORDER_THIN); 281 style4.setBorderLeft(HSSFCellStyle.BORDER_THIN); 282 style4.setBorderRight(HSSFCellStyle.BORDER_THIN); 283 style4.setBorderTop(HSSFCellStyle.BORDER_THIN); 284 style4.setAlignment(HSSFCellStyle.ALIGN_CENTER); 285 style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 286 // 生成另一个字体 287 HSSFFont font4 = workbook.createFont(); 288 font4.setFontHeightInPoints((short)11);289 // 把字体应用到当前的样式 290 style4.setFont(font4);291 292 //列头293 HSSFRow rowbody = sheet.createRow(0);294 for (int i = 0; i < headers.length; i++) {295 HSSFCell cell = rowbody.createCell(i);296 cell.setCellStyle(style2);297 HSSFRichTextString text = new HSSFRichTextString(headers[i]);298 cell.setCellValue(text);299 }300 301 //遍历集合数据，产生数据行302 int index = 0;303 Iterator it = rkdjs.iterator();304 while (it.hasNext()) {305 index++;306 HSSFRow row = sheet.createRow(index);307 Rkdj t = (Rkdj) it.next();308 //创建单元格309 HSSFCell cell0 = row.createCell(0);310 cell0.setCellStyle(style4);311 cell0.setCellValue(t.getJoinTime());312 313 HSSFCell cell1 = row.createCell(1);314 cell1.setCellStyle(style4);315 cell1.setCellValue(t.getJoinNumbers());316 317 HSSFCell cell2 = row.createCell(2);318 cell2.setCellStyle(style4);319 cell2.setCellValue(t.getEquipmentName());320 321 HSSFCell cell3 = row.createCell(3);322 cell3.setCellStyle(style4);323 cell3.setCellValue(t.getStandard());324 325 HSSFCell cell4 = row.createCell(4);326 cell4.setCellStyle(style4);327 cell4.setCellValue(t.getSeriesNumber());328 329 HSSFCell cell5 = row.createCell(5);330 cell5.setCellStyle(style4);331 cell5.setCellValue(t.getLocation());332 333 HSSFCell cell6 = row.createCell(6);334 cell6.setCellStyle(style4);335 cell6.setCellValue(t.getAmount());336 337 HSSFCell cell7 = row.createCell(7);338 cell7.setCellStyle(style4);339 cell7.setCellValue(t.getStock());340 341 HSSFCell cell8 = row.createCell(8);342 cell8.setCellStyle(style4);343 cell8.setCellValue(t.getComments());344 }345 return workbook;346 }347 } 这样基本就没什么问题了，本人小白，希望能得到更多的指导!在此多谢了....