j2e中操作EXCEL

在j2e中操作excel，无非2种情况，在这里我贴部分代码做个例子就OK，不管是导入和导出都是操作的都是流
1，导入，浏览器输入EXCEL到java后台解析

package action;import java.io.OutputStream;import java.sql.ResultSet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.struts.action.Action;import org.apache.struts.action.ActionForm;import org.apache.struts.action.ActionForward;import org.apache.struts.action.ActionMapping;import bean.ExcelBean;import bean.SQLBean;public class DownAction extends Action{public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception{SQLBean sq = new SQLBean();String sql = "select * from detial";try{String fname = "detial";// Excel文件名OutputStream os = response.getOutputStream();// 取得输出流response.reset();// 清空输出流response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xls");// 设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。response.setContentType("application/msexcel");// 定义输出类型ResultSet res = sq.select(sql);ExcelBean eb = new ExcelBean();eb.createFixationSheet(res, os);// 调用生成excel文件beanres.close();}catch (Exception e){System.out.println(e);}return mapping.findForward("display");}}

package bean;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.sql.ResultSet;import java.sql.SQLException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.Region;public class ExcelBean{private HSSFWorkbook wb = null;public ExcelBean(){wb = new HSSFWorkbook();}public void createFixationSheet(ResultSet res, OutputStream os) throws IOException{HSSFSheet sheet = wb.createSheet("new sheet");wb.setSheetName(0, "话费详单", HSSFWorkbook.ENCODING_UTF_16);HSSFRow row = sheet.createRow((short) 0);sheet.createFreezePane(0, 1);cteateCell(wb, row, (short) 0, "手机号码");cteateCell(wb, row, (short) 1, "呼叫类型");cteateCell(wb, row, (short) 2, "对方号码");cteateCell(wb, row, (short) 3, "起始时间");cteateCell(wb, row, (short) 4, "通话时间");cteateCell(wb, row, (short) 5, "通话地点");cteateCell(wb, row, (short) 6, "长途类型");cteateCell(wb, row, (short) 7, "基本话费");cteateCell(wb, row, (short) 8, "长话费");cteateCell(wb, row, (short) 9, "总话费");int ii = 0;try{int i = 0;ii = res.getMetaData().getColumnCount();while (res.next()){i++;HSSFRow row2 = sheet.createRow((short) i);for (int j = 0; j < ii; j++){String ss = "";if (res.getString(j + 1) == null)ss = "空  null";elsess = res.getString(j + 1);cteateCell(wb, row2, (short) j, ss);}}}catch (SQLException e){e.printStackTrace();}wb.write(os);os.flush();os.close();}private void cteateCell(HSSFWorkbook wb, HSSFRow row, short col, String val){HSSFCell cell = row.createCell(col);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(val);HSSFCellStyle cellstyle = wb.createCellStyle();cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);cell.setCellStyle(cellstyle);}}

2，导出，java后台生成excel输入到浏览器

public class UserAction extends ActionSupport{//导出excel，这里就是说下载模板，里面的东西是空的public String export() throws Exception{Connection con = null;try{con = dbUtil.getCon();Workbook wb = new HSSFWorkbook();String headers[] = { "编号", "姓名", "电话", "Email", "QQ" };ResultSet rs = userDao.userList(con, null);ExcelUtil.fillExcelData(rs, wb, headers);ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");}catch (Exception e){// TODO Auto-generated catch blocke.printStackTrace();}finally{try{dbUtil.closeCon(con);}catch (Exception e){// TODO Auto-generated catch blocke.printStackTrace();}}return null;}//模板不是说生成的，是放在服务器上的一个模板，直接读下就OKpublic String export2() throws Exception{Connection con = null;try{con = dbUtil.getCon();ResultSet rs = userDao.userList(con, null);Workbook wb = ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls");}catch (Exception e){// TODO Auto-generated catch blocke.printStackTrace();}finally{try{dbUtil.closeCon(con);}catch (Exception e){// TODO Auto-generated catch blocke.printStackTrace();}}return null;}//上传excel模板，后台解析excelpublic String upload() throws Exception{POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("上传的file"));HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页if (hssfSheet != null){for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++){HSSFRow hssfRow = hssfSheet.getRow(rowNum);if (hssfRow == null){continue;}User user = new User();user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));Connection con = null;try{con = dbUtil.getCon();userDao.userAdd(con, user);}catch (Exception e){e.printStackTrace();}finally{dbUtil.closeCon(con);}}}JSONObject result = new JSONObject();result.put("success", "true");ResponseUtil.write(ServletActionContext.getResponse(), result);return null;}}

public class ExcelUtil {public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{int rowIndex=0;Sheet sheet=wb.createSheet();Row row=sheet.createRow(rowIndex++);for(int i=0;i<headers.length;i++){row.createCell(i).setCellValue(headers[i]);}while(rs.next()){row=sheet.createRow(rowIndex++);for(int i=0;i<headers.length;i++){row.createCell(i).setCellValue(rs.getObject(i+1).toString());}}}public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java1234/template/"+templateFileName);POIFSFileSystem fs=new POIFSFileSystem(inp);Workbook wb=new HSSFWorkbook(fs);Sheet sheet=wb.getSheetAt(0);// 获取列数int cellNums=sheet.getRow(0).getLastCellNum();int rowIndex=1;while(rs.next()){Row row=sheet.createRow(rowIndex++);for(int i=0;i<cellNums;i++){row.createCell(i).setCellValue(rs.getObject(i+1).toString());}}return wb;}public static String formatCell(HSSFCell hssfCell){if(hssfCell==null){return "";}else{if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){return String.valueOf(hssfCell.getBooleanCellValue());}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){return String.valueOf(hssfCell.getNumericCellValue());}else{return String.valueOf(hssfCell.getStringCellValue());}}}}

public class ResponseUtil {public static void write(HttpServletResponse response,Object o)throws Exception{response.setContentType("text/html;charset=utf-8");PrintWriter out=response.getWriter();out.print(o.toString());out.flush();out.close();}public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));response.setContentType("application/ynd.ms-excel;charset=UTF-8");OutputStream out=response.getOutputStream();wb.write(out);out.flush();out.close();}}

3，利用springMVC中自带的AbstractExcelView或者AbstractJExcelView视图，这2个视图就是专门操作Excel的，区别就是前面一个用的jxl的包，后面一个用的poi的包。

这里贴出我以前用jxl写的一段代码（其实POI一个意思，只不过继承的包不同而已），首先打开AbstractJExcelView源码看一下：

public class ExcelView extends AbstractJExcelView {private String[] columnsNames = { "工单号","县市", "申请人", "申请人手机号码"};private int[] columnWidths = { 10,30,20,20};@Overrideprotected void buildExcelDocument(Map<String, Object> model,WritableWorkbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception {response.setContentType("application/octet-stream");response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode("号码明细.xls", "UTF-8"));OutputStream os = null;try {    os = response.getOutputStream();        workbook = Workbook.createWorkbook(os);        WritableSheet ws = workbook.createSheet("号码明细", 1);        addColumnNamesToSheet(ws);        List<TerminalSendOtherBean> terminalSendOtherBeans = (List<TerminalSendOtherBean>) model.get("beans");        addData(ws, terminalSendOtherBeans);        workbook.write();        } catch (Exception e) {        }finally{            workbook.close();            os.flush();            os.close();                    }}/** * 将数据写入工作表中 *  * @param ws * @param customers */private void addData(WritableSheet ws, List<TerminalSendOtherBean> terminalSendOtherBeans) {Label label = null;for (int i = 0; i < terminalSendOtherBeans.size(); i++) {    TerminalSendOtherBean bean = terminalSendOtherBeans.get(i);try {label = new Label(0, (i + 1), bean.getId()+"");ws.addCell(label);label = new Label(1, (i + 1), bean.getDeptName());ws.addCell(label);label = new Label(2, (i + 1), bean.getPersonName());                ws.addCell(label);                label = new Label(3, (i + 1), bean.getTelephone1());                ws.addCell(label);} catch (WriteException e) {e.printStackTrace();}}}/** * 增加表头 *  * @param ws */private void addColumnNamesToSheet(WritableSheet ws) {Label label = null;for (int i = 0; i < columnsNames.length; i++) {label = new Label(i, 0, columnsNames[i],getFormat());try {ws.addCell(label);ws.setColumnView(i, columnWidths[i]);} catch (WriteException e) {e.printStackTrace();}}}    private WritableCellFormat getFormat() {WritableFont font = new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);WritableCellFormat format=new WritableCellFormat(font);try {format.setWrap(true);format.setAlignment(Alignment.CENTRE);format.setVerticalAlignment(VerticalAlignment.CENTRE);} catch (WriteException e) {e.printStackTrace();}return format;}}

控制器中返回ModelAndView就好了

 public ModelAndView doQuery(HttpServletRequest request, HttpServletResponse response) throws Exception {        FirstInvestmentForm firstInvestmentForm = (FirstInvestmentForm) this.bindForm(request);        FirstInvestmentBean firstInvestmentBean = firstInvestmentForm.getFirstInvestmentBean();        List<FirstInvestmentOtherBean1> list = firstInvestmentService.getBeans1(firstInvestmentBean);        Map<String, List<FirstInvestmentOtherBean1>> model = new HashMap();        model.put("beans", list);        return new ModelAndView(new ExcelView(), model);    }