Struts 2导出EXCEL

最新推荐文章于 2019-06-06 15:34:00 发布

转载最新推荐文章于 2019-06-06 15:34:00 发布 · 1.2k 阅读

文章标签：

#struts #string #exception #null #excel #resources

POI相关专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个使用Java实现的导出数据到Excel的具体案例，包括如何创建Excel工作簿、设置样式、填充数据及导出文件等关键步骤。

import java.io.IOException; import java.io.OutputStream; import java.util.List; import javax.servlet.http.HttpServletResponse; 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; import org.apache.struts2.ServletActionContext; import com.neusoft.dcd.css.common.BaseAction; import com.neusoft.dcd.css.model.management.user.UserBaseInfo; import com.neusoft.dcd.css.model.tsc.Tsc; import com.neusoft.dcd.css.service.tsc.TscService; import com.opensymphony.xwork2.Preparable; public class ExportExcel { private static final long serialVersionUID = 1L; private TscService cssTscService; public void prepare() throws Exception { } /** * 绘出Excel * * @param actionMapping * @param actionForm * @param request * @param response * @return * @throws Exception */ public String init() throws Exception { UserBaseInfo userinfo = (UserBaseInfo) ServletActionContext .getRequest().getSession().getAttribute("UserBaseInfo"); if(userinfo!=null){ HttpServletResponse response = ServletActionContext.getResponse(); List dataList=cssTscService.queryForProTsclb(userinfo.getUserId()); HSSFWorkbook workbook = exportExcel(dataList); if(workbook!= null){ this.printExcel(workbook,response,"ExcelTsc.xls"); } } return SUCCESS; } // 导出Excel private void printExcel(HSSFWorkbook workbook,HttpServletResponse response, String string) throws IOException { OutputStream out = response.getOutputStream(); response.setHeader("Content-disposition","attachment; filename=" + "TSC.xls"); response.setContentType("application/msexcel;charset=UTF-8"); workbook.write(out); out.flush(); out.close(); } public HSSFWorkbook exportExcel(List dataList) throws Exception { HSSFWorkbook workbook = null; try { // 这里的数据即时你要从后台取得的数据 // 创建工作簿实例 workbook = new HSSFWorkbook(); // 创建工作表实例 HSSFSheet sheet = workbook.createSheet("TscExcel"); // 设置列宽 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, this.getText("css.tsc.asktime")); this.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.productname")); this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.passsort")); this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.askusername")); this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.dtype")); this.createCell(row, 5, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.askone")); this.createCell(row, 6, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.department")); this.createCell(row, 7, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.requesttime")); this.createCell(row, 8, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.answertime")); this.createCell(row, 9, style, HSSFCell.CELL_TYPE_STRING, this.getText("css.tsc.requesttow")); // 给excel填充数据 for(int i=0;i<dataList.size();i++) { // 将dataList里面的数据取出来，假设这里取出来的是Model,也就是某个javaBean的意思啦 Tsc model= (Tsc)dataList.get(i); HSSFRow row1 = sheet.createRow((short) (i + 1));// 建立新行 if(model.getAsktime()!=null) this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING, model.getAsktime()); if(model.getProductname()!=null) this.createCell(row1, 1, style, HSSFCell.CELL_TYPE_STRING, model.getProductname()); if(model.getPasssort()!=null) this.createCell(row1, 2, style, HSSFCell.CELL_TYPE_STRING, model.getPasssort()); if(model.getAskusername()!=null) this.createCell(row1, 3, style, HSSFCell.CELL_TYPE_STRING, model.getAskusername()); if(model.getDtype()!=null) this.createCell(row1, 4, style, HSSFCell.CELL_TYPE_STRING, model.getDtype()); if(model.getAskone()!=null) this.createCell(row1, 5, style, HSSFCell.CELL_TYPE_STRING, model.getAskone()); if(model.getDepartment()!=null) this.createCell(row1, 6, style, HSSFCell.CELL_TYPE_STRING, model.getDepartment()); if(model.getRequesttime()!=null) this.createCell(row1, 7, style, HSSFCell.CELL_TYPE_STRING, model.getRequesttime()); if(model.getAnswertime()!=null) this.createCell(row1, 8, style, HSSFCell.CELL_TYPE_STRING, model.getAnswertime()); if(model.getRequesttow()!=null) this.createCell(row1, 9, style, HSSFCell.CELL_TYPE_STRING, model.getRequesttow()); } } else { this.createCell(sheet.createRow(0), 0, style, HSSFCell.CELL_TYPE_STRING, "查无资料"); } }catch(Exception e) { e.printStackTrace(); } return workbook; } 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); sheet.setColumnWidth((short) 5, (short) 5000); sheet.setColumnWidth((short) 6, (short) 5000); sheet.setColumnWidth((short) 7, (short) 5000); sheet.setColumnWidth((short) 8, (short) 5000); sheet.setColumnWidth((short) 9, (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; } } public void setCssTscService(TscService cssTscService) { this.cssTscService = cssTscService; } }