|
阅读：10086回复：5
4.2平台导出Excel问题
4.2平台导出Excel没有表头。如图所示：
图片：QQ图片20170322103449.png  |
|
最新喜欢： 曾昭洪
|
|
板凳#
发布于：2017-09-20 17:08
这是平台导出的问题。需要重新修改导出的servlet。现将代码贴出来。
import com.sunsheen.jfids.commons.io.IOUtils; import com.sunsheen.jfids.gson.Gson; import com.sunsheen.jfids.gson.reflect.TypeToken; import com.sunsheen.jfids.system.base.composite.data.query.QueryParameterImpl; import com.sunsheen.jfids.system.persistence.ISession; import com.sunsheen.jfids.system.servlet.Servlet; import com.sunsheen.jfids.util.DataBaseUtil; import com.sunsheen.jfids.util.IdsDate; import com.sunsheen.jfids.util.StringUtil; import java.io.BufferedInputStream; import java.io.BufferedReader; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.net.URLDecoder; import java.net.URLEncoder; import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import javax.servlet.ServletException; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; 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.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; 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.CellRangeAddress; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.transform.Transformers; @Servlet("/data/GridToExcel.svt") public class GridToExcelServlet extends HttpServlet { private static final long serialVersionUID = 1L; private ArrayList<Map> jsoncolums; private int columsnum; private String title; private Query query; private HSSFWorkbook book; private HSSFSheet sheet; private HSSFRow row; private HSSFCell cell; private HSSFCellStyle style; private HSSFFont font; public String getStringFromInputStream(InputStream is) throws IOException { BufferedReader br = new BufferedReader(new InputStreamReader( new BufferedInputStream(new BufferedInputStream(is)))); StringBuffer sb = new StringBuffer(); try { String line = ""; while ((line = br.readLine()) != null) { sb.append(line).append("\n"); } } catch (IOException e) { e.printStackTrace(); } return sb.toString(); } protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException { InputStream is = null; try { this.title = URLDecoder.decode(req.getParameter("title"), "UTF-8"); this.title = ((this.title == null) || ("".equals(this.title)) ? new SimpleDateFormat( "yyyy-MM-dd").format(new Date()) : this.title); resp.setContentType("application/vnd.ms-excel"); resp.setCharacterEncoding("UTF-8"); resp.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(this.title, "UTF-8") + ".xls"); is = getExcel(req); // resp.setHeader("Content-length", is.available()); IOUtils.copy(is, resp.getOutputStream()); } catch (IOException e) { e.printStackTrace(); try { is.close(); resp.getOutputStream().close(); } catch (Exception e1) { e1.printStackTrace(); } } finally { try { is.close(); resp.getOutputStream().close(); } catch (Exception e) { e.printStackTrace(); } } } protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doGet(req, resp); } @SuppressWarnings("rawtypes") private InputStream getExcel(HttpServletRequest req) throws IOException { StringBuilder columsBuilder = new StringBuilder(); String baseparams = URLDecoder.decode(req.getParameter("baseparams"), "UTF-8"); String dataurl = URLDecoder.decode(req.getParameter("dataurl"), "UTF-8"); columsBuilder.append(URLDecoder.decode(req.getParameter("column"), "UTF-8")); Gson gson = new Gson(); this.jsoncolums = gson.fromJson(columsBuilder.toString(), ArrayList.class); /* this.jsoncolums = ((ArrayList<Map>)StringUtil.gson.fromJson(columsBuilder.toString(), new TypeToken(){}.getType()));*/ for (Map column : this.jsoncolums) { //System.out.println(column); /* if (!((Boolean)column.get("export_")).booleanValue()) { this.jsoncolums.remove(column); }*/ } HashMap hm2 = (HashMap)StringUtil.gson.fromJson(baseparams, HashMap.class); Object hm = new HashMap(); for (String s : dataurl.substring(dataurl.indexOf('?') + 1).split("&")) { int length = s.split("=").length; if (length > 0) ((HashMap)hm).put(s.split("=")[0], length > 1 ? s.split("=")[1] : ""); } this.columsnum = this.jsoncolums.size(); ((HashMap)hm).putAll(hm2); for (Object key : ((HashMap)hm).keySet()) { Object aObject = ((HashMap)hm).get(key); if ((aObject instanceof String)) { ((HashMap)hm).put(key, convertDateStringToDate((String)aObject)); } } ISession session = DataBaseUtil.getHibernateSession(); session.beginTransaction(); SQLQuery querytrad = session.createDySQLQuery((String)((HashMap)hm).get("sqlid"), (Map)hm); this.query = new QueryParameterImpl().initParameter(querytrad, (Map)hm); session.commit(); initExcel(); initFirstRowExcel(); initOrtherRowExcel(); return exportExcel(); } private static Object convertDateStringToDate(String dateStr) { if (dateStr == null) { return null; } Map<String, Object> ret = new HashMap(); Pattern datePattern = Pattern.compile("^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]((((0?[13578])|(1[02]))[\\-\\/\\s]((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]((((0?[13578])|(1[02]))[\\-\\/\\s]((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(T(((0?[0-9])|([1-2][0-3]))\\:([0-5]?[0-9])((\\s)|(\\:([0-5]?[0-9])))))?$"); Pattern arrayPattern = Pattern.compile("^\\s*\\[([\\w\\W]*)\\]\\s*$"); if (datePattern.matcher(dateStr).matches()) { try { if (dateStr.length() <= 10) { return new IdsDate( new SimpleDateFormat("yyyy-MM-dd").parse(dateStr), "yyyy-MM-dd"); } return new IdsDate(new SimpleDateFormat( "yyyy-MM-dd'T'HH:mm:ss").parse(dateStr), "yyyy-MM-dd HH:mm:ss"); } catch (ParseException e1) { return null; } } return dateStr; } private void initExcel() { this.book = new HSSFWorkbook(); this.sheet = this.book.createSheet(this.title); sheet.setDefaultColumnWidth((short) 15); this.row = this.sheet.createRow(0); this.row.setHeight((short)600); this.style = this.book.createCellStyle(); this.style.setVerticalAlignment((short)1); this.style.setAlignment((short)2); this.style.setBorderBottom((short)1); this.style.setBorderLeft((short)1); this.style.setBorderRight((short)1); this.style.setBorderTop((short)1); this.font = this.book.createFont(); this.font.setBoldweight((short)700); this.font.setFontHeight((short)(this.font.getFontHeight() * 2)); this.style.setFont(this.font); for (int i = 0; i < this.columsnum; i++) { this.cell = this.row.createCell(i); this.cell.setCellStyle(this.style); this.cell.setCellValue(this.title); } this.sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, this.columsnum - 1)); } private void initFirstRowExcel() { this.style = this.book.createCellStyle(); this.style.setVerticalAlignment((short)1); this.style.setAlignment((short)1); this.style.setBorderBottom((short)1); this.style.setBorderLeft((short)1); this.style.setBorderRight((short)1); this.style.setBorderTop((short)1); this.font = this.book.createFont(); this.font.setBoldweight((short)700); this.style.setFont(this.font); this.row = this.sheet.createRow(1); this.row.setHeight((short)300); } private void initOrtherRowExcel() { this.style = this.book.createCellStyle(); this.style.setVerticalAlignment((short)1); this.style.setAlignment((short)1); this.style.setBorderBottom((short)1); this.style.setBorderLeft((short)1); this.style.setBorderRight((short)1); this.style.setBorderTop((short)1); this.query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List<Map<String, Object>> ls = this.query.list(); /** * 添加一行标题 */ //this.sheet.autoSizeColumn((short)15); HSSFRow row = sheet.createRow(1); for (short i = 0; i < this.jsoncolums.size(); i++) { HSSFCell cell = row.createCell(i); //cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(this.jsoncolums.get(i).get("header").toString()); cell.setCellValue(text); cell.setCellStyle(this.style); } for (int i = 0; i < ls.size(); i++) { this.row = this.sheet.createRow(i + 2); Map<String, Object> ob = (Map)ls.get(i); for (int j = 0; j < this.columsnum; j++) { this.cell = this.row.createCell(j); String dataIndex = (String)((Map)this.jsoncolums.get(j)).get("dataIndex"); Map columConfig = (Map)this.jsoncolums.get(j); if (ob.get(dataIndex) != null) { if (columConfig.get("dateFormat") != null) { String dateFormat = ((String)columConfig.get("dateFormat")).replace("Y", "yyyy") .replace("m", "MM").replace("d", "dd") .replace("H", "hh").replace("i", "mm") .replace("s", "ss"); SimpleDateFormat sdFormat = new SimpleDateFormat( dateFormat); this.cell.setCellValue(sdFormat.format(ob.get(dataIndex))); } else if (columConfig.get("numberFormat") != null) { DecimalFormat df2 = new DecimalFormat( (String)columConfig.get("numberFormat")); this.cell.setCellValue(df2.format(ob.get(dataIndex))); } else { this.cell.setCellValue(ob.get(dataIndex) == null ? "" : ob .get(dataIndex).toString()); } } else { this.cell.setCellValue(""); } this.cell.setCellStyle(this.style); } } } private InputStream exportExcel() throws IOException { ByteArrayOutputStream bos = new ByteArrayOutputStream(); this.book.write(bos); ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray()); bos.close(); return bis; } } 创建一个这样的servlet。注解一定要用代码所示名字，才能覆盖平台本身的导出的servlet。 |
|
|
|
地板#
发布于：2018-08-10 09:46
|
|
|
4楼#
发布于：2019-09-06 17:10
|
|
