Shulou(Shulou.com)06/02 Report--

How to use EasyPOI to achieve dynamic generation of columns, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Prepare for

Cn.afterturn easypoi-base 3.2.0 org.slf4j slf4j-api org.apache.commons commons-lang3 Cn.afterturn easypoi-annotation 3.2.0 IV. Detailed steps

Define a table style

/ * define table style * * @ param start query start date * @ param end query end date * @ return java.util.List * @ author huan * @ date 2019-6-21 * @ since 2.8.2 * / private List setExportExcelStyle (DateTime start, DateTime end) {/ / define table column name What the collection holds is the listing of the table. Each object is a column of List modelList = new ArrayList () in the table. / / this object is the object that defines the column properties ExcelExportEntity excelentity = null; / / defines the first column excelentity = new ExcelExportEntity ("enterprise full name", "companyName"); excelentity.setWidth (20); excelentity.setHeight (10); modelList.add (excelentity); / / defines the second column excelentity = new ExcelExportEntity ("enterprise abbreviation", "companyShortName") Excelentity.setWidth (20); excelentity.setHeight (10); modelList.add (excelentity); / / define the third column, where the date display format excelentity = new ExcelExportEntity ("Certification date", "openDate"); excelentity.setWidth (20); excelentity.setHeight (10); excelentity.setFormat ("yyyy-MM-dd"); modelList.add (excelentity) is specified / / define the fourth column, which is dynamically generated here. With the date range selected by the user, the number of dynamically generated columns excelentity = new ExcelExportEntity (null, "recordDate"); / / set a collection to store the dynamically generated column List modelListChild = new ArrayList (); start = DateUtils.getDateZeroTime (start) While (start.isBefore (end)) {String date = start.toString ("yyyy-MM-dd"); modelListChild.add (new ExcelExportEntity (date, date, 15)); start = start.plusDays (1);} / / dates are sorted from smallest to largest. Here the simplest bubble sort for (int I = 0; I < modelListChild.size ()) is used. For (int j = 0; j < modelListChild.size ()) {ExcelExportEntity e1 = modelListChild.get (I). GetKey (). ToString (); String e2 = modelListChild.get (j). GetKey (). ToString (); if (e1.compareTo (e2) < 0) {ExcelExportEntity x1 = modelListChild.get (I) ExcelExportEntity x2 = modelListChild.get (j); modelListChild.set (j, x1); modelListChild.set (I, x2);} / / put the defined word column in the parent column excelentity.setList (modelListChild); modelList.add (excelentity) / / define the fifth column excelentity = new ExcelExportEntity ("days should be used", "shouldUseDay"); excelentity.setWidth (20); excelentity.setHeight (10); modelList.add (excelentity); / / define the sixth column excelentity = new ExcelExportEntity ("days in practice", "actualUseDay"); excelentity.setWidth (20); excelentity.setHeight (10) ModelList.add (excelentity); / / define the seventh column excelentity = new ExcelExportEntity ("usage", "rate"); excelentity.setWidth (20); excelentity.setHeight (10); modelList.add (excelentity); / / define the eighth column excelentity = new ExcelExportEntity ("references", "commandMan"); excelentity.setWidth (20); excelentity.setHeight (10) ModelList.add (excelentity); / / define the ninth column excelentity = new ExcelExportEntity ("Topper", "tk"); excelentity.setWidth (20); excelentity.setHeight (10); modelList.add (excelentity); / / define the tenth column excelentity = new ExcelExportEntity ("docker", "connector"); excelentity.setWidth (20); excelentity.setHeight (10) ModelList.add (excelentity); return modelList;}

Define tabular data

Private List getData (AnalyseStockQuery analyseStockQuery, boolean type) {/ / get data source ArrayList dtoList = listDetailDataWithNum (analyseStockQuery, type); List dataList = new ArrayList (); / / store date data without a row List dataListChild = null; / / store each row of data in the table Map mapParent = null; / / data sort dtoList.sort (new ExportComparator ()) / / define table data for (AnalyseStockExportDto dto: dtoList) {mapParent = new HashMap (7); / / as long as the name here is the same as when defining the table style, I do not set the default value mapParent.put ("companyName", dto.getCompanyName ()) because there are three fields that do not need to be generated by my side. MapParent.put ("companyShortName", dto.getCompanyShortName ()); mapParent.put ("openDate", dto.getOpenDate ()); mapParent.put ("shouldUseDay", dto.getShouldUseDay ()); mapParent.put ("actualUseDay", dto.getActualUseDay ()); mapParent.put ("rate", dto.getRate ()); Map map = dto.getDateList () DataListChild = new ArrayList (); dataListChild.add (map); mapParent.put ("recordDate", dataListChild); dataList.add (mapParent);} return dataList;}

Subject method

/ * * report export * * @ param analyseStockQuery analyseStockQuery * @ param response response * @ return javax.servlet.http.HttpServletResponse * @ author huan * @ date 2019-6-21 * @ since 2.8.2 * / public HttpServletResponse exportStock (AnalyseStockQuery analyseStockQuery, HttpServletResponse response) {try {/ / set the default query date analyseStockQuery = setDefaultQueryDate (analyseStockQuery) / / Parameter check checkListDetailDataParam (analyseStockQuery); / / date format DateTime start = new DateTime () .withDate (new LocalDate (analyseStockQuery.getQueryStartDate (); DateTime end = new DateTime () .withDate (new LocalDate (analyseStockQuery.getQueryLastDate (); / / define table style List modelList = setExportExcelStyle (start, end) / / define table name String fileName = URLEncoder.encode ("customer inventory usage Statistics -" + start.toString ("yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy / / set the name of sheet sheet1ExportParams.setSheetName ("incoming statistics"); / / use map Map sheet1ExportMap = new HashMap () to create sheet1; / / the parameter of title is of type ExportParams. Currently, only sheetName sheet1ExportMap.put ("title", sheet1ExportParams) is set in ExportParams. / / sheet1 style sheet1ExportMap.put ("entityList", modelList); / / data to be populated in sheet1. True means querying incoming data. False means querying easy-to-check-in data sheet1ExportMap.put ("data", getData (analyseStockQuery, true)); / / Sheet2 sets ExportParams sheet2ExportParams = new ExportParams () Sheet2ExportParams.setSheetName ("easy check-in Statistics"); Map sheet2ExportMap = new HashMap (); sheet2ExportMap.put ("title", sheet2ExportParams); sheet2ExportMap.put ("entityList", modelList); sheet2ExportMap.put ("data", getData (analyseStockQuery, false)); / / Packaging map used by sheet1 and sheet2 List sheetsList = new ArrayList () SheetsList.add (sheet1ExportMap); sheetsList.add (sheet2ExportMap); / / execution method Workbook workBook = exportExcel (sheetsList, ExcelType.HSSF); / / set response response.setHeader ("content-disposition", "attachment;filename=" + fileName + ".xls"); / / set encoding format response.setCharacterEncoding ("GBK") / / write the table contents to the output stream and refresh the cache @ Cleanup ServletOutputStream out = response.getOutputStream (); workBook.write (out); out.flush (); workBook.close ();} catch (FileNotFoundException e) {log.debug ("FileNotFoundException: {}", e.getMessage ()) } catch (UnsupportedEncodingException e) {log.debug ("UnsupportedEncodingException: {}", e.getMessage ());} catch (IOException e) {log.debug ("IOException: {}", e.getMessage ());} return response;} is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.