The introduction
This article uses FreeMarker as a template to export Excel
Click export to export the component by passing in the templateId
1. Define Excel templates
2. Save it as an XML file
3. Create an FTL file under the project
4. Format the XML content
5. Copy the file to FTL
Table design (Simplified version)
CREATE TABLE 'es_templates' (' template_id' bigint(20) NOT NULL COMMENT '主键', 'template_name' varchar(255) DEFAULT NULL COMMENT 'template ',' template_usage 'varchar(255) DEFAULT NULL COMMENT' template ', PRIMARY KEY (`template_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT=' export templates (es_templates)';Copy the codeThe emulation has inserted a piece of template data
7. Export the conditional entity class
Simplified version: assume conditions on the time interval
import java.util.Date; / * * * @ program: choice - goods * @ description: export template condition * @ packagename: com. Meritlink. Choice. Goods. Bean. The vo * @ the author: Cxy * @date: 2021-07-14 11:02:50 **/ public class TemplateExportVO {/** * private date startTime; /** * private Date endTime; public TemplateExportVO() { } public Date getStartTime() { return startTime; } public void setStartTime(Date startTime) { this.startTime = startTime; } public Date getEndTime() { return endTime; } public void setEndTime(Date endTime) { this.endTime = endTime; }}Copy the code8. Control layer
TemplateExport: Conditional filter export exists, add conditional entity class as input (see step 7)
TemplateId: Assume that templates have been added to the previous work and that the database (step 6) contains the template data
The page triggers export, passing in the conditions for exporting data and the selected template ID
import com.meritlink.choice.goods.bean.vo.TemplateExportVO; import com.meritlink.choice.goods.service.goods.ExportManager; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; /** * @program: choicing-goods * @description: export control layer * @packagename: com.meritlink.choice.goods.api.manager.goods * @author: cxy * @date: 2021-07-12 10:28:42 **/ @RestController @RequestMapping("/admin/goods/export") public class ExportManagerController { @Autowired private ExportManager exportManager; @PostMapping("/{templateId}") public void export(HttpServletRequest request, HttpServletResponse response, @RequestBody TemplateExportVO templateExport, @PathVariable Long templateId) throws IOException { this.exportManager.createFreeMarker(request, response, templateExport, templateId); }}Copy the code9. The interface
import com.meritlink.choice.goods.bean.vo.TemplateExportVO; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; / * * * @ program: choice - goods * @ description: export * @ packagename: com. Meritlink. Choice. Goods. Service. Goods * @ the author: cxy * @date: 2021-07-12 10:41:14 **/ public interface ExportManager {/** ** export ** @param request * @param response * @param templateExport * @param templateId * @return */ void createFreeMarker(HttpServletRequest request, HttpServletResponse response, TemplateExportVO templateExport, Long templateId) throws IOException; }Copy the codeThe implementation class
Logic:
10-1: Obtain template information based on templateId (omit query database method here)
10-2: Get templateName(FTL template will be retrieved later) and templateUsage (render data will be retrieved later)
10-3: Obtain data
10-3-1: Obtain data through factory mode + Policy modeCopy the code10-4: create excel
10-4-1: obtains template information based on the template name. 10-4-2: renders data. 10-4-3: outputs a file to response and sends it back to the clientCopy the code
import com.meritlink.choice.framework.exception.ServiceException; import com.meritlink.choice.goods.bean.dos.TemplatesDO; import com.meritlink.choice.goods.bean.vo.TemplateExportVO; import com.meritlink.choice.goods.common.code.GoodsErrorCode; import com.meritlink.choice.goods.service.goods.ExportManager; import com.meritlink.choice.goods.service.goods.TemplateManager; import com.meritlink.choice.goods.service.strategy.templates.ITemplateStrategy; import com.meritlink.choice.goods.service.strategy.templates.TemplateStrategyFactory; import freemarker.template.Configuration; import freemarker.template.Template; import org.apache.commons.lang.StringUtils; import org.apache.http.util.Asserts; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.FileInputStream; import java.io.FileWriter; import java.io.IOException; import java.net.URLEncoder; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @program: choice-goods * @description: export * @packagename: com.meritlink.choice.goods.service.goods.impl * @author: cxy * @date: 2021-07-12 10:41:37 **/ @Service public class ExportManagerImpl implements ExportManager { @Autowired private TemplateManager templateManager; @Autowired private TemplateStrategyFactory templateStrategyFactory; @Override public void createFreeMarker(HttpServletRequest request, HttpServletResponse response, TemplateExportVO templateExport, TemplatesDO template = getTemplateById(templateId); Long templateId) {try {TemplatesDO template = getTemplateById(templateId); String templateName = template.getTemplateName(); String templateUsage = template.getTemplateUsage(); If (stringutils.isblank (templateName)) {throw new ServiceException(goodserrorcode.e301.code (), "templateName empty "); } if (stringutils.isblank (templateUsage)) {throw new ServiceException(goodSerrorcode.e301.code (), "template use is empty "); } Object templateData = templateStrategyFactory.getStrategy(templateUsage).getTemplateData(templateExport); Map<String, Object> map = new HashMap<>(16); map.put("items", templateData); Excel this.createExcel(map, templateName, response); } catch (Exception e) { e.printStackTrace(); } } private TemplatesDO getTemplateById(Long templateId) { TemplatesDO template = templateManager.getTemplateNameById(templateId); If (template == null) {throw new ServiceException(goodSerrorcode.e301.code (), "error "); } return template; } /** * createExcel ** @param dataMap * @param templateName * @param response * @return */ private void createExcel(Map) dataMap, String templateName, HttpServletResponse response) throws IOException { FileInputStream inputStream = null; ServletOutputStream outputStream = null; File file = null; String excelName = templatename.split ("[.]")[0] + System.currentTimemillis () + ".xls"; response.reset(); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF8")); response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.ms-excel; charset=utf-8"); Configuration Configuration = new Configuration(configuration.getVersion ()); // Create a Configuration object. configuration.setDefaultEncoding("utf-8"); / / set the template path configuration. SetClassForTemplateLoading (enclosing getClass (), "/ templates"); / / structure output stream Template Template = configuration. GetTemplate (templateName, "utf-8"); file = new File(excelName); FileWriter out = new FileWriter(excelName); // Replace template.process(dataMap, out); InputStream = new FileInputStream(file); byte[] buffer = new byte[inputStream.available()]; inputStream.read(buffer); inputStream.close(); outputStream = response.getOutputStream(); outputStream.write(buffer); outputStream.flush(); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } finally { if (inputStream ! = null) inputStream.close(); if (outputStream ! = null) outputStream.close(); if (file ! = null) // Delete temporary file file.delete(); }}}Copy the code10-1. Obtain template information based on templateId
The query database method is omitted here, and you can obtain data based on your project.
10-2. Obtain templateName and templateUsage
TemplateUsage is the getCode value of the enumeration class, which will be added for subsequent templates
/ * * * @ program: choice - goods * @ description: templates use * @ packagename: com.meritlink.choice.goods.com mon. Enums * @ the author: Cxy * @date: 2021-07-14 10:25:46 **/ Public enum TemplateUsageEnum {TemplateUsageEnum; private String code; private String desc; TemplateUsageEnum(String code, String desc) { this.code = code; this.desc = desc; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } public String getDesc() { return desc; } public void setDesc(String desc) { this.desc = desc; }}Copy the code10-3. Get render data
I can obtain the rendering data of the template through factory mode + policy mode, so that the class and factory can be modified later, and the template can be added only by adding policies
10-3-1. Obtain data through factory mode + Strategy mode
The factory
Override the setApplicationContext() method to get the value.getStrategyKey() method of all implementation classes of the ITemplateStrategy interface to dynamically add all policies to the templatesMap
Get the policy through the getStrategy() method
import com.meritlink.choice.framework.exception.ResourceNotFoundException; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.BeansException; import org.springframework.context.ApplicationContext; import org.springframework.context.ApplicationContextAware; import org.springframework.stereotype.Component; import java.util.HashMap; import java.util.Map; /** * @program: choicing-goods * @description: export template factory * @packagename: com.meritlink.choice.goods.service.strategy.templates * @author: cxy * @date: 2021-07-14 11:12:30 **/ @Component @Slf4j public class TemplateStrategyFactory implements ApplicationContextAware { private static final Map<String, ITemplateStrategy> templatesMap = new HashMap<>(); @Override public void setApplicationContext(ApplicationContext applicationContext) throws BeansException { Map<String, ITemplateStrategy> map = applicationContext.getBeansOfType(ITemplateStrategy.class); map.forEach((key, value) -> templatesMap.put(value.getStrategyKey(), value)); } public <T extends ITemplateStrategy> T getStrategy(String strategyKey) { ITemplateStrategy strategy = templatesMap.get(ITemplateStrategy.EXPORT_TEMPLATE_STRATEGY_KEY_PREFIX + strategyKey); if (strategy == null) { log.error("TemplateStrategyFactory.getStrategy is error, strategyKey is {}", strategyKey); throw new ResourceNotFoundException("TemplateStrategyFactory.getStrategy is error, strategyKey is " + strategyKey); } return (T) strategy; }}Copy the codestrategy
- Define a common method to get the key of the policy
/** * @program: choicing-goods * @description: policy base interface * @packagename: com.meritlink.choice.goods.service.strategy * @author: cxy * @date: 2021-07-14 10:48:27 **/ public interface BaseStrategy { public final static String KEY_CONCAT = "_"; String getStrategyKey(); }Copy the code- Defining a Policy Interface
import com.meritlink.choice.goods.bean.vo.TemplateExportVO; import com.meritlink.choice.goods.service.strategy.BaseStrategy; /** * @program: choicing-goods * @description: export template policy * @packagename: com.meritlink.choice.goods.service.strategy.templates * @author: cxy * @date: 2021-07-14 10:53:21 **/ public interface ITemplateStrategy extends BaseStrategy { public final static String EXPORT_TEMPLATE_STRATEGY_KEY_PREFIX = ITemplateStrategy.class.getSimpleName() + KEY_CONCAT; ** @param templateExport * @return */ Object getTemplateData(TemplateExportVO templateExport); }Copy the code- Add the implementation of the policy
import com.meritlink.choice.goods.bean.dos.GoodsDO; import com.meritlink.choice.goods.bean.vo.TemplateExportVO; import com.meritlink.choice.goods.common.enums.TemplateUsageEnum; import com.meritlink.choice.goods.service.goods.GoodsManager; import com.meritlink.choice.goods.service.strategy.templates.ITemplateStrategy; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; /** * @program: choicing-goods * @description: com.meritlink.choice.goods.service.strategy.templates.impl * @author: cxy * @date: 2021-07-14 10:56:41 **/ @Service public class GoodsTemplateStrategyImpl implements ITemplateStrategy { @Autowired private GoodsManager goodsManager; @Override public String getStrategyKey() { return EXPORT_TEMPLATE_STRATEGY_KEY_PREFIX + TemplateUsageEnum.GOODS.getCode(); Override public Object getTemplateData(TemplateExportVO templateExport) {List<GoodsDO> goodsList = goodsManager.getGoodsList(1, 10); //return goodsList; }}Copy the code10-4: create excel
dataMap: It must be a Map or JavaBean, otherwise errors will be reported when rendering data. See Step 10 to get Object templateData (which is actually a List< JavaBean>). Then the map. The put (” items “, templateData);
TemplateName templateName (index. FTL)
/** * createExcel ** @param dataMap * @param templateName * @param response * @return */ private void createExcel(Map) dataMap, String templateName, HttpServletResponse response) throws IOException { FileInputStream inputStream = null; ServletOutputStream outputStream = null; File file = null; String excelName = templatename.split ("[.]")[0] + System.currentTimemillis () + ".xls"; response.reset(); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF8")); response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.ms-excel; charset=utf-8"); Configuration Configuration = new Configuration(configuration.getVersion ()); // Create a Configuration object. configuration.setDefaultEncoding("utf-8"); / / set the template path configuration. SetClassForTemplateLoading (enclosing getClass (), "/ templates"); / / structure output stream Template Template = configuration. GetTemplate (templateName, "utf-8"); file = new File(excelName); FileWriter out = new FileWriter(excelName); // Replace template.process(dataMap, out); InputStream = new FileInputStream(file); byte[] buffer = new byte[inputStream.available()]; inputStream.read(buffer); inputStream.close(); outputStream = response.getOutputStream(); outputStream.write(buffer); outputStream.flush(); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } finally { if (inputStream ! = null) inputStream.close(); if (outputStream ! = null) outputStream.close(); if (file ! = null) // Delete temporary file file.delete(); }}Copy the code10-4-1: Obtain template information based on the template name
Configuration Configuration = new Configuration(configuration.getVersion ()); // Create a Configuration object. configuration.setDefaultEncoding("utf-8"); / / set the template path configuration. SetClassForTemplateLoading (enclosing getClass (), "/ templates"); / / structure output stream Template Template = configuration. GetTemplate (templateName, "utf-8");Copy the code10-4-2: Render data
// Replace template.process(dataMap, out);Copy the code10-4-3: Outputs a file to response and sends it back to the client
InputStream = new FileInputStream(file); byte[] buffer = new byte[inputStream.available()]; inputStream.read(buffer); inputStream.close(); response.reset(); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF8")); response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.ms-excel; charset=utf-8"); outputStream = response.getOutputStream(); outputStream.write(buffer); outputStream.flush(); outputStream.close();Copy the code10-4-5: Close streams and delete temporary files
if (inputStream ! = null) inputStream.close(); if (outputStream ! = null) outputStream.close(); if (file ! = null) // Delete temporary file file.delete();Copy the codeconclusion
Later, you only need to add templates to database table ES_template and manually create FTL templates. The required data can be exported by adding corresponding policies to obtain corresponding data, effectively avoiding repeated development of export functions (POI).