1. 如何用jxl导出大批量数据到Excel
jxl最多支持excel2003，所以单sheet最大的记录数为65536；Office2007单sheet支持的记录数已经超过100W了。对于大批量数据版，我都是每次取65535条记权录出来（第一行有表头）放在一个Sheet里面。插入完毕后清空list再去取下一页。经测试，单文件多Sheet出现内存溢出比多文件单sheet的机率大得太多，服务器的jvm怎么得也要512以上。
2. 用jxl从数据库导出的excel文件是导出到本地还是导出到服务器
导出到服务器，详细代码如下：public String exportMeetingUser() throws NumberFormatException, ServiceException, IOException, RowsExceededException, WriteException { String forward = SUCCESS; // TODO 进行导出操作 // 获取查询条件 String username = this.getParameter("username"); String mobile = this.getParameter("mobile"); String isAdmin = this.getParameter("isAdmin"); String meetingId = this.getParameter("meetingId"); User user = new User(); user.setName(username); user.setMobile(mobile); // 把PAGE_SIZE设置很大 pageSize = 1000; Pager<User> pager = userService.findMeetingUserPager( Long.valueOf(meetingId), user, currentPage, pageSize, isAdmin); log.debug("meeting id is： {}", meetingId); List<User> userList = pager.getPageRecords(); String[] title = { "姓名", "手机号码", "职位(通讯录)", "单位", "房间号", "显示房间号", "性别", "电子邮箱", "城市", "加入通讯录", "显示电话号码", "排序", "职位简称", "显示职位简称" }; // TODO 进行导出的操作 SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); String exportFileName = sdf.format(new Date()); // 创建文件夹 File uploadFoldPath = new File(fileSystemService.getDocumentRoot() + this.USER_EXPORT_FOLDER); if (!uploadFoldPath.exists()) { uploadFoldPath.mkdirs(); } // 判断文件是否存在 String fileSaveName = exportFileName + ".xls"; String dstPath = fileSystemService.getDocumentRoot() + this.USER_EXPORT_FOLDER + File.separator + fileSaveName; String from = "WEB"; String documentRoot = fileSystemService.getDocumentRoot(); // DOCUMENT ROOT getRequest().setAttribute("documentRoot", documentRoot); getRequest().setAttribute("from", from); OutputStream os = new FileOutputStream(dstPath); // 再建完这个文件的时候再建立工作文件 jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os); int rowIndex = 0; Label label; WritableCellFormat wc = new WritableCellFormat(); wc.setAlignment(Alignment.CENTRE); //wc.set WritableSheet sheet = wwb.createSheet("会议用户导出", 0); label = new Label(2, rowIndex, "此文件为会议云用户导出文件"); sheet.addCell(label); rowIndex++; // 将列标题循环添加到Label中 for (int i = 0; i < title.length; i++) { label = new Label(i, rowIndex, title[i]); sheet.addCell(label); } rowIndex++; // TODO下面是添加数据 if (userList != null && userList.size() > 0) { for (User exportUser : userList) { MeetingMember member = meetingMemberService.selectById( exportUser.getId(), Long.valueOf(meetingId)); exportUser.setMeetingMember(member); // "姓名", "手机号码", "职位(通讯录)", "单位", "房间号", "显示房间号", "性别", // "电子邮箱", "城市", "加入通讯录", "显示电话号码", "排序", "职位简称", "显示职位简称" // 用户名 label = new Label(0, rowIndex, exportUser.getName()); sheet.addCell(label); label = new Label(1, rowIndex, exportUser.getMobile()); sheet.addCell(label); label = new Label(2, rowIndex, exportUser.getMeetingMember() .getJob()); sheet.addCell(label); // 单位 label = new Label(3, rowIndex, exportUser.getMeetingMember() .getDepartment()); sheet.addCell(label); // 房间号 label = new Label(4, rowIndex, exportUser.getMeetingMember() .getRoomNumber()); rowIndex++; } } wwb.write(); wwb.close(); // 现在开始进行下载操作 MeetingFiles meetingFiles = new MeetingFiles(); meetingFiles.setFilePath(this.USER_EXPORT_FOLDER); meetingFiles.setFileName(fileSaveName); meetingFiles.setFileSaveName(fileSaveName); this.getRequest().setAttribute("meetingFiles", meetingFiles); return forward;
3. jxl 导出excel 结果打开文件出现“文件错误，某些数据格式已丢失” 查了很多网上的回答 说是excel版本问题
会出现这种情况的，譬如你使用2010版的Excel打开2003版的excel文件，在里面进行编辑，同时使用了很多只有2010版及其以上的版本才会有的功能或者模块。那么你在保存的时候他就会提示你如下：
以上，希望对你有所帮助。
4. Jxl如何创建EXCEL模板,并怎样可以有效导出EXCEL数据
package gongju;import java.util.Vector;import javax.servlet.http.HttpServletResponse;import org.apache.struts2.ServletActionContext;import com.opensymphony.xwork2.ActionSupport;import .ExcelDao;import jxl.Workbook;import jxl.format.UnderlineStyle;import jxl.write.Label;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;public class DaoChuExcel extends ActionSupport { /** * 生成Excel文件 * @return L */ public String DCEecel() { HttpServletResponse response = ServletActionContext.getResponse(); try { String fileName = "test"; response.setContentType("aplication/msexcel"); // 设置文件类型 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); WritableWorkbook book = Workbook.createWorkbook(response .getOutputStream()); // 创建文件 WritableSheet sheet = book.createSheet("sheet", 0); // 创建工作薄 jxl.write.WritableFont titleFont = new jxl.write.WritableFont( WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.DARK_BLUE); jxl.write.WritableCellFormat titleCellFormat = new jxl.write.WritableCellFormat( titleFont); // 设置表头 String[] titles = new String[] { "Jid", "Jdoec", "Jmin", "Jmax" }; int columnLength[] = { 20, 30, 10, 30, 20, 15, 15, 15 }; for (int i = 0; i < titles.length; i++) { sheet.setColumnView(i, columnLength[i]); // 设置列的宽度 Label label = new Label(i, 0, titles[i], titleCellFormat); sheet.addCell(label); } ExcelDao e = new ExcelDao(); // 动态填充数据 Vector vector = e.ex(); for (int i = 0; i < vector.size(); i++) { Object[] jobdata = (Object[]) vector.elementAt(i); for (int j = 0; j < jobdata.length; j++) { if(jobdata[j] instanceof Integer) { Label wlabel1 = new Label(j, i, jobdata[j].toString()); //转String、 sheet.addCell(wlabel1); }else{ Label wlabel1 = new Label(j, i, (String) jobdata[j]); sheet.addCell(wlabel1); } } } book.write(); book.close(); } catch (Exception e) { e.printStackTrace(); } return null; }}
5. Jxl如何创建EXCEL模板,并怎样可以有效导出EXCEL数据
response.setHeader("Content-disposition", "attachment; filename="+ fileName + ".xls");WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream()); // 创建文件WritableSheet sheet = book.createSheet("sheet", 0); // 创建工作薄 jxl.write.WritableFont titleFont = new jxl.write.WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.DARK_BLUE);jxl.write.WritableCellFormat titleCellFormat = new jxl.write.WritableCellFormat(titleFont); // 设置表头String[] titles = new String[] { "Jid", "Jdoec", "Jmin", "Jmax" }; int columnLength[] = { 20, 30, 10, 30, 20, 15, 15, 15 };for (int i = 0; i < titles.length; i++) {sheet.setColumnView(i, columnLength[i]); // 设置列的宽度Label label = new Label(i, 0, titles[i], titleCellFormat);sheet.addCell(label);} ExcelDao e = new ExcelDao();// 动态填充数据Vector vector = e.ex();for (int i = 0; i < vector.size(); i++) {Object[] jobdata = (Object[]) vector.elementAt(i);for (int j = 0; j < jobdata.length; j++) {if(jobdata[j] instanceof Integer) {Label wlabel1 = new Label(j, i, jobdata[j].toString()); //转String、sheet.addCell(wlabel1);}else{Label wlabel1 = new Label(j, i, (String) jobdata[j]);sheet.addCell(wlabel1);}}}book.write();book.close();
6. Jxl如何创建EXCEL模板,并怎样可以有效导出EXCEL数据
具体实现代码如下：package gongju;import java.util.Vector;import javax.servlet.http.HttpServletResponse;import org.apache.struts2.ServletActionContext;import com.opensymphony.xwork2.ActionSupport;import .ExcelDao;import jxl.Workbook;import jxl.format.UnderlineStyle;import jxl.write.Label;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;public class DaoChuExcel extends ActionSupport {/*** 生成Excel文件* @return L*/public String DCEecel() { HttpServletResponse response = ServletActionContext.getResponse();try {String fileName = "test";response.setContentType("aplication/msexcel"); // 设置文件类型response.setHeader("Content-disposition", "attachment; filename="+ fileName + ".xls");WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream()); // 创建文件 WritableSheet sheet = book.createSheet("sheet", 0); // 创建工作薄 jxl.write.WritableFont titleFont = new jxl.write.WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.DARK_BLUE);jxl.write.WritableCellFormat titleCellFormat = new jxl.write.WritableCellFormat(titleFont); // 设置表头String[] titles = new String[] { "Jid", "Jdoec", "Jmin", "Jmax" }; int columnLength[] = { 20, 30, 10, 30, 20, 15, 15, 15 };for (int i = 0; i < titles.length; i++) {sheet.setColumnView(i, columnLength[i]); // 设置列的宽度Label label = new Label(i, 0, titles[i], titleCellFormat);sheet.addCell(label);} ExcelDao e = new ExcelDao();// 动态填充数据Vector vector = e.ex();for (int i = 0; i < vector.size(); i++) {Object[] jobdata = (Object[]) vector.elementAt(i);for (int j = 0; j < jobdata.length; j++) {if(jobdata[j] instanceof Integer) {Label wlabel1 = new Label(j, i, jobdata[j].toString()); //转String、sheet.addCell(wlabel1);}else{Label wlabel1 = new Label(j, i, (String) jobdata[j]);sheet.addCell(wlabel1);}}}book.write();book.close();} catch (Exception e) {e.printStackTrace();} return null;}}
7. java jxl导出excle但是弹不出对话框，指定路径可以生成excel
jxl没有提供界面控件，你需要用JFileChooser自己做文件选择对话框，然后用jxl将文件写到选择的文件中。
8. jxl怎么导出excel2007
public String exportxm() throws Exception { HttpServletRequest request = ServletActionContext.getRequest(); // String wid=request.getParameter("wid"); String[] headers = { "项目名称", "填报人", "填报时间", "合同额", "预计毛利", "项目计划完成时间", "所属部门"}; String[] columns = { "sernum", "personer.name", "times","xsjobes", "xsb", "plannedate", "subdept.name"}; Map<String, Object> values = new HashMap<String, Object>(); //Page<Worktype> page1 = new Page<Worktype>(40); // 设置默认排序方式 if (!page.isOrderBySetted()) { page.setOrderBy("id"); page.setOrder(Page.DESC); } page.setPageSize(9999); page = manager.searchWork(page, values); String title="项目管理表"; // 生成Excel文件. Workbook wb = new ExcelExport(title, headers,columns, page) .exportExcelWorkbook(); // 输出Excel文件. HttpServletResponse response = Struts2Utils.getResponse(); response.setContentType(ServletUtils.EXCEL_TYPE); ServletUtils.setFileDownloadHeader(response, "项目管理-"+accountManager.getcurrentUser().getName()+".xls"); wb.write(response.getOutputStream()); response.getOutputStream().flush(); return null; } 、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、 /** * 设置让浏览器弹出下载对话框的Header. * * @param fileName 下载后的文件名. */ public static void setFileDownloadHeader(HttpServletResponse response, String fileName) { try { //中文文件名支持 String encodedfileName = new String(fileName.getBytes(), "ISO8859-1"); response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedfileName + "\""); } catch (UnsupportedEncodingException e) { } } public static final String EXCEL_TYPE = "application/vnd.ms-excel";、、、、、、、、、、、、、、、/** * 导出到excel * @param title 标题 * @param headers 表格头 * @param result 数据 */ @SuppressWarnings({ "rawtypes", "unchecked" }) public ExcelExport(String title,String[] headers,String[]columns, Page page){ this.title=title; this.headers=headers; this.result=transhashmap(columns, page.getResult()); } /** * 将listObject 转换为List<HashMap<Integer, Object>> * @param columns * @param olist * @return */ private List<HashMap<Integer, Object>> transhashmap(String[] columns,List<Object> olist){ List<HashMap<Integer, Object>> result=Lists.newArrayList(); for(Object o:olist){ HashMap<Integer, Object> ht=this.transhashmap(columns, o); result.add(ht); } return result; } /** * 将object 转换为hashmap * @param columns * @param olist * @return */ private HashMap<Integer, Object> transhashmap(String[] columns,Object o){ int col=0; HashMap<Integer, Object> ht=new HashMap<Integer, Object>(); for(String column:columns){ //根据属性名称直接获取值 Object value=this.invokeGetterMethod(o, column); ht.put(col,value); col=col+1; } return ht; }
9. java利用jxl导出Excel的问题
把标题和其实体类路径放到一个数组里，然后将数据push到list里遍历list，每个对象都是标题和其对应的get方法
未经允许不得转载：山九号 » jxl导出excel文件|如何用jxl导出大批量数据到Excel
