Java Exporting xlsx files

Userportalstatisticexportservice.java

/** To change the license header, choose License Headers in the Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ Packagecom.ndkey.am.statistics.portal;Importcom.ndkey.am.tenant.TenantId;Importcom.ndkey.exception.DkRuntimeException;Importcom.ndkey.utils.RandomString;ImportJava.io.File;Importjava.io.IOException;ImportJava.io.InputStream;ImportJava.io.OutputStream;ImportJava.util.Calendar;Importjava.util.List;Importorg.apache.commons.io.FileUtils;Importorg.apache.commons.lang3.time.DateFormatUtils;Importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;ImportOrg.apache.poi.openxml4j.opc.OPCPackage;ImportOrg.apache.poi.ss.usermodel.Cell;ImportOrg.apache.poi.xssf.usermodel.XSSFRow;ImportOrg.apache.poi.xssf.usermodel.XSSFSheet;ImportOrg.apache.poi.xssf.usermodel.XSSFWorkbook;Importorg.springframework.transaction.annotation.Transactional;/** * * @authorTino*/@Transactional Public classUserportalstatisticexportservice {PrivateUserportalstatisticservice Userportalstatisticservice; Private Static FinalString date_format = "Yyyy-mm-dd" HH:mm:ss "; Private Final Static intTime_index = 0; Private Final Static intTenant_name_index = 1; Private Final Static intLogin_name_index = 2; Private Final Static intMobile_index = 3; Private Final Static intWeixin_open_id_index = 4; Private Final Static intFirst_login_time_index = 5; Private Final Static intLast_logout_time_index = 6; Private Final Static intAccess_duration_index = 7; Private Final Static intStay_duration_index = 8; Private Final Static intFlow_in_bytes_index = 9; PrivateString Generatefilename () {returnRandomstring.getrandomstring ("qazwsxedcrfvtgbyhnujmikl1234567890", 6); } @Transactional Public voidExportdailyrecords (TenantId TenantId, calendar from, calendar to, OutputStream OS)throwsIOException {List<UserDailyRecordInfo> records = Userportalstatisticservice.getuserdailyrecords (Tenantid.getid (), from, to, 0, Integer.max_value);  This. Exportdailyrecords (Records, OS); } @Transactional Public voidExportdailyrecords (calendar from, calendar to, OutputStream OS)throwsIOException {List<UserDailyRecordInfo> records = Userportalstatisticservice.getuserdailyrecords (from, to, 0, Integer.max_value);  This. Exportdailyrecords (Records, OS); }         Public voidExportdailyrecords (List<userdailyrecordinfo> Records, OutputStream OS)throwsIOException {String TempPath=Fileutils.gettempdirectorypath (); String TemplatePath= "Meta-inf/com/ndkey/am/res/report/template_user_daily.xlsx"; File Excelfile=NewFile (TempPath + "/" + generatefilename () + ". xlsx"); Try(InputStream instream = This. GetClass (). getClassLoader (). getResourceAsStream (TemplatePath))            {Fileutils.copyinputstreamtofile (instream, excelfile);            Xssfworkbook WB; Try(Opcpackage OPK =Opcpackage.open (Excelfile)) {WB=NewXssfworkbook (OPK); Xssfsheet sheet= Wb.getsheetat (0); intMaxrownum =Sheet.getlastrownum (); Xssfrow Row=NULL;  for(inti = 3; i < Maxrownum; i++) {row=Sheet.getrow (i); if(Row! =NULL) {sheet.removerow (row); }                }                                inti = 3;  for(Userdailyrecordinfo record:records) {row= Sheet.createrow (i); Row.createcell (Time_index, cell.cell_type_string). Setcellvalue (Dateformatutils.format (Record.getTime (), DATE_                    FORMAT));                    Row.createcell (Tenant_name_index, cell.cell_type_string). Setcellvalue (Record.gettenantname ());                    Row.createcell (Login_name_index, cell.cell_type_string). Setcellvalue (Record.getloginname ());                    Row.createcell (Mobile_index, cell.cell_type_string). Setcellvalue (Record.getmobile ());                    Row.createcell (Weixin_open_id_index, cell.cell_type_string). Setcellvalue (Record.getweixinopenid ()); Row.createcell (First_login_time_index, cell.cell_type_string). Setcellvalue (Dateformatutils.format (                    Record.getfirstlogintime (), date_format)); Row.createcell (Last_logout_time_index, cell.cell_type_string). Setcellvalue (Dateformatutils.format (                    Record.getlastlogouttime (), date_format)); Row.createcell (Access_duration_index, Cell.cell_type_string). Setcellvalue (Record.getaccessduration ()  /60); Row.createcell (Stay_duration_index, cell.cell_type_string). Setcellvalue (Record.getstayduration ()/60); Row.createcell (Flow_in_bytes_index, cell.cell_type_string). Setcellvalue (Record.getflowinbytes ()/(1024 * 1024)); I++;            } wb.write (OS); } Catch(Invalidformatexception ex) {Throw NewDkruntimeexception (ex); } finally{fileutils.forcedelete (excelfile); }        }    }         Public voidSetuserportalstatisticservice (Userportalstatisticservice userportalstatisticservice) { This. Userportalstatisticservice =Userportalstatisticservice; }    }

Reportcontroller.java

/** To change the license header, choose License Headers in the Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ PackageCom.ndkey.am.web.controller.operator.v3.report;ImportCom.ndkey.am.web.controller.tenant.v2.BaseController;Importjava.io.IOException;ImportJava.io.OutputStream;ImportJava.util.Calendar;ImportJavax.servlet.http.HttpServletResponse;Importorg.springframework.beans.factory.annotation.Autowired;Importorg.springframework.web.bind.annotation.RequestMapping;ImportOrg.springframework.web.bind.annotation.RestController;ImportCom.ndkey.am.statistics.portal.UserPortalStatisticExportService;Importorg.apache.commons.io.IOUtils;ImportOrg.slf4j.Logger;Importorg.slf4j.LoggerFactory;/** * * @authorZXF*/@RestController (Value= "V3operatorreportcontroller") @RequestMapping (value= "/operator/api/3/report") Public classReportcontrollerextendsBasecontroller {Private FinalLogger _logger = Loggerfactory.getlogger (Reportcontroller.class); @AutowiredPrivateUserportalstatisticexportservice Userportalstatisticsexportservice; @RequestMapping (Value= "/userdaily/export")     Public voidExportuserdaily (LongFromLongto, httpservletresponse response) {outputstream OS=NULL; Calendar FromTime=calendar.getinstance ();        Fromtime.settimeinmillis (from); Calendar ToTime=calendar.getinstance ();        Totime.settimeinmillis (to); Try{response.reset (); Response.setcontenttype ("Application/octet-stream;charset=utf-8"); OS=Response.getoutputstream ();        Userportalstatisticsexportservice.exportdailyrecords (FromTime, ToTime, OS); } Catch(IOException e) {_logger.error (E.getmessage (), E); } finally{ioutils.closequietly (OS); }    }}

Java Exporting xlsx files