Today I want to sort out a problem I encountered some time ago.
Because of the project's requirements, part of the export function must be done. I used the company's export at the beginning, but it was strange that some modules reported an error of 500 when exporting. I found that after deleting some fields, it returned to normal. At that time, because the project was tight, I temporarily deleted some, but it was not a long-term solution. Later, I revised and sorted it out on the original basis. The current operation is still stable. I will share it with you.
Export requires three parts, js, public methods, and background methods.
js code
function exportData(){ //The parameters received by the foreground rwmc = $("#txt_rwmc").val(); rwlb = $("#com_rwlb").combobox("getValues").join(","); //Calling the background export function var service = new Service("cx.RybjcxBndService.exprotExcel"); var str="<RWMC>" + rwmc + "</RWMC><RWLB>" + rwlb + "</RWLB>"; var res = service.doService(str); var oDoc = loadXml(res); if (service.getCode() != "2000") { showMessage("Query failed: "+service.getMessage()); return; } var nodata = oDoc.selectSingleNode("ROOT/NODATA").text; if (nodata == "nodata") { showMessage("No data!"); return; } // Get export information var titleName = oDoc.selectSingleNode("ROOT/TITLE_NAME").text; var fileName = oDoc.selectSingleNode("ROOT/FILE_NAME").text; var outPutInfo = oDoc.selectSingleNode("ROOT/OUTPUTINFO").text; var download_path = oDoc.selectSingleNode("ROOT/DOWNLOAD_PATH").text; if (outPutInfo != "") { showMessage(outPutInfo); return; } if (confirm("Export successfully! Confirm the download file? /n file name is: "+fileName)) { var file = fileName; var showfile = titleName + ".xls"; showfile = decodeURIComponent(showfile); var idx = document.URL.indexOf("/adp"); if (idx == -1) { alert("Host address cannot be recognized: " + document.URL); return; } var host = document.URL.substring(0, idx); var width = screen.width; var height = screen.height; debugger; // Open the download page var param = "toolbar=no,location=no,status=yes,resizable=no,scrollbars=yes,top=" + height + ",left=" + width + ",width=100,height=100"; // --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (isIE6) { window.location.href=host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + file + "&showfile=" + showfile + "&download_path=" + download_path; } else { window.open(host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + file + "&showfile=" + showfile + "&download_path=" + download_path, "_blank", param); } }}Public category
package ctais.business.gzkp.common;import java.io.File;import java.io.FileInputStream;import java.text.SimpleDateFormat;import java.util.Date;import org.apache.poi2.hssf.usermodel.HSSFCell;import org.apache.poi2.hssf.usermodel.HSSFRow;import org.apache.poi2.hssf.usermodel.HSSFSheet;import org.apache.poi2.hssf.usermodel.HSSFWorkbook;import ctaiis.business.dashboard.service.ExportExcel;import ctaiis.config.Config;import ctaiis.services.data.DataWindow;import ctaiis.services.xml.XMLDataObject;import ctaiis.services.xml.XMLParser;import ctaiis.util.StringEx;import jxl.Workbook;import jxl.format.Alignment;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;/** * <p>Title: Generate EXCEL file</p> * <p>Description: Convert String string to EXCEL document</p> * <p>Copyright: Copyright (c) 2004</p> * <p>Company: DC</p> * @author FENGZG * @version 1.0 * Time: 2015-12-28 */public class CreateExcel { private final static String CONFIG_FILE_PATH = Config.CTAIS_HOME; WritableWorkbook wwb = null; XMLDataObject xdo = null; public CreateExcel(){ } /** * Generate EXCEL * @param sql Query SQL * @param czryDm Operator Code * @param titles Export column title * @param exlTitle excel table header * @return * @throws Exception */ public String newToExcel(String sql,String czryDm,String[] titles,String exlTitle) throws Exception { try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); ExportExcel exportExcel = new ExportExcel(wb, sheet); StringBuffer sffer = new StringBuffer(); //int colNum = 30; DataWindow dw = DataWindow.dynamicCreate(sql.toString()); dw.setConnectionName(Icomm.GZKPJNDI); long dwRet = dw.retrieve(); if (dwRet <= 0) { sffer.append("<NODATA>nodata</NODATA>"); return sffer.toString(); } else { sffer.append("<NODATA></NODATA>"); } int colNum = dw.getColumnCount(); // Define the column width for the worksheet column (actually change the number of columns by yourself) for (short i = 0; i <= colNum; i++) { sheet.setColumnWidth(i, (short) 4000); } // Create cell style HSSFCellStyle cellHeadStyle = wb.createCellStyle(); // Specify cell center alignment cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // Specify cell vertically center alignment cellHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Specify cell line wrapping when the cell content is not displayed below cellHeadStyle.setWrapText(true); // Set cell font HSSFFont headFont = wb.createFont(); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headFont.setFontName("安一"); headFont.setFontHeight((short) 200); cellHeadStyle.setFont(headFont); // Create report header Date dt=new Date(); SimpleDateFormat sdt=new SimpleDateFormat("yyyyMMddhhmmssS"); String sfm = czryDm + "_" + sdt.format(dt); // Set column header exportExcel.createNormalHead(exlTitle, colNum-1); HSSFRow row1 = sheet.createRow(1); for(int i = 0; i < titles.length; i ++) { HSSFCell cell = row1.createCell((short)i); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellStyle(cellHeadStyle); cell.setCellValue(titles[i]); } Object value = ""; //Set the table style HSSFCellStyle cellStyle = wb.createCellStyle(); // Specify cell center alignment cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // Specify cell vertically center alignment cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Specify cell line wrapping when the cell content cannot be displayed cellStyle.setWrapText(true); // Set cell font HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.SS_NONE); font.setFontName("宋体"); font.setFontHeight((short) 200); cellStyle.setFont(font); for(int i = 0 ; i < dw.getRowCount(); i++) { HSSFRow row = sheet.createRow(i + 2); for(int j = 1; j <= dw.getColumnCount(); j++) { HSSFCell cell = row.createCell((short)(j-1)); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellStyle(cellStyle); value = dw.getItemAny(i, j-1); if(value == null) { cell.setCellValue(""); } else { cell.setCellValue(value.toString()); } } } //Set the export path, please note here that if it is a Linux system that requires manual path creation, (If someone is clear about the reason here, please give me some advice) Quote the newly created file path String path = "/export/"; File file = new File(path); if(!file.exists()) { file.mkdirs(); } String fileName = sfm+".xls"; //String pth = path.trim() + File.separator + fileName; String pth = path.trim() + fileName; pth = pth.trim(); String outPutInfo = exportExcel.outputExcel(pth); sffer.append("<TITLE_NAME>" + sfm + "</TITLE_NAME>"); sffer.append("<DOWNLOAD_PATH>" + path + "</DOWNLOAD_PATH>"); sffer.append("<FILE_NAME>"" + fileName + "</FILE_NAME>"); sffer.append("<OUTPUTINFO>" + outPutInfo + "</OUTPUTINFO>"); return sffer.toString(); } catch (Exception e) { e.printStackTrace(); throw new Exception(e.getMessage()); } }}Background code
/** * Export function* @param xdo Foreground parameter* @param czryDm Operator code* @return Generated XLS information* @throws Exception description*/ public String exportExcel(XMLDataObject args,String czryDm) throws Exception { //Receive query parameters passed by the foreground String rwmc = StringEx.sNull(args.getItemValue("RWMC")); String rwlb = StringEx.sNull(args.getItemValue("RWLB")); if(null != rwmc && !"".equals(rwmc)) { sqlWhere.append(" AND A.RWMC LIKE '%"+rwmc+"%' "); } if(null != rwlb && !"".equals(rwlb)) { sqlWhere.append(" AND A.RWLB_DM = '"+rwlb+"' "); } StringBuilder sql = new StringBuilder(); //Splicing query SQL sql.append("SELECT RWXH,RWMC FROM RWXX") .append(sqlWhere).append(" ORDER BY RWXH ) ").append(sqlisWhere); //Exported column title String[] titles = {"Task Serial Number", "Task Name"}; //Instantiate the public class CreateExcel excel = new CreateExcel(); return excel.newToExcel(sql.toString(), czryDm, titles,"Exlcel header"); }The above method of exporting Excel (supporting IE low version) is all the content I have shared with you. I hope you can give you a reference and I hope you can support Wulin.com more.